Dataframe has two columns item-1 and item-2, I want to find average of Buy and Sell columns for all the rows where item-1 value is in item-2 and item-2 value is in item-1
Here the value of first row in item-1 is A, item-2 has value A at index 2,4 and 6, so the avg-item-1-buy should be average of buy value at those indexes
similarly the value of first row in item-2 is C, item-1 has value C at index 2 and 5, so the avg-item-2-buy should be average of buy value at those indexes
Expected Result:
I don't think this is the best answer but this surely will do it (i have try it).
First we calculate the average and store to the dictionary. I made 4 dictionary for each desired columns.
def count_avg(col_1, col_2, target_col):
col_avg= {}
for i in df[col_1].unique():
col_avg[i]= df[df[col_2]==i][target_col].mean()
return col_avg
item1_avg_buy_dict= count_avg('Item-1', 'Item-2', 'buy')
item1_avg_sell_dict= count_avg('Item-1', 'Item-2', 'sell')
item2_avg_buy_dict= count_avg('Item-2', 'Item-1', 'buy')
item2_avg_sell_dict= count_avg('Item-2', 'Item-1', 'sell')
And then we create a function to pair x with the dictionary we just created.
def get_avg(x, dict_):
try:
res= dict_[x]
except:
res= None
return res
And then we can apply each new columns for that function by .map()
df['avg-item1-buy']= df['Item-1'].map(lambda x: get_avg(x, item1_avg_buy_dict))
df['avg-item1-sell']= df['Item-1'].map(lambda x: get_avg(x, item1_avg_sell_dict))
df['avg-item2-buy']= df['Item-2'].map(lambda x: get_avg(x, item2_avg_buy_dict))
df['avg-item2-sell']= df['Item-2'].map(lambda x: get_avg(x, item2_avg_sell_dict))
