Python Code

import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns
import statsmodels as sts
import datetime as dt
import numpy as np
import statistics as st
import openpyxl as opx

wq= pd.read_csv('woqooyi.csv')

column_names = wq.iloc[1]

wq = wq.drop([1,0], axis = 0)

wq.columns = column_names

wq= wq.melt(id_vars=["Year/Month"], var_name="Month", value_name="Price")

wq['Date']  = wq['Month'] + '-' + wq['Year/Month']

date = pd.to_datetime(wq.Date, format='%b-%Y', exact=False)
wq['Date']  = date

prices = []
for i in wq['Price']:
    if pd.isna(i)!= True:
        i = i.replace(',', '')
        i = str(i)
        i = int(i)
        prices.append(i)
    else:
        prices.append(i)
wq['Price'] = prices

wq= wq.sort_values(by='Date')

wq= wq.reset_index(drop = True)

pd.options.display.max_rows = 500
pd.options.display.max_columns = 300
wq

wq['Quarter'] = wq.apply(lambda x: x['Date'].quarter, axis=1)

wq['Quarter'] =wq.apply(lambda x: x['Date'].quarter, axis=1)
wq['Quarter'] = wq.apply(lambda x: x['Date'].quarter, axis=1)

wq.rename(columns = {'Year/Month':'Year'}, inplace = True)

wq['Year'] = wq['Year'].astype(str)
wq['Quarter'] = wq['Quarter'].astype(str)
wq['Quarter2'] = wq['Year']  + '-' + wq['Quarter']

wq= wq.reset_index(drop = True)
wq['Price'] = wq['Price'].fillna(wq.groupby('Quarter2')['Price'].transform('mean'))

wq.drop(['Quarter','Year','Month'], axis = 1, inplace = True)
wq.rename(columns = {'Quarter2': 'Quarter'}, inplace= True)

plt.style.use('seaborn')
sns.lineplot(data = wq, x = 'Date', y = 'Price')

sns.regplot(x=wq.index, y="Price", data=wq)

excel_file = pd.ExcelWriter('Woqooyi Region Cleaned.xlsx')
wq.to_excel(excel_file)
excel_file.save()