How to convert a matrix looking dataframe with row and column labels to a regular pandas dataframe?

user_11993862 0
Viewed 871

I have a pandas dataframe which looks like the matrix below with row and column labels: 

data=pd.DataFrame(np.array([[1,1,3],[2,1,4],[2,3,0.8]]),columns=['d','e','f'],index=['a','b','c'])

enter image description here

I would like to convert it to a proper dataframe which looks like this: 

output=pd.DataFrame(np.array([[1,1,1,0.8]]),columns=['values'],index=['a-d','a-e','b-e','c-f'])

enter image description here

I tried using pd.melt but it just converts the column labels to row labels without merging them with the existing row labels. Is there a function or a way to achieve my goal in python?

pandas dataframe matrix
2 Answers

Start with yourr dataframe: 

data = pd.DataFrame(
    np.array([[1, 1, 3], [2, 1, 4], [2, 3, 0.8]]),
    columns=["d", "e", "f"],
    index=["a", "b", "c"],
)
print(data)
      d     e     f
a 1.000 1.000 3.000
b 2.000 1.000 4.000
c 2.000 3.000 0.800

Use pandas stack to move the column headers to a multi index.

df = data.stack()
print(df)
a  d   1.000
   e   1.000
   f   3.000
b  d   2.000
   e   1.000
   f   4.000
c  d   2.000
   e   3.000
   f   0.800
dtype: float64

Use list comprehension to combime the two indices. 

[x[0] + "-" + x[1] for x in df.index]
print
['a-d', 'a-e', 'a-f', 'b-d', 'b-e', 'b-f', 'c-d', 'c-e', 'c-f']

Set the index to the list above.

df.index = [x[0] + "-" + x[1] for x in df.index]
print(df)
a-d   1.000
a-e   1.000
a-f   3.000
b-d   2.000
b-e   1.000
b-f   4.000
c-d   2.000
c-e   3.000
c-f   0.800
user_108886550

using melt and str.cat

s = data.reset_index().melt(id_vars='index').sort_values('index')
s['index'] = s['index'].str.cat(s['variable'],sep='-')
s = s.drop('variable',axis=1)

print(s)

  index  value
0   a-d    1.0
3   a-e    1.0
6   a-f    3.0
1   b-d    2.0
4   b-e    1.0
7   b-f    4.0
2   c-d    2.0
5   c-e    3.0
8   c-f    0.8

Edit

Cosider this simpler solution as proposed by our resident genius anky using .pop in conjunction with str.cat

s = data.reset_index().melt(id_vars='index').sort_values('index')
s['index'] = s['index'].str.cat(s.pop('variable'),sep='-') 

print(s)

  index  value
0   a-d    1.0
3   a-e    1.0
6   a-f    3.0
1   b-d    2.0
4   b-e    1.0
7   b-f    4.0
2   c-d    2.0
5   c-e    3.0
8   c-f    0.8
user_93751020
Related