CODEWITHSUNDEEP
pythonpandasmulti-index
peter_b
peter_b

Reputation: 21

Pandas replace value in multiindex row

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.

Upvotes: 2

Views: 1352

Answers (2)

CypherX
CypherX

Reputation: 7353

Solution

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

Logic

  1. Separate index and process it as a dataframe
  2. Prepare a MultiIndex
  3. 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)

Upvotes: 2

Karthik V
Karthik V

Reputation: 1897

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

Upvotes: 3

Related Questions