Data wrangling with Excel and Pandas is actually quite useful tool in the belt of any Excel professional, financial professional, data analyst or a developer. Really, everyonecan benefit from the well defined libraries that ease people’s lifes. These are the libraries used:
|
1 2 3 4 5 |
import pandas as pd # Main data manipulation from openpyxl import Workbook # Excel writing from openpyxl.styles import Font # Excel formatting (bold, colors) import glob # File path handling from datetime import datetime |
Additionally, a function for making a unique Excel name is used:
|
1 2 3 |
def make_unique_name(): timestamp = datetime.now().strftime('%Y%m%d_%H%M%S') return f'{timestamp}__report.xlsx' |
An example of the video, where Jupyter Notebook is used.
In the YT video below, the following 8 points are discussed:
# Trick 1 – Simple reading of worksheet from Excel workbook
|
1 2 3 4 5 |
excel_file_name = "financial_data.xlsx" df = pd.read_excel(excel_file_name, sheet_name = "Transactions", parse_dates = ["Date"], dtype={"InvoiceID":str}) |
# Trick 2 – Combine Reports
|
1 2 3 4 5 6 7 |
income = pd.read_excel(excel_file_name, sheet_name="Income") expenses = pd.read_excel(excel_file_name, sheet_name="Expenses") combined = pd.concat([ income.assign(From_Worksheet="Income"), expenses.assign(From_Worksheet="Expenses") ]) |
# Trick 3 – Fix Missing Values
|
1 |
combined["Amount"] = combined["Amount"].fillna(combined["Amount"].mean()) |
# Trick 4 – Formatting the exported Excel file
|
1 2 3 4 5 6 7 8 9 |
with pd.ExcelWriter(new_worksheet, engine="openpyxl") as writer: combined.to_excel(writer, index=False) workbook = writer.book worksheet=writer.sheets["Sheet1"] for cell in worksheet["1:1"]: cell.font = Font(bold=True) cell.font = Font(color="FFFF22") |
# Trick 5 – Merging Excel Files
|
1 2 |
files = glob.glob("sales12/sales_*.xlsx") annual_data = pd.concat([pd.read_excel(f) for f in files]) |
# Trick 6 – Smart Filtering
|
1 2 3 4 5 6 |
web_design_only = annual_data[ (annual_data["Description"]=="Web Design" )] small_transactions = annual_data[ (annual_data["Amount"] < 200 )] |
# Trick 7 – Mergining Tables
|
1 2 3 4 5 6 7 8 9 10 11 |
df_transactions = pd.read_excel( excel_file_name, sheet_name="Transactions") df_customers = pd.read_excel( excel_file_name, sheet_name="Customers") merged = pd.merge( df_transactions, df_customers, on = "CustomerID" ) |
# Trick 8 – Export Dataframe to Excel
|
1 2 |
with pd.ExcelWriter(new_worksheet, engine="openpyxl") as writer: merged.to_excel(writer) |
The whole code with the Excel files is available in GitHub here.
Enjoy it!