Python shift() from same column like in Excel

Question

I want to create this table in python:

This is easy to solve in Excel,

but I don't know, how can I do this in pyton: First target row is "1", then:

target = if ['x1']=1 then ['target']=1 else ['target']=['target'].shift(1)+1

I tried this code, but doesn't worked:

import pandas as pd
df=pd.read_excel('/x1.xlsx')
df.loc[df.index == 0, 'target'] = 1
df.loc[df['x1']==1, 'target'] = 1
df.loc[df['x1'] != 1, 'target'] = df['target'].shift(1)+1

Anyone know how to solve this? Thanks in advance!

x1: [1,1,23,32,1,2,2,4,1] target: [1,1,2,3,1,2,3,4,1]

Bela
– Bela

2021-01-18 22:32:44 +00:00
Commented Jan 18, 2021 at 22:32 — Bela
– Bela, Commented Jan 18, 2021 at 22:32
Yes, you can do shift(1) or shift(-1)

Joe Ferndz
– Joe Ferndz

2021-01-18 22:37:01 +00:00
Commented Jan 18, 2021 at 22:37 — Joe Ferndz
– Joe Ferndz, Commented Jan 18, 2021 at 22:37

Chris · Accepted Answer · 2021-01-18 22:45:21Z

1

You can get groups with cumsum and number with cumcount

import pandas as pd
df = pd.DataFrame({'x1': {0: 1, 1: 1, 2: 23, 3: 32, 4: 1, 5: 2, 6: 2, 7: 4, 8: 1}})
df['target'] = df.groupby(df['x1'].eq(1).cumsum()).cumcount()+1

Output

   x1  target
0   1       1
1   1       1
2  23       2
3  32       3
4   1       1
5   2       2
6   2       3
7   4       4
8   1       1

It can be easier to look at the groupby part separately. It creates a number to group on, where every time x1 is equal to 1 the number increases. Once we group on this, we can take the cumulative count of the number of records in the group and add 1 to account for starting at zero.

df['x1'].eq(1).cumsum()

0    1
1    2
2    2
3    2
4    3
5    3
6    3
7    3
8    4

answered Jan 18, 2021 at 22:45

Chris

16.3k3 gold badges26 silver badges41 bronze badges

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

1 Comment

Bela Over a year ago

Thank you Chris! This solution is work perfectly, but my rally problem is more complicated: stackoverflow.com/questions/65788112/… Could you help me please?

Collectives™ on Stack Overflow

Python shift() from same column like in Excel

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related