问题描述
我是 pandas 的新手,现在我不知道如何安排我的时间系列,看看吧:
I am new on pandas and for now i don't get how to arrange my time serie, take a look at it :
date & time of connection 19/06/2017 12:39 19/06/2017 12:40 19/06/2017 13:11 20/06/2017 12:02 20/06/2017 12:04 21/06/2017 09:32 21/06/2017 18:23 21/06/2017 18:51 21/06/2017 19:08 21/06/2017 19:50 22/06/2017 13:22 22/06/2017 13:41 22/06/2017 18:01 23/06/2017 16:18 23/06/2017 17:00 23/06/2017 19:25 23/06/2017 20:58 23/06/2017 21:03 23/06/2017 21:05
这是 130 k 原始数据集的样本,我试过:df.groupby('连接的日期和时间')['日期&连接时间'].apply(list)
This is a sample of a dataset of 130 k raws,I tried : df.groupby('date & time of connection')['date & time of connection'].apply(list)
我猜还不够
我想我应该:
- 创建一个索引从 dd/mm/yyyy 到 dd/mm/yyyy 的字典
- 将连接的日期和时间"类型 dateTime 转换为 Date
- 连接日期和时间"的分组和计数日期
- 把我数到的数字放进字典里?
你觉得我的逻辑怎么样?你知道一些tutos吗?非常感谢
What do you think about my logic ? Do you know some tutos ? Thank you very much
推荐答案
你可以使用dt.floor 用于转换为 dates,然后转换为 value_counts 或 groupby 与 大小:
You can use dt.floor for convert to dates and then value_counts or groupby with size:
df = (pd.to_datetime(df['date & time of connection']) .dt.floor('d') .value_counts() .rename_axis('date') .reset_index(name='count')) print (df) date count 0 2017-06-23 6 1 2017-06-21 5 2 2017-06-19 3 3 2017-06-22 3 4 2017-06-20 2
或者:
s = pd.to_datetime(df['date & time of connection']) df = s.groupby(s.dt.floor('d')).size().reset_index(name='count') print (df) date & time of connection count 0 2017-06-19 3 1 2017-06-20 2 2 2017-06-21 5 3 2017-06-22 3 4 2017-06-23 6
时间安排:
np.random.seed(1542) N = 220000 a = np.unique(np.random.randint(N, size=int(N/2))) df = pd.DataFrame(pd.date_range('2000-01-01', freq='37T', periods=N)).drop(a) df.columns = ['date & time of connection'] df['date & time of connection'] = df['date & time of connection'].dt.strftime('%d/%m/%Y %H:%M:%S') print (df.head()) In [193]: %%timeit ...: df['date & time of connection']=pd.to_datetime(df['date & time of connection']) ...: df1 = df.groupby(by=df['date & time of connection'].dt.date).count() ...: 539 ms ± 45.9 ms per loop (mean ± std. dev. of 7 runs, 1 loop each) In [194]: %%timeit ...: df1 = (pd.to_datetime(df['date & time of connection']) ...: .dt.floor('d') ...: .value_counts() ...: .rename_axis('date') ...: .reset_index(name='count')) ...: 12.4 ms ± 350 μs per loop (mean ± std. dev. of 7 runs, 100 loops each) In [195]: %%timeit ...: s = pd.to_datetime(df['date & time of connection']) ...: df2 = s.groupby(s.dt.floor('d')).size().reset_index(name='count') ...: 17.7 ms ± 140 μs per loop (mean ± std. dev. of 7 runs, 100 loops each)