r/pythonhelp • • May 17 '22

Pull data from another table if contains ...

Hello - I have 2 csv files each containing a data . I would like python to read in the files, then if any value from file 1 is found in file2, it will add that value to file2 in a new column. When completed, it will save new file file3 as csv.

file1

NAME
word1
word2
word3
word4
word5
word6

file2

SNNAME
1083word1 and other text
1099word6 with more text
1146text with word2
1171more with word2
1329word4
1446word5 blah blah
1662here is word3
1777word3 again

file3

SNNAMEFOUND
1083word1 and other textword1
1099word6 with more textword6
1146text with word2word2
1171more with word2word2
1329word4word4
1446word5 blah blahword5
1662here is word3word3
1777word3 againword3
2 Upvotes

9 comments sorted by

View all comments

Show parent comments

1

u/WizardOfOdz_777 May 17 '22 edited May 17 '22

The code that I have written for the actual project (below) is missing what I am trying to do:
- It only looks at the line, if it is in there, it prints the line. It does not add to another column
- I must make sure everything is capitalized in the first file, because all the data is capitalized in the 2nd file
- it does not save to a file.
brand = []
with open (r'C:\python_files\ez1.csv', 'r') as f:
# skip first line
f.readline()
for line in f:
brand.append(line.strip("\n"))
with open (r'C:\python_files\ez2.csv', 'r') as f:
# skip the first line
f.readline()
for line in f:
if (line.split(",")[2]) in brand:
print (line)

1

u/Goobyalus May 17 '22

You can format your code for Reddit by indenting each line by an additional 4 spaces, and leaving blank lines before and after the code block.

I assume it looks like this?

brand = []
with open (r'C:\python_files\ez1.csv', 'r') as f:
    # skip first line
    f.readline()
    for line in f:
        brand.append(line.strip("\n"))
with open (r'C:\python_files\ez2.csv', 'r') as f:
    # skip the first line
    f.readline()
    for line in f:
        if (line.split(",")[2]) in brand:
        print (line)

In this line:

        if (line.split(",")[2]) in brand:

I'm not sure what it's supposed to do. I think we want to check whether any of the words in the "name" column are in the list of brands, right?

1

u/WizardOfOdz_777 May 17 '22

I was trying everything to get to indent with no luck. So, thank you. In the line that you reference, it should just be column 3 of the line, but I couldn't get it to work. So, I did it that way. I was using:

if line[2] in brand:

right now, my primary concern is searching if 'brand' is contained anywhere in the 2nd column. Currently, it is looking for an exact match.

1

u/Goobyalus May 17 '22

Print out some things to get an idea of what's going on.

line[2] is the second character in the string line.

line.split(",")[2] is the element in the third colum of the row in the CSV.

So if line is:

"1083,word1 and other text"

line.split(",") is:

["1083", "word1 and other text"]

line.split(",")[0] is for the first column:

"1083"

line.split(",")[1] is for the second column:

"word1 and other text"

line.split(",")[2] does not exist and results in an error.


Additionally, checking if any of the words in one element are in the list of brands is more complicated than in. injust checks if some element is inside of something else, but it does not check for the intersection. Like [1, 2, 3] in [3, 4, 5] would try to find the list [1, 2, 3] inside of [3, 4, 5]. Similarly for "one two three" in ["one", "five", "nine"] it would try to find "one two three" in the list and fail. Maybe that's what you mean by exact match?

1

u/WizardOfOdz_777 May 17 '22

Thank you for this explanation