Comparing row values in pandas dataframe
Question: I have data in a pandas dataframe where two columns contain numerical
sequences (start and stop). I want to identify which rows have stop values
which overlap with the next rows' start values. Then I need to concatenate
them into a single row so that I only have single none-overlapping numerical
sequences represented by my start and stop values in each row.

I have loaded my data into a pandas dataframe:

>
>       chr     start       stop        geneID
>     0 chr13   32889584    32889814    BRCA2
>     1 chr13   32890536    32890737    BRCA2
>     2 chr13   32893194    32893307    BRCA2
>     3 chr13   32893282    32893400    BRCA2
>     4 chr13   32893363    32893466    BRCA2
>     5 chr13   32899127    32899242    BRCA2
>  

I want to compare the rows in the dataframe. Check whether the stop value for
each row is less than the start value for the following row and then create a
row in a new dataframe with the correct start and stop values. Ideally when
there are several rows which all overlap this would be concatenated all in one
go, however I suspect I will have to iterate over my output until this doesn't
happen any more.

My code so far can identify whether there is an overlap (adapted from [this
post](http://stackoverflow.com/questions/19409335/comparing-pandas-dataframe-
rows-dropping-rows-with-overlapping-dates)):

    
    
    import pandas as pd
    import numpy as np
    
    columns = ['chr','start','stop','geneID']
    bed = pd.read_table('bedfile.txt',sep='\s',names=['chr','start','stop','geneID'],engine='python')
    
    def bed_prepare(inp_bed):
        inp_bed['next_start'] = inp_bed['start'].shift(periods=-1)
        inp_bed['distance_to_next'] = inp_bed['next_start'] - inp_bed['stop']
        inp_bed['next_region_overlap'] = inp_bed['next_start'] < inp_bed['stop']
        intermediate_bed = inp_bed
        return intermediate_bed
    

And this gives me output like this:

    
    
    print bed_prepare(bed)
    

>
>            chr     start      stop geneID  next_start  distance_to_next
> next_region_overlap
>     0    chr13  32889584  32889814  BRCA2    32890536               722
> False
>     1    chr13  32890536  32890737  BRCA2    32893194              2457
> False
>     2    chr13  32893194  32893307  BRCA2    32893282               -25
> True
>     3    chr13  32893282  32893400  BRCA2    32893363               -37
> True
>     4    chr13  32893363  32893466  BRCA2    32899127              5661
> False
>  

I want to put this intermediate dataframe into the following function in order
get the desired output (shown below):

    
    
    new_bed = pd.DataFrame(data=np.zeros((0,len(columns))),columns=columns)
    
    def bed_collapse(intermediate_bed, new_bed,columns=columns):
        for row in bed.itertuples():
        output = {}
            if row[7] == False:
                # If row doesn't overlap next row, insert into new dataframe unchanged.                                   
                output_row = list(row[1:5])
            if row[7] == True:
                # For overlapping rows take the chromosome and start coordinate                                           
                output_row = list(row[1:3])
                # Iterate to next row                                                                                     
                bed.itertuples().next()
                # Append stop coordinate and geneID                                                                       
                output_row.append(row[3])
            output_row.append(row[4])
            #print output_row                                                                                             
            for k, v in zip(columns,output_row): otpt[k] = v
            #print output                                                                                                 
            new_bed = new_bed.append(otpt,ignore_index=True)
        output_bed = new_bed
        return output_bed
    
    
    int_bed = bed_prepare(bed)
    print bed_collapse(int_bed,new_bed)
    

Desired output:

>
>       chr     start       stop        geneID
>     0 chr13   32889584    32889814    BRCA2
>     1 chr13   32890536    32890737    BRCA2
>     2 chr13   32893194    32893466    BRCA2
>     5 chr13   32899127    32899242    BRCA2
>  

However, when I run the function I get my original dataframe back unchanged. I
know that the problem is when I try to call bed.itertuples().next(), as this
is clearly not quite the right syntax/location for the call. But I don't know
the correct way to rectify this.

Some pointers would be great.

SB :)

## Update

This is a [BED file](http://genome.ucsc.edu/FAQ/FAQformat.html#format1) where
each row refers to an amplicon (genomic region) with start and stop
coordinates. Some of the amplicons overlap; ie the start coordinate is before
the stop coordinate on the previous row. Therefore I need to identify which
rows overlap and concatenate the correct starts and stops so that each row
represents and entirely unique amplicon which doesn't overlap any other row.


Answer: I will try to give you some pointers.

One pointer is that you want the get the rows based on a Series consisting of
booleans that is shifted. Probably you can get a new shifted Series using:

    
    
    Boolean_Series = intermediate_bed.loc[:,'next_region_overlap'].shift(periods=1, freq=None, axis=0, **kwds)
    

More background about this function: <http://pandas.pydata.org/pandas-
docs/dev/generated/pandas.DataFrame.shift.html>

Second pointer is that by using this shifted Series you can get your Dataframe
by:

    
    
    int_bed = bed.loc[Boolean_Series, :] 
    

More about indexing can be found here: <http://pandas.pydata.org/pandas-
docs/dev/indexing.html>

These are only pointers now, I do not know if this an actual working solution.

