Assigning values to multi-indexed dataframe based on index level

Question

I have a multi-indexed dataframe df1 with three indices whose values only belong to the first index. However, after a merge, the dataframe got split up which resulted in various 0. Now I want to replace the 0 with the values belonging to the first index level.

Here is an extraction from the dataframe:

FID_Weser_Catchments_134_WQ_Stations_FINAL_LAEA... SNR1  gridcode_1               
1                                                  6631  2           543738.387186
                                                         3           543738.387186
                                                         4           543738.387186
                                                   6632  1           543738.387186
                                                         2           543738.387186
                                                         3           543738.387186
                                                         4           543738.387186
                                                   6633  2                0.000000
                                                         3                0.000000
                                                   6634  2                0.000000
                                                   6636  1           543738.387186
                                                         2           543738.387186
                                                         3           543738.387186
                                                         4           543738.387186
                                                   9473  2           543738.387186
                                                         3           543738.387186
                                                   9673  2           543738.387186
                                                         3           543738.387186
                                                         4           543738.387186
                                                   16054 1           543738.387186

FID_Weser_Catchments..., SNR1 and gridcode_1 are the indices but the values only originate from index(level=0). There are in total 133 index(level=0) so I want to find a general function to replace the 0 with the affiliated value.

jezrael · Accepted Answer · 2019-09-26 11:41:59Z

1

Use Series.mask with replacement by Series created by GroupBy.transform with GroupBy.first by first level of MultiIndex:

first = s.groupby(level=0).transform('first')

s = s.mask(s == 0, first)

edited Sep 26, 2019 at 11:41

answered Sep 26, 2019 at 11:36

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

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

Collectives™ on Stack Overflow

Assigning values to multi-indexed dataframe based on index level

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