Pandas: How to filter out timestamps that are listed

user_13168303 0
Viewed 51

I have a dataframe that looks like this: It captures the specific time (at minute level) when a particular event happened.

+---------------------+
| time                |
+---------------------+
| 2021-01-01 08:01:00 |
+---------------------+
| 2021-01-01 09:32:00 |
+---------------------+
| 2021-01-02 12:01:00 |
+---------------------+
| 2021-01-02 16:30:00 |
+---------------------+
| ...                 |
+---------------------+
| ...                 |
+---------------------+
| 2021-01-31 06:01:00 |
+---------------------+

I would like to create a new separate dataframe that contains minute-level timestamps from 2021-01-01 00:00:00 to 2021-01-31 11:59:00 where the time is NOT listed in the dataframe above.

It would look sth like this:

+---------------------+
| time                |
+---------------------+
| 2021-01-01 00:00:00 |
+---------------------+
| 2021-01-01 00:01:00 |
+---------------------+
| ...                 |
+---------------------+
| 2021-01-01 08:00:00 |
+---------------------+
| 2021-01-01 08:02:00 |
+---------------------+
| ...                 |
+---------------------+
| 2021-01-01 09:31:00 |
+---------------------+
| 2021-01-01 09:33:00 |
+---------------------+
| ...                 |
+---------------------+
| 2021-01-02 12:00:00 |
+---------------------+
| 2021-01-02 12:02:00 |
+---------------------+
| ...                 |
+---------------------+
| 2021-01-02 16:29:00 |
+---------------------+
| 2021-01-02 16:31:00 |
+---------------------+
| ...                 |
+---------------------+
| 2021-01-02 06:00:00 |
+---------------------+
| 2021-01-02 06:02:00 |
+---------------------+

What is an elegant way to do this?

Much appreciation for your help!

pandas date datetime python
2 Answers

Generate random input data:

>>> df
                   time
0   2021-01-01 01:08:00
1   2021-01-01 01:23:00
2   2021-01-01 01:35:00
3   2021-01-01 02:13:00
4   2021-01-01 03:47:00
..                  ...
995 2021-01-31 08:24:00
996 2021-01-31 09:30:00
997 2021-01-31 10:24:00
998 2021-01-31 10:31:00
999 2021-01-31 10:34:00

[1000 rows x 1 columns]  # <- 1000

Create a DatetimeIndex with freq=T (1 minute)

start_date = "2021-01-01 00:00:00"
end_date = "2021-01-31 11:59:00"
dti = pd.date_range(start_date, end_date, freq="T")

>>> dti
DatetimeIndex(['2021-01-01 00:00:00', '2021-01-01 00:01:00',
               '2021-01-01 00:02:00', '2021-01-01 00:03:00',
               '2021-01-01 00:04:00', '2021-01-01 00:05:00',
               '2021-01-01 00:06:00', '2021-01-01 00:07:00',
               '2021-01-01 00:08:00', '2021-01-01 00:09:00',
               ...
               '2021-01-31 11:50:00', '2021-01-31 11:51:00',
               '2021-01-31 11:52:00', '2021-01-31 11:53:00',
               '2021-01-31 11:54:00', '2021-01-31 11:55:00',
               '2021-01-31 11:56:00', '2021-01-31 11:57:00',
               '2021-01-31 11:58:00', '2021-01-31 11:59:00'],
              dtype='datetime64[ns]', length=43920, freq='T')  # <- 43920

Compute the difference between two sets of DatetimeIndex:

>>> dti.difference(df["time"])
DatetimeIndex(['2021-01-01 00:00:00', '2021-01-01 00:01:00',
               '2021-01-01 00:02:00', '2021-01-01 00:03:00',
               '2021-01-01 00:04:00', '2021-01-01 00:05:00',
               '2021-01-01 00:06:00', '2021-01-01 00:07:00',
               '2021-01-01 00:08:00', '2021-01-01 00:09:00',
               ...
               '2021-01-31 11:50:00', '2021-01-31 11:51:00',
               '2021-01-31 11:52:00', '2021-01-31 11:53:00',
               '2021-01-31 11:54:00', '2021-01-31 11:55:00',
               '2021-01-31 11:56:00', '2021-01-31 11:57:00',
               '2021-01-31 11:58:00', '2021-01-31 11:59:00'],
              dtype='datetime64[ns]', length=42920, freq=None)  # <- 43920 - 1000
user_152399510

You can try this:

blacklist = df_yours['time'].unique().tolist()
df = pd.DataFrame({'mins':pd.date_range(start='1/1/2021', end='1/1/2022', freq='1min')}).iloc[:-1]
df = df[~df['mins'].isin(blacklist)]
user_119717850
Related