python-3.xanalyticslocf

Last Observation Carried Forward in python with datetime

I have this event dataset and while retrieving it only recorded the changes and I want these changes to be converted to a uniform time series. The data is recorded at 12 hour time interval. The retrieval_time is an object and start_time is datetime64.

   ID        Count  retrieval_time                start_time
   100231380 70     2017-10-11T23:30:00.000+10:30 21/10/17 23:30
   100231380 70     2017-10-12T11:30:00.000+10:30 21/10/17 23:30
   100231380 72     2017-10-12T23:30:00.000+10:30 21/10/17 23:30
   100231380 72     2017-10-13T11:30:00.000+10:30 21/10/17 23:30
   100231380 73     2017-10-13T23:30:00.000+10:30 21/10/17 23:30
   100231380 74     2017-10-14T11:30:00.000+10:30 21/10/17 23:30
   100231380 74     2017-10-14T23:30:00.000+10:30 21/10/17 23:30
   100231380 74     2017-10-15T11:30:00.000+10:30 21/10/17 23:30
   100231380 77     2017-10-15T23:30:00.000+10:30 21/10/17 23:30
   100231380 83     2017-10-16T11:30:00.000+10:30 21/10/17 23:30
   100231380 85     2017-10-16T23:30:00.000+10:30 21/10/17 23:30
   100231380 85     2017-10-17T11:30:00.000+10:30 21/10/17 23:30
   100231380 90     2017-10-17T23:30:00.000+10:30 21/10/17 23:30
   100231380 90     2017-10-18T11:30:00.000+10:30 21/10/17 23:30
   100231380 93     2017-10-18T23:30:00.000+10:30 21/10/17 23:30
   100231380 99     2017-10-19T23:30:00.000+10:30 21/10/17 23:30
   100231380 104    2017-10-20T23:30:00.000+10:30 21/10/17 23:30
   100231380 117    2017-10-21T23:30:00.000+10:30 21/10/17 23:30

I want to be able to make it consistent for example in last 3 rows, from 19/10/2017 in retrieval time, there is no recorded data for 11:30am. I want to be able to add a row and replace it with last observation for entire row.

I want to output to be something like this..

   ID        Count  retrieval_time                start_time
   100231380 70     2017-10-11T23:30:00.000+10:30 21/10/17 23:30
   100231380 70     2017-10-12T11:30:00.000+10:30 21/10/17 23:30
   100231380 72     2017-10-12T23:30:00.000+10:30 21/10/17 23:30
   100231380 72     2017-10-13T11:30:00.000+10:30 21/10/17 23:30
   100231380 73     2017-10-13T23:30:00.000+10:30 21/10/17 23:30
   100231380 74     2017-10-14T11:30:00.000+10:30 21/10/17 23:30
   100231380 74     2017-10-14T23:30:00.000+10:30 21/10/17 23:30
   100231380 74     2017-10-15T11:30:00.000+10:30 21/10/17 23:30
   100231380 77     2017-10-15T23:30:00.000+10:30 21/10/17 23:30
   100231380 83     2017-10-16T11:30:00.000+10:30 21/10/17 23:30
   100231380 85     2017-10-16T23:30:00.000+10:30 21/10/17 23:30
   100231380 85     2017-10-17T11:30:00.000+10:30 21/10/17 23:30
   100231380 90     2017-10-17T23:30:00.000+10:30 21/10/17 23:30
   100231380 90     2017-10-18T11:30:00.000+10:30 21/10/17 23:30
   100231380 93     2017-10-18T23:30:00.000+10:30 21/10/17 23:30
   100231380 93     2017-10-19T11:30:00.000+10:30 21/10/17 23:30
   100231380 99     2017-10-19T23:30:00.000+10:30 21/10/17 23:30
   100231380 99     2017-10-20T11:30:00.000+10:30 21/10/17 23:30
   100231380 104    2017-10-20T23:30:00.000+10:30 21/10/17 23:30
   100231380 104    2017-10-21T11:30:00.000+10:30 21/10/17 23:30
   100231380 117    2017-10-21T23:30:00.000+10:30 21/10/17 23:30

I also want to know how to format the retrieval_time and start_time to make it similar to be able to compare it.

And, I want some generic solution as I have aggregated grouped data for multiple events and time interval is the same 12 hours, however, the retrieval_time and start_time is different for all the events.

Thanks.

Solution

  • This is how I have implemented the above, based on my understanding. My csv data is:

    id,count,ret_time,start_time
10022,60,2017-10-11T11:30:00.000+10:30,21/10/2017 23:30
10023,70,2017-10-11T23:30:00.000+10:30,21/10/2017 23:30
10024,70,2017-10-12T11:30:00.000+10:30,21/10/2017 23:30
10025,80,2017-10-12T23:30:00.000+10:30,21/10/2017 23:30
10026,90,2017-10-13T11:30:00.000+10:30,21/10/2017 23:30
10027,95,2017-10-14T11:30:00.000+10:30,21/10/2017 23:30

    Script below:

    import csv
import time
import datetime
import os
from pathlib import Path

#Read csv data (my file is in a folder '/data')
data_folder = Path(os.getcwd())
file_path = data_folder / 'data/stack_overflow.csv'

#Create list to store csv data
csv_data = []

#Read csv file
with open(file_path) as csvFile:
    readCsv = csv.reader(csvFile, delimiter=',')
    #Skip header
    next(readCsv)
    for row in readCsv:
        #Add rows in the end of the list
        csv_data.append(row)

#Transform time in string to datetime object in dict
for row in range(len(csv_data)):
  #Convert the time to floating point milliseconds
  csv_data[row][2] = time.mktime(time.strptime(csv_data[row][2], '%Y-%m-%dT%H:%M:%S.%f%z'))

#Parse the dictionary and compare difference between ret_times
prev_time = csv_data[0][2]
print(type(csv_data[row][2]))
for row in range(len(csv_data)):
    #Find delta in hours (divide by seconds/hr)
    delta = (csv_data[row][2] - prev_time) / 3600
    prev_time = csv_data[row][2]

    #If the delta is greater than 24 hours, i.e
    #there is no value for the 12 hour difference
    #then copy the (current row - 1) and assign to a new temp list,
    #update the time to 12 hours ahead in the new list,
    #add the list item before the current row in dict 

    if delta > 12.0:
      #index of item that is to be copied (current row - 1)
      idx = row - 1
      #Store the value to be copied in a temp list
      temp_list = []
      temp_list = csv_data[idx].copy()
      #Add 12 hours to the time (add seconds)
      temp_list[2] = temp_list[2] + 43200
      #Add temp_list element before current row
      csv_data.insert(row, temp_list)

#Shows that id: 1026 is added before 1027 as 1026 is missing the value for 11:30PM 
print(csv_data)

    You can follow the same logic to convert start_time as in:

    csv_data[row][2] = time.mktime(time.strptime(csv_data[row][2], '%Y-%m-%dT%H:%M:%S.%f%z'))

    and then do a comparison between ret_time and start_time.

    Hope this helps.