python - 组合 Groupby 功能代码(带和不带 grouper)

Question

我在我的数据集上编写了这两个 groupby 函数，第一个函数对我的数据进行分组，并将数据的日期时间分隔为开始日期时间、结束日期时间。

这是数据集:

Blast Hole	East Coordinate	North Coordinate	Collar	Theoritical Depth	Tag Detector ID	Date and Time	Detection_Location	Detection Date & Time
64	16745.42	107390.32	2634.45	15.95	385656531	23-08-2018 2:39:34 PM	CV23	2018-09-08 14:18:17
61	16773.48	107382.6	2634.68	16.18	385760755	23-08-2018 2:38:32 PM	CV23	2018-09-08 14:24:19
63	16755.07	107387.68	2634.58	16.08	385262370	23-08-2018 2:39:30 PM	CV23	2018-09-08 14:12:42
105	16764.83	107347.67	2634.74	16.24	385742468	23-08-2018 2:41:29 PM	CV22	2018-09-06 20:02:46
100	16752.74	107360.32	2634.33	15.83	385112050	23-08-2018 2:41:08 PM	CV22	2018-09-06 20:15:42
99	16743.1	107362.96	2634.36	15.86	385087366	23-08-2018 2:41:05 PM	CV22	2018-09-06 20:49:21
35	16747.75	107417.68	2635.9	17.4	385453358	23-08-2018 2:36:09 PM	CV22	2018-09-23 05:47:44
5	16757.27	107452.4	2636	17.5	385662254	23-08-2018 2:35:03 PM	CV22	2018-09-23 05:01:12
19	16770.89	107420.83	2634.81	16.31	385826979	23-08-2018 2:35:50 PM	CV22	2018-09-23 05:52:54

第二部分帮助我像以前一样将分组数据框中的所有列保留为逗号分隔。

我面临着如何将这两个代码组合成一个代码并执行操作的问题:

df2 = (df1.groupby([pd.Grouper(key = 'Detection Date & Time', freq = 'H'),df.Detection_Location])
      ['Detection Date & Time'].agg(['first','last','size'])).reset_index()

df2 = df1.groupby("Detection date & Hour").agg({
        'Blast Hole': lambda x: ','.join([str(n) for n in x]),
        'East Coordinate': lambda x: ','.join([str(n) for n in x]),
        'North Coordinate': lambda x: ','.join([str(n) for n in x]),
        'Tag Detector ID': lambda x: ','.join([str(n) for n in x]),
        'Detection_Location': lambda x: min(x),
        'Detection Date & Time' : lambda x: len(x)}).reset_index().rename(columns = {'Detection Date & Time' : 'Tags'})

这是期望的结果:

Detection_Location_	first	last	size	Blast Hole	East Coordinate	North Coordinate	Tag Detector ID
CV22	2018-09-06 20:02:46	2018-09-06 20:49:21	3	105,100,99	16764.83,16752.74,16743.1	107347.67,107360.32,107362.96	385742468,385112050,385087366
CV23	2018-09-08 14:12:42	2018-09-08 14:24:19	3	64,61,63	16745.42,16773.48,16755.07	107390.32,107382.6,107387.68	385656531,385760755,385262370
CV22	2018-09-23 05:01:12	2018-09-23 05:52:54	3	35,5,19	16747.75,16757.27,16770.89	107417.68,107452.4,107420.83	385453358,385662254,385826979

谢谢

Answer 1

第一个想法是，groupby 中是否需要不同的值 - 第一个 df21 带有 Grouper，第二个仅带有 Grouper :

df1['Date and Time'] = pd.to_datetime(df1['Date and Time'])
df1['Detection Date & Time'] = pd.to_datetime(df1['Detection Date & Time'])


df21 = (df1.groupby([pd.Grouper(key = 'Detection Date & Time', freq = 'H'),
                     df1.Detection_Location])
      ['Detection Date & Time'].agg(['first','last','size']))
#print (df21)


f = lambda x: ','.join(x.astype(str))
df22=(df1.groupby(pd.Grouper(key = 'Detection Date & Time', freq = 'H')).agg({
        'Blast Hole': f,
        'East Coordinate': f,
        'North Coordinate': f,
        'Tag Detector ID': f,
        'Detection_Location': 'min',
        'Detection Date & Time' : 'size'})
        .dropna()
        .rename(columns = {'Detection Date & Time' : 'Tags'})
        .set_index('Detection_Location', append=True))

#print (df22)

<小时/>

df = pd.merge(df21, df22, left_index=True, right_index=True).reset_index()
print (df)
  Detection Date & Time Detection_Location               first  \
0   2018-09-06 20:00:00               CV22 2018-09-06 20:02:46   
1   2018-09-08 14:00:00               CV23 2018-09-08 14:18:17   
2   2018-09-23 05:00:00               CV22 2018-09-23 05:47:44   

                 last  size  Blast Hole             East Coordinate  \
0 2018-09-06 20:49:21     3  105,100,99   16764.83,16752.74,16743.1   
1 2018-09-08 14:12:42     3    63,64,61  16755.07,16745.42,16773.48   
2 2018-09-23 05:52:54     3     5,35,19  16757.27,16747.75,16770.89   

                North Coordinate                Tag Detector ID  Tags  
0  107347.67,107360.32,107362.96  385742468,385112050,385087366     3  
1   107387.68,107390.32,107382.6  385262370,385656531,385760755     3  
2   107452.4,107417.68,107420.83  385662254,385453358,385826979     3  

编辑:

如果需要按Grouper和列一起分组:

df1['Date and Time'] = pd.to_datetime(df1['Date and Time'])
df1['Detection Date & Time'] = pd.to_datetime(df1['Detection Date & Time'])


f = lambda x: ','.join(x.astype(str))
df2=(df1.groupby([pd.Grouper(key='Detection Date & Time',freq='H'),
                 df1.Detection_Location]).agg({
        'Blast Hole': f,
        'East Coordinate': f,
        'North Coordinate': f,
        'Tag Detector ID': f,
        'Detection Date & Time' : ['first','last','size']})
               .reset_index()
               .rename(columns = {'Detection Date & Time' : '', '<lambda>':''}))

df2.columns = df2.columns.map(''.join)
df2 = df2.rename(columns = {'' : 'Detection Date & Time'})

<小时/>

print (df2)
  Detection Date & Time Detection_Location  Blast Hole  \
0   2018-09-06 20:00:00               CV22  105,100,99   
1   2018-09-08 14:00:00               CV23    64,61,63   
2   2018-09-23 05:00:00               CV22     35,5,19   

              East Coordinate               North Coordinate  \
0   16764.83,16752.74,16743.1  107347.67,107360.32,107362.96   
1  16745.42,16773.48,16755.07   107390.32,107382.6,107387.68   
2  16747.75,16757.27,16770.89   107417.68,107452.4,107420.83   

                 Tag Detector ID               first                last  size  
0  385742468,385112050,385087366 2018-09-06 20:02:46 2018-09-06 20:49:21     3  
1  385656531,385760755,385262370 2018-09-08 14:18:17 2018-09-08 14:12:42     3  
2  385453358,385662254,385826979 2018-09-23 05:47:44 2018-09-23 05:52:54     3