CODEWITHSUNDEEP
pandaspython-3.5
Zanam
Zanam

Reputation: 4807

Transforming pandas dataframe while creating new columns

I have a pandas dataframe df which has values as following:

HE  PR  MastLoc Loc     Type    Qty
1   2   E1      LocA    TC      3
2   23  E1      LocA    TB      18
3   17  E1      LocA    TC      11
3   2   E1      LocB    TC      3
24  3   E1      LocB    TB      4

I want to create the following dataframe df from the above dataframe:

MastLoc Loc     Type    Qty1    PR1   Qty2  PR2   Qty3  PR3  Qty24  PR24
E1      LocA    TC      3       2                 11    17      
E1      LocA    TB                    18    23              
E1      LocB    TC                                3     2       
E1      LocB    TB                                            4      3

New Columns are created by concatenating column names Qty and PR with integer values in column HE (column HE always has integer values).

df2 basically has rows with unique combination of MastLoc, Loc and Type and all values from columns Qty and HE have been moved to new columns created using integers from column HE.

So, far I have done:

df['colQty'] = ['Qty' + str(x) for x in df['HE'].tolist()]
df['colPR'] = ['PR' + str(x) for x in df['HE'].tolist()]

I am not able to figure a clean way of doing anything further.

Upvotes: 1

Views: 105

Answers (1)

BENY
BENY

Reputation: 323356

Using pivot_table with multiple index adjust

s=pd.pivot_table(df,index=['MastLoc','Loc','Type'],columns=['HE'],values=['PR','Qty'],aggfunc='sum')
s.columns=s.columns.map('{0[0]}{0[1]}'.format) 
s
Out[895]: 
                   PR1   PR2   PR3  PR24  Qty1  Qty2  Qty3  Qty24
MastLoc Loc  Type                                                
E1      LocA TB    NaN  23.0   NaN   NaN   NaN  18.0   NaN    NaN
             TC    2.0   NaN  17.0   NaN   3.0   NaN  11.0    NaN
        LocB TB    NaN   NaN   NaN   3.0   NaN   NaN   NaN    4.0
             TC    NaN   NaN   2.0   NaN   NaN   NaN   3.0    NaN

Upvotes: 5

Related Questions