# Pivot pandas DataFrame while removing index duplicates

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.

I would use the built-in drop_duplicates() functionality.
x.drop_duplicates(['Loc', 'Change', 'Chrom'], keep='first', inplace=True)