Working with CSV, Excel, and JSON Data

import pandas as pd

# Problem 1: The separator isn't a comma, it's a semicolon (common in Europe)
# Solution: Use the 'sep' parameter
df = pd.read_csv('data.csv', sep=';')

# Problem 2: The CSV has no header row, so Pandas accidentally makes the first row of data the header!
# Solution: Tell Pandas there is no header, and provide the names manually
df = pd.read_csv('data.csv', header=None, names=['ID', 'Name', 'Age'])

# Problem 3: You only want 3 specific columns out of a 100-column file to save RAM.
# Solution: Use 'usecols'
df = pd.read_csv('data.csv', usecols=['Name', 'Salary'])

# Problem 4: Automatically converting string dates into proper DateTime objects!
df = pd.read_csv('data.csv', parse_dates=['Date_Joined'])

# By default, this reads the FIRST sheet only
df_excel = pd.read_excel('financials.xlsx')

# To read a specific sheet by name
df_q2 = pd.read_excel('financials.xlsx', sheet_name='Q2_Report')

# To read a specific sheet by index (the second sheet)
df_sheet2 = pd.read_excel('financials.xlsx', sheet_name=1)

# Reading a JSON file directly into a DataFrame
df_json = pd.read_json('users.json')

# Often, JSON is nested (dictionaries inside dictionaries). 
# Pandas has a utility to "flatten" it out.
import json
from pandas.json_normalize import json_normalize

raw_json = [
    {"id": 1, "name": "Alice", "address": {"city": "NY", "zip": "10001"}},
    {"id": 2, "name": "Bob", "address": {"city": "LA", "zip": "90001"}}
]

# Flattens 'address' into 'address.city' and 'address.zip' columns!
df_flat = json_normalize(raw_json)
print(df_flat)

# 1. Export to CSV
# index=False prevents Pandas from writing the 0,1,2 index numbers as a column
df.to_csv('cleaned_data.csv', index=False)

# 2. Export to Excel
df.to_excel('final_report.xlsx', sheet_name='Summary', index=False)

# 3. Export to JSON (records orientation is best for APIs)
df.to_json('data_export.json', orient='records')

# 1. Ingest Data
# (Assuming these files exist)
# customers = pd.read_csv('customers.csv')
# sales = pd.read_excel('sales_2023.xlsx', sheet_name='Dec')

# Simulate the data for this example
customers = pd.DataFrame({"ID": [1, 2], "Name": ["Alice", "Bob"]})
sales = pd.DataFrame({"Cust_ID": [1, 2], "Amount": [500, 300]})

# 2. Merge Data (Like SQL JOIN)
report = pd.merge(sales, customers, left_on='Cust_ID', right_on='ID')

# 3. Export to a clean CSV for the boss
report.to_csv('December_Sales_Report.csv', index=False)
print("Report successfully generated and exported!")