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 — Aqueous Carlos
– Aqueous Carlos, Commented Nov 13, 2018 at 6:26
@ ch1marea, if any of the answers best fits into your question you can mark that as an answer. — Karn Kumar
– Karn Kumar, Commented Nov 13, 2018 at 16:15

jezrael · Accepted Answer · 2018-11-13 06:21:33Z

2

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

answered Nov 13, 2018 at 6:21

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Karn Kumar · Accepted Answer · 2018-11-13 06:40:13Z

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

Sanchit Kumar · Accepted Answer · 2018-11-13 06:10:58Z

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, 2018 at 6:10

Sanchit Kumar

1,7052 gold badges14 silver badges19 bronze badges

Comments

Sagun Shrestha · Accepted Answer · 2018-11-13 06:18:44Z

0

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)

answered Nov 13, 2018 at 6:18

Sagun Shrestha

1,19812 silver badges24 bronze badges

Collectives™ on Stack Overflow

Replace value in existing column .csv pandas

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related