import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import matplotlib.patches as mpatches

#Used to display the cpi of food items over the years from 2019 to 2024


df = pd.read_csv("cpi_food.csv")
df['DataSeries'] = df['DataSeries'].str.strip()
food_items = ['Food', 'Meat', 'Vegetables', 'Fish & Seafood', 'Fruits', 'Rice']
df_filtered = df[df['DataSeries'].isin(food_items)]

columns_to_keep = ['DataSeries', '2019Jan', '2020Jan', '2021Jan', '2022Jan', '2023Jan', '2024Jan']

# Make an explicit copy here
df_result = df_filtered[columns_to_keep].copy()

# Now rename columns safely on this copy
df_result.rename(columns=lambda x: x.replace('Jan','') if 'Jan' in x else x, inplace=True)

# Set index and transpose
df_transposed = df_result.set_index('DataSeries').T


print(df_transposed.to_string(float_format='{:7.2f}'.format))

DataSeries    Food    Rice    Meat  Fish & Seafood  Fruits  Vegetables
2019         99.45   97.88   99.55          102.71   98.91       98.36
2020        101.10   98.66  102.55          106.83  101.74      102.42
2021        102.58   99.53  105.12          102.28  101.71      109.90
2022        105.20   96.61  107.62          109.03  106.92      116.28
2023        113.74   97.57  123.74          114.86  110.45      120.31
2024        117.53  101.80  124.77          114.81  113.35      119.77

# Load and clean data
df = pd.read_csv("cpi_food.csv")
df['DataSeries'] = df['DataSeries'].str.strip()

# Filter only the desired food categories
food_items = ['Food', 'Meat', 'Vegetables', 'Fish & Seafood', 'Fruits', 'Rice']
columns_to_keep = ['DataSeries', '2019Jan', '2020Jan', '2021Jan', '2022Jan', '2023Jan', '2024Jan']
df_filtered = df[df['DataSeries'].isin(food_items)][columns_to_keep]

# Melt the DataFrame to long format for plotting
df_melted = df_filtered.melt(id_vars='DataSeries', var_name='YearMonth', value_name='CPI')

# Convert YearMonth to datetime format for better plotting
df_melted['YearMonth'] = pd.to_datetime(df_melted['YearMonth'], format='%Y%b')

# Set up subplots: 6 food categories = 2 rows x 3 columns
fig, axs = plt.subplots(2, 3, figsize=(18, 8))
axs = axs.flatten()

# Plot each food item in its own subplot
for i, item in enumerate(food_items):
    data = df_melted[df_melted['DataSeries'] == item]
    axs[i].plot(data['YearMonth'], data['CPI'], marker='o', color='teal', linewidth=2)
    axs[i].set_title(item, fontsize=12)
    axs[i].set_xlabel('Year')
    axs[i].set_ylabel('CPI')
    axs[i].grid(True, linestyle='--', alpha=0.5)

    # Add annotation for Rice CPI drop (2021 to 2022)
    if item == 'Rice':
        year_2021 = pd.to_datetime('2021Jan', format='%Y%b')
        year_2022 = pd.to_datetime('2022Jan', format='%Y%b')
        cpi_2021 = data[data['YearMonth'] == year_2021]['CPI'].values[0]
        cpi_2022 = data[data['YearMonth'] == year_2022]['CPI'].values[0]
        axs[i].annotate('Significant Drop',
                        xy=(year_2022, cpi_2022),
                        xytext=(year_2021, cpi_2021 + 2),
                        arrowprops=dict(facecolor='red', shrink=0.05, width=2),
                        fontsize=10, color='red')

    # Add annotation for Seafood CPI drop (2020 to 2021)
    if item == 'Fish & Seafood':
        year_2020 = pd.to_datetime('2020Jan', format='%Y%b')
        year_2021 = pd.to_datetime('2021Jan', format='%Y%b')
        cpi_2020 = data[data['YearMonth'] == year_2020]['CPI'].values[0]
        cpi_2021 = data[data['YearMonth'] == year_2021]['CPI'].values[0]
        axs[i].annotate('Significant Drop',
                        xy=(year_2021, cpi_2021),
                        xytext=(year_2020, cpi_2020 + 2),
                        arrowprops=dict(facecolor='red', shrink=0.05, width=2),
                        fontsize=10, color='red')

