<jupyter_start><jupyter_text>Load EIA natural gas and coal prices for electric power plants in TexasThese values are real (not adjusted for inflation)I forgot that we don't have 2007 coal price data. Applying the average 2008 price for now.<jupyter_code>%matplotlib inline
import matplotlib.pyplot as plt
import pandas as pd
import os
import glob
import re
import cPickle as pickle
import gzip
import seaborn as sns<jupyter_output>/Users/Home/anaconda/lib/python2.7/site-packages/matplotlib/font_manager.py:273: UserWarning: Matplotlib is building the font cache using fc-list. This may take a moment.
  warnings.warn('Matplotlib is building the font cache using fc-list. This may take a moment.')<jupyter_text>Natural gasPrice is $/thousand cubic feet (mcf)<jupyter_code>path = os.path.join('EIA data', 'Natural gas price', 'N3045TX3m.xls')
ng_price = pd.read_excel(path, sheetname='Data 1', header=2)
ng_price.head()
ng_price.dtypes
ng_price.rename(columns={"Texas Natural Gas Price Sold to Electric Power Consumers (Dollars per Thousand Cubic Feet)":
                         "NG Price ($/mcf)"}, inplace=True)
ng_price.head()
ng_price.loc[:,'Month'] = ng_price.loc[:,'Date'].apply(lambda x: x.month)
ng_price.loc[:,'Year'] = ng_price.loc[:,'Date'].apply(lambda x: x.year)
ng_price.head()
ng_price.dtypes<jupyter_output><empty_output><jupyter_text>CoalPrice is $/short ton  EIA data[Source](http://www.eia.gov/beta/coal/data/browser//topic/45?agg=1,0&geo=vvvvvvvvvvvvo&rank=l&linechart=~~COAL.SHIP_PLANT_PRICE.TX-LIG.Q~COAL.SHIP_PLANT_PRICE.TX-SUB.Q&columnchart=COAL.SHIP_PLANT_PRICE.US-TOT.Q&map=COAL.SHIP_PLANT_PRICE.US-TOT.Q&freq=Q&start=200801&end=201601&chartindexed=0&ctype=linechart&ltype=pin&rtype=s&pin=&rse=0&maptype=0)<jupyter_code>path = os.path.join('EIA data', 'Coal prices', 'Texas electric sector coal price.xlsx')
coal_temp = pd.read_excel(path, sheetname='Sheet1')
coal_temp.head()
coal_temp.loc[:,'Quarter'] = coal_temp.loc[:,'Date'].apply(lambda x: int(x[1]))
coal_temp.loc[:,'Year'] = coal_temp.loc[:,'Date'].apply(lambda x: int(x[-4:]))
coal_temp.head()
for coal in ['All coal', 'Lignite', 'Subbituminous']:
    plt.plot(coal_temp.index, coal_temp[coal], label=coal)
plt.legend(loc=0)
coal_temp[['All coal', 'Lignite', 'Subbituminous']].corr()<jupyter_output><empty_output><jupyter_text>Make up data for 2007The EIA source only goes back to 2008. Use average 2008 price for 2007. If there is time, look for more data or figure out better method.<jupyter_code>coal_2007 = pd.DataFrame(columns=coal_temp.columns)
coal_2007['Quarter'] = [1,2,3,4]
coal_2007['Year'] = 2007
for coal in ['All coal', 'Lignite', 'Subbituminous']:
    coal_2007[coal] = coal_temp.loc[coal_temp['Year']==2008,coal].mean()
coal_2007
coal_temp = coal_temp.append(coal_2007)
coal_temp.tail()<jupyter_output><empty_output><jupyter_text>Make dataframe with quarter, month, and year columns from 2007-2015<jupyter_code>df = pd.DataFrame(pd.date_range('2007-1-15', periods=12*9, freq='M'), columns=['tempdate'])
df.head()
df.loc[:,'Quarter'] = df.loc[:,'tempdate'].apply(lambda x: x.quarter)
df.loc[:,'Month'] = df.loc[:,'tempdate'].apply(lambda x: x.month)
df.loc[:,'Year'] = df.loc[:,'tempdate'].apply(lambda x: x.year)
df.head()
df.tail()
coal_price = pd.merge(coal_temp, df, on=['Quarter', 'Year'])
coal_price.head()<jupyter_output><empty_output><jupyter_text>Combine fuel dataframes<jupyter_code>fuel_price = pd.merge(ng_price, coal_price, how='right', on=['Month', 'Year'])
fuel_price.head()
fuel_price.tail()
fuel_price.drop(['Date_x', 'Date_y', 'tempdate', 'Quarter'], axis=1, inplace=True)
path = os.path.join('..', 'Clean Data', 'Fuel prices.csv')
fuel_price.to_csv(path)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.16149068322981366, 'fraction_numerical': 0.03078584931136916, 'mean_word_length': 6.965591397849463, 'pattern_counts': {'":': 1, '<': 24, '<?xml version=': 0, '>': 24, 'https://': 0, 'lorem ipsum': 0, 'www.': 1, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 1, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '6', 'dir': 'jupyter-structured-clean-dedup', 'id': '187528', 'license': 'MIT', 'n_tokens_mistral': 1503, 'n_tokens_neox': 1375, 'n_words': 282, 'path': 'Raw Data/Load fuel prices.ipynb', 'repo_name': 'nikhilba/ERCOT', 'size': '3703'}