SoFunction
Updated on 2025-03-04

Implementation of Python replacing null or specified values ​​in Excel tables

In Python, batch replacement of null values ​​or specified values ​​in Excel tables can usually be done bypandasLibrary combinationopenpyxl(for writing.xlsxto implement it. Here will show how to use itpandasTo read the Excel file, replace the empty value or specified value, and write the result back to the new Excel file.

1. Install the necessary libraries

If you haven't installed itpandasandopenpyxl, you can install them via pip:

pip install pandas openpyxl

2. Replace empty value

Suppose we want to put all null values ​​in Excel (NaN) Replace with a specific value (e.g.0or"N/A"）。

import pandas as pd

# Read Excel filedf = pd.read_excel('', sheet_name='Sheet1')

# Replace all null values ​​to 0 (or whatever value you want)(0, inplace=True)

# If you want to replace it with a string, make sure the data type is correct# ('N/A', inplace=True)

# Write the result back to a new Excel filedf.to_excel('modified_example.xlsx', sheet_name='Sheet1', index=False, engine='openpyxl')

3. Replace the specified value

If you want to replace a specified value in Excel (such as all"Old Value"Replace with"New Value"), can be usedreplacemethod.

import pandas as pd

# Read Excel filedf = pd.read_excel('', sheet_name='Sheet1')

# Replace the specified value('Old Value', 'New Value', inplace=True)

# Write the result back to a new Excel filedf.to_excel('modified_example.xlsx', sheet_name='Sheet1', index=False, engine='openpyxl')

Things to note

  • inplace=TrueParameters mean that the replacement operation will directly modify the original DataFrame.dfinstead of returning a new DataFrame. If you do not want to modify the original data, you can omit this parameter and save the replaced DataFrame to a new variable.
  • In useto_excelWhen the method,index=FalseParameters are used to prevent the index of DataFrame from being written to Excel files as well. If you need to keep the index, you can omit this parameter or set it toTrue。
  • engine='openpyxl'Parameters are written.xlsxRecommended settings for files, which allowspandasuseopenpyxlThe library functions to handle.xlsxdocument.
  • If you have memory problems when dealing with large Excel files, you can consider usingchunksizeParameters are inread_excelThe data is read in batches and processed block by block. But this may not be very direct for a replacement operation, because you need to merge the results back into a DataFrame. In this case, other solutions may need to be considered, such as usingxlsxwriterLibrary (but please note,xlsxwriterReading Excel files is not supported, only for writing).

This is the article about Python replacing empty values ​​or specified values ​​in Excel tables. For more related content on Python replacing Excel values, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!