Creating a Month End spreadsheet

user_13840936 0
Viewed 44

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.

pandas concatenation merge python
3 Answers

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)
user_29010020

Assuming you have three dataframes (df1, df2,df3), you can simply use the add function along columns:

df_sum=df1.add(df2)
df_sum=df_sum.add(df3)
print(df_sum)
Nominal
1118  2263.23
1135  3267.06
1158  2271.84

Hopefully, this can help you: import pandas as pd

from pathlib import Path
path = Path("Spreadsheets")
df_sum=pd.DataFrame(columns=['Nominal'],index=[1118,1135,1158],data=[0,0,0])
for file in path.glob("*.xlsx"):
   df = pd.read_excel(f"{file}")
   client_total = df.groupby(["Nominal"]).sum()["Amount"]
   print(client_total)
   df_sum=df_sum.add(client_total)
print(df_sum)
user_86808100

Would this work?

import pandas as pd
from pathlib import Path

path = Path("Spreadsheets")
dfs = []

for file in path.glob("*.xlsx"):
    df = pd.read_excel(f"{file}")
    client_total = df.groupby(["Nominal"]).sum()["Amount"]
    dfs.append(client_total)

df = dfs[0]
for df in dfs[1:]:
    df = df.add(df)

print(df)
user_135524700
Related