Retrieve data from another table using index

Question

I want to deal with biological data using python and pandas.

Table 1 is consist of name of protein pairs. XX, YY, ZZ and AA mean the name of protein. Table 2 is consist of some data(values) of each proteins.

What I want to calculate is Pearson Correlation Coefficient(PCC) of each protein pairs that exist in Table 1, using the Data list of Table 2. It is a kind of database-like approach.

Table 1

Col1 Col2
 XX YY
 XX ZZ
 ZZ AA

Table 2

      XX YY ZZ AA BB CC ...
Data1 10 20 30 40 50 60 ...
Data2 11 12 13 14 15 16 ...
Data3 80 70 60 50 40 20 ...

Result

      PCC
XX_YY R1
XX_ZZ R2
ZZ_AA R3

R1 in the result table is the PCC value of protein XX and YY, in other words, the result of pearsonr([10, 11, 80], [20, 12, 70]). R2 and R3 will be pearsonr([10, 11, 80], [30, 13, 60]) and pearsonr([30, 13, 60], [40, 14, 50]) each.

I got an advice to use combination function before in this kind of work. It was very nice when calculating with all pairs, but in this case, I need to calculate only about the protein pairs which are in table 1, so it is difficult to use.

Is there any simple way to do this kind of work easily with pandas please?

Stefan Stefan · Accepted Answer · 2015-12-02T17:23:28

You could iterate through the rows of the first DataFrame (df1), select the relevant columns from df2 and add the result with appropriate labels to a dictionary, which you can convert to a Series when done:

correlations = {}
for i, row in df1.iterrows():
    correlations[row['col1' + '_' + row['col2']]] = df2.loc[:,row['col1']].corr(df2.loc[:, row['col2'], method='pearson')

result = pd.Series(correlations)

Retrieve data from another table using index

1 Answers