Spaces:
Sleeping
Sleeping
File size: 6,782 Bytes
bbd5f9c | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 | #!/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()
|