<jupyter_start><jupyter_text>Reshape Fuel Prices - Duke Energy Progress3/18/2021 \by [<NAME>](<EMAIL>)<jupyter_code>import csv
import datetime as dt
import numpy as np
import pandas as pd
df_lookup = pd.read_csv('./inputs/UnitLookupAndDetailTable_(DEC-DEP).csv')
df_fuel_DEP = pd.read_csv('./inputs/UNIT_FUEL_PRICE(DEP 2019).csv')
list(df_fuel_DEP.columns)
#Slicing data and filter all the values where end date is before Jan 1st
df_fuel_DEP['UNIT_ID'] = df_fuel_DEP.UNIT_NAME + '_'+ df_fuel_DEP.CC_KEY.apply(str)
df_fuel_DEP = df_fuel_DEP.loc[:, ['UNIT_ID', 'FUEL_TYPE','PRICE $/MBTU', 'FROM_DATE', 'TO_DATE']]

df_fuel_DEP.sort_values(by=['UNIT_ID', 'FUEL_TYPE'], inplace=True)

df_fuel_DEP.to_csv('./outputs/UNIT_FUEL_PRICE(DEP 2019)_sorted.csv', sep=',', encoding='utf-8', index= False)

df_fuel_DEP.head()<jupyter_output><empty_output><jupyter_text>Descriptive statisticsData from Duke Energy Carolinas and Duke Energy Progress<jupyter_code>df_fuel_DEP.describe(include='all')<jupyter_output><empty_output><jupyter_text>Calculating range of days between initial and end dates<jupyter_code>def convertStringToDate(date_string): 
    date_obj = dt.datetime.strptime(date_string.split(" ")[0], '%m/%d/%Y')
    #if date_obj - dt.date(2018, 7, 11)
    return date_obj    

#convertStringToDate('5/10/2018')
df_fuel_DEP['FROM_DATE'] = df_fuel_DEP['FROM_DATE'].apply(convertStringToDate)
df_fuel_DEP['TO_DATE'] = df_fuel_DEP['TO_DATE'].apply(convertStringToDate)

df_fuel_DEP.describe(include='all')
First_day = convertStringToDate('1/1/2019')
Last_day = convertStringToDate('12/31/2019')

#remove all the values where the end dates are in 2018
df_fuel_DEP['END_YEAR'] = df_fuel_DEP['TO_DATE'].map(lambda TO_DATE: TO_DATE.year)
df_fuel_DEP['START_YEAR'] = df_fuel_DEP['FROM_DATE'].map(lambda FROM_DATE: FROM_DATE.year)

df_fuel_DEP = df_fuel_DEP[df_fuel_DEP['START_YEAR'] < 2020]
df_fuel_DEP = df_fuel_DEP[df_fuel_DEP['END_YEAR'] >= 2019]

df_fuel_DEP['FROM_DATE'] = df_fuel_DEP['FROM_DATE'].map(lambda FROM_DATE: First_day if (First_day - FROM_DATE).days > 0 else FROM_DATE )
df_fuel_DEP['TO_DATE'] = df_fuel_DEP['TO_DATE'].map(lambda TO_DATE: Last_day if (TO_DATE - Last_day).days > 0 else TO_DATE)

df_fuel_DEP = df_fuel_DEP[df_fuel_DEP['TO_DATE'] != First_day]

df_fuel_DEP.describe(include='all')
# Adding columns to compute number of days from FROM_DATE to TO_DATE 
df_fuel_DEP['DAYS'] = df_fuel_DEP['TO_DATE'] - df_fuel_DEP['FROM_DATE']
df_fuel_DEP['DAYS'] = df_fuel_DEP['DAYS'].map(lambda DAYS: DAYS.days )

df_fuel_DEP['REF_FROM_DATE'] = df_fuel_DEP['FROM_DATE'] - First_day
df_fuel_DEP['REF_FROM_DATE'] = df_fuel_DEP['REF_FROM_DATE'].map(lambda DAYS: DAYS.days )

# Replace last value when the number of days is zero
df_fuel_DEP['DAYS'] = np.where((df_fuel_DEP['DAYS'] == 0) & (df_fuel_DEP['TO_DATE'] == Last_day), 1, df_fuel_DEP['DAYS'])

df_fuel_DEP = df_fuel_DEP.loc[:, ['UNIT_ID', 'FUEL_TYPE', 'PRICE $/MBTU', 'FROM_DATE', 'TO_DATE', 'DAYS', 'REF_FROM_DATE']]
df_fuel_DEP.head()
# Creating pivot tableto summarize unit units and fuel type
df_fuel_DEP_pivot = df_fuel_DEP.groupby(['UNIT_ID', 'FUEL_TYPE']).sum()
df_fuel_DEP_pivot.to_csv('./outputs/fuel_summary.csv', sep=',', encoding='utf-8')
#print(list(df_fuel_DEP_pivot.index))
df_fuel_DEP_pivot<jupyter_output><empty_output><jupyter_text>Manipulating dataframe to organize data<jupyter_code>First_day = convertStringToDate('1/1/2019')
Last_day = convertStringToDate('12/31/2019')

#Create list with dates from First_day to last_day
date_list = [First_day + dt.timedelta(days=x) for x in range(0, (Last_day-First_day).days + 1)]

date_str_list = []
for date in date_list:
    date_str_list.append(date.strftime("%m/%d/%Y"))
#create results dataframe to store prices every day
df_fuel_result = pd.DataFrame(index=df_fuel_DEP_pivot.index, columns=date_list)
#df_fuel_DEP_pivot = df_fuel_DEP_pivot.reindex(columns = df_fuel_DEP_pivot.columns.tolist() + date_str_list)
                                
df_fuel_result.head(n=5)
current_index = ()
old_index = ()
aux_index = 0
fuel_price_list = [None] * 365

for index, row in df_fuel_DEP.iterrows():
    aux_index = index
    index_current = (row['UNIT_ID'], row['FUEL_TYPE'])
    
    # access data using column names
    fuel_price = row['PRICE $/MBTU']
    days = row['DAYS']
    ref_day = row['REF_FROM_DATE']
    
    current_index = (row['UNIT_ID'], row['FUEL_TYPE'])
    #print(index, row['UNIT_ID'], row['FUEL_TYPE'], row['PRICE $/MBTU'], row['REF_FROM_DATE'], row['DAYS'])
        
    if index == 0:
        old_index = current_index
    
    if (old_index != current_index):
        df_fuel_result.loc[old_index] = fuel_price_list

        old_index = current_index
        fuel_price_list = [None] * 365
    
    fuel_price_list[ref_day:(ref_day + days)] = [fuel_price]*(days)

        #print(index, row['PRICE $/MBTU'], row['REF_FROM_DATE'], row['DAYS'])
#Save last value
if aux_index != 0 :
    df_fuel_result.loc[current_index] = fuel_price_list
    
df_fuel_result.head()
df_fuel_result.to_csv('./outputs/UNIT_FUEL_PRICES_DEP_Results.csv', sep=',', encoding='utf-8')
df_fuel_DEP.to_csv('./outputs/UNIT_FUEL_PRICES_DEP_Short.csv', sep=',', encoding='utf-8')
#dfSummary['UNIT_ID'] dfSummary.UNIT_ID == 'ALLE_UN01_0')
#dfSummary[dfSummary.DAYS == 364]<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.14536199095022626, 'fraction_numerical': 0.018288084464555054, 'mean_word_length': 6.168918918918919, 'pattern_counts': {'":': 0, '<': 20, '<?xml version=': 0, '>': 22, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '4', 'dir': 'jupyter-structured-clean-dedup', 'id': '64150', 'license': 'MIT', 'n_tokens_mistral': 2247, 'n_tokens_neox': 2005, 'n_words': 347, 'path': 'data_cleaning_analysis/Reshape_data_fuel_prices_DEP.ipynb', 'repo_name': 'power-systems-op/grace-arpae', 'size': '5323'}