Fastest way to create time series dataframe

user_8309904 0
Viewed 97

I want to take data that contains time gaps as well as time repeats and basically create a time series using the first occurrence of any given time and filling forwards. Consider the following example.

Lets say this is the time range we are interested in:

Time
1:00
1:01
1:02
1:03
1:04
1:05

And this is the data, dataframe X, we would like to put into our time series:

Occurance Value
1:00 "R"
1:03 "G"
1:03 "L"
1:03 "P"
1:03 "T"
1:05 "S"

And this is the Final Dataframe:

Occurance Value
1:00 "R"
1:01 "R"
1:02 "R"
1:03 "G"
1:04 "G"
1:05 "S"

As you can see, in the Final Dataframe, 1:00 has the value "R" because that is the value in the first occurrence of 1:00 in dataframe X. 1:01 and 1:02 also have the value "R" because there is no data for those time instances in dataframe X and will therefore use the last valid value (which is the value for 1:00). 1:03 has the value "G" because, similar to the case with 1:00, "G" is the first value for 1:03 that we have in dataframe X. Since there is no value for 1:04 in dataframe X, 1:04 gets the last valid value, "G", in our resulting dataframe. Lastly, 1:05 will have the value "S" in our resulting dataframe as that is the value for the first occurrence of 1:05 in dataframe X.

What is the quickest way to accomplish this?

pandas series dataframe time python
1 Answers

merge_asof

See Solution below to see final solution.

First, we need to change those columns to pd.Timedelta:

df1['Time'] = pd.to_timedelta(df1['Time'] + ':00')
df2['Occurance'] = pd.to_timedelta(df2['Occurance'] + ':00')

Then we can merge_asof

pd.merge_asof(df1, df2, left_on='Time', right_on='Occurance')

             Time       Occurance Value
0 0 days 01:00:00 0 days 01:00:00     R
1 0 days 01:01:00 0 days 01:00:00     R
2 0 days 01:02:00 0 days 01:00:00     R
3 0 days 01:03:00 0 days 01:03:00     T
4 0 days 01:04:00 0 days 01:03:00     T
5 0 days 01:05:00 0 days 01:05:00     S

There are a couple of things wrong with this:

  1. There are more columns than OP specified.
  2. I have Ts instead of Gs

Ok to get rid of the columns, we just rename one of the columns instead of using left_on/right_on

pd.merge_asof(df1.set_axis(['Occurance'], axis=1), df2)

        Occurance Value
0 0 days 01:00:00     R
1 0 days 01:01:00     R
2 0 days 01:02:00     R
3 0 days 01:03:00     T
4 0 days 01:04:00     T
5 0 days 01:05:00     S

But we still have Ts instead of Gs and that's because look at df2

        Occurance Value
0 0 days 01:00:00     R
1 0 days 01:03:00     G  # same Occurance
2 0 days 01:03:00     L  # same Occurance
3 0 days 01:03:00     P  # same Occurance
4 0 days 01:03:00     T  # same Occurance
5 0 days 01:05:00     S

Obviously, Pandas took the last one while OP wanted first one. So let's try again with drop_duplicates

Solution

pd.merge_asof(df1.set_axis(['Occurance'], axis=1),
              df2.drop_duplicates('Occurance'))

        Occurance Value
0 0 days 01:00:00     R
1 0 days 01:01:00     R
2 0 days 01:02:00     R
3 0 days 01:03:00     G
4 0 days 01:04:00     G
5 0 days 01:05:00     S
user_23366540
Related