how to replace a lengthy white space to Nan in a column under Object format? in python

user_15562026 0
Viewed 163

I have a dataframe with two columns, one of the columns contains of many blank cells. I tried all the method that I could think of to drop those rows,but none worked.

for example:

FGS_data[FGS_data['Ins ISIN code']==''] = np.nan
FGS_data[FGS_data['Ins ISIN code']!='']
nan_value = float("NaN")
# Convert NaN values to empty string
FGS_data.replace("", nan_value, inplace=True)
FGS_data.dropna(subset = ["Ins ISIN code"], inplace=True)

    Ins ISIN code   Quantity
0                   21836.59
31              -56231449.00
118              -1141045.00
51  JP3970300004    30500.00
143 JP3970300004    37000.00
176 JP3982800009    11500.00

FGS_data.dtypes
Ins ISIN code     object
Quantity         float64
dtype: object

then I tried this,

In:  FGS_data.iat[2,0]
Out: '                    '

In:  len(FGS_data.iat[2,0])
Out: 20

how can I drop the blank rows? if I use

FGS_data.replace("                    ", nan_value, inplace=True)

it workes,but I am not if all the cells have the same len of whitespaces

pandas data-cleaning python replace
2 Answers

Series.str.contains

FGS_data[~FGS_data['Ins ISIN code'].str.contains(r'^\s*$')]

     Ins ISIN code  Quantity
51   JP3970300004   30500.0
143  JP3970300004   37000.0
176  JP3982800009   11500.0

Regex details:

  • ^ : Assert position at start of line
  • \s* : Matches a whitespace character between zero or unlimited times
  • $ : Asserts position at the end of line
user_128331660

$ FGS_data['Ins ISIN code'].str.strip()

$ FGS_data.replace("", nan_value, inplace=True)

user_157515640
Related