How to replace values in a pandas multiindex column set

Question

Given the following:

dd = {}
for i in range(3):
    for j in range(3):
        key = (f"col_{i}", j)
        dd[key] = {1: 2, 3: 4}



print(pd.DataFrame.from_dict(dd))

Which looks as:

  col_0       col_1       col_2      
      0  1  2     0  1  2     0  1  2
1     2  2  2     2  2  2     2  2  2
3     4  4  4     4  4  4     4  4  4

I would like to use the following replacements:

reps = {
    "col_0": {0: "o", 1: "one", 2: "two"},
    "col_1": {0: "o2", 1: "one2", 2: "two2"},
    "col_2": {0: "o3", 1: "one3", 2: "two3"},
}

So that the col_0, col_1, col_2 are unchanged, but the second level of 0,1,2 is changed to o, one, two, o2, one2, two2, and o3, one, two3 respectively , giving something similar to :

  col_0             col_1            col_2      
      o  one  two   o2 one2 two2     o3  one3  two3
1     2  2    2      2  2    2        2    2     2
3     4  4    4      4  4    4        4    4     4

jezrael · Accepted Answer · 2020-08-11 11:03:29Z

You can create tuples with columns names and then for match dictionary is used get with second argument for default value, so if no match no replacement:

L = [(a, reps[a].get(b, b)) if a in reps else (a, b) for a, b in df.columns.tolist()]
df.columns = pd.MultiIndex.from_tuples(L)
print (df)
  col_0         col_1           col_2          
      o one two    o2 one2 two2    o3 one3 two3
1     2   2   2     2    2    2     2    2    2
3     4   4   4     4    4    4     4    4    4

Test if no match outer key in reps dict:

reps = {
    "col_100": {0: "o", 1: "one", 2: "two"},
    "col_1": {0: "o2", 1: "one2", 2: "two2"},
    "col_2": {0: "o3", 1: "one3", 2: "two3"},
}

L = [(a, reps[a].get(b, b)) if a in reps else (a, b) for a, b in df.columns.tolist()]
df.columns = pd.MultiIndex.from_tuples(L)
print (df)
  col_0       col_1           col_2          
      0  1  2    o2 one2 two2    o3 one3 two3
1     2  2  2     2    2    2     2    2    2
3     4  4  4     4    4    4     4    4    4

Test if no match inner keys:

reps = {
    "col_0": {100: "o", 1: "one", 20: "two"},
    "col_1": {0: "o2", 1: "one2", 2: "two2"},
    "col_2": {0: "o3", 1: "one3", 2: "two3"},
}

L = [(a, reps[a].get(b, b)) if a in reps else (a, b) for a, b in df.columns.tolist()]
df.columns = pd.MultiIndex.from_tuples(L)
print (df)
  col_0        col_1           col_2          
      0 one  2    o2 one2 two2    o3 one3 two3
1     2   2  2     2    2    2     2    2    2
3     4   4  4     4    4    4     4    4    4

Collectives™ on Stack Overflow

How to replace values in a pandas multiindex column set

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related