scripts/combine_datasets.py

#!/usr/bin/env python3
"""
Script to combine DES District Data 2023-24 and crop yield datasets.

This script will:
1. Load both CSV files
2. Standardize column names and formats
3. Combine the datasets
4. Handle missing values appropriately
5. Save the combined dataset
"""

import pandas as pd
import numpy as np
import os
from pathlib import Path

def load_and_clean_des_data(file_path):
    """Load and clean the DES District data."""
    print("Loading DES District Data 2023-24...")
    
    # Read the CSV file
    df_des = pd.read_csv(file_path)
    
    # Clean column names - remove quotes if present
    df_des.columns = df_des.columns.str.strip('"')
    
    # Standardize column names
    df_des = df_des.rename(columns={
        'Area-2023-24': 'Area',
        'Production-2023-24': 'Production',
        'Yield-2023-24': 'Yield'
    })
    
    # Add missing columns with default values
    df_des['Crop_Year'] = 2023
    df_des['Annual_Rainfall'] = np.nan
    df_des['Fertilizer'] = np.nan
    df_des['Pesticide'] = np.nan
    
    # Convert data types
    df_des['Area'] = pd.to_numeric(df_des['Area'], errors='coerce')
    df_des['Production'] = pd.to_numeric(df_des['Production'], errors='coerce')
    df_des['Yield'] = pd.to_numeric(df_des['Yield'], errors='coerce')
    
    # Clean string columns
    df_des['State'] = df_des['State'].str.strip('"')
    df_des['District'] = df_des['District'].str.strip('"')
    df_des['Crop'] = df_des['Crop'].str.strip('"')
    df_des['Season'] = df_des['Season'].str.strip('"')
    
    # Reorder columns to match the target structure
    df_des = df_des[['Crop', 'Crop_Year', 'Season', 'State', 'District', 
                     'Area', 'Production', 'Annual_Rainfall', 'Fertilizer', 
                     'Pesticide', 'Yield']]
    
    print(f"DES data loaded: {len(df_des)} rows")
    return df_des

def load_and_clean_crop_yield_data(file_path):
    """Load and clean the crop yield data."""
    print("Loading crop yield data...")
    
    # Read the CSV file
    df_crop = pd.read_csv(file_path)
    
    # Add District column (not present in original data)
    df_crop['District'] = np.nan
    
    # Clean Season column - standardize formatting
    df_crop['Season'] = df_crop['Season'].str.strip()
    
    # Convert data types
    df_crop['Area'] = pd.to_numeric(df_crop['Area'], errors='coerce')
    df_crop['Production'] = pd.to_numeric(df_crop['Production'], errors='coerce')
    df_crop['Yield'] = pd.to_numeric(df_crop['Yield'], errors='coerce')
    df_crop['Annual_Rainfall'] = pd.to_numeric(df_crop['Annual_Rainfall'], errors='coerce')
    df_crop['Fertilizer'] = pd.to_numeric(df_crop['Fertilizer'], errors='coerce')
    df_crop['Pesticide'] = pd.to_numeric(df_crop['Pesticide'], errors='coerce')
    
    # Reorder columns to match the target structure
    df_crop = df_crop[['Crop', 'Crop_Year', 'Season', 'State', 'District', 
                       'Area', 'Production', 'Annual_Rainfall', 'Fertilizer', 
                       'Pesticide', 'Yield']]
    
    print(f"Crop yield data loaded: {len(df_crop)} rows")
    return df_crop

def standardize_seasons(df):
    """Standardize season names across both datasets."""
    season_mapping = {
        'Kharif     ': 'Kharif',
        'Rabi       ': 'Rabi',
        'Summer     ': 'Summer',
        'Whole Year ': 'Whole Year',
        'Autumn     ': 'Autumn',
        'Winter     ': 'Winter',
        'Total': 'Total'
    }
    
    df['Season'] = df['Season'].replace(season_mapping)
    return df

def combine_datasets(df_des, df_crop):
    """Combine the two datasets."""
    print("Combining datasets...")
    
    # Standardize seasons in both datasets
    df_des = standardize_seasons(df_des)
    df_crop = standardize_seasons(df_crop)
    
    # Combine the datasets
    combined_df = pd.concat([df_des, df_crop], ignore_index=True)
    
    # Sort by State, Crop, Year, Season
    combined_df = combined_df.sort_values(['State', 'Crop', 'Crop_Year', 'Season'])
    
    print(f"Combined dataset: {len(combined_df)} rows")
    return combined_df

def generate_summary_stats(df):
    """Generate summary statistics for the combined dataset."""
    print("\n" + "="*50)
    print("DATASET SUMMARY")
    print("="*50)
    
    print(f"Total records: {len(df):,}")
    print(f"Date range: {df['Crop_Year'].min()}-{df['Crop_Year'].max()}")
    print(f"Number of states: {df['State'].nunique()}")
    print(f"Number of crops: {df['Crop'].nunique()}")
    print(f"Number of seasons: {df['Season'].nunique()}")
    print(f"Number of districts: {df['District'].nunique()}")
    
    print("\nTop 10 States by record count:")
    print(df['State'].value_counts().head(10))
    
    print("\nTop 10 Crops by record count:")
    print(df['Crop'].value_counts().head(10))
    
    print("\nSeasons:")
    print(df['Season'].value_counts())
    
    print("\nYears covered:")
    print(df['Crop_Year'].value_counts().sort_index())
    
    print("\nData completeness:")
    missing_data = df.isnull().sum()
    missing_percent = (missing_data / len(df)) * 100
    completeness = pd.DataFrame({
        'Missing_Count': missing_data,
        'Missing_Percent': missing_percent,
        'Complete_Percent': 100 - missing_percent
    })
    print(completeness)

def main():
    """Main function to combine the datasets."""
    # Define file paths
    des_file = "DES-District-Data-For-2023-24 (1).csv"
    crop_file = "crop_yield.csv"
    output_file = "combined_crop_data.csv"
    
    # Check if files exist
    if not os.path.exists(des_file):
        print(f"Error: {des_file} not found!")
        return
    if not os.path.exists(crop_file):
        print(f"Error: {crop_file} not found!")
        return
    
    try:
        # Load and clean both datasets
        df_des = load_and_clean_des_data(des_file)
        df_crop = load_and_clean_crop_yield_data(crop_file)
        
        # Combine datasets
        combined_df = combine_datasets(df_des, df_crop)
        
        # Save the combined dataset
        print(f"Saving combined dataset to {output_file}...")
        combined_df.to_csv(output_file, index=False)
        
        # Generate summary statistics
        generate_summary_stats(combined_df)
        
        print(f"\nSuccess! Combined dataset saved as '{output_file}'")
        print(f"Combined dataset shape: {combined_df.shape}")
        
        # Save a sample of the data for inspection
        sample_file = "combined_crop_data_sample.csv"
        sample_df = combined_df.head(100)
        sample_df.to_csv(sample_file, index=False)
        print(f"Sample data (first 100 rows) saved as '{sample_file}'")
        
    except Exception as e:
        print(f"Error: {str(e)}")
        raise

if __name__ == "__main__":
    main()