Pandas - Insert Blank Row For Each Group In Pandas

I have a dataframe import pandas as pd import numpy as np df1=pd.DataFrame({'group':[1,1,2,2,2], 'value':[2,3,np.nan,5,4]}) df1 group value 0 1 2 1 1

Solution 1:

concat with append

s = df1.groupby('group')
out = pd.concat([i.append({'value': np.nan}, ignore_index=True) for _, i in s])
out.group = out.group.ffill().astype(int)

apply with append

df1.groupby('group').apply(
    lambda d: d.append({'group': d.name}, ignore_index=True).astype({'group': int})
).reset_index(drop=True)

Both produce:

   group  value
012.0113.021NaN32NaN425.0524.062NaN

---

This solution brought to you by your local @piRSquared

Solution 2:

Can also just groupby+apply, a one-liner

df.groupby('group').apply(lambda gr: gr.append(gr.tail(1).assign(value=np.nan))).reset_index(drop=True)

or to be explicit

g = df.groupby('group')
def f(gr):
    n = gr.tail(1).copy()
    n.value = np.nan
    return gr.append(n)
g.apply(f).reset_index(drop=True)


    groupvalue012.0113.021       NaN
32       NaN
425.0524.062       NaN

Solution 3:

My version of concat

ii = dict(ignore_index=True)
pd.concat([
    d.append({'group': n}, **ii) for n, d in df1.groupby('group')
], **ii).astype({'group': int})

   group  value
012.0113.021    NaN
32    NaN
425.0524.062    NaN

Solution 4:

I wanted to get a little creative:

(pd.concat([df1, 
            df1.groupby('group')['value'].apply(lambda x: x.shift(-1).iloc[-1]).reset_index()])
    .sort_values('group')
    .reset_index(drop=True))

Output:

   group  value
012.0113.021NaN32NaN425.0524.062NaN