CODEWITHSUNDEEP
pythonpandasdataframemulti-index
Winand
Winand

Reputation: 2423

Replace a value in MultiIndex (pandas)

In the following DataFrame: How can I replace ["x2", "Total"] with ["x2", "x2"] leaving x1 as is?

l1           900    902    912     913    916     
l2           ИП ПС  ИП ПС  ИП  ПС  ИП ПС  ИП ПС  
i1    i2                                      
x1    Total  10  6   3  3  10  16   2  9   3  8  
x2    Total   1  0   0  0   0   0   0  0   0  0

.rename will replace all "Total" values, not just the one I need.

Upvotes: 7

Views: 14768

Answers (2)

Muhammad Yasirroni
Muhammad Yasirroni

Reputation: 2137

For those that come here to replace the value of the DataFrame, not the index:

# data
import pandas as pd

index = pd.MultiIndex.from_tuples([('x1', 'Total'), ('x2', 'Total'), ('x1', 'Foo'), ('x2', 'Foo')],
                                 names=('i1', 'i2'))
columns = pd.MultiIndex.from_tuples([('900', 'ИП'), ('900', 'ПС'), ('902', 'ИП'), ('902', 'ПС'),
                                    ('912', 'ИП'), ('912', 'ПС'), ('913', 'ИП'), ('913', 'ПС'),
                                    ('916', 'ИП'), ('916', 'ПС')],
                                   names=('l1', 'l2'))
data = [[10, 6, 3, 3, 10, 16, 2, 9, 3, 8],
        [1, 0, 0, 0, 0, 0, 0, 0, 0, 0],
        [0, 0, 3, 10, 16, 2, 0, 0, 0, 0],
        [0, 0, 0, 0, 3, 10, 16, 2, 0, 0]]

df = pd.DataFrame(data, index=index, columns=columns).T

print(df)
# i1        x1    x2  x1  x2
# i2     Total Total Foo Foo
# l1  l2                    
# 900 ИП    10     1   0   0
#     ПС     6     0   0   0
# 902 ИП     3     0   3   0
#     ПС     3     0  10   0
# 912 ИП    10     0  16   3
#     ПС    16     0   2  10
# 913 ИП     2     0   0  16
#     ПС     9     0   0   2
# 916 ИП     3     0   0   0
#     ПС     8     0   0   0

# solution
dfx = df.xs('Total', axis=1, level=1, drop_level=False)
dfx.loc[['902', '913'], :] = 'bar'  # you also can change ['902', '913'] to index-based approach
df.loc[:, dfx.columns] = dfx
print(df)
# i1        x1    x2  x1  x2
# i2     Total Total Foo Foo
# l1  l2                    
# 900 ИП    10     1   0   0
#     ПС     6     0   0   0
# 902 ИП   bar   bar   3   0
#     ПС   bar   bar  10   0
# 912 ИП    10     0  16   3
#     ПС    16     0   2  10
# 913 ИП   bar   bar   0  16
#     ПС   bar   bar   0   2
# 916 ИП     3     0   0   0
#     ПС     8     0   0   0

Upvotes: 0

khammel
khammel

Reputation: 2127

Assuming your dataframe is called df the following code will perform your desired substitution by replacing the existing index with a modified index.

index = df.index
names = index.names
index = df.index.tolist()[:1]+[('x2','x2')]  
df.index = pd.MultiIndex.from_tuples(index, names = names)

Or you can directly modify the inner level of the index:

df.index.set_levels([u'Total', u'x2'],level=1,inplace=True)
df.index.set_labels([0, 1],level=1,inplace=True)

You can also use level='i2' in place of level=1

Upvotes: 12

Related Questions