# This Python 3 environment comes with many helpful analytics libraries installed
# It is defined by the kaggle/python Docker image: https://github.com/kaggle/docker-python
# For example, here's several helpful packages to load

import numpy as np # linear algebra
import pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)

# Input data files are available in the read-only "../input/" directory
# For example, running this (by clicking run or pressing Shift+Enter) will list all files under the input directory

import os
for dirname, _, filenames in os.walk('/kaggle/input'):
    for filename in filenames:
        print(os.path.join(dirname, filename))

# You can write up to 20GB to the current directory (/kaggle/working/) that gets preserved as output when you create a version using "Save & Run All" 
# You can also write temporary files to /kaggle/temp/, but they won't be saved outside of the current session
import pandas as pd
import numpy as np
import seaborn as sns
import matplotlib.pyplot as plt
df=pd.read_csv('../input/ataljalyojana/Atal Jal 31 March 2021 .xlsx - Sheet1.csv')
df.head()
df.State=df['State'].str.lower()
pd.crosstab(index=df['State'], columns=df['Well Depth'], margins=True)
"""
# inference
* we can see that UP has maximum number of 0 data for well depth . We have to find is this a random or not
"""
pd.crosstab(index=df['State'], columns=df['Pre_2015'], margins=True)
df[['State', 'Well Depth']].describe()
df.columns
pd.crosstab(index=df['State'], columns=df['TYPE'], margins=True)
(pd.crosstab(index=df['State'], columns=df['Aquifier'], margins=True)).T
"""
# State wise analysis using contigency table
"""
df['State'].value_counts()
df_guj=df[df['State']=='gujarat']
df_maha=df[df['State']=='maharashtra']
df_raj=df[df['State']=='rajasthan']
df_har=df[df['State']=='haryana']
df_mp=df[df['State']=='madhya pradesh']
df_up=df[df['State']=='uttar pradesh']
df_kar=df[df['State']=='karnataka']
col=['Well Depth', 'Pre_2015','Pre_2016','Pre_2017', 'Pre_2018', 'Pre_2019', 'Pst_2015',
   'Pst_2016','Pst_2017','Pst_2018','Pst_2019']

df_guj[col].describe().T
df_guj[col].describe().T.plot(kind='bar')
df_guj['Well Depth'].describe()
df_raj['Well Depth'].describe().T
df_har['Well Depth'].describe().T
df_mp['Well Depth'].describe().T
df_up['Well Depth'].describe().T
df_kar['Well Depth'].describe().T
df_maha['Well Depth'].describe().T
"""
# Tools to visuaize the columns data
"""
plt.style.use('seaborn-darkgrid')

orange_black = ['#fdc029', '#df861d', 'FF6347', '#aa3d01',
                '#a30e15', '#800000', '#171820']

plt.rcParams['figure.figsize'] = (10,5) 
plt.rcParams['figure.facecolor'] = '#FFFACD' 
plt.rcParams['axes.facecolor'] = 'FFFFE0' 
plt.rcParams['axes.grid'] = True 
plt.rcParams['grid.color'] = orange_black[3]
plt.rcParams['grid.linestyle'] = '--'
def tools_visuaize(data, ):
    
    plt.figure(figsize=(10,10))
    plt.subplot(3,1,1)
    sns.kdeplot(data=data, fill=True)
    plt.show()
    
    plt.figure(figsize=(10,10))
    plt.subplot(3,1,2)
    sns.barplot(data=data)
    plt.show()
    
    plt.figure(figsize=(10,10))
    plt.subplot(3,1,3)
    sns.lineplot(data=data)
    plt.show()

    
"""
# Columns wise Analysis 
"""
col1={'State':['Gujarat', 'Maharastra', 'Karnataka', 'Harayna','MP', 'UP', 'Rajasthan'],
      'Mean_well_depth':[118.984549,17.182362,87.388121,98.191765,16.751989,10.652172,52.581923],
       'STD_depth':[69.617500,11.306925,64.949886,81.175749,14.420278,11.695323,44.848036],
        "Median":[108.500000,13.750000,62.900000,82.500000,12.625000,9.080000,35.050000]}
