Pandas Merge And Grouby

I have 2 pandas dataframes which looks like below. Data Frame 1: Section Chainage Frame R125R002 10.133 1 R125R002 10.138 2 R125R002 10.143 3 R125R002

Solution 1:

You can first aggregate by each 5 rows with define functions in dictionary:

d = {'Section':'first','Chainage':'first','1':'sum','2':'max', '8':'mean'}
df22 = df2.groupby([np.arange(len(df2.index)) // 5], as_index=False).agg(d)print (df22)
    Section  Chainage  1280  R125R002    10.1330001  R125R002    10.1380002  R125R002    10.1430003  R125R002    10.1480004  R125R002    10.153000

Detail:

print (np.arange(len(df2.index)) // 5)
[000001111122222333334]

And then need merge:

df = df1.merge(df22, on=['Section','Chainage'])
print (df)
    Section  Chainage  Frame  1  2  8
0  R125R002    10.133      1  0  0  0
1  R125R002    10.138      2  0  0  0
2  R125R002    10.143      3  0  0  0
3  R125R002    10.148      4  0  0  0
4  R125R002    10.153      5  0  0  0