CuriousAboutEverything
Reputation: 25
Pandas (Python): How to apply values to similar row?
sorry for the badly phrased question, currently only the first hour is updated with holiday.
e.g.
- 2013-01-01 00:00:00 - New Years Day
- 2013-01-01 00:00:00 - None
- 2013-01-01 00:00:00 - None
I would like to apply similar holidays to the same date using Pandas (Python).
What would be the most efficient method to apply the holiday to the same dates, there are a number of other holidays to apply as well?
Thank you in advance!
Upvotes: 2
Views: 69
Answers (3)
Scott Boston
Reputation: 153460
IIUC, you have only the first hour of a day listed with a holiday. Here is a small sample of a dataframe with two months of data and three holidays on three separate days.
import pandas as pd
import numpy as np
df = pd.DataFrame({'temp':np.random.randint(50,110, 60*24)}, index=pd.date_range('2013-01-01', periods=(60*24), freq='H'))
df['Holiday'] = np.nan
df.loc['2013-01-01 00:00:00', 'Holiday'] = 'New Years Day'
df.loc['2013-02-02 00:00:00', 'Holiday'] = 'Groundhog Day'
df.loc['2013-02-14 00:00:00', 'Holiday'] = "Valentine's Day"
Now, let's use groupby with day from DatetimeIndex and ffill:
df['Holiday'] = df.groupby(df.index.day)['Holiday'].ffill()
Let's look at a few records:
print(df.head(40))
print(df['2013-02-02'])
print(df['2013-02-13':'2013-02-15'])
Output:
temp Holiday
2013-01-01 00:00:00 51 New Years Day
2013-01-01 01:00:00 71 New Years Day
2013-01-01 02:00:00 61 New Years Day
2013-01-01 03:00:00 90 New Years Day
2013-01-01 04:00:00 77 New Years Day
2013-01-01 05:00:00 69 New Years Day
2013-01-01 06:00:00 50 New Years Day
2013-01-01 07:00:00 99 New Years Day
2013-01-01 08:00:00 86 New Years Day
2013-01-01 09:00:00 72 New Years Day
2013-01-01 10:00:00 89 New Years Day
2013-01-01 11:00:00 62 New Years Day
2013-01-01 12:00:00 53 New Years Day
2013-01-01 13:00:00 91 New Years Day
2013-01-01 14:00:00 51 New Years Day
2013-01-01 15:00:00 93 New Years Day
2013-01-01 16:00:00 97 New Years Day
2013-01-01 17:00:00 83 New Years Day
2013-01-01 18:00:00 87 New Years Day
2013-01-01 19:00:00 58 New Years Day
2013-01-01 20:00:00 84 New Years Day
2013-01-01 21:00:00 92 New Years Day
2013-01-01 22:00:00 106 New Years Day
2013-01-01 23:00:00 104 New Years Day
2013-01-02 00:00:00 78 NaN
2013-01-02 01:00:00 104 NaN
2013-01-02 02:00:00 96 NaN
2013-01-02 03:00:00 103 NaN
2013-01-02 04:00:00 60 NaN
2013-01-02 05:00:00 87 NaN
2013-01-02 06:00:00 108 NaN
2013-01-02 07:00:00 85 NaN
2013-01-02 08:00:00 67 NaN
2013-01-02 09:00:00 61 NaN
2013-01-02 10:00:00 91 NaN
2013-01-02 11:00:00 79 NaN
2013-01-02 12:00:00 99 NaN
2013-01-02 13:00:00 82 NaN
2013-01-02 14:00:00 75 NaN
2013-01-02 15:00:00 90 NaN
temp Holiday
2013-02-02 00:00:00 82 Groundhog Day
2013-02-02 01:00:00 58 Groundhog Day
2013-02-02 02:00:00 102 Groundhog Day
2013-02-02 03:00:00 90 Groundhog Day
2013-02-02 04:00:00 79 Groundhog Day
2013-02-02 05:00:00 50 Groundhog Day
2013-02-02 06:00:00 50 Groundhog Day
2013-02-02 07:00:00 83 Groundhog Day
2013-02-02 08:00:00 80 Groundhog Day
2013-02-02 09:00:00 50 Groundhog Day
2013-02-02 10:00:00 52 Groundhog Day
2013-02-02 11:00:00 69 Groundhog Day
2013-02-02 12:00:00 100 Groundhog Day
2013-02-02 13:00:00 61 Groundhog Day
2013-02-02 14:00:00 62 Groundhog Day
2013-02-02 15:00:00 76 Groundhog Day
2013-02-02 16:00:00 83 Groundhog Day
2013-02-02 17:00:00 109 Groundhog Day
2013-02-02 18:00:00 109 Groundhog Day
2013-02-02 19:00:00 81 Groundhog Day
2013-02-02 20:00:00 52 Groundhog Day
2013-02-02 21:00:00 108 Groundhog Day
2013-02-02 22:00:00 68 Groundhog Day
2013-02-02 23:00:00 75 Groundhog Day
temp Holiday
2013-02-13 00:00:00 93 NaN
2013-02-13 01:00:00 93 NaN
2013-02-13 02:00:00 74 NaN
2013-02-13 03:00:00 97 NaN
2013-02-13 04:00:00 58 NaN
2013-02-13 05:00:00 103 NaN
2013-02-13 06:00:00 79 NaN
2013-02-13 07:00:00 65 NaN
2013-02-13 08:00:00 72 NaN
2013-02-13 09:00:00 100 NaN
2013-02-13 10:00:00 66 NaN
2013-02-13 11:00:00 60 NaN
2013-02-13 12:00:00 95 NaN
2013-02-13 13:00:00 51 NaN
2013-02-13 14:00:00 71 NaN
2013-02-13 15:00:00 58 NaN
2013-02-13 16:00:00 58 NaN
2013-02-13 17:00:00 98 NaN
2013-02-13 18:00:00 61 NaN
2013-02-13 19:00:00 63 NaN
2013-02-13 20:00:00 57 NaN
2013-02-13 21:00:00 102 NaN
2013-02-13 22:00:00 69 NaN
2013-02-13 23:00:00 86 NaN
2013-02-14 00:00:00 94 Valentine's Day
2013-02-14 01:00:00 64 Valentine's Day
2013-02-14 02:00:00 62 Valentine's Day
2013-02-14 03:00:00 59 Valentine's Day
2013-02-14 04:00:00 93 Valentine's Day
2013-02-14 05:00:00 99 Valentine's Day
2013-02-14 06:00:00 64 Valentine's Day
2013-02-14 07:00:00 80 Valentine's Day
2013-02-14 08:00:00 89 Valentine's Day
2013-02-14 09:00:00 96 Valentine's Day
2013-02-14 10:00:00 60 Valentine's Day
2013-02-14 11:00:00 76 Valentine's Day
2013-02-14 12:00:00 82 Valentine's Day
2013-02-14 13:00:00 65 Valentine's Day
2013-02-14 14:00:00 90 Valentine's Day
2013-02-14 15:00:00 62 Valentine's Day
2013-02-14 16:00:00 64 Valentine's Day
2013-02-14 17:00:00 98 Valentine's Day
2013-02-14 18:00:00 52 Valentine's Day
2013-02-14 19:00:00 72 Valentine's Day
2013-02-14 20:00:00 108 Valentine's Day
2013-02-14 21:00:00 85 Valentine's Day
2013-02-14 22:00:00 87 Valentine's Day
2013-02-14 23:00:00 62 Valentine's Day
2013-02-15 00:00:00 106 NaN
2013-02-15 01:00:00 82 NaN
2013-02-15 02:00:00 77 NaN
2013-02-15 03:00:00 52 NaN
2013-02-15 04:00:00 94 NaN
2013-02-15 05:00:00 71 NaN
2013-02-15 06:00:00 95 NaN
2013-02-15 07:00:00 96 NaN
2013-02-15 08:00:00 71 NaN
2013-02-15 09:00:00 69 NaN
2013-02-15 10:00:00 85 NaN
2013-02-15 11:00:00 92 NaN
2013-02-15 12:00:00 106 NaN
2013-02-15 13:00:00 77 NaN
2013-02-15 14:00:00 65 NaN
2013-02-15 15:00:00 104 NaN
2013-02-15 16:00:00 98 NaN
2013-02-15 17:00:00 107 NaN
2013-02-15 18:00:00 106 NaN
2013-02-15 19:00:00 67 NaN
2013-02-15 20:00:00 59 NaN
2013-02-15 21:00:00 81 NaN
2013-02-15 22:00:00 56 NaN
2013-02-15 23:00:00 75 NaN
Note: In this dataframe your datetime column is in the index.
Upvotes: 1
Heikki Pulkkinen
Reputation: 277
Using a library called holidays together with pandas apply could be a great solution to your problem. Here is a short contained example example
import pandas as pd
import holidays
us_holidays = holidays.UnitedStates()
# Create a sample DataFrame. You can just use your own
data = pd.DataFrame(pd.date_range('2020-01-01', '2020-01-30'), columns=['date'])
data['holiday'] = data['date'].apply(lambda x: us_holidays.get(x))
print(data)
Output
date holiday
0 2020-01-01 New Year's Day
1 2020-01-02 None
2 2020-01-03 None
3 2020-01-04 None
4 2020-01-05 None
5 2020-01-06 None
6 2020-01-07 None
7 2020-01-08 None
8 2020-01-09 None
9 2020-01-10 None
10 2020-01-11 None
11 2020-01-12 None
12 2020-01-13 None
13 2020-01-14 None
14 2020-01-15 None
15 2020-01-16 None
16 2020-01-17 None
17 2020-01-18 None
18 2020-01-19 None
19 2020-01-20 Martin Luther King, Jr. Day
20 2020-01-21 None
21 2020-01-22 None
22 2020-01-23 None
23 2020-01-24 None
24 2020-01-25 None
25 2020-01-26 None
26 2020-01-27 None
27 2020-01-28 None
28 2020-01-29 None
29 2020-01-30 None
Upvotes: 2
zach-s
Reputation: 89
You can try using the apply method: https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.apply.html
The input to this is the function you want to be applied to each row. And in this case "axis" should be zero so that it is applied to each row.
Upvotes: 1
Related Questions
- Python - How to apply values from other columns
- Using pandas, how to assign the values in the column based on the values from another colum?
- How to set the values of a row based on similar rows in pandas dataframe?
- Assigning values to duplicate row in pandas dataframe
- Pandas: How to apply a value to a group of rows based on a specific row value?
- Pandas apply based value based on value in another column
- Applying values to column based on different columns
- Assign values to column based on same values in another column
- apply values to pandas column
- Duplicating some rows and changing some values in pandas