con_table=pd.DataFrame(col1)
con_table
tools_visuaize(con_table)
"""
# Pre_2015
"""
col2={'State':['Gujarat', 'Maharastra', 'Karnataka', 'Harayna','MP', 'UP', 'Rajasthan'],
      'Mean_Pre_2015_depth':[df_guj['Pre_2015'].mean(),df_maha['Pre_2015'].mean(),
                         df_kar['Pre_2015'].mean(),df_har['Pre_2015'].mean(),
                         df_mp['Pre_2015'].mean(),df_up['Pre_2015'].mean(),df_raj['Pre_2015'].mean()],
       'STD_depth_Pre_2015_depth':[df_guj['Pre_2015'].std(),df_maha['Pre_2015'].std(),
                         df_kar['Pre_2015'].std(),df_har['Pre_2015'].std(),
                         df_mp['Pre_2015'].std(),df_up['Pre_2015'].std(),df_raj['Pre_2015'].std()],
        "Median_Pre_2015_depth":[df_guj['Pre_2015'].median(),df_maha['Pre_2015'].median(),
                         df_kar['Pre_2015'].median(),df_har['Pre_2015'].median(),
                         df_mp['Pre_2015'].median(),df_up['Pre_2015'].median(),df_raj['Pre_2015'].median()]}
con_pre_2015=pd.DataFrame(col2)
con_pre_2015
tools_visuaize(con_pre_2015)
"""
# Pre_2016
"""
col3={'State':['Gujarat', 'Maharastra', 'Karnataka', 'Harayna','MP', 'UP', 'Rajasthan'],
      'Mean_Pre_2016_depth':[df_guj['Pre_2016'].mean(),df_maha['Pre_2016'].mean(),
                         df_kar['Pre_2016'].mean(),df_har['Pre_2016'].mean(),
                         df_mp['Pre_2016'].mean(),df_up['Pre_2016'].mean(),df_raj['Pre_2016'].mean()],
       'STD_depth_Pre_2016_depth':[df_guj['Pre_2016'].std(),df_maha['Pre_2016'].std(),
                         df_kar['Pre_2016'].std(),df_har['Pre_2016'].std(),
                         df_mp['Pre_2016'].std(),df_up['Pre_2016'].std(),df_raj['Pre_2016'].std()],
        "Median_Pre_2016_depth":[df_guj['Pre_2016'].median(),df_maha['Pre_2016'].median(),
                         df_kar['Pre_2016'].median(),df_har['Pre_2016'].median(),
                         df_mp['Pre_2016'].median(),df_up['Pre_2016'].median(),df_raj['Pre_2016'].median()]}
con_pre_2016=pd.DataFrame(col3)
con_pre_2016
tools_visuaize(con_pre_2016)
"""
# Pre_2017
"""
col4={'State':['Gujarat', 'Maharastra', 'Karnataka', 'Harayna','MP', 'UP', 'Rajasthan'],
      'Mean_Pre_2017_depth':[df_guj['Pre_2017'].mean(),df_maha['Pre_2017'].mean(),
                         df_kar['Pre_2017'].mean(),df_har['Pre_2017'].mean(),
                         df_mp['Pre_2017'].mean(),df_up['Pre_2017'].mean(),df_raj['Pre_2017'].mean()],
       'STD_depth_Pre_2017_depth':[df_guj['Pre_2017'].std(),df_maha['Pre_2017'].std(),
                         df_kar['Pre_2017'].std(),df_har['Pre_2017'].std(),
                         df_mp['Pre_2017'].std(),df_up['Pre_2017'].std(),df_raj['Pre_2017'].std()],
        "Median_Pre_2017_depth":[df_guj['Pre_2017'].median(),df_maha['Pre_2017'].median(),
                         df_kar['Pre_2017'].median(),df_har['Pre_2017'].median(),
                         df_mp['Pre_2017'].median(),df_up['Pre_2017'].median(),df_raj['Pre_2017'].median()]}
con_pre_2017=pd.DataFrame(col4)
con_pre_2017
tools_visuaize(con_pre_2017)
"""
# Pre_2018
"""
col5={'State':['Gujarat', 'Maharastra', 'Karnataka', 'Harayna','MP', 'UP', 'Rajasthan'],
      'Mean_Pre_2018_depth':[df_guj['Pre_2018'].mean(),df_maha['Pre_2018'].mean(),
                         df_kar['Pre_2018'].mean(),df_har['Pre_2018'].mean(),
                         df_mp['Pre_2018'].mean(),df_up['Pre_2018'].mean(),df_raj['Pre_2018'].mean()],
       'STD_depth_Pre_2018_depth':[df_guj['Pre_2018'].std(),df_maha['Pre_2018'].std(),
                         df_kar['Pre_2018'].std(),df_har['Pre_2018'].std(),
                         df_mp['Pre_2018'].std(),df_up['Pre_2018'].std(),df_raj['Pre_2018'].std()],
        "Median_Pre_2018_depth":[df_guj['Pre_2018'].median(),df_maha['Pre_2018'].median(),
                         df_kar['Pre_2018'].median(),df_har['Pre_2018'].median(),
                         df_mp['Pre_2018'].median(),df_up['Pre_2018'].median(),df_raj['Pre_2018'].median()]}
con_pre_2018=pd.DataFrame(col5)
con_pre_2018
tools_visuaize(con_pre_2018)
"""
# Pre_2019
"""
col6={'State':['Gujarat', 'Maharastra', 'Karnataka', 'Harayna','MP', 'UP', 'Rajasthan'],
      'Mean_Pre_2019_depth':[df_guj['Pre_2019'].mean(),df_maha['Pre_2019'].mean(),
                         df_kar['Pre_2019'].mean(),df_har['Pre_2019'].mean(),
                         df_mp['Pre_2019'].mean(),df_up['Pre_2019'].mean(),df_raj['Pre_2019'].mean()],
       'STD_depth_Pre_2019_depth':[df_guj['Pre_2019'].std(),df_maha['Pre_2019'].std(),
                         df_kar['Pre_2019'].std(),df_har['Pre_2019'].std(),
                         df_mp['Pre_2019'].std(),df_up['Pre_2019'].std(),df_raj['Pre_2019'].std()],
        "Median_Pre_2019_depth":[df_guj['Pre_2019'].median(),df_maha['Pre_2019'].median(),
                         df_kar['Pre_2019'].median(),df_har['Pre_2019'].median(),
                         df_mp['Pre_2019'].median(),df_up['Pre_2019'].median(),df_raj['Pre_2019'].median()]}
con_pre_2019=pd.DataFrame(col6)
con_pre_2019
tools_visuaize(con_pre_2019)
"""
# Pst_2015
"""
coll2={'State':['Gujarat', 'Maharastra', 'Karnataka', 'Harayna','MP', 'UP', 'Rajasthan'],
      'Mean_Pst_2015_depth':[df_guj['Pst_2015'].mean(),df_maha['Pst_2015'].mean(),
                         df_kar['Pst_2015'].mean(),df_har['Pst_2015'].mean(),
                         df_mp['Pst_2015'].mean(),df_up['Pst_2015'].mean(),df_raj['Pst_2015'].mean()],
       'STD_depth_Pst_2015_depth':[df_guj['Pst_2015'].std(),df_maha['Pst_2015'].std(),
                         df_kar['Pst_2015'].std(),df_har['Pst_2015'].std(),
                         df_mp['Pst_2015'].std(),df_up['Pst_2015'].std(),df_raj['Pst_2015'].std()],
        "Median_Pst_2015_depth":[df_guj['Pst_2015'].median(),df_maha['Pst_2015'].median(),
                         df_kar['Pst_2015'].median(),df_har['Pst_2015'].median(),
                         df_mp['Pst_2015'].median(),df_up['Pst_2015'].median(),df_raj['Pst_2015'].median()]}
con_pst_2015=pd.DataFrame(coll2)
con_pst_2015
tools_visuaize(con_pst_2015)
"""
# Pst_2016
"""
coll3={'State':['Gujarat', 'Maharastra', 'Karnataka', 'Harayna','MP', 'UP', 'Rajasthan'],
      'Mean_Pst_2016_depth':[df_guj['Pst_2016'].mean(),df_maha['Pst_2016'].mean(),
                         df_kar['Pst_2016'].mean(),df_har['Pst_2016'].mean(),
                         df_mp['Pst_2016'].mean(),df_up['Pst_2016'].mean(),df_raj['Pst_2016'].mean()],
       'STD_depth_Pst_2016_depth':[df_guj['Pst_2016'].std(),df_maha['Pst_2016'].std(),
                         df_kar['Pst_2016'].std(),df_har['Pst_2016'].std(),
                         df_mp['Pst_2016'].std(),df_up['Pst_2016'].std(),df_raj['Pst_2016'].std()],
        "Median_Pst_2016_depth":[df_guj['Pst_2016'].median(),df_maha['Pst_2016'].median(),
                         df_kar['Pst_2016'].median(),df_har['Pst_2016'].median(),
                         df_mp['Pst_2016'].median(),df_up['Pst_2016'].median(),df_raj['Pst_2016'].median()]}
