import pandas as pd

# Sample data for illustration
data = [
    [1, 'Male', 30, True, 123, False, '1-2 years', True],
    [2, 'Female', 25, False, 456, True, '< 1>    # ... more data ...
]

# Column names
column_names = [
    'customer_id', 'Gender', 'age', 'driving_licence_present',
    'region_code', 'previously_insured', 'vehicle_age', 'vehicle_damage'
]

# Create a DataFrame with data and column names
customer_details_df = pd.DataFrame(data, columns=column_names)

Print the DataFrame
print(customer_details_df)

# Sample data for illustration
policy_data = [
    [1, 'Comprehensive'],
    [2, 'Third Party'],
    # ... more data ...
]

# Column names
policy_column_names = ['customer_id', 'policy_type']

# Create a DataFrame with policy data and column names
customer_policy_df = pd.DataFrame(policy_data, columns=policy_column_names)

Print the DataFrame
print(customer_policy_df)

import pandas as pd

Sample data for illustration
policy_data = [
    [1, 5000, 123, 50, 'Yes'],
    [2, 3000, 456, 30, 'No'],

 

import pandas as pd

# Load your customer details DataFrame (replace 'your_data.csv' with the actual file path)
customer_details_df = pd.read_csv('your_data.csv')

# Generate a summary of count of null values column wise
null_summary = customer_details_df.isnull().sum()

# Print the summary of null values
print("Summary of Null Values:")
print(null_summary)

# Drop rows with null values in the 'customer_id' column
customer_details_df = customer_details_df.dropna(subset=['customer_id'])

# Replace null values in numeric columns with mean
numeric_columns = ['age', 'region_code']
for column in numeric_columns:
    mean_value = customer_details_df[column].mean()
    customer_details_df[column].fillna(mean_value, inplace=True)

# Replace null values in categorical columns with mode
categorical_columns = ['Gender', 'driving_licence_present', 'previously_insured', 'vehicle_age', 'vehicle_damage']
for column in categorical_columns:
    mode_value = customer_details_df[column].mode()[0]
    customer_details_df[column].fillna(mode_value, inplace=True)

# Print the cleaned DataFrame
print("Cleaned DataFrame:")
print(customer_details_df)

    # ... more data ...
]

# Column names
policy_column_names = [
    'customer_id', 'annual_premium', 'sales_channel_code',
    'vintage', 'response'
]

# Create a DataFrame with policy data and column names
customer_policy_df = pd.DataFrame(policy_data, columns=policy_column_names)

Print the DataFrame
print(customer_policy_df)

import pandas as pd

# Load customer details DataFrame (replace 'customer_details.csv' with the actual file path)
customer_details_df = pd.read_csv('customer_details.csv')

# Load customer policy DataFrame (replace 'customer_policy.csv' with the actual file path)
customer_policy_df = pd.read_csv('customer_policy.csv')

# Merge the two DataFrames using 'customer_id' as the key
master_df = pd.merge(customer_details_df, customer_policy_df, on='customer_id', how='inner')

# Print the master DataFrame
print(master_df)

import pandas as pd

# Load the master DataFrame (replace 'master_table.csv' with the actual file path)
master_df = pd.read_csv('master_table.csv')

# i. Gender wise average annual premium
gender_avg_premium = master_df.groupby('Gender')['annual_premium'].mean()

# ii. Age wise average annual premium
age_avg_premium = master_df.groupby('age')['annual_premium'].mean()

# iii. Check gender balance
gender_balance = master_df['Gender'].value_counts(normalize=True)

# Print the results
print("Gender wise average annual premium:")
print(gender_avg_premium)

print("\nAge wise average annual premium:")
print(age_avg_premium)

print("\nGender balance:")
print(gender_balance)