获取打开的每个文件的第一、第二和第三个日期

Question

我有这段代码按col1分组，以获得文件打开时间顺序的NSmalle日期，但它似乎是随机顺序出现的，但这是我不需要的。 这是我的密码

data=(df.groupby(['col1']).date
   .apply(lambda x: pd.Series(x.value_counts()
                               .nsmallest(3).index))
   .unstack(level=1).fillna(0).astype(str))

输出：

  col1              0                                1                          2
  file 1    2020-05-07 09:52:44.502     2020-05-06 04:53:32.449     2020-05-18 06:12:27.872
  file 2    2020-05-08 09:02:44.502     2020-05-04 04:53:32.449     2020-05-19 06:12:27.872

我想要的输出：

       col1                 0                                1                          2
      file 1   2020-05-06 04:53:32.449  2020-05-07 09:52:44.502     2020-05-18 06:12:27.872
      file 2    2020-05-04 04:53:32.449 2020-05-08 09:02:44.502     2020-05-19 06:12:27.872

数据：

'{"col1":{"0":"file1","1":"file2","2":"file3","3":"file","4":"file1","5":"file2","6":"file3","7":"file4","8":"file3","9":"file1"},"date":{"0":"2020-05-01 00:38:38.154","1":"2020-05-01 00:38:53.904","2":"2020-05-01 00:43:52.487","3":"2020-05-01 03:35:40.399","4":"2020-05-01 04:19:32.615","5":"2020-05-01 04:22:15.545","6":"2020-05-01 04:22:47.439","7":"2020-05-01 04:23:48.706","8":"2020-05-01 04:25:26.140","9":"2020-05-01 04:28:36.257"}}'

顺便说一句，日期是字符串格式，我无法将其转换为datetime，因为我需要在下一阶段为每个日期指定一个字符串，以便进行一些差异化

请帮助，我如何才能获得所需顺序的输出

Answer 1

我想你想要这个：

df['count'] = df.sort_values('date').groupby('col1').cumcount()

df.set_index(['col1', 'count']).query('count <= 2')['date'].unstack()

输出：

count                        0                        1                        2
col1                                                                            
file   2020-05-01 03:35:40.399                      NaN                      NaN
file1  2020-05-01 00:38:38.154  2020-05-01 04:19:32.615  2020-05-01 04:28:36.257
file2  2020-05-01 00:38:53.904  2020-05-01 04:22:15.545                      NaN
file3  2020-05-01 00:43:52.487  2020-05-01 04:22:47.439  2020-05-01 04:25:26.140
file4  2020-05-01 04:23:48.706                      NaN                      NaN