Excel aggregation with Python pandas Part 1

motivation

It slows down when the calculation formula is messed up in the sheet to aggregate the Excel information. I've come to see books related to Python + Excel at bookstores, so let's study.

environment

windows10 python3.9 VS Code Pandas openpyxl

Preparation

Assuming that python and pandas have been installed (using pip)

theme

There is such Excel. As a feature, the columns of departments differ depending on the seat. (Yellow column) I want to count the number of people in each department.

sheet1	sheet2	sheet3

Answer immediately

2 function definition in pandas_lib.py

countByKeyFromFileAndSheet: Function that aggregates key columns: Aggregate using value_counts () of pandas function and convert to dict type with to_dict ().
concatenateDict: A function that connects two dicts

`pandas_lib.py`


import pandas as pd #pandas is a library that provides functions to support data analysis in Python.
import numpy as np

#Group by key and count
def countByKeyFromFileAndSheet(filename, sheetname, key):
  df = pd.read_excel(filename, sheet_name=sheetname, engine="openpyxl")
  dict = df[key].value_counts().to_dict()
  print(dict)
  return dict

def concatenateDict(dict1, dict2):
  newdict=dict1
  for k in dict2:
    if k in newdict:
      newdict[k] += dict2[k]
    else:
      #newdict.update(k, dict2[k])
      newdict[k] = dict2[k]
  return newdict

pandas_main.py Use countByKeyFromFileAndSheet to aggregate the columns of department names of Sheet1 to Sheet3, store them in dict, and connect dicts with concatenateDict to generate dict.

`pandas_main.py`


import pandas_lib as pl

dict1 = pl.countByKeyFromFileAndSheet("./example.xlsx", "Sheet1", "Department")
dict2 = pl.countByKeyFromFileAndSheet("./example.xlsx", "Sheet2", "Department")
dict3 = pl.countByKeyFromFileAndSheet("./example.xlsx", "Sheet3", "Department")

dict = pl.concatenateDict(dict1, dict2)
dict = pl.concatenateDict(dict, dict3)

print(dict)

Execution result

After totaling to dict1-3, you can see that the total of 3 sheets is finally totaled.

{'Sales department': 4, 'Development department': 3, 'General Affairs Department': 3}
{'Sales department': 5, 'Development department': 3, 'Accounting department': 2}
{'Development department': 9, 'Accounting department': 1}
{'Sales department': 9, 'Development department': 15, 'General Affairs Department': 3, 'Accounting department': 3}

Finally

I implemented it muddy using the for statement in concatenateDict, but if there is another smarter implementation method, please let me know.