How can I replace value in cells in dataframe?

Question

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.

energy.head(10)

Could you please copy paste the result of `print(energy.head(10)` to your question (there should be "..." in the data, otherwise we need parts of your df where there are "..." values) — Rabinzel, Nov 12 '22 at 12:27
Some general information on how to ask here on SO. [Please do not upload images of code/data/errors.](//meta.stackoverflow.com/q/285551). For now try the given answer, it might solve your question. — Rabinzel, Nov 12 '22 at 12:36

score 0 · Accepted Answer · answered Nov 12 '22 at 12:32

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’.

How can I replace value in cells in dataframe?

1 Answers1