timeseries_scripts/read.py
def read_energinet_dk(filepath, url, headers):
    '''Read a file from energinet.dk into a DataFrame'''
    df = pd.read_excel(
        io=filepath,
        header=2,  # the column headers are taken from 3rd row.
        # 2nd row also contains header info like in a multiindex,
        # i.e. wether the colums are price or generation data.
        # However, we will make our own columnnames below.
        # Row 3 is enough to unambigously identify the columns
        skiprows=None,
        index_col=None,
        parse_cols=None,  # None means: parse all columns
        thousands=','
    )

    # pandas on it's own authority sets first 2 columns as index
    # probably because the column's names are in merged cells
    df.index.rename(['date', 'hour'], inplace=True)
    df.reset_index(inplace=True)
    df['timestamp'] = pd.to_datetime(
        df['date'].astype(str) + ' ' +
        (df['hour'] - 1).astype(str) + ':00')
    df.set_index('timestamp', inplace=True)

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

    # Drop 3rd hour for (spring) DST-transition from df.
    df = df[~df.index.isin(dst_transitions_spring)]

    dst_arr = np.ones(len(df.index), dtype=bool)
    df.index = df.index.tz_localize('Europe/Copenhagen', ambiguous=dst_arr)
    df.index = df.index.tz_convert(None)

    source = 'Energinet.dk'
    colmap = {
        'DK-Vest': {
            'variable': 'price',
            'region': 'DK_1',
            'attribute': 'day_ahead',
            'source': source,
            'web': url,
            'unit': 'EUR'
        },
        'DK-Øst': {
            'variable': 'price',
            'region': 'DK_2',
            'attribute': 'day_ahead',
            'source': source,
            'web': url,
            'unit': 'EUR'
        },
        'Norge': {
            'variable': 'price',
            'region': 'NO',
            'attribute': 'day_ahead',
            'source': source,
            'web': url,
            'unit': 'EUR'
        },
        'Sverige (SE)': {
            'variable': 'price',
            'region': 'SE',
            'attribute': 'day_ahead',
            'source': source,
            'web': url,
            'unit': 'EUR'
        },
        'Sverige (SE3)': {
            'variable': 'price',
            'region': 'SE_3',
            'attribute': 'day_ahead',
            'source': source,
            'web': url,
            'unit': 'EUR'
        },
        'Sverige (SE4)': {
            'variable': 'price',
            'region': 'SE_4',
            'attribute': 'day_ahead',
            'source': source,
            'web': url,
            'unit': 'EUR'
        },
        'DE European Power Exchange': {
            'variable': 'price',
            'region': 'DE',
            'attribute': 'day_ahead',
            'source': source,
            'web': url,
            'unit': 'EUR'
        },
        'DK-Vest: Vindproduktion': {
            'variable': 'wind',
            'region': 'DK_1',
            'attribute': 'generation',
            'source': source,
            'web': url,
            'unit': 'MW'
        },
        'DK-Vest: Solcelle produktion (estimeret)': {
            'variable': 'solar',
            'region': 'DK_1',
            'attribute': 'generation',
            'source': source,
            'web': url,
            'unit': 'MW'
        },
        'DK-Øst: Vindproduktion': {
            'variable': 'wind',
            'region': 'DK_2',
            'attribute': 'generation',
            'source': source,
            'web': url,
            'unit': 'MW'
        },
        'DK-Øst: Solcelle produktion (estimeret)': {
            'variable': 'solar',
            'region': 'DK_2',
            'attribute': 'generation',
            'source': source,
            'web': url,
            'unit': 'MW'
        },
        'DK: Vindproduktion (onshore)': {
            'variable': 'wind_onshore',
            'region': 'DK',
            'attribute': 'generation',
            'source': source,
            'web': url,
            'unit': 'MW'
        },
        'DK: Vindproduktion (offshore)': {
            'variable': 'wind_offshore',
            'region': 'DK',
            'attribute': 'generation',
            'source': source,
            'web': url,
            'unit': 'MW'
        },
    }

    # Drop any column not in colmap
    df = df[list(colmap.keys())]

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

    return df
