CODEWITHSUNDEEP
pandaspivot-table
codingknob
codingknob

Reputation: 11660

pivoting pandas df - turn column values into column names

I have a df:

    pd.DataFrame({'time_period': {0: pd.Timestamp('2017-04-01 00:00:00'),
  1: pd.Timestamp('2017-04-01 00:00:00'),
  2: pd.Timestamp('2017-03-01 00:00:00'),
  3: pd.Timestamp('2017-03-01 00:00:00')},
 'cost1': {0: 142.62999999999994,
  1: 131.97000000000003,
  2: 142.62999999999994,
  3: 131.97000000000003},
 'revenue1': {0: 56,
  1: 113.14999999999998,
  2: 177,
  3: 99},
 'cost2': {0: 309.85000000000002,
  1: 258.25,
  2: 309.85000000000002,
  3: 258.25},
 'revenue2': {0: 4.5,
  1: 299.63,2: 309.85,
  3: 258.25},
 'City': {0: 'Boston',
  1: 'New York',2: 'Boston',
  3: 'New York'}})

I want to re-structure this df such that for revenue and cost separately:

    pd.DataFrame({'City': {0: 'Boston', 1: 'New York'},
 'Apr-17 revenue1': {0: 56.0, 1: 113.15000000000001},
 'Apr-17 revenue2': {0: 4.5, 1: 299.63},
 'Mar-17 revenue1': {0: 177, 1: 99},
 'Mar-17 revenue2': {0: 309.85000000000002, 1: 258.25}})

And a similar df for costs.

Basically, turn the time_period column values into column names like Apr-17, Mar-17 with revenue/cost string as appropriate and values of revenue1/revenue2 and cost1/cost2 respectively.

I've been playing around with pd.pivot_table with some success but I can't get exactly what I want.

Upvotes: 1

Views: 78

Answers (1)

Vaishali
Vaishali

Reputation: 38415

Use set_index and unstack 

import datetime as dt
df['time_period'] = df['time_period'].apply(lambda x: dt.datetime.strftime(x,'%b-%Y'))

df = df.set_index(['A', 'B', 'time_period'])[['revenue1', 'revenue2']].unstack().reset_index()
df.columns = df.columns.map(' '.join)


    A           B       revenue1 Apr-2017   revenue1 Mar-2017   revenue2 Apr-2017   revenue2 Mar-2017
0   Boston      Orlando 56.00               177.0               4.50                309.85
1   New York    Dallas  113.15              99.0                299.63              258.25

Upvotes: 2

Related Questions