Replace missing values (given as strings) in pandas dataframe by np.NaN

Question

I have a dataframe energy with missing values in some column. The missing values are represented by a string ... in the dataframe. I want to replace all these values by np.NaN

In [3]: import pandas as pd

In [4]: import numpy as np

In [7]: energy = pd.read_excel('test.xls', skiprows = 17, skip_footer = 38, parse_cols = range(2, 6), index_col = None, names = ['Country', 'ES'
   ...: , 'ESC', '% Renewable'])

In [8]: energy[(energy['ES'] == "...") | (energy['ESC'] == "...")]
Out[8]: 
                          Country   ES  ESC  % Renewable
3                  American Samoa  ...  ...     0.641026
86                           Guam  ...  ...     0.000000
150      Northern Mariana Islands  ...  ...     0.000000
210                        Tuvalu  ...  ...     0.000000
217  United States Virgin Islands  ...  ...     0.000000

To replace these values, I tried:

In [9]: energy[(energy['ES'] == "...")]['ES'] = np.NaN
/usr/local/bin/ipython:1: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy
  #!/usr/bin/python3

I don't understand the error and also I don't see any other way to achieve what I want to. Any ideas?

reopen because also I don't see any other way to achieve what I want to — jezrael
– jezrael, Commented Dec 21, 2016 at 16:41

jezrael · Accepted Answer · 2016-12-21 16:21:45Z

1

I think you need:

energy['ES'] = energy.loc[energy['ES'] != "...", 'ES']

Another solution:

energy['ES'] = energy['ES'].mask(energy['ES'] == "...")

Or:

energy['ES'] = energy['ES'].replace({'...': np.nan})

But the best is ayhan comment:

you can pass na_values='...' to pd.read_excel

answered Dec 21, 2016 at 16:21

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ashu007 · Accepted Answer · 2019-08-02 05:08:52Z

0

If Energy is your pandas dataframe then in your case you can also try:

for col in Energy.columns:
    Energy[col] = pd.to_numeric(Energy[col], errors = 'coerce')

Above code will convert all your missing values to nan automatically for all columns in your dataframe.

edited Aug 2, 2019 at 5:08

answered Aug 2, 2019 at 5:03

Ashu007

7951 gold badge9 silver badges14 bronze badges

Collectives™ on Stack Overflow

Replace missing values (given as strings) in pandas dataframe by np.NaN

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related