'Join tables with different datetime formats

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?

pythonsqlpandasjoin


Solution 1:[1]

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')

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1

Related Questions