CODEWITHSUNDEEP
pythonpandas
koPytok
koPytok

Reputation: 3713

Add missing rows within groups

Suppose dataframe:

df = pd.DataFrame({
    "id":     [1, 1, 1, 2, 2, 3],
    "day":    [1, 2, 3, 1, 3, 2],
    "value":  [1, 2, 3, 4, 5, 6],
})

I need all ids to have the same set of days. How to add rows with missing days?

Upvotes: 2

Views: 117

Answers (2)

jezrael
jezrael

Reputation: 862601

For solutions with multiple columns filled by 0 for new missing rows use DataFrame.unstack with DataFrame.stack working with MultiIndex by DataFrame.set_index, last convert again to columns by DataFrame.reset_index:

df = df.set_index(['id','day']).unstack(fill_value=0).stack().reset_index()
print (df)
   id  day  value
0   1    1      1
1   1    2      2
2   1    3      3
3   2    1      4
4   2    2      0
5   2    3      5
6   3    1      0
7   3    2      6
8   3    3      0

Another solution with DataFrame.reindex by MultiIndex.from_product, working with MultiIndex by DataFrame.set_index, last convert again to columns by DataFrame.reset_index:

df = df.set_index(['id','day'])
m = pd.MultiIndex.from_product(df.index.levels, names=df.index.names)

df = df.reindex(m, fill_value=0).reset_index()
print (df)
   id  day  value
0   1    1      1
1   1    2      2
2   1    3      3
3   2    1      4
4   2    2      0
5   2    3      5
6   3    1      0
7   3    2      6
8   3    3      0

Upvotes: 3

BENY
BENY

Reputation: 323226

IIUC

df=df.pivot(*df.columns).fillna(0).stack().reset_index()
   id  day    0
0   1    1  1.0
1   1    2  2.0
2   1    3  3.0
3   2    1  4.0
4   2    2  0.0
5   2    3  5.0
6   3    1  0.0
7   3    2  6.0
8   3    3  0.0

Upvotes: 4

Related Questions