Read a file from energinet.dk into a DataFrame
Read a file from energinet.dk into a DataFrame
Read a file from energinet . dk into a DataFrame
def read_energinet_dk(filepath, url, headers):
    df = pd.read_excel(
        io=filepath,
        header=2,  
        skiprows=None,
        index_col=None,
        parse_cols=None,  
        thousands=','
    )
    df.index.rename(['date', 'hour'], inplace=True)
    df.reset_index(inplace=True)
    df['timestamp'] = pd.to_datetime(
        df['date'].astype(str) + ' ' +
        (df['hour'] - 1).astype(str) + ':00')
    df.set_index('timestamp', inplace=True)
    dst_transitions_spring = [
        d.replace(hour=2)
        for d in pytz.timezone('Europe/Copenhagen')._utc_transition_times
        if d.year >= 2000 and d.month == 3]
    df = df[~df.index.isin(dst_transitions_spring)]
    dst_arr = np.ones(len(df.index), dtype=bool)
    df.index = df.index.tz_localize('Europe/Copenhagen', ambiguous=dst_arr)
    df.index = df.index.tz_convert(None)
    source = 'Energinet.dk'
    colmap = {
        'DK-Vest': {
            'variable': 'price',
            'region': 'DK_1',
            'attribute': 'day_ahead',
            'source': source,
            'web': url,
            'unit': 'EUR'
        },
        'DK-Øst': {
            'variable': 'price',
            'region': 'DK_2',
            'attribute': 'day_ahead',
            'source': source,
            'web': url,
            'unit': 'EUR'
        },
        'Norge': {
            'variable': 'price',
            'region': 'NO',
            'attribute': 'day_ahead',
            'source': source,
            'web': url,
            'unit': 'EUR'
        },
        'Sverige (SE)': {
            'variable': 'price',
            'region': 'SE',
            'attribute': 'day_ahead',
            'source': source,
            'web': url,
            'unit': 'EUR'
        },
        'Sverige (SE3)': {
            'variable': 'price',
            'region': 'SE_3',
            'attribute': 'day_ahead',
            'source': source,
            'web': url,
            'unit': 'EUR'
        },
        'Sverige (SE4)': {
            'variable': 'price',
            'region': 'SE_4',
            'attribute': 'day_ahead',
            'source': source,
            'web': url,
            'unit': 'EUR'
        },
        'DE European Power Exchange': {
            'variable': 'price',
            'region': 'DE',
            'attribute': 'day_ahead',
            'source': source,
            'web': url,
            'unit': 'EUR'
        },
        'DK-Vest: Vindproduktion': {
            'variable': 'wind',
            'region': 'DK_1',
            'attribute': 'generation',
            'source': source,
            'web': url,
            'unit': 'MW'
        },
        'DK-Vest: Solcelle produktion (estimeret)': {
            'variable': 'solar',
            'region': 'DK_1',
            'attribute': 'generation',
            'source': source,
            'web': url,
            'unit': 'MW'
        },
        'DK-Øst: Vindproduktion': {
            'variable': 'wind',
            'region': 'DK_2',
            'attribute': 'generation',
            'source': source,
            'web': url,
            'unit': 'MW'
        },
        'DK-Øst: Solcelle produktion (estimeret)': {
            'variable': 'solar',
            'region': 'DK_2',
            'attribute': 'generation',
            'source': source,
            'web': url,
            'unit': 'MW'
        },
        'DK: Vindproduktion (onshore)': {
            'variable': 'wind_onshore',
            'region': 'DK',
            'attribute': 'generation',
            'source': source,
            'web': url,
            'unit': 'MW'
        },
        'DK: Vindproduktion (offshore)': {
            'variable': 'wind_offshore',
            'region': 'DK',
            'attribute': 'generation',
            'source': source,
            'web': url,
            'unit': 'MW'
        },
    }
    df = df[list(colmap.keys())]
    tuples = [tuple(colmap[col][level] for level in headers)
              for col in df.columns]
    df.columns = pd.MultiIndex.from_tuples(tuples, names=headers)
    return df
def read_energinet_dk ( filepath , url , headers ) : df = pd . read_excel ( io = filepath , header = 2 , skiprows = None , index_col = None , parse_cols = None , thousands = ',' ) df . index . rename ( [ 'date' , 'hour' ] , inplace = True ) df . reset_index ( inplace = True ) df [ 'timestamp' ] = pd . to_datetime ( df [ 'date' ] . astype ( str ) + ' ' + ( df [ 'hour' ] - 1 ) . astype ( str ) + ':00' ) df . set_index ( 'timestamp' , inplace = True ) dst_transitions_spring = [ d . replace ( hour = 2 ) for d in pytz . timezone ( 'Europe/Copenhagen' ) . _utc_transition_times if d . year >= 2000 and d . month == 3 ] df = df [ ~ df . index . isin ( dst_transitions_spring ) ] dst_arr = np . ones ( len ( df . index ) , dtype = bool ) df . index = df . index . tz_localize ( 'Europe/Copenhagen' , ambiguous = dst_arr ) df . index = df . index . tz_convert ( None ) source = 'Energinet.dk' colmap = { 'DK-Vest' : { 'variable' : 'price' , 'region' : 'DK_1' , 'attribute' : 'day_ahead' , 'source' : source , 'web' : url , 'unit' : 'EUR' } , 'DK-Øst': 'variable' : 'price' , 'region' : 'DK_2' , 'attribute' : 'day_ahead' , 'source' : source , 'web' : url , 'unit' : 'EUR' } , 'Norge' : { 'variable' : 'price' , 'region' : 'NO' , 'attribute' : 'day_ahead' , 'source' : source , 'web' : url , 'unit' : 'EUR' } , 'Sverige (SE)' : { 'variable' : 'price' , 'region' : 'SE' , 'attribute' : 'day_ahead' , 'source' : source , 'web' : url , 'unit' : 'EUR' } , 'Sverige (SE3)' : { 'variable' : 'price' , 'region' : 'SE_3' , 'attribute' : 'day_ahead' , 'source' : source , 'web' : url , 'unit' : 'EUR' } , 'Sverige (SE4)' : { 'variable' : 'price' , 'region' : 'SE_4' , 'attribute' : 'day_ahead' , 'source' : source , 'web' : url , 'unit' : 'EUR' } , 'DE European Power Exchange' : { 'variable' : 'price' , 'region' : 'DE' , 'attribute' : 'day_ahead' , 'source' : source , 'web' : url , 'unit' : 'EUR' } , 'DK-Vest: Vindproduktion' : { 'variable' : 'wind' , 'region' : 'DK_1' , 'attribute' : 'generation' , 'source' : source , 'web' : url , 'unit' : 'MW' } , 'DK-Vest: Solcelle produktion (estimeret)' : { 'variable' : 'solar' , 'region' : 'DK_1' , 'attribute' : 'generation' , 'source' : source , 'web' : url , 'unit' : 'MW' } , 'DK-Øst: Vindproduktion': 'variable' : 'wind' , 'region' : 'DK_2' , 'attribute' : 'generation' , 'source' : source , 'web' : url , 'unit' : 'MW' } , 'DK-Øst: Solcelle produktion (estimeret)': 'variable' : 'solar' , 'region' : 'DK_2' , 'attribute' : 'generation' , 'source' : source , 'web' : url , 'unit' : 'MW' } , 'DK: Vindproduktion (onshore)' : { 'variable' : 'wind_onshore' , 'region' : 'DK' , 'attribute' : 'generation' , 'source' : source , 'web' : url , 'unit' : 'MW' } , 'DK: Vindproduktion (offshore)' : { 'variable' : 'wind_offshore' , 'region' : 'DK' , 'attribute' : 'generation' , 'source' : source , 'web' : url , 'unit' : 'MW' } , } df = df [ list ( colmap . keys ( ) ) ] tuples = [ tuple ( colmap [ col ] [ level ] for level in headers ) for col in df . columns ] df . columns = pd . MultiIndex . from_tuples ( tuples , names = headers ) return df
Read a file from energinet.dk into a DataFrame
Read a file from energinet . dk into a DataFrame
'''Read a file from energinet.dk into a DataFrame''' # the column headers are taken from 3rd row. # 2nd row also contains header info like in a multiindex, # i.e. wether the colums are price or generation data. # However, we will make our own columnnames below. # Row 3 is enough to unambigously identify the columns # None means: parse all columns # pandas on it's own authority sets first 2 columns as index # probably because the column's names are in merged cells # Create a list of spring-daylight savings time (DST)-transitions # Drop 3rd hour for (spring) DST-transition from df. # Drop any column not in colmap # Create the MultiIndex
filepath
url
headers