Add missing rows based on column

Question

I have given the following df

df = pd.DataFrame(data = {'day': [1, 1, 1, 2, 2, 3], 'pos': 2*[1, 14, 18], 'value': 2*[1, 2, 3]}    
df

    day pos value
0   1   1   1
1   1   14  2
2   1   18  3
3   2   1   1
4   2   14  2
5   3   18  3

and i want to fill in rows such that every day has every possible value of column 'pos'

desired result:

    day pos value
0   1   1   1.0
1   1   14  2.0
2   1   18  3.0
3   2   1   1.0
4   2   14  2.0
5   2   18  NaN
6   3   1   NaN
7   3   14  NaN
8   3   18  3.0

Proposition:

df.set_index('pos').reindex(pd.Index(3*[1,14,18])).reset_index()

yields:

ValueError: cannot reindex from a duplicate axis

Answer 1

Let's try pivot then stack:

df.pivot('day','pos','value').stack(dropna=False).reset_index(name='value')

Output:

   day  pos  value
0    1    1    1.0
1    1   14    2.0
2    1   18    3.0
3    2    1    1.0
4    2   14    2.0
5    2   18    NaN
6    3    1    NaN
7    3   14    NaN
8    3   18    3.0

---

Option 2: merge with MultiIndex:

df.merge(pd.DataFrame(index=pd.MultiIndex.from_product([df['day'].unique(), df['pos'].unique()])),
         left_on=['day','pos'], right_index=True, how='outer')

Output:

   day  pos  value
0    1    1    1.0
1    1   14    2.0
2    1   18    3.0
3    2    1    1.0
4    2   14    2.0
5    3   18    3.0
5    2   18    NaN
5    3    1    NaN
5    3   14    NaN