import pandas as pd
import numpy as np

import seaborn as sns
import matplotlib.pyplot as plt

from datetime import date
from datetime import datetime, timedelta
import os
for dirname, _, filenames in os.walk('/kaggle/input'):
    for filename in filenames:
        print(os.path.join(dirname, filename))
pd.set_option('display.max_columns', 500)
"""
## Reading all input file and 
- printing their columns
- modifying column names for some inputs
"""
df_current = pd.read_csv("/kaggle/input/raw_data12.csv",engine='python')
#df_current[(df_current['Date Announced'] == '30/07/2020') & total_cases_filter]['Num Cases'].sum()
df_current.columns
df_18July_22July = pd.read_csv("/kaggle/input/raw_data11.csv",engine='python')
df_14July_17July = pd.read_csv("/kaggle/input/raw_data10.csv",engine='python')
df_8July_13July = pd.read_csv("/kaggle/input/raw_data9.csv",engine='python')
df_8July_13July.columns
df_1July_7July = pd.read_csv("/kaggle/input/raw_data8.csv",engine='python')
df_1July_7July.columns
df_20June_30June = pd.read_csv("/kaggle/input/raw_data7.csv",engine='python')
df_20June_30June.columns
df_5June_19June = pd.read_csv("/kaggle/input/raw_data6.csv",engine='python')
df_5June_19June.columns
df_24May_4June = pd.read_csv("/kaggle/input/raw_data5.csv",engine='python')
df_24May_4June.columns
df_10May_23May = pd.read_csv("/kaggle/input/raw_data4.csv",engine='python')
df_10May_23May.columns
df_27April_09May = pd.read_csv("/kaggle/input/raw_data3.csv",engine='python')
df_27April_09May.columns
df_20April_26April = pd.read_csv("/kaggle/input/raw_data2.csv",engine='python')
df_20April_26April.columns
df_20April_26April.rename(columns={"Num cases": "Num Cases"}, inplace=True)
df_till_19thApril = pd.read_csv("/kaggle/input/raw_data1.csv",engine='python')
df_till_19thApril.columns
df_till_19thApril.rename(columns={"Num cases": "Num Cases"}, inplace=True)
"""
### Mergin Data
"""
frames = [df_current, df_18July_22July,df_14July_17July,df_8July_13July,df_1July_7July,df_20June_30June, df_5June_19June,df_24May_4June, df_10May_23May,df_27April_09May,df_20April_26April,df_till_19thApril]
"""
#### Merged data 
"""
result = pd.concat(frames).reset_index()
result.drop('index',axis=1,inplace=True)
"""
### Analysis Start
"""
result.info()
result.head()
days_to_subtract = 1
d = datetime.today() - timedelta(days=days_to_subtract)

