Dataframe Det1 stores details about a state such as TaxCollection.

Q. Dataframe Det1 stores details about a state such as TaxCollection. Dabts, Surplus, Int_rate for its district D1, D2, D4, D7, Dataframe Det2 also stores details of districts D1, D2, D5, D6, D7 details as PerCapita, Income, Population, Unemployment.

Write a script that merges the details from two dataframes district-wise and then calculate the average working Income as AvgIncome / (Population-UnEmployment). Make sure to handle missing data average efficiently.

Answer = 

import pandas as pd

# Create DataFrames Det1 and Det2
data1 = {'District': ['D1', 'D2', 'D4', 'D7'],
         'TaxCollection': [1000, 1500, 800, 1200],
         'Debts': [500, 600, 300, 400],
         'Surplus': [500, 900, 500, 800],
         'Int_rate': [5.5, 6.0, 5.2, 5.7]}
data2 = {'District': ['D1', 'D2', 'D5', 'D6', 'D7'],
         'PerCapita': [2000, 2500, 1800, 2100, 2200],
         'Income': [50000, 60000, 45000, 48000, 55000],
         'Population': [25000, 28000, 22000, 24000, 30000],
         'Unemployment': [1000, 1200, 800, None, 1500]}

Det1 = pd.DataFrame(data1)
Det2 = pd.DataFrame(data2)

# Merge the dataframes based on the 'District' column
merged_df = pd.merge(Det1, Det2, on='District', how='outer')

# Calculate 'AvgIncome' while handling missing data
merged_df['AvgIncome'] = merged_df['Income'] / (merged_df['Population'] - merged_df['Unemployment'])

# Fill missing data with 0
merged_df = merged_df.fillna(0)  

print(merged_df)

Output :

  District  TaxCollection  Debts  ...  Population  Unemployment  AvgIncome
0       D1         1000.0  500.0  ...     25000.0        1000.0   2.083333
1       D2         1500.0  600.0  ...     28000.0        1200.0   2.238806
2       D4          800.0  300.0  ...         0.0           0.0   0.000000
3       D7         1200.0  400.0  ...     30000.0        1500.0   1.929825
4       D5            0.0    0.0  ...     22000.0         800.0   2.122642
5       D6            0.0    0.0  ...     24000.0           0.0   0.000000
[6 rows x 10 columns]

>>>