Fill in missing group in multiindex pandas dataframe

Question

I have the following df:

date      family    ID    value
2019      abc       10    0.5
                    15    0.3 
          xyz       22    0.4
                    40    1.1
2020      xyz       22    1.9
                    46    0.3

I would like to forward fill value only if the family does not exist in a date. In the example above, the ID and value would forward fill to 2020 for abc, but not for xyz.

I have tried df.unstack(['family','ID']).fillna(method = 'ffill'), however this will incorrectly forward fill ID 40 for family xyz in the above example.

Desired result is :

    date      family    ID    value
    2019      abc       10    0.5
                        15    0.3 
              xyz       22    0.4
                        40    1.1
    2020      abc       10    0.5
                        15    0.3    
              xyz       22    1.9
                        46    0.3

df.unstack(['family','ID']).ffill().stack(['family','ID']) seems to work as expected. — Quang Hoang
– Quang Hoang, Commented Jul 8, 2020 at 16:22
Do you just have 2 years, or what should happen if abc also didn't exist in 2021? (would it get forward filled to all future years?) — ALollz
– ALollz, Commented Jul 8, 2020 at 16:35
abc would get forward filled until it appears again in a subsequent year, similar to fillna behavior. — akr24
– akr24, Commented Jul 8, 2020 at 16:38

Ben.T · Accepted Answer · 2020-07-08 16:42:26Z

1

Once unstack, you probably need to check where there is no data for the family in a year, it can be done by groupby.trasnform with any. then use where on the unstack dataframe ffilled to keep only the value you want to fill. use this in fillna. then stack back

df_u = df.unstack(['family','ID'])
df_ = df_u.fillna(
             df_u.ffill()
                 .where(~df_u.groupby(level='family', axis=1)
                             .transform('any'))
           ).stack(['family','ID'])
print (df_)
                value
date family ID       
2019 abc    10    0.5
            15    0.3
     xyz    22    0.4
            40    1.1
2020 abc    10    0.5
            15    0.3
     xyz    22    1.9
            46    0.3

answered Jul 8, 2020 at 16:42

Ben.T

29.7k6 gold badges39 silver badges57 bronze badges

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

Collectives™ on Stack Overflow

Fill in missing group in multiindex pandas dataframe

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related