# 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
!pwd
import matplotlib.pyplot as plt
import plotly.graph_objs as go
from plotly.subplots import make_subplots
"""
## 1. Reading the data
"""
df = pd.read_csv('/kaggle/input/chennai-water-management/chennai_reservoir_levels.csv',
                parse_dates=['Date'], dayfirst=True)
#df['Date'] = pd.to_datetime(df['Date'], format='%d-%m-%Y')
df.head()
df.dtypes
df.isnull().sum()
df.Date.dt.year.min(), df.Date.dt.year.max()
fig = make_subplots(rows=2, cols=2,
                   subplot_titles=['Poondi (in mcft)',
                                  'Redhills (in mcft)',
                                  'Chembarambakkam (in mcft)',
                                  'Cholavaram (in mcft)'])

fig.add_trace(go.Scatter(x=df.Date, y=df.POONDI, name='Poondi'), row=1, col=1)
fig.add_trace(go.Scatter(x=df.Date, y=df.REDHILLS, name='Redhills'), row=1, col=2)
fig.add_trace(go.Scatter(x=df.Date, y=df.CHEMBARAMBAKKAM, name='Chembarambakkam'), row=2, col=1)
fig.add_trace(go.Scatter(x=df.Date, y=df.CHOLAVARAM, name='Cholavaram'), row=2, col=2)

fig.update_layout(title_text = "Water availability in Chennai's four major reserviours {}-{}".format(df.Date.dt.year.min(), df.Date.dt.year.max()))

fig.show()
df_tidy = df.melt(id_vars = ['Date'], var_name='Resoviour', value_name='Water_Level')
df_tidy.head()
import plotly.express as px

px.line(df_tidy,
       x='Date',
       y='Water_Level',
        facet_col="Resoviour",
        facet_col_wrap=2,
        color='Resoviour',
       title="Water availability in Chennai's four major reserviours {}-{}".format(df.Date.dt.year.min(), df.Date.dt.year.max()))
fig = px.line(df.melt(id_vars=['Date'],var_name='Resoviour',value_name='Water_Level'), 
              x="Date", 
              y="Water_Level", 
              color="Resoviour",                  
              facet_col="Resoviour",
              facet_col_wrap=1,
              height=1000
             )
fig.update_yaxes(matches=None)
fig.show()
"""
**Inference:**
- We could clearly see that evey year there is a decremental phase and a replenishment phase (mainly during october to december)
- There was a very bad water scarcity phase seen during 2004.
- We can also see a bad phase during 2014-15 but there was to water availability in two reservoirs (Redhills and Chembarambakkam) and so it was a savior.
- Now coming to recent times, the data shows that there is no water availability in any of the four major reservoirs.
"""
"""
## 2. Look at the overall water levels

"""
df['total'] = df.drop(columns='Date').sum(axis=1)
df.head()
px.line(df,
       x='Date',
       y='total',
       title='Total water availability from all four resoviours in mcft')
"""
## 3. Rainfall Levels
"""
rain_df = pd.read_csv('/kaggle/input/chennai-water-management/chennai_reservoir_rainfall.csv',
                     parse_dates=['Date'], dayfirst=True)
rain_df.head()
rain_df.dtypes
px.line(rain_df.melt(id_vars=['Date'], var_name='Resoviour', value_name='Rainfall'),
      x='Date',
      y='Rainfall',
      facet_col='Resoviour',
      facet_col_wrap=2,
      color='Resoviour')

rain_df['YearMonth'] = pd.to_datetime(rain_df.Date.dt.year.astype(str)+rain_df.Date.dt.month.astype(str), format='%Y%m')
rain_df.head()
rain_df.YearMonth.value_counts()
rain_df['total'] = rain_df.drop(columns=['Date', 'YearMonth']).sum(axis=1)
rain_df.head()
"""
## 4. Add a season column and use it as color in the bar plot
"""
rain_df.groupby('YearMonth').total.sum().reset_index()
px.bar(rain_df.groupby('YearMonth').total.sum().reset_index(),
       x='YearMonth',
       y='total',
      #color='season'
      )
"""
## 4. Total Yearly rainfall
"""
rain_df['Year'] = pd.to_datetime(rain_df.Date.dt.year.astype(str), format='%Y')
rain_df.head()
monthly_rain_df = rain_df.groupby('YearMonth').total.sum().reset_index()
monthly_rain_df
# Creating Season column
month_to_season = {1: 'winter', 2: 'winter', 3: 'summer', 4: 'summer', 5: 'summer', 6: 'monsoon', 7: 'monsoon', 8: 'monsoon', 9: 'monsoon',
 10: 'post-monsoon', 11: 'post-monsoon', 12: 'post-monsoon'}
monthly_rain_df['season'] = monthly_rain_df.YearMonth.dt.month.map(month_to_season) 
monthly_rain_df
px.bar(monthly_rain_df,
      x='YearMonth',
      y='total',
      color='season',
      title='Yearly rainfall in the four major resoviour regions in mm'
      )
"""
## 5. Water Shortage estimation
"""
px.bar(df.query("Date.dt.month== 3 and Date.dt.day== 1"),
       x='Date',
       y='total',
       title='Availability of water in total at the beginning of summer')