Replace value in existing column .csv pandas

Question

Let's say I have a csv where a sample row looks like: [' ', 1, 2, 3, 4, 5] where indicates an empty cell. I want to iterate through all of the rows in the .csv and replace all of the values in the first column for each row with another value, i.e. [100, 1, 2, 3, 4, 5]. How could this be done? It's also worth noting that the columns don't have labels (they were converted from an .xlsx).

Currently, I'm trying this:

for i, row in test.iterrows():
    value = randomFunc(x, row)

    test.loc[test.index[i], 0] = value

But this adds a column at the end with the label 0.

Possible duplicate of [Replacing blank values (white space) with NaN in pandas](https://stackoverflow.com/questions/13445241/replacing-blank-values-white-space-with-nan-in-pandas) — Aqueous Carlos, Nov 13 '18 at 06:26
@ ch1marea, if any of the answers best fits into your question you can mark that as an answer. — Karn Kumar, Nov 13 '18 at 16:15

Karn Kumar · Answer 1 · 2018-11-13T06:40:13.063

You don't need a for loop while using pandas and numpy,

Just an example Below where we have b and c are empty which is been replaced by replace method:

 import pandas as pd
 import numpy as np

>>> df
   0
a  1
b
c

>>> df.replace('', 100, inplace=True)
>>> df
     0
a    1
b  100
c  100

Example to replace the empty cells in a Specific column:

In the Below example we have two columns col1 and col2, Where col1 having an empty cells at index 2 and 4 in col1.

>>> df
  col1 col2
0    1    6
1    2    7
2
3    4
4        10

Just to replace the above mentioned empty cells in col1 only:

However, when we say col1 then it implies to all the rows down to the column itself which is handy in a sense.

>>> df.col1.replace('', 100, inplace=True)
>>> df
   col1 col2
0     1    6
1     2    7
2   100
3     4
4   100   10

Another way around Just choosing the DataFrame column Specific:

>>> df['col1'] =  df.col1.replace('', 100, regex=True)
>>> df
   col1 col2
0     1    6
1     2    7
2   100
3     4
4   100   10

score 2 · Accepted Answer · answered Nov 13 '18 at 06:21

Use iloc for select first column by position with replace by regex for zero or more whitespaces:

df = pd.DataFrame({
        0:['',20,' '],
         1:[20,10,20]
})


df.iloc[:, 0] = df.iloc[:, 0].replace('^\s*$',100, regex=True)
print (df)
     0   1
0  100  20
1   20  10
2  100  20

score 0 · Answer 3 · answered Nov 13 '18 at 06:10

0

Why don't you do something like this:

df = pd.DataFrame([1, ' ', 2, 3, ' ', 5, 5, 5, 6, 7, 7])
df[df[0] == " "] = rd.randint(0,100)

The output is:

answered Nov 13 '18 at 06:10

Sanchit Kumar

1,545
1
11
19

score 0 · Answer 4 · answered Nov 13 '18 at 06:18

Here is a solution using csv module

import csv
your_value = 100    # value that you want to replace with
with open('input.csv', 'r') as infile, open('output.csv', 'w') as outfile:
    reader = csv.reader(infile)
    writer = csv.writer(outfile)
    for row in reader:
        row[0] = your_value
        writer.writerow(row)

Replace value in existing column .csv pandas

4 Answers4