How to reset MultiIndex?

Question

This is my df:

id  val1    val2    cnt
1       5       6       1
2       2       5       2
2       5       1       1
3       4       2       1
3       1       3       2

I run this code and get MultiIndex:

df = df.pivot(index="id", 
              columns="cnt", 
              values=["val1","val2"]) \
       .fillna(0)

If I add .reset_index(level=["id"]), then I get columnswith (, e.g. ('id', '').

How can I fix it?

Answer 1

If need flatten MultiIndex in columns use f-strings in list comprehension:

df = df.pivot(index="id", 
              columns="cnt", 
              values=["val1","val2"]) \
       .fillna(0)

df.columns = [f'{a}_{b}' for a, b in df.columns]

df = df.reset_index()
print (df)
   id  val1_1  val1_2  val2_1  val2_2
0   1     5.0     0.0     6.0     0.0
1   2     5.0     2.0     1.0     5.0
2   3     4.0     1.0     2.0     3.0

Another solution:

df.columns = ['_'.join(str(y) for y in x) for x in df.columns]
df = df.reset_index()
print (df)
   id  val1_1  val1_2  val2_1  val2_2
0   1     5.0     0.0     6.0     0.0
1   2     5.0     2.0     1.0     5.0
2   3     4.0     1.0     2.0     3.0

Answer 2

df.columns=['_'.join(map(str,i)) for i in df.columns]

output

   val1_1   val1_2  val2_1  val2_2
id              
1   5.0     0.0     6.0     0.0
2   5.0     2.0     1.0     5.0
3   4.0     1.0     2.0     3.0