I have cells with values ‘…’, I wanna replace them on ‘NaN’. My dataframe called energy.
import pandas as pd
import numpy as np
file_name_energy = "data/Energy Indicators.xls"
energy = pd.read_excel(file_name_energy)
energy.replace('...', np.NaN)
I tried to use replace() but it doesn’t work and dont output any error.
>Solution :
You need to reassign your dataframe or use inplace=True:
energy= energy.replace('...', np.NaN) #or energy.replace('...', np.NaN, inplace=True)
But since you’re reading and Excel, why not using na_values parameter of pandas.read_excel ?
Try this :
energy = pd.read_excel(file_name_energy, na_values= ["..."])
From the documentation:
na_values : scalar, str, list-like, or dict, default None
Additional strings to recognize as NA/NaN.
If dict passed, specific per-column NA
values. By default the following values are interpreted as NaN: ‘’,
‘#N/A’, ‘#N/A N/A’, ‘#NA’, ‘-1.#IND’, ‘-1.#QNAN’, ‘-NaN’, ‘-nan’,
‘1.#IND’, ‘1.#QNAN’, ‘’, ‘N/A’, ‘NA’, ‘NULL’, ‘NaN’, ‘n/a’, ‘nan’,
‘null’.