Pandas replace value in multiindex row

Question

So, I have a MultiIndex DataFrame and I cannot figure out row to modify a row index value.

In this example, I would like to set c = 1 where the "a" index is 4:

import pandas as pd
import numpy as np

df = pd.DataFrame({('colA', 'x1'): {(1, np.nan, 0): np.nan, (4, np.nan, 0): np.nan},
('colA', 'x2'): {(1, np.nan, 0): np.nan, (4, np.nan, 0): np.nan},
('colA', 'x3'): {(1, np.nan, 0): np.nan, (4, np.nan, 0): np.nan},
('colA', 'x4'): {(1, np.nan, 0): np.nan, (4, np.nan, 0): np.nan}})

df.index.set_names(['a', 'b', 'c'], inplace=True)

print(df)


            colA
              x1    x2  x3  x4
a   b   c               
1   NaN 0   NaN NaN NaN NaN
4   NaN 0   NaN NaN NaN NaN

Desired output:

            colA
              x1    x2  x3  x4
a   b   c               
1   NaN 0   NaN NaN NaN NaN
4   NaN 1   NaN NaN NaN NaN

Any help is appreciated.

This will give IndexError: only integers, slices (:), ellipsis (...), numpy.newaxis (None) and integer or boolean arrays are valid indices — peter_b
– peter_b, Commented May 14, 2020 at 20:28
hmmm sorry its a bit harder than i expected... I dont have time to solve it right now ... but here is how you can get the mask to use mask = df.index.get_level_values('a') == 4 — Joran Beasley
– Joran Beasley, Commented May 14, 2020 at 20:44

Karthik V · Accepted Answer · 2020-05-14 20:55:42Z

3

Assuming we start with df.

x = df.reset_index()
x.loc[x[x.a == 4].index, 'c'] = 1
x = x.set_index(['a', 'b', 'c'])
print(x)

        colA            
          x1  x2  x3  x4
a b   c                 
1 NaN 0  NaN NaN NaN NaN
4 NaN 1  NaN NaN NaN NaN

answered May 14, 2020 at 20:55

Karthik V

1,8971 gold badge16 silver badges24 bronze badges

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

3 Comments

peter_b Over a year ago

I'm guessing there's no way to do it directly, without reseting the index, right? BC I'm working with a big dataframe in reality

Karthik V Over a year ago

resetting the index doesn't change the order of the data and the size of the data doesn't really matter.

Joran Beasley Over a year ago

big is pretty fluid.. how do you define big?

CypherX · Accepted Answer · 2020-05-16 01:06:13Z

2

Solution

Separate the index, process it and put it back together with the data.

Logic

Separate index and process it as a dataframe
Prepare a MultiIndex
Either of the following two options:
- combine data and MultiIndex together Method-1
- update the index of the original dataframe Method-2

Code

# separate the index and process it
names = ['a', 'b', 'c'] # same as df.index.names
#dfd = pd.DataFrame(df.to_records())
dfd = df.index.to_frame().reset_index(drop=True)
dfd.loc[dfd['a']==4, ['c']] = 1

# prepare index for original dataframe: df
index = pd.MultiIndex.from_tuples([tuple(x) for x in dfd.loc[:, names].values], names=names)

## Method-1
# create new datframe with updated index
dfn = pd.DataFrame(df.values, index=index, columns=df.columns)
# dfn --> new dataframe

## Method-2
# reset the index of original dataframe df
df.set_index(index)

Output:

            colA            
              x1  x2  x3  x4
a   b   c                   
1.0 NaN 0.0  NaN NaN NaN NaN
4.0 NaN 1.0  NaN NaN NaN NaN

Dummy Data

import pandas as pd
import numpy as np

df = pd.DataFrame({('colA', 'x1'): {(1, np.nan, 0): np.nan, (4, np.nan, 0): np.nan},
('colA', 'x2'): {(1, np.nan, 0): np.nan, (4, np.nan, 0): np.nan},
('colA', 'x3'): {(1, np.nan, 0): np.nan, (4, np.nan, 0): np.nan},
('colA', 'x4'): {(1, np.nan, 0): np.nan, (4, np.nan, 0): np.nan}})

df.index.set_names(['a', 'b', 'c'], inplace=True)

edited May 16, 2020 at 1:06

answered May 14, 2020 at 21:16

CypherX

7,4034 gold badges29 silver badges39 bronze badges

2 Comments

CypherX Over a year ago

@peter_b Here's another option.

data-monkey Over a year ago

I can see this certainly works but it really feels like an overkill!

Collectives™ on Stack Overflow

Pandas replace value in multiindex row

2 Answers 2

3 Comments

Solution

Logic

Code

Dummy Data

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Solution

Logic

Code

Dummy Data

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related