# Add main title
fig.suptitle('CPI Trends by Food Category in Singapore (2019-2024)', fontsize=16)
plt.tight_layout(rect=[0, 0, 1, 0.95])  # Leave space for the main title
plt.show()

# Read and prepare the data
df = pd.read_csv("cpi_food.csv")
df['DataSeries'] = df['DataSeries'].str.strip()

# Filter for specific food items
food_items = ['Food', 'Meat', 'Vegetables', 'Fish & Seafood', 'Fruits', 'Rice']
df_filtered = df[df['DataSeries'].isin(food_items)]

# Keep only necessary columns
columns_to_keep = ['DataSeries', '2019Jan', '2020Jan', '2021Jan', '2022Jan', '2023Jan', '2024Jan']
df_result = df_filtered[columns_to_keep]

# Transpose for plotting
df_plot = df_result.set_index('DataSeries').T

# Set y-axis range
y_min = 90
y_max = df_plot.max().max() + 5

# Plotting
fig, axes = plt.subplots(2, 3, figsize=(15, 8))
axes = axes.flatten()

for i, year in enumerate(df_plot.index):
    ax = axes[i]
    values = df_plot.loc[year]
    max_idx = values.idxmax()
    
    colors = ['red' if item == max_idx else 'skyblue' for item in df_plot.columns]
    
    bars = ax.bar(df_plot.columns, values, color=colors)
    ax.set_title(year)
    ax.set_ylabel("CPI")
    ax.set_ylim(y_min, y_max)
    ax.set_xticks(range(len(df_plot.columns)))
    ax.set_xticklabels(df_plot.columns, rotation=45, ha='right')

# Add legend
red_patch = mpatches.Patch(color='red', label='Highest CPI')
blue_patch = mpatches.Patch(color='skyblue', label='Other Categories')
fig.legend(handles=[red_patch, blue_patch], loc='upper right')

fig.suptitle("CPI of Food Categories (2019-2024)", fontsize=16)
plt.tight_layout(rect=[0, 0, 1, 0.95])
plt.show()

#Used to display the percentage change in cpi of food items over the years from 2019 to 2024
df = pd.read_csv("cpi_change.csv")
df['DataSeries'] = df['DataSeries'].str.strip()
food_items = ['Food', 'Meat', 'Vegetables', 'Fish & Seafood', 'Fruits', 'Rice']
df_filtered = df[df['DataSeries'].isin(food_items)]
columns_to_keep = ['DataSeries', '20191H', '20201H', '20211H', '20221H', '20231H', '20241H']
df_result = df_filtered[columns_to_keep]
print(df_result.to_string(index=False))

    DataSeries 20191H  20201H  20211H  20221H  20231H  20241H
          Food    0.9     1.3     0.7     2.7     3.1     1.9
          Rice    1.7     1.8    -0.6    -1.4     0.8    -0.4
          Meat   -0.3     5.0    -0.2     4.3     2.0     0.0
Fish & Seafood    1.5     1.1     0.6     5.1     6.4    -0.9
        Fruits    0.6     0.8     0.8    -0.4     0.6    -0.6
    Vegetables    1.9     3.8     3.4     3.0     1.4     2.3

# Load CSV
df = pd.read_csv("cpi_change.csv")

# Clean headers
df.columns = df.columns.str.strip()
df['DataSeries'] = df['DataSeries'].str.strip()

# Filter relevant food categories
food_items = ['Food', 'Meat', 'Vegetables', 'Fish & Seafood', 'Fruits', 'Rice']
columns_to_keep = ['DataSeries', '20191H', '20201H', '20211H', '20221H', '20231H', '20241H']
df_filtered = df[df['DataSeries'].isin(food_items)][columns_to_keep]

# Transpose
df_plot = df_filtered.set_index('DataSeries').T
df_plot.index = df_plot.index.astype(str)
df_plot = df_plot.apply(pd.to_numeric, errors='coerce')

# Plot
plt.figure(figsize=(10, 6))
for col in df_plot.columns:
    plt.plot(df_plot.index, df_plot[col], marker='o', label=col)

