How to add an additional column to a existing pivot table and add group total to that column

Question

for sheet in prio_list:
        df_pivot_comp = pd.pivot_table(pd.read_excel(excel_file,sheet_name=sheet),\
        values=['COMPILER WARNING'], columns=None, index=['GROUP','DOMAIN'],\
        aggfunc='count',margins=True,margins_name='Total',fill_value=0)

To this code , output would be

enter image description here

I want the output like :

enter image description here

I tried :

group_count = df_pivot_comp.sum(level='GROUP')

But it's not working Please help me with this

Laurent · Answer 1 · 2023-05-31T17:16:16.987

With the following toy dataframe:

import pandas as pd

df = pd.DataFrame(
    {
        "A": ["foo", "foo", "foo", "foo", "foo", "bar", "bar", "bar", "bar"],
        "B": ["one", "two", "three", "two", "five", "six", "seven", "six", "seven"],
        "C": [1, 2, 2, 3, 3, 4, 5, 6, 7],
    }
)

print(df)
# Output

     A      B  C
0  foo    one  1
1  foo    two  2
2  foo  three  2
3  foo    two  3
4  foo   five  3
5  bar    six  4
6  bar  seven  5
7  bar    six  6
8  bar  seven  7

First, create a pivot table with Pandas pivot_table and use margins=True and margins_name="total" to sum rows and columns:

table = pd.pivot_table(
    df,
    values="C",
    index=["A", "B"],
    aggfunc=np.sum,
    margins=True,
    margins_name="total",
)

print(table)
# Output

              C
A     B        
bar   seven  12
      six    10
foo   five    3
      one     1
      three   2
      two     5
total        33

Then, using Pandas advanced indexing:

# Drop column "total" and sort index to avoid performance warning
table = table.sort_index()

# Calculate total for rows and add in a new column
rows_total = table.reset_index().groupby(["A"]).agg({"C": sum})
for idx in table.index.get_level_values(0).unique():  # bar, foo, total
    table.loc[(idx,), "total"] = rows_total.loc[idx].values[0]

# Sort index and format values
table = table.reindex(index=["bar", "foo", "total"], level=0).astype(int)

# Remove repeated values
for idx_0 in table.index.get_level_values(0).unique():  # bar, foo, total
    if idx_0 == "total":
        continue
    for idx_1 in table.loc[(idx_0,), :].index[1:]:
        table.loc[(idx_0, idx_1), "total"] = ""

Finally:

print(table)
# Output

              C total
A     B
bar   seven  12    22
      six    10      
foo   five    3    11
      one     1      
      three   2      
      two     5      
total        33    33

Thanks Laurent for the solution but this works if same repeated string like "two" is present in the table , How to remove the repeated values in rows other than two like this — Ravindra Reddy, May 16 '23 at 07:14
Hii Laurent , im unable to merge the empty cells in the above table , please help me with the code for that as im stuck with this from a long time — Ravindra Reddy, May 31 '23 at 05:57
Hi, I fear this is a job for Excel, not Pandas, sorry. Cheers. — Laurent, May 31 '23 at 17:23
i taught merge_cells or dropna would help in this , but whatever i try , im not able to remove the empty cells — Ravindra Reddy, Jun 01 '23 at 05:18
Hii Laurent , https://stackoverflow.com/questions/76331344/how-to-merge-empty-cells-in-a-pivot-table-using-python , please check on this and help me if you could — Ravindra Reddy, Jun 16 '23 at 06:41

How to add an additional column to a existing pivot table and add group total to that column

1 Answers1