CODEWITHSUNDEEP
pythonpandas
Mike
Mike

Reputation: 1844

Pandas: Creating Column From Previous TimeSeries Value

I have a pandas dataframe that looks something like this:

    user    date        value
    1       01-2000     5
    1       02-2000     6
    1       03-2000     7
    1       04-2000     8
    2       01-2000     3
    2       02-2000     4
    2       03-2000     5
    2       04-2000     6
    3       01-2000     10
    3       02-2000     11
    3       03-2000     12
    3       04-2000     13

I'd like to create a new column with the value for each user for the previous month, so it looks something like this:

    user    date        value    new_col
    1       01-2000     5        NaN
    1       02-2000     6        5
    1       03-2000     7        6
    1       04-2000     8        7
    2       01-2000     3        NaN
    2       02-2000     4        3
    2       03-2000     5        4
    2       04-2000     6        5
    3       01-2000     10       NaN
    3       02-2000     11       10
    3       03-2000     12       11
    3       04-2000     13       12

Please can someone advise how best to do this? Thanks.

Upvotes: 1

Views: 135

Answers (1)

unutbu
unutbu

Reputation: 879311

Use shift():

import pandas as pd
import io

text = '''\
    user    date        value
    1       01-2000     5
    1       02-2000     6
    1       03-2000     7
    1       04-2000     8
    2       01-2000     3
    2       02-2000     4
    2       03-2000     5
    2       04-2000     6
    3       01-2000     10
    3       02-2000     11
    3       03-2000     12
    3       04-2000     13'''

df = pd.read_table(io.BytesIO(text), sep='\s+')
df['new_col'] = df.groupby('user')['value'].apply(lambda grp: grp.shift())
print(df)

yields

    user     date  value  new_col
0      1  01-2000      5      NaN
1      1  02-2000      6        5
2      1  03-2000      7        6
3      1  04-2000      8        7
4      2  01-2000      3      NaN
5      2  02-2000      4        3
6      2  03-2000      5        4
7      2  04-2000      6        5
8      3  01-2000     10      NaN
9      3  02-2000     11       10
10     3  03-2000     12       11
11     3  04-2000     13       12

Upvotes: 3

Related Questions