Subset a data frame according to pattern matches in a file.txt pandas

Question

I have a data frame such as

query   subject col1
A   dog ok
B   cat okl
C   cat oklp
D   frog    ok
E   cat ok
F   fox ok

and a file.txt such as:

dog
cat

and the idea is to only keep row that have a pattern present in the file.txt. Here I should get :

query   subject col1
A   dog ok
B   cat okl
C   cat oklp
E   cat ok

I tried with :

file = open('file.txt').read()

df=[]
for row in tab['subject']:
 if row in file: 
   row.append(df)

but it does not seem to be the solution, thank you for your help.

score 3 · Answer 1 · answered Mar 19 '19 at 16:30

3

You can use df.loc[] to grab specific rows where a criterion is met, and isin() to check whether something in your dataframe shows up in a given list. First make a list of the entries in your file:

vals = []  # placeholder for our file contents
with open('file.txt', 'r') as a_file:
    for line in a_file:
        vals.append(line)

Next select rows where the subject column is one of the values read from your file. Assuming your dataframe is called df:

new_df = df.loc[df.subject.isin(vals)]

Side note: you almost never want to iterate over a dataframe. There are exceptions to this, but generally speaking if you find yourself iterating on a dataframe (e.g. for row in df:), you can rewrite that piece of code to take advantage of vectorized functions in Pandas which are much more efficient.

answered Mar 19 '19 at 16:30

Engineero

12,340
5
53
75

1

i think if you remove the loc you still should be fine. :) – anky Mar 19 '19 at 16:36
1

Thanks very much is does exactly what I needed :) – chippycentra Mar 19 '19 at 17:27
@anky_91 you're right it should still work, but `loc` is the preferred method. See https://stackoverflow.com/a/48411543/2464597 and https://stackoverflow.com/a/38886211/2464597 for some reasons why. Generally I'd say it's best to get in the habit of using the preferred method; in this case `loc`. – Engineero Mar 19 '19 at 17:29
1

@Engineero cool, thanks for sharing. Not necessary here is what i meant incase you wanted to reduce operation. :) already upvoted – anky Mar 19 '19 at 17:30

score 2 · Accepted Answer · answered Mar 19 '19 at 16:34

Considering that your data frame is called df, this answer reads the file.txt also as a dataframe and merge both data frames, resulting in the desired result - solution similar to an inner join from SQL:

>> df2 = pd.read_csv('file.txt', header= None, names=['subject'])
>> pd.merge(df, df2, on='subject')

    query   subject col1
0   A       dog     ok
1   B       cat     okl
2   C       cat     oklp
3   E       cat     ok

Subset a data frame according to pattern matches in a file.txt pandas

2 Answers2