Data Manipulation - using data frame aggregation function

Question

You guys were very helpful with my question before - see link below. I was looking to sort the index which had alphanumeric values. I have run this script which was successful today but have been receiving an error:

/Library/Python/2.7/site-packages/pandas/core/groupby.py:4036: FutureWarning: using a dict with renaming is deprecated and will be removed in a future version
  return super(DataFrameGroupBy, self).aggregate(arg, *args, **kwargs)
Traceback (most recent call last)
aggfunc={'sum': np.sum}, fill_value=0)
  File "/Library/Python/2.7/site-packages/pandas/core/reshape/pivot.py", line 136, in pivot_table
    agged = grouped.agg(aggfunc)
  File "/Library/Python/2.7/site-packages/pandas/core/groupby.py", line 4036, in aggregate
    return super(DataFrameGroupBy, self).aggregate(arg, *args, **kwargs)

Traces back to the pivot:

df = df.pivot_table(index=['customer'], columns=['Duration'],
                                                     aggfunc={'sum': np.sum}, 
    fill_value=0)

The only change that I've applied before this error was to introduce a calculation to one data column of the data frame rather then run the calculation in the SQL statement.

New calculation:

df['Duration'] = df['Duration']/30

Old group-by and aggregation:

df = df.pivot_table(index=['customer'], columns=['Duration'],
                                             aggfunc={'sum': np.sum}, fill_value=0)
c = df.columns.levels[1]
c = sorted(ns.natsorted(c), key=lambda x: not x.isdigit())
df = df.reindex_axis(pd.MultiIndex.from_product([df.columns.levels[0], c]), axis=1)

New Code snippet:

df = df.groupby(['customer', 'Duration']).agg({'sum': np.sum})
c = df.columns.get_level_values(1)
c = sorted(ns.natsorted(c), key=lambda x: not x.isdigit())
df = df.reindex_axis(pd.MultiIndex.from_product([df.columns.levels[0], c]), axis=1)

Multi-index levels with new approach:

MultiIndex(levels=[[u'Invoice A', u'Invoice B', u'Invoice C', u'Invoice B'], [u'0', u'1', u'10', u'11', u'2', u'2Y', u'3', u'3Y', u'4', u'4Y', u'5', u'5Y', u'6', u'7', u'8', u'9', u'9Y']], labels=[[0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0], [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16]], names=['customer', u'Duration'])

When assigning this c = df.columns.get_level_values(1), I receive an error message: IndexError: Too many levels: Index has only 1 level, not 2

Input sample:

customer              Duration             sum          
Invoice A                1                 1250
Invoice B                2                 2000
Invoice B                3                 1200
Invoice C                2                 10250
Invoice D                3                 20500
Invoice D                5                 18900
Invoice E                2Y                5000
Invoice F                1                 5000
Invoice F                1Y                12100

Not sure why, as both levels and names have two levels. The end result is a data frame that is sorted by customer and the columns are sorted by Duration showing the sum for each Duration. Also, the reason why I used pivot in the previous code version was so that I keep this output format:

Duration                            2          2Y         3         3Y   
customer                                                                     
Invoice A                         2550        0.00      0.00       2000   
Invoice B                         5000        2500      1050       0.00
Invoice C                         12500       0.00      1120       2050
Invoice D                         0.00        1500      0.00       8010

Am I on the right track?

Data Manipulation - stackoverflow

Answer 1

You can use instaed agg function sum() and then reshape by unstack:

import natsort as ns

df = df.groupby(['customer', 'Duration'])['sum'].sum().unstack()

c = sorted(ns.natsorted(df.columns), key=lambda x: not x.isdigit())
df = df.reindex(columns=c)
print (df)
Duration        1        2        3        5       1Y      2Y
customer                                                     
Invoice A  1250.0      NaN      NaN      NaN      NaN     NaN
Invoice B     NaN   2000.0   1200.0      NaN      NaN     NaN
Invoice C     NaN  10250.0      NaN      NaN      NaN     NaN
Invoice D     NaN      NaN  20500.0  18900.0      NaN     NaN
Invoice E     NaN      NaN      NaN      NaN      NaN  5000.0
Invoice F  5000.0      NaN      NaN      NaN  12100.0     NaN