Pivot table pandas with column prefix

user_8857533 0
Viewed 5387

I am trying to reshape dataframe as shown in the images. I want to create a wide dataframe with the values in asset as new columns with the prefix asset_. In the new asset_ columns the corresponding value from the value column should be stored. See attached images. This is some sort of pivot operation but I can't figure out following:

  1. How to dynamically add the prefix asset_ to the new columns.
  2. How to keep the ser_num and day column that have the same values for each row.

I tried to do this with pandas.pivot but there are no prefix parameter and I do not want to create any multilevel indexes:

Import pandas as pd
data  = {"rowkey":["2017:P7W", "2017:P7W", "2017:P7W"], 
            "ser_num":[467, 467, 467], 
            "asset": ["A", "B", "C"], 
            "value": [123,456, 789],
           "day":[1,1,1]}
df = pd.DataFrame(data)
df.pivot(index="rowkey", columns="asset", values="value").reset_index()

In my problem I have 200+ unique assets so the result will be a very wide dataframe. See images:

Dataframe before manipulation

Dataframe after manipulation

pandas pivot python
1 Answers

Hello you have to use : ad_prefix() like below in your code

import pandas as pd 
data  = {"rowkey":["2017:P7W", "2017:P7W", "2017:P7W"], 
         "ser_num":[467, 467, 467], "asset": ["A", "B", "C"], 
          "value": [123,456,789],"day":[1,1,1]} 
df =pd.DataFrame(data)

df.head()

df.pivot(index="rowkey",columns="asset",values="value").reset_index().add_prefix('asset_')

here an output after manipulation:

output

Best, Abakar

user_116750710
Related