Search code examples
pythonpandasdataframedatetimeindex

create a "Business Hour" column to indicate whether a particular timestamp was within business hours in pandas

I'm trying to add a "Buiness_hour" to the dataframe base on value of the datetime index. Say if an entry is fall between 0800 to 1800, the entry under the "Business_hour" would return "Yes" else "No".

The existing df is like:

Index UserID
2021-03-31 20:54:54 143173
2021-03-31 22:54:54 143173
2021-03-31 09:54:54 143173

I would like to insert the "business hr" column so I can find out the number of transactions made outside business hours | Index | UserID | Business_hr | |-------|-------|-------------| |2021-03-31 20:54:54|143173|No| |2021-03-31 22:54:54|143173|No| |2021-03-31 09:54:54|143173|Yes|

I tried using apply with lambda function

df['Business_hour'] = df.index.apply(lambda x: 'Yes' if df.index.hour >=9 and df.index.hour < 18 else 'No')

and it says that"'DatetimeIndex' object has no attribute 'apply'"

i then tried the more basic solution and having the same results:


def business_hr(x):
    if x >=8 :
        return 'Yes'
    if x <=18:
        return "Yes"
    else:
        'No'

df['Business_hr'] = df.index.hour.apply(business_hr)

I am new to pandas so I tried to get my head round it. Spent hours on research but seems no luck so far so hopefully someone will lend me some helps.

Thanks first for your helps and advise.

Solution

  • Use np.where:

    m = (9 <= df.index.hour) & (df.index.hour < 18)
df['Business_hour'] = np.where(m, 'Yes', 'No')
print(df)

# Output
                     UserID Business_hour
2021-03-31 20:54:54  143173            No
2021-03-31 22:54:54  143173            No
2021-03-31 09:54:54  143173           Yes

    If you want to use your function, replace apply (DataFrame) by map (Series):

    def business_hr(x):
    return 'Yes' if 8 <= x < 18 else 'No'

df['Business_hour'] = df.index.hour.map(business_hr)
print(df)

# Output
                     UserID Business_hour
2021-03-31 20:54:54  143173            No
2021-03-31 22:54:54  143173            No
2021-03-31 09:54:54  143173           Yes