Skip to content
Advertisement

how to compare two columns and get the mean value of the the 3rd column for all matching items in the two in python pandas dataframe?

I have the following table named Rides :

start_id end_id eta
A B 5
B C 4
A C 6
A B 5
B A 3
C A 3
B C 6
C A 5
A B 8

From the Rides Table , I want to Create a new table which should look like something like below :

start_id end_id mean _eta
A B 6 ((5+5+8)/3)
B C 5 ((4+6)/2))
A C 6
B A 3
C A 4 ((3+5)/2))

so mean_eta of 1st row is returning 8 as there are three matching rides between start_ID = “A” and end_ID = “B” with eta 5,5,8 , so the mean_eta = (5+5+8)/3 = 6 How should I do it ? Please help .

Advertisement

Answer

groupby and get the aggregate mean. Code below;

df.groupby(['start_id','end_id'])['eta'].agg('mean').to_frame('eta-mean')
User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement