Problem

I get a dataframe with a datetime information, need to extract date and time, store both in a database, get the id's back and replace the date/time values with the database id's.

This should be a very common problem, but I couldn't find a solution.

Preconditions

I receive a dataframe from an API like this (with data between a few 100 to >100000 rows):

data = {
        'datetime' : [
            '2022-02-23 10:00:00',
            '2022-02-23 10:05:00',
            '2022-02-23 10:10:00',
            '2022-02-23 10:20:30',
            '2022-02-23 10:42:00'],
        'temp' : [
            16.539,
            16.98,
            16.82001,
            17.03,
            17.85]
        }
df = pd.DataFrame(data)

The data types of the columns are:

print(df.dtypes)
datetime     object
temp        float64

The data has to be stored in a SQLite database. There is a table for time, date and measured data like this:

CREATE TABLE Tempvalues (
            id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE,
            date_id INTEGER NOT NULL,
            time_id INTEGER NOT NULL,
            tval REAL
            );

CREATE TABLE Time (
            id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE,
            time TEXT NOT NULL UNIQUE
            );

CREATE TABLE Date (
            id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE,
            date TEXT NOT NULL UNIQUE
            );

This is the way I extract date and time into seperate columns in the dataframe:

df['date'] = pd.to_datetime(df['datetime']).dt.date
df['time'] = pd.to_datetime(df['datetime']).dt.time

Result:

              datetime      temp        date      time
0  2022-02-23 10:00:00  16.53900  2022-02-23  10:00:00
1  2022-02-23 10:05:00  16.98000  2022-02-23  10:05:00
2  2022-02-23 10:10:00  16.82001  2022-02-23  10:10:00
3  2022-02-23 10:20:30  17.03000  2022-02-23  10:20:30
4  2022-02-23 10:42:00  17.85000  2022-02-23  10:42:00

Data types:

print(df.dtypes)
datetime     object
temp        float64
date         object
time         object
dtype: object      

Store the 'date' and 'time' column in the database:

df[['date', 'time']].to_sql('Tempdf', conn, index=False)

Update the Time and Data tables and remove Tempdf table:

with conn:
    cur.execute("UPDATE Tempdf SET time=STRFTIME('%H:%M:%S', time);")
    cur.execute("INSERT OR IGNORE INTO Date (date) SELECT date FROM Tempdf;")
    cur.execute("INSERT OR IGNORE INTO Time (time) SELECT time FROM Tempdf;")
    cur.execute("DROP TABLE IF EXISTS Tempdf;")

Replace 'date', 'time' with id / what I tried

Up to this point, everything works fine. Now I want to replace the date and time values in the dataframe with the Date.id and Time.id values from the database. Since I shouldn't iterate over a pandas dataframe, I thought using the pandas replace or map methods for this task would be a good idea. But it fails because the data types don't match:

date_date_id = dict(cur.execute("SELECT date, id FROM Date").fetchall())
time_time_id = dict(cur.execute("SELECT time, id FROM Time").fetchall())

I get back a string and an integer:

print(date_date_id)
{'2022-02-23': 1}

Using pandas map method:

df['date'] = df['date'].map(date_date_id)
df['time'] = df['time'].map(time_time_id)

Result:

print(df)
              datetime      temp  date  time
0  2022-02-23 10:00:00  16.53900   NaN   NaN
1  2022-02-23 10:05:00  16.98000   NaN   NaN
2  2022-02-23 10:10:00  16.82001   NaN   NaN
3  2022-02-23 10:20:30  17.03000   NaN   NaN
4  2022-02-23 10:42:00  17.85000   NaN   NaN

Data types:

datetime     object
temp        float64
date        float64
time        float64
dtype: object    

When I try this with floats, it works like expected:

test_temp = {16.539 : 42, 16.98 : 42}
df['temp'] = df['temp'].map(test_temp)

Result:

              datetime  temp        date      time
0  2022-02-23 10:00:00  42.0  2022-02-23  10:00:00
1  2022-02-23 10:05:00  42.0  2022-02-23  10:05:00
2  2022-02-23 10:10:00   NaN  2022-02-23  10:10:00
3  2022-02-23 10:20:30   NaN  2022-02-23  10:20:30
4  2022-02-23 10:42:00   NaN  2022-02-23  10:42:00

So I assume it doesn't replace the values because the data type of 'date' and 'time' is object instead of string.

The result is the same, when the replacement value is pointing to an empty column:

df['date_id'] = df['date'].map(date_date_id)
df['time_id'] = df['time'].map(time_time_id)

Result:

              datetime      temp        date      time  date_id  time_id
0  2022-02-23 10:00:00  16.53900  2022-02-23  10:00:00      NaN      NaN
1  2022-02-23 10:05:00  16.98000  2022-02-23  10:05:00      NaN      NaN
2  2022-02-23 10:10:00  16.82001  2022-02-23  10:10:00      NaN      NaN
3  2022-02-23 10:20:30  17.03000  2022-02-23  10:20:30      NaN      NaN
4  2022-02-23 10:42:00  17.85000  2022-02-23  10:42:00      NaN      NaN

It feels like this is a task that should be completely done in sql, but I failed to do so. This inserts the same id in every row, probably the first one that matches:

WITH tid (t_id, t_time, df_time) AS
    (SELECT Time.id, Time.time, Tempdf.time
     FROM Tempdf
     JOIN Time
        ON Time.time=Tempdf.time)

UPDATE Tempdf
SET time_id = (SELECT t_id FROM tid WHERE t_time = df_time);

Questions

• How do I get the Date.id and Time.id values into the dataframe, where it matches the values of the 'date'/'time' columns? -> partially answered

• Do I need to change the data type somehow from object to string? If yes, how? -> answered

• Is this a proper way to replace the values in the dataframe with the database id's or am I on the wrong path?

• Is there a native SQLite solution?

Sorry for beeing a bit lost on this. I'd be very grateful for any help.