I am currently trying to solve a problem revolving around data sorting in a python pandas code. I basically have a bunch of fuel data that is logged every x minutes to an excel file. One of the things it logs is the amount of fuel in the tank (%) at a specific time (let us say it checks every 5 minutes). I want to get how much has been refueled. However, the problem is that sometimes the refueling process is split into two different timeframes (for example if the refueling process starts 4 minutes and 40 seconds after the last tank (%) was checked). Because the refueling started at this time it will split into two processes (because refueling takes longer than 20 seconds). I partially fixed this problem by using the difference in my index column to assume that the time interval between two rows is short enough to be two separate refueling processes. This is shown in the "row-difference"-column below.
row Händelse Tid Bränslenivå (%) Vägmätare (km) Difference (%) row-difference
0 61 Bränslenivåökning vid stillastående 20210601 100.0 1325217.0 73.0 NaN
1 124 Bränslenivåökning vid stillastående 20210601 93.0 1325708.0 63.0 0.0
2 125 Position 20210601 97.0 1325708.0 4.0 1.0
3 126 Position 20210601 100.0 1325720.0 3.0 1.0
4 176 Bränslenivåökning vid stillastående 20210602 100.0 1326038.0 46.0 0.0
5 234 Bränslenivåökning vid stillastående 20210603 90.0 1326528.0 56.0 0.0
6 235 Position 20210603 96.0 1326528.0 6.0 1.0
7 236 Position 20210603 100.0 1326540.0 4.0 1.0
8 301 Bränslenivåökning vid stillastående 20210603 100.0 1327019.0 77.0 0.0
9 360 Position 20210603 42.0 1327510.0 9.0 0.0
10 361 Bränslenivåökning vid stillastående 20210603 92.0 1327510.0 50.0 1.0
11 362 Position 20210604 100.0 1327513.0 8.0 1.0
What I am currently trying to do, is to add the rows in the "Difference (%)"-column where the row difference is 1. For example, I would like to sum the "Difference (%)" in rows 124, 125, and 126 and just print it as 126 by summing up all the "Difference (%)" cells.
In other words, my desired output is:
row Händelse Tid Bränslenivå (%) Vägmätare (km) Difference (%) row-difference
0 61 Bränslenivåökning vid stillastående 20210601 100.0 1325217.0 73.0 NaN
1 124 Bränslenivåökning vid stillastående 20210601 93.0 1325708.0 70.0 0.0
4 176 Bränslenivåökning vid stillastående 20210602 100.0 1326038.0 46.0 0.0
5 234 Position 20210603 100.0 1326540.0 66.0 0.0
8 301 Bränslenivåökning vid stillastående 20210603 100.0 1327019.0 77.0 0.0
9 360 Position 20210604 100.0 1327513.0 67.0 0.0
As you can see in the "row-difference"-column, there is no 1.0:s. This is because the rows with the value 1.0 have been added to the row above until it hits the value 0.0. This is my solution, but I am not sure how to further on procced. As mentioned I have come as far as marking the values I want to sum upwards with 1.0 in the row-difference column but am yet to add them to each other (direction up).
Hopefully, this is enough information, the code is provided below.
CURRENT CODE (dont mind the unnecessary imports)
import numpy as np
import os
from numpy import empty, int64, percentile
import pandas as pd
from pandas.core.base import IndexOpsMixin
from pandas.core.frame import DataFrame
from tkinter import Tk # from tkinter import Tk for Python 3.x
from tkinter.filedialog import askopenfilename
import logging
import sys
import pathlib
from datetime import date, datetime
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)
df1filt.rename(columns={"index": "row"}, inplace=True)
df1filt["row-difference"] = df1filt["row"]
df1filt["row-difference"] = df1filt.loc[:, "row"].diff()
df1filt['row-difference'].values[df1filt['Match'].values > 1] = 0
Thank you in advance!