Create new columns by comparing the current row's values and previous in Pandas

user_8410477 0
Viewed 37

Given a dummy dataset df as follow:

   year   v1   v2
0  2017  0.3  0.1
1  2018  0.1  0.1
2  2019 -0.2  0.5
3  2020  NaN -0.3
4  2021  0.8  0.0

or:

[{'year': 2017, 'v1': 0.3, 'v2': 0.1},
 {'year': 2018, 'v1': 0.1, 'v2': 0.1},
 {'year': 2019, 'v1': -0.2, 'v2': 0.5},
 {'year': 2020, 'v1': nan, 'v2': -0.3},
 {'year': 2021, 'v1': 0.8, 'v2': 0.0}]

I need to create two more columns trend_v1 and trend_v2 based on v1 and v2 respectively.

The logic to create new columns is this: for each column, if its current value is greater than the previous, the trend value is increase, if its current value is less than the previous, the trend value is decrease, if its current value is equal to the previous, the trend value is equal, if the current or previous value is NaN, the trend also is NaN.

   year   v1   v2  trend_v1  trend_v2
0  2017  0.3  0.1       NaN       NaN
1  2018  0.1  0.1  decrease     equal
2  2019 -0.2  0.5  decrease  increase
3  2020  NaN -0.3       NaN  decrease
4  2021  0.8  0.0       NaN  increase

How could I achieve that in Pandas? Thanks for your help at advance.

pandas dataframe python-3.x
1 Answers

You can specify columns for test trend by compare shifted values with filtered missing values:

cols = ['v1','v2']
arr = np.where(df[cols] < df[cols].shift(),'decrease',
      np.where(df[cols] > df[cols].shift(),'increase',
      np.where(df[cols].isna() | df[cols].shift().isna(), None, 'equal')))

df = df.join(pd.DataFrame(arr, columns=cols, index=df.index).add_prefix('trend_'))
print (df)
   year   v1   v2  trend_v1  trend_v2
0  2017  0.3  0.1      None      None
1  2018  0.1  0.1  decrease     equal
2  2019 -0.2  0.5  decrease  increase
3  2020  NaN -0.3      None  decrease
4  2021  0.8  0.0      None  increase

Or:

cols = ['v1','v2']

m1 = df[cols] < df[cols].shift()
m2 = df[cols] > df[cols].shift()
m3 = df[cols].isna() | df[cols].shift().isna()

arr = np.select([m1, m2, m3],['decrease','increase', None], default='equal')

df = df.join(pd.DataFrame(arr, columns=cols, index=df.index).add_prefix('trend_'))

EDIT:

Nice improvement is change m3 like mentioned in comments:

cols = ['v1','v2']

m1 = df[cols] < df[cols].shift()
m2 = df[cols] > df[cols].shift()
m3 = df[cols] == df[cols].shift()

arr = np.select([m1, m2, m3],['decrease','increase', 'equal'], default=None)
user_29010020
Related