Filtering a dataframe from information in another dataframe using Pandas

Question

I have a dataframe that is below.

df = pd.DataFrame(columns=['Chromosome', 'Start','End'],
     data=[
           ['chr1', 2000, 3000],
           ['chr1', 500, 1500],
           ['chr3', 3000, 4000],
           ['chr5', 4000, 5000],
           ['chr17', 9000, 10000],
           ['chr19', 1500, 2500]
           ])

I have a probe dataframe as below.

probes = pd.DataFrame(columns=['Probe', 'Chrom','Position'],
     data=[
           ['CG999', 'chr1', 2500],
           ['CG000', 'chr19, 2000],
           ])

I want to filter df for rows which contains a probes chromosome and which has the probes position between it's Start and End numbers, then add the probes name to a new column/field in df. The desired output is below:

    Probe    Chrom    Start    End
0   CG999    chr1     2000     3000
5   CG000    chr19    1500     2500

My attempt below works but doesn't place the probe name into a Probe column and is reliant on looping probes data. There must be a more efficient way of doing this.

all_indexes = []

# fake2.tsv is the aforementioned probes dataframe
with open('fake2.tsv') as f:
    for x in f:
        probe, chrom, pos = x.rstrip("\n").split("\t")
        row = df[(df['Chromosome'] == chrom) & ((int(pos) > df['Start']) & (int(pos) < df['End']))]
        all_indexes.append(t.index.tolist())

all_t = [y for x in all_t for y in x]
df.iloc[all_indexes]

Scott Boston · Accepted Answer · 2017-06-15 15:40:20Z

5

You can try this:

df.merge(probes, left_on='Chromosome', right_on='Chrom').query('Start < Position < End')

Output:

  Chromosome  Start   End  Probe  Chrom  Position
0       chr1   2000  3000  CG999   chr1      2500
2      chr19   1500  2500  CG000  chr19      2000

answered Jun 15, 2017 at 15:40

Scott Boston

154k15 gold badges160 silver badges207 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Dimgold · Accepted Answer · 2017-06-15 15:33:09Z

1

I just encountered the same problem, and apparently there is no built-in solution in pandas. However you may use of the solutions on following threads:

answered Jun 15, 2017 at 15:33

Dimgold

2,9445 gold badges30 silver badges50 bronze badges

Collectives™ on Stack Overflow

Filtering a dataframe from information in another dataframe using Pandas

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related