Shifting rows with multiindex Pandas

Question

I want to create a table of stocks consisting of three columns:

Quantity Day - 1 | Quantity traded | Quantity Day 0

The stocks come in a frame like :

> df
Date        Stock     Quantity
2019-04-01  ALSC3      19600
            AMAR3      3080
2019-04-02  ALSC3       4000
            AMAR3      3070

I expected that a simple shift would match the index (Date,StockTicker), but in reality it ignores the index and shifts the row entirely.

Date        Stock     Quantity Day-1
2019-04-01  ALSC3      3080
            AMAR3      4000
2019-04-02  ALSC3      3070 
            AMAR3      NaN

Does anyone know how to perform it correctly? I would like something like this:

Date        Stock     Quantity     Quantity Day -1
2019-04-01  ALSC3      19600       4000
            AMAR3      3080        3070
2019-04-02  ALSC3      4000        NaN
            AMAR3      3070        NaN

Quang Hoang · Accepted Answer · 2019-04-05 16:52:52Z

2

Here you go, assuming the date index are countinuous.

df.Quantity.groupby(level=1).shift(-1)

This matches your expected output. Although I think "Quantity Day-1" means shift() instead of shift(-1).

edited Apr 5, 2019 at 16:52

answered Apr 5, 2019 at 16:47

Quang Hoang

151k11 gold badges64 silver badges86 bronze badges

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

1 Comment

Eug_Zazou Over a year ago

Thanks for your quick response! I used for my time series and it worked perfectly, even if if the dates that skip weekends.

Collectives™ on Stack Overflow

Shifting rows with multiindex Pandas

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related