Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Set level values in MultiIndex

Tags:

python

pandas

How can I set the level values of a Series, either by using a dictionary to replace the values, or just with a list of values as long as the series?

Here's a sample DataFrame:

     sector from_country to_country           0
0  Textiles          FRA        AUS   47.502096
1  Textiles          FRA        USA  431.890710
2  Textiles          GBR        AUS   83.500590
3  Textiles          GBR        USA  324.836158
4      Wood          FRA        AUS   27.515607
5      Wood          FRA        USA  276.501148
6      Wood          GBR        AUS    1.406096
7      Wood          GBR        USA    8.996177

Now set the index:

df = df.set_index(['sector', 'from_country', 'to_country']).squeeze()

For example, if I wanted to change based on the following key/value pairs:

In [69]: replace_dict = {'FRA':'France', 'GBR':'UK'}
In [70]: new_vals = [replace_dict[x] for x in df.index.get_level_values('from_country')]

I would like the output to look like:

In [68]: df.index.set_level_values(new_vals, level='from_country')
Out[68]: 
sector    from_country  to_country
Textiles  France        AUS            47.502096
                        USA           431.890710
          UK            AUS            83.500590
                        USA           324.836158
Wood      France        AUS            27.515607
                        USA           276.501148
          UK            AUS             1.406096
                        USA             8.996177

I currently do this, but it seems pretty dumb to me:

def set_index_values(df_or_series, new_values, level):
    """
    Replace the MultiIndex level `level` with `new_values`

    `new_values` must be the same length as `df_or_series`
    """
    levels = df_or_series.index.names
    retval = df_or_series.reset_index(level)
    retval[level] = new_values
    retval = retval.set_index(level, append=True).reorder_levels(levels).sortlevel().squeeze()
    return retval
like image 598
LondonRob Avatar asked Oct 01 '15 16:10

LondonRob


People also ask

How do I change my pandas multilevel index?

We can easily convert the multi-level index into the column by the reset_index() method. DataFrame. reset_index() is used to reset the index to default and make the index a column of the dataframe.

How do I drop one level of MultiIndex pandas?

Drop Level Using MultiIndex.droplevel() to drop columns level. When you have Multi-level columns DataFrame. columns return MultiIndex object and use droplevel() on this object to drop level.


1 Answers

Slightly hacky, but you can do this with .index.set_levels:

In [11]: df1.index.levels[1]
Out[11]: Index(['FRA', 'GBR'], dtype='object', name='from_country')

In [12]: df1.index.levels[1].map(replace_dict.get)
Out[12]: array(['France', 'UK'], dtype=object)

In [13]: df1.index = df1.index.set_levels(df1.index.levels[1].map(replace_dict.get), "from_country")

In [14]: df1
Out[14]:
sector    from_country  to_country
Textiles  France        AUS            47.502096
                        USA           431.890710
          UK            AUS            83.500590
                        USA           324.836158
Wood      France        AUS            27.515607
                        USA           276.501148
          UK            AUS             1.406096
                        USA             8.996177
Name: 0, dtype: float64

Note: There is a way to get the level number from the name, but I don't recall it.

like image 131
Andy Hayden Avatar answered Sep 22 '22 13:09

Andy Hayden