Im trying to read two big excel files and depending on if they match I want them to row up and the same row. I have 2 columns that have the same data but written in different forms (Illustrated below). Because the dates are written in different ways I cant match them (I want to make the data from the same dates to be on the same row).
Illustration:
Excel doc 1:
Time Fuel (l)
1 2021-12-12 14:44 33
2 2021-12-13 16:34 53
3 2021-12-14 12:14 255
4 2021-12-15 15:42 22
Excel doc 2:
Time Fuel (l)
1 20211212 33
2 20211213 53
3 20211214 255
4 20211215 22
My current code:
import os
from numpy import empty, percentile
import pandas as pd
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()
# Choosing filepath
filepathname1 = askopenfilename()
filepathname2 = askopenfilename()
print("You have chosen to mix", filepathname1, "and", filepathname2)
# Reading the 2 excels
df1 = pd.read_excel(filepathname1, 'CWA107 Event', na_values=['NA'], usecols="A, B, F")
df2 = pd.read_excel(filepathname2)
df1.dropna(inplace=True)
df2.dropna(inplace=True)
# Filtering 1st excel so that only the dates I need are included
df1filt = df1[(df1['Händelseinfo'] == "Bränslenivåökning vid stillastående")]
print(df1filt)
print("Antal tankningar:", len(df1filt))
input()
In my dataframes there are time columns which I would like to use as a reference point for the other data on the same row so I can match the data. However, one time columns is written like this:
Time
1 20211212
and one like this:
Time
1 2021-12-12 14:44
Which results in me not being able to make them match in my code. (There is also not only date but also time in the second one which also makes it harder to match, I would like to remove this in my code).
Hopefully this is enough info to get you the idea of what I am trying to accomplish.