pandas - include all column and row pair values

Question

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.

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:

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.

Any suggestions?

Thanks

Answer 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.

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