How can we transform a column previous value (greater, equal or smaller) to (1, 0, -1) on a pandas dataframe?

Question

Imagine we have this pandas dataframe:

TABLE_A

What transformation do I have to do from the table above to create a column (B column) like this?:

TABLE_B

Note: what I really want to do is create a new column B, with the coding of three values (-1,0 or 1) depending if in column A the just previous value is greater, smaller or equal.

Thank you!

Please include any relevant information [as text directly into your question](https://stackoverflow.com/editing-help), do not link or embed external images of source code or data. Images make it difficult to efficiently assist you as they cannot be copied and offer poor usability as they cannot be searched. See: [Why not upload images of code/errors when asking a question?](https://meta.stackoverflow.com/q/285551/15497888) — Henry Ecker, May 24 '21 at 16:17
Please include a _small_ subset of your data as a __copyable__ piece of code that can be used for testing as well as your expected output for the __provided__ data. See [MRE - Minimal, Reproducible, Example](https://stackoverflow.com/help/minimal-reproducible-example), and [How to make good reproducible pandas examples](https://stackoverflow.com/q/20109391/15497888). — Henry Ecker, May 24 '21 at 16:18

score 4 · Accepted Answer · answered May 24 '21 at 16:25

Use Series.diff() + numpy.sign():

df["B"] = np.sign(df["A"].diff())

Prints:

   Index   A    B
0      0  10  NaN
1      1  15  1.0
2      2  19  1.0
3      3  12 -1.0
4      4  15  1.0
5      5   8 -1.0
6      6   7 -1.0
7      7   7  0.0
8      8   7  0.0

score 2 · Answer 2 · answered May 24 '21 at 16:25

np.select + shift is an option:

import numpy as np
import pandas as pd

df = pd.DataFrame({'A': [10, 15, 19, 12, 15, 8, 7, 7, 7]})

s = df['A'].shift()

df['B'] = np.select(
    [df['A'].lt(s), df['A'].eq(s), df['A'].gt(s)],
    [-1, 0, 1],
    default=np.nan
)

print(df)

Also diff + clip:

import pandas as pd

df = pd.DataFrame({'A': [10, 15, 19, 12, 15, 8, 7, 7, 7]})

df['B'] = df['A'].diff().clip(lower=-1, upper=1)

print(df)

df:

score 1 · Answer 3 · answered May 24 '21 at 16:25

1

shifted = df['A'].shift()
df['B'] = np.where(shifted < (df['A']), 1, 
                   np.where(shifted > df['A'], -1, 0))
df.loc[0, 'B'] = np.nan # Fix the first line

answered May 24 '21 at 16:25

DYZ

55,249
10
64
93

How can we transform a column previous value (greater, equal or smaller) to (1, 0, -1) on a pandas dataframe?

3 Answers3