Transpose Dataframe with some Conditions

Question

I have one dataframe and sample is like below-

      UserId      UserName     Temp     Values       City     Country

 1    jsmith     James Smith     x        20        London     UK
 2    msmith     Michael Smith   y        25        Chicago    US
 3    mgarcia    Maria Garcia    z        60        Delhi      India

I want to create new dataframe in which, I want to keep the UserID and UserName column in the New Dataframe and Don't want Column Temp and From Values to Country column I want to transpose the column. I tried various ways but couldn't get it. I need final output like below-

      UserId      UserName      Values 

 1    jsmith     James Smith      20
 2    jsmith     James Smith    London
 3    jsmith     James Smith      UK
 4    msmith     Michael Smith    25
 5    msmith     Michael Smith  Chicago
 6    msmith     Michael Smith    US
 7    mgarcia    Maria Garcia     60
 8    mgarcia    Maria Garcia    Delhi
 9    mgarcia    Maria Garcia    India

How to achieve this?

score 2 · Answer 1 · answered May 03 '20 at 11:39

res = (df.set_index(['UserId','UserName'])
       .drop('Temp',axis=1)
       .stack()
       .droplevel(-1)
       .reset_index(name='Values')
      )

res

    UserId  UserName    Values
0   jsmith  James Smith 20
1   jsmith  James Smith London
2   jsmith  James Smith UK
3   msmith  Michael Smith   25
4   msmith  Michael Smith   Chicago
5   msmith  Michael Smith   US
6   mgarcia Maria Garcia    60
7   mgarcia Maria Garcia    Delhi
8   mgarcia Maria Garcia    India

Transpose Dataframe with some Conditions

1 Answers1