# dd/mm/YY
today = d.strftime("%d/%m/%Y")
result[result['Date Announced'] == today]['Num Cases'].sum()
#result['Current Status'].unique()
### Below query should give almost similar confirmed cases as in https://www.covid19india.org/ (1 day before)
total_cases_filter = (result['Current Status']!='Deceased') & (result['Current Status']!='Recovered') 
result[total_cases_filter]['Num Cases'].sum()
"""
## Univariate Analysis
"""
"""
### Analysis on entry_id
"""
result[result['Entry_ID'].isnull()].head()
result.Entry_ID.nunique()
"""
#### Entry_id has only unique value. So this column is not of much use
"""
"""
### 'State Patient Number' Analysis 
"""
result['State Patient Number'].isnull().sum()
result[(result['State Patient Number'].str.startswith('AN', na = False))].head()
result['State Patient Number'].str[:2].unique()
"""
#### State Patient Number is also of not much use as it contains statecode followed by unique id

#### Not so useful but inference made out of this column is below:
- States which actaully captures patient information using patient id.
"""
"""
### Some analysis using 'Date Announced'
"""
result['Date Announced'].isnull().sum()
result['Date Announced'] = pd.to_datetime(result['Date Announced'], format='%d/%m/%Y')
# Number of cases in each month 
df_month = result[total_cases_filter].groupby(result['Date Announced'].dt.month_name())['Num Cases'].sum().reset_index().sort_values('Num Cases')
sns.barplot(x = 'Date Announced',y='Num Cases',data=df_month)
df_month
"""
#### Not doing much analysis on 'Date Announced' which is ordered categorical variable as covid19india.org website already done lot of analysis on that
"""
"""
### Analysis using 'Age Bracket'
"""
result['Age Bracket'].isnull().sum()
# Percentage of null data 
(result['Age Bracket'].isnull().sum()/len(result))*100
# Removing months string from age column 
result.loc[result['Age Bracket'].str.contains('onth', na=False),'Age Bracket']=(result[result['Age Bracket'].str.contains('onth', na=False)]['Age Bracket'].str[0].astype(int))/10
# Removing ages which are given as range
result.loc[result['Age Bracket']=='28-35','Age Bracket'] = (28+35)/2
# Converting 'Age Bracket' column to numeric
result['Age Bracket'] = pd.to_numeric(result['Age Bracket'],errors='coerce')
"""
### Creating bins for age
"""
result['Age_Bracket_bins']= pd.cut(result['Age Bracket'], bins=[0,4,9,14,19,24,29,34,39,44,49,54,59,64,69,74,79,84,89,94,99], 
             include_lowest=True)
# Active cases age analysis
plt.figure(figsize=(30,10))
sns.countplot(x = 'Age_Bracket_bins', data=result[(~result['Age Bracket'].isnull()) & (total_cases_filter) & (result['Num Cases'] != 0) & 
       (result['Num Cases'] != 5)] )
plt.show()
#plt.savefig('ex1.pdf')
# Deceased cases age analysis
plt.figure(figsize=(30,10))
sns.countplot(x = 'Age_Bracket_bins', data=result[(~result['Age Bracket'].isnull()) & (result['Current Status']=='Deceased') & (result['Num Cases'] != 0) & 
       (result['Num Cases'] != 5)] )
plt.show()
"""
#### Verdict : Age bracket column has following problems:
- Data is not acurate as there are spikes at the multiple of age 5
- Some data has doesn't have proper age mentioned.
- Lot of missing data is there alsmost 60%
- some data contains months as suffix
"""
"""
### Analysis on 'Gender' column
"""
# Percentage of null data 
(result['Gender'].isnull().sum()/len(result))*100
result.Gender.unique()
result[result['Gender'] == 'Non-Binary'].shape
result['Gender'] = result.Gender.str.strip()
result[(~result['Gender'].isnull()) & (total_cases_filter)].shape
result[total_cases_filter].groupby('Gender')['Num Cases'].sum().plot.bar()
"""
#### Verdict : Gender
- Only around 47% cases have gender mentioned for it. 
- Lot of missing data
"""
"""
### 'Detected City' Analysis
"""
# Percentage of null data 
(result['Detected City'].isnull().sum()/len(result))*100
result.groupby('Detected City')['Num Cases'].sum().nlargest(10)
result[result['Detected City'] == 'Police Line'].head()
"""
#### Verdict : Detected City
- Since there is only 9% valid data not much analysis can be done here.
"""
"""
### 'Detected District' Analysis
"""
# Percentage of null data 
(result['Detected District'].isnull().sum()/len(result))*100
result['Detected District'] = result['Detected District'].str.title()
#a = result['Detected District'].astype(str).unique()
#sorted(a)
# Converting Others as Unassigned for Detected District
result.loc[result['Detected District']=='Others','Detected District'] = 'Unassigned'
# Converting Other State as Unassigned for Detected District
result.loc[result['Detected District']=='Other State','Detected District'] = 'Unassigned'
# Converting Agar malwa district to Agar Malwa for 'Detected District'
result.loc[result['Detected District']=='Agar malwa','Detected District'] = 'Agar Malwa'
# Converting Airport quarantine district to Airport Quarantine for 'Detected District'
result.loc[result['Detected District']=='Airport quarantine','Detected District'] = 'Airport Quarantine'
# Mapping Airport Quarantine to a new column and converting Airport Quarantine to Unassigned 
result['Airport_Quarantine'] = result['Detected District'].apply(lambda x: 1 if x == 'Airport Quarantine' else 0)
result.loc[result['Detected District']=='Airport Quarantine','Detected District'] = 'Unassigned'
# Mapping BSF Camp to a new column and converting BSF Camp to Unassigned 
result['BSF_Camp'] = result['Detected District'].apply(lambda x: 1 if x == 'BSF Camp' else 0)
result.loc[result['Detected District']=='BSF Camp','Detected District'] = 'Unassigned'
# Mapping Evacuees to a new column and converting Evacuees to Unassigned 
result['Evacuees'] = result['Detected District'].apply(lambda x: 1 if x == 'Evacuees' else 0)
result.loc[result['Detected District']=='Evacuees','Detected District'] = 'Unassigned'
# Mapping Foreign Evacuees to a new column and converting Foreign Evacuees to Unassigned 
result['Foreign_Evacuees'] = result['Detected District'].apply(lambda x: 1 if x == 'Foreign Evacuees' else 0)
result.loc[result['Detected District']=='Foreign Evacuees','Detected District'] = 'Unassigned'
# Mapping Italians to a new column and converting Italians to Unassigned 
result['Italians'] = result['Detected District'].apply(lambda x: 1 if x == 'Italians' else 0)
result.loc[result['Detected District']=='Italians','Detected District'] = 'Unassigned'
# Mapping Railway Quarantine to a new column and converting Railway Quarantine to Unassigned 
result['Railway_Quarantine'] = result['Detected District'].apply(lambda x: 1 if x == 'Railway Quarantine' else 0)
result.loc[result['Detected District']=='Railway Quarantine','Detected District'] = 'Unassigned'
# Converting Unknown to Unassigned for Detected District 
result.loc[result['Detected District']=='Unknown','Detected District'] = 'Unassigned'
# Filling NA with Unassigned
values = {'Detected District': 'Unassigned'}
result.fillna(value=values,inplace=True)
"""
### Analsis on 'Detected State' and 'State Code'
"""
# Percentage of null data 
round((result['Detected State'].isnull().sum()/len(result))*100,2)
state = result['Detected State'].astype(str).unique()
state
result[result['Detected State'].isnull()].head()
# Percentage of null data 
round((result['State code'].isnull().sum()/len(result))*100,2)
"""
### For 'Deteted_State' and 'State_code' below is observation
* Only 35 and 34 rows are having nan value respectively 
* Only 66 rows are having unassigned state as column value for 'Detected_State' and 'State_code'
"""
"""
## Analysis on Num_Cases
"""
# Percentage of null data 
round((result['Num Cases'].isnull().sum()/len(result))*100,2)
round(result[result['Num Cases'] == 1]['Num Cases'].count()/len(result)*100,2)
round(result[result['Num Cases'] > 1]['Num Cases'].count()/len(result)*100,2)
round(result[result['Num Cases'] <= 0]['Num Cases'].count()/len(result)*100,2)
improper_entry = result[(result['Num Cases'] > 1) & total_cases_filter]['Num Cases'].sum() + result[(result['Num Cases'] <= 0) & total_cases_filter]['Num Cases'].sum()
improper_entry
proper_entry = result[(result['Num Cases'] == 1) & total_cases_filter]['Num Cases'].sum()
proper_entry
result['Num Cases'].value_counts()

"""
### Analysis on 'Num Cases'
- 90% data have num of cases not equals to 1
- Although this column has only 1% missing data.
"""
"""
## Current Status
"""
# Percentage of null data 
round((result['Current Status'].isnull().sum()/len(result))*100,2)
result['Current Status'].fillna('Missing', inplace=True)
df_current_status = result.groupby('Current Status')['Num Cases'].sum().reset_index()
df_current_status
plt.figure(figsize=(20,6))
sns.barplot(x='Current Status',y='Num Cases',data=df_current_status)
# Converting Migrated_Other to Migrated for Current Status 
result.loc[result['Current Status']=='Migrated_Other','Current Status'] = 'Migrated'
# Converting Migrated_other to Migrated for Current Status 
result.loc[result['Current Status']=='Migrated_other','Current Status'] = 'Migrated'
"""
## Analysis on 'Contracted from which Patient (Suspected)'
"""
result.rename(columns = {"Contracted from which Patient (Suspected)":"Contracted_From"},inplace= True)
# Percentage of null data 
round((result['Contracted_From'].isnull().sum()/len(result))*100,2)
"""
### No analysis is required as it has 99% null data.
"""
"""
## Analysis on Notes
"""
# Percentage of null data 
round((result['Notes'].isnull().sum()/len(result))*100,2)
result[(~result['Notes'].isnull()) & total_cases_filter]['Num Cases'].sum()
result[result['Notes'].str.contains("suffering from", na=False)]['Num Cases'].sum()
result[~result['Notes'].isnull()]['Entry_ID'].count()
"""
### Contains other disease analysis using notes but 
- has only 97 total count and so not much helpful. 
"""
result['Other_disease_2'] = result['Notes'].str.split("suffering from").str[1].str.split("and").str[1]
result['Other_disease_1']  = result['Notes'].str.split("suffering from").str[1].str.split("and").str[0]
result['Other_disease_1'] = result['Other_disease_1'].str.strip()
result['Other_disease_1'] = result['Other_disease_1'].str.rstrip(".")
result['Other_disease_1'] = result['Other_disease_1'].str.strip()
result['Other_disease_1'] = result['Other_disease_1'].str.title()
"""
#### Other Other_disease_2 analysis
"""
result['Other_disease_2'] = result['Other_disease_2'].str.rstrip(".")
result['Other_disease_2'] = result['Other_disease_2'].str.strip()
# easy mrthod for Split and expand
#result[~result['Notes'].isnull()]['Notes'].str.split("suffering from").str[1].str.split("and",expand=True)
result['Notes'] = result['Notes'].str.lower()
result[(~result['Notes'].isnull()) & 
       (~result['Notes'].str.contains("details awaited", na=False)) & 
      (~result['Notes'].str.contains("suffering ", na=False)) & 
      (~result['Notes'].str.contains("correction ", na=False)) &
      (~result['Notes'].str.contains("contact", na=False)) &
      (~result['Notes'].str.contains("travel", na=False)) &
      (~result['Notes'].str.contains("sari", na=False)) &
      (~result['Notes'].str.contains("influenza", na=False)) & 
       (~result['Notes'].str.contains("bulk", na=False)) &
       (~result['Notes'].str.contains("re con", na=False)) &
       (~result['Notes'].str.contains("reconciled", na=False)) &
       (~result['Notes'].str.contains("returnees", na=False)) &
       (~result['Notes'].str.contains("previous", na=False)) &
       (~result['Notes'].str.contains("returned", na=False)) &
       (~result['Notes'].str.contains("district wise", na=False)) &
       (~result['Notes'].str.contains("bsf", na=False)) &
       (result['Num Cases'] > 1.0) & total_cases_filter].sort_values('Num Cases',ascending=False).head()
print(result[(~result['Notes'].isnull()) & (result['Notes'].str.contains("correction ", na=False))]['Num Cases'].sum())
result[(~result['Notes'].isnull()) & (result['Notes'].str.contains("correction ", na=False))]['Num Cases'].count()
print(result[(~result['Notes'].isnull()) & (result['Notes'].str.contains("suffering ", na=False))]['Num Cases'].sum())
result[(~result['Notes'].isnull()) & (result['Notes'].str.contains("suffering ", na=False))]['Num Cases'].count()
print(result[(~result['Notes'].isnull()) & (result['Notes'].str.contains("details awaited", na=False))]['Num Cases'].sum())
result[(~result['Notes'].isnull()) & (result['Notes'].str.contains("details awaited", na=False))]['Num Cases'].count()
print(result[(~result['Notes'].isnull()) & (result['Notes'].str.contains("contact", na=False))]['Num Cases'].sum())
result[(~result['Notes'].isnull()) & (result['Notes'].str.contains("contact", na=False))]['Num Cases'].count()
print(result[(~result['Notes'].isnull()) & (result['Notes'].str.contains("travel", na=False))]['Num Cases'].sum())
result[(~result['Notes'].isnull()) & (result['Notes'].str.contains("travel", na=False))]['Num Cases'].count()
print(result[(~result['Notes'].isnull()) & (result['Notes'].str.contains("sari", na=False))]['Num Cases'].sum())

print(result[(~result['Notes'].isnull()) & (result['Notes'].str.contains("influenza", na=False))]['Num Cases'].sum())
result[(~result['Notes'].isnull()) & (result['Notes'].str.contains("influenza", na=False))]['Num Cases'].count()
print(result[(~result['Notes'].isnull()) & (result['Notes'].str.contains("bulk", na=False))]['Num Cases'].sum())
result[(~result['Notes'].isnull()) & (result['Notes'].str.contains("bulk", na=False))]['Num Cases'].count()
print(result[(~result['Notes'].isnull()) & (result['Notes'].str.contains("re con", na=False))]['Num Cases'].sum())
result[(~result['Notes'].isnull()) & (result['Notes'].str.contains("re con", na=False))]['Num Cases'].count()

print(result[(~result['Notes'].isnull()) & (result['Notes'].str.contains("reconciled", na=False))]['Num Cases'].sum())
result[(~result['Notes'].isnull()) & (result['Notes'].str.contains("reconciled", na=False))]['Num Cases'].count()
print(result[(~result['Notes'].isnull()) & (result['Notes'].str.contains("returnees", na=False))]['Num Cases'].sum())
result[(~result['Notes'].isnull()) & (result['Notes'].str.contains("returnees", na=False))]['Num Cases'].count()
print(result[(~result['Notes'].isnull()) & (result['Notes'].str.contains("previous", na=False))]['Num Cases'].sum())
result[(~result['Notes'].isnull()) & (result['Notes'].str.contains("previous", na=False))]['Num Cases'].count()
print(result[(~result['Notes'].isnull()) & (result['Notes'].str.contains("returned", na=False))]['Num Cases'].sum())
result[(~result['Notes'].isnull()) & (result['Notes'].str.contains("returned", na=False))]['Num Cases'].count()
print(result[(~result['Notes'].isnull()) & (result['Notes'].str.contains("district wise", na=False))]['Num Cases'].sum())
result[(~result['Notes'].isnull()) & (result['Notes'].str.contains("district wise", na=False))]['Num Cases'].count()
print(result[(~result['Notes'].isnull()) & (result['Notes'].str.contains("bsf", na=False))]['Num Cases'].sum())
result[(~result['Notes'].isnull()) & (result['Notes'].str.contains("bsf", na=False))]['Num Cases'].count()
"""
### Further Information can be found from 'Notes' column
- As there are 555 more rows.
- Although it hard to group now
- Also not much trend can be found out of this column as only 157069.0 have notes as of 31st July
"""
result.info()
"""
## Analysis on Source_1 column now
"""
# Percentage of null data 
round((result['Source_1'].isnull().sum()/len(result))*100,2)
result['Source_1_derived'] = result['Source_1'].str.split("/").str[2]
result['Source_1_test'] = result['Source_1'].str.split("/").str[1]
result[(~result['Source_1'].isnull()) & (~result['Source_1'].str.startswith('http',na=False))]['Source_1'].str.split("/").str[0].unique()
#result[result['Source_1_test'].str.contains('wp',na=False)]