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

Improve this question
0

2 Answers 2

Reset to default
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
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

4

Use pivot:

df.pivot(index='Business', columns='Date', values='Value')
Improve this answer

1 Comment

You'll need to clarify and provide example. This code transforms data you provided as example into data you provided as desired output :)