Reputation: 1960
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
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
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
Reputation: 6483
you could try with np.where
:
df['prev_val']=np.where(df['idx']!=0, df.val.shift(),np.nan)
Upvotes: 1
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