Finding overlap in range based on multiple dataframe column values

user_5463597 0
Viewed 44

I have a TSV that looks as follows:

chr_1   start_1 chr_2   start_2
11  69633786    14  105884873
12  81940993    X   137690551
13  29782093    12  97838049
14  105864244   11  69633799
17  33207000    20  9992701
17  38446991    20  2102271
17  38447482    17  29623333
20  9992701 17  33207000
20  10426599    17  33094167
20  13765533    17  29469669
22  27415959    8   36197094
22  37191634    8   38983042
22  44464751    18  74004141
8   36197054    22  23130534
8   36197054    22  23131537
8   36197054    8   23130539

This will be referred to as transDiffStartEndChr, which is a Dataframe.

I am working on a program that takes this TSV as input, and outputs rows that have the same chr_1 and chr_2, and a start_1 and start_2 that are +/- 1000.

Ideal output would look like:

chr_1   start_1 chr_2   start_2

8   36197054    8   23130539
8   36197054    22  23131537

Potentially creating groups for every hit based on chr_1 and chr_2.

My current script/thoughts:

transDiffStartEndChr = pd.read_csv('test-input.tsv', sep='\t')


#I will extract rows first by chr_1, in this case I'm doing a test case for 17. 
rowsStartChr17 = transDiffStartEndChr[transDiffStartEndChr.apply(extractChr, chr='17', axis=1)]

#I figure I can do something stupid and using brute force, but I feel like I'm not tackling this problem correctly
for index, row in rowsStartChr17.iterrows():
    for index2, row2 in rowsStartChr17.iterrows():
        if index == index2:
            continue
        elif row['chr_1'] == row2['chr_1'] and row['chr_2'] == row2['chr_2']:
            if proximityCheck(row['start_1'], row2['start_1']) and proximityCheck(row['start_2'], row2['start_2']):
                print(f'Row: {index} Match: {index2}')

Any thoughts are appreciated.

pandas dataframe overlap python
1 Answers

Can play with numpy and pandas to filter out the groups that don't match your requirements.

>>> df.groupby(['chr_1', 'chr_2'])\
      .filter(lambda s: len(np.array(np.where(
                                     np.tril(
                                     np.abs(
                                     np.subtract.outer(s['start_2'].values, 
                                                       s['start_2'].values)) < 1500  , -1)))\
                                       .flatten()) > 0)

The logic is to groupby chr_1 and chr_2 and perform an outer subtraction between start_2 values to check whether we can values below 1500 (the threshold I used).

user_25356110
Related