Search code examples
pandasrowtranspose

take a subset of column from one df and paste into a row from another


df1

First Name    Last Name         
1. John       Smith              
2. Mary       Brohowski           
3. Aristidis  Papageorgopoulos   
4. James      Watss               
5. Kid        Red               

df2

                  1.      2.      3.         4.    5.     6.    7. 
First Name        Marty   Sandy   Parry      May   Manny  Rudy  Wesley 
Last Name         Smith   Kevin   Brohowski  Dave  Garido Red   Redknap

how can i replace row First Name 2:5 column in df2, with index 1:5 from df1 Name column, so the out put will be :

df2

              1.      2.          3.                4.      5.     6.  7.
First Name    John    Mary        Aristidis         James   Kid   Rudy Wesle
Last Name     Smith   Brohowski   Papageorgopoulos  Watss   Red   Red  Redkn

Ive tried:

df1[['First Name']].combine_first(df2) 

as i did with columns but this is not working


Solution

  • Use DataFrame.combine_first like you want only trnspose values of first DataFrame by DataFrame.T:

    #if necessary
    #df2 = df2.rename(columns=float)
    #df1 = df1.rename(index=float)
    
    df = df1.T.combine_first(df2)
    print (df)
                  1.0        2.0               3.0    4.0  5.0   6.0      7.0
    First Name   John       Mary         Aristidis  James  Kid  Rudy   Wesley
    Last Name   Smith  Brohowski  Papageorgopoulos  Watss  Red   Red  Redknap
    

    First solution with DataFrame.iloc - only first value in python is not 1, but 0 so:

    df2.iloc[0, 0:5] = df1.iloc[0:5, 0]
    df2.iloc[1, 1:5] = df1.iloc[1:5, 1]
    print (df2)
                   1.         2.                3.     4.   5.    6.       7.
    First Name   John       Mary         Aristidis  James  Kid  Rudy   Wesley
    Last Name   Smith  Brohowski  Papageorgopoulos  Watss  Red   Red  Redknap