Pandas: Adding a row to each group if missing values from a list [duplicate]

Question

I hope you're doing okay. I've been trying to think how to solve the next problem, but I can't find a way to do it. Can you guys give me a hand, please?

I have a dataframe with 4 columns, I want to add the remaining rows per group to have 3 Calendar Weeks, I want the new rows to keep the same value of ID of the group and display a NaN value for the Price and Attribute columns.

import pandas as pd
import numpy as np

input = {'ID':['ITEM1', 'ITEM2', 'ITEM1', 'ITEM4', 'ITEM2', 'ITEM3', 'ITEM4', 'ITEM4'], 
        'Price':['11', '12', '11', '14', '12', '13', '14', '14' ],
        'Attribute': ['A', 'B', 'A', 'D', 'B', 'C', 'D', 'D' ],
        'Calendar Week':['1', '2', '2', '1', '3', '1', '3', '2']
        }

df = pd.DataFrame(input)

df = df.sort_values(['ID', 'Calendar Week'], ascending = True).reset_index().drop(columns = 'index')

df =

ID	Price	Attribute	Calendar Week
ITEM1	11	A	1
ITEM1	11	A	2
ITEM2	12	B	2
ITEM2	12	B	3
ITEM3	13	C	1
ITEM4	14	D	1
ITEM4	14	D	2
ITEM4	14	D	3

Expected output:

ID	Price	Attribute	Calendar Week
ITEM1	11	A	1
ITEM1	11	A	2
ITEM1	NaN	NaN	3
ITEM2	NaN	NaN	1
ITEM2	12	B	2
ITEM2	12	B	3
ITEM3	13	C	1
ITEM3	NaN	NaN	2
ITEM3	NaN	NaN	3
ITEM4	14	D	1
ITEM4	14	D	2
ITEM4	14	D	3

Answer 1

(df.set_index(["ID", "Calendar Week"])
   .reindex(pd.MultiIndex.from_product([df["ID"].unique(), ["1", "2", "3"]], 
                                       names=["ID", "Calendar Week"]))
   .reset_index()[df.columns])

• you can move ID & Calendar Week to index part
• then reindex with the every possibility of ID versus Calendar Week generated with a product
• then move them back to columns and restore the original column order

to get

       ID Price Attribute Calendar Week
0   ITEM1    11         A             1
1   ITEM1    11         A             2
2   ITEM1   NaN       NaN             3
3   ITEM2   NaN       NaN             1
4   ITEM2    12         B             2
5   ITEM2    12         B             3
6   ITEM3    13         C             1
7   ITEM3   NaN       NaN             2
8   ITEM3   NaN       NaN             3
9   ITEM4    14         D             1
10  ITEM4    14         D             2
11  ITEM4    14         D             3