shifting up the column basis groupby-CodePudding

Existing Dataframe :

Id       event      time_spent_in_sec
A         in               0
A         step_1           2.2
A         step_2           3
A         done             3
B          in              0
B         step_1           5
B         step_2           8
B         step_3           15
B         done             7

Expected Dataframe :

Id       event      time_spent_in_sec
A         in               2.2
A         step_1           3
A         step_2           3
A         done             0
B          in              5
B         step_1           8
B         step_2           15
B         step_3           7
B         done             0

I am looking to shift the value in a column time_spent_in_sec and fill last row of each unique Id by 0.

I tried using shift(1) but stuck with filling the last row with 0

CodePudding user response：

You can use .fillna() to fill it with the original first number:

df.time_spent_in_sec.shift(-1).fillna(df.time_spent_in_sec[0])

Or:

df.time_spent_in_sec.shift(-1, fill_value = df.time_spent_in_sec[0])

CodePudding user response：

You can use numpy.roll

df['time_spent_in_sec'] = np.roll(df['time_spent_in_sec'], -1)

CodePudding user response：

Other way to do it: Transforming into a list and shift if from there.

list_col = list(df["time_spent_in_sec"])
list_col.append(list_col.pop(0))
df["time_spent_in_sec"] = list_col.copy()