How to convert many columns into rows in python dataframe?

Question

I have a data frame with the following structure. I need to bring all the values in columns code1,code2,code3into different rows under same name. Similarly for textcolumns.

Name      Code1   text1  code2  text2  code3  text3     
Alexa     362     Eng    639    scien  563    maths
john      23      cri    36     ball   03     value

What I would like is for it to look like

Name      Code   text 
Alexa     362    Eng    
Alexa     639    scien  
Alexa     563    maths
john      23     cri    
john      36     ball   
john      03     value

you can check this post: https://stackoverflow.com/questions/28654047/pandas-convert-some-columns-into-rows — huy, Mar 20 '20 at 07:23

score 1 · Accepted Answer · answered Mar 20 '20 at 10:01

For each name, you can add a new row with their code and text in a separate dataframe.

r, c = df.shape  # original dataframe shape
df2 = pd.DataFrame()  # Create a new dataframe
for i in range(r):
    name = df.iloc[i, 0]
    for j in range(1, c, 2):
        df2 = df2.append({'Name': name, 'Code': df.iloc[i, j], 'Text': df.iloc[i, j + 1]},ignore_index=True)
print(df2)

score 0 · Answer 2 · answered Mar 20 '20 at 11:57

Starting with df as below (note all headers in lowercase for consistency):

#    name  code1 text1  code2  text2  code3  text3
#0  Alexa    362   Eng    639  scien    563  maths
#1   john     23   cri     36   ball      3  value

Execute the following:

df1=pd.melt(df, id_vars=["name"], value_vars=df[['code1','code2','code3']], var_name='initial code column', value_name="code")
df1=df1.drop(columns=['initial code column'])

df2=pd.melt(df, id_vars=["name"], value_vars=df[['text1','text2','text3']], var_name='initial text column', value_name="text")
df2=df2.drop(columns=['initial text column'])    #you don't need this either

diff= df2[df2.columns.difference(df1.columns)]
df=pd.concat([df1,diff],axis=1)

Output:

#    name  code   text
#0  Alexa   362    Eng
#1   john    23    cri
#2  Alexa   639  scien
#3   john    36   ball
#4  Alexa   563  maths
#5   john     3  value

Comments:

create df1 with pd.melt to get all codes under one column, respectively df2 for text values
var_name("initial code column") is something that pd.melt creates, useful sometimes, but not in your case, so drop this column; same with "initial text column"
use difference and concat to build the required df

How to convert many columns into rows in python dataframe?

2 Answers2