2

I am attempting to fill all float columns NaN values with 0 in my DataFrame all_files_d then put it in either an empty list or DataFrame called ts.

A sample of my dats is as such: 

 ColX              ColY
 56.9              6.4
 67.5              NaN
 NaN               8.9
 NaN               NaN

I have tried to follow this questions code as it seems to have worked for some users but there appears to be NaN values and it hasn't filled anything:

Fillna in multiple columns in place in Python Pandas

This is my code:

ts = []
all_files_d.apply(lambda x: x.fillna(0, inplace = True) if x.dtype.kind
 in 'f' else x.fillna('.', inplace = True), axis = 1)

ts.append(all_files_d)

I would hope to have the following results with all NaNs filled with 0. Thanks in advance.

 ColX              ColY
 56.9              6.4
 67.5              0
 0                 8.9
 0                 0

Any help would be appreciated.

geds133
  • 1,503
  • 5
  • 20
  • 52

3 Answers3

1

Use combine_first with pd.to_numeric():

tested with(added an extra string column):

df['Colz']=['abc',np.nan,'def',np.nan]
print(df)

   ColX ColY Colz
0  56.9  6.4  abc
1  67.5  NaN  NaN
2   NaN  8,9  def
3   NaN  NaN  NaN

df.combine_first(df.apply(lambda x: \
      pd.to_numeric(x,errors='coerce')).dropna(how='all',axis=1).fillna(0))

Output

   ColX ColY Colz
0  56.9  6.4  abc
1  67.5    0  NaN
2   0.0  8,9  def
3   0.0    0  NaN

EDIT, for fetching float dtypes and filling with NaNs:

m=df.select_dtypes('float').columns 
df.loc[:,m]=df.loc[:,m].fillna(0) 
print(df)
anky
  • 74,114
  • 11
  • 41
  • 70
  • Still have NaNs which is strange. Is this an inplace method? – geds133 Mar 26 '19 at 15:57
  • @geds133 no, you have to assign it `df=df.combine_first(.....)` and then print(df) – anky Mar 26 '19 at 15:59
  • @geds133 No problem. :) 1 more thing, by the example if you have data like `8,9` is this a typo or you have strings in float columns? – anky Mar 26 '19 at 16:08
  • @geds133 No problem. :) 1 more thing, by the example if you have data like `8,9` is this a typo or you have strings in float columns? – anky Mar 26 '19 at 16:09
  • It was a typo haved fixed in the question now :)! – geds133 Mar 26 '19 at 16:10
  • @geds133 then use `m=df.select_dtypes('float').columns` `df.loc[:,m]=df.loc[:,m].fillna(0)` `print(df)` it is really important that you paste the exact data, else people might get confused, here this solution was not required if the columns are all floats. :) – anky Mar 26 '19 at 16:14
  • 1
    Thanks. You may want to edit your answer and add the correct one in, also changing my typing error for clarity for others. – geds133 Mar 26 '19 at 16:32
0

Use df.select_dtype()

# fillna to float64 columns
ts = df.select_dtypes(include=['float64']).fillna(0) 

# merge data
df.join(ts, lsuffix="_").reindex(df.columns, axis=1)
jxc
  • 13,553
  • 4
  • 16
  • 34
0

IIUC,

ts = df.apply(lambda x: x.fillna(0) if x.dtypes == float else x.fillna('.'))


    ColX    ColY
0   56.9    6.4
1   67.5    0.0
2   0.0     8.9
3   0.0     0.0
Vaishali
  • 37,545
  • 5
  • 58
  • 86