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

Question

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?

Answer 1

Not so easy:

def f(x):
    x = pd.DataFrame(np.concatenate([np.array([[0, x['id'].iat[0]]]), 
                                     x.values,
                                     np.array([[0, x['id'].iat[0]]])]), columns=x.columns)
    return (x)

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

df['seq'] = df.groupby('id').cumcount() + 1
print (df)
    value  id  seq
0       0   1    1
1     100   1    2
2     200   1    3
3     300   1    4
4       0   1    5
5       0   2    1
6     500   2    2
7     600   2    3
8       0   2    4
9       0   3    1
10    700   3    2
11      0   3    3

Answer 2

Let's try groupby with an apply.

df = df.groupby('id')['value']\
         .apply(lambda x: pd.Series([0] + x.tolist() + [0]))\
         .reset_index().drop('level_1', 1)
df 
    id  value
0    1      0
1    1    100
2    1    200
3    1    300
4    1      0
5    2      0
6    2    500
7    2    600
8    2      0
9    3      0
10   3    700
11   3      0

---

And now, use cumcount for the sequence.

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

In [228]: df
Out[228]: 
    id  value  sequence
0    1      0         1
1    1    100         2
2    1    200         3
3    1    300         4
4    1      0         5
5    2      0         1
6    2    500         2
7    2    600         3
8    2      0         4
9    3      0         1
10   3    700         2
11   3      0         3

Answer 3

data = [zip([k] * (len(group) + 2), [0] + group.values.tolist() + [0]) 
        for k, group in df.groupby('id')['value']]
df = pd.DataFrame([x for g in data for x in g], columns=['id', 'value'])
df.assign(sequence=df.groupby(['id'])['value'].transform(
    lambda group: range(1, group.count() + 1)))
>>> df
    id  value  sequence
0    1      0         1
1    1    100         2
2    1    200         3
3    1    300         4
4    1      0         5
5    2      0         1
6    2    500         2
7    2    600         3
8    2      0         4
9    3      0         1
10   3    700         2
11   3      0         3