1

There are 2 sheets in my xlsx file. The file is stored locally. when I am printing the rows and columns,I am getting the output. what i want is if any value in first column (patient id) of "patient info" sheet matches with any value in first column (p id) of "records" sheet, then I want to append that cell value from "records" sheet to the list c and print all list of matching values.But when I am running my code, no output is generated and even there is no error or warning shown when it is run. please find the dataset attached.patients dataset

import openpyxl
import pandas as pd

filename="week_05_homework_XLSX_openpyxl.xlsx"
wb= openpyxl.load_workbook(filename)
sheet1=wb['patient info']
sheet2=wb['records']
df1= pd.DataFrame(sheet1.values)
df2= pd.DataFrame(sheet2.values)
p=len(df1.index)
q=len(df2.index)
c =[]
for i in range(2,p):
    for j in range(2,q):
        if df1.iloc[i,1]==df2.iloc[j,1]:
            c.append((df2.iloc[j,1]))
            print(c)
pri
  • 23
  • 3
  • hi pri. from your code, you do not need openpyxl, you can read in the data via pandas directly and then compare the relevant columns. kindly share a bit of ur data, and someone may have a look at it. pls no pics, just data. have a look at this for more guidance: https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples – sammywemmy Mar 11 '20 at 20:21
  • Hello sammy, it is mandatory to use openpyxl for reading the notebook as per our assignment. please find the dataset shared. – pri Mar 17 '20 at 14:43

1 Answers1

0

If you are trying to just get the common IDs from both the data frames, it might be faster and cleaner to just use an intersection operation to find the common IDs. This answer might be helpful:

If not, you can always print them and then print the common IDs as the end.

One way to do this would be to set the IDs as index and then:

common_ids = df1.index.intersection(df2.index)
c = records.iloc[common_ids,:]
eagleShot95
  • 16
  • 1