How to flatten a dataframe and combine column and row labels?-CodePudding

I have a dataframe like so

     0210  0222  0525
001     1     4     7
002     2     5     8
003     3     6     9

How do I flatten it and combine the labels as such?

0210-001    1
0210-002    2
0210-003    3
0222-001    4
0222-002    5
0222-003    6
0525-001    7
0525-002    8
0525-003    9

CodePudding user response：

Use DataFrame.unstack with flatten MultiIndex values:

s = df.unstack()
s.index = [f'{a}-{b}' for a, b in s.index]

print (s)
0210-001    1
0210-002    2
0210-003    3
0222-001    4
0222-002    5
0222-003    6
0525-001    7
0525-002    8
0525-003    9
dtype: int

Last for DataFrame use:

df = s.rename_axis('a').reset_index(name='b')
print (df)
          a  b
0  0210-001  1
1  0210-002  2
2  0210-003  3
3  0222-001  4
4  0222-002  5
5  0222-003  6
6  0525-001  7
7  0525-002  8
8  0525-003  9

CodePudding user response：

You can stack and combine the two MultiIndex levels:

s = df.T.stack()
s.index = s.index.map(lambda x: f'{x[0]}-{x[1]:03d}')

output:

0210-001    1
0210-002    2
0210-003    3
0222-001    4
0222-002    5
0222-003    6
0525-001    7
0525-002    8
0525-003    9
dtype: int64