Shifting values in pandas by another column (python)

Question

Good time a day. I have a data and i have to shift column "VALUES" by one where column 'Year' is equal 2008 in Pandas.

A total result is look like column "shifted_value". I will bless you if you could help me with that case.

An example of data you can download by that link: https://docs.google.com/spreadsheets/d/1LpqWPJjaiCT_GAJmEMPq-GCYg2qD-AJz/edit?usp=sharing&ouid=104703556348297089571&rtpof=true&sd=true

Thanks a lot.

Andrej Kesely · Accepted Answer · 2021-09-12 14:03:21Z

2

Try:

df["shifted_value"] = (
    df.groupby(df.Year.eq(2008).cumsum())["VALUES"].shift().fillna("")
)
print(df)

Prints:

    Year  AREAS   VALUES shifted_value
0   2008  AREA1      123              
1   2009  AREA1      456           123
2   2010  AREA1     5678           456
3   2011  AREA1      890          5678
4   2012  AREA1   3667,5           890
5   2013  AREA1   4419,8        3667,5
6   2014  AREA1   5172,1        4419,8
7   2015  AREA1   5924,4        5172,1
8   2016  AREA1   6676,7        5924,4
9   2017  AREA1     7429        6676,7
10  2018  AREA1   8181,3          7429
11  2008  AREA2   8933,6              
12  2009  AREA2   9685,9        8933,6
13  2010  AREA2  10438,2        9685,9
14  2011  AREA2  11190,5       10438,2
15  2012  AREA2  11942,8       11190,5
16  2013  AREA2  12695,1       11942,8
17  2014  AREA2  13447,4       12695,1
18  2015  AREA2  14199,7       13447,4
19  2016  AREA2    14952       14199,7
20  2017  AREA2  15704,3         14952
21  2018  AREA2  16456,6       15704,3

answered Sep 12, 2021 at 14:03

Andrej Kesely

196k15 gold badges60 silver badges105 bronze badges

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

1 Comment

Corralien Over a year ago

I know this is the expected output but if you fill with "" the dtype of the column will be object... At least, let it to NaN to keep numeric data or fill with 0.

Corralien · Accepted Answer · 2021-09-12 14:07:56Z

It seems you want to group by AREAS and shift VALUES:

df['shifted_value'] = df.groupby('AREAS')['VALUES'].shift()

Output:

>>> df
    Year  AREAS   VALUES shifted_value
0   2008  AREA1      123           NaN
1   2009  AREA1      456           123
2   2010  AREA1     5678           456
3   2011  AREA1      890          5678
4   2012  AREA1   3667,5           890
5   2013  AREA1   4419,8        3667,5
6   2014  AREA1   5172,1        4419,8
7   2015  AREA1   5924,4        5172,1
8   2016  AREA1   6676,7        5924,4
9   2017  AREA1     7429        6676,7
10  2018  AREA1   8181,3          7429
11  2008  AREA2   8933,6           NaN
12  2009  AREA2   9685,9        8933,6
13  2010  AREA2  10438,2        9685,9
14  2011  AREA2  11190,5       10438,2
15  2012  AREA2  11942,8       11190,5
16  2013  AREA2  12695,1       11942,8
17  2014  AREA2  13447,4       12695,1
18  2015  AREA2  14199,7       13447,4
19  2016  AREA2    14952       14199,7
20  2017  AREA2  15704,3         14952
21  2018  AREA2  16456,6       15704,3

Collectives™ on Stack Overflow

Shifting values in pandas by another column (python)

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related