comparing a dataframe with a .csv file using python

user_18126587 0
Viewed 79

i have a .csv files which contains many data and i want to compare that with another .csv file and based on that i want to have an output file, for example my first file contains the data in following manner :-

enter image description here

and the second .csv file which named as second file, contains the list of the diseases which we need for example -:

enter image description here

so based on that our output file should be -:

enter image description here

i have written the python code for that, but i'm not getting the desired result, Please have a look-:

import pandas as pd
import numpy as np
df=pd.read_csv("final.csv")
df1=pd.read_csv("diseases.csv")
df =df[df['extId'].isin(df.loc[df['Diseases'].isin(df1), 'extId'])]
df.to_csv("filtered.csv")
data-analysis bioinformatics dataframe python
1 Answers

One way you can go about is to import your two csv's as pandas DataFrame, and then use isin with loc to check which extId's from df1 are associated with df2 Diseases:

import pandas as pd

print(
      df1[df1.extId.isin(\
                   df1.loc[df1.Diseases.isin(df2.Disesaes)]['extId'].values)\
    ]
          )

   extId Diseases
0    123      HIV
1    123    Fever
2    321   Cancer

Setup:

df1 = pd.DataFrame({'extId':[123,123,321,456],
                    'Diseases':['HIV','Fever','Cancer','Diareah']})

df2 = pd.DataFrame({'Disesaes':['HIV','Cancer']})
user_91673820
Related