How to add padded rows of 0 to a pandas dataframe?

user_825707 0
Viewed 91

I have a df in the following form

import pandas as pd
df = pd.DataFrame({'col1' : [1,1,1,2,2,3,3,4],
    'col2' : ['a', 'b', 'c', 'a', 'b', 'a', 'b', 'a'],
    'col3' : ['x', 'y', 'z', 'p','q','r','s','t']
        })

col1    col2    col3
0   1   a   x
1   1   b   y
2   1   c   z
3   2   a   p
4   2   b   q
5   3   a   r
6   3   b   s
7   4   a   t


df2 = df.groupby(['col1','col2'])['col3'].sum()

df2

col1  col2
1     a       x
      b       y
      c       z
2     a       p
      b       q
3     a       r
      b       s
4     a       t

Now I want to add padded 0 rows to each of col1 index where a , b, c, d is missing , so expected output should be

col1  col2
1     a       x
      b       y
      c       z
      d       0
2     a       p
      b       q
      c       0
      d       0
3     a       r
      b       s
      c       0
      d       0
4     a       t
      b       0
      c       0
      d       0
pandas python
2 Answers

Use unstack + reindex + stack:

out = (
    df2.unstack(fill_value=0)
        .reindex(columns=['a', 'b', 'c', 'd'], fill_value=0)
        .stack()
)

out:

col1  col2
1     a       x
      b       y
      c       z
      d       0
2     a       p
      b       q
      c       0
      d       0
3     a       r
      b       s
      c       0
      d       0
4     a       t
      b       0
      c       0
      d       0
dtype: object

Complete Working Example:

import pandas as pd

df = pd.DataFrame({
    'col1': [1, 1, 1, 2, 2, 3, 3, 4],
    'col2': ['a', 'b', 'c', 'a', 'b', 'a', 'b', 'a'],
    'col3': ['x', 'y', 'z', 'p', 'q', 'r', 's', 't']
})

df2 = df.groupby(['col1', 'col2'])['col3'].sum()
out = (
    df2.unstack(fill_value=0)
        .reindex(columns=['a', 'b', 'c', 'd'], fill_value=0)
        .stack()
)
print(out)
user_154978880

Here's another way using pd.MultiIndex.from_product, then reindex:

mindx = pd.MultiIndex.from_product([df2.index.levels[0], [*'abcd']])
df2.reindex(mindx, fill_value=0)

Output:

col1   
1     a    x
      b    y
      c    z
      d    0
2     a    p
      b    q
      c    0
      d    0
3     a    r
      b    s
      c    0
      d    0
4     a    t
      b    0
      c    0
      d    0
Name: col3, dtype: object
user_63615310
Related