Reorganise the data into matrix data format

user_19591552 0
Viewed 33

I have a dataframe like this:

df = pd.DataFrame({'isin': ['a', 'a', 'c', 'd','c', 'e', 'd','f','s','d','c','f','a'], 'fii':['ee', 'ff', 'gg', 'hh','hh','ee','gg','rr','hh','ss','gg','rr','ff'], 'buy_value':[40,80, 15, 100, 45, 90, 120, 60, 50 ,20,60,20,15]})

and another data frame

df2 = pd.DataFrame({'isin': ['c', 'a', 'd','f', 'e'], 'mcap' : ['l','s','l','m','s'], 'company':['hdfc','sbi','pnb','cb','ub']})

I need to create a new data frame with column header as unique 'fii', 'mcap', 'company' while row header as unique 'isin'. Values inside data frame should be sum of buy_value (in case isin and fii combination repeats).

Kindly help.

pandas dataframe python
1 Answers

Use DataFrame.merge with DataFrame.pivot_table:

df1 = (df.merge(df2, on='isin', how='left')
         .pivot_table(index=['isin','mcap','company'],
                      columns='fii', 
                      values='buy_value', 
                      aggfunc='sum'))
print (df1)
fii                  ee    ff     gg     hh    rr    ss
isin mcap company                                      
a    s    sbi      40.0  95.0    NaN    NaN   NaN   NaN
c    l    hdfc      NaN   NaN   75.0   45.0   NaN   NaN
d    l    pnb       NaN   NaN  120.0  100.0   NaN  20.0
e    s    ub       90.0   NaN    NaN    NaN   NaN   NaN
f    m    cb        NaN   NaN    NaN    NaN  80.0   NaN
user_29010020
Related