10

So my dataset has some information by business n dates as below:

Business    Date    Value
a         1/1/2017   127
a         2/1/2017   89
b         2/1/2017   122
a         1/1/2018   555
a         2/1/2018   455

I need this data as below format: How can i tranpose it . And i dont want multilevel in my output dataset

Business    1/1/2017  2/1/2017 1/1/2018  2/1/2018
 a           127         89     555        455
 b           N/A        122      N/A       N/A

I tried below syntax:

df = df.set_index(['Business','Date'])['Value'].unstack()
df=df.pivot(index='Business', columns='Date', values='Value')

i got the output as below:

Date    1/1/2017    2/1/2017    1/1/2018    2/1/2018
Business
 a        454         5555        555         444 
 b        -            444         -           -

when i print columns, it doesn't show LOB as column. My final dataframe should also include Business,Date fields as columns so that i can join this dataframe with another dataframe on business

DYZ
  • 55,249
  • 10
  • 64
  • 93

2 Answers2

18

You are very close to what you want. All you need is to remove the custom index and replace it with the default index.

pivoted = df.pivot(index='Business', columns='Date', values='Value')\
            .reset_index()
pivoted.columns.name=None
print(pivoted)
#  Business  1/1/2017  1/1/2018  2/1/201  2/1/2017
#0        a     127.0     555.0    455.0      99.0
#1        b       NaN       NaN      NaN     122.0
DYZ
  • 55,249
  • 10
  • 64
  • 93
4

Use pivot:

df.pivot(index='Business', columns='Date', values='Value')
zipa
  • 27,316
  • 6
  • 40
  • 58
  • 1
    You'll need to clarify and provide example. This code transforms data you provided as example into data you provided as desired output :) – zipa Feb 24 '18 at 00:32