Add a column ['Price Rate'] for the hourly data based on some conditions from the date column

Question

I would like to add a column ['Price Rate'] for the hourly data for the year 2021 from datetime based on some conditions from the date column:

Winter Weekdays

[12am - 7am] & [10pm - 12am ] Rate 1
[7am - 9am] & [9pm - 7pm]     Rate 2
[9- 4]                        Rate 3

Summer Weekdays

[12am - 7am] & [10pm - 12am ] Rate 4
[7am - 2pm] & [7pm - 10pm]    Rate 5
[2pm- 7pm]                    Rate 6

Holidays and weekends

[12am - 7am] & [10pm - 12am ] Rate 7
[7am - 10pm ]                 Rate 8

I started the code to generate the data but I was stuck after that.

import date
import holidays

df_date = pd.DataFrame(pd.date_range('2021-01-01', periods=8760, freq='H'), columns = ['Date'])

# Extract Holiday
us_holidays = holidays.US()

df_date['hols'] = pd.Series(df_date.index).apply(lambda x: holidays.CountryHoliday('AUS',prov='NSW').get(x)).values
df_date['hols'] = df_date['hols'].astype('bool').astype('int')

# Extract Weekend 
df_date["Is Weekend"] = df_date['Date'].dt.dayofweek > 4
df_date["Is Weekend"] = df_date["Is Weekend"].astype('bool').astype('int')

df_date

Beste · Answer 1 · 2022-04-15T12:29:27.033

You could basically create a function to check time ranges and other features and return a price rate which later you will use apply to create the new column.

First, let's create a function to check if a time is in a time range (ref:time_in_range):

from datetime import date
import datetime, holidays
import pandas as pd

def in_range(start, end, x):
    """Return true if x is in the range [start, end]"""
    if start <= end:
        return start <= x <= end
    else:
        return start <= x or x <= end

Then create a function to return the price rate:

def get_price_rate(item):
    if (item.is_hol or item.is_weekend):
        if (in_range(datetime.time(22, 0, 0), datetime.time(7, 0, 0), item.date_.time())):
            return 7
        elif (in_range(datetime.time(7, 0, 0), datetime.time(22, 0, 0), item.date_.time())):
            return 8
    elif (item.date_.month in [12,1,2] and ~item.is_weekend):
        if (in_range(datetime.time(22, 0, 0), datetime.time(7, 0, 0), item.date_.time())):
            return 1
        elif (in_range(datetime.time(7, 0, 0), datetime.time(9, 0, 0), item.date_.time()) or\
              in_range(datetime.time(19, 0, 0), datetime.time(21, 0, 0), item.date_.time())):
            return 2
        elif (in_range(datetime.time(9, 0, 0), datetime.time(16, 0, 0), item.date_.time())):
            return 3
    elif (item.date_.month in [6,7,8] and ~item.is_weekend):
        if (in_range(datetime.time(22, 0, 0), datetime.time(7, 0, 0), item.date_.time())):
            return 4
        elif (in_range(datetime.time(7, 0, 0), datetime.time(14, 0, 0), item.date_.time()) or\
              in_range(datetime.time(19, 0, 0), datetime.time(22, 0, 0), item.date_.time())):
            return 5
        elif (in_range(datetime.time(14, 0, 0), datetime.time(19, 0, 0), item.date_.time())):
            return 6

Then you just need to insert the new column by applying the price rate function.

# dates
df_date = pd.DataFrame(pd.date_range('2021-01-01', periods=8760, freq='H'), columns = ['date_'])

# extract holiday
us_holidays = holidays.US()
df_date['is_hol'] = df_date.apply(lambda row: holidays.CountryHoliday('AUS',prov='NSW').get(row.date_), axis = 1)
df_date.is_hol = df_date.is_hol.astype('bool').astype('int')

# extract weekend 
df_date["is_weekend"] = df_date.date_.dt.dayofweek > 4
df_date.is_weekend = df_date.is_weekend.astype('bool').astype('int')

# set price_rate
df_date["price_rate"] = df_date.apply(lambda row: get_price_rate(row), axis = 1)

There was an issue with your holiday extraction which I have fixed. Also, there are some missing hour ranges that are not assigned to any price rate. For those, you will get NaN assigned. Also, I have edited the time ranges a bit ([12 am - 7 am] & [10 pm - 12 am] are the same as [10 pm - 7 am]), so you may need to check the desired ranges and confirm if it fits your needs. However, this solution does not consider timezones or hemispheres. If you want a more detailed implementation, you may search about them in other threads since a lot of solutions for season and time extraction were shared.

Thank you so much! It`s a great solution. What if I need different rates for holidays & weekends in winter and summer? holidays & weekends (winter) 7, 8 holidays & weekends (summer ) 9, 10 — AAA, Apr 15 '22 at 12:13
You may do it just by editing the first if statement in the get_price_rate function above. The first statement basically checks if the date is a holiday or a weekend (initially it was and but I changed the answer as or since it is probably what you wanted). Now instead, you may replace the first if statement with an if and a following elif statement which checks also for the season. For example: `if ((item.is_hol or item.is_weekend) and item.date_.month in [6,7,8]): #summer` `elif ((item.is_hol or item.is_weekend) and item.date_.month in [12,1,2]): #winter` — Beste, Apr 15 '22 at 12:27

Add a column ['Price Rate'] for the hourly data based on some conditions from the date column

1 Answers1