Ndtyjky

I have two dataframes in Python named GroupedCode (70000 rows and 3 columns) and ICD9 (11500 rows and 27 columns). My goal is to find every element in Code9 column of GroupedCode that is present in the ICD9CMCode column of ICD9 and every time that I find a match, append the value of the TotalDiag column of the ICD9 into a list called Freq.
I came up with a For loop to do this but it takes a good amount of time to complete. I was wondering if there is a better way to speed up the for loop or even better avoid it.

Here is my for loop:

Freq = 

for code in GroupedCode.Code9:



if (len(ICD9.TotalDiag[ICD9['ICD9CMCode'].str.match(code)]) == 0):

    Freq.append(0)

else:

    Freq.append(ICD9.TotalDiag[ICD9['ICD9CMCode'].str.match(code)].values)

Consider merging the two data frames to retain matches between each other then downcasting pandas Series to a list. Currently, you are storing numpy arrays (not single values) or 0 to a list.

merged_df = pd.merge(GroupedCode, ICD9, left_on='ICD9CMCode', right_on='Code9')



Freq = merged_df['TotalDiag'].tolist()

Even consider unique() for unique values in case of multiple inner join matches.

Freq = merged_df['TotalDiag'].unique().tolist()