sourceMerge_correlation_matrix.py

#!/usr/bin/env python
# coding: utf-8

# In[1]:


import pandas as pd
import matplotlib.pyplot as plt
import numpy as np
from pathlib import Path

from calendar import monthrange
import datetime as dt


# In[2]:


def create_dateNum(listVal,buffVal):
    for each in listVal:
        if each == 'JAN':
            buffVal.append(1)
        elif each == 'FEB':
            buffVal.append(2)
        elif each == 'MAR':
            buffVal.append(3)
        elif each == 'APR':
            buffVal.append(4)
        elif each == 'MAY':
            buffVal.append(5)
        elif each == 'JUN':
            buffVal.append(6)
        elif each == 'JUL':
            buffVal.append(7)
        elif each == 'AUG':
            buffVal.append(8)
        elif each == 'SEP':
            buffVal.append(9)
        elif each == 'OCT':
            buffVal.append(10)
        elif each == 'NOV':
            buffVal.append(11)
        elif each == 'DEC':
            buffVal.append(12)
    return buffVal


# In[3]:


def create_dataFrame(df,indexDel):
    df.set_index('YEAR_PRD', append=True, inplace=True)
    df.set_index('MONTH_PRD', append=True, inplace=True)
    df.set_index('MONTH_NO', append=True, inplace=True)
    df.set_index(indexDel, append=True, inplace=True)
    df.index.names = ['seqID', 'YEAR_PRD', 'MONTH_PRD','MONTH_NO',indexDel]
    df = df.unstack(4)
    df = df.reset_index(level=['seqID'])
    df.drop('seqID', axis=1,level = 0,inplace = True)
    df = df.groupby(['YEAR_PRD','MONTH_PRD','MONTH_NO']).sum()
    return df


# In[4]:


rootSource = Path('transformed_data')
rootOutput = Path('aggregated_data')
pd.set_option('display.width', 300)


# In[5]:


powGen_byFuel_excel_file = "Pandas power generated by fuel.csv"
powGen_bf_df = pd.read_csv(rootSource / powGen_byFuel_excel_file)
powGen_bySec_excel_file = "Pandas power generated by sector.csv"
powGen_bs_df = pd.read_csv(rootSource / powGen_bySec_excel_file)
ngCons_excel_file = "Pandas ng consumption.csv"
ngCons_df = pd.read_csv(rootSource / ngCons_excel_file)
ngPrd_excel_file = "Pandas ng production.csv"
ngPrd_df = pd.read_csv(rootSource / ngPrd_excel_file)


# In[6]:


powGen_bf_listVal = powGen_bf_df.iloc[:,1]
powGen_bs_listVal = powGen_bs_df.iloc[:,1]
ngCons_listVal = ngCons_df.iloc[:,1]
ngPrd_listVal = ngPrd_df.iloc[:,1]

buffPowVal_bf,buffPowVal_bs,buffNgCons,buffNgPrd = [],[],[],[]
buffPowVal_bf = create_dateNum(powGen_bf_listVal,[])
buffPowVal_bs = create_dateNum(powGen_bs_listVal,[])
buffNgCons = create_dateNum(ngCons_listVal,[])
buffNgPrd = create_dateNum(ngPrd_listVal,[])

powGen_bf_df.insert(loc=2, column='MONTH_NO', value=buffPowVal_bf)
powGen_bs_df.insert(loc=2, column='MONTH_NO', value=buffPowVal_bs)
ngCons_df.insert(loc=2, column='MONTH_NO', value=buffNgCons)
ngPrd_df.insert(loc=2, column='MONTH_NO', value=buffNgPrd)


# In[7]:


try:
    powGen_bf_df = create_dataFrame(powGen_bf_df,'FUELTYPE')
    powGen_bs_df = create_dataFrame(powGen_bs_df,'FUEL_SOURCE')
    print('Create powerGen dataframe done.')
except Exception as e:
    print(e)


# In[8]:


try:
    ngCons_df.drop('SECTOR', axis=1,inplace=True)
    ngCons_df = create_dataFrame(ngCons_df,'SUBSECTOR')
    print('Create ngConsume dataframe done.')
except Exception as e:
    print(e)


# In[9]:


try:
    ngPrd_df.drop('PLANT', axis=1,inplace=True)
    ngPrd_df = create_dataFrame(ngPrd_df,'SOURCE')
    print('Create ngProduction dataframe done.')
except Exception as e:
    print(e)


# In[10]:


try:
    join_df = pd.merge(ngPrd_df,ngCons_df,on=['YEAR_PRD','MONTH_NO'],how='left',suffixes=('_x', '_y'))
    print('Dataframe ngPrd_df,ngCons_df join done.')
except  Exception as e:
    print(e)


# In[11]:


join_df = join_df.rename(columns={
    "Domestic Production": "NG PRD Domestic",
    "Import": "NG PRD Import",
    "EGAT": "NG CONS EGAT",
    "GSP": "NG CONS GSP",
    "IPP": "NG CONS IPP",
    "Industry": "NG CONS Industry",
    "NGV": "NG CONS NGV",
    "SPP": "NG CONS SPP",
    }
)


# In[12]:


try:
    join_df = pd.merge(join_df,powGen_bs_df,on=['YEAR_PRD','MONTH_NO'],how='left',suffixes=('_x', '_y'))
    print('Dataframe powGen_bs_df join done.')
except  Exception as e:
    print(e)


# In[13]:


join_df = join_df.rename(columns={
    "DEDP": "POWGEN DEDP",
    "EGAT": "POWGEN EGAT",
    "IMPORTED": "POWGEN Import",
    "VSPP": "POWGEN VSPP",
    "IPP": "POWGEN IPP",
    "SPP": "POWGEN SPP",
    }
)


# In[14]:


#try:
#    join_df = pd.merge(join_df,powGen_bf_df,on=['YEAR_PRD','MONTH_NO'],how='left',suffixes=('_j2', '_b'))
#    print('Dataframe powGen_bf_df join done.')
#except  Exception as e:
#    print(e)


# In[15]:


#join_df = join_df.rename(columns={
#    "DEDP": "POWGEN DEDP",
#    "EGAT": "POWGEN EGAT",
#    "IMPORTED": "POWGEN Import",
#    "VSPP": "POWGEN VSPP",
#    "IPP": "POWGEN IPP",
#    "SPP": "POWGEN SPP",
#    }
#)


# In[16]:


join_df = join_df.stack(0)
join_df.index.names = ['YEAR_PRD', 'MONTH_NO','SOURCE']
join_df = join_df.reset_index(level=['SOURCE'])
join_df.drop('SOURCE', axis=1,inplace = True)
join_df = join_df.reset_index(level=['YEAR_PRD'])
join_df = join_df.reset_index(level=['MONTH_NO'])


# In[17]:


buffDateVal = []


# In[18]:


m_listVal = join_df.iloc[:,0]
y_listVal = join_df.iloc[:,1]


# In[19]:


for i,yrs in enumerate(y_listVal,0):
    x = dt.datetime(y_listVal[i],m_listVal[i],monthrange(y_listVal[i], m_listVal[i])[1])
    buffDateVal.append(x.strftime("%Y-%m-%d"))


# In[20]:


join_df.insert(loc=2, column='DS', value=buffDateVal)


# In[21]:


join_df.drop('YEAR_PRD', axis=1,inplace=True)
join_df.drop('MONTH_NO', axis=1,inplace=True)


# In[22]:


join_df


# In[23]:


try:
    join_df.to_csv(rootOutput / 'aggregate_data.csv',header = True,index=False)
    join_df.corr().to_excel(rootOutput / 'correlation_raw_output.xlsx')
    print('###### Program Completed. #######')
except  Exception as e:
    print(e)