How to convert a series into an index of another series, aligned by their indexes

Question

I have this series:

dealer      certificate_status brand_kia 
aeropuerto      NO               False     22 days
                                 True      29 days
Galerías         SI              False     59 days
                 NO              False     34 days

And I have another series:

dealer
aeropuerto    36 days
Galerías      41 days

How can I place the values of the last series as an index of the first series, but with an average name. It would be like this:

dealer       average     certificate_status    brand_kia 
aeropuerto    36 days           NO              False     22 days
                                                True      29 days
Galerías      41 ays            SI              False     59 days
                                NO              False     34 days

It is possible to make this movement. Thanks for your help.

jezrael · Accepted Answer · 2018-02-09T17:07:51.307

You can first reset_index fo columns from MultiIndex, then insert new column to position 1 by replace and last set_index for Multiindex with squeeze for convert one column df to Series:

s = s.reset_index()
s.insert(1, 'average', s['dealer'].replace(s1))
s = s.set_index(['dealer','average','certificate_status','brand_kia']).squeeze()
print (s)
dealer      average  certificate_status  brand_kia
aeropuerto  36 days  NO                  False        22 days
                                         True         29 days
Galerias    41 days  SI                  False        59 days
                     NO                  False        34 days
Name: dt, dtype: object

Or you can create new MultiIndex by get_level_values with replace and MultiIndex.from_arrays and assign back:

lvl1 = s.index.get_level_values(0)
lvl2 = s.index.get_level_values(1)
lvl3 = s.index.get_level_values(2)

lvl = pd.Series(lvl1).replace(s1).values

s.index = pd.MultiIndex.from_arrays([lvl1, lvl, lvl2, lvl3],
                         names=['dealer','average','certificate_status','brand_kia'])
print (s)
dealer      average  certificate_status  brand_kia
aeropuerto  36 days  NO                  False        22 days
                                         True         29 days
Galerias    41 days  SI                  False        59 days
                     NO                  False        34 days
Name: dt, dtype: object

How to convert a series into an index of another series, aligned by their indexes

1 Answers1