-2

I was creating a program to replace all the none values in a data file ( excel sheet) using pandas. for that I first read the excel file and then used the replace method.

My code:

import pandas as pd
import numpy as np
A='Book2.xlsx'
dataa=pd.read_excel(A)
dataa[:].replace(np.nan,1,inplace=True)
print(dataa.iloc[3,1])
print(dataa)

But the changes reflected only in the read variable (dataa), not in the original file.

The original excel data file (after code execution), where no changes took place.

Please tell if I've done something going wrong, and what to do to resolve this.

Ashray Gupta
  • 9
  • 2
  • **[No Screenshots](https://meta.stackoverflow.com/questions/303812/)** of code or data. Always provide a [mre] with code, **data, errors, current output, and expected output**, as **[formatted text](https://stackoverflow.com/help/formatting)**. If relevant, plot images are okay. Please see [How to ask a good question](https://stackoverflow.com/help/how-to-ask). Provide data with [How to provide a reproducible copy of your DataFrame using `df.head(15).to_clipboard(sep=',')`](https://stackoverflow.com/questions/52413246), then **[edit] your question**, and paste the clipboard into a code block. – Trenton McKinney Jan 12 '21 at 19:00
  • `read_excel` does exactly what it says, it reads the file and dumps it into whatever variable you've chosen (`dataa`, in this case). You need to use `to_excel` to "commit" any changes back into .xlsx format. – Ed Kloczko Jan 12 '21 at 19:29
  • @EdKloczko ..Yes...Thank you for the clarification!:) – Ashray Gupta Jan 12 '21 at 19:33

1 Answers1

1

This is because you are not changing the file, you just reading it.

try this:

import pandas as pd
import numpy as np
A='Book2.xlsx'
dataa=pd.read_excel(A)
dataa[:].replace(np.nan,1,inplace=True)
dataa.to_excel("book2_modified.xlsx")

The "to_excel" method documentation: https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.to_excel.html

Roni Antonio
  • 1,334
  • 1
  • 6
  • 11