Pandas Create Missing Rows

Question

I have a df that looks like this, with data from Week 1, 2 and 3:

Product   Week   
A         1      
A         3      
B         1   
B         2

I wish to create missing Week rows automatically to get this output:

Product   Week   
A         1 
A         2     
A         3      
B         1   
B         2
B         3

Should be straightforward enough but somehow I cannot get this. Thank you.

Answer 1

Here's one way:

df = df.groupby('Product').apply(lambda x: x.set_index('Week').reindex(df.Week.unique())).reset_index(0 ,drop =True).ffill()

If you need all the values between min/max:

import numpy as np

df = (
    df.groupby('Product')
    .apply(lambda x:
           x.set_index('Week')
           .reindex(np.arange(df.Week.min(), df.Week.max() + 1)))
    .reset_index(0, drop=True)
    .ffill()
).reset_index()

Alternative:

df = df.reset_index().pivot('Product','Week', 'index').stack(dropna=False).ffill().reset_index().drop(0, 1)

OUTPUT:

   Week Product
0     1       A
1     2       A
2     3       A
3     1       B
4     2       B
5     3       B