con_pst_2016=pd.DataFrame(coll3)
con_pst_2016
tools_visuaize(con_pst_2016)
"""
# Pst_2017
"""
coll4={'State':['Gujarat', 'Maharastra', 'Karnataka', 'Harayna','MP', 'UP', 'Rajasthan'],
      'Mean_Pst_2017_depth':[df_guj['Pst_2017'].mean(),df_maha['Pst_2017'].mean(),
                         df_kar['Pst_2017'].mean(),df_har['Pst_2017'].mean(),
                         df_mp['Pst_2017'].mean(),df_up['Pst_2017'].mean(),df_raj['Pst_2017'].mean()],
       'STD_depth_Pst_2017_depth':[df_guj['Pst_2017'].std(),df_maha['Pst_2017'].std(),
                         df_kar['Pst_2017'].std(),df_har['Pst_2017'].std(),
                         df_mp['Pst_2017'].std(),df_up['Pst_2017'].std(),df_raj['Pst_2017'].std()],
        "Median_Pst_2017_depth":[df_guj['Pst_2017'].median(),df_maha['Pst_2017'].median(),
                         df_kar['Pst_2017'].median(),df_har['Pst_2017'].median(),
                         df_mp['Pst_2017'].median(),df_up['Pst_2017'].median(),df_raj['Pst_2017'].median()]}
con_pst_2017=pd.DataFrame(coll4)
con_pst_2017
tools_visuaize(con_pst_2017)
"""
# Pst_2018
"""
coll5={'State':['Gujarat', 'Maharastra', 'Karnataka', 'Harayna','MP', 'UP', 'Rajasthan'],
      'Mean_Pst_2018_depth':[df_guj['Pst_2018'].mean(),df_maha['Pst_2018'].mean(),
                         df_kar['Pst_2018'].mean(),df_har['Pst_2018'].mean(),
                         df_mp['Pst_2018'].mean(),df_up['Pst_2018'].mean(),df_raj['Pst_2018'].mean()],
       'STD_depth_Pst_2018_depth':[df_guj['Pst_2018'].std(),df_maha['Pst_2018'].std(),
                         df_kar['Pst_2018'].std(),df_har['Pst_2018'].std(),
                         df_mp['Pst_2018'].std(),df_up['Pst_2018'].std(),df_raj['Pst_2018'].std()],
        "Median_Pst_2018_depth":[df_guj['Pst_2018'].median(),df_maha['Pst_2018'].median(),
                         df_kar['Pst_2018'].median(),df_har['Pst_2018'].median(),
                         df_mp['Pst_2018'].median(),df_up['Pst_2018'].median(),df_raj['Pst_2018'].median()]}
con_pst_2018=pd.DataFrame(coll5)
con_pst_2018
tools_visuaize(con_pst_2018)
"""
# Pst_2019
"""
coll6={'State':['Gujarat', 'Maharastra', 'Karnataka', 'Harayna','MP', 'UP', 'Rajasthan'],
      'Mean_Pst_2019_depth':[df_guj['Pst_2019'].mean(),df_maha['Pst_2019'].mean(),
                         df_kar['Pst_2019'].mean(),df_har['Pst_2019'].mean(),
                         df_mp['Pst_2019'].mean(),df_up['Pst_2019'].mean(),df_raj['Pst_2019'].mean()],
       'STD_depth_pst_2019':[df_guj['Pst_2019'].std(),df_maha['Pst_2019'].std(),
                         df_kar['Pst_2019'].std(),df_har['Pst_2019'].std(),
                         df_mp['Pst_2019'].std(),df_up['Pst_2019'].std(),df_raj['Pst_2019'].std()],
        "Median_pst_2019":[df_guj['Pst_2019'].median(),df_maha['Pst_2019'].median(),
                         df_kar['Pst_2019'].median(),df_har['Pst_2019'].median(),
                         df_mp['Pst_2019'].median(),df_up['Pst_2019'].median(),df_raj['Pst_2019'].median()]}
con_pst_2019=pd.DataFrame(coll6)
con_pst_2019
tools_visuaize(con_pst_2019)
"""
# Importing Plotly to visuaize the box pot 
"""
dffs=[con_table, con_pre_2015, con_pre_2016, con_pre_2017, con_pre_2018, con_pre_2019,
                        con_pst_2015, con_pst_2016, con_pst_2017, con_pst_2018, con_pst_2019]

from functools import reduce
contigancy_table=reduce(lambda left, right: pd.merge(left, right, on="State"), dffs)
contigancy_table.T
tools_visuaize(contigancy_table)
"""
# Making the dataframe as image
"""
contiganc_style=contigancy_table.style.background_gradient()
contiganc_style
pip install dataframe_image
import dataframe_image as dfi