CODEWITHSUNDEEP
pythonpandascsv
ch1maera
ch1maera

Reputation: 1447

Replace value in existing column .csv pandas

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.

Upvotes: 1

Views: 13464

Answers (4)

Karn Kumar
Karn Kumar

Reputation: 8816

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

Upvotes: 2

jezrael
jezrael

Reputation: 862511

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

Upvotes: 2

Sagun Shrestha
Sagun Shrestha

Reputation: 1198

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)

Upvotes: 0

Sanchit Kumar
Sanchit Kumar

Reputation: 1695

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:

    0
0   1
1   10
2   2
3   3
4   67
5   5
6   5
7   5
8   6
9   7
10  7

Upvotes: 0

Related Questions