CODEWITHSUNDEEP
pythonpython-3.xpandas
Hiro
Hiro

Reputation: 183

Python : How do I easily get sub-totals on row and column?

I am new to Python and am pulling my hair out trying to get Pandas/XlsWriter to do what i want.

I started with a set of data from a CSV which i loaded into a Dataframe and manipulated. Eventually, after doing a PIVOT, I ended up with the data as seen below. The PINK highlighted represents what I am trying to get Pandas/XLSwriter to produce programatically. so the number of rows and columns will be variable.

Two questions :

1) Please advise how I can find subtotals per row and column ?

2) How do I apply number formatting to get it to be xxx,xxx,xxx.00 ?

BONUS QUESTION :

Would you recommend OpenPYXL over XLSWriter (or vice versa) and why ?

enter image description here

Upvotes: 1

Views: 1545

Answers (2)

jack6e
jack6e

Reputation: 1522

This is mostly a duplicate question: Pandas: sum DataFrame rows for given columns

You are looking for df.sum() per the docs: https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.sum.html

For your specific case it would look like:

# New column with row totals
df['Daily Total'] = df.sum(axis=1, numeric_only=True)
# New row with column totals
df = df.append(df.sum(numeric_only=True), ignore_index=True)

Formatting is just:

df[col] = df[col].map('{:,.2f}'.format)

Or to format everything that is not the date/counterparty column, in case that is not the index, you can do:

df = df.apply(lambda col: col.map('{:,.2f}'.format) if col.name != 'Counterparty' else col)

Upvotes: 1

Yuval Raz
Yuval Raz

Reputation: 116

df["Daily Total"] = df["Company A"] + df["Company B"] + df["Company C"]+ df["Company D"]

Upvotes: 0

Related Questions