I have the following two initial dataframes:
df1 = pd.DataFrame(
{
'trip': [1, 1, 2, 2, 2, 3,3],
'segment': [0,1,1,3,5,2,4],
'trip-distance': [2800,2800, 3500,3500, 3500, 2200,2200],
'segment-length': [1100, 1300,1000,1200,2300,900,1000]
}
)
df2 = pd.DataFrame(
{
'trip': [1,1,1,1,1,2,2,2,2,2,2,3,3,3],
'segment': [0,0,1,1,1,1,1,3,5,5,5,2,4,4],
'instance': [1,2,1,2,3,1,2,1,1,2,3,1,1,2],
'speed': [15.4,17.6,22.8,25.0,27,9.8,9.2, 12.4,27.1,25,24.3,5.3,12.5,13],
'bearing': [112,113.5,271,275,273,281,287,231,269,270,268,79,181,183]
}
)
df1
trip segment trip-distance segment-length
0 1 0 2800 1100
1 1 1 2800 1300
2 2 1 3500 1000
3 2 3 3500 1200
4 2 5 3500 2300
5 3 2 2200 900
6 3 4 2200 1000
df2
trip segment instance speed bearing
0 1 0 1 15.4 112.0
1 1 0 2 17.6 113.5
2 1 1 1 22.8 271.0
3 1 1 2 25.0 275.0
4 1 1 3 27.0 273.0
5 2 1 1 9.8 281.0
6 2 1 2 9.2 287.0
7 2 3 1 12.4 231.0
8 2 5 1 27.1 269.0
9 2 5 2 25.0 270.0
10 2 5 3 24.3 268.0
11 3 2 1 5.3 79.0
12 3 4 1 12.5 181.0
13 3 4 2 13.0 183.0
But I want to expand df2 with the columns trip-distance, segment-length from df1 assigning the trips distances and segment lengths corresponding to such trip & segment accordingly.
Expected result:
trip segment instance speed bearing trip-distance segment-length
0 1 0 1 15.4 112.0 2800 1100
1 1 0 2 17.6 113.5 2800 1100
2 1 1 1 22.8 271.0 2800 1300
3 1 1 2 25.0 275.0 2800 1300
4 1 1 3 27.0 273.0 2800 1300
5 2 1 1 9.8 281.0 3500 1000
6 2 1 2 9.2 287.0 3500 1000
7 2 3 1 12.4 231.0 3500 1200
8 2 5 1 27.1 269.0 3500 2300
9 2 5 2 25.0 270.0 3500 2300
10 2 5 3 24.3 268.0 3500 2300
11 3 2 1 5.3 79.0 2200 900
12 3 4 1 12.5 181.0 2200 1000
13 3 4 2 13.0 183.0 2200 1000
