In [2]:

    

import pandas as pd
import os
df = pd.read_excel('xlsx/2008_ERCOT_Hourly_Load_Data.xls')
regions = list(df.columns[1:-1])

translate = {
    'FAR_WEST': 'FWEST',
    'NORTH_C': 'NCENT',
    'SOUTHERN': 'SOUTH',
    'SOUTH_C': 'SCENT'
}

files = os.listdir('xlsx')
for region in regions:
    print(region)
    df = pd.DataFrame()
    for file in sorted(files):
        year = file[:4]
        df_1 = pd.read_excel('xlsx/' + file)
        if int(year) > 2016 and region in translate:
            region = translate[region]
        df[year] = df_1[region]
        print(year, end=', ')
    df.to_csv('csv/{}.csv'.format(region), index=False)
    print('\n')


    

    




COAST
2002, 2003, 2004, 2005, 2006, 2007, 2008, 2009, 2010, 2011, 2012, 2013, 2014, 2015, 2016, 2017, 2018, 

EAST
2002, 2003, 2004, 2005, 2006, 2007, 2008, 2009, 2010, 2011, 2012, 2013, 2014, 2015, 2016, 2017, 2018, 

FAR_WEST
2002, 2003, 2004, 2005, 2006, 2007, 2008, 2009, 2010, 2011, 2012, 2013, 2014, 2015, 2016, 2017, 2018, 

NORTH
2002, 2003, 2004, 2005, 2006, 2007, 2008, 2009, 2010, 2011, 2012, 2013, 2014, 2015, 2016, 2017, 2018, 

NORTH_C
2002, 2003, 2004, 2005, 2006, 2007, 2008, 2009, 2010, 2011, 2012, 2013, 2014, 2015, 2016, 2017, 2018, 

SOUTHERN
2002, 2003, 2004, 2005, 2006, 2007, 2008, 2009, 2010, 2011, 2012, 2013, 2014, 2015, 2016, 2017, 2018, 

SOUTH_C
2002, 2003, 2004, 2005, 2006, 2007, 2008, 2009, 2010, 2011, 2012, 2013, 2014, 2015, 2016, 2017, 2018, 

WEST
2002, 2003, 2004, 2005, 2006, 2007, 2008, 2009, 2010, 2011, 2012, 2013, 2014, 2015, 2016, 2017, 2018, 

In [ ]: