Creating a Month End spreadsheet

Question

Probably a simple answer but I am new to coding and this is my first project.

I have managed to sum together the necessary information from individual spreadsheets and would now like to write an 'End of Month' spreadsheet to sum all individual data.

heres what i have so far..

import pandas as pd


from pathlib import Path
path = Path("Spreadsheets")
for file in path.glob("*.xlsx"):
    df = pd.read_excel(f"{file}")
    client_total = df.groupby(["Nominal"]).sum()["Amount"]
    print(client_total)

This returns

Nominal
1118     379
1135    2367
1158     811
Name: Amount, dtype: int64
Nominal
1118    1147.85
1135     422.66
1158     990.68
Name: Amount, dtype: float64
Nominal
1118    736.38
1135    477.40
1158    470.16
Name: Amount, dtype: float64

Please let me know how I can merge these three separate results into one easy to read month total.

Many thanks.

Answer 1

Create list of Series called out and then use concat with sum by index by sum(level=0):

out = []
from pathlib import Path
path = Path("Spreadsheets")
for file in path.glob("*.xlsx"):
    df = pd.read_excel(f"{file}")
    client_total = df.groupby(["Nominal"])["Amount"].sum()
    out.append(client_total)

df = pd.concat(out).sum(level=0)
print(df)