Data aggregation and cleaning for financial datasets

📅 2023-09-28 — Session: Data aggregation and cleaning for financial datasets

🕒 16:00–16:35
🏷️ Labels: Data Aggregation, Python, Pandas, Data Cleaning, Csv Export
📂 Project: Dev
⭐ Priority: MEDIUM

Session Goal

The primary goal of this session was to plan and execute data aggregation and cleaning processes for multiple financial datasets, focusing on money-related columns and ensuring data consistency.

Key Activities

• Developed a structured plan for aggregating datasets by characteristics and year, considering unique value constraints.
• Identified key columns of interest for datasets df_wb, df_aiddata_china, and df_aiddata_wb.
• Implemented a Python function for data aggregation using pandas, addressing common DataFrame issues such as SettingWithCopyWarning and aggregation duplication.
• Created a loop to print money column values for data review, and provided code for parsing numeric columns by cleaning and converting string-formatted numbers.
• Developed a function to identify and handle duplicate entries in DataFrames, ensuring accurate data aggregation.
• Ensured consistent datetime formatting across DataFrames for further analysis.
• Exported aggregated data to CSV files for external review.
• Notified stakeholders, Eric and Raolin, about the availability of cross-section datasets for review.

Achievements

• Successfully aggregated and cleaned multiple datasets, addressing key data processing challenges.
• Prepared datasets for stakeholder review, facilitating further analysis and feedback.

Pending Tasks

• Await feedback from Eric and Raolin regarding the cross-section datasets to make any necessary adjustments.