2

I have a similar question to this one.

I have a dataframe with an ID column and a counter column for this idea which looks like this:

ID counter valueA   valueB
A   1       10        1
A   2       10        1
A   3       5         1
B   1       1         2
B   2       1         3
B   3       2         4
B   4       3         4
...

How can i count the row changes per column with a 1 in the dataframe, so that the dataframe looks like:

ID counter valueA   valueB
A   1       0         0
A   2       0         0
A   3       1         0
B   1       0         0
B   2       0         1
B   3       1         1
B   4       1         1
...

So that everytime a value change in the columns (only if it is the same ID, the counter should not be marked) I got a marker with 1. Note that I have more value columns, this two are only an example.

PV8
  • 5,799
  • 7
  • 43
  • 87

1 Answers1

4

Use:

df = pd.DataFrame({'ID': ['A', 'A', 'A', 'B', 'B', 'B', 'B'], 'counter': [1, 2, 3, 1, 2, 3, 4], 'valueA': [10, 10, 5, 1, 1, 2, 3], 'valueB': [1, 1, 1, 2, 3, 4, 4]})

print (df)

c = ['valueA','valueB']
df[c] = df[c].ne(df[c].groupby(df['ID']).shift().bfill()).astype(int)
print (df)
  ID  counter  valueA  valueB
0  A        1       0       0
1  A        2       0       0
2  A        3       1       0
3  B        1       0       0
4  B        2       0       1
5  B        3       1       1
6  B        4       1       0

For counter per groups I try this solution, but still different output:

df[c] = df[c].ne(df[c].groupby(df['ID']).shift().bfill()).groupby(df['ID']).cumsum()
print (df)
  ID  counter  valueA  valueB
0  A        1       0       0
1  A        2       0       0
2  A        3       1       0
3  B        1       0       0
4  B        2       0       1
5  B        3       1       2
6  B        4       2       2

Note: For a success the newest pandas version should be installed

PV8
  • 5,799
  • 7
  • 43
  • 87
jezrael
  • 822,522
  • 95
  • 1,334
  • 1,252