How to replace white space in column? Pandas

Question

What I tried?

//1    myDataFrame['Gender'] = myDataFrame['Gender'].replace('^\s*$', np.nan)
//2    myDataFrame['Gender'] = myDataFrame['Gender'].replace('', np.nan)
       myDataFrame.to_csv('new_Paymets_Loan.csv')

The white space is still the csv file. Link to test csv: https://docs.google.com/spreadsheets/d/1eeHGsx3s7nZaSVPCFvtl0Wwp4KJGK8hAlH97n_4DPbI/edit?usp=sharing

[![enter image description here][1]][1]

Gender
Male
Female
Female
Male
Female
Male
Male
Male
Male
Male
Male
Male
Male
Female
Male
Male
Male
Male
Male
Male
Male
Female
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Female
Male
Male
Male
Female

Male
Male
Male
Male
Female
Female
Female
Male
Male
Male
Male
Male
Female
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Female
Male
Male
Male
Male
Male
Female
Female
Male
Male
Male
Male
Female
Male
Female
Female
Male
Female
Female
Female
Male
Male
Male
Male
Male
Male
Male
Female
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Female
Male
Male
Male
Male
Male
Male
Female
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Female
Male
Male
Female
Male
Male
Male
Male
Male
Male
Female
Male
Male
Female

Male
Male
Male
Male
Female
Male
Male
Female
Male
Male
Female
Male
Male
Male
Male
Female
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Female
Male
Male
Male
Male
Female
Male
Male
Male

Male
Male
Female
Female
Male
Male
Male
Male
Male
Male
Male
Female
Male
Male
Male
Female
Male
Male
Male
Male
Male
Male
Female
Male
Male
Female
Male
Female
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Female
Male
Male
Male
Male
Male
Male
Male
Male
Male
Female
Male
Female
Male
Female
Male
Male
Male
Female
Male
Male
Male
Male
Male
Male

Female
Male
Male
Male
Male
Female
Male
Male
Female
Female
Male
Male
Female
Male
Female
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Female
Male
Male
Female
Male

Male
Male
Female
Male
Male
Male
Male
Male
Male
Male
Male
Male
Female
Male
Male
Male
Male
Male
Female
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Female
Male
Male
Male
Male
Female
Female
Male
Male
Male
Female
Male
Female
Male
Male

Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male

Male
Male
Male
Male
Male
Male
Male
Male
Male
Female
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Female
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male

Male
Female
Male
Male
Male
Female
Male
Female
Male
Male
Male
Female
Male
Male
Male
Male
Male
Male
Female
Male
Male
Male
Male
Female
Male
Male
Male
Male
Male
Female
Male
Male
Male
Male
Female
Male
Male
Male
Male
Male

Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Female
Female
Male
Male
Male
Male
Male
Male
Male
Male
Female
Female
Female
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male
Male

Male
Male
Male
Female
Male

can you share sample input data in copy-pastable format from the csv file — Anshul, May 31 '20 at 13:08
Problem is if write empty values to file it by default write no value. — jezrael, May 31 '20 at 13:12
need `na_rep='NaN'`, default is `na_rep=''` parameter in to_csv — jezrael, May 31 '20 at 13:14
Could you show the csv file **as text**? Excel is known to use fancy displaying... — Serge Ballesta, May 31 '20 at 13:16
@SergeBallesta text is shown in original question as requested. — bibscy, May 31 '20 at 13:20
@bibscy Can you show, how you are reading the csv file in first place? — Shubham Sharma, May 31 '20 at 13:46
I added na_rep='NaN' and it is finally working. Please see full code https://gist.github.com/bibscy/28b0c85b61aed478904476dfed08c535 — bibscy, May 31 '20 at 13:52

score 1 · Accepted Answer · answered May 31 '20 at 13:21

1

You want the string NaN in your text csv file. Just put it in the DataFrame:

myDataFrame['Gender'] = myDataFrame['Gender'].str.replace('^\s*$', 'NaN')

answered May 31 '20 at 13:21

Serge Ballesta

143,923
11
122
252

I did and it is not doing anything. ```myDataFrame['Gender'] = myDataFrame['Gender'].str.replace('^\s*$', 'NaN')``` //write to csv ```myDataFrame.to_csv('new_Paymets_Loan.csv')``` – bibscy May 31 '20 at 13:24

EXODIA · Answer 2 · 2020-05-31T13:45:39.440

0

You can use the below command to remove white spaces

myDataFrame['Gender'].replace('\s','',inplace=True,regex=True)

edited May 31 '20 at 13:45

answered May 31 '20 at 13:16

EXODIA

908
3
10
28

error: TypeError: expected string or bytes-like object – bibscy May 31 '20 at 13:27

How to replace white space in column? Pandas

2 Answers2