Add row before first and after last position of every group in pandas in 3+ columns dataframe

user_13032923 0
Viewed 558

Actually I want to do the same operation as Shubham R question but using a broader dataframe df:

id    value_1    value_2      value_3 ... 
1     x1         2011-01-01   shoe    ...
1     x2         2011-01-02   shoe    ... 
1     x3         2011-01-05   shoe    ...
2     x1         2011-01-02   hat     ...
2     x2         2011-01-03   hat     ...
2     x3         2011-01-07   hat     ...
3     x1         2011-01-06   glove   ...
3     x5         2011-01-10   glove   ...
...

df.shape = (1.000.000+, 12)

For every groupby('id') add a row before 1st row and after last row with value 'x0' and 'x-1' respectively into value_1 column and fill others with 1st and last row values respectively, like this:

id    value_1    value_2      value_3 ... 
1     x0          2011-01-01   shoe    ...
1     x1          2011-01-01   shoe    ...
1     x2          2011-01-02   shoe    ... 
1     x3          2011-01-05   shoe    ...
1     x-1         2011-01-05   shoe    ...
2     x0          2011-01-02   hat     ...
2     x1          2011-01-02   hat     ...
2     x2          2011-01-03   hat     ...
2     x3          2011-01-07   hat     ...
2     x-1         2011-01-07   hat     ...
3     x0          2011-01-06   glove   ...
3     x1          2011-01-06   glove   ...
3     x5          2011-01-10   glove   ...
3     x-1         2011-01-10   glove   ...
...

I did it this way but only works for this specific dataframe, I need the function to be reusable (all kind of dataframe's shapes), is there a broader way to do it without using for loop?

def add_0_first_last(x):
    last_index = len(x.values)-1
    c_start = np.array([[x.values[0][0], x.values[0][1], x.values[0][2], '0', x.values[0][4], x.values[0][5], x.values[0][6],...]])
    c_end = np.array([[x.values[last_index][0], x.values[last_index][1], x.values[last_index][2], '0', x.values[last_index][4], x.values[last_index][5], x.values[last_index][6],...]])
    data = np.concatenate([c_start, x.values, c_end])
    df = pd.DataFrame(data=data, columns=x.columns)
    return df

df.groupby('case_id').apply(add_0_first_last)
pandas dataframe numpy python
1 Answers

Here is a possible approach involving unstack() reindex and then stack back:

out = df.set_index(['id','value_1']).unstack()
re_idx = ['x0']  + out.columns.levels[1].tolist() + ['x-1']

idx = pd.MultiIndex.from_product([out.columns.levels[0], re_idx])
out = (out.reindex(idx,axis=1).groupby(level=0,axis=1).fillna(method='ffill').bfill(1)
          .stack().reindex(re_idx,level=1).reset_index())

print(out)

    id level_1     value_2 value_3
0    1      x0  2011-01-01    shoe
1    1      x1  2011-01-01    shoe
2    1      x2  2011-01-02    shoe
3    1      x3  2011-01-05    shoe
4    1      x5  2011-01-05    shoe
5    1     x-1  2011-01-05    shoe
6    2      x0  2011-01-02     hat
7    2      x1  2011-01-02     hat
8    2      x2  2011-01-03     hat
9    2      x3  2011-01-07     hat
10   2      x5  2011-01-07     hat
11   2     x-1  2011-01-07     hat
12   3      x0  2011-01-06   glove
13   3      x1  2011-01-06   glove
14   3      x2  2011-01-06   glove
15   3      x3  2011-01-06   glove
16   3      x5  2011-01-10   glove
17   3     x-1  2011-01-10   glove
user_98406370
Related