Excel aggregation with Python pandas Part 2 Variadic

motivation

This is a continuation of Excel Aggregation with Python pandas Part 1. The place where the function that connects dict is called twice is awkward. I want to make it one function. One solution is to make it variable.

environment

windows10 python3.9 VS Code Pandas openpyxl

Preparation

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

Answer immediately

2 function definition in pandas_lib.py

Added function concatenateDicts that can have multiple arguments of dict type.

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()
  return dict

def concatenateDicts(*dicts):
  newdict = {} # empty dict

  for dict in dicts:
    print(dict)
    for k in dict:
        if k in newdict: # key detected in newdict
          newdict[k] += dict[k]
        else: # not detected
          newdict[k] = dict[k]  
  return newdict

pandas_main.py I was able to call concatenateDicts once instead of calling concatenateDict twice.

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.concatenateDicts(dict1, dict2, dict3)

print(dict)

Execution result

Of course, the result is the same.

{'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}

Recommended Posts

Excel aggregation with Python pandas Part 2 Variadic
Excel aggregation with Python pandas Part 1
Excel with Python
[Easy Python] Reading Excel files with pandas
Handle Excel with python
Operate Excel with Python (1)
Operate Excel with Python (2)
[Python] How to read excel file with pandas
Image processing with Python (Part 2)
Operate Excel with Python openpyxl
Read csv with python pandas
Bordering images with python Part 1
Python application: Pandas Part 1: Basic
Python application: Pandas Part 2: Series
Scraping with Selenium + Python Part 1
Let's run Excel with Python
Studying Python with freeCodeCamp part2
[Python] Change dtype with pandas
Image processing with Python (Part 1)
Solving Sudoku with Python (Part 2)
Image processing with Python (Part 3)
Scraping with Selenium + Python Part 2
How to read an Excel file (.xlsx) with Pandas [Python]
solver> Link> Solve Excel Solver with python
Playing handwritten numbers with python Part 1
[Automation with python! ] Part 1: Setting file
Create an Excel file with Python3
Let's play with Excel with Python [Beginner]
[Python] Join two tables with pandas
Handle Excel CSV files with Python
1. Statistics learned with Python 1-1. Basic statistics (Pandas)
Automate simple tasks with Python Part0
[Automation with python! ] Part 2: File operation
[Python] Format when to_csv with pandas
Play handwritten numbers with python Part 2 (identify)
FM modulation and demodulation with Python Part 3
Process Pubmed .xml data with python [Part 2]
Quickly create an excel file with Python #python
Convert Excel data to JSON with python
100 Language Processing Knock with Python (Chapter 2, Part 2)
[Python] Display list elements with variadic arguments
Working with Azure CosmosDB from Python Part.2
Python application: Pandas Part 4: DataFrame concatenation / combination
[Easy Python] Reading Excel files with openpyxl
100 Language Processing Knock with Python (Chapter 2, Part 1)
Create Excel file with Python + similarity matrix
FM modulation and demodulation with Python Part 2
[Part1] Scraping with Python → Organize to csv!
Excel table creation with Python [Progress management table]
Convenient time series aggregation with TimeGrouper in pandas
QGIS + Python Part 2
FizzBuzz with Python3
Scraping with Python
Machine learning starting with Python Personal memorandum Part2
Create test data like that with Python (Part 1)
My pandas (python)
Automatic operation of Chrome with Python + Selenium + pandas
Scraping with Python
Read Python csv data with Pandas ⇒ Graph with Matplotlib
Python with Go
QGIS + Python Part 1