Append rows to groups in pandas

Question

I'm trying to append a number of NaN rows to each group in a pandas dataframe. Essentially I want to pad each group to be 5 rows long. Ordering is important. I have:

    Rank id
0   1  a
1   2  a
2   3  a
3   4  a
4   5  a
5   1  c
6   2  c
7   1  e
8   2  e
9   3  e

I want:

    Rank id
0   1    a
1   2    a
2   3    a
3   4    a
4   5    a
5   1    c
6   2    c
7   NaN  c
8   NaN  c
9   NaN  c
10  1    e
11  2    e
12  3    e
13  NaN  e
14  NaN  e

Answer 1

Using pd.crosstab:

df = pd.crosstab(df.Rank, df.ID).iloc[:5].unstack().reset_index()
df.loc[(df[0]==0),'Rank'] = np.nan
del df[0]

Output:

   ID  Rank
0   a   1.0
1   a   2.0
2   a   3.0
3   a   4.0
4   a   5.0
5   c   1.0
6   c   2.0
7   c   NaN
8   c   NaN
9   c   NaN
10  e   1.0
11  e   2.0
12  e   3.0
13  e   NaN
14  e   NaN

Another approach, assuming the maximum group size in df is exactly 5.

In [251]: df.groupby('ID').Rank.apply(np.array).apply(pd.Series).stack(dropna=False)
Out[251]: 
ID
a   0    1.0
    1    2.0
    2    3.0
    3    4.0
    4    5.0
c   0    1.0
    1    2.0
    2    NaN
    3    NaN
    4    NaN
e   0    1.0
    1    2.0
    2    3.0
    3    NaN
    4    NaN
dtype: float64

Full explanation:

import pandas as pd
import numpy as np

df = pd.read_csv(pd.compat.StringIO("""Rank ID
0   1  a
1   2  a
2   3  a
3   4  a
4   5  a
6   1  c
7   2  c
8   1  e
9   2  e
10  3  e"""), sep=r' +')

df = pd.crosstab(df.Rank, df.ID).iloc[:5].T.stack().reset_index()
df.loc[(df[0]==0),'Rank'] = np.nan
del df[0]

# pd.crosstab(df.Rank, df.ID) produces:

# ID    a  c  e
# Rank
# 1.0   1  1  1
# 2.0   1  1  1
# 3.0   1  0  1
# 4.0   1  0  0
# 5.0   1  0  0

# applying .T.stack().reset_index() yields:

   # ID  Rank  0
# 0   a   1.0  1
# 1   a   2.0  1
# 2   a   3.0  1
# 3   a   4.0  1
# 4   a   5.0  1
# 5   c   1.0  1
# 6   c   2.0  1
# 7   c   3.0  0
# 8   c   4.0  0
# 9   c   5.0  0
# 10  e   1.0  1
# 11  e   2.0  1
# 12  e   3.0  1
# 13  e   4.0  0
# 14  e   5.0  0

# finally, use df[0] to filter df['Rank']

Answer 2

concat and reindex

This solution does not consider the values in the Rank column and only adds more rows if more are needed.

pd.concat([
    d.reset_index(drop=True).reindex(range(5)).assign(id=n)
    for n, d in df.groupby('id')
], ignore_index=True)

    Rank id
0    1.0  a
1    2.0  a
2    3.0  a
3    4.0  a
4    5.0  a
5    1.0  c
6    2.0  c
7    NaN  c
8    NaN  c
9    NaN  c
10   1.0  e
11   2.0  e
12   3.0  e
13   NaN  e
14   NaN  e

---

Same answer phrased a bit differently

f = lambda t: t[1].reset_index(drop=True).reindex(range(5)).assign(id=t[0])
pd.concat(map(f, df.groupby('id')), ignore_index=True)

---

factorize

This solution produces the Cartesian product of unique values from id and Rank

i, r = df.id.factorize()
j, c = df.Rank.factorize()
b = np.empty((r.size, c.size))
b.fill(np.nan)
b[i, j] = df.Rank.values

pd.DataFrame(dict(Rank=b.ravel(), id=r.repeat(c.size)))

    Rank id
0    1.0  a
1    2.0  a
2    3.0  a
3    4.0  a
4    5.0  a
5    1.0  c
6    2.0  c
7    NaN  c
8    NaN  c
9    NaN  c
10   1.0  e
11   2.0  e
12   3.0  e
13   NaN  e
14   NaN  e