Q7: Data Loading, Storage and File Formats Problem Statement: Analyzing Sales Data from Multiple File Formats Dataset: Sales data in multiple file formats (e.g., CSV, Excel, JSON) Description: The goal is to load and analyze sales data from different file formats, including CSV, Excel, and JSON, and perform data cleaning, transformation, and analysis on the dataset. Tasks to Perform: Obtain sales data files in various formats, such as CSV, Excel, and JSON. 1. Load the sales data from each file format into the appropriate data structures or dataframes. 2. Explore the structure and content of the loaded data, identifying any inconsistencies, missing values, or data quality issues. 3. Perform data cleaning operations, such as handling missing values, removing duplicates, or correcting inconsistencies. 4. Convert the data into a unified format, such as a common dataframe or data structure, to enable seamless analysis. 5. Perform data transformation tasks, such as merging multiple datasets, splitting columns, or deriving new variables. 6. Analyze the sales data by performing descriptive statistics, aggregating data by specific variables, or calculating metrics such as total sales, average order value, or product category distribution. 7. Create visualizations, such as bar plots, pie charts, or box plots, to represent the sales data and gain insights into sales trends, customer behavior, or product performance.

# Load each file format separately. import pandas as pd import matplotlib.pyplot as plt csv_sales = pd.read_csv("sales_data.csv") excel_sales = pd.read_excel("sales_data.xlsx") json_sales = pd.read_json("sales_data.json") for name, frame in [("CSV", csv_sales), ("Excel", excel_sales), ("JSON", json_sales)]: print(name, frame.shape, frame.columns.tolist()) print(frame.head(2).to_string(index=False), "\n")

# Inspect missing data and repeated order IDs before cleaning. sales = pd.concat([csv_sales, excel_sales, json_sales], ignore_index=True) print("Rows before cleaning:", len(sales)) print("Missing values:\n", sales.isna().sum().to_string()) print("Repeated order IDs:", sales.duplicated(subset="OrderID").sum())

# Standardize fields, remove duplicate orders, and fill a missing quantity. sales["Region"] = sales["Region"].astype(str).str.strip().str.title() sales["Date"] = pd.to_datetime(sales["Date"], errors="coerce") sales["Quantity"] = pd.to_numeric(sales["Quantity"], errors="coerce") sales["UnitPrice"] = pd.to_numeric(sales["UnitPrice"], errors="coerce") sales = sales.drop_duplicates(subset="OrderID") sales["Quantity"] = sales["Quantity"].fillna(sales["Quantity"].median()) sales = sales.dropna(subset=["Date", "UnitPrice"]) print("Clean rows:", len(sales)) print("Remaining missing values:", int(sales.isna().sum().sum()))

# Derive sales metrics and a month for aggregation. sales["TotalSales"] = sales["Quantity"] * sales["UnitPrice"] sales["Month"] = sales["Date"].dt.to_period("M").astype(str) print("Total sales:", round(sales["TotalSales"].sum(), 2)) print("Average order value:", round(sales["TotalSales"].mean(), 2)) print("By category:\n", sales.groupby("Category")["TotalSales"].agg(["count", "sum", "mean"]).round(2).to_string())

# Compare regions, categories and monthly trends visually. fig, axes = plt.subplots(1, 3, figsize=(15, 4)) sales.groupby("Region")["TotalSales"].sum().plot.bar(ax=axes[0], title="Sales by region") sales.groupby("Category")["TotalSales"].sum().plot.pie(ax=axes[1], title="Category share", autopct="%1.0f%%") axes[1].set_ylabel("") sales.groupby("Month")["TotalSales"].sum().plot(ax=axes[2], marker="o", title="Monthly sales") axes[0].set_ylabel("Sales amount") axes[2].tick_params(axis="x", rotation=45) plt.tight_layout() plt.show()

Data Modeling & Visualization (dmv) Codes

Group Part II: Data Modeling and Visualization

Data Loading, Storage and File Formats

Q7: Data Loading, Storage and File Formats

Other Questions in Data Modeling & Visualization