How to order and keep common indexes from two DataFrames

Question

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'd like to:

1. Get a common index for both DataFrame
2. Reorder both DataFrame identically.

Hence, 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

Answer 1

You can use join with the parameter how='inner' to get the common index. Then just reindex each dataframe 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