Merge rows with index +-1 of the current row

Question

I have quite an interesting question. I am trying to merge rows that are too close to each other. Obviously "too close" depends on what you want too close to be but what I want to do is merging rows that are +-1 row close to another. I have this dataframe:

index   Händelse                        Time        Fuel level (%)  Km driven (km)  Difference (%)
61  Bränslenivåökning vid stillastående 20210601    100             1325217         73
124 Bränslenivåökning vid stillastående 20210601    93              1325708         63
125 Position                            20210601    97              1325708         4
126 Position                            20210601    100             1325720         3
176 Bränslenivåökning vid stillastående 20210602    100             1326038         46
234 Bränslenivåökning vid stillastående 20210603    90              1326528         56
235 Position                            20210603    96              1326528         6
236 Position                            20210603    100             1326540         4
301 Bränslenivåökning vid stillastående 20210603    100             1327019         77
360 Position                            20210603    42              1327510         9
361 Bränslenivåökning vid stillastående 20210603    92              1327510         50
362 Position                            20210604    100             1327513         8
436 Bränslenivåökning vid stillastående 20210604    100             1328013         72
499 Bränslenivåökning vid stillastående 20210606    87              1328504         57
500 Position                            20210606    98              1328506         11
501 Position                            20210606    100             1328516         2
...

As you can see in the index, there are multiple occurrences where the rows are followed up by another one with a very small time difference (I gather the data using a 10-minute interval which is not shown in the time column but is shown by looking at the index tab. For example 124, 125 and 126 who are close to each other). However, because of the small-time difference, I would like to sum the "Difference-column" for these rows but not the "Km driven", "fuel level" or "Time". In conclusion, if we take 124, 125, and 126 for example, I would like the output to be:

index   Händelse                        Time        Fuel level (%)  Km driven (km)  Difference (%)
126 Bränslenivåökning vid stillastående 20210601    100 (from 126)  1325710 (126)   70 (124, 125, 126)

To quickly explain what is happening in the data, there are different time stamps where a change in the fuel tank is taking place. This makes the analyst of the data assume that a refueling process is taking place. However, sometimes these "refueling-processes" take more than my time interval, resulting in it being noted as 3 different (like row 124, 125, 126) positive changes in the fuel tank. Also, I can't change the time interval.

Hopefully, this was enough information. Thank you in advance!

CURRENT CODE

from tkinter import Tk  # from tkinter import Tk for Python 3.x
from tkinter.filedialog import askopenfilename

import pandas as pd

Tk().withdraw()
filepathname1 = askopenfilename()
filepathname2 = askopenfilename()

print("You have chosen to mix", filepathname1, "and", filepathname2)
pd.set_option("display.max_rows", None, "display.max_columns", 10)

df1 = pd.read_excel(
    filepathname1, "CWA107 Event", na_values=["NA"], skiprows=1, usecols="A, B, D, E, F"
)
df2 = pd.read_excel(
    filepathname2,
    na_values=["NA"],
    skiprows=1,
    usecols=["Tankad mängd diesel", "Unnamed: 3"],
)

df1["Difference (%)"] = df1["Bränslenivå (%)"]
df1["Difference (%)"] = df1.loc[:, "Bränslenivå (%)"].diff()

# Renames time-column so that they match
df2.rename(columns={"Unnamed: 3": "Tid"}, inplace=True)

# Drop NaN
df2.dropna(inplace=True)

# Drop NaN
df1.dropna(inplace=True)

# Filters out the rows with a difference smaller than 2
df1filt = df1[(df1["Difference (%)"] >= 2)]
print(len(df1filt))

# Converts time-column to only year, month and date.
df1filt["Tid"] = pd.to_datetime(df1filt["Tid"]).dt.strftime("%Y%m%d").astype(str)

print(df1filt)

df1filt.reset_index(level=0, inplace=True)


filepathname3 = askopenfilename()
df1filt.to_excel(filepathname3, index=False)

input()

user_16850840 · Answer

So I solved this problem by creating a new column that depends on the difference between my row-column (formerly known as the index column in the dataframe above). The new column represents the difference in the row column. If the difference is more than 1 row then it sets the value of the row to 0. By giving either 1 or 0 to these rows in the 'Match'-column I can further know what values to merge and which not to.

If the value = 0 then it will set the actual amount refueled to be the value of the current row (it does not merge with other rows) and it is marked as summed. If the value is bigger than 1 and under 4, the values are added. This will repeat until it hits an row with the value 0 which will mark it as "summed".

Here is the code (feel free to make changes):

df1filt["Match"] = df1filt["row"]
df1filt["Match"] = df1filt.loc[:, "row"].diff()

df1filt['Match'].values[df1filt['Match'].values > 1] = 0

ROWRANGE = len(df1filt)+1

thevalue = 0
for currentrow in range(ROWRANGE-1):
    if df1filt.loc[currentrow, 'Match'] == 0.0:
        df1filt.loc[currentrow-1,'Difference (%)'] = thevalue
        df1filt.loc[currentrow-1,'Match'] = "SUMMED"
        thevalue = df1filt.loc[currentrow, 'Difference (%)']
    if df1filt.loc[currentrow, 'Match'] >= 1.0 and df1filt.loc[currentrow, 'Match'] <= 4:
        thevalue += df1filt.loc[currentrow, 'Difference (%)']

Merge rows with index +-1 of the current row

1 Answers