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

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.

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 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