adding two columns in multiindex dataframe pandas

Question

I am using pandas 1.14.

I have dataframe that looks like this:

                          col1      col2  ....
    A   B   C  D  E    

   11   1   1  1  1        2          3
                  3        3          4
               30 3        10         2
                ...        ...
   22   3   4  5  6        3          1

df.index outputs

MultiIndex([('11', '1', '1', '1', '1'),
            ('11', '1', '1', '1', '3'),
            ('11', '1', '1', '30', '3'),
            ...
            ('22', '3', '4', '5', '6')],
           names=["A","B","C", "D", "E"], length=10000)

df.columns outputs

Index(["col1", "col2", ...], dtype="object")

what I want to do it add both columns and divide by 2. in single index dataframe I would usually do df["new"] = (df["col1"] + df["col2"])/2

how can I do this with multiindex dataframe?

My desired dataframe should look like this

                          col1      col2  new
    A   B   C  D  E    

   11   1   1  1  1        2          3    2.5
                  3        3          4    3.5
               30 3        10         2    6
                ...        ...
   22   3   4  5  6        3          1    2

Thanks in advance!

Does df["new"] = (df["col1"] + df["col2"])/2 not work? I thought it would. Could you explain the error you get when you try? — maow
– maow, Commented Jan 6, 2021 at 8:47

maow · Accepted Answer · 2021-01-06 08:50:56Z

1

Your solution should work for MultiIndexes as well

In [14]: df = pd.DataFrame([[2,3],[3,4],[10,2],[3,1]], columns=['col1', 'col2'], index=index)                                                                                                              

In [15]: df                                                                                                                                                                                                
Out[15]: 
             col1  col2
A  B C D  E            
11 1 1 1  1     2     3
          3     3     4
       30 3    10     2
22 3 4 5  6     3     1

In [16]: df['new'] = (df['col1'] + df['col2'])/2                                                                                                                                                           

In [17]: df                                                                                                                                                                                                
Out[17]: 
             col1  col2  new
A  B C D  E                 
11 1 1 1  1     2     3  2.5
          3     3     4  3.5
       30 3    10     2  6.0
22 3 4 5  6     3     1  2.0

answered Jan 6, 2021 at 8:50

maow

2,8971 gold badge14 silver badges27 bronze badges

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

1 Comment

haneulkim Over a year ago

Yup, it works... Idk what I was thinking lol... sorry about that. I've tried it again and it works too.

mosc9575 · Accepted Answer · 2021-01-06 08:52:44Z

0

I did an experiment and your approche should work.

df = pd.DataFrame({'a':[1,2,3,4], 'b':[2,3,4,5]}, index=[['1', '1', '2', '2'], ['1','2','1','2']])
df
>>>

     a  b
1 1  1  2
  2  2  3
2 1  3  4
  2  4  5

Your approche.

df['new'] = (df['a'] + df['b']) / 2

df
>>>
     a  b  new
1 1  1  2  1.5
  2  2  3  2.5
2 1  3  4  3.5
  2  4  5  4.5
```

answered Jan 6, 2021 at 8:52

mosc9575

6,3922 gold badges12 silver badges36 bronze badges

Comments

Rob Raymond · Accepted Answer · 2021-01-06 08:58:04Z

0

no special treatment, standard techniques. My standard is to always use assign()

df = pd.DataFrame({"A":[11],"B":[1],"C":[1],"D":[1],"E":[1],"col1":[2],"col2":[3]})
df = df.set_index(["A","B","C","D","E"])
df = df.assign(new=lambda dfa: dfa.sum(axis=1)/2)

print(df.to_string())

output

            col1  col2  new
A  B C D E                 
11 1 1 1 1     2     3  2.5

answered Jan 6, 2021 at 8:58

Rob Raymond

31.5k3 gold badges19 silver badges34 bronze badges

Collectives™ on Stack Overflow

adding two columns in multiindex dataframe pandas

3 Answers 3

1 Comment

Comments

output

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

output

Comments

Your Answer

Sign up or log in

Post as a guest

Related