Fast way of filtering pandas dataframes according to element positions in another dataset

user_14094304 0
Viewed 122

I am working with 3 pandas dataframes that contain information about differential gene expression for multiple cell groups. It's essentially a multi-dimentional dataframe, where one dataframe (names) is the index of where to look for in the p-value and foldchange dataframes for the corresponding values.

columns = ['g0','g1','g2','g3']
names = pd.DataFrame(data = [
   ['Fxyd3', 'Apoe', 'Apoe', 'Apoe'],
   ['Apoe', 'Hspg2', 'Hspg2', 'Ltbp3'],
   ['Tpm1', 'Ltbp3', 'Ltbp3', 'Hspg2'],
   ['App', 'Serpinh1', 'Fxyd3', 'Fxyd3'],
   ['Ltbp3', 'Fxyd3', 'Serpinh1', 'Lgr5'],
   ['Hspg2', 'Lgr5', 'Lgr5', 'App'],
   ['Slc6a6', 'App', 'App', 'Serpinh1'],
   ['Serpinh1', 'Slc6a6', 'Slc6a6', 'Slc6a6'],
   ['Lgr5', 'Tpm1', 'Tpm1', 'Tpm1'],
   ['Krt15', 'Krt15', 'Krt15', 'Krt15']], 
   columns = columns)

np.random.seed(0)
pvalues = pd.DataFrame(data = np.random.rand(10,4)/100, columns = columns)
foldchanges = pd.DataFrame(data =np.random.rand(10,4)*100, columns = columns)

What I would like to do is to find the minimum p-value for each gene and the corresponding foldchange and group name. After hours of trying to wrap my head around this, I came to this solution:

gene_set = ['Hspg2', 'Ltbp3', 'Lgr5', 'Krt15', 'Serpinh1', 'Tpm1', 'App', 'Apoe', 'Slc6a6', 'Fxyd3']
df = pd.DataFrame(index = gene_set, columns = ['pvalues', 'foldchanges', 'group'], data = 0)

for gene in gene_set:
    bool_df = names.values == gene
    values = pvalues.values[bool_df]
    df['pvalues'].loc[gene] = min(values)
    df['foldchanges'].loc[gene] = foldchanges.values[bool_df][values==min(values)]  
    values = pvalues.T.values[bool_df.T] #Fix to get out correct group name
    df['group'].loc[gene] = columns[np.where(values==min(values))[0][0]]

Resulting in an output like this:

           pvalues  foldchanges group
Hspg2     0.004376    21.038256    g2
Ltbp3     0.000202    65.310833    g0
Lgr5      0.004562    97.676109    g0
Krt15     0.006121    28.280696    g0
Serpinh1  0.005218    83.794491    g0
Tpm1      0.000188    73.926358    g2
App       0.001434    82.099323    g2
Apoe      0.004237    66.676672    g0
Slc6a6    0.001183    19.658236    g0
Fxyd3     0.000710    20.887676    g2

Now, my issue is that in the full dataset, I have 20+ groups and around 50,000 genes, which ends up taking about 15-20 minutes to run. And I would like to run this code for multiple datasets. So I was wondering if there maybe is a more elegant and faster way to achieve the same goal?

Edit: Added random seed for reproducibility and added a fix to get out correct group names

pandas dataframe numpy python
2 Answers

It is essential to have all relevant data in the body of the dataframe and in this sense, the melt() function is an important ally to organize your data.

df_melted = pd.melt(pvalues, var_name="group", value_name="pvalues")
df_melted['foldchanges'] = pd.melt(foldchanges, var_name="group", value_name="foldchanges")['foldchanges']
df_melted['gene'] = pd.melt(names, var_name="group", value_name="gene")['gene']

Now, you can simply do some basic grouping to get the indexes with the minimum pvalues

min_idx = df_melted.groupby(by=["gene"])["pvalues"].idxmin()
out_df = df_melted.iloc[min_idx]

Some formatting to get the output on the format you want

out_df = out_df.set_index('gene').rename_axis(None)[['pvalues', 'foldchanges', 'group']]

And you are good to go

           pvalues  foldchanges group
Apoe      0.004237    66.676672    g0
App       0.001434    82.099323    g2
Fxyd3     0.000710    20.887676    g2
Hspg2     0.004376    21.038256    g2
Krt15     0.006121    28.280696    g0
Lgr5      0.004562    97.676109    g0
Ltbp3     0.000202    65.310833    g0
Serpinh1  0.005218    83.794491    g0
Slc6a6    0.001183    19.658236    g0
Tpm1      0.000188    73.926358    g2
user_24646710

I want to avoid looping to speed up the process. So we recombine the three data frames into a long format. Group them together in a new data frame and aggregate the minimum p-value. Extract a new data frame with the obtained gene names and P-values. What differs from your logic is the timing of the extraction of the group names. The group name corresponding to the P-value is obtained from the beginning. If this approach is wrong, we can only help you speed up the process partially. Thank you for your understanding.

g0 = pd.concat([names['g0'],pvalues['g0'],foldchanges['g0']],axis=1)
g0.columns = ['names','pvalues','foldchanges']
g0['group'] = 'g0'

g1 = pd.concat([names['g1'],pvalues['g1'],foldchanges['g1']],axis=1)
g1.columns = ['names','pvalues','foldchanges']
g1['group'] = 'g1'

g2 = pd.concat([names['g2'],pvalues['g2'],foldchanges['g2']],axis=1)
g2.columns = ['names','pvalues','foldchanges']
g2['group'] = 'g2'

g3 = pd.concat([names['g3'],pvalues['g3'],foldchanges['g3']],axis=1)
g3.columns = ['names','pvalues','foldchanges']
g3['group'] = 'g3'

all_df = pd.concat([g0, g1, g2, g3], axis=0)

gb = all_df.groupby('names')['pvalues'].agg('min').reset_index()
all_df[(all_df['names'].isin(gb['names'])) & (all_df['pvalues'].isin(gb['pvalues']))]

    names   pvalues foldchanges group
1   Hspg2   0.004153    59.926384   g1
3   Serpinh1    0.007515    30.217304   g1
5   Lgr5    0.003352    15.884651   g1
7   Slc6a6  0.003947    99.277559   g1
8   Tpm1    0.000299    36.480099   g1
3   Fxyd3   0.000485    0.583842    g2
6   App 　　0.000566  23.006282   g2
0   Apoe    0.003422    11.763652   g3
1   Ltbp3   0.003203    25.222484   g3
9   Krt15   0.005134    80.433481   g3
user_131078040
Related