我有一个包含两列的DataFrame,一个日期和一个类别。我想根据以下规则创建一个新的日期列:如果category是B
,则值应该是最接近日期的工作日(仅来自过去或日期本身),否则是日期列本身的值。
我将工作日定义为不在周末的任何一天,也不存在于下面最小示例中定义的列表holidays
中。
请考虑以下DataFrame df
import datetime as dt
import pandas as pd
from IPython.display import display
holidays = [dt.datetime(2018, 10, 11)]
df = pd.DataFrame({"day": ["2018-10-10", "2018-10-11", "2018-10-12",
"2018-10-13", "2018-10-14", "2018-10-15"
],
"category":["A", "B", "C", "B", "C", "A"]
}
)
df["day"] = pd.to_datetime(df.day, format="%Y-%m-%d")
display(df)
day category
0 2018-10-10 A
1 2018-10-11 B
2 2018-10-12 C
3 2018-10-13 B
4 2018-10-14 C
5 2018-10-15 A
如何获得第三列,其值如下所示?
2018-10-10
2018-10-10
2018-10-12
2018-10-12
2018-10-14
2018-10-15
我创建了一个在处理列表时查找最后一个工作日的函数,如果这有任何帮助的话。
# creates a list whose elements are all days in the years 2017, 2018 and 2019
days = [dt.datetime(2017, 1 , 1) + dt.timedelta(k) for k in range(365*3)]
def lastt_bus_day(date):
return max(
[d for d in days if d.weekday() not in [5, 6]
and d not in holidays
and d <= date
]
)
for d in df.day:
print(last_bus_day(d))
#prints
2018-10-10 00:00:00
2018-10-10 00:00:00
2018-10-12 00:00:00
2018-10-12 00:00:00
2018-10-12 00:00:00
2018-10-15 00:00:00
发布于 2018-10-10 22:17:50
你已经很接近了:
holidays = [dt.date(2018, 10, 11)]
days = [dt.date(2017, 1 , 1) + dt.timedelta(k) for k in range(365*3)]
def lastt_bus_day(date, format='%Y-%m-%d'):
if not isinstance(date, dt.date):
date = dt.datetime.strptime(date, format).date()
return max(
[d for d in days if d.weekday() not in [5, 6]
and d not in holidays
and d <= date
]
)
然后将其应用于整个数据帧:
df['business_day'] = df['day']
df['business_day'].loc[df['category'] == 'B'] = df.loc[df['category'] == 'B', 'day'].apply(lastt_bus_day)
发布于 2018-10-10 22:26:06
通过使用pandas
BDay
df.day.update(df.loc[(df.category=='B')&((df.day.dt.weekday.isin([5,6])|(df.day.isin(holidays )))),'day']-pd.tseries.offsets.BDay(1))
df
Out[22]:
category day
0 A 2018-10-10
1 B 2018-10-10
2 C 2018-10-12
3 B 2018-10-12
4 C 2018-10-14
5 A 2018-10-15
发布于 2018-10-10 22:29:17
您可以在子集where category == 'B'
上对所有非假日工作日使用pd.merge_asof
,并为所有其他类别分配日期。设置allow_exact_matches=False
以确保您不会与B
的同一天匹配。
import pandas as pd
mask = df.category == 'B'
# DataFrame of all non-holiday days
df_days = pd.DataFrame(days, columns=['day'])
df_days = df_days.loc[(df_days.day.dt.weekday<5) & ~df_days.day.isin(holidays)]
dfb = pd.merge_asof(
df.loc[mask],
df_days.assign(new_day=df_days.day),
on='day',
direction='backward',
allow_exact_matches=False)
dfnb = df.assign(new_day = df.day)[~mask]
pd.concat([dfnb, dfb], ignore_index=True).sort_values('day')
输出:
day category new_day
0 2018-10-10 A 2018-10-10
4 2018-10-11 B 2018-10-10
1 2018-10-12 C 2018-10-12
5 2018-10-13 B 2018-10-12
2 2018-10-14 C 2018-10-14
3 2018-10-15 A 2018-10-15
https://stackoverflow.com/questions/52742118
复制相似问题