Create new Pandas column from existing column using previous date

Question

I have a dataset where I have a value for each month that looks like:

Date         Platform    Name   Value
1-31-2019    Pine        Dave   100
1-31-2019    Pine        Sam    300
1-31-2019    Cherry      Sam    200
2-28-2019    Pine        Dave   150
2-28-2019    Pine        Sam    200
2-28-2019    Cherry      Sam    250

I need to use the previous month's value to run calculations, and I'd like to add a column that is last month's value. So I'd like to have an end result that looks like

Date         Platform    Name   Value    Previous Value
1-31-2019    Pine        Dave   100      NaN
1-31-2019    Pine        Sam    300      NaN
1-31-2019    Cherry      Sam    200      NaN
2-28-2019    Pine        Dave   150      100
2-28-2019    Pine        Sam    200      300
2-28-2019    Cherry      Sam    250      200

I have the date columns set to a datetime data type. Is it possible to do a date calculation to create a new columns?

Looks like df.groupby(['Platform','Name']).Value.shift() would do, given the above comments. — Quang Hoang
– Quang Hoang, Commented Jun 27, 2019 at 16:14

anky · Accepted Answer · 2019-06-27 16:46:19Z

1

IIUC, you can do something like:

d=df.set_index([df.Date.dt.month,'Platform','Name'])['Value'].to_dict()
df['Previous_Value']=(df.set_index([df.Date.dt.month-1,'Platform','Name']).
                      index.to_series().map(d).reset_index(drop=True))
print(df)

        Date Platform  Name  Value  Previous_Value
0 2019-01-31     Pine  Dave    100             NaN
1 2019-01-31     Pine   Sam    300             NaN
2 2019-01-31   Cherry   Sam    200             NaN
3 2019-02-28     Pine  Dave    150           100.0
4 2019-02-28     Pine   Sam    200           300.0
5 2019-02-28   Cherry   Sam    250           200.0

answered Jun 27, 2019 at 16:46

anky

75.3k11 gold badges46 silver badges76 bronze badges

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

Collectives™ on Stack Overflow

Create new Pandas column from existing column using previous date

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related