completing a sparse timeline

user_8517060 0
Viewed 42

I have some sparse measurements of process status over time, looking like this:

               tag
2022-01-15  
2022-01-08  #Step3
2022-01-06  #Step2
2021-12-31  
2021-12-28  #Step1

... and I would like to transform them into a complete series that I can use to plot a timeline, looking like this:

               tag
2021-12-28  #Step1
2021-12-29  #Step1
2021-12-30  #Step1
2021-12-31  #Step1
2022-01-01  #Step1
2022-01-02  #Step1
2022-01-03  #Step1
2022-01-04  #Step1
2022-01-05  #Step1
2022-01-06  #Step2
2022-01-07  #Step2
2022-01-08  #Step3
2022-01-09  #Step3
2022-01-10  #Step3
2022-01-11  #Step3
2022-01-12  #Step3
2022-01-13  #Step3
2022-01-14  #Step3
2022-01-15  #Step3

The code below works, but it looks pretty ugly and I was wondering if there is a more elegant/efficient way to achieve this. Also, I get a SettingWithCopyWarning which I should be able to fix.

Thanks!!

#! /usr/bin/env python3

import pandas as pd


MY_SAMPLINGS = [
    {'timestamp': '2022-01-15', 'tag': ''}, 
    {'timestamp': '2022-01-08', 'tag': '#Step3'}, 
    {'timestamp': '2022-01-06', 'tag': '#Step2'}, 
    {'timestamp': '2021-12-31', 'tag': ''}, 
    {'timestamp': '2021-12-28', 'tag': '#Step1'}
    ]


myDates = [d['timestamp'] for d in MY_SAMPLINGS]
dates = pd.date_range(myDates[-1],myDates[0])
df = pd.DataFrame(index=dates, columns=['tag'])


for x in range((len(MY_SAMPLINGS)-1),0,-1): 
    startTime = MY_SAMPLINGS[x]['timestamp']
    endTime = MY_SAMPLINGS [x-1]['timestamp']
    if (MY_SAMPLINGS[x]['tag']):
        myTag = MY_SAMPLINGS[x]['tag']
    else:
        myTag = MY_SAMPLINGS[x+1]['tag']
    df.loc[startTime:endTime]['tag'] = myTag

print (df)
pandas python performance
2 Answers

Alright, I'll break everything down in small steps but it's just a few lines of code and is much more scalable than a loop.

import pandas as pd 

df = pd.DataFrame(MY_SAMPLINGS)

We first want to have the timestamps as an ordered time index.

df["timestamp"] = pd.to_datetime(df["timestamp"], format="%Y-%m-%d")
df = df.set_index("timestamp").sort_index()

Which gives :

               tag
timestamp
2021-12-28  #Step1
2021-12-31
2022-01-06  #Step2
2022-01-08  #Step3
2022-01-15

Then, resample on a daily basis and fill forward, meaning that any missing values generated by the resampling will take the value of the previous non-missing value.

df = df.resample("1D").ffill()

Which gives:

               tag
timestamp
2021-12-28  #Step1
2021-12-29  #Step1
2021-12-30  #Step1
2021-12-31
2022-01-01
2022-01-02
2022-01-03
2022-01-04
2022-01-05
2022-01-06  #Step2
2022-01-07  #Step2
2022-01-08  #Step3
2022-01-09  #Step3
2022-01-10  #Step3
2022-01-11  #Step3
2022-01-12  #Step3
2022-01-13  #Step3
2022-01-14  #Step3
2022-01-15

Now, we still have all those empty strings, let's just consider them as empty and forward fill again.

df = df.replace('', pd.NA).ffill()

Now we're done :

               tag
timestamp
2021-12-28  #Step1
2021-12-29  #Step1
2021-12-30  #Step1
2021-12-31  #Step1
2022-01-01  #Step1
2022-01-02  #Step1
2022-01-03  #Step1
2022-01-04  #Step1
2022-01-05  #Step1
2022-01-06  #Step2
2022-01-07  #Step2
2022-01-08  #Step3
2022-01-09  #Step3
2022-01-10  #Step3
2022-01-11  #Step3
2022-01-12  #Step3
2022-01-13  #Step3
2022-01-14  #Step3
2022-01-15  #Step3
user_94791280

You could try to use resample here. Note that I have replace an empty string with Nones so that I can use fillna with ffill method later on.

MY_SAMPLINGS = [
    {'timestamp': '2022-01-15', 'tag': None}, 
    {'timestamp': '2022-01-08', 'tag': '#Step3'}, 
    {'timestamp': '2022-01-06', 'tag': '#Step2'}, 
    {'timestamp': '2021-12-31', 'tag': None}, 
    {'timestamp': '2021-12-28', 'tag': '#Step1'}
    ]
df = pd.DataFrame(MY_SAMPLINGS).assign(timestamp=lambda x: pd.to_datetime(x["timestamp"])).set_index("timestamp")
result = df.resample('1D').pad().fillna(method="ffill")
user_140787580
Related