r/pythonhelp • u/WizardOfOdz_777 • 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
| SN | NAME |
|---|---|
| 1083 | word1 and other text |
| 1099 | word6 with more text |
| 1146 | text with word2 |
| 1171 | more with word2 |
| 1329 | word4 |
| 1446 | word5 blah blah |
| 1662 | here is word3 |
| 1777 | word3 again |
file3
| SN | NAME | FOUND |
|---|---|---|
| 1083 | word1 and other text | word1 |
| 1099 | word6 with more text | word6 |
| 1146 | text with word2 | word2 |
| 1171 | more with word2 | word2 |
| 1329 | word4 | word4 |
| 1446 | word5 blah blah | word5 |
| 1662 | here is word3 | word3 |
| 1777 | word3 again | word3 |
1
u/Goobyalus May 17 '22
You described it pretty clearly, is there something specific you have a question about?
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 stringline.
line.split(",")[2]is the element in the third colum of the row in the CSV.So if
lineis:"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
2
u/kaleidist May 17 '22
It's ambiguous because it's not clear what you want to happen if more than one word matches between the name columns.
If you never have more than one word match both of these will solutions will work. The pandas solution will also work if you have multiple words matching: it will simply concatenate with a single space separation. It would be easy to modify this so they are elements in a list as well. The non-pandas solution can also handle multiple matches, but it will add each new match as a new column (which, beside the first, "FOUND", are unnamed). Any of this could be changed, depending on how you want to handle multiple matches (if you indeed do). These assume you have the "SN", "NAME" headers in the csv file on the first row:
Using pandas:
Without pandas: