Connection to DB

This is the connection link to my database on postgreSQL, the actual connection function is on the file db_connect.py

# Import necessary packages
import pandas as pd
from db_connect import connect_to_db

# Step 1: Connect to the database
conn = connect_to_db()

# Step 2: Create a cursor and run a query
cursor = conn.cursor()
query = "SELECT * FROM food_prices_cleaned.food_prices_kenya;"
cursor.execute(query)

# Step 3: Fetch results and convert to a DataFrame
rows = cursor.fetchall()
df = pd.DataFrame(rows, columns=[desc[0] for desc in cursor.description])

# Step 4: Display the data
print("Connection successful! Previewing data:")
display(df.head(25))

Database connection successful!
Connection successful! Previewing data:

Data Exploration with Python, tryna get to understand my data

df.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 41400 entries, 0 to 41399
Data columns (total 34 columns):
 #   Column                      Non-Null Count  Dtype  
---  ------                      --------------  -----  
 0   provinces                   41400 non-null  object 
 1   counties                    41400 non-null  object 
 2   mkt_name                    41400 non-null  object 
 3   latitude                    41400 non-null  float64
 4   longitude                   41400 non-null  float64
 5   geo_id                      41400 non-null  object 
 6   price_date                  41400 non-null  object 
 7   year                        41400 non-null  int64  
 8   month                       41400 non-null  int64  
 9   components                  41400 non-null  object 
 10  o_beans                     41400 non-null  float64
 11  h_beans                     41400 non-null  float64
 12  l_beans                     41400 non-null  float64
 13  c_beans                     41400 non-null  float64
 14  inflation_beans             39192 non-null  float64
 15  trust_beans                 41400 non-null  float64
 16  o_maize                     41400 non-null  float64
 17  h_maize                     41400 non-null  float64
 18  l_maize                     41400 non-null  float64
 19  c_maize                     41400 non-null  float64
 20  inflation_maize             39192 non-null  float64
 21  trust_maize                 41400 non-null  float64
 22  o_potatoes                  41400 non-null  float64
 23  h_potatoes                  41400 non-null  float64
 24  l_potatoes                  41400 non-null  float64
 25  c_potatoes                  41400 non-null  float64
 26  inflation_potatoes          39192 non-null  float64
 27  trust_potatoes              41400 non-null  float64
 28  o_food_price_index          41400 non-null  float64
 29  h_food_price_index          41400 non-null  float64
 30  l_food_price_index          41400 non-null  float64
 31  c_food_price_index          41400 non-null  float64
 32  inflation_food_price_index  39192 non-null  float64
 33  trust_food_price_index      41400 non-null  float64
dtypes: float64(26), int64(2), object(6)
memory usage: 10.7+ MB

df.describe()

Data Manipulation and Modification

Standardising potatoes data to price per KG

Divide potatoes price columns by 50 to standardize to price per 1kg I choose to do this on derived columns to avoid confusion, or incase i’ll need the original data in future.

df['o_potatoes_1kg'] = df['o_potatoes'] / 50
df['h_potatoes_1kg'] = df['h_potatoes'] / 50
df['l_potatoes_1kg'] = df['l_potatoes'] / 50
df['c_potatoes_1kg'] = df['c_potatoes'] / 50

confirm if the additional potatoes columns have been added

df.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 41400 entries, 0 to 41399
Data columns (total 38 columns):
 #   Column                      Non-Null Count  Dtype  
---  ------                      --------------  -----  
 0   provinces                   41400 non-null  object 
 1   counties                    41400 non-null  object 
 2   mkt_name                    41400 non-null  object 
 3   latitude                    41400 non-null  float64
 4   longitude                   41400 non-null  float64
 5   geo_id                      41400 non-null  object 
 6   price_date                  41400 non-null  object 
 7   year                        41400 non-null  int64  
 8   month                       41400 non-null  int64  
 9   components                  41400 non-null  object 
 10  o_beans                     41400 non-null  float64
 11  h_beans                     41400 non-null  float64
 12  l_beans                     41400 non-null  float64
 13  c_beans                     41400 non-null  float64
 14  inflation_beans             39192 non-null  float64
 15  trust_beans                 41400 non-null  float64
 16  o_maize                     41400 non-null  float64
 17  h_maize                     41400 non-null  float64
 18  l_maize                     41400 non-null  float64
 19  c_maize                     41400 non-null  float64
 20  inflation_maize             39192 non-null  float64
 21  trust_maize                 41400 non-null  float64
 22  o_potatoes                  41400 non-null  float64
 23  h_potatoes                  41400 non-null  float64
 24  l_potatoes                  41400 non-null  float64
 25  c_potatoes                  41400 non-null  float64
 26  inflation_potatoes          39192 non-null  float64
 27  trust_potatoes              41400 non-null  float64
 28  o_food_price_index          41400 non-null  float64
 29  h_food_price_index          41400 non-null  float64
 30  l_food_price_index          41400 non-null  float64
 31  c_food_price_index          41400 non-null  float64
 32  inflation_food_price_index  39192 non-null  float64
 33  trust_food_price_index      41400 non-null  float64
 34  o_potatoes_1kg              41400 non-null  float64
 35  h_potatoes_1kg              41400 non-null  float64
 36  l_potatoes_1kg              41400 non-null  float64
 37  c_potatoes_1kg              41400 non-null  float64
dtypes: float64(30), int64(2), object(6)
memory usage: 12.0+ MB

Renaming some columns for better understanding of what they represent

import pandas as pd

def rename_agric_columns(df):
    """
    Renames columns like o_beans, h_beans, c_maize, etc. 
    to a consistent format such as beans_open, maize_high, etc.
    """
    rename_map = {}
    prefix_map = {
        'o': 'open',
        'h': 'high',
        'l': 'low',
        'c': 'close'
    }

    # Iterate through existing columns
    for col in df.columns:
        # Check for trading-style prefixes (o_, h_, l_, c_)
        for prefix, new_prefix in prefix_map.items():
            if col.startswith(f"{prefix}_"):
                # Example: o_beans → beans_open
                rename_map[col] = f"{col.split('_', 1)[1]}_{new_prefix}"
                break
        # Handle inflation_* and trust_* as is
        if col.startswith("inflation_") or col.startswith("trust_"):
            rename_map[col] = col  # keep same (optional)
    
    # Apply renaming
    df = df.rename(columns=rename_map)
    return df

df = rename_agric_columns(df)

df.columns

Index(['provinces', 'counties', 'mkt_name', 'latitude', 'longitude', 'geo_id',
       'price_date', 'year', 'month', 'components', 'beans_open', 'beans_high',
       'beans_low', 'beans_close', 'inflation_beans', 'trust_beans',
       'maize_open', 'maize_high', 'maize_low', 'maize_close',
       'inflation_maize', 'trust_maize', 'potatoes_open', 'potatoes_high',
       'potatoes_low', 'potatoes_close', 'inflation_potatoes',
       'trust_potatoes', 'food_price_index_open', 'food_price_index_high',
       'food_price_index_low', 'food_price_index_close',
       'inflation_food_price_index', 'trust_food_price_index',
       'potatoes_1kg_open', 'potatoes_1kg_high', 'potatoes_1kg_low',
       'potatoes_1kg_close'],
      dtype='object')

In this step i’m grouping the data by province, then ordering it by year: This keeps all rows but arranges them so that: All rows from the same province are grouped together Within each province, data appears in chronological order

df = df.sort_values(['provinces', 'year']).reset_index(drop=True)
df

VISUALS

Visualizing trends by Time(Yearly and Monthly)

General Trends and Overview

What are the overall trends in food prices (beans, maize, potatoes, and the food price index) across Kenya over the years (2007–2025)?

We want to see how prices have changed over time, for each commodity across all regions. That means we’ll probably focus on averages per year (national trend), not per province yet.

We’ll start by calculating the mean closing price for each commodity per year:

Yearly Trends for Food

yearly_trends = df.groupby('year')[['beans_close','maize_close','potatoes_1kg_close','food_price_index_close']].mean().reset_index()
yearly_trends

Overall Food Price Trends in Kenya by Year (2007–2025):

import matplotlib.pyplot as plt
import seaborn as sns

plt.figure(figsize=(12, 6))
sns.lineplot(data=yearly_trends, x='year', y='beans_close', label='Beans')
sns.lineplot(data=yearly_trends, x='year', y='maize_close', label='Maize')
sns.lineplot(data=yearly_trends, x='year', y='potatoes_1kg_close', label='Potatoes')
sns.lineplot(data=yearly_trends, x='year', y='food_price_index_close', label='Food_Price_Index')

plt.title('Overall Food Price Trends in Kenya (2007–2025)', fontsize=14)
plt.xlabel('Year')
plt.ylabel('Average Closing Price')
plt.legend(title='Commodity')
plt.grid(True, linestyle='--', alpha=0.5)
plt.tight_layout()
plt.show()

/home/kobey/anaconda3/envs/medium_article/lib/python3.10/site-packages/scipy/__init__.py:146: UserWarning: A NumPy version >=1.17.3 and <1.25.0 is required for this version of SciPy (detected version 1.26.4
  warnings.warn(f"A NumPy version >={np_minversion} and <{np_maxversion}"

overall foodprices in kenya(2007–2025)

Breakdown based on the Line chart above

1. Upward Trend Overall:

All commodities beans, maize, and potatoes show a gradual increase in average prices from 2007 through around 2023, followed by a slight decline toward 2025.
This suggests long-term inflationary pressure on food prices in Kenya.

2. Beans are consistently the most expensive:

The blue line(Beans) remains well above maize and potatoes throughout the period. This likely reflects both higher production costs and strong demand for beans as a protein source

3. Parallel movement between maize and potatoes

The Orange(maize) and green(Potatoes) line move roughly together, meaning price changes for one often coincide with the other This may reflect shared market influence like weather condition or fuel prices that affect all stable crops.

4. Food Price Index follows the same direction:

Even though its values are smaller in scale, the Food Price Index (red line) mirrors the general direction of the other commodities. It acts as a summary indicator of overall food inflation, showing peaks and troughs that align with the crops’ price changes.

5. Notable peaks (2022–2023):

There’s a sharp spike across all commodities around 2022–2023, likely due to global and local disruptions e.g., drought, COVID-19 aftereffects, global supply chain issues or elections. After this spike, prices dip slightly toward 2025, suggesting a partial recovery or stabilization.

6. Notable Dips

There are two visible dips, around 2010 and 2018, across most commodities.
These years coincide with major election periods in Kenya (the 2010 constitutional referendum and the 2017 general election).
Such events often influence food prices through market disruptions, political uncertainty, and short-term policy changes that affect production and distribution.

Monthly Trends for Food

monthly_trends = df.groupby(['year','month'])[['beans_close','maize_close','potatoes_1kg_close','food_price_index_close']].mean().reset_index()
monthly_trends

Visuals For Monthly Trends

import calendar

def month_to_name(x):
    # If it's an integer (1–12), convert to month name
    if isinstance(x, int):
        return calendar.month_name[x]
    # If it's already a string (e.g. "January"), keep it as is
    return x

monthly_trends['month'] = monthly_trends['month'].apply(month_to_name)

Heat map for Beans

# Make sure month names are properly formatted and ordered
month_order = list(calendar.month_name[1:])

# Convert month column to an ordered categorical type
monthly_trends['month'] = pd.Categorical(monthly_trends['month'],
                                         categories=month_order,
                                         ordered=True)

# pivot will respect that order
pivot = monthly_trends.pivot(index='year', columns='month', values='beans_close')

plt.figure(figsize=(10,6))
sns.heatmap(pivot, cmap='RdYlGn_r', annot=True, fmt=".1f")
plt.title('Seasonal Heatmap of Beans (Monthly Averages)')
plt.xlabel('Month')
plt.ylabel('Year')
plt.show()

Heatmap of Beans (Monthly Averages)

Heat map for Maize

# Make sure month names are properly formatted and ordered
month_order = list(calendar.month_name[1:])

# Convert month column to an ordered categorical type
monthly_trends['month'] = pd.Categorical(monthly_trends['month'],
                                         categories=month_order,
                                         ordered=True)

# pivot will respect that order
pivot = monthly_trends.pivot(index='year', columns='month', values='maize_close')

plt.figure(figsize=(10,6))
sns.heatmap(pivot, cmap='RdYlGn_r', annot=True, fmt=".1f")
plt.title('Seasonal Heatmap of Maize (Monthly Averages)')
plt.xlabel('Month')
plt.ylabel('Year')
plt.show()

Heatmap of Maize(Monthly Averages)

Heat map for potatoes

# Make sure month names are properly formatted and ordered
month_order = list(calendar.month_name[1:])

# Convert month column to an ordered categorical type
monthly_trends['month'] = pd.Categorical(monthly_trends['month'],
                                         categories=month_order,
                                         ordered=True)

# pivot will respect that order
pivot = monthly_trends.pivot(index='year', columns='month', values='potatoes_1kg_close')

plt.figure(figsize=(10,6))
sns.heatmap(pivot, cmap='RdYlGn_r', annot=True, fmt=".1f")
plt.title('Seasonal Heatmap of Potatoes (Monthly Averages)')
plt.xlabel('Month')
plt.ylabel('Year')
plt.show()

Heatmap of Potatoes (Monthly Averages)

Heat map for Food Prices Index

import calendar
# Make sure month names are properly formatted and ordered
month_order = list(calendar.month_name[1:])

# Convert month column to an ordered categorical type
monthly_trends['month'] = pd.Categorical(monthly_trends['month'],
                                         categories=month_order,
                                         ordered=True)

# pivot will respect that order
pivot = monthly_trends.pivot(index='year', columns='month', values='food_price_index_close')

plt.figure(figsize=(10,6))
sns.heatmap(pivot, cmap='RdYlGn_r', annot=True, fmt=".1f")
plt.title('Seasonal Heatmap of Food Price Index (Monthly Averages)')
plt.xlabel('Month')
plt.ylabel('Year')
plt.show()

Food Price Index (Monthly Averages)

Interpretation of the Heatmap: Seasonal Food Price Index (2007–2025)

1.General Trend Over Time (2007-2025)

The color intensity deepens gradually from top to bottom, indicating that food prices have generally increased over the years. Earlier years (2007–2010) show light yellow-green colors (index ≈ 0.4–0.8), while recent years (2023–2025) show dark blue shades (index ≈ 1.3–1.7). Conclusion: There’s a clear long-term upward trend in food prices, reflecting inflation, cost of production, or other macroeconomic factors.

2.Seasonal (Month-to-Month) Variation

Prices tend to peak between May and August (darker shades). January to April often shows lighter colors, relatively lower food prices. September to December stabilizes or slightly cools off, depending on the year. Interpretation: This suggests seasonal price pressure mid-year, possibly linked to: Planting or lean seasons before harvest (reduced supply). Increased demand or transport challenges in those months. Conversely, lower prices early in the year may correspond to harvest periods when supply is abundant.

3. Exceptional Years

2023 shows the darkest overall colors (1.6–1.8 range), indicating exceptionally high prices likely due to inflationary pressures or external shocks (e.g., global food shortages, climate effects). 2010 and 2016 show relatively cooler colors even mid-year, meaning prices were comparatively stable during those periods. Conclusion: Prices have become consistently high but less seasonally volatile in recent years.

Visualizing trends by Regions(Province, County and Market)

Regions that consistently have the highest or lowest food prices By Province

Average Closing Price of Food Per Province

# Step 1: Group by provinces and year to get average closing prices per year
avg_prices_province = (
    df.groupby(['provinces','year'], as_index=False)[['beans_close', 'maize_close','potatoes_1kg_close','food_price_index_close']].mean()
)
avg_prices_province

Visualize the table above (Average Closing Price of Food Per Province)

# Step 1: Reshape your data from wide to long format
price_columns = ['beans_close', 'maize_close', 'potatoes_1kg_close', 'food_price_index_close']
df_long = avg_prices_province.melt(
    id_vars=['provinces', 'year'],
    value_vars=price_columns,
    var_name='Commodity',
    value_name='Price'
)

print("--- Head of Reshaped (Long) Data ---")
print(df_long.head())
print("\n")

# 2. Plot the data using sns.relplot
sns.set_style("whitegrid")

# This creates a Figure-level plot that automatically handles faceting
g = sns.relplot(
    data=df_long,
    kind='line',          # Specify a line plot
    x='year',
    y='Price',
    hue='Commodity',      # Color lines by commodity
    col='provinces',      # Create columns for each province
    col_wrap=3,           # Wrap the columns after 3 plots
    height=3.5,           # Height of each individual plot
    aspect=1.5,           # Aspect ratio (width/height)
    legend='full'
)

# Step 3: Customize titles and labels
g.set_axis_labels('Year', 'Average Closing Price')
g.set_titles(col_template='{col_name}')
g.fig.suptitle('Food Price Trends by Province (2007–2025)', fontsize=16, y=1.03) # Add a main title

# 4. Save the figure
#plt.tight_layout()
plt.savefig('province_price_trends.png')

print("Faceted plot saved to 'province_price_trends.png'")

--- Head of Reshaped (Long) Data ---
  provinces  year    Commodity      Price
0   Central  2007  beans_close  43.306667
1   Central  2008  beans_close  74.847500
2   Central  2009  beans_close  77.480833
3   Central  2010  beans_close  60.275833
4   Central  2011  beans_close  90.697500


Faceted plot saved to 'province_price_trends.png'

Food Price Trends by Province (2007–2025)

Province with consistently the Highest and Lowest Food Price

# Created a New Column average_food_price
avg_prices_province['avg_food_price'] = avg_prices_province[
   ['beans_close', 'maize_close', 'potatoes_1kg_close']
].mean(axis=1)

Highest & Lowest Overall Average Food Prices By Province

# Compute the long-term average for each province
province_avg_overall = (
    avg_prices_province.groupby('provinces', as_index=False)['avg_food_price']
    .mean()
    .sort_values(by='avg_food_price', ascending=False)
)

# Display results
print(province_avg_overall)

# Get the top province
top_province = province_avg_overall.iloc[0]
bottom_province = province_avg_overall.iloc[-1]
print(f"Province with consistently highest food price: {top_province['provinces']} ({top_province['avg_food_price']:.2f})")
print(f"Province with consistently lowest food price: {bottom_province['provinces']} ({bottom_province['avg_food_price']:.2f})")

       provinces  avg_food_price
4  North Eastern       61.944615
3        Nairobi       59.616837
6    Rift Valley       58.805979
2        Eastern       56.963456
0        Central       56.529800
1          Coast       54.771665
5         Nyanza       53.851025
Province with consistently highest food price: North Eastern (61.94)
Province with consistently lowest food price: Nyanza (53.85)

Higest & Lowest Overall Prices by Province for each crop

# List of commodities to analyze
crops = ['beans_close', 'maize_close', 'potatoes_1kg_close', 'food_price_index_close']

for crop in crops:
    crop_name = crop.replace('_close', '').capitalize()
    print(f"\n🌾 Ranking of provinces by average {crop_name} price:\n")

    # Compute average price per province
    province_avg = (
        avg_prices_province.groupby('provinces', as_index=False)[crop]
        .mean()
        .sort_values(by=crop, ascending=False)
    )

    # Print full ranking
    for i, row in province_avg.iterrows():
        print(f"{i+1}. {row['provinces']}: {row[crop]:.2f}")

    # Identify top and bottom provinces
    top = province_avg.iloc[0]
    bottom = province_avg.iloc[-1]

    # Print summary
    print(f"\n🏆 Highest average {crop_name} price: {top['provinces']} ({top[crop]:.2f})")
    print(f"💰 Lowest average {crop_name} price: {bottom['provinces']} ({bottom[crop]:.2f})")
    print("-" * 60)

🌾 Ranking of provinces by average Beans price:

5. North Eastern: 102.25
6. Nairobi: 101.45
7. Rift Valley: 101.34
8. Eastern: 93.33
9. Coast: 92.49
10. Central: 91.42
11. Nyanza: 88.98

🏆 Highest average Beans price: North Eastern (102.25)
💰 Lowest average Beans price: Nyanza (88.98)
------------------------------------------------------------

🌾 Ranking of provinces by average Maize price:

12. North Eastern: 39.39
13. Central: 36.14
14. Eastern: 35.71
15. Nairobi: 35.65
16. Rift Valley: 34.29
17. Nyanza: 33.01
18. Coast: 32.68

🏆 Highest average Maize price: North Eastern (39.39)
💰 Lowest average Maize price: Coast (32.68)
------------------------------------------------------------

🌾 Ranking of provinces by average Potatoes_1kg price:

19. North Eastern: 44.19
20. Central: 42.03
21. Eastern: 41.85
22. Nairobi: 41.74
23. Rift Valley: 40.78
24. Nyanza: 39.56
25. Coast: 39.15

🏆 Highest average Potatoes_1kg price: North Eastern (44.19)
💰 Lowest average Potatoes_1kg price: Coast (39.15)
------------------------------------------------------------

🌾 Ranking of provinces by average Food_price_index price:

26. North Eastern: 1.05
27. Nairobi: 1.01
28. Rift Valley: 1.00
29. Eastern: 0.97
30. Central: 0.96
31. Coast: 0.93
32. Nyanza: 0.92

🏆 Highest average Food_price_index price: North Eastern (1.05)
💰 Lowest average Food_price_index price: Nyanza (0.92)
------------------------------------------------------------

Regions that consistently have the highest or lowest food prices By County

Average Closing price of Food per County

# Step 2: Group by counties and year to get average closing prices per year
avg_prices_county = (
    df.groupby(['counties','year'], as_index=False)[['beans_close', 'maize_close','potatoes_1kg_close','food_price_index_close']].mean()
)
avg_prices_county

Visualize the table above (Average Closing Price of Food Per County)

# Step 1: Keep province info for later merge
county_province_map = df[['counties', 'provinces']].drop_duplicates()

# Step 2: Group by counties and year (correct averaging)
avg_prices_county = (
    df.groupby(['counties', 'year'], as_index=False)[
        ['beans_close', 'maize_close', 'potatoes_1kg_close', 'food_price_index_close']
    ].mean()
)

# Step 3: Merge province info back
avg_prices_county = avg_prices_county.merge(county_province_map, on='counties', how='left')

# Step 4: Append province name to county
avg_prices_county['county_label'] = avg_prices_county['counties'] + " (" + avg_prices_county['provinces'] + ")"

# Step 5: Reshape for visualization
price_columns = ['beans_close', 'maize_close', 'potatoes_1kg_close', 'food_price_index_close']
df_long = avg_prices_county.melt(
    id_vars=['provinces', 'county_label', 'year'],
    value_vars=price_columns,
    var_name='Commodity',
    value_name='Price'
)

# Step 6: Plot
sns.set_style("whitegrid")

g = sns.relplot(
    data=df_long,
    kind='line',
    x='year',
    y='Price',
    hue='Commodity',
    col='county_label',    # use new county label
    col_wrap=3,
    height=3.5,
    aspect=1.5,
    legend='full'
)

g.set_axis_labels('Year', 'Average Closing Price')
g.set_titles(col_template='{col_name}')
g.fig.suptitle('Food Price Trends by County (2007–2025)', fontsize=16, y=1.03)

plt.savefig('county_price_trends_labeled.png', bbox_inches='tight')
print("Faceted plot saved to 'county_price_trends_labeled.png'")

Faceted plot saved to 'county_price_trends_labeled.png'

Food Price Trends by County (2007–2025)

Counties with consistently the Highest & Lowest Food Price

# Created a New Column average_food_price
avg_prices_county['avg_food_price'] = avg_prices_county[
    ['beans_close', 'maize_close', 'potatoes_1kg_close']
].mean(axis=1)
avg_prices_county

Highest & Lowest Overall Average Food Prices By county

# Compute the long-term average for each County
county_avg_overall = (
    avg_prices_county.groupby('counties', as_index=False)['avg_food_price']
    .mean()
    .sort_values(by='avg_food_price', ascending=False)
)

# Display results
print(county_avg_overall)

# Get the top & bottom County
top_county = county_avg_overall.iloc[0]
bottom_county = county_avg_overall.iloc[-1]
print(f"County with consistently highest food price: {top_county['counties']} ({top_county['avg_food_price']:.2f})")
print(f"County with consistently lowest food price: {bottom_county['counties']} ({bottom_county['avg_food_price']:.2f})")

        counties  avg_food_price
23         Wajir       63.705852
21       Turkana       63.455772
10       Mandera       62.450173
1        Garissa       60.877888
15       Nairobi       59.616837
11      Marsabit       59.035170
2         Isiolo       57.636550
17         Nyeri       56.529800
8       Machakos       56.230134
20    Tana River       56.217690
3        Kajiado       55.728368
4         Kilifi       54.770632
12    Meru North       54.318829
16        Nakuru       54.172254
5         Kisumu       53.851025
6          Kitui       53.719964
13    Meru South       53.655970
14       Mombasa       53.358758
24    West Pokot       52.861940
9        Makueni       52.855744
7          Kwale       52.599195
18       Samburu       52.502900
19  Taita Taveta       52.309043
0        Baringo       49.603472
22   Uasin Gishu       49.594992
County with consistently highest food price: Wajir (63.71)
County with consistently lowest food price: Uasin Gishu (49.59)

Higest & Lowest Overall Prices by county for each crop

# List of commodities to analyze
crops = ['beans_close', 'maize_close', 'potatoes_1kg_close', 'food_price_index_close']

for crop in crops:
    crop_name = crop.replace('_close', '').capitalize()
    print(f"\n🌾 Ranking of counties by average {crop_name} price:\n")

    # Compute average price per county
    county_avg = (
        avg_prices_county.groupby('counties', as_index=False)[crop]
        .mean()
        .sort_values(by=crop, ascending=False)
    )

    # Print full ranking
    for i, row in county_avg.iterrows():
        print(f"{i+1}. {row['counties']}: {row[crop]:.2f}")

    # Identify top and bottom county
    top = county_avg.iloc[0]
    bottom = county_avg.iloc[-1]

    # Print summary
    print(f"\n🏆 Highest average {crop_name} price: {top['counties']} ({top[crop]:.2f})")
    print(f"💰 Lowest average {crop_name} price: {bottom['counties']} ({bottom[crop]:.2f})")
    print("-" * 60)

🌾 Ranking of counties by average Beans price:

22. Turkana: 110.47
23. Wajir: 106.61
24. Mandera: 103.39
25. Nairobi: 101.45
26. Marsabit: 101.17
27. Garissa: 99.67
28. Kilifi: 98.50
29. Mombasa: 94.78
30. Isiolo: 94.48
31. West Pokot: 94.18
32. Kwale: 92.56
33. Tana River: 91.42
34. Nyeri: 91.42
35. Machakos: 90.67
36. Nakuru: 89.50
37. Kajiado: 89.33
38. Kisumu: 88.98
39. Samburu: 88.05
40. Meru South: 86.35
41. Meru North: 86.28
42. Baringo: 85.64
43. Uasin Gishu: 84.31
44. Makueni: 80.81
45. Taita Taveta: 78.69
46. Kitui: 77.55

🏆 Highest average Beans price: Turkana (110.47)
💰 Lowest average Beans price: Kitui (77.55)
------------------------------------------------------------

🌾 Ranking of counties by average Maize price:

47. Wajir: 39.90
48. Mandera: 39.62
49. Garissa: 39.05
50. Turkana: 36.99
51. Isiolo: 36.39
52. Kitui: 36.27
53. Taita Taveta: 36.17
54. Nyeri: 36.14
55. Machakos: 36.05
56. Makueni: 35.87
57. Tana River: 35.75
58. Kajiado: 35.71
59. Nairobi: 35.65
60. Meru North: 35.32
61. Marsabit: 34.99
62. Meru South: 34.13
63. Uasin Gishu: 33.53
64. Kisumu: 33.01
65. Samburu: 31.10
66. Nakuru: 30.20
67. Kilifi: 29.28
68. Mombasa: 28.99
69. Kwale: 28.89
70. West Pokot: 28.49
71. Baringo: 27.78

🏆 Highest average Maize price: Wajir (39.90)
💰 Lowest average Maize price: Baringo (27.78)
------------------------------------------------------------

🌾 Ranking of counties by average Potatoes_1kg price:

72. Kitui: 47.34
73. Wajir: 44.61
74. Mandera: 44.34
75. Garissa: 43.91
76. Turkana: 42.91
77. Nakuru: 42.82
78. Kajiado: 42.15
79. Taita Taveta: 42.06
80. Isiolo: 42.04
81. Nyeri: 42.03
82. Machakos: 41.97
83. Makueni: 41.89
84. Nairobi: 41.74
85. Tana River: 41.49
86. Meru North: 41.35
87. Marsabit: 40.95
88. Meru South: 40.49
89. Kisumu: 39.56
90. Samburu: 38.35
91. Kilifi: 36.54
92. Kwale: 36.35
93. Mombasa: 36.30
94. West Pokot: 35.92
95. Baringo: 35.39
96. Uasin Gishu: 30.95

🏆 Highest average Potatoes_1kg price: Kitui (47.34)
💰 Lowest average Potatoes_1kg price: Uasin Gishu (30.95)
------------------------------------------------------------

🌾 Ranking of counties by average Food_price_index price:

97. Wajir: 1.08
98. Turkana: 1.08
99. Mandera: 1.06
100. Garissa: 1.04
101. Nairobi: 1.01
102. Marsabit: 1.00
103. Isiolo: 0.98
104. Nyeri: 0.96
105. Machakos: 0.96
106. Tana River: 0.96
107. Kajiado: 0.95
108. Kilifi: 0.93
109. Meru North: 0.92
110. Nakuru: 0.92
111. Kisumu: 0.92
112. Kitui: 0.91
113. Meru South: 0.91
114. Mombasa: 0.91
115. West Pokot: 0.90
116. Makueni: 0.90
117. Kwale: 0.89
118. Samburu: 0.89
119. Taita Taveta: 0.89
120. Uasin Gishu: 0.84
121. Baringo: 0.84

🏆 Highest average Food_price_index price: Wajir (1.08)
💰 Lowest average Food_price_index price: Baringo (0.84)
------------------------------------------------------------

Regions that consistently have the highest or lowest food prices By Market

Average Closing Price for Food per Market

# Step 3: Group by market and year to get average closing prices per year
avg_prices_mkt = (
    df.groupby(['mkt_name','year'], as_index=False)[['beans_close', 'maize_close','potatoes_1kg_close','food_price_index_close']].mean()
)
avg_prices_mkt

Visualize the table above (Average Closing Price of Food Per Market)

# Step 1: Create a mapping of market → county & province
market_location_map = df[['mkt_name', 'counties', 'provinces']].drop_duplicates()

# Step 2: Group by market and year to compute averages (if not already done)
avg_prices_mkt = (
    df.groupby(['mkt_name', 'year'], as_index=False)[
        ['beans_close', 'maize_close', 'potatoes_1kg_close', 'food_price_index_close']
    ].mean()
)

# Step 3: Merge location info into the averages
avg_prices_mkt = avg_prices_mkt.merge(market_location_map, on='mkt_name', how='left')

# Step 4: Append county and province to market name
avg_prices_mkt['mkt_label'] = (
    avg_prices_mkt['mkt_name']
    + " (" 
    + avg_prices_mkt['counties'] 
    + ", " 
    + avg_prices_mkt['provinces'] 
    + ")"
)

# Step 5: Reshape data for plotting
price_columns = ['beans_close', 'maize_close', 'potatoes_1kg_close', 'food_price_index_close']
df_long = avg_prices_mkt.melt(
    id_vars=['mkt_label', 'year'],
    value_vars=price_columns,
    var_name='Commodity',
    value_name='Price'
)

print("--- Head of Reshaped (Long) Data ---")
print(df_long.head(), "\n")

# Step 6: Plot
sns.set_style("whitegrid")

g = sns.relplot(
    data=df_long,
    kind='line',
    x='year',
    y='Price',
    hue='Commodity',
    col='mkt_label',     # use new combined label
    col_wrap=3,
    height=3.5,
    aspect=1.5,
    legend='full'
)

g.set_axis_labels('Year', 'Average Closing Price')
g.set_titles(col_template='{col_name}')
g.fig.suptitle('Food Price Trends by Market (2007–2025)', fontsize=16, y=1.03)

plt.savefig('Market_price_trends_labeled.png', bbox_inches='tight')
print("Faceted plot saved to 'Market_price_trends_labeled.png'")

--- Head of Reshaped (Long) Data ---
                              mkt_label  year    Commodity      Price
0  Alango Arba (Garissa, North Eastern)  2007  beans_close  46.450833
1  Alango Arba (Garissa, North Eastern)  2008  beans_close  80.490833
2  Alango Arba (Garissa, North Eastern)  2009  beans_close  82.659167
3  Alango Arba (Garissa, North Eastern)  2010  beans_close  64.414167
4  Alango Arba (Garissa, North Eastern)  2011  beans_close  97.013333 

Faceted plot saved to 'Market_price_trends_labeled.png'

Markets with consistently the Highest & Lowest Food Price

# Created a New Column average_food_price per market
avg_prices_mkt['avg_food_price'] = avg_prices_mkt[
    ['beans_close', 'maize_close', 'potatoes_1kg_close']
].mean(axis=1)
avg_prices_mkt

Highest & Lowest Overall Average Food Prices By Market

# Compute the long-term average for each market
mkt_avg_overall = (
    avg_prices_mkt.groupby('mkt_name', as_index=False)['avg_food_price']
    .mean()
    .sort_values(by='avg_food_price', ascending=False)
)

# Display results
print(mkt_avg_overall)

# Get the top & bottom County
top_mkt = mkt_avg_overall.iloc[0]
bottom_mkt = mkt_avg_overall.iloc[-1]
print(f"Market with consistently highest food price: {top_mkt['mkt_name']} ({top_mkt['avg_food_price']:.2f})")
print(f"Market with consistently lowest food price: {bottom_mkt['mkt_name']} ({bottom_mkt['avg_food_price']:.2f})")

                   mkt_name  avg_food_price
103             Lodwar town       66.537321
64    Kalobeyei (Village 2)       66.192429
122               Loturerei       66.142083
32             Eliye Centre       66.116122
63    Kalobeyei (Village 1)       65.780203
..                      ...             ...
119           Lororo Centre       48.564512
101            Loboi Center       48.427539
93                  Koriema       48.419481
136  Marigat town (Baringo)       48.349804
81           Kimalel Center       48.313668

[184 rows x 2 columns]
Market with consistently highest food price: Lodwar town (66.54)
Market with consistently lowest food price: Kimalel Center (48.31)

Highest & Lowest Overall Prices by Market for each crop

# List of commodities to analyze
crops = ['beans_close', 'maize_close', 'potatoes_1kg_close', 'food_price_index_close']

for crop in crops:
    crop_name = crop.replace('_close', '').capitalize()
    print(f"\n🌾 Ranking of markets by average {crop_name} price:\n")

    # Compute average price per Market
    mkt_avg = (
        avg_prices_mkt.groupby('mkt_name', as_index=False)[crop]
        .mean()
        .sort_values(by=crop, ascending=False)
    )

    # Print full ranking
    for i, row in mkt_avg.iterrows():
        print(f"{i+1}. {row['mkt_name']}: {row[crop]:.2f}")

    # Identify top and bottom Market
    top = mkt_avg.iloc[0]
    bottom = mkt_avg.iloc[-1]

    # Print summary
    print(f"\n🏆 Highest average {crop_name} price: {top['mkt_name']} ({top[crop]:.2f})")
    print(f"💰 Lowest average {crop_name} price: {bottom['mkt_name']} ({bottom[crop]:.2f})")
    print("-" * 60)

🌾 Ranking of markets by average Beans price:

45. HongKong (Kakuma): 115.70
46. Lodwar town: 114.98
47. Kakuma 4: 114.28
48. Ethiopia (Kakuma): 114.22
49. Lopur: 114.14
50. Mogadishu (Kakuma): 113.98
51. Kakuma 3: 113.82
52. Loturerei: 113.81
53. Eliye Centre: 113.78
54. Kalobeyei (Village 2): 113.60
55. Lorugum: 113.60
56. Namoruputh: 113.26
57. Lokiriama: 112.90
58. Kalemunyang: 112.77
59. Letea: 112.62
60. Kalokol: 112.59
61. Alemsekon: 112.44
62. Kataboi: 112.24
63. Kalobeyei (Village 1): 112.16
64. Kakuma 2: 112.15
65. Naduat: 111.91
66. Lolupe: 111.88
67. Lokangae: 111.70
68. Kaeris: 111.47
69. Lowarengak: 111.03
70. Lokitaung: 111.01
71. Kaleng: 110.89
72. Kaikor: 110.71
73. Kaakelai: 110.71
74. Napak: 110.67
75. Nakinomet: 110.64
76. Kokuro: 110.58
77. Kalobeyei (Village 3): 110.40
78. Wajir town: 110.21
79. Aposta: 110.10
80. Dagahaley (Daadab): 109.79
81. Lokapel: 109.66
82. Lokichar (Turkana): 109.38
83. Kiliwaheri: 109.27
84. Loyangalani: 109.13
85. Naakalei: 109.00
86. Lokichoggio: 108.69
87. Banissa: 108.39
88. Tarbaj: 108.30
89. Lagbogol: 108.02
90. Griftu: 107.23
91. Marigat (Baringo): 106.63
92. Kargi: 105.93
93. Hadado: 105.79
94. Bute: 105.21
95. Nakukulas: 104.80
96. Kalemgorok: 104.77
97. Kibra (Nairobi): 104.66
98. Eldas: 104.33
99. El Wak: 104.22
100. Habaswein: 103.83
101. Katilia: 103.51
102. Marsabit: 103.25
103. Dub Goba: 103.24
104. Marsabit town: 103.14
105. Elelea: 103.08
106. Kawangware (Nairobi): 102.85
107. Kangemi (Nairobi): 102.78
108. Dirib: 102.74
109. Saretho: 102.52
110. Wakulima (Nairobi): 102.47
111. Songa: 102.35
112. Nairobi: 102.31
113. Lotubae: 102.22
114. Kitengela (Kajiado): 102.22
115. Karare: 102.02
116. Mathare (Nairobi): 101.83
117. Rhamu: 101.82
118. Kargi South Horr: 101.71
119. Lokwii: 101.29
120. Baraki: 101.23
121. Morulem: 101.21
122. Alinjugur: 101.14
123. Lokori: 101.13
124. Ashabito: 100.98
125. Takaba (Mandera): 100.90
126. Alango Arba: 100.84
127. Mandera town: 100.78
128. Mandera: 100.76
129. Korr: 100.61
130. Damajale: 100.48
131. Modogashe: 100.28
132. IFO (Daadab): 100.08
133. Sericho Center: 100.06
134. Shanta Abak: 99.95
135. Saka Town: 98.97
136. Dadaab town: 98.75
137. Garissa town (Garissa): 98.68
138. Kilifi: 98.50
139. Garissa: 98.48
140. Dertu: 98.41
141. Iresaboru Centre: 98.30
142. Malka GALLA: 98.17
143. Dandora (Nairobi): 97.94
144. Maalamin: 97.87
145. Lesirkan: 97.85
146. Liboi: 97.75
147. Loglogo: 97.07
148. Jarajara: 96.76
149. Nanigi: 96.16
150. Mukuru (Nairobi): 96.03
151. Benane: 95.97
152. Kajiado: 95.64
153. Bulesa Bulesa: 95.57
154. Shauri Yako: 95.51
155. Kisumu Ndogo (Mombasa): 95.49
156. Hagadera (Daadab): 95.48
157. Biliqo: 95.41
158. Moroto (Mombasa): 95.33
159. Kongowea (Mombasa): 95.27
160. Merti CENTER: 95.22
161. Shonda (Mombasa): 95.15
162. Baragoi: 95.13
163. Kalahari (Mombasa): 95.02
164. Garbatulla: 94.93
165. Ngurunit: 94.62
166. Bilbil: 94.56
167. Malkadaka: 94.50
168. Boka: 94.46
169. Makutano (West Pokot): 94.32
170. Mombasa: 94.25
171. Lomut (West Pokot): 94.03
172. Charidende: 93.62
173. Junda (Mombasa): 93.53
174. Bangladesh (Mombasa): 93.47
175. Boji: 93.45
176. Bura: 92.96
177. Laisamis Town: 92.82
178. Vanga (Kwale): 92.56
179. Makere: 91.74
180. Karatina (Nyeri): 91.42
181. Merille Town: 91.14
182. Tala Centre Market (Machakos): 90.67
183. Hola town: 90.43
184. Nakuru: 89.80
185. Kibuye (Kisumu): 89.24
186. Wakulima (Nakuru): 89.19
187. Kisumu: 88.72
188. Garsen: 88.36
189. Wayu: 88.20
190. Tarasaa: 88.16
191. Kipini: 87.99
192. Ngilai: 87.75
193. Isiolo town: 87.75
194. Bangale: 87.62
195. Wamba: 87.32
196. Lolkuniyani: 87.00
197. Oldonyiro: 86.80
198. Kiwanja: 86.69
199. Porro: 86.56
200. Lengusaka: 86.44
201. Lpus: 86.39
202. Kaanwa (Tharaka Nithi): 86.35
203. Kipsing CENTER: 86.30
204. Lodungokwe: 86.23
205. Ngaremara: 85.87
206. Kisima: 85.37
207. Lolkunono: 85.33
208. Maralal: 85.26
209. Loosuk: 84.93
210. Chemolingot: 84.85
211. Suguta: 84.64
212. Eldoret town (Uasin Gishu): 84.31
213. Nginyang: 83.98
214. Emali: 83.75
215. Amaya: 83.43
216. Kitui: 83.33
217. Illbissil Food Market (Kajiado): 83.02
218. Koriema: 82.69
219. Lororo Centre: 82.66
220. Kimalel Center: 82.25
221. Loboi Center: 82.21
222. Marigat town (Baringo): 82.04
223. Kibwezi: 81.00
224. Wote town (Makueni): 80.23
225. Makueni: 80.20
226. Kathonzweni (Makueni): 78.88
227. Mtito Andei: 78.69
228. Kitui town (Kitui): 71.77

🏆 Highest average Beans price: HongKong (Kakuma) (115.70)
💰 Lowest average Beans price: Kitui town (Kitui) (71.77)
------------------------------------------------------------

🌾 Ranking of markets by average Maize price:

229. Hagadera (Daadab): 41.70
230. Wajir town: 40.75
231. Lagbogol: 40.48
232. Tarbaj: 40.35
233. El Wak: 40.13
234. Dagahaley (Daadab): 40.06
235. Kalobeyei (Village 1): 39.92
236. Griftu: 39.90
237. Kalobeyei (Village 2): 39.81
238. IFO (Daadab): 39.66
239. Lodwar town: 39.66
240. Loturerei: 39.65
241. Banissa: 39.63
242. Eliye Centre: 39.63
243. Habaswein: 39.62
244. Ashabito: 39.60
245. Kiliwaheri: 39.59
246. Mandera: 39.57
247. Mandera town: 39.56
248. Rhamu: 39.48
249. Damajale: 39.44
250. Dadaab town: 39.42
251. Alinjugur: 39.39
252. Hadado: 39.38
253. Bute: 39.37
254. Saretho: 39.35
255. Takaba (Mandera): 39.35
256. Eldas: 39.34
257. Baraki: 39.21
258. Garissa town (Garissa): 39.10
259. Garissa: 38.99
260. Alango Arba: 38.80
261. Modogashe: 38.80
262. Kalokol: 38.73
263. Letea: 38.55
264. Shanta Abak: 38.47
265. Nanigi: 38.46
266. Kalemunyang: 38.43
267. Lorugum: 38.37
268. Kataboi: 38.36
269. Maalamin: 38.36
270. Liboi: 38.26
271. Sericho Center: 38.15
272. Dandora (Nairobi): 38.14
273. Kalobeyei (Village 3): 38.09
274. Bilbil: 38.07
275. Bura: 37.90
276. Jarajara: 37.71
277. Namoruputh: 37.68
278. Lowarengak: 37.64
279. Saka Town: 37.59
280. Benane: 37.51
281. Dertu: 37.51
282. Garbatulla: 37.43
283. Bangale: 37.40
284. Lokitaung: 37.40
285. Iresaboru Centre: 37.31
286. Lolupe: 37.24
287. Lokiriama: 37.22
288. Kaeris: 37.21
289. Boji: 37.18
290. Mogadishu (Kakuma): 37.17
291. Naduat: 37.16
292. Naakalei: 37.14
293. Makere: 37.13
294. Malka GALLA: 37.06
295. Ethiopia (Kakuma): 37.02
296. Loyangalani: 36.93
297. Kokuro: 36.93
298. Lokichar (Turkana): 36.90
299. Kaleng: 36.89
300. Kaakelai: 36.81
301. Kangemi (Nairobi): 36.73
302. HongKong (Kakuma): 36.72
303. Lokapel: 36.71
304. Kawangware (Nairobi): 36.67
305. Kakuma 3: 36.67
306. Boka: 36.59
307. Kaikor: 36.46
308. Alemsekon: 36.43
309. Merti CENTER: 36.40
310. Nakinomet: 36.35
311. Napak: 36.35
312. Kitui town (Kitui): 36.28
313. Kitui: 36.26
314. Kakuma 2: 36.22
315. Kibra (Nairobi): 36.18
316. Mtito Andei: 36.17
317. Wayu: 36.17
318. Kathonzweni (Makueni): 36.15
319. Karatina (Nyeri): 36.14
320. Lopur: 36.13
321. Tala Centre Market (Machakos): 36.05
322. Nakukulas: 36.03
323. Kakuma 4: 36.02
324. Bulesa Bulesa: 36.00
325. Wote town (Makueni): 35.99
326. Hola town: 35.99
327. Kajiado: 35.98
328. Makueni: 35.98
329. Malkadaka: 35.96
330. Kibwezi: 35.89
331. Biliqo: 35.85
332. Kisumu: 35.78
333. Kargi: 35.76
334. Lokangae: 35.76
335. Katilia: 35.70
336. Dub Goba: 35.60
337. Dirib: 35.59
338. Kalemgorok: 35.54
339. Marsabit: 35.51
340. Elelea: 35.49
341. Ngaremara: 35.48
342. Mukuru (Nairobi): 35.46
343. Illbissil Food Market (Kajiado): 35.44
344. Marsabit town: 35.44
345. Aposta: 35.42
346. Songa: 35.38
347. Emali: 35.34
348. Kiwanja: 35.15
349. Karare: 35.14
350. Isiolo town: 35.12
351. Lotubae: 35.04
352. Morulem: 34.81
353. Lokwii: 34.80
354. Loglogo: 34.76
355. Wakulima (Nairobi): 34.74
356. Lokichoggio: 34.71
357. Lokori: 34.61
358. Mathare (Nairobi): 34.61
359. Kitengela (Kajiado): 34.25
360. Korr: 34.18
361. Kaanwa (Tharaka Nithi): 34.13
362. Kipsing CENTER: 34.10
363. Nairobi: 34.09
364. Laisamis Town: 33.80
365. Merille Town: 33.69
366. Eldoret town (Uasin Gishu): 33.53
367. Garsen: 33.24
368. Kipini: 33.13
369. Tarasaa: 33.10
370. Kargi South Horr: 33.10
371. Wamba: 33.03
372. Lengusaka: 32.92
373. Ngurunit: 32.84
374. Lolkuniyani: 32.68
375. Charidende: 32.64
376. Ngilai: 32.59
377. Oldonyiro: 32.55
378. Mombasa: 32.40
379. Nakuru: 32.22
380. Lpus: 31.68
381. Lodungokwe: 31.68
382. Lesirkan: 31.41
383. Baragoi: 31.11
384. Junda (Mombasa): 30.72
385. Kibuye (Kisumu): 30.24
386. Kisima: 30.14
387. Maralal: 29.53
388. Porro: 29.36
389. Kilifi: 29.28
390. Lolkunono: 29.27
391. Loosuk: 29.14
392. Suguta: 29.14
393. Vanga (Kwale): 28.89
394. Lomut (West Pokot): 28.79
395. Bangladesh (Mombasa): 28.50
396. Amaya: 28.46
397. Kalahari (Mombasa): 28.41
398. Shauri Yako: 28.34
399. Kisumu Ndogo (Mombasa): 28.25
400. Moroto (Mombasa): 28.25
401. Makutano (West Pokot): 28.18
402. Wakulima (Nakuru): 28.17
403. Shonda (Mombasa): 28.03
404. Kongowea (Mombasa): 28.02
405. Nginyang: 27.87
406. Chemolingot: 27.80
407. Loboi Center: 27.72
408. Lororo Centre: 27.72
409. Marigat town (Baringo): 27.71
410. Marigat (Baringo): 27.70
411. Kimalel Center: 27.56
412. Koriema: 27.51

🏆 Highest average Maize price: Hagadera (Daadab) (41.70)
💰 Lowest average Maize price: Koriema (27.51)
------------------------------------------------------------

🌾 Ranking of markets by average Potatoes_1kg price:

413. Kitui: 52.50
414. Nakuru: 49.91
415. Hagadera (Daadab): 45.97
416. Kalobeyei (Village 1): 45.27
417. Wajir town: 45.27
418. Kalobeyei (Village 2): 45.16
419. Lagbogol: 45.02
420. Lodwar town: 44.98
421. Loturerei: 44.96
422. Eliye Centre: 44.94
423. Tarbaj: 44.94
424. El Wak: 44.77
425. Griftu: 44.64
426. Dagahaley (Daadab): 44.46
427. Habaswein: 44.40
428. Banissa: 44.33
429. Ashabito: 44.33
430. Mandera: 44.31
431. Mandera town: 44.31
432. Kiliwaheri: 44.31
433. Kalokol: 44.26
434. Rhamu: 44.24
435. Hadado: 44.22
436. IFO (Daadab): 44.21
437. Eldas: 44.19
438. Bute: 44.18
439. Alinjugur: 44.17
440. Damajale: 44.17
441. Dadaab town: 44.15
442. Saretho: 44.15
443. Takaba (Mandera): 44.15
444. Letea: 44.13
445. Kalemunyang: 44.07
446. Baraki: 44.03
447. Lorugum: 44.00
448. Kataboi: 43.97
449. Kalobeyei (Village 3): 43.85
450. Garissa town (Garissa): 43.84
451. Alango Arba: 43.79
452. Garissa: 43.78
453. Modogashe: 43.70
454. Shanta Abak: 43.63
455. Dandora (Nairobi): 43.53
456. Liboi: 43.50
457. Namoruputh: 43.48
458. Maalamin: 43.48
459. Nanigi: 43.40
460. Lowarengak: 43.38
461. Sericho Center: 43.31
462. Bilbil: 43.20
463. Lokitaung: 43.20
464. Lolupe: 43.12
465. Lokiriama: 43.11
466. Naduat: 43.07
467. Kaeris: 43.06
468. Bura: 43.04
469. Jarajara: 42.95
470. Naakalei: 42.94
471. Saka Town: 42.91
472. Mogadishu (Kakuma): 42.89
473. Dertu: 42.86
474. Kokuro: 42.84
475. Lokichar (Turkana): 42.84
476. Benane: 42.84
477. Kaleng: 42.83
478. Ethiopia (Kakuma): 42.78
479. Bangale: 42.76
480. Kaakelai: 42.76
481. Garbatulla: 42.75
482. Iresaboru Centre: 42.73
483. Lokapel: 42.71
484. Loyangalani: 42.60
485. HongKong (Kakuma): 42.57
486. Boji: 42.56
487. Malka GALLA: 42.54
488. Kakuma 3: 42.54
489. Kaikor: 42.48
490. Alemsekon: 42.47
491. Makere: 42.45
492. Kangemi (Nairobi): 42.44
493. Kawangware (Nairobi): 42.42
494. Napak: 42.40
495. Nakinomet: 42.40
496. Kakuma 2: 42.28
497. Kajiado: 42.24
498. Nakukulas: 42.21
499. Lopur: 42.21
500. Boka: 42.19
501. Kitui town (Kitui): 42.19
502. Kakuma 4: 42.15
503. Kathonzweni (Makueni): 42.09
504. Mtito Andei: 42.06
505. Merti CENTER: 42.06
506. Illbissil Food Market (Kajiado): 42.05
507. Kisumu: 42.04
508. Karatina (Nyeri): 42.03
509. Wote town (Makueni): 42.01
510. Makueni: 42.00
511. Kibra (Nairobi): 42.00
512. Lokangae: 41.98
513. Tala Centre Market (Machakos): 41.97
514. Katilia: 41.91
515. Aposta: 41.87
516. Kalemgorok: 41.85
517. Kibwezi: 41.83
518. Bulesa Bulesa: 41.76
519. Malkadaka: 41.74
520. Elelea: 41.74
521. Wayu: 41.69
522. Biliqo: 41.65
523. Hola town: 41.58
524. Kargi: 41.57
525. Emali: 41.49
526. Ngaremara: 41.48
527. Mukuru (Nairobi): 41.40
528. Lotubae: 41.38
529. Lokichoggio: 41.37
530. Dub Goba: 41.35
531. Dirib: 41.34
532. Nairobi: 41.32
533. Marsabit: 41.32
534. Marsabit town: 41.26
535. Kiwanja: 41.22
536. Morulem: 41.20
537. Lokwii: 41.20
538. Songa: 41.14
539. Isiolo town: 41.14
540. Lokori: 41.08
541. Karare: 41.00
542. Wakulima (Nairobi): 40.99
543. Mathare (Nairobi): 40.91
544. Loglogo: 40.72
545. Kitengela (Kajiado): 40.69
546. Kipsing CENTER: 40.50
547. Kaanwa (Tharaka Nithi): 40.49
548. Korr: 40.35
549. Laisamis Town: 40.04
550. Merille Town: 40.01
551. Wamba: 39.92
552. Lengusaka: 39.86
553. Lolkuniyani: 39.66
554. Kargi South Horr: 39.66
555. Garsen: 39.65
556. Ngurunit: 39.58
557. Ngilai: 39.58
558. Kipini: 39.55
559. Tarasaa: 39.55
560. Oldonyiro: 39.34
561. Charidende: 39.27
562. Lodungokwe: 38.92
563. Lpus: 38.91
564. Lesirkan: 38.51
565. Baragoi: 38.32
566. Mombasa: 38.02
567. Junda (Mombasa): 37.80
568. Kisima: 37.69
569. Kibuye (Kisumu): 37.09
570. Maralal: 37.06
571. Porro: 36.91
572. Lolkunono: 36.83
573. Suguta: 36.82
574. Loosuk: 36.73
575. Kilifi: 36.54
576. Vanga (Kwale): 36.35
577. Lomut (West Pokot): 36.25
578. Amaya: 36.14
579. Bangladesh (Mombasa): 36.02
580. Kalahari (Mombasa): 35.95
581. Shauri Yako: 35.93
582. Kisumu Ndogo (Mombasa): 35.86
583. Moroto (Mombasa): 35.84
584. Wakulima (Nakuru): 35.74
585. Kongowea (Mombasa): 35.66
586. Shonda (Mombasa): 35.65
587. Makutano (West Pokot): 35.60
588. Nginyang: 35.48
589. Chemolingot: 35.41
590. Loboi Center: 35.35
591. Marigat (Baringo): 35.33
592. Lororo Centre: 35.32
593. Marigat town (Baringo): 35.30
594. Kimalel Center: 35.13
595. Koriema: 35.06
596. Eldoret town (Uasin Gishu): 30.95

🏆 Highest average Potatoes_1kg price: Kitui (52.50)
💰 Lowest average Potatoes_1kg price: Eldoret town (Uasin Gishu) (30.95)
------------------------------------------------------------

🌾 Ranking of markets by average Food_price_index price:

597. Lodwar town: 1.13
598. Kalobeyei (Village 2): 1.13
599. Loturerei: 1.13
600. Eliye Centre: 1.12
601. Kalobeyei (Village 1): 1.12
602. Wajir town: 1.11
603. Lorugum: 1.11
604. Kalokol: 1.11
605. Kalemunyang: 1.11
606. Letea: 1.11
607. HongKong (Kakuma): 1.11
608. Kataboi: 1.10
609. Namoruputh: 1.10
610. Dagahaley (Daadab): 1.10
611. Mogadishu (Kakuma): 1.10
612. Ethiopia (Kakuma): 1.10
613. Tarbaj: 1.10
614. Lagbogol: 1.10
615. Lokiriama: 1.10
616. Kiliwaheri: 1.10
617. Kakuma 3: 1.09
618. Lopur: 1.09
619. Kakuma 4: 1.09
620. Banissa: 1.09
621. Kalobeyei (Village 3): 1.09
622. Lolupe: 1.09
623. Naduat: 1.09
624. Lowarengak: 1.09
625. Kaeris: 1.09
626. Griftu: 1.09
627. Lokitaung: 1.09
628. Alemsekon: 1.08
629. Kakuma 2: 1.08
630. Kaleng: 1.08
631. Kokuro: 1.08
632. Kaakelai: 1.08
633. Kaikor: 1.08
634. Lokangae: 1.07
635. Napak: 1.07
636. Hadado: 1.07
637. Nakinomet: 1.07
638. Lokichar (Turkana): 1.07
639. Lokapel: 1.07
640. El Wak: 1.07
641. Naakalei: 1.07
642. Bute: 1.07
643. Loyangalani: 1.07
644. Eldas: 1.07
645. Habaswein: 1.06
646. Aposta: 1.06
647. Saretho: 1.05
648. Rhamu: 1.05
649. Ashabito: 1.05
650. Lokichoggio: 1.05
651. Alinjugur: 1.05
652. Mandera town: 1.05
653. Mandera: 1.05
654. Baraki: 1.05
655. Takaba (Mandera): 1.05
656. Damajale: 1.04
657. IFO (Daadab): 1.04
658. Alango Arba: 1.04
659. Kargi: 1.04
660. Hagadera (Daadab): 1.04
661. Nakukulas: 1.04
662. Kibra (Nairobi): 1.04
663. Modogashe: 1.04
664. Dadaab town: 1.03
665. Kalemgorok: 1.03
666. Shanta Abak: 1.03
667. Kangemi (Nairobi): 1.03
668. Kawangware (Nairobi): 1.03
669. Garissa town (Garissa): 1.03
670. Sericho Center: 1.03
671. Garissa: 1.03
672. Katilia: 1.03
673. Elelea: 1.02
674. Dub Goba: 1.02
675. Marsabit: 1.02
676. Marsabit town: 1.02
677. Dirib: 1.02
678. Maalamin: 1.02
679. Dandora (Nairobi): 1.02
680. Liboi: 1.02
681. Saka Town: 1.02
682. Songa: 1.01
683. Dertu: 1.01
684. Lotubae: 1.01
685. Iresaboru Centre: 1.01
686. Wakulima (Nairobi): 1.01
687. Karare: 1.01
688. Nanigi: 1.01
689. Malka GALLA: 1.01
690. Nairobi: 1.01
691. Jarajara: 1.01
692. Mathare (Nairobi): 1.01
693. Lokwii: 1.01
694. Morulem: 1.00
695. Kitengela (Kajiado): 1.00
696. Lokori: 1.00
697. Benane: 1.00
698. Bilbil: 1.00
699. Korr: 0.99
700. Garbatulla: 0.99
701. Kargi South Horr: 0.99
702. Bura: 0.99
703. Kajiado: 0.99
704. Merti CENTER: 0.98
705. Bulesa Bulesa: 0.98
706. Boka: 0.98
707. Boji: 0.98
708. Biliqo: 0.98
709. Mukuru (Nairobi): 0.98
710. Loglogo: 0.98
711. Malkadaka: 0.98
712. Kitui: 0.98
713. Nakuru: 0.97
714. Makere: 0.97
715. Marigat (Baringo): 0.96
716. Karatina (Nyeri): 0.96
717. Tala Centre Market (Machakos): 0.96
718. Hola town: 0.95
719. Bangale: 0.95
720. Lesirkan: 0.95
721. Ngurunit: 0.95
722. Laisamis Town: 0.94
723. Kisumu: 0.94
724. Wayu: 0.94
725. Charidende: 0.94
726. Merille Town: 0.93
727. Mombasa: 0.93
728. Baragoi: 0.93
729. Kilifi: 0.93
730. Isiolo town: 0.93
731. Kiwanja: 0.92
732. Ngaremara: 0.92
733. Junda (Mombasa): 0.92
734. Garsen: 0.91
735. Kaanwa (Tharaka Nithi): 0.91
736. Kipsing CENTER: 0.91
737. Tarasaa: 0.91
738. Kipini: 0.91
739. Emali: 0.91
740. Illbissil Food Market (Kajiado): 0.91
741. Wamba: 0.91
742. Ngilai: 0.91
743. Shauri Yako: 0.91
744. Kisumu Ndogo (Mombasa): 0.90
745. Moroto (Mombasa): 0.90
746. Lolkuniyani: 0.90
747. Kalahari (Mombasa): 0.90
748. Lengusaka: 0.90
749. Lomut (West Pokot): 0.90
750. Kongowea (Mombasa): 0.90
751. Shonda (Mombasa): 0.90
752. Oldonyiro: 0.90
753. Kibwezi: 0.90
754. Wote town (Makueni): 0.90
755. Makueni: 0.90
756. Makutano (West Pokot): 0.90
757. Bangladesh (Mombasa): 0.90
758. Vanga (Kwale): 0.89
759. Kathonzweni (Makueni): 0.89
760. Lpus: 0.89
761. Mtito Andei: 0.89
762. Lodungokwe: 0.89
763. Kibuye (Kisumu): 0.89
764. Kisima: 0.87
765. Wakulima (Nakuru): 0.87
766. Porro: 0.87
767. Maralal: 0.86
768. Lolkunono: 0.86
769. Loosuk: 0.86
770. Suguta: 0.85
771. Kitui town (Kitui): 0.85
772. Eldoret town (Uasin Gishu): 0.84
773. Chemolingot: 0.84
774. Amaya: 0.84
775. Nginyang: 0.84
776. Lororo Centre: 0.83
777. Loboi Center: 0.82
778. Koriema: 0.82
779. Marigat town (Baringo): 0.82
780. Kimalel Center: 0.82

🏆 Highest average Food_price_index price: Lodwar town (1.13)
💰 Lowest average Food_price_index price: Kimalel Center (0.82)
------------------------------------------------------------

Insights From the Charts

1. Universal Price Shock (2022-2023)

The most striking feature across every single province and county is a dramatic and sharp increase in food prices starting around 2022 and peaking in 2023.

Nationwide Event: The fact that this spike occurs simultaneously in all regions (from Wajir in the North Eastern to Mombasa on the Coast, and Nakuru in the Rift Valley) strongly suggests a nationwide event, such as a severe drought, major economic factors (e.g., inflation, fuel prices), or global supply chain issues affecting the entire country.

Gradual Rise Pre-2020: Before this spike, the general trend from 2007 to 2020 was a slow, gradual increase in prices for all commodities.

Post-Peak Leveling: The data from 2023 towards 2025 indicates that prices have begun to fall slightly or level off from their extreme 2023 peaks, though they remain significantly higher than pre-2022 levels.

2. Commodity-Specific Price Tiers

The commodities exist in distinct price brackets:

Beans (beans_close): This is consistently the most expensive commodity by a significant margin in all regions. It also exhibits the highest volatility, with the most dramatic peaks and troughs, and it experienced the most extreme price surge during the 2022-2023 event.

Maize (maize_close) and Potatoes (potatoes_1kg_close): These two commodities are much cheaper than beans. Their prices track each other very closely across the entire 18-year period and in all regions. This high correlation suggests their prices are driven by similar market forces, and they may act as substitutes for one another.

Food Price Index (food_price_index_close): This red line remains consistently flat and low (around a value of 10-15) in every chart. This indicates it is not a direct price but likely a composite index measured against a base year, and it is not as volatile as the prices of these individual staple foods.

3. Regional Variations in Price Magnitude

While all regions follow the same pattern, the magnitude of the price spikes varies.

Highest Price Spikes: The provincial chart shows that the North Eastern, Coast, and Eastern provinces experienced the most severe price peaks for beans (beans_close), with average prices surging well above 150.

Supporting County Data: This is confirmed by the county-level charts. For example, Wajir (North Eastern) and Mombasa (Coast) show some of the most extreme peaks in bean prices.

Major Production Areas: Regions like the Rift Valley (e.g., Nakuru, Uasin Gishu counties) also saw severe price hikes, but the peak for beans appears to be slightly less extreme than in the North Eastern or Coast regions. This could imply that regions further from production centers or with logistical challenges (like North Eastern) face greater price volatility.

Inflation & Volatility

So for this phase, we will check out the inflation across the years for each food price, by province that is, which province has the most volatile inflation prices, then we’ll double up on county and market. For the visuals, maybe a bar graph

Visualizing Inflation by Time (Yearly & Monthly)

yearly_trends = df.groupby('year')[['inflation_beans','inflation_maize','inflation_potatoes','inflation_food_price_index']].mean().reset_index()
yearly_trends

Beans Inflation over the years (2008 - 2025)

# Filter out 2007 and enhance the visualization
sns.set_theme(style='whitegrid')

plt.figure(figsize=(12, 6))

# Filter data to remove 2007 and get non-NaN values
filtered_data = yearly_trends[
    (yearly_trends['year'] > 2007) & 
    (yearly_trends['inflation_beans'].notna())
]

# Create the bar plot
bars = sns.barplot(
    data=filtered_data,
    x='year',
    y='inflation_beans',
    color='#3498db',       # Blue for positive values
    edgecolor='#2980b9'    # Darker blue for edges
)

# Color negative bars in red
for i, bar in enumerate(bars.patches):
    if bar.get_height() < 0:
        bar.set_color('#e74c3c')    # Red for negative values
        bar.set_edgecolor('#c0392b') # Darker red for edges

# Enhance the visual style
plt.title('Beans Inflation in Kenya (2008–2025)', 
          fontsize=16, 
          pad=20,
          weight='bold')
plt.xlabel('Year', fontsize=12)
plt.ylabel('Inflation', fontsize=12)

# Customize grid
plt.grid(True, linestyle='--', alpha=0.3, axis='y')

# Rotate x-axis labels for better readability
plt.xticks(rotation=45)

# Add value labels on top/bottom of each bar
for i, row in enumerate(filtered_data.itertuples()):
    if pd.notna(row.inflation_beans):
        value = row.inflation_beans
        va = 'bottom' if value >= 0 else 'top'  # Adjust label position based on value
        plt.text(i, value, 
                f'{value:.1f}', 
                ha='center', 
                va=va,
                fontsize=10)

# Adjust layout
plt.tight_layout()

# Set y-axis limits with padding for both positive and negative values
valid_values = filtered_data['inflation_beans'].dropna()
if not valid_values.empty:
    max_val = max(valid_values)
    min_val = min(valid_values)
    plt.ylim(min_val * 1.1, max_val * 1.1)  # Add 10% padding on both ends

# Add horizontal line at y=0
plt.axhline(y=0, color='black', linestyle='-', linewidth=0.5, alpha=0.3)

plt.show()

Beans Inflation in Kenya (2008–2025)

Maize Inflation over the years (2008 - 2025)

# Filter out 2007 and enhance the visualization
sns.set_theme(style='whitegrid')

plt.figure(figsize=(12, 6))

# Filter data to remove 2007 and get non-NaN values
filtered_data = yearly_trends[
    (yearly_trends['year'] > 2007) & 
    (yearly_trends['inflation_maize'].notna())
]

# Create the bar plot
bars = sns.barplot(
    data=filtered_data,
    x='year',
    y='inflation_maize',
    color='#3498db',       # Blue for positive values
    edgecolor='#2980b9'    # Darker blue for edges
)

# Color negative bars in red
for i, bar in enumerate(bars.patches):
    if bar.get_height() < 0:
        bar.set_color('#e74c3c')    # Red for negative values
        bar.set_edgecolor('#c0392b') # Darker red for edges

# Enhance the visual style
plt.title('Maize Inflation in Kenya (2008–2025)', 
          fontsize=16, 
          pad=20,
          weight='bold')
plt.xlabel('Year', fontsize=12)
plt.ylabel('Inflation', fontsize=12)

# Customize grid
plt.grid(True, linestyle='--', alpha=0.3, axis='y')

# Rotate x-axis labels for better readability
plt.xticks(rotation=45)

# Add value labels on top/bottom of each bar
for i, row in enumerate(filtered_data.itertuples()):
    if pd.notna(row.inflation_maize):
        value = row.inflation_maize
        va = 'bottom' if value >= 0 else 'top'  # Adjust label position based on value
        plt.text(i, value, 
                f'{value:.1f}', 
                ha='center', 
                va=va,
                fontsize=10)

# Adjust layout
plt.tight_layout()

# Set y-axis limits with padding for both positive and negative values
valid_values = filtered_data['inflation_maize'].dropna()
if not valid_values.empty:
    max_val = max(valid_values)
    min_val = min(valid_values)
    plt.ylim(min_val * 1.1, max_val * 1.1)  # Add 10% padding on both ends

# Add horizontal line at y=0
plt.axhline(y=0, color='black', linestyle='-', linewidth=0.5, alpha=0.3)

plt.show()

Maize Inflation in Kenya (2008–2025)

Potatoes Inflation over the years (2008 - 2025)

# Filter out 2007 and enhance the visualization
sns.set_theme(style='whitegrid')

plt.figure(figsize=(12, 6))

# Filter data to remove 2007 and get non-NaN values
filtered_data = yearly_trends[
    (yearly_trends['year'] > 2007) & 
    (yearly_trends['inflation_potatoes'].notna())
]

# Create the bar plot
bars = sns.barplot(
    data=filtered_data,
    x='year',
    y='inflation_potatoes',
    color='#3498db',       # Blue for positive values
    edgecolor='#2980b9'    # Darker blue for edges
)

# Color negative bars in red
for i, bar in enumerate(bars.patches):
    if bar.get_height() < 0:
        bar.set_color('#e74c3c')    # Red for negative values
        bar.set_edgecolor('#c0392b') # Darker red for edges

# Enhance the visual style
plt.title('Potatoes Inflation in Kenya (2008–2025)', 
          fontsize=16, 
          pad=20,
          weight='bold')
plt.xlabel('Year', fontsize=12)
plt.ylabel('Inflation', fontsize=12)

# Customize grid
plt.grid(True, linestyle='--', alpha=0.3, axis='y')

# Rotate x-axis labels for better readability
plt.xticks(rotation=45)

# Add value labels on top/bottom of each bar
for i, row in enumerate(filtered_data.itertuples()):
    if pd.notna(row.inflation_potatoes):
        value = row.inflation_potatoes
        va = 'bottom' if value >= 0 else 'top'  # Adjust label position based on value
        plt.text(i, value, 
                f'{value:.1f}', 
                ha='center', 
                va=va,
                fontsize=10)

# Adjust layout
plt.tight_layout()

# Set y-axis limits with padding for both positive and negative values
valid_values = filtered_data['inflation_potatoes'].dropna()
if not valid_values.empty:
    max_val = max(valid_values)
    min_val = min(valid_values)
    plt.ylim(min_val * 1.1, max_val * 1.1)  # Add 10% padding on both ends

# Add horizontal line at y=0
plt.axhline(y=0, color='black', linestyle='-', linewidth=0.5, alpha=0.3)

plt.show()

Potatoes Inflation in Kenya (2008–2025)

Inflation Trends over the years (2008 - 2025)