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


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  \
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  
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)
x = x.pivot(index='newindex', columns='Individual', values='VAF')

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.