在熊猫中检测带有NaN值的所有行的连续时间戳

Question

我想在dataframe中检测连续一组行的开始和结束(Datetime)，所有的值都是NaN。

用NaN值存储每组日期时间的开始和结束的元组中的结果的最佳方法是什么？

例如，使用dataframe，元组应该如下所示：

missing_datetimes = [('2018-10-10 22:00:00', '2018-10-11 00:00:00 '),
('2018-10-11 02:00:00','2018-10-11 02:00:00'), ('2018-10-11 04:00:00', '2018-10-11 04:00:00')

dataframe示例：

-------------+---------------------+------------+------------+
| geo_id     | Datetime            |  Variable1 |  Variable2 |    
+------------+---------------------+------------+------------+
| 1          | 2018-10-10 18:00:00 |     20     |     10     |
| 2          | 2018-10-10 18:00:00 |     22     |     10     |
| 1          | 2018-10-10 19:00:00 |     20     |     nan    |
| 2          | 2018-10-10 19:00:00 |     21     |     nan    |
| 1          | 2018-10-10 20:00:00 |     30     |     nan    |
| 2          | 2018-10-10 20:00:00 |     30     |     nan    |
| 1          | 2018-10-10 21:00:00 |     nan    |     5      |
| 2          | 2018-10-10 21:00:00 |     nan    |     5      |
| 1          | 2018-10-10 22:00:00 |     nan    |     nan    |
| 1          | 2018-10-10 23:00:00 |     nan    |     nan    |
| 1          | 2018-10-11 00:00:00 |     nan    |     nan    |
| 1          | 2018-10-11 01:00:00 |     5      |     2      |
| 1          | 2018-10-11 02:00:00 |     nan    |     nan    |
| 1          | 2018-10-11 03:00:00 |     2      |     1      |
| 1          | 2018-10-11 04:00:00 |     nan    |     nan    |
+------------+---------------------+------------+------------+

更新：，如果某些日期时间被重复怎么办？

Answer 1

您可能需要使用带有条件的groupby

s=df.set_index('Datetime').isnull().all(axis=1)

df.loc[s,'Datetime'].groupby((~s).cumsum()[s]).agg(['first','last']).apply(tuple,1).tolist()
# find the all nan value and if they are consecutive we pull them into one group

Out[89]: 
[('2018-10-1022:00:00', '2018-10-1100:00:00'),
 ('2018-10-1102:00:00', '2018-10-1102:00:00'),
 ('2018-10-1104:00:00', '2018-10-1104:00:00')]