plt.title("CPI % Change (1H) for Food Categories (2019-2024)")
plt.xlabel("Year (First Half)")
plt.ylabel("CPI % Change")
plt.xticks(rotation=45)
plt.grid(True)
plt.legend(title="Food Category")

# Place note inside the plot (top-left)
plt.text(0.01, 0.97,
         "Note: Values on vertical axis above 0 indicate an increase in CPI,\nwhile values below 0 indicate a decrease in CPI.",
         transform=plt.gca().transAxes,
         fontsize=9,
         verticalalignment='top',
         bbox=dict(facecolor='white', edgecolor='gray', boxstyle='round,pad=0.4'))

plt.tight_layout()
plt.show()

#Used to display the value of local production in millions from 2019 to 2024


# Load CSV with pandas
df = pd.read_csv('local_production.csv')

# Extract rows based on label in first column
veg_row = df[df.iloc[:, 0].str.contains("Vegetables", na=False)].iloc[0, 1:7].astype(int).values
seafood_row = df[df.iloc[:, 0].str.contains("Seafood", na=False)].iloc[0, 1:7].astype(int).values
eggs_row = df[df.iloc[:, 0].str.contains("Hen Shell Eggs", na=False)].iloc[0, 1:7].astype(int).values
total_row = df[df.iloc[:, 0].str.contains("Total Value Of Local Production", na=False)].iloc[0, 1:7].astype(int).values
# Convert years manually (assuming the columns are in reverse order)
years = np.array([2024, 2023, 2022, 2021, 2020, 2019])
vegetables_value = np.array(veg_row)
seafood_value = np.array(seafood_row)
hen_shell_eggs_value = np.array(eggs_row)
total = np.array(total_row)


# Display using numpy-style formatting
print(f"{'Year':<6}{'Total value':<14}{'Vegetables':<14}{'Seafood':<14}{'Hen Shell Eggs':<18}{'Unit'}")
for i in range(len(years)):
    print(f"{years[i]:<6}{total[i]:<14}{vegetables_value[i]:<14}{seafood_value[i]:<14}{hen_shell_eggs_value[i]:<18}million dolllars")

Year  Total value   Vegetables    Seafood       Hen Shell Eggs    Unit
2024  231           40            30            161               million dolllars
2023  233           43            34            156               million dolllars
2022  207           43            40            124               million dolllars
2021  186           47            41            99                million dolllars
2020  163           40            35            89                million dolllars
2019  166           42            46            79                million dolllars

# Load raw CSV data
df_raw = pd.read_csv("local_production.csv", header=None)

# Extract and clean data
df_data = df_raw.iloc[9:].reset_index(drop=True)
df_data.columns = df_data.iloc[0]
df_data = df_data.drop(index=0).reset_index(drop=True)
df_data.columns = df_data.columns.astype(str).str.strip()
df_data = df_data.rename(columns={df_data.columns[0]: "Data Series"})
df_data["Data Series"] = df_data["Data Series"].str.strip()

# Filter required data series
keywords = [
    "Total Value Of Local Production (Million Dollars)",
    "Vegetables (Million Dollars)",
    "Seafood (Million Dollars)",
    "Hen Shell Eggs (Million Dollars)"
]
filtered_data = df_data[df_data["Data Series"].isin(keywords)]

# Extract data for plotting
years = [str(year) for year in range(2019, 2025)]

# Convert to numeric and create series
total_value = filtered_data[filtered_data["Data Series"] == "Total Value Of Local Production (Million Dollars)"][years].values.flatten().astype(float)
vegetables = filtered_data[filtered_data["Data Series"] == "Vegetables (Million Dollars)"][years].values.flatten().astype(float)
seafood = filtered_data[filtered_data["Data Series"] == "Seafood (Million Dollars)"][years].values.flatten().astype(float)
hen_eggs = filtered_data[filtered_data["Data Series"] == "Hen Shell Eggs (Million Dollars)"][years].values.flatten().astype(float)
years_int = list(map(int, years))

# Plotting
plt.figure(figsize=(10, 6))
plt.plot(years_int, total_value, marker='o', label='Total Value')
plt.plot(years_int, vegetables, marker='o', label='Vegetables')
plt.plot(years_int, seafood, marker='o', label='Seafood')
plt.plot(years_int, hen_eggs, marker='o', label='Hen Shell Eggs')

# Annotations
plt.axvline(x=2019, color='red', linestyle='--', linewidth=1.5, label='Beginning of COVID')
plt.axvline(x=2022, color='blue', linestyle='--', linewidth=1.5, label='Significant seafood production drop')

# Formatting
plt.title('Local Food Production in Singapore (201-2024)', fontsize=14)
plt.xlabel('Year', fontsize=12)
plt.ylabel('Value (in millions SGD)', fontsize=12)
plt.grid(True, linestyle='--', alpha=0.5)
plt.legend()
plt.tight_layout()
plt.show()

#Used to display the amount of local production in tonnes/million pieces from 2019 to 2024
#Million pieces count individual items, while tonnes measure weight; for example, 1 million eggs roughly equals 60 tonnes, so converting million pieces to tonnes allows for a consistent comparison of total production by weight.
#For eggs: 1 million eggs ≈ 60 tonnes , average egg ≈ 60g (This conversion is required as the eggs were in million pieces while the rest were in tonnes making it difficult to compare)

# Step 1: Load and clean data
df = pd.read_csv('local_production.csv')

# Step 2: Extract rows using keyword match
veg_row = df[df.iloc[:, 0].str.contains("Local Production Of Vegetables", na=False)].iloc[0, 1:7].astype(int).values
seafood_row = df[df.iloc[:, 0].str.contains("Local Production Of Seafood", na=False)].iloc[0, 1:7].astype(int).values
eggs_million = df[df.iloc[:, 0].str.contains("Local Production Of Hen Shell Eggs", na=False)].iloc[0, 1:7].astype(int).values

# Step 3: Convert eggs from million pieces to tonnes
eggs_tonnes = eggs_million * 60

# Step 4: Prepare arrays
years = np.array([2024, 2023, 2022, 2021, 2020, 2019])
vegetables = np.array(veg_row)
seafood = np.array(seafood_row)
eggs = np.array(eggs_tonnes)

# Step 5: Calculate total in tonnes
total = vegetables + seafood + eggs

# Step 6: Display
print(f"{'Year':<6}{'Total':<10}{'Vegetables':<14}{'Seafood':<14}{'Hen Shell Eggs':<18}{'Unit'}")
for i in range(len(years)):
    print(f"{years[i]:<6}{total[i]:<10}{vegetables[i]:<14}{seafood[i]:<14}{eggs[i]:<18}tonnes")

Year  Total     Vegetables    Seafood       Hen Shell Eggs    Unit
2024  66304     16391         3533          46380             tonnes
2023  62105     16915         4090          41100             tonnes
2022  60861     19881         4440          36540             tonnes
2021  67215     23506         5069          38640             tonnes
2020  64320     22793         4567          36960             tonnes
2019  61311     24296         5335          31680             tonnes

CA1 Assignment - "What is the government doing to mitigate the issue of food price inflation in Singapore?"¶

Introduction¶

Aims¶

CPI definition¶

Explanation of what each dataset represents¶

Sub question 1: How have food prices changed over time across different categories?¶

The dataset above is the filtered data from cpi_food, it displays the cpi of food items from 2019 to 2024¶

Analysis of the visual above (CPI trends by food category across the years)¶

Analysis of the visual above ( CPI graph for various food catogaries each year )¶

The dataset above is the filtered data from cpi_change, it displays the % cpi change in food items from 2019 to 2024¶

Analysis of the visual above ( CPI % Change graph )¶

Overall Food Price Trends¶

Category-Specific Insights¶

Overall Conclusion for Sub question 1¶

Sub question 2: "How has local food production changed over time, and is it aligned with efforts to reduce reliance on imports?"¶

The dataset above is the filtered data from local_production, it displays the value of local production in millions from 2019 to 2024.¶

Analysis of the visual above (Local food production trends in singapore over the years)¶

The dataset above is the filtered data from local_production, it displays the amount of local production in tonnes from 2019 to 2024.¶

How far are we from the 30 by 30 goal?¶

Overall Conclusion for Sub question 2¶

SUMMARY¶

REFERENCES¶