Replacing NaN with null python pandas

user_13343264 0
Viewed 7064

I am trying to write from a pandas dataframe to AWS redshift: 

df_tmp_rpt = pd.read_csv('path')
df_tmp_rpt = df_tmp_rpt[df_tmp_rpt['COL'] == 'VALUE']
df_tmp_rpt = df_tmp_rpt.replace(np.nan, null, regex=True)
records = df_tmp_rpt.to_records(index=False)
for record in records:
     script_insert = ScriptReader.get_script(SCRIPT_PATH).format(record)
     RedshiftDataManager.run_update(script_insert, DB_CONNECTION)

Redshift expects the format ('value1','value2',null) for inserting data. That is why i try to replace all NaN with null in the dataframe. How would I achieve such thing? (I need a null value not the string 'null')

Thanks for help in advance

amazon-redshift pandas python
2 Answers

This is what worked for me.

df_tmp_rpt = df_tmp_rpt.where(df_tmp_rpt.notna(), None)

This will replace all the NaN values in your Dataframe to None. None is loaded as NULL in the Database. This works in MS SQL.

user_154101680
user_81406590
Related