Pandas Merging rows with column values within a range of each other

Question

I have an example dataframe as shown below

   x    y   dx
0  1  6.0  1.1
1  2  6.0  1.5
2  2  6.5  1.2
3  3  7.2  4.3
4  4  7.5  4.5
5  4  8.0  4.7
6  5  1.1  7.0

I would like to merge the rows if the values in column dx are within a range of 1 of each other. There will be no overlapping ranges. I can either keep one of those rows and drop the rest or take an average of all the rows. So the expected output would look like

   x    y   dx
1  1  6.0  1.1
2  3  7.2  4.3
3  5  1.1  7.0

or

   x     y     dx
0  1.67  6.17  1.26
1  3.67  7.57   4.5
2  5     1.1   7.0

Does this answer your question? [Pandas Merging 101](https://stackoverflow.com/questions/53645882/pandas-merging-101) — Trenton McKinney, Sep 04 '20 at 22:37

IoaTzimas · Accepted Answer · 2020-09-04T23:02:37.847

1

You can have the first option with the following:

import pandas as pd
new_df=df[0:1]
for i in range(1,len(df)):
    if df.dx.iloc[i]-new_df.dx.iloc[-1]>1:
        new_df=pd.concat([new_df, df.iloc[i:i+1,:]], ignore_index=True)

edited Sep 04 '20 at 23:02

answered Sep 04 '20 at 22:41

IoaTzimas

10,538
2
13
30

I have modified the example data frame to correctly reflect the problem. It is not necessary that one row will have all integers. – beeprogrammer Sep 04 '20 at 22:47
So, starting from first row, you want to keep first row and drop all rows in range dx+1 then keep the next row (no matter the value of dx) and drop next rows that have dx+1 from that row, etc? – IoaTzimas Sep 04 '20 at 22:50
yes, that's correct – beeprogrammer Sep 04 '20 at 22:50
i have made some changes, please check my answer – IoaTzimas Sep 04 '20 at 23:07
Thanks for the acceptance. Maybe an upvote too? Regards – IoaTzimas Sep 04 '20 at 23:09

score 0 · Answer 2 · answered Sep 04 '20 at 23:13

0

Try this

df_final = df.groupby((df.dx.diff().abs() > 1).cumsum(), as_index=False).first()

Out[288]:
   x    y   dx
0  1  6.0  1.1
1  3  7.2  4.3
2  5  1.1  7.0

answered Sep 04 '20 at 23:13

Andy L.

24,909
4
17
29

Pandas Merging rows with column values within a range of each other

2 Answers2