PYTHON: Using if statement to find where two variable columns are same and storing index-CodePudding

I'm trying to store the indices where two DataFrames have the same values. For example...

current_gages = 95x4 DataFrame with the following headers: site_no, sitename, Lat, Lon

ucol_gages = 253 x 2 Dataframe with the following headers: site_no, area

I want to find where the indices for these two dataframes have the same site number (site_no).

I am working with the following:

count1=0
gages_idx=[]
for j in range(len(current_gages)):
    if ucol_gages['site_no'][count1] == current_gages['site_no'][j]:
        gages_idx.append([count1, j])
        count1 =1
    elif ucol_gages['site_no'][count1] != current_gages['site_no'][j]:
        count1 =0

The loop stops at [4,4] with the first if statement and then to make sure there aren't any other rows in current gages, I added the elif statement to run through the remainder of the rows.

I know the next indices where ucol_gages and current_gages match is: ucol_gages['site_no'][5] and current_gages['site_no'][4].

Is there an easier way to do this? I need to make sure I run through all variations of the row indices within the DFs to determine any possible matches.

CodePudding user response：

To get the matching indices of the ucol_gages dataframe you can do the following

ucol_gages[ucol_gages['site_no'].isin(pd.merge(ucol_gages, current_gages, on=['site_no'], how='inner')['site_no'])].index.values.tolist()

To get the matching indices of the current_gages dataframe you can do the following

current_gages[current_gages['site_no'].isin(pd.merge(current_gages, ucol_gages, on=['site_no'], how='inner')['site_no'])].index.values.tolist()

Hope this helps ;)