pandas - 包括所有列和行对值

I have a dataset that is missing a fair amount of data. Example data file:

我有一个缺少大量数据的数据集。示例数据文件:

a,b,c,w
a1,,,
a2,b1,c1,
a2,b1,c2,
a2,,,
a3,b2,c3,
a4,,,
a5,b1,c1,100
a6,b2,c4,
a7,b1,c2,214.285714285714
a7,b1,c2,245.454545454545
a7,b1,c2,292.105263157895
a7,b1,c2,
a8,b1,c2,
a9,b2,c3,
,b3,,
,,c4,
,,c5,

I am struggling to create a pivot table that looks like:

我正在努力创建一个如下所示的数据透视表:

         w
      mean
a       a1  a2  a3  a4     a5  a6          a7  a8  a9
b  c
       NaN NaN NaN NaN    NaN NaN         NaN NaN NaN
b1 c1  NaN NaN NaN NaN  100.0 NaN         NaN NaN NaN
b1 c2  NaN NaN NaN NaN    NaN NaN  250.615174 NaN NaN
b2 c3  NaN NaN NaN NaN    NaN NaN         NaN NaN NaN
b2 c4  NaN NaN NaN NaN    NaN NaN         NaN NaN NaN
b3     NaN NaN NaN NaN    NaN NaN         NaN NaN NaN
   c4  NaN NaN NaN NaN    NaN NaN         NaN NaN NaN
   c5  NaN NaN NaN NaN    NaN NaN         NaN NaN NaN

I don't care if the blanks are at the top or bottom. The key is that every A value appears as a column and for rows, only the existing B,C pairs are displayed.

我不在乎空白是在顶部还是在底部。关键是每个A值都显示为一列,对于行,只显示现有的B,C对。

The following code:

以下代码:

dataframe = pd.read_csv('test/data/sparse.csv')
pd.set_option('display.width', 1000)
print(dataframe)
col_names = ['a']
row_names = ['b', 'c']
value_names = ['w']
aggregates = {'w': ['mean']}

pivot = pd.pivot_table(
    dataframe,
    index=row_names,
    columns=col_names,
    values=value_names,
    aggfunc=aggregates
)

creates a pivot table like:

创建一个数据透视表,如:

           w
        mean
a         a5          a7
b  c
b1 c1  100.0         NaN
   c2    NaN  250.615174
b2 c3    NaN         NaN
   c4    NaN         NaN

If I set all None values to blank, via:

如果我将所有None值设置为空白,请通过:

for c in dataframe:
    if str(dataframe[c].dtype) in ('object', 'string_', 'unicode_'):
        dataframe[c].fillna(value='', inplace=True)

then I get

然后我明白了

           w            
        mean            
a         a5          a7
b  c                    
         NaN         NaN
   c4    NaN         NaN
   c5    NaN         NaN
b1 c1  100.0         NaN
   c2    NaN  250.615174
b2 c3    NaN         NaN
   c4    NaN         NaN
b3       NaN         NaN

which gets me my rows but not my columns. If I add dropna=False to the pivot_table call, then I get all my columns, but I also get row pairs that don't exist in my original data set.

它让我的行,但不是我的列。如果我将pivotna = False添加到pivot_table调用,那么我将获得所有列,但我也获得了原始数据集中不存在的行对。

Any suggestions?

Thanks

2 个解决方案

#1

If you're okay with nan instead of blank spaces, then groupby + unstack works here. First, convert columns a, b, and c to string using astype(str). This will cause groupby to no longer ignore NaNs when grouping data.

如果你没有使用nan而不是空格,那么groupby + unstack就可以在这里运行。首先,使用astype(str)将列a,b和c转换为字符串。这将导致groupby在分组数据时不再忽略NaN。

cols = ['a', 'b', 'c']
df[cols] = df[cols].astype(str)

df.groupby(cols)\
  .w.mean()\
  .unstack(0)\
  .drop('nan', 1)

a        a1  a2  a3  a4     a5  a6          a7  a8  a9
b   c                                                 
b1  c1  NaN NaN NaN NaN  100.0 NaN         NaN NaN NaN
    c2  NaN NaN NaN NaN    NaN NaN  250.615174 NaN NaN
b2  c3  NaN NaN NaN NaN    NaN NaN         NaN NaN NaN
    c4  NaN NaN NaN NaN    NaN NaN         NaN NaN NaN
b3  nan NaN NaN NaN NaN    NaN NaN         NaN NaN NaN
nan c4  NaN NaN NaN NaN    NaN NaN         NaN NaN NaN
    c5  NaN NaN NaN NaN    NaN NaN         NaN NaN NaN
    nan NaN NaN NaN NaN    NaN NaN         NaN NaN NaN

#2

One way to reach your target output is to collect all of your unique pairs of b and c as tuples:

达到目标输出的一种方法是将所有唯一的b和c对收集为元组:

tups = df[['b', 'c']].drop_duplicates().apply(tuple, axis=1)

# 0     (nan, nan)
# 1       (b1, c1)
# 2       (b1, c2)
# 4       (b2, c3)
# 7       (b2, c4)
# 14     (b3, nan)
# 15     (nan, c4)
# 16     (nan, c5)

...and then call .pivot_table with dropna=True, and immediately reindex with your b-c tuples:

...然后使用dropna = True调用.pivot_table,并立即使用b-c元组重新索引:

df.pivot_table( \
  index=['b', 'c'], columns='a', aggfunc='mean', dropna=False \ 
  ).reindex(tups)

#           w                                           
# a        a1  a2  a3  a4     a5  a6          a7  a8  a9
# NaN NaN NaN NaN NaN NaN    NaN NaN         NaN NaN NaN
# b1  c1  NaN NaN NaN NaN  100.0 NaN         NaN NaN NaN
#     c2  NaN NaN NaN NaN    NaN NaN  250.615174 NaN NaN
# b2  c3  NaN NaN NaN NaN    NaN NaN         NaN NaN NaN
#     c4  NaN NaN NaN NaN    NaN NaN         NaN NaN NaN
# b3  NaN NaN NaN NaN NaN    NaN NaN         NaN NaN NaN
# NaN c4  NaN NaN NaN NaN    NaN NaN         NaN NaN NaN
#     c5  NaN NaN NaN NaN    NaN NaN         NaN NaN NaN