Fill gap in Panda's dataframe with repeated values

user_794402 0
Viewed 123

If I have a dataframe like the following

timestamp   v
1           5
2           6
6           7
8           8

Is there a way in Pandas to create a dataframe that completes the missing timestamp and uses the last assigned values of v without having to iterate between each line?

timestamp   v
1           5
2           6
3           6
4           6
5           6
6           7
7           7
8           8

I was able to solve it by iterating between the lines and detecting the missing values in the timestamp and adding the last value of v but it is too slow.

pandas python
2 Answers

Code

df = df.set_index('timestamp')
df = df.reindex(np.arange(df.index[0], 1+df.index[-1])).ffill().reset_index().astype(int)

Output

    timestamp   v
0   1           5
1   2           6
2   3           6
3   4           6
4   5           6
5   6           7
6   7           7
7   8           8

Explanation

We are setting the index to timestamp, then we are reindexing the index based on first and last value of index and then forward fill the values.

user_21069340

You can use merge_asof:

# consider `pd.date_range` to generate range of timestamps
all_time = pd.DataFrame({'timestamp':np.arange(df.timestamp.iloc[0], 
                                               df.timestamp.iloc[-1]+1)
                        })
pd.merge_asof(all_time, df, on='timestamp', direction='backward')

Output:

   timestamp  v
0          1  5
1          2  6
2          3  6
3          4  6
4          5  6
5          6  7
6          7  7
7          8  8
user_42384080
Related