Geek Answers Handbook

How to order and save common indexes from two DataFrames

I have two DataFrames :

 import pandas as pd import io from scipy import stats ctrl=u"""probegenes,sample1,sample2,sample3 1415777_at Pnliprp1,20,0.00,11 1415884_at Cela3b,47,0.00,100 1415805_at Clps,17,0.00,55 1115805_at Ckkk,77,10.00,5.5 """ df_ctrl = pd.read_csv(io.StringIO(ctrl),index_col='probegenes') test=u"""probegenes,sample1,sample2,sample3 1415777_at Pnliprp1,20.1,10.00,22.3 1415805_at Clps,7,3.00,1.5 1415884_at Cela3b,47,2.01,30""" df_test = pd.read_csv(io.StringIO(test),index_col='probegenes')

They look like this:

 In [35]: df_ctrl Out[35]: sample1 sample2 sample3 probegenes 1415777_at Pnliprp1 20 0 11.0 1415884_at Cela3b 47 0 100.0 1415805_at Clps 17 0 55.0 1115805_at Ckkk 77 10 5.5 In [36]: df_test Out[36]: sample1 sample2 sample3 probegenes 1415777_at Pnliprp1 20.1 10.00 22.3 1415805_at Clps 7.0 3.00 1.5 1415884_at Cela3b 47.0 2.01 30.0

I would like to:

  • Get a generic index for a DataFrame
  • DataFrame both DataFrame .

Therefore, in the end, I get two new DataFrame :

 new_df_ctrl sample1 sample2 sample3 probegenes 1415884_at Cela3b 47 0 100.0 1415805_at Clps 17 0 55.0 1415777_at Pnliprp1 20 0 11.0 new_df_test sample1 sample2 sample3 probegenes 1415884_at Cela3b 47.0 2.01 30.0 1415805_at Clps 7.0 3.00 1.5 1415777_at Pnliprp1 20.1 10.00 22.3
+5
source share
2 answers

You can use join with the how='inner' parameter to get a common index. Then simply reindex each data file using this common index.

 idx = df_ctrl.join(df_test, rsuffix='_', how='inner').index >>> df_ctrl.reindex(idx) sample1 sample2 sample3 probegenes 1415777_at Pnliprp1 20 0 11 1415805_at Clps 17 0 55 1415884_at Cela3b 47 0 100 >>> df_test.reindex(idx) sample1 sample2 sample3 probegenes 1415777_at Pnliprp1 20.1 10.00 22.3 1415805_at Clps 7.0 3.00 1.5 1415884_at Cela3b 47.0 2.01 30.0
+3
source

You can use pd.Index.intersection() and select with .loc[] or .reindex() . Use .sort_values() in index to get the desired order:

 idx = df_ctrl.index.intersection(df_test.index).sort_values(ascending=False) df_ctrl.loc[idx] sample1 sample2 sample3 probegenes 1415884_at Cela3b 47 0.0 100.0 1415805_at Clps 17 0.0 55.0 1415777_at Pnliprp1 20 0.0 11.0 df_test.loc[idx] sample1 sample2 sample3 probegenes 1415884_at Cela3b 47.0 2.01 30.0 1415805_at Clps 7.0 3.00 1.5 1415777_at Pnliprp1 20.1 10.00 22.3
+1
source

More articles:


All Articles