I have created a pandas dataframe using the following code:

ds = {"col1":[1,1,1,1,2,3,4,4,4,4,4,5,4,4,5,6]}

df = pd.DataFrame(data=ds)

The dataframe looks like this:

print(df)

    col1
0      1
1      1
2      1
3      1
4      2
5      3
6      4
7      4
8      4
9      4
10     4
11     5
12     4
13     4
14     5
15     6

I need to calculate a new dataframe column (called col2) which contains the next value from col1 which is different than the current value.

Example.

Let’s take a look at row 0.
The value in col1 is 1.
What is the next value in col1 that is different than 1? It is the value 2 (at row 4).

Let’s now take a look at row 1.
The value in col1 is 1.
What is the next value in col1 that is different than 1? It is the value 2 (at row 4).

And so on.

The resulting dataframe would look like this:

What is the python code to produce such dataframe?

>Solution :

You can use a mask with where, shift the masked values up, and bfill:

# identify the first value of each stretch
m = df['col1'].ne(df['col1'].shift())
# mask the other, shift up, backfill
df['col2'] = df['col1'].where(m).shift(-1).bfill()

Output:

    col1  col2
0      1   2.0
1      1   2.0
2      1   2.0
3      1   2.0
4      2   3.0
5      3   4.0
6      4   5.0
7      4   5.0
8      4   5.0
9      4   5.0
10     4   5.0
11     5   4.0
12     4   5.0
13     4   5.0
14     5   6.0
15     6   NaN

Intermediates to understand the logic;

    col1  col2      m  where(m)  shift(-1)
0      1   2.0   True       1.0        NaN
1      1   2.0  False       NaN        NaN
2      1   2.0  False       NaN        NaN
3      1   2.0  False       NaN        2.0
4      2   3.0   True       2.0        3.0
5      3   4.0   True       3.0        4.0
6      4   5.0   True       4.0        NaN
7      4   5.0  False       NaN        NaN
8      4   5.0  False       NaN        NaN
9      4   5.0  False       NaN        NaN
10     4   5.0  False       NaN        5.0
11     5   4.0   True       5.0        4.0
12     4   5.0   True       4.0        NaN
13     4   5.0  False       NaN        5.0
14     5   6.0   True       5.0        6.0
15     6   NaN   True       6.0        NaN