mardi 30 avril 2019

How to conditionally compare a list and a pandas dataframe

I'm trying to compare a list of datetimes for the last 12 hours (from timenow) :

print(times)

[datetime.datetime(2019, 4, 30, 11, 0), datetime.datetime(2019, 4, 30, 10, 0), datetime.datetime(2019, 4, 30, 9, 0), datetime.datetime(2019, 4, 30, 8, 0), datetime.datetime(2019, 4, 30, 7, 0), datetime.datetime(2019, 4, 30, 6, 0), datetime.datetime(2019, 4, 30, 5, 0), datetime.datetime(2019, 4, 30, 4, 0), datetime.datetime(2019, 4, 30, 3, 0), datetime.datetime(2019, 4, 30, 2, 0), datetime.datetime(2019, 4, 30, 1, 0), datetime.datetime(2019, 4, 30, 0, 0)]

against a number of slices of pandas main dataframe (filt):

print(filt)

             timestamp  switchID    deviceID  count
0  2019-04-29 10:00:00         1  GTEC122277      3
(...)
16 2019-04-30 09:00:00         1  GTEC122277      1
17 2019-04-30 10:00:00         1  GTEC122277      1
18 2019-04-30 10:00:00         1  GTEC122585      1
19 2019-04-30 10:00:00         2  GTEC122585      1

Typical Slice:

print(d1_sw1)

             timestamp  switchID    deviceID  count
16 2019-04-30 09:00:00         1  GTEC122277      1
17 2019-04-30 10:00:00         1  GTEC122277      1

I want to check the array times against the first column of my SLICED pandas df d1_sw1, if they match, then store in a new df in the format:

             timestamp  count
0  2019-04-30 12:00:00      0
1  2019-04-30 11:00:00      0
2  2019-04-30 10:00:00      1
3  2019-04-30 09:00:00      1
4  2019-04-30 08:00:00      0
5  2019-04-30 07:00:00      0
6  2019-04-30 06:00:00      0
7  2019-04-30 05:00:00      0
8  2019-04-30 04:00:00      0
9  2019-04-30 03:00:00      0
10 2019-04-30 02:00:00      0
11 2019-04-30 01:00:00      0
12 2019-04-30 00:00:00      0

I am really struggling to work out how to achieve this, any help is valued!

Aucun commentaire:

Enregistrer un commentaire