Calculating a summary row in a pandas dataframe

Question

Here's what I have (Pandas DataFrame), that I created so far: Code:

table = pd.pivot_table(df1, index=['Assignee', 'IssueType'], columns=['Status'], values='Key', aggfunc={'Key': np.count_nonzero}, dropna=True)
table['Total'] = table.sum(axis=1)
table = table.fillna(0)
table = table.apply(pd.to_numeric, errors='ignore')
table = table.astype(int)
table.to_csv(output_file_path, sep=delimiter)

Output:

Assignee~IssueType~Analysis~Blocked~Closed~Done~In Progress~Open~Ready For QA Testing~Total
Smith, John~Story~0~0~0~0~0~1~0~1
Smith, John~Sub-task~0~0~0~0~0~1~0~1
Smith, John~Task~0~0~0~0~2~5~0~7
Doe, Jane~Bug~0~0~0~0~1~0~0~1
Polo, Marco~Bug~0~0~0~0~0~2~0~2
Polo, Marco~Story~0~0~1~0~0~0~0~1
Polo, Marco~Task~1~0~0~0~4~2~0~7

Here's what I would like to have (considering that I could have numeric/non-numeric columns:

Assignee~IssueType~Analysis~Blocked~Closed~Done~In Progress~Open~Ready For QA Testing~Total
    Smith, John~Story~0~0~0~0~0~1~0~1
    Smith, John~Sub-task~0~0~0~0~0~1~0~1
    Smith, John~Task~0~0~0~0~2~5~0~7
    Doe, Jane~Bug~0~0~0~0~1~0~0~1
    Polo, Marco~Bug~0~0~0~0~0~2~0~2
    Polo, Marco~Story~0~0~1~0~0~0~0~1
    Polo, Marco~Task~1~0~0~0~4~2~0~7
**GrandTotal~GrandTotal~1~0~1~0~7~11~0~20**

What would be the best/optimal way to achieve this using Pandas DataFrames? Appreciate your help in advance.

Pandas doesn't really have a summary row. Maybe you could do something like [this](https://stackoverflow.com/a/21755752). — Josh Friedlander, Feb 06 '19 at 09:16
Thanks Josh Friedlander and everyone else who reviewed this question. My apologies for not noticing that the header was meaningless and thank you for editing it. — Keshav Prabhu, Feb 07 '19 at 07:42

Keshav Prabhu · Accepted Answer · 2022-11-24T16:15:57.017

Here's my answer to this question. Perhaps there is a scope for improvement (but atleast it works to my contentment).

def append_summary_total(df_index, file_path, delimiter):
    file_path = os.path.abspath(file_path)
    delimiter = str(delimiter)
    df = pd.read_csv(file_path, sep=delimiter)
    sums = df.select_dtypes(np.number).sum().rename('Grand Total')
    df.loc['Grand Total'] = df.select_dtypes(np.number).sum()
    df = df.fillna("GrandTotal")
    df = df.set_index(df_index)
    df = df.apply(pd.to_numeric, errors='ignore')
    df = df.astype(int)
    df.to_csv(file_path, sep=delimiter)

Here's the output: Sample Output

Calculating a summary row in a pandas dataframe

1 Answers1