Add row before first and after last position of every group in pandas

user_6803114 0
Viewed 1031

i have a dataframe

value    id
100       1
200       1
300       1
500       2
600       2
700       3

i want to group by id and add row before 1st row and after last row of every group such that my dataframe looks: i am adding row with value 0

value    id
0         1
100       1
200       1
300       1
0         1
0         2
500       2
600       2
0         2
0         3
700       3
0         3

Now for every group of id, i want to add sequence column such that:

value    id    sequence
0         1    1
100       1    2
200       1    3
300       1    4
0         1    5
0         2    1
500       2    2
600       2    3
0         2    4
0         3    1
700       3    2
0         3    3

the last part is easy but i am looking for how to add rows before and after every group?

pandas dataframe python
4 Answers

If like me you are a fan of concatenating dataframes operations, check out this solution:

def add(df, column, value):
    df[column] += value
    return df

(df
 .groupby('id')
 ["value"]
 .apply(lambda x: pd.Series([0] + x.tolist() + [0]))
 .reset_index()
 .rename({"level_1": "sequence"}, axis=1)
 .pipe(add, column="sequence", value=1)
)

This is also faster than the two other top-ranked answers.

edd313

%%timeit
(df
 .groupby('id')
 ["value"]
 .apply(lambda x: pd.Series([0] + x.tolist() + [0]))
 .reset_index()
 .rename({"level_1": "sequence"}, axis=1)
 .pipe(add, column="sequence", value=1)
)

4.28 ms ± 393 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)

jezrael

%%timeit
df2 = df.groupby('id').apply(f).reset_index(drop=True)

df2['seq'] = df2.groupby('id').cumcount() + 1

4.42 ms ± 47.2 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)

cs95

%%timeit
df2 = df.groupby('id')['value']\
        .apply(lambda x: pd.Series([0] + x.tolist() + [0]))\
        .reset_index().drop('level_1', 1)

df2['sequence'] = df2.groupby('id').cumcount() + 1

5.58 ms ± 159 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)
user_120407510
Related