How to update csv values given their row and column numbers are known with python

user_17700979 0
Viewed 124

I have some code that looks through the lines of a .csv file and checks if the lines contain any values from a specific column of another .csv file.

These files look like this:

Lookup file:

0:  TextExclude, text, other
1:  aa,        , x ,   y
2:  bb,        , x ,   y
3:  cc,        , x ,   y

File where I'm looking to find these values in:

0: x, longtext, exclude
1: x, helloaa,  0
2: x, testaa,   0
3: x, testcc,   0
4: x, no,       0
5: x, aabb,     0

The output of my code is supposed to change the value of the 'contains' column from 0 to 1 in every row except for row 4, yielding this expected output csv table:

0: x, longtext, exclude
1: x, helloaa,  1
2: x, testaa,   1
3: x, testcc,   1
4: x, no,       0
5: x, aabb,     1

Since my code can output the row numbers where a match is found and the column number is already defined, I was wondering what would be the best way to solve this and update the .csv file accordingly?

Here's my code:

import pandas
findlist = []
linecount=0
       
with open('lookup.csv', 'r') as f:
    column_names = ["TextExclude", "Exclusion", "Filename"]
    r = pandas.read_csv(f, names=column_names)
    findlist = r.TextExclude.to_list()

with open('datafile.csv', 'r') as f:
    # Skip the first line
    f.readline()
    for line in f: 
        linecount = linecount +1
        if any(listelement in line for listelement in findlist):
            print(line)
pandas csv python
1 Answers

You should parse datafile.csv using pandas, not just read it as a flat text file. That way you can isolate your search to the correct column and also update the third column more easily.

import pandas
findlist = []
linecount=0

with open('lookup.csv', 'r') as f:
    column_names = ["TextExclude", "Exclusion", "Filename"]
    r = pandas.read_csv(f, names=column_names)
    findlist = r.TextExclude.to_list()

with open('datafile.csv', 'r') as f:
    df = pandas.read_csv(f)
    for ri, row in df.iterrows():
        if any(x in row[1] for x in findlist):
            row[2] = "1"

# print result to stdout
print(df)

# or write result to a file:
df.to_csv("output.csv")

If you are wondering what version of Pandas and Python you are using, you can run these two commands:

Python version:
python --version

Pandas version:
python -c "import pandas;print(pandas.__version__)"

Here is a second way of searching that breaks the steps out a little more which might aid in debugging:

import pandas
findlist = []
linecount=0

with open('lookup.csv', 'r') as f:
    column_names = ["TextExclude", "Exclusion", "Filename"]
    r = pandas.read_csv(f, names=column_names)
    findlist = r.TextExclude.to_list()

def search(search_in):
    for to_find in findlist:
        if to_find in search_in:
            return True
    return False

with open('datafile.csv', 'r') as f:
    df = pandas.read_csv(f)
    for ri, row in df.iterrows():
        if search(row[1]):
            row[2] = "1"

# print result to stdout
print(df)

# or write result to a file:
df.to_csv("output.csv")
user_116111950
Related