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 |
2
Upvotes
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: