Pivot pandas DataFrame while removing index duplicates

Question

The goal of my code is to pivot a pandas DataFrame which is shown below. The idea is to use the Individual column as the column IDs and the VAF column as the values. I would like to combine the data such that the values from the columns Loc, Change, Chrom are used as the new index. To do this I made two new columns, newindex, and uniques in order to remove duplicates and then pivot.

Original DataFrame:

  Chrom        Loc WT Var Change ConvChange  AO     DP          VAF IntEx  \
0  chr1  115227855  T   A    T>A        T>A   5  19346  0.000258451  TIII   
1  chr1  115227855  T   C    T>C        T>C   4  19346  0.000206761  TIII   
2  chr1  115227856  C   T    C>T        C>T  14  19377  0.000722506  TIII   
3  chr1  115227857  C   A    C>A        C>A   3  19377  0.000154823  TIII   
4  chr1  115227857  C   T    C>T        C>T  15  19377  0.000774114  TIII   

    Gene Upstream Downstream Individual          newindex             uniques  
0  TIIIa        T          C          1  115227855T>Achr1  115227855T>Achr1_1  
1  TIIIa        T          C          1  115227855T>Cchr1  115227855T>Cchr1_1  
2  TIIIa        T          C          1  115227856C>Tchr1  115227856C>Tchr1_1  
3  TIIIa        C          A          1  115227857C>Achr1  115227857C>Achr1_1  
4  TIIIa        C          A          1  115227857C>Tchr1  115227857C>Tchr1_1

Code:

x = x.set_index(['uniques'])
x = x[~x.index.duplicated(keep='first')]
x = x.pivot(index='newindex', columns='Individual', values='VAF')

So in the above code I set the new index that uses the uniques column that is the same as the newindex column but with the values from Individual appended. Then remove the duplicate indices, and finally pivot into the final form shown below.

Individual                  1            2            3            4  \
newindex                                                               
106155152T>Achr4  0.000120685  0.000383835  0.000383224  0.000136617   
106155152T>Cchr4  0.000603427  0.000575753  0.000694594  0.000461081   
106155152T>Gchr4         None         None  0.000143709  6.83083e-05   
106155153G>Achr4         None  0.000355969  0.000239257  0.000392398   
106155153G>Tchr4         None  5.47645e-05         None  3.41215e-05   

Individual                  5            6            7  
newindex                                                 
106155152T>Achr4  0.000403506  0.000443759  0.000477926  
106155152T>Cchr4  0.000560425  0.000570547   0.00155326  
106155152T>Gchr4         None  8.45255e-05         None  
106155153G>Achr4  0.000179127  0.000274534  0.000298436  
106155153G>Tchr4  6.71727e-05         None         None

To clarify, I'm guessing there is probably a much more efficient way of making the new indexes that don't contain duplicates than making the newindex and uniques columns like I do.

Answer 1

I would use the built-in drop_duplicates() functionality.

x.drop_duplicates(['Loc', 'Change', 'Chrom'], keep='first', inplace=True)
x = x.pivot(index='newindex', columns='Individual', values='VAF')