比较pandas datafram中的行值

Question

我有一个pandas数据框中的数据，其中两列包含数字序列（start和stop）。我想确定哪些行的停止值与下一行的开始值重叠。然后我需要将它们连接成一行，这样在每行中只有一个由起始值和终止值表示的无重叠的数字序列。

我已将数据加载到熊猫数据框中：

  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

我想比较数据框中的行。检查每一行的停止值是否小于下一行的开始值，然后在具有正确的开始值和停止值的新数据框中创建一行。理想情况下，当有多个行重叠时，这将一次连接所有行，但是我怀疑我将不得不迭代我的输出，直到不再发生这种情况。

到目前为止，我的代码可以识别是否存在重叠（改编自this post）：

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

这给了我这样的输出：

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

我想将这个中间数据帧放入以下函数中，以便获得所需的输出（如下所示）：

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)

期望输出：

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

但是，当我运行这个函数时，我得到的原始数据帧没有改变。我知道问题是当我试图调用bed.itertuples（）.next（）时，因为这显然不是调用的正确语法/位置。但我不知道该怎么纠正。

一些指针会很好。

某人：）

更新

这是一个BED file，其中每一行都指向一个带有起始和终止坐标的扩增子（基因组区域）。有些放大器重叠（起始坐标在前一行的终止坐标之前）。因此，我需要确定哪些行重叠，并连接正确的开始和结束，以便每一行代表和完全唯一的放大器，不重叠任何其他行。

Answer 1

我不确定我是否理解您为什么要做您正在做的事情，但是您可以通过简单地使用索引来获得所需的输出。e、 g

# assume your data is stored in <df>
# call the temporary dataframe <tmp>
tmp = df[ ['chr','start','stop','geneID'] ][(df.stop - df.start.shift(-1))>0]

这就是你最终想要做的吗？

更新 好吧，我知道你在做什么了。请记住，我从来没有处理过任何基因组数据，所以我不知道你的列中有多少行如此简单的“循环”可能相当慢（如果你有几十亿行这可能需要一段时间），但这是唯一想到的解决方案。首先要想到的是（注意：这不是一个成品，因为您需要确定如何处理引入的NaN以及如何处理循环终止）。

import pandas as pd

df = pd.DataFrame(index = [0,1,2,3,4,5],columns=['chr','start','stop','geneID'])

df['chr']    = np.array( ['chr13']*6 )
df['start']  = np.array( [32889584,32890536,32893194,32893282,32893363,32899127] )
df['stop']   = np.array( [32889814,32890737,32893307,32893400,32893466,32899242] )
df['geneID'] = np.array( ['BRCA2']*6 )

# calculate difference between start/stop times for adjacent rows
# this will effectively "look into the future" to see if the upcoming row has 
# a start time that is greater than the current stop time
df['tdiff'] = (df.start - df.stop.shift(1)).shift(-1)

# create new dataframe
df_cut = df.copy()*0

r = 0
while r < df.shape[0]:
    if df.tdiff[r] > 0:
        df_cut.iloc[r] = df.iloc[r]
        r+=1

    elif df.tdiff.iloc[r] < 0: # have to determine how you will handle the NaN's later
        df_cut.chr.iloc[r] = df.chr.iloc[r]
        df_cut.start.iloc[r] = df.start.iloc[r]
        df_cut.geneID.iloc[r] = df.geneID.iloc[r]

        # get the next-valid row and put "stop" value into <df_cut>
        df_cut.stop.iloc[r] = df.ix[r:][df.tdiff>0].stop.iloc[0]

        # determine new index location for <r>
        r = df.ix[r:][df.tdiff>0].index[0] + 1

# eliminate empty rows
df_cut = df_cut[df_cut.start<>0]

运行后：

>>> df_cut
     chr     start      stop geneID  tdiff
0  chr13  32889584  32889814  BRCA2    722
1  chr13  32890536  32890737  BRCA2   2457
2  chr13  32893194  32893466  BRCA2     -0

Answer 2

我会给你一些建议。

一个指针是，您希望基于一个由移位的布尔值组成的序列获取行。可能您可以使用以下方法获得新的移位序列：

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

有关此功能的更多背景信息： http://pandas.pydata.org/pandas-docs/dev/generated/pandas.DataFrame.shift.html

第二个指针是，通过使用此移位序列，可以通过以下方式获取数据帧：

int_bed = bed.loc[Boolean_Series, :] 

有关索引的详细信息，请访问： http://pandas.pydata.org/pandas-docs/dev/indexing.html

这些只是指针，我不知道这是否是一个实际的工作解决方案。

Answer 3

我修改了bed_prepare功能，以检查上一个和下一个基因组区域的重叠：

def bed_prepare(inp_bed):
    ''' Takes pandas dataframe bed file and identifies which regions overlap '''
    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']
    inp_bed['previous_stop'] = inp_bed['stop'].shift(periods=1)
    inp_bed['distance_from_previous'] = inp_bed['start'] - inp_bed['previous_stop']
    inp_bed['previous_region_overlap'] = inp_bed['previous_stop'] >= inp_bed['start']
    intermediate_bed = inp_bed
    return intermediate_bed

然后，我使用它们的布尔输出来存储用于写入步骤的变量：

# Create empty dataframe to fill with parsed values                                                                   
new_bed = pd.DataFrame(data=np.zeros((0,len(columns))),columns=columns,dtype=int)

def bed_collapse(intermediate_bed, new_bed,columns=columns):
    ''' Takes a pandas dataframe bed file with overlap information and returns                                        
    genomic regions without overlaps '''
    output_row = []
    for row in bed.itertuples():
        output = {}
        if row[7] == False and row[10] == False:
            # If row doesn't overlap next row, insert into new dataframe unchanged.                                   
            output_row = list(row[1:5])
        elif row[7] == True and row[10] == False:
            # Only next region overlaps; take the chromosome and start coordinate                                     
            output_row = list(row[1:3])
        elif row[7] == True and row[10] == True:
            # Next and previous regions overlap. Skip row.                                                            
            pass
        elif row[7] == False and row[10]  == True:
            # Only previous region overlaps; append stop coordinate and geneID to output_row variable                 
            output_row.append(row[3])
            output_row.append(row[4])
        if row[7] == False:
            #Zip columns and output_row values together to form a dict for appending                                  
            for k, v in zip(columns,output_row): output[k] = v
            #print output                                                                                             
            new_bed = new_bed.append(output,ignore_index=True)
    output_bed = new_bed
    return output_bed

这已经解决了我的问题，并给出了问题中指定的所需输出。：）