Pandas merge on Index but columns shifted by 1

Question

My first dataframe looks like this, df:

lastUpdated          midprice   returns
2020-05-12 00:19:10   0.000200    NaN
2020-05-12 00:19:20   0.000200  0.000000
2020-05-12 00:19:30   0.000200  0.000025
...
2020-05-12 06:56:40   0.000206  -0.000049
2020-05-12 06:56:50   0.000206  0.000000
2020-05-12 06:57:00   0.000206  0.000000

My second dataframe looks like this, df2:

                              ts         1.38259                lastUpdated
    lastUpdated         
   2020-05-12 00:19:10  1.589244e+12    3.36436     2020-05-12 00:42:32.202
   2020-05-12 00:19:20  1.589244e+12    3.34661     2020-05-12 00:42:23.399
   2020-05-12 00:19:30  1.589244e+12    3.34661     2020-05-12 00:42:23.399
   2020-05-12 00:19:40  1.589244e+12    3.15296     2020-05-12 00:40:47.384
    ...

    2020-05-12 06:59:30 1.589267e+12    35.121100   2020-05-12 06:58:24.165
    2020-05-12 06:59:40 1.589267e+12    35.121100   2020-05-12 06:58:24.165
    2020-05-12 06:59:50 1.589267e+12    35.521100   2020-05-12 06:57:31.452

What I need to do is to merge the dataframe like the below:

lastUpdated          midprice   returns   1.38259

2020-05-12 00:19:10   0.000200    NaN     3.34661
2020-05-12 00:19:20   0.000200  0.000000  3.34661
2020-05-12 00:19:30   0.000200  0.000025  3.15296

....

So the column 1.38259 is shifted by Index+1, I tried to do with merge but I am unable to do Index+1, I do not know if you understand what I try to achieve... any help? thanks!!

jwzinserl · Accepted Answer · 2020-05-14 16:35:20Z

1

you can create a new df2 column with the shifted column and then merge it normally with df1:

df2["shifted_col"] = df2["1.38259"].shift(1)
new_df = df1.merge(df2, left_index=True, right_index=True)

answered May 14, 2020 at 16:35

jwzinserl

4771 gold badge4 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Helio Santos · Accepted Answer · 2020-05-14 16:40:37Z

0

before merging, shift the column '1.38259' up:
df2['1.38259'] = df2['1.38259'].shift(-1)

answered May 14, 2020 at 16:40

Helio Santos

6,8653 gold badges28 silver badges31 bronze badges

Collectives™ on Stack Overflow

Pandas merge on Index but columns shifted by 1

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related