timeseries_scripts/read.py
def read_entso_e_portal(filepath, url, headers):
    '''Read a file from ENTSO-E into a DataFrame'''
    df = pd.read_excel(
        io=filepath,
        header=9,  # 0 indexed, so the column names are actually in the 10th row
        skiprows=None,
        # create MultiIndex from first 2 columns ['Country', 'Day']
        index_col=[0, 1],
        parse_cols=None,  # None means: parse all columns
        na_values=['n.a.']
    )

    df.columns.names = ['raw_hour']

    # The original data has days and countries in the rows and hours in the
    # columns.  This rearranges the table, mapping hours on the rows and
    # countries on the columns.
    df = df.stack(level='raw_hour').unstack(level='Country').reset_index()

    # Format of the raw_hour-column is normally is 01:00:00, 02:00:00 etc.
    # during the year, but 3A:00:00, 3B:00:00 for the (possibely
    # DST-transgressing) 3rd hour of every day in October, we truncate the
    # hours column after 2 characters and replace letters which are there to
    # indicate the order during fall DST-transition.
    df['hour'] = df['raw_hour'].str[:2].str.replace(
        'A', '').str.replace('B', '')
    # Hours are indexed 1-24 by ENTSO-E, but pandas requires 0-23, so we
    # deduct 1, i.e. the 3rd hour will be indicated by "2:00" rather than
    # "3:00"
    df['hour'] = (df['hour'].astype(int) - 1).astype(str)

    df['timestamp'] = pd.to_datetime(df['Day'] + ' ' + df['hour'] + ':00')
    df.set_index('timestamp', inplace=True)

    # Create a list of daylight savings time (DST)-transitions
    dst_transitions = [
        d.replace(hour=2)
        for d in pytz.timezone('Europe/Berlin')._utc_transition_times
        if d.year >= 2000]

    # Drop 2nd occurence of 3rd hour appearing in October file
    # except for the day of the actual autumn DST-transition.
    df = df[~((df['raw_hour'] == '3B:00:00') & ~
              (df.index.isin(dst_transitions)))]

    # Drop 3rd hour for (spring) DST-transition. October data
    # is unaffected the format is 3A:00:00/3B:00:00.
    df = df[~((df['raw_hour'] == '03:00:00') &
              (df.index.isin(dst_transitions)))]

    df.drop(['Day', 'hour', 'raw_hour'], axis=1, inplace=True)
    df.index = df.index.tz_localize('Europe/Brussels', ambiguous='infer')
    df.index = df.index.tz_convert(None)

    renamer = {'DK_W': 'DK_1', 'UA_W': 'UA_west', 'NI': 'GB_NIR'}
    df.rename(columns=renamer, inplace=True)

    colmap = {
        'variable': 'load',
        'region': '{country}',
        'attribute': 'old',
        'source': 'ENTSO-E Data Portal',
        'web': url,
        'unit': 'MW'
    }

    # Create the MultiIndex
    tuples = [tuple(colmap[level].format(country=col)
                    for level in headers) for col in df.columns]
    df.columns = pd.MultiIndex.from_tuples(tuples, names=headers)

    return df
Read a file from ENTSO-E into a DataFrame
Read a file from ENTSO-E into a DataFrame
Read a file from ENTSO - E into a DataFrame
def read_entso_e_portal(filepath, url, headers):
    df = pd.read_excel(
        io=filepath,
        header=9,  
        skiprows=None,
        index_col=[0, 1],
        parse_cols=None,  
        na_values=['n.a.']
    )
    df.columns.names = ['raw_hour']
    df = df.stack(level='raw_hour').unstack(level='Country').reset_index()
    df['hour'] = df['raw_hour'].str[:2].str.replace(
        'A', '').str.replace('B', '')
    df['hour'] = (df['hour'].astype(int) - 1).astype(str)
    df['timestamp'] = pd.to_datetime(df['Day'] + ' ' + df['hour'] + ':00')
    df.set_index('timestamp', inplace=True)
    dst_transitions = [
        d.replace(hour=2)
        for d in pytz.timezone('Europe/Berlin')._utc_transition_times
        if d.year >= 2000]
    df = df[~((df['raw_hour'] == '3B:00:00') & ~
              (df.index.isin(dst_transitions)))]
    df = df[~((df['raw_hour'] == '03:00:00') &
              (df.index.isin(dst_transitions)))]
    df.drop(['Day', 'hour', 'raw_hour'], axis=1, inplace=True)
    df.index = df.index.tz_localize('Europe/Brussels', ambiguous='infer')
    df.index = df.index.tz_convert(None)
    renamer = {'DK_W': 'DK_1', 'UA_W': 'UA_west', 'NI': 'GB_NIR'}
    df.rename(columns=renamer, inplace=True)
    colmap = {
        'variable': 'load',
        'region': '{country}',
        'attribute': 'old',
        'source': 'ENTSO-E Data Portal',
        'web': url,
        'unit': 'MW'
    }
    tuples = [tuple(colmap[level].format(country=col)
                    for level in headers) for col in df.columns]
    df.columns = pd.MultiIndex.from_tuples(tuples, names=headers)
    return df
def read_entso_e_portal ( filepath , url , headers ) : df = pd . read_excel ( io = filepath , header = 9 , skiprows = None , index_col = [ 0 , 1 ] , parse_cols = None , na_values = [ 'n.a.' ] ) df . columns . names = [ 'raw_hour' ] df = df . stack ( level = 'raw_hour' ) . unstack ( level = 'Country' ) . reset_index ( ) df [ 'hour' ] = df [ 'raw_hour' ] . str [ : 2 ] . str . replace ( 'A' , '' ) . str . replace ( 'B' , '' ) df [ 'hour' ] = ( df [ 'hour' ] . astype ( int ) - 1 ) . astype ( str ) df [ 'timestamp' ] = pd . to_datetime ( df [ 'Day' ] + ' ' + df [ 'hour' ] + ':00' ) df . set_index ( 'timestamp' , inplace = True ) dst_transitions = [ d . replace ( hour = 2 ) for d in pytz . timezone ( 'Europe/Berlin' ) . _utc_transition_times if d . year >= 2000 ] df = df [ ~ ( ( df [ 'raw_hour' ] == '3B:00:00' ) & ~ ( df . index . isin ( dst_transitions ) ) ) ] df = df [ ~ ( ( df [ 'raw_hour' ] == '03:00:00' ) & ( df . index . isin ( dst_transitions ) ) ) ] df . drop ( [ 'Day' , 'hour' , 'raw_hour' ] , axis = 1 , inplace = True ) df . index = df . index . tz_localize ( 'Europe/Brussels' , ambiguous = 'infer' ) df . index = df . index . tz_convert ( None ) renamer = { 'DK_W' : 'DK_1' , 'UA_W' : 'UA_west' , 'NI' : 'GB_NIR' } df . rename ( columns = renamer , inplace = True ) colmap = { 'variable' : 'load' , 'region' : '{country}' , 'attribute' : 'old' , 'source' : 'ENTSO-E Data Portal' , 'web' : url , 'unit' : 'MW' } tuples = [ tuple ( colmap [ level ] . format ( country = col ) for level in headers ) for col in df . columns ] df . columns = pd . MultiIndex . from_tuples ( tuples , names = headers ) return df
Read a file from ENTSO-E into a DataFrame
Read a file from ENTSO - E into a DataFrame
'''Read a file from ENTSO-E into a DataFrame''' # 0 indexed, so the column names are actually in the 10th row # create MultiIndex from first 2 columns ['Country', 'Day'] # None means: parse all columns # The original data has days and countries in the rows and hours in the # columns.  This rearranges the table, mapping hours on the rows and # countries on the columns. # Format of the raw_hour-column is normally is 01:00:00, 02:00:00 etc. # during the year, but 3A:00:00, 3B:00:00 for the (possibely # DST-transgressing) 3rd hour of every day in October, we truncate the # hours column after 2 characters and replace letters which are there to # indicate the order during fall DST-transition. # Hours are indexed 1-24 by ENTSO-E, but pandas requires 0-23, so we # deduct 1, i.e. the 3rd hour will be indicated by "2:00" rather than # "3:00" # Create a list of daylight savings time (DST)-transitions # Drop 2nd occurence of 3rd hour appearing in October file # except for the day of the actual autumn DST-transition. # Drop 3rd hour for (spring) DST-transition. October data # is unaffected the format is 3A:00:00/3B:00:00. # Create the MultiIndex
filepath
url
headers