Python replace string in pandas data frame with NaN

Question

How can i replace the '-' fields in the columns with a value of zero(0) or Nan

Possible duplicate: https://stackoverflow.com/questions/13295735/how-to-replace-nan-values-by-zeroes-in-a-column-of-a-pandas-dataframe — Stef, Sep 01 '20 at 09:47
@Stef He is trying to replace the char `-` with the value 0 or NaN, is not trying to rplace Nan with 0. — Carlo Zanocco, Sep 01 '20 at 09:48
Does this answer your question? [Pandas How to Replace ? with NaN - handling non standard missing values](https://stackoverflow.com/questions/29247712/pandas-how-to-replace-with-nan-handling-non-standard-missing-values) — Carlo Zanocco, Sep 01 '20 at 09:49

score 1 · Answer 1 · answered Sep 01 '20 at 09:52

Use the replace() method to replace - with numpy nan:

import numpy as np
import pandas as pd

lst = ['-', 'For', '-', 'is',
       'portal', 'for', '-']

VALUE_TO_REPLACE       = '-'
TARGET_REPLACED_VALUE  = np.NaN # Or either 0

df = pd.DataFrame(lst)

df = df.replace([VALUE_TO_REPLACE], TARGET_REPLACED_VALUE)

print(df)

score 1 · Answer 2 · answered Sep 01 '20 at 09:58

1

If you have a small Excel file, just open it and manually replace it in the file.

If you want to use Python then you can try this.

df['column_name'] = df['column_name'].str.replace('-', 0)

answered Sep 01 '20 at 09:58

pykam

1,223
6
16

Great! :) Can you please mark this as the correct answer then. – pykam Sep 01 '20 at 10:26

Kolawole Ojo · Answer 3 · 2020-09-01T10:18:47.147

Do a pip install openpyxl then get the workbook and find the sheet, select all the cells and replace the ones having a - with a 0. This works, I have tested it with a document here. Hope this solves your issues. You should change "replace.xlsx" to your document name.

import openpyxl

#open workbook
wb = openpyxl.load_workbook("replace.xlsx") #ensure you change this

#select worksheet
sheet = wb['Sheet1']
#choose fields
chosen_field = sheet
for rowOfCellObjects in chosen_field: # loop over every rows
    for cellObj in rowOfCellObjects:  #loop every cells in one row
        print(cellObj.value)
        if cellObj.value == "-":
            cellObj.value =  0
        print(cellObj.coordinate, cellObj.value)
wb.save('replace.xlsx') #ensure you change this

Python replace string in pandas data frame with NaN

3 Answers3