Home Is there a way in python using Pandas to merge two rows when one column value is equal the another column value of the following line

Questions

Is there a way in python using Pandas to merge two rows when one column value is equal the another column value of the following line

byMR

September 21, 2023

I have the flowing problem. I have a data frame as follow:

Country_code    country    low_number    high_number
-----------------------------------------------------
AU              Australia  1             10
FR              France     2             45
AU              Australia  10            23
AU              Australia  30            43
AU              Australia  43            55
FR              France     45            55
FR              France     67            80
FR              France     80            98

I would like to get an output such as:

Country_code    country    low_number    high_number
-----------------------------------------------------
AU              Australia  1             23
FR              France     2             55
AU              Australia  30            55
FR              France     67            98

How can I merge rows with a similar country or country_code when the high_number is equal to the low_number of the next line with the same country ?

Note that my data frame is already order by low_number values.

My level in Pandas is low. However I already group by countries:

countriesGroup = df.groupby(['country'], as_index=False)

I then unsuccessfully tried to groupby on one specific country using a condition:

france = countriesGroup.get_group('France')
france.groupby(france['high_number'] == france['low_number'].shift(1)).agg( { 'high_number': max, 'low_number': min})

I don’t know how to keep all the other columns while replacing the low_number value by the min value and the high_number by the high calue using agg.

Thank you in advance!

>Solution :

You can merge the DataFrame with itself:

>>> df.rename(columns={"high_number":"key"}).merge(df.rename(columns={"low_number":"key"})).drop(columns="key")

  Country_code    country  low_number  high_number
0           AU  Australia         1.0         23.0
1           FR     France         2.0         55.0
2           AU  Australia        30.0         55.0
3           FR     France        67.0         98.0