CODEWITHSUNDEEP
pythonpandas
MichlF
MichlF

Reputation: 139

pandas: reshape column and row information into separate columns

Using python pandas, I would like to transform the following data (frame)...

A1 - A2 - A3
10 - 30 - 50
11 - 31 - 51
12 - 32 - 52

to something like...

Ro - Co - Value
R1 - A1 - 10
R1 - A2 - 30
R1 - A3 - 50
R2 - A1 - 11
R2 - A2 - 31
R2 - A3 - 51
R3 - A1 - 12
R3 - A2 - 32
R3 - A3 - 52

In other words, original rows should be represented in a separate column, the original columns should also be represented in a separate column and the original values should be represented for all combinations in a separate column.

Any help is appreciated!

PS: Sorry for the poor title choice. If there is a suggestion to improve, please let me know.

Upvotes: 2

Views: 84

Answers (3)

cph_sto
cph_sto

Reputation: 7585

# Creating the DataFrame first
import pandas as pd
df=pd.DataFrame({'A1':[10,11,12],'A2':[30,31,32],'A3':[50,51,52]})
df['Ro']=df.index
df['Ro'] = df['Ro'].map(lambda x: 'R'+str(x))
df

   A1  A2  A3  Ro
0  10  30  50  R0
1  11  31  51  R1
2  12  32  52  R2

df = df.set_index(['Ro']).stack().reset_index().rename(columns={'level_1':'Co',0:'Value'})
df

   Ro  Co  Value
0  R0  A1     10
1  R0  A2     30
2  R0  A3     50
3  R1  A1     11
4  R1  A2     31
5  R1  A3     51
6  R2  A1     12
7  R2  A2     32
8  R2  A3     52

Upvotes: 0

user3483203
user3483203

Reputation: 51165

stack + rename_axis:

u = df.stack().rename_axis(['Ro', 'Co']).rename('Value').reset_index()
u.assign(Ro=[f'L{r+1}' for r in u.Ro])


   Ro  Co  Value
0  L1  A1     10
1  L1  A2     30
2  L1  A3     50
3  L2  A1     11
4  L2  A2     31
5  L2  A3     51
6  L3  A1     12
7  L3  A2     32
8  L3  A3     52

Upvotes: 2

yatu
yatu

Reputation: 88226

Use .melt:

df.reset_index().melt(id_vars='index')

   index   variable  value
0      0       A1     10
1      1       A1     11
2      2       A1     12
3      0       A2     30
4      1       A2     31
5      2       A2     32
6      0       A3     50
7      1       A3     51
8      2       A3     52

Upvotes: 2

Related Questions