Python3.x 逐行比较2个Pandas数据帧，并对每一行执行计算

EDITED: Now includes count of REFIXes per trial as requested in comments个

你的问题看起来很复杂，有点难以理解，但我想这可能是你想要的？

我不认为使用嵌套的if语句迭代两个df语句是最有效的方法，但我现在想不出更好的 Select :)

第1部分:加载您提供的数据:(您显然可以忽略这一点，但为了便于重现/测试，将其放入:

import pandas as pd

# Define the data for DF1
data_df1 = {
    'IP_INDEX': [1, 1, 1, 1, 1],
    'IP_LABEL': ['1st 3 Seconds', '1st 3 Seconds', '1st 3 Seconds', '1st 3 Seconds', '1st 3 Seconds'],
    'PARTICIPANT': ['a', 'a', 'a', 'b', 'b'],
    'TRIAL': [1, 1, 2, 1, 2],
    'CURRENT_FIX_INDEX': [1, 2, 1, 1, 1],
    'PRE_FIX_X': [550, 600, 250, 400, 600],
    'PRE_FIX_Y': [150, 300, 600, 400, 400]
}

# Create DF1
df1 = pd.DataFrame(data_df1)

data_df2 = {
    'IP_INDEX': [2, 2, 2, 2, 2, 2, 2],
    'IP_LABEL': ['2nd 3 Seconds', '2nd 3 Seconds', '2nd 3 Seconds', '2nd 3 Seconds', '2nd 3 Seconds', '2nd 3 Seconds', '2nd 3 Seconds'],
    'PARTICIPANT': ['a', 'a', 'a', 'b', 'b', 'b', 'b'],
    'TRIAL': [1, 1, 2, 1, 1, 2, 2],
    'CURRENT_FIX_INDEX': [1, 2, 1, 1, 2, 1, 2],
    'POST_FIX_X': [500, 650, 300, 250, 450, 550, 350],
    'POST_FIX_Y': [200, 350, 650, 700, 150, 350, 550]
}

# Create DF2
df2 = pd.DataFrame(data_df2)

第2部分:进行计算:

import math

#Create function to calculate distance between coordinates
def calculate_distance(df1_row,df2_row):
    p = (df1_row['PRE_FIX_X'], df1_row['PRE_FIX_Y'])
    q = (df2_row['POST_FIX_X'], df2_row['POST_FIX_Y'])
    return math.dist(p, q)

refix_values = [] #Create empty list to hold lists of matching FIX_INDEX values in df2

for index, df1_row in df1.iterrows(): #Iterate through each row in df1
    fix_index_list = [] #Create empty list to hold matching INDEX values for loop row
    df2_subset = df2.loc[(df2['PARTICIPANT'] == df1_row['PARTICIPANT']) & (df2['TRIAL'] == df1_row['TRIAL'])] #Subset out matching data in df2 so we're not looping through the entirety of df2
    for i, df2_row in df2_subset.iterrows(): #Iterate through df2 subset
        dist = calculate_distance(df1_row,df2_row) #Calculate dist for each row
        if dist <= 150:
            fix_index_list.append(df2_row['CURRENT_FIX_INDEX']) #Add dist to list if greater or equal to 150
    refix_values.append(','.join(map(str, fix_index_list))) #Add list of matching INDEX values to list of lists

df1['REFIX'] = refix_values #Convert list of lists to new column

def count_refix_items(row_refix_value):
    integers = [int(s) for s in row_refix_value.split(',') if s.isdigit()] #Split string by commas and count resultant elements
    return len(integers)

refix_per_trial = df1[df1['REFIX'].notna()].groupby(['TRIAL','PARTICIPANT'])['REFIX'].apply(lambda x: x.apply(count_refix_items).sum()).reset_index()
refix_per_trial = refix_per_trial.rename(columns={'REFIX': 'REFIXes/trial/participant'}) #Rename column

df1 = pd.merge(df1, refix_per_trial, on = ['TRIAL','PARTICIPANT'], how = 'outer')

#Write df to csv
df1.to_csv('REFIX_calc_output.tab', sep='\t', index=False) #Change name of file to whatever you want

我使用您提供的输入获得的输出: