Pivot pandas data and add column

Question

I'm trying to add a summed column to a pivoted dataframe but keep getting a data parse error.

mydata = [{'amount': 3200, 'close_date':'2013-03-31', 'customer': 'Customer 1',},
          {'amount': 1430, 'close_date':'2013-11-30', 'customer': 'Customer 2',},
          {'amount': 4320, 'close_date':'2014-03-31', 'customer': 'Customer 3',},
          {'amount': 2340, 'close_date':'2015-05-18', 'customer': 'Customer 4',},
          {'amount': 4320, 'close_date':'2015-06-29', 'customer': 'Customer 5',},]
df = pd.DataFrame(mydata)
>>> df.dtypes
amount         int64
close_date    object
customer      object
dtype: object

I convert to dates and then display as quarters

df.close_date = pd.to_datetime(df.close_date)
>>> df.dtypes
amount                 int64
close_date    datetime64[ns]
customer              object
dtype: object

df.close_date = df.close_date.dt.to_period('Q')

I then pivot:

pivot = pd.pivot_table(df,index='customer',columns='close_date')

Finally, I want to sum the rows and the columns but my this piece of code won't seem to add a column...

pivot['sum'] = pivot.sum(axis=1)

What am I doing wrong?

Alexander · Accepted Answer · 2015-12-20 04:22:13Z

You can just set margins to True.

df['close_date'] = pd.to_datetime([cd.to_period('Q') for cd in df.close_date])
pivot = pd.pivot_table(df,index='customer',columns='close_date', margins=True)

>>> pivot
            amount                        
close_date     172   175   176   181   All
customer                                  
Customer 1    3200   NaN   NaN   NaN  3200
Customer 2     NaN  1430   NaN   NaN  1430
Customer 3     NaN   NaN  4320   NaN  4320
Customer 4     NaN   NaN   NaN  2340  2340
Customer 5     NaN   NaN   NaN  4320  4320
All            NaN   NaN   NaN   NaN  3122

You can also sum the columns as follows:

pivot = pd.pivot_table(df,index='customer',columns='close_date')
pivot['sum'] = pivot.sum(axis=1)

>>> pivot
            amount                     sum
close_date     172   175   176   181      
customer                                  
Customer 1    3200   NaN   NaN   NaN  3200
Customer 2     NaN  1430   NaN   NaN  1430
Customer 3     NaN   NaN  4320   NaN  4320
Customer 4     NaN   NaN   NaN  2340  2340
Customer 5     NaN   NaN   NaN  4320  4320

And this is how you could sum the rows:

pivot.ix['Total'] = pivot.sum()

>>> pivot
           amount                     
close_date 2013Q1 2013Q4 2014Q1 2015Q2
customer                              
Customer 1   3200    NaN    NaN    NaN
Customer 2    NaN   1430    NaN    NaN
Customer 3    NaN    NaN   4320    NaN
Customer 4    NaN    NaN    NaN   2340
Customer 5    NaN    NaN    NaN   4320
Total        3200   1430   4320   6660

Collectives™ on Stack Overflow

Pivot pandas data and add column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related