Adding column in pandas based on values from other columns with conditions

Question

I have a dataframe with information about sales of some products (unit):

    unit    year    month   price
0   1       2018    6       100
1   1       2013    4       70
2   2       2015    10      80
3   2       2015    2       110
4   3       2017    4       120
5   3       2002    6       90
6   4       2016    1       55

and I would like to add, for each sale, columns with information about the previous sales and NaN if there is no previous sale.

    unit    year    month   price   prev_price  prev_year   prev_month
0   1       2018    6       100      70.0        2013.0      4.0
1   1       2013    4        70      NaN         NaN         NaN
2   2       2015    10       80      110.0       2015.0      2.0
3   2       2015    2       110      NaN         NaN         NaN
4   3       2017    4       120      90.0        2002.0      6.0
5   3       2002    6        90      NaN         NaN         NaN
6   4       2016    1        55      NaN         NaN         NaN

For the moment I am doing some grouping on the unit, keeping those that have several rows, then extracting the information for these units that are associated with the minimal date. Then joining this table with my original table keeping only the rows that have a different date in the 2 tables that have been merged. I feel like there is a much simple way to do this but I am not sure how.

jezrael · Accepted Answer · 2019-02-01 14:35:32Z

1

Use DataFrameGroupBy.shift with add_prefix and join to append new DataFrame to original:

#if real data are not sorted
#df = df.sort_values(['unit','year','month'], ascending=[True, False, False])

df = df.join(df.groupby('unit', sort=False).shift(-1).add_prefix('prev_'))
print (df)
   unit  year  month  price  prev_year  prev_month  prev_price
0     1  2018      6    100     2013.0         4.0        70.0
1     1  2013      4     70        NaN         NaN         NaN
2     2  2015     10     80     2015.0         2.0       110.0
3     2  2015      2    110        NaN         NaN         NaN
4     3  2017      4    120     2002.0         6.0        90.0
5     3  2002      6     90        NaN         NaN         NaN
6     4  2016      1     55        NaN         NaN         NaN

edited Feb 1, 2019 at 14:35

answered Feb 1, 2019 at 14:05

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

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

2 Comments

Wendy Over a year ago

Thank you but in the case where 2015/02 is on row 2 and 2015/10 on row 3 this doesn't work as the groupby will keep the value for 2015/10 which is not the latest sale and in my original data sales are in any order. I know I could simply order the dataset before but I would like to know if a solution that does not alter the order of my dataset exists.

jezrael Over a year ago

@Wendy - I think you need ordered data, else maybe solution exist, but complicated and slow.

Collectives™ on Stack Overflow

Adding column in pandas based on values from other columns with conditions

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related