CODEWITHSUNDEEP
pythonpandas
Wendy
Wendy

Reputation: 871

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

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.

Upvotes: 1

Views: 43

Answers (1)

jezrael
jezrael

Reputation: 862511

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

Upvotes: 1

Related Questions