CODEWITHSUNDEEP
pythonpandaspivot
Axattack
Axattack

Reputation: 101

Pivot table pandas with column prefix

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

Upvotes: 9

Views: 10591

Answers (1)

abakar
abakar

Reputation: 301

You can use the add_prefix() method as shown below:

# Create pandas dataframe
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)

# Pivot data and add prefix
df.pivot(index="rowkey",columns="asset",values="value").reset_index().add_prefix('asset_')

here an output after manipulation:

output

Best,

Abakar

Upvotes: 10

Related Questions