0

I have two tables with different columns representing time.

Table 1 contains coordinates for every second from one particular day (March 20, 2022):

time Coordinates
2022-03-20T09:16:23.000 x/y
2022-03-20T09:16:25.000 x/y
2022-03-20T09:16:27.000 x/y

Table 2 contains number of registered events by time (also from March 20, but we have just time without date and milliseconds):

time Number of events
09:16:23 23
09:16:27 53

I want to join this tables to extract column with coordinates and additional columns for every row from the Table 2. Desired result:

time Coordinates Number of events
09:16:23 x/y 23
09:16:27 x/y 53

How I can do it by using Python or SQL?

Alem
  • 1
  • 1

1 Answers1

0

With pandas you can ensure both time are in a common format (datetime, string) and merge:

df1['time'] = pd.to_datetime(df1['time']).dt.time

# only if df2['time'] are strings
#df2['time'] = pd.to_datetime(df2['time']).dt.time

df1.merge(df2, on='time')

output:

       time Coordinates  Number of events
0  09:16:23         x/y                23
1  09:16:27         x/y                53

if you rather prefer to use strings, you can convert the time in df1 with: pd.to_datetime(df1['time']).dt.strftime('%H:%M:%S')

mozway
  • 194,879
  • 13
  • 39
  • 75
  • Thank you! But for both of these commands I have the same error: TypeError: is not convertible to datetime. The type of "time" column from df1 is - datetime64[ns, SimpleTZ("Z")]. Type from df2 - object. – Alem Apr 11 '22 at 13:53
  • If you already have the correct type in df2, don't convert ;) – mozway Apr 11 '22 at 13:54