FIll in python Dataform with "empty" lines, where I have no data

user_17913412 0
Viewed 24

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?

dataframe python
1 Answers

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())
user_71944740
Related