Pandas Insert missing row based on value and order

Question

Given the following dataframe:

import pandas as pd
df = pd.DataFrame({'level_0':[0,0,0,1,1,2,2,2,3,3],'name':['a','b','c','a','b','a','b','c','a','b'],'value':[1,2,3,1,2,1,2,3,1,2]})
df.set_index('level_0', inplace=True)
df
        name  value
level_0            
0          a      1
0          b      2
0          c      3
1          a      1
1          b      2
2          a      1
2          b      2
2          c      3
3          a      1
3          b      2

I want to insert a row containing "c" for "name" and 3 for "value" where missing per index (level_0). I need to preserve the order such that "c" always occurs after "b".

Desired result:

        name  value
level_0            
0          a      1
0          b      2
0          c      3
1          a      1
1          b      2
1          c      3
2          a      1
2          b      2
2          c      3
3          a      1
3          b      2
3          c      3

Thanks in advance!

Anurag Dabas · Accepted Answer · 2021-07-24 05:49:01Z

2

try via pd.MultiIndex.from_product() method and reindex() method:

df=df.set_index('name',append=True)
idx=pd.MultiIndex.from_product([df.index.get_level_values(0).unique(),df.index.get_level_values(1).unique()])
#OR as suggested by @Scott Boston
idx = pd.MultiIndex.from_product(df.index.levels)
#Finally:
df=df.reindex(idx,fill_value=3).reset_index(level=1)

OR

If you don't make 'level_0' column as index in beginning then you can do:

idx=pd.MultiIndex.from_product([df['level_0'].unique(),df['name'].unique()])
#Finally:
df=df.set_index(['level_0','name']).reindex(idx,fill_value=3).reset_index(level=1)

output of df:

            name    value
level_0         
0           a       1
0           b       2
0           c       3
1           a       1
1           b       2
1           c       3
2           a       1
2           b       2
2           c       3
3           a       1
3           b       2
3           c       3

edited Jul 24, 2021 at 5:49

answered Jul 24, 2021 at 4:01

Anurag Dabas

24.3k9 gold badges25 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Scott Boston Over a year ago

idx = pd.MultiIndex.from_product(df.index.levels) another way.

Collectives™ on Stack Overflow

Pandas Insert missing row based on value and order

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related