Cranjis
Cranjis

Reputation: 1960

dataframe take value of previous row

I have a dataframe that looks as follows:

idx  prev_val  val  type
 0      nan     8    a
 1      nan     9    a
 2      nan     7    a
 0      nan     3    b
 1      nan     1    b
 0      nan     2    c
 1      nan     7    c
 2      nan     5    c
 3      nan     4    c

I want that, per type, for each row that idx > 0, the value of prev_val will be taken from val of the previous row. So, the new dataframe will be:

idx  prev_val  val  type
 0      nan     8    a
 1       8      9    a
 2       9      7    a
 0      nan     3    b
 1       3      1    b
 0      nan     2    c
 1       2      7    c
 2       7      5    c
 3       5      4    c

What is the best way to do this?

Upvotes: 0

Views: 82

Answers (4)

ScootCork
ScootCork

Reputation: 3686

You can do so using groupby and shift

df['prev_val'] = df.groupby('type')['val'].shift()

0   0      NaN   8    a
1   1        8   9    a
2   2        9   7    a
3   0      NaN   3    b
4   1        3   1    b
5   0      NaN   2    c
6   1        2   7    c
7   2        7   5    c
8   3        5   4    c

Note as @jezrael mentioned this solution works if each group starts with idx = 0.

Upvotes: 0

Shubham Sharma
Shubham Sharma

Reputation: 71707

Use DataFrame.groupby on type + shift the column val, then assign the value to the column prev_val using boolean indexing:

df.loc[df['idx'].gt(0), 'prev_val'] = df.groupby('type')['val'].shift()

Result:

   idx  prev_val  val type
0    0       NaN    8    a
1    1       8.0    9    a
2    2       9.0    7    a
3    0       NaN    3    b
4    1       3.0    1    b
5    0       NaN    2    c
6    1       2.0    7    c
7    2       7.0    5    c
8    3       5.0    4    c

Upvotes: 1

MrNobody33
MrNobody33

Reputation: 6483

you could try with np.where:

df['prev_val']=np.where(df['idx']!=0, df.val.shift(),np.nan)

Upvotes: 1

Martin Stagl
Martin Stagl

Reputation: 56

Best would be to use Pandas fillna function like this:

https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.fillna.html

df.fillna(method='ffill')

Upvotes: 0

Related Questions