Why do i lose rows when upsample pandas dataframe?

user_5187911 0
Viewed 44

This is probably due to my crappy math/pandas skills but i don't understand why i lose rows when i upsample a pandas dataframe. Here is an exmaple:

In:
import numpy as np
import pandas as pd

tester = pd.DataFrame(np.linspace(0, 3, 4))
tester.index = pd.to_datetime(tester.index, unit="s")
print(tester.shape)
tester

Out:
(4, 1)
1970-01-01 00:00:00     0.0
1970-01-01 00:00:01     1.0
1970-01-01 00:00:02     2.0
1970-01-01 00:00:03     3.0

In:
tester2 = tester.resample('0.5S').ffill()
print(tester2.shape)
tester2

Out:
(7, 1)
    
1970-01-01 00:00:00.000     0.0
1970-01-01 00:00:00.500     0.0
1970-01-01 00:00:01.000     1.0
1970-01-01 00:00:01.500     1.0
1970-01-01 00:00:02.000     2.0
1970-01-01 00:00:02.500     2.0
1970-01-01 00:00:03.000     3.0

This gets worse as I increase the upsampling rate. Is there any way to circumvent the behavior? E.g. if the input is 4 rows then i want to see exactly 8 rows after '0.5S' upsampling.

pandas dataframe python
1 Answers

It's a matter of math.

If you resample you don't change the boundaries. You still have 4 initial values, and you add as many values as there are intermediates (4 values -> 3 intermediates, n values -> n-1 intermediates)

Here is a workaround using reindex:

freq = '0.5S'
delta = tester.index[1]-tester.index[0]
idx = pd.date_range(tester.index.min(),
                    tester.index.max()+delta,
                    inclusive='left',
                    freq=freq)

tester.reindex(idx).ffill()

output:

                           0
1970-01-01 00:00:00.000  0.0
1970-01-01 00:00:00.500  0.0
1970-01-01 00:00:01.000  1.0
1970-01-01 00:00:01.500  1.0
1970-01-01 00:00:02.000  2.0
1970-01-01 00:00:02.500  2.0
1970-01-01 00:00:03.000  3.0
1970-01-01 00:00:03.500  3.0
as function
def upsample(df, freq):
    delta = df.index[1]-df.index[0]
    idx = pd.date_range(df.index.min(),
                        df.index.max()+delta,
                        inclusive='left',
                        freq=freq)

    return df.reindex(idx).ffill()

freqs = ['1S', '0.8S', '0.6S', '0.5S','0.4S', '0.2S', '0.1S', '0.05S']
# checking shapes
{freq: upsample(tester, freq).shape for freq in freqs}

output:

{'1S': (4, 1),
 '0.8S': (5, 1),
 '0.6S': (7, 1),
 '0.5S': (8, 1),
 '0.4S': (10, 1),
 '0.2S': (20, 1),
 '0.1S': (40, 1),
 '0.05S': (80, 1)}
user_163434640
Related