CODEWITHSUNDEEP
pandaspivot-table
Jordan
Jordan

Reputation: 1495

How do I make the pandas index of a pivot table part of the column names?

I'm trying to pivot two columns out by another flag column with out multi-indexing. I would like to have the column names be a part of the indicator itself. Take for example:

import pandas as pd
df_dict = {'fire_indicator':[0,0,1,0,1],
           'cost':[200, 300, 354, 456, 444],
           'value':[1,1,2,1,1],
           'id':['a','b','c','d','e']}
df = pd.DataFrame(df_dict)

If I do the following:

df.pivot_table(index = 'id', columns = 'fire_indicator', values = ['cost','value'])

I get the following:

                 cost        value     
fire_indicator      0      1     0    1
id                                     
a               200.0    NaN   1.0  NaN
b               300.0    NaN   1.0  NaN
c                 NaN  354.0   NaN  2.0
d               456.0    NaN   1.0  NaN
e                 NaN  444.0   NaN  1.0

What I'm trying to do is the following:

id    fire_indicator_0_cost    fire_indicator_1_cost    fire_indicator_0_value    fire_indicator_0_value
a               200                      0                          1                        0
b               300                      0                          1                        0
c                0                      354                         0                        2
d               456                      0                          1                        0
e                0                      444                         0                        1

I know there is a way in SAS. Is there a way in python pandas?

Upvotes: 0

Views: 557

Answers (1)

Quang Hoang
Quang Hoang

Reputation: 150745

Just rename and re_index:

out = df.pivot_table(index = 'id', columns = 'fire_indicator', values = ['cost','value'])

out.columns = [f'fire_indicator_{y}_{x}' for x,y in out.columns]

# not necessary if you want `id` be the index
out = out.reset_index()

Output:

    id      fire_indicator_0_cost    fire_indicator_1_cost    fire_indicator_0_value    fire_indicator_1_value
--  ----  -----------------------  -----------------------  ------------------------  ------------------------
 0  a                         200                      nan                         1                       nan
 1  b                         300                      nan                         1                       nan
 2  c                         nan                      354                       nan                         2
 3  d                         456                      nan                         1                       nan
 4  e                         nan                      444                       nan                         1

Upvotes: 1

Related Questions