[Python] Converting pandas dataframe from float to int, handling NaN values

Context


I have a pandas DataFrame I receive from a transform stage in an ETL process, and I am trying to load it to a postgres database. This has been fine for most of the tables, but the final assignment table is causing me problems.

The player_match_event_df DataFrame looks like this:

match_event_id player_id related_player_id
0 150573493 37623454 NaN
1 150573495 787556 NaN
2 150573550 37736097 NaN
3 150573587 978873 37677332.0
4 150573592 323669 37757705.0
5 150573591 191273 788849.0
6 150573605 73555 787556.0
7 150573625 191273 32113.0
8 150573634 29763093 35095.0
9 150573635 320908 320430.0
10 150573633 320715 37493565.0
11 150573672 37677530 12844068.0
12 150573646 820 37425064.0
13 150573648 12447486 73555.0
14 150573681 37677330 29723379.0
15 150573689 220042 NaN


player_match_event_df.info():

Data columns (total 3 columns):
# Column Non-Null Count Dtype
--- ------ -------------- -----
0 match_event_id 16 non-null int64
1 player_id 16 non-null int64
2 related_player_id 12 non-null float64
dtypes: float64(1), int64(2)


The schema.sql defines the table player_match_event having the following columns: player_match_event_id, match_event_id, player_id, related_player_id. Snippet:

CREATE TABLE player_match_event (
player_match_event_id INT GENERATED ALWAYS AS IDENTITY,
player_id INT NOT NULL,
related_player_id BIGINT,
match_event_id INT NOT NULL,
PRIMARY KEY (player_match_event_id),
FOREIGN KEY (player_id) REFERENCES player(player_id),
FOREIGN KEY (related_player_id) REFERENCES player(player_id),
FOREIGN KEY (match_event_id) REFERENCES match_event(match_event_id)
);

Problem


The related_player_id column is being problematic, and whenever I try to insert into the table I get the following error: Error inserting data: bigint out of range. As you can see from the player_match_event_df, none of the numerical values are out of range. I think the issue must be with the fact it is of type float and contains NaN values.

Expected result


When loading to player_match_event, I insert the relevant player_id and match_event_id always, and related_player_id is happy with a null or empty value when there wasn't a relevant_player_id associated with this event.

Fixes tried


So I've tried this:

player_match_event_df["related_player_id"] = player_match_event_df["related_player_id"].apply(
lambda x: int(x) if pd.notna(x) else None
)


In the hopes to force all values to be an integer, and na values to be None. This doesn't work though as I think None can't exist in an int dtype column, so nothing actually changes in the dataframe when I run that.

I found another stackoverflow thread which suggested using

player_match_event_df["related_player_id"] = player_match_event_df["related_player_id"].astype(
float).astype('Int64')


Running this does change how the DataFrame looks:

match_event_id player_id related_player_id
0 150573493 37623454 <NA>
1 150573495 787556 <NA>
2 150573550 37736097 <NA>
3 150573587 978873 37677332
4 150573592 323669 37757705
5 150573591 191273 788849
6 150573605 73555 787556
7 150573625 191273 32113
8 150573634 29763093 35095
9 150573635 320908 320430
10 150573633 320715 37493565
11 150573672 37677530 12844068
12 150573646 820 37425064
13 150573648 12447486 73555
14 150573681 37677330 29723379
15 150573689 220042 <NA>


But when I try to load this to the database, using execute_values I get columns and values this way:

columns = ", ".join(df.columns)
values = df.to_records(index=False).tolist()


And when I print values, this is the output:

[(150573493, 37623454, nan), (150573495, 787556, nan), (150573550, 37736097, nan), (150573587, 978873, 37677332.0), (150573592, 323669, 37757705.0), (150573591, 191273, 788849.0), (150573605, 73555, 787556.0), (150573625, 191273, 32113.0), (150573634, 29763093, 35095.0), (150573635, 320908, 320430.0), (150573633, 320715, 37493565.0), (150573672, 37677530, 12844068.0), (150573646, 820, 37425064.0), (150573648, 12447486, 73555.0), (150573681, 37677330, 29723379.0), (150573689, 220042, nan)]


Which still shows floats and nan, and then still results in the same error message. I'd really appreciate some guidance on this, thank you!

Continue reading...