英文:
How to get the earlier hour and the latest hour in each day on a pandas Dataframe?
问题
我有一个数据框，看起来像这样：
data['date']
date
0 2018-01-01 03:00:00
1 2018-01-01 04:00:00
2 2018-01-01 08:00:00
3 2018-01-02 04:00:00
4 2018-01-02 06:00:00
5 2018-01-02 12:00:00
6 2018-01-03 05:00:00
7 2018-01-03 07:00:00
8 2018-01-03 17:00:00
我想要创建两个新列，分别表示每天最早和最晚的小时，类似这样：
date_daily earlier_hour latest_hour
0 2018-01-01 03:00:00 08:00:00
1 2018-01-02 04:00:00 12:00:00
2 2018-01-03 05:00:00 17:00:00
感谢您的帮助。
英文:
I have a DF that looks like that:
data['date']
date0 2018-01-01 03:00:001 2018-01-01 04:00:002 2018-01-01 08:00:003 2018-01-02 04:00:004 2018-01-02 06:00:005 2018-01-02 12:00:006 2018-01-03 05:00:007 2018-01-03 07:00:008 2018-01-03 17:00:00
I would like to create to new columns with the earlier and latest hour in each day, something like that:
date_daily earlier_hour latest_hour0 2018-01-01 03:00:00 08:00:001 2018-01-02 04:00:00 12:00:002 2018-01-03 05:00:00 17:00:00
Thanks for your help.
答案1
得分: 4
你可以使用自定义的 groupby.agg 与 dt.normalize 来获取日期部分，然后使用 dt.time 来获取时间部分：
df['date'] = pd.to_datetime(df['date'])(df.groupby(df['date'].dt.normalize())['date'].agg(['min', 'max']).apply(lambda s: s.dt.time).reset_index())
输出：
date min max0 2018-01-01 03:00:00 08:00:001 2018-01-02 04:00:00 12:00:002 2018-01-03 05:00:00 17:00:00
自定义列名
df['date'] = pd.to_datetime(df['date'])(df.groupby(df['date'].dt.normalize().rename('date_daily')).agg(**{'earlier_hour': ('date', 'min'),'later_hour': ('date', 'max')}).apply(lambda s: s.dt.time).reset_index())
输出：
date_daily earlier_hour later_hour0 2018-01-01 03:00:00 08:00:001 2018-01-02 04:00:00 12:00:002 2018-01-03 05:00:00 17:00:00
英文:
You can use a custom groupby.agg using dt.normalize to get the date only as grouper, then dt.time for the time component:
df['date'] = pd.to_datetime(df['date'])(df.groupby(df['date'].dt.normalize())['date'].agg(['min', 'max']).apply(lambda s: s.dt.time).reset_index())
Output:
date min max0 2018-01-01 03:00:00 08:00:001 2018-01-02 04:00:00 12:00:002 2018-01-03 05:00:00 17:00:00
with custom names
df['date'] = pd.to_datetime(df['date'])(df.groupby(df['date'].dt.normalize().rename('date_daily')).agg(**{'earlier_hour': ('date', 'min'),'later_hour': ('date', 'max')}).apply(lambda s: s.dt.time).reset_index())
Output:
date_daily earlier_hour later_hour0 2018-01-01 03:00:00 08:00:001 2018-01-02 04:00:00 12:00:002 2018-01-03 05:00:00 17:00:00
答案2
得分: 1
这是使用 GroupBy.apply/strftime 的一种选项：
out = (data.groupby(data["date"].dt.date)["date"].agg(["min", "max"]).apply(lambda s: s.dt.strftime("%H:%M:%S")).reset_index().set_axis(["date_daily", "earlier_hour", "latest_hour"], axis=1))
输出：
print(out)date_daily earlier_hour latest_hour0 2018-01-01 03:00:00 08:00:001 2018-01-02 04:00:00 12:00:002 2018-01-03 05:00:00 17:00:00
英文:
Here is one option with GroupBy.apply/strftime :
out = (data.groupby(data["date"].dt.date)["date"].agg(["min", "max"]).apply(lambda s: s.dt.strftime("%H:%M:%S")).reset_index().set_axis(["date_daily", "earlier_hour", "latest_hour"], axis=1))
Output :
print(out)date_daily earlier_hour latest_hour0 2018-01-01 03:00:00 08:00:001 2018-01-02 04:00:00 12:00:002 2018-01-03 05:00:00 17:00:00
答案3
得分: 0
你可以在分组之前对日期进行排序，以提取所需时间的排名：
df['date'] = pd.to_datetime(df['date'])# 用 0 表示较早的小时# 用 1 表示第二早的小时# 用 -1 表示最晚的小时out = (df.sort_values('date').drop_duplicates('date').groupby(pd.Grouper(freq='D', key='date')).agg(earlier_hour=('date', lambda x: x.iloc[0].time()),second_earlier_hour=('date', lambda x: x.iloc[1].time()),latest_hour=('date', lambda x: x.iloc[-1].time())).reset_index())
输出：
>>> outdate earlier_hour second_earlier_hour latest_hour0 2018-01-01 03:00:00 04:00:00 08:00:001 2018-01-02 04:00:00 06:00:00 12:00:002 2018-01-03 05:00:00 07:00:00 17:00:00
英文:
You can sort the date before grouping to extract the rank of the desired time:
df['date'] = pd.to_datetime(df['date'])# Use 0 for the earlier hour# Use 1 for the second earlier hour# Use -1 for the latest hourout = (df.sort_values('date').drop_duplicates('date').groupby(pd.Grouper(freq='D', key='date')).agg(earlier_hour=('date', lambda x: x.iloc[0].time()),second_earlier_hour=('date', lambda x: x.iloc[1].time())latest_hour=('date', lambda x: x.iloc[-1].time())).reset_index())
Output:
>>> outdate earlier_hour second_earlier_hour latest_hour0 2018-01-01 03:00:00 04:00:00 08:00:001 2018-01-02 04:00:00 06:00:00 12:00:002 2018-01-03 05:00:00 07:00:00 17:00:00
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论