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

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:

import pandas as pd

df1 = pd.read_csv('ez1.csv', header=0)
df2 = pd.read_csv('ez2.csv', header=0)
df2['FOUND'] = df2['NAME'].apply(lambda x: " ".join([word for word in x.split() if word in list(df1['NAME'])]))
df2.to_csv("ez3.csv", header=True, index=False)

Without pandas:

import csv


def csv_to_list(filename, header=False):
    with open(filename, newline="") as f:
        reader = csv.reader(f)
        if header:
            return list(reader)
        return list(reader)[1:]


ez1 = csv_to_list('ez1.csv')
ez2 = csv_to_list('ez2.csv')
for row in ez2:
    for word in row[1].split():
        if [word] in ez1:
            row.append(word)

with open('ez3.csv', 'w', newline="") as f:
    wr = csv.writer(f)
    wr.writerow(['SN', 'NAME', 'FOUND'])
    wr.writerows(ez2)

1

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

This is starting to make sense with the pandas. I just tried the other and it works perfectly.

However, when I run this with Pandas, I am getting an error:

Input In [46], in <lambda>(x)

3 df1 = pd.read_csv(r'C:\python_files\ez1.csv', header=0)

4 df2 = pd.read_csv(r'C:\python_files\ez2.csv', header=0)

----> 5 df2['FOUND'] = df2['NAME'].apply(lambda x: " ".join([word for word in x.split() if word in list(df1['NAME'])]))

6 df2.to_csv("ez3.csv", header=True, index=False)

AttributeError: 'float' object has no attribute 'split'

1

u/kaleidist May 17 '22 edited May 17 '22

So your 'NAME' column is being regarded as float type. Maybe you loaded the SN column data as the NAME column? Pandas will auto-guess the datatype for a column unless otherwise indicated. If you do have the right data in NAME, then try inserting before that erroneous line the following line: df2['NAME'] = df2['NAME'].astype(str)

If that still does not work, maybe give me the first few lines of your CSV files (including headers) so I can see what needs to be done.