0

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

enter image description here

The Great
  • 7,215
  • 7
  • 40
  • 128
  • I was able to achieve the expected output with your original code. Can you also add the of the code you ran and explain why it was not your desired result? – lthc Apr 01 '22 at 04:15
  • What should be ouput from sample data after jon both DataFrames? Because if need align `df` with default indices and `df2` from `df2 = df1.reset_index(drop=True)` it should working well. – jezrael Apr 01 '22 at 05:37

1 Answers1

2

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
wwnde
  • 26,119
  • 6
  • 18
  • 32
  • Oh okay. You suggest that we don't even have to align indices? Is that what you mean? upvoted for the help – The Great Apr 01 '22 at 04:41
  • how does pandas link 1st row from one dataframe to first row from another dataframe? when there is no link – The Great Apr 01 '22 at 04:41
  • @The Great It does that by matching index. It is just a merge done on index – wwnde Apr 01 '22 at 04:51
  • but index on two dataframes are different. Isn't it? – The Great Apr 01 '22 at 05:37
  • @The Great index in this case was not set. It was automatically generated. Thats what I leveraged. Please accept the answer if it got you sorted – wwnde Apr 01 '22 at 05:39
  • My original dataframe has different indices between df and df1 as shown in sample post. So, we can join only if we reset the index of 2nd dataframe. Am I right? – The Great Apr 01 '22 at 05:44
  • Yes, thats right. And thats why I commented it may not be a reasonable data processing decisions. However. because you just want to melt as indicated, it doesnt matter because what matches between df and df1 is immaterial. – wwnde Apr 01 '22 at 05:48