Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

Replace singular value on second level of pandas dataframe multiindex when that index level has identical values

I have a dataframe that has a multiindex with two levels. Given the following example for the second level:

d = {
"col1": [1, 2, 3, 4],
"col2": [1, 2, 3, 4],
"col3": [1, 2, 3, 4],
"col4": [1, 2, 3, 4],
"col5": [1, 2, 3, 4],
}
df = pd.DataFrame(data=d)
df.columns = pd.MultiIndex.from_product([df.columns, ["identical"]])

How do I change a singular value so that the second level of the index looks like this?

['example', 'identical', 'identical', 'identical', 'identical']

I have tried to do it this way:

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

        updated_columns = list(df.columns.get_level_values(1))

        updated_columns[0] = 'example'

        df.columns.set_levels(
            updated_columns, level=1, inplace=True, verify_integrity=False
        )

My change is ignored in this case.

I have also tried the answer from this topic: pandas MultiIndex with duplicate values in one level

        df.columns = pd.MultiIndex.from_tuples(
            df.columns.set_levels(updated_columns, 1, verify_integrity=False).values
        )

Which was also ignored.

I have also considered using the rename() method. Unfortunately it only works if the value of the column that is renamed is provided. Given that there are identical values, this will not work.

For non-multiindexes there is this method:

df.columns.values[0] = 'example'

But from what I have gathered, it will not work for a multi index.

I have added verify_integrity=False because the method would not otherwise allow me to set identical values.

Any help would be appreciated.

>Solution :

One way would be to get the tuples that make up the MultiIndex and modify them directly:

tuples = df.columns.tolist()
tuples[0] = (tuples[0][0], 'example')

df.columns = pd.MultiIndex.from_tuples(tuples)

Output:

>>> df
     col1      col2      col3      col4      col5
  example identical identical identical identical
0       1         1         1         1         1
1       2         2         2         2         2
2       3         3         3         3         3
3       4         4         4         4         4
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading