Pandas describe(include='all')在SQL生成的DataFrame中无法正常运行

我通过原生SQL查询生成Pandas DataFrame后，df.describe()可以正常执行，但调用df.describe(include='all')时出现报错，无法查看整个DataFrame的描述信息。

我的代码如下：

import psycopg2
import pandas as pd
import numpy as np

credentials = {
    'database': '',
    'host': '',
    'user': '',
    'password': ''
}

print('Database connection started.')
conn = psycopg2.connect(**credentials)
cur = conn.cursor()
cur.execute('select * from userdetail')
df = pd.DataFrame(cur.fetchall())
fields = [x[0] for x in cur.description]
cur.close()
conn.close()
print("Database connection is closed now ")
df.columns = fields

df.describe() # works
df.describe(include='all') # doesn't work

完整报错栈信息：

---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
<ipython-input-1-10a5f5b12254> in <module>()
     20 print("Database connection is closed now ")
     21 df.columns = fields
---> 22 df.describe(include='all')

/usr/lib/python3.6/site-packages/pandas/core/generic.py in describe(self, percentiles, include, exclude)
   8568         data = self.select_dtypes(include=include, exclude=exclude)
   8569 
-> 8570         ldesc = [describe_1d(s) for _, s in data.iteritems()]
   8571         # set a convenient order for rows
   8572         names = []

/usr/lib/python3.6/site-packages/pandas/core/generic.py in <listcomp>(.0)
   8568         data = self.select_dtypes(include=include, exclude=exclude)
   8569 
-> 8570         ldesc = [describe_1d(s) for _, s in data.iteritems()]
   8571         # set a convenient order for rows
   8572         names = []

/usr/lib/python3.6/site-packages/pandas/core/generic.py in describe_1d(data)
   8551             return describe_numeric_1d(data)
   8552         else:
-> 8553             return describe_categorical_1d(data)
   8554 
   8555         if self.ndim == 1:

/usr/lib/python3.6/site-packages/pandas/core/generic.py in describe_categorical_1d(data)
   8525 def describe_categorical_1d(data):
   8526     names = ['count', 'unique']
-> 8527     objcounts = data.value_counts()
   8528     count_unique = len(objcounts[objcounts != 0])
   8529     result = [data.count(), count_unique]

/usr/lib/python3.6/site-packages/pandas/core/base.py in value_counts(self, normalize, sort, ascending, bins, dropna)
   1036         from pandas.core.algorithms import value_counts
   1037         result = value_counts(self, sort=sort, ascending=ascending,
-> 1038                               normalize=normalize, bins=bins, dropna=dropna)
   1039         return result
   1040 

/usr/lib/python3.6/site-packages/pandas/core/algorithms.py in value_counts(values, sort, ascending, normalize, bins, dropna)
    714 
    715         else:
--> 716             keys, counts = _value_counts_arraylike(values, dropna)
    717 
    718         if not isinstance(keys, Index):

/usr/lib/python3.6/site-packages/pandas/core/algorithms.py in _value_counts_arraylike(values, dropna)
    759         # TODO: handle uint8
    760         f = getattr(htable, "value_count_{dtype}".format(dtype=ndtype))
--> 761         keys, counts = f(values, dropna)
    762 
    763         mask = isna(values)

pandas/_libs/hashtable_func_helper.pxi in pandas._libs.hashtable.value_count_object()

pandas/_libs/hashtable_func_helper.pxi in pandas._libs.hashtable.value_count_object()

TypeError: unhashable type: 'list'

错误原因分析 #

这个报错的核心原因是：你的DataFrame中存在包含list类型的列。当调用df.describe(include='all')时，Pandas会尝试对所有列（包括非数值型列）计算统计信息，其中会调用value_counts()来统计分类列的频次。但list是不可哈希（unhashable）的类型，而value_counts()需要元素是可哈希的才能进行分组计数，所以就抛出了这个TypeError。

而df.describe()默认只统计数值型列，不会处理那些包含list的非数值列，所以能正常运行。

解决方法 #

方法1：排除包含list类型的列 #

先找出哪些列包含list类型，然后在调用describe时排除这些列：

# 找出包含list的列
list_cols = [col for col in df.columns if df[col].apply(lambda x: isinstance(x, list)).any()]
# 排除这些列后调用describe(include='all')
df.drop(list_cols, axis=1).describe(include='all')

方法2：将list类型列转换为可哈希的类型 #

如果这些list列对你的统计有意义，可以把它们转换成字符串或者元组（元组是可哈希的）：

# 转换为字符串
df['your_list_col'] = df['your_list_col'].astype(str)
# 或者转换为元组
df['your_list_col'] = df['your_list_col'].apply(tuple)
# 之后再调用describe(include='all')
df.describe(include='all')

方法3：使用read_sql直接生成DataFrame（更简洁的方式） #

其实你可以不用手动处理fetchall和列名，直接用pd.read_sql来读取数据，它会自动处理列类型和列名，可能避免这类问题：

import psycopg2
import pandas as pd

credentials = {
    'database': '',
    'host': '',
    'user': '',
    'password': ''
}

print('Database connection started.')
conn = psycopg2.connect(**credentials)
# 直接用read_sql读取
df = pd.read_sql('select * from userdetail', conn)
conn.close()
print("Database connection is closed now ")

# 现在尝试调用describe(include='all')
df.describe(include='all')

注意：如果数据库中对应的字段本身就是数组类型（比如PostgreSQL的array），pd.read_sql可能还是会把它转成list，这时候你还是需要用方法1或方法2来处理。

内容的提问来源于stack exchange，提问作者ShrAwan Poudel