Align not matching indices and perform pandas melt-CodePudding

I have two dataframes with different indices

subject_ID,score,region,supplier
1001,27,AP,ABC
1002,35,AP,ABC
1003,29,ANZ,DEF
1004,35,ANZ,DEF
1005,30,KOREA,GHI
1006,34,KOREA,GHI

df = pd.read_clipboard(sep=',')

test_score,dumma,dummeel
217,23,45
315,43,65
219,12,46
315,17,87
310,19,97
314,23,63

df1 = pd.read_clipboard(sep=',')
s = pd.Series([11, 21, 31, 114,261,321])
df1.set_index([s],inplace=True)

Basically, both dataframes are of equal len.

So, 1st row (index 0) in df corresponds to index 11 in df1. Similarly, index 2 in df corresponds to index 21 in df1..so on

I would like to concat two dataframes and do pandas melt operation

I tried the below but it doesn't work

df2 = df1.reset_index(drop=True)
t2 = pd.concat([df, df2], axis=1)
pd.melt(t2, id_vars =['subject_ID'], value_vars =['score','region','supplier','test_score','dumma','dummeel'])

I expect my output like below

CodePudding user response：

Not sure why you would want to do that, but here is the code

pd.melt(df.join(df1), id_vars=['subject_ID'], value_vars=['score','region','supplier'])



 subject_ID  variable  value
0         1001     score     27
1         1002     score     35
2         1003     score     29
3         1004     score     35
4         1005     score     30
5         1006     score     34
6         1001    region     AP
7         1002    region     AP
8         1003    region    ANZ
9         1004    region    ANZ
10        1005    region  KOREA
11        1006    region  KOREA
12        1001  supplier    ABC
13        1002  supplier    ABC
14        1003  supplier    DEF
15        1004  supplier    DEF
16        1005  supplier    GHI
17        1006  supplier    GHI