write data to database with Nan in a integer column in database

Question

As numpy.nan is a special float type in python, I have a pandas.Dataframe, some columns are consists of integer and Nan. As we know these columns will be float datatype. I want to copy pandas.Dataframe.to_csv , a CSV file,to database. But as those columns in CSV are float datatype (even I set 'object' type for them in pandas but they are converted to float when I do .to_csv), but the table schema of those columns in DB are the integer type, so they will be rejected to load in. this is the problem I got. what would be a good way to fix this? It's not good to change the DB schema.

for example I have a data = pandas.Dataframe() as below. ID: float Value: float

   ID       Value
0  1001.0    500.0 
1  1002.0    NaN
2  NaN       600.0
3  1003.0    800.0

How could I create a csv to be like this:

ID|Value
1001|500 
1002|
|600
1003|800

Format them as strings with zero decimals '{:0.0f}'.format(0.)? — piRSquared
– piRSquared, Commented Jun 2, 2017 at 17:37
Not without more data to play with. Give me some sample data and I'll show you what I'm talking about. You should read MCVE and HowToAsk — piRSquared
– piRSquared, Commented Jun 2, 2017 at 18:00
Sorry.I added a sample of data in the description. let me know if it works. Thanks! — yabchexu
– yabchexu, Commented Jun 2, 2017 at 18:13

piRSquared · Accepted Answer · 2017-06-02 18:17:30Z

1

df.to_csv('yourfile.csv', sep='|', index=None, float_format='%0.0f')

ID|Value
1001|500
1002|
|600
1003|800

answered Jun 2, 2017 at 18:17

piRSquared

296k68 gold badges509 silver badges654 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

write data to database with Nan in a integer column in database

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related