# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .py
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernelspec:
#     display_name: Python 3
#     language: python
#     name: python3
# ---

import pandas as pd
import os
import sqlalchemy

# +
#Change desired directory
#os.chdir("") 
# -

# Storage data pulled from https://ir.eia.gov/ngs/ngs.html

#Read in historic averages
url2 = 'https://ir.eia.gov/ngs/ngsstats.xls'
five_year = pd.read_excel(url2)

old_averages = pd.read_csv('C:/Users/<NAME>/Documents/Python Scripts/EIA Storage Reports/Archive/ngsstats - ngsstats 2021 (2016-2020).csv')

old_averages['Report Date'] = pd.to_datetime(old_averages['Report Date'])

old_averages = old_averages.set_index(['Report Date'])

#Find columns and data and get rid of junk rows
head = five_year.iloc[1]
five_year = five_year.iloc[2:]
five_year.columns = head
five_year_short = five_year.iloc[:-3]
five_year_short = five_year_short.set_index(['Report Date'])

#Seperate data into categories by column indexing
five_year_avg = five_year_short.iloc[:,:8]
five_year_avg.loc[:, 'Category'] = 'Five Year Storage Average'
five_year_max = five_year_short.iloc[:,8:16]
five_year_max.loc[:, 'Category'] = 'Five Year Storage Maximum'
five_year_min = five_year_short.iloc[:, 16:24]
five_year_min.loc[:, 'Category'] = 'Five Year Storage Minimum'

#Append the seperated columns together for final average storage df
df = five_year_avg.append(five_year_max, ignore_index=False)
df = df.append(five_year_min, ignore_index=False)

df = df.append(old_averages, ignore_index = False)

df = df.drop_duplicates()

#Pull in current storage numbers
url = 'https://ir.eia.gov/ngs/ngshistory.xls'
dfex = pd.read_excel(url)

#Find columns and data
colum = dfex.iloc[5]
dfex = dfex.iloc[6:]
dfex.columns = colum

#Renaming Initial Column
new_columns = dfex.columns.values
new_columns[0] = 'Report Date'
dfex.columns  = new_columns

#Set column index to report date, drop 'Source', and add 'Category'
dfex = dfex.set_index(['Report Date'])
dfex = dfex.drop(['Source'], axis =1)
dfex.loc[:,'Category'] = 'Current Storage'

#Rename df columns to match dfex
df.columns = dfex.columns

#Combine all data
all_data = df.append(dfex, ignore_index=False)

#Pull Report Date index out into a column
all_data['Report Date'] = all_data.index

#Melt data so category contains all these columns
final_data = all_data.melt(id_vars=['Report Date', 'Category'], value_vars=['East Region', 'Midwest Region',
       'Mountain Region', 'Pacific Region', 'South Central Region', 'Salt',
       'NonSalt', 'Total Lower 48'])

#Rename melted columns
final_data.columns = ['Report Date', 'Category', 'Region', 'Storage Amount']

final_data.to_csv(r'ngshistory - html_report_history.csv')



StarcoderdataJupyterScriptsDedupFiltered