Replacing blank values (white space) with NaN in pandas

by

I think df.replace() does the job, since pandas 0.13:

df = pd.DataFrame([
    [-0.532681, 'foo', 0],
    [1.490752, 'bar', 1],
    [-1.387326, 'foo', 2],
    [0.814772, 'baz', ' '],     
    [-0.222552, '   ', 4],
    [-1.176781,  'qux', '  '],         
], columns="A B C".split(), index=pd.date_range('2000-01-01','2000-01-06'))

# replace field that's entirely space (or empty) with NaN
print(df.replace(r'^\s*$', np.nan, regex=True))

Produces:

                   A    B   C
2000-01-01 -0.532681  foo   0
2000-01-02  1.490752  bar   1
2000-01-03 -1.387326  foo   2
2000-01-04  0.814772  baz NaN
2000-01-05 -0.222552  NaN   4
2000-01-06 -1.176781  qux NaN

As Temak pointed it out, use df.replace(r'^\s+$', np.nan, regex=True) in case your valid data contains white spaces.

More Related Contents:

  1. how to calculation cost time [closed]
  2. How to add a new column to an existing DataFrame?
  3. Python Pandas – Find difference between two data frames
  4. pandas: filter rows of DataFrame with operator chaining
  5. Multiple aggregations of the same column using pandas GroupBy.agg()
  6. Sorting columns in pandas dataframe based on column name [duplicate]
  7. Pandas DataFrame stored list as string: How to convert back to list
  8. How to shift a column in Pandas DataFrame
  9. What is the difference between using loc and using just square brackets to filter for columns in Pandas/Python?
  10. Find maximum value of a column and return the corresponding row values using Pandas
  11. Merge multiple column values into one column in python pandas
  12. How do I get the row count of a Pandas DataFrame?
  13. Dataframe set_index not setting
  14. Pandas dataframe fillna() only some columns in place
  15. Apply CSS class to Pandas DataFrame using to_html
  16. Python – splitting dataframe into multiple dataframes based on column values and naming them with those values [duplicate]
  17. Shift NaNs to the end of their respective rows
  18. How can I replicate rows in Pandas?
  19. OSError: Initializing from file failed on csv in Pandas
  20. Convert column to row in Python Pandas
  21. How to create a DataFrame of random integers with Pandas?
  22. Putting many python pandas dataframes to one excel worksheet
  23. Remove Unnamed columns in pandas dataframe [duplicate]
  24. Find difference between two data frames
  25. Create new column based on values from other columns / apply a function of multiple columns, row-wise in Pandas
  26. Efficient way to add rows to dataframe
  27. Find names of top-n highest-value columns in each pandas dataframe row
  28. Combine Pandas data frame column values into new column
  29. pandas extract year from datetime: df[‘year’] = df[‘date’].year is not working
  30. pandas.read_csv FileNotFoundError: File b’\xe2\x80\xaa’ despite correct path

Leave a Comment