Convert time stored as six digit number to time stamp and find difference in hrs of two such time
I have a dataset where date stored in format yyyyMMdd and time stores as hhmmss (see image)
i need to calculate the difference in hrs between Datetime combined of both type of columns , can someone help, already tried through various methods regex, python code no success.
Operating system used: windows 11
Best Answer
-
RoyE Dataiker, Dataiku DSS Core Designer, Dataiku DSS ML Practitioner, Dataiku DSS Adv Designer, Registered Posts: 31 DataikerOptionsHi RAKS,
Assuming that there are no issues with the rows in your dataset, this can be accomplished through a prepare recipe. Please see below for more details.
Step 1: Concatenate columns
This will output a string with the format of yyyyMMddHHmmss. You will use this in the next step.
Step 2: Format the string into a proper timestamp
This will output a new column, timestamp_start_formatted in a proper date format.
Do the above for your start time and stop time before continuing to calculate the time between columns.
Step 3: Calculate the time between two columns.
As you can see, this step will calculate the time in minutes from column timestamp_start_formatted until column timestamp_stop_formatted.
You can change the column names to whatever your column's names are/want to be.
A sample output from the dataset that you provided:
Hope this helps!
Answers
-
Thanks RoyE, it works