#!/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()