Python replace NaN values of a field holding numeric values with empty and not single quotes which will be treated later as strings

Question

I am uploading some data frames into snowflake cloud. I had to use the following to transform all field values into string:

data = data.applymap(str)

The only reason I am doing that is that without it, I will get the following error:

TypeError: not all arguments converted during string formatting

The reason for that is there is fields containing numeric values, but not all rows have it, some of them have 'NA' instead. And for data integrity, we cannot replace them with 0s as in our domain, 0 might seems something, and in our work blank is different to the value of 0.

At the beginning, I tried to relace NAa with single quotes '', but then, all fields having numbers were transformed into float. So if a value is 123, it will be 123.0.

How can I replace NA values in a numeric field into completly blank and not '' so the field can still be considered as type INT.

In the image below, I don't want the empty cell to be treated as string, as the other fields will be transformed with the applymap() into floats if they are int:

[Please do not post sample data or code as images](https://meta.stackoverflow.com/q/285551). — Bill Huang, Nov 26 '20 at 09:13

Bill Huang · Answer 1 · 2020-11-26T10:41:18.303

1

Detect nan's using np.isnan() and put only non-nan numbers into str().

If you don't want float-typed intgers, just change the mapping fromstr() to str(int()).

Data

Note that column B contains nan which is actually a float number, so its dtype is automatically float.

df = pd.DataFrame({"A": [1 ,2], "B":[3, np.nan]})

print(df)
   A    B
0  1  3.0
1  2  NaN

Code

import numpy as np
df.applymap(lambda el: "" if np.isnan(el) else str(el))

Out[12]: 
   A    B
0  1  3.0
1  2

edited Nov 26 '20 at 10:41

answered Nov 26 '20 at 09:15

Bill Huang

4,491
2
13
31

what if I had a column with floats ? – alim1990 Nov 26 '20 at 10:00
This code deals with a float column. What is the problem? – Bill Huang Nov 26 '20 at 10:03
i want to keep floats as floats and ints as ints – alim1990 Nov 26 '20 at 10:06
That is ambiguous. Should 1.0 be converted to 1 or kept as 1.0? Please update such information in your post. And provide representative input and expected output data containing these corner cases for clarity. – Bill Huang Nov 26 '20 at 10:09
Bill, 1.0 should stay 1.0 and 1 still 1. The main problem is when the apply map is detecting NaN value or blank , it is transforming the whole column into string. – alim1990 Nov 26 '20 at 10:31
Then just remove `int()`. I have updated the answer. Is this what you meant? – Bill Huang Nov 26 '20 at 10:35

Python replace NaN values of a field holding numeric values with empty and not single quotes which will be treated later as strings

1 Answers1

Data

Code