基于布尔列的多索引数据帧条件重采样

Question

我有一个df，它有一个多索引[（纬度、经度、时间）]，行数为148x244x90x24。对于每个纬度和经度，时间为每小时一次，从2014-01-01 00:00:00到2014:03:31 23:00:00

                                                FFDI         isInRange
latitude    longitude   time    
-39.20000   140.80000   2014-01-01 00:00:00     6.20000      True
                        2014-01-01 01:00:00     4.10000      True
                        2014-01-01 02:00:00     2.40000      True
                        2014-01-01 03:00:00     1.90000      True
                        2014-01-01 04:00:00     1.70000      True
                        2014-01-01 05:00:00     1.50000      True
                        2014-01-01 06:00:00     1.40000      True
                        2014-01-01 07:00:00     1.30000      True
                        2014-01-01 08:00:00     1.20000      True
                        2014-01-01 09:00:00     1.00000      True
                        2014-01-01 10:00:00     1.00000      True
                        2014-01-01 11:00:00     0.90000      True
                        2014-01-01 12:00:00     0.90000      True
                        ... ... ... ...
                        2014-03-31 21:00:00     0.30000      False
                        2014-03-31 22:00:00     0.30000      False
                        2014-03-31 23:00:00     0.50000      False
            140.83786   2014-01-01 00:00:00     3.20000      True
                        2014-01-01 01:00:00     2.90000      True
                        2014-01-01 02:00:00     2.10000      True
                        2014-01-01 03:00:00     2.90000      True
                        2014-01-01 04:00:00     1.20000      True
                        2014-01-01 05:00:00     0.90000      True
                        2014-01-01 06:00:00     1.10000      True
                        2014-01-01 07:00:00     1.60000      True
                        2014-01-01 08:00:00     1.40000      True
                        2014-01-01 09:00:00     1.50000      True
                        2014-01-01 10:00:00     1.20000      True
                        2014-01-01 11:00:00     0.80000      True
                        2014-01-01 12:00:00     0.40000      True
                        ... ... ... ...
                        2014-03-31 21:00:00     0.30000      False
                        2014-03-31 22:00:00     0.30000      False
                        2014-03-31 23:00:00     0.50000      False
            ... ... ... ...
... ... ...
-33.90000   140.80000   2014-01-01 00:00:00     6.20000      True
                        2014-01-01 01:00:00     4.10000      True
                        2014-01-01 02:00:00     2.40000      True
                        2014-01-01 03:00:00     1.90000      True
                        2014-01-01 04:00:00     1.70000      True
                        2014-01-01 05:00:00     1.50000      True
                        2014-01-01 06:00:00     1.40000      True
                        2014-01-01 07:00:00     1.30000      True
                        2014-01-01 08:00:00     1.20000      True
                        2014-01-01 09:00:00     1.00000      True
                        2014-01-01 10:00:00     1.00000      True
                        2014-01-01 11:00:00     0.90000      True
                        2014-01-01 12:00:00     0.90000      True
                        ... ... ... ...
                        2014-03-31 21:00:00     0.30000      False
                        2014-03-31 22:00:00     0.30000      False
                        2014-03-31 23:00:00     0.50000      False
            140.83786   2014-01-01 00:00:00     3.20000      True
                        2014-01-01 01:00:00     2.90000      True
                        2014-01-01 02:00:00     2.10000      True
                        2014-01-01 03:00:00     2.90000      True
                        2014-01-01 04:00:00     1.20000      True
                        2014-01-01 05:00:00     0.90000      True
                        2014-01-01 06:00:00     1.10000      True
                        2014-01-01 07:00:00     1.60000      True
                        2014-01-01 08:00:00     1.40000      True
                        2014-01-01 09:00:00     1.50000      True
                        2014-01-01 10:00:00     1.20000      True
                        2014-01-01 11:00:00     0.80000      True
                        2014-01-01 12:00:00     0.40000      True
                        ... ... ... ...
                        2014-03-31 21:00:00     0.30000      False
                        2014-03-31 22:00:00     0.30000      False
                        2014-03-31 23:00:00     0.50000      False

78001920 rows × 1 columns

我想要实现的是，在以下条件下，计算每个纬度和经度每24小时的每日最大FFDI值：

If isInRange = True  for all 24 hours/rows in the group - use FFDI from 13:00:00 of previous day to 12:00:00 of next day
If isInRange = False for all 24 hours/rows in the group - use FFDI from 14:00:00 of previous day to 13:00:00 of next day

那么我的代码是：

df_daily_max = df.groupby(['latitude', 'longitude', pd.Grouper(freq='24H',base=13,loffset='11H',label='right',level='time')])['FFDI'].max().reset_index(name='Max FFDI') if df['isInRange'] else isInRange.groupby(['latitude', 'longitude', pd.Grouper(freq='24H',base=14,loffset='10H',label='right',level='time')])['FFDI'].max().reset_index(name='Max FFDI')

但是，此行引发了一个错误：

ValueError: The truth value of a Series is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all().

Answer 1

您可以先筛选所有True行，然后筛选所有False行以进行聚合max，然后按^{}连接，按MultiIndex排序，并按^{}转换为DataFrame：

s1 = df[df['isInRange']].groupby(['latitude', 'longitude', pd.Grouper(freq='24H',base=13,loffset='11H',label='right',level='time')])['FFDI'].max()

s2 = df[~df['isInRange']].groupby(['latitude', 'longitude', pd.Grouper(freq='24H',base=14,loffset='10H',label='right',level='time')])['FFDI'].max()

df_daily_max = pd.concat([s1, s2]).sort_index().reset_index(name='Max FFDI')