Convert single df to multiple dfs by interchanging rows to columns and taking sum of each column in Pandas

user_11445415 0
Viewed 116

I have the following pandas dataframe:

Depts Category Monthly Booked Monthly Delivered Monthly Target Yearly Booked Yearly Delivered Yearly Target
HR Human 2345 2000 3000 1234556 234543 6432212
Software Engg 654345 343213 765432 98765123 2345654 9999999
Security Human 1234 1234 2000 23456 34568 234567
Software Engg 12345 54334 324546 345645345 65345654 643563452
Software Human 12345 54334 324546 345645345 65345654 643563452
Security Engg 12345 54334 324546 34564534 65345654 643563452

Now I want to convert the values of Depts to column headers and group by Category and then take the Yearly and Monthly sums to two datatables along with total of each metric of each column. Like below:

Monthly Data

Category Metric Software Security HR
Engg Target 1089978 324546
Delivered 397547 12345
Booked 666690 54334
Human Target 324546 2000 3000
Delivered 54334 1234 2000
Booked 12345 1234 2345
Total Target 1414524 326546 3000
Delivered 451881 1234 2000
Booked 679035 55568 2345

Yearly Data

Category Metric Software Security HR
Engg Target 653563451 643563452
Delivered 67691308 65345654
Booked 44410468 34564534
Human Target 643563452 234567 6432212
Delivered 65345654 34568 234543
Booked 345645345 23456 1234556
Total Target 1297126903 643798019 6432212
Delivered 133036962 65380222 234543
Booked 390055813 34587990 1234556

Can I do this using pandas functions? If yes, then how can I do it? Note: I also want to keep the grouping but change indices to columns. Meaning I want to change the index names to column names but keep the grouping in the first two columns.

My present code - based on the answer given by @Code Different below:

tmp = df.set_index(["Category", "Depts"])
tmp.columns = pd.MultiIndex.from_tuples([tuple(col.split(" ")) for col in tmp.columns], name=[None, "Metric"])
tmp = tmp.stack(level=1)

monthly = tmp.pivot_table(index=["Category", "Metric"], columns="Depts", values="Monthly", aggfunc="sum")

monthly = pd.concat([d.append(d.sum().rename(('Total', k))) for k, d in monthly.groupby(level=1)])
monthly = monthly.groupby(level=[0, 1], as_index=True).sum()
monthly.loc[:,'Total'] = monthly.sum(axis=1)

This keeps the multilevel index but if I use reset_index then the grouping is lost if I use to_html or to_excel functions. I want to avoid that.

pandas-groupby pandas dataframe python
2 Answers

Try this:

tmp = df.set_index(["Category", "Depts"])
tmp.columns = pd.MultiIndex.from_tuples([tuple(col.split(" ")) for col in tmp.columns], name=[None, "Metric"])
tmp = tmp.stack(level=1)

monthly = tmp.pivot_table(index=["Category", "Metric"], columns="Depts", values="Monthly", aggfunc="sum")
yearly  = tmp.pivot_table(index=["Category", "Metric"], columns="Depts", values="Yearly", aggfunc="sum")
user_25389390

Inspired based on the other answer's work:

def pivot_and_stuff(dataframe, values):

    tmp = dataframe.set_index(["Category", "Depts"])
    tmp.columns = pd.MultiIndex.from_tuples([tuple(col.split(" ")) for col in tmp.columns], name=[None, "Metric"])
    tmp = tmp.stack(level=1)

    tmp = tmp.pivot_table(index=["Category", "Metric"], columns="Depts", values=values, aggfunc="sum")
    tmp2 = tmp.groupby(level=[1]).sum()
    tmp2['Category'] = 'Total'
    tmp2 = tmp2.set_index('Category', append=True).reorder_levels([1,0])

    dataframe = pd.concat([tmp, tmp2]).rename_axis('', axis=1).rename_axis(['Category', 'Metric'])
    dataframe = dataframe.reset_index().rename_axis('', axis=1)
    dataframe.Category = [i if not j else '' for i, j in zip(dataframe.Category.values, dataframe.Category.duplicated())]
    
    return dataframe

pd.set_option('display.float_format', '{:.0f}'.format)

df_m = pivot_and_stuff(df, 'Monthly')
df_y = pivot_and_stuff(df, 'Yearly')
print(df_m)
print()
print(df_y)

Output:

  Category     Metric   HR  Security  Software
0     Engg     Booked  NaN     12345    666690
1           Delivered  NaN     54334    397547
2              Target  NaN    324546   1089978
3    Human     Booked 2345      1234     12345
4           Delivered 2000      1234     54334
5              Target 3000      2000    324546
6    Total     Booked 2345     13579    679035
7           Delivered 2000     55568    451881
8              Target 3000    326546   1414524

  Category     Metric      HR  Security   Software
0     Engg     Booked     NaN  34564534  444410468
1           Delivered     NaN  65345654   67691308
2              Target     NaN 643563452  653563451
3    Human     Booked 1234556     23456  345645345
4           Delivered  234543     34568   65345654
5              Target 6432212    234567  643563452
6    Total     Booked 1234556  34587990  790055813
7           Delivered  234543  65380222  133036962
8              Target 6432212 643798019 1297126903
user_118659560
Related