We help IT Professionals succeed at work.

Python read 2 files and write output when data doesnt match

Newton
Newton asked
on
Hi,
I got 2 files i.e File1 and File2, as shown below

File1
A | APPLE
B | ORANGE
C | GRAPES

File2
A | 10
B | 15
D | 20

I need following 2 outputs,
Output 1
A | APPLE | 10
B | ORANGE | 15

Output 2
C | GRAPES | NA

I got the output1 using below below script.
Script1

for line1 in file1:
Data1 = line1.split("|")
Data10 = Data1[0]
Data11 = Data1[1]
 for line2 in file2:
 Data2 = line2.split("|")
 Data20 = Data2[0]
 Data21 = Data2[1]
  If Data10 == Data20:
  Print ( Data10, "|", Data11, "|", Data21)

My question is, how can I get my output2. I tired putting if data10 != data 20, i tired break and continue function but still I am not getting my output. Can advice what is best way to get my output2.
Comment
Watch Question

Analyst Assistant
Commented:
Try combining the data from each file using pandas merge and then splitting it out again to get the matched/non-matched records.
import pandas as pd

df1 =pd.read_csv('file1.txt', sep='|', header=None)
df2 = pd.read_csv('file2.txt', sep='|', header=None)

print(df1)
print(df2)

mergedf = pd.merge(df1, df2, how='left', on=0)



output1= mergedf[mergedf.iloc[:,2].notnull()]
output2= mergedf[mergedf.iloc[:,2].isnull()]

print(output1)
print(output2)

Open in new window

Author

Commented:
Thanks Norie. My problem solved..