So I have a 2000 row python DataFrame where I have some data: It looks something like this:
| Index | Date | Product | Data1 | Data2 |
|---|---|---|---|---|
| 0 | 2021-11-01 | A | 5 | 8 |
| 1 | 2021-11-01 | C | 2 | 0 |
| 2 | 2021-11-01 | D | 3 | 0 |
| 3 | 2021-11-02 | A | 5 | 3 |
| 4 | 2021-11-02 | B | 6 | 4 |
| 5 | 2021-11-03 | A | 10 | 8 |
| 6 | 2021-11-03 | B | 1 | 5 |
| 7 | 2021-11-03 | C | 3 | 8 |
| 8 | 2021-11-03 | D | 0 | 5 |
| 9 | 2021-11-04 | A | 2 | 9 |
The data has no rows when both "Data1" and "Data2" are 0. What I want to do is kind of "fill in the gaps" so the dataframe has individual rows for every Data and Product Name pairing, something like this:
| Index | Data | Product | Data1 | Data2 |
|---|---|---|---|---|
| 0 | 2021-11-01 | A | 5 | 8 |
| 1 | 2021-11-01 | B | 0 | 0 |
| 2 | 2021-11-01 | C | 2 | 0 |
| 3 | 2021-11-01 | D | 3 | 0 |
| 4 | 2021-11-02 | A | 5 | 3 |
| 5 | 2021-11-02 | B | 6 | 4 |
| 6 | 2021-11-02 | C | 0 | 0 |
| 7 | 2021-11-02 | D | 0 | 0 |
| 8 | 2021-11-03 | A | 10 | 8 |
| 9 | 2021-11-03 | B | 1 | 5 |
| 10 | 2021-11-03 | C | 3 | 8 |
| 11 | 2021-11-03 | D | 0 | 5 |
| 12 | 2021-11-04 | A | 2 | 9 |
| 13 | 2021-11-04 | B | 0 | 0 |
| 14 | 2021-11-04 | C | 0 | 0 |
| 15 | 2021-11-04 | D | 0 | 0 |
I was thinking of doing it in a for loop, but there might be a way to avoid this. Anyone has any ideas of a more elegant way to do this?
CodePudding user response：
As Christophe mentioned, you can create a time series (or all unique values of your Date column) and cross join it with all (unique) products. Then you can merge it with your original dataframe and fill na with 0:
products = df[['Product']].drop_duplicates().sort_values('Product')
dates = df[['Date']].drop_duplicates()
dates.merge(products, how='cross').merge(df, on=['Date','Product'], how='left').fillna(0)
For dates you can also use date_range, if there are missing dates as well:
pd.date_range(df.Date.min(), df.Date.max())