Use index column directly when sending dataframe from wide to long-CodePudding

I am trying to bring dataframe from wide to long through pd.wide_to_long() given all the columns share a string year pattern. However, I am wondering if there's a way I can do a one-liner by using the index column directly like pd.wide_to_long(df, ["A", "B"], i = df.index, j = "year").reset_index(). I tried and it didn't seem to accept the practice. Is there a way to do it?

df = pd.DataFrame({"cid" : {0 : "cd1", 1 : "cd2", 2 : "cd3"},
                   "A1970" : {0 : 3.2, 1 : 3.5, 2 : .4},
                   "A1980" : {0 : 3.1, 1 : 3.6, 2 : .5},
                   "B1970" : {0 : 2.5, 1 : 1.2, 2 : .7},
                   "B1980" : {0 : 3.2, 1 : 1.3, 2 : .1}
                  })
df = df.set_index(['cid'])

     A1970  A1980  B1970  B1980
cid                            
cd1    3.2    3.1    2.5    3.2
cd2    3.5    3.6    1.2    1.3
cd3    0.4    0.5    0.7    0.1

df['cid'] = df.index
pd.wide_to_long(df, ["A", "B"], i = 'cid', j = "year").reset_index()

Expected output:
   cid  year    A    B
0  cd1  1970  3.2  2.5
1  cd2  1970  3.5  1.2
2  cd3  1970  0.4  0.7
3  cd1  1980  3.1  3.2
4  cd2  1980  3.6  1.3
5  cd3  1980  0.5  0.1

CodePudding user response：

Can reset index inside so that wide_to_long sees the reseted dataframe. Your index already has a name so when resetted, the column name of it becomes that ('cid') and we put that to i. (In default it would be 'index')

#               this is inside
pd.wide_to_long(df.reset_index(), ["A", "B"], i='cid', j="year").reset_index()

   cid  year    A    B
0  cd1  1970  3.2  2.5
1  cd2  1970  3.5  1.2
2  cd3  1970  0.4  0.7
3  cd1  1980  3.1  3.2
4  cd2  1980  3.6  1.3
5  cd3  1980  0.5  0.1