CODEWITHSUNDEEP
pythonpandasmatrixdataframeindexing
Cyril
Cyril

Reputation: 495

Panda: add a sublevel to an index that depend from the upper one

Possible duplicates but the solution provided there cannot fit my problem due to the information I get.

The idea is quite simple. I have a matrix with a multilevel (and in my case I didn't build the index, I get only the DataFrame):

#test = (('2','C'),('2','B'),('1','A'))
#test = pd.MultiIndex.from_tuples(test)
#pandas.dataFrame(index=test, columns=test)
        2                1
        C        B       A
2   C   NaN     NaN     NaN
    B   NaN     NaN     NaN
1   A   NaN     NaN     NaN

I would like to add a sublevel on the two axis in function of A, B, C. E.g.:

            2                1
            C        B       A
            kg       kg      m3
2   C   kg  NaN     NaN     NaN
    B   kg  NaN     NaN     NaN
1   A   m3  NaN     NaN     NaN

In reality the index is available through the DataFrame (I didn't build it), and I only know this: {'C':'kg', 'B':'kg', 'A':'m3'}. I can get the index series and use an approach similar to the link above, but it is very slow and I cannot imagine there is something simpler and more effective.

Upvotes: 2

Views: 334

Answers (2)

MaxU - stand with Ukraine
MaxU - stand with Ukraine

Reputation: 210852

Source DF:

In [303]: df
Out[303]:
       2         1
       C    B    A
2 C  NaN  NaN  NaN
  B  NaN  NaN  NaN
1 A  NaN  NaN  NaN

Solution:

In [304]: cols = df.columns

In [305]: new_lvl = [d[c] for c in df.columns.get_level_values(1)]

In [306]: df.columns = pd.MultiIndex.from_arrays([cols.get_level_values(0), 
                                                  cols.get_level_values(1),
                                                  new_lvl])

In [307]: df
Out[307]:
       2         1
       C    B    A
      kg   kg   m3
2 C  NaN  NaN  NaN
  B  NaN  NaN  NaN
1 A  NaN  NaN  NaN

where d is:

In [308]: d = {'C':'kg', 'B':'kg', 'A':'m3'}

In [309]: d
Out[309]: {'A': 'm3', 'B': 'kg', 'C': 'kg'}

Upvotes: 1

heyu91
heyu91

Reputation: 1272

yout can use set_index(..., append=True) to add new index

test = (('2','C'),('2','B'),('1','A'))
test = pd.MultiIndex.from_tuples(test)
x = pd.DataFrame(index=test, columns=test)

# add new index
x['new'] = pd.Series(x.index.get_level_values(-1), index=x.index).replace({'C':'kg', 'B':'kg', 'A':'m3'})
x.set_index('new', append=True, inplace=True)
x.index.names = [None] * 3

# transpose dataframe and do the same thing
x = x.T
x['new'] = pd.Series(x.index.get_level_values(-1), index=x.index).replace({'C':'kg', 'B':'kg', 'A':'m3'})
x.set_index('new', append=True, inplace=True)
x.index.names = [None] * 3
x = x.T

Upvotes: 0

Related Questions