Explode rows with predefined lists while keeping the values for existing rows

user_15996118 0
Viewed 78

I'm struggling with exploding the rows with predefined lists while keeping the values for existing rows.

I have a dataframe like this:

df = pd.DataFrame({'id': ['01','01','02'],
                    'color': ['red', 'yellow','yellow'],
                    'wave': ['1', '2', '2'],
                    'count':[1,2,1]})
print(df)
[output]
   id   color wave  count
0  01     red    1      1
1  01  yellow    2      2
2  02  yellow    2      1

I have two lists:

ls_color = ['yellow', 'red', 'blue']
ls_wave = ['1','2']

My expected dataframe:

    id   color wave  count
0   01  yellow    1      0
1   01  yellow    2      2
2   01     red    1      1
3   01     red    2      0
4   01    blue    1      0
5   01    blue    2      0
6   02  yellow    1      0
7   02  yellow    2      1
8   02     red    1      0
9   02     red    2      0
10  02    blue    1      0
11  02    blue    2      0
pandas explode python
2 Answers
from itertools import product
# create an expand of all combinations of color and wave
expand = list(product(df.id.unique(), ls_color, ls_wave))

expand
[('01', 'yellow', '1'), ('01', 'yellow', '2'), ('01', 'red', '1'), ('01', 'red', '2'), ('01', 'blue', '1'), ('01', 'blue', '2'), ('02', 'yellow', '1'), ('02', 'yellow', '2'), ('02', 'red', '1'), ('02', 'red', '2'), ('02', 'blue', '1'), ('02', 'blue', '2')]

# merge with original dataframe to add the count column
pd.DataFrame.from_records(expand, columns=['id', 'color', 'wave'])
  .merge(df, how='left').fillna(0)

    id   color wave  count
0   01  yellow    1    0.0
1   01  yellow    2    2.0
2   01     red    1    1.0
3   01     red    2    0.0
4   01    blue    1    0.0
5   01    blue    2    0.0
6   02  yellow    1    0.0
7   02  yellow    2    1.0
8   02     red    1    0.0
9   02     red    2    0.0
10  02    blue    1    0.0
11  02    blue    2    0.0
user_49834500

Using explode, here is one approach:

df2 = pd.DataFrame({'id':df['id'], 'color':[ls_color]*len(df), 'wave':[ls_wave]*len(df)})
df2 = df2.explode('color', ignore_index=True).explode('wave', ignore_index=True)
df2.merge(df, how = 'left').fillna(0).drop_duplicates(ignore_index=True)

Output:

df2

    id   color  wave    count
0   01  yellow     1      0.0
1   01  yellow     2      2.0
2   01     red     1      1.0
3   01     red     2      0.0
4   01    blue     1      0.0
5   01    blue     2      0.0
6   02  yellow     1      0.0
7   02  yellow     2      1.0
8   02     red     1      0.0
9   02     red     2      0.0
10  02    blue     1      0.0
11  02    blue     2      0.0
user_14103030
Related