我一直在使用iterrows()比較兩個數據集之間的列值并合并滿足某些條件的行，但是這需要很長時間。有沒有更好的方法可以在不迭代的情況下做到這一點？這是完整的功能def find_peak_matches(lncRNA, CAGE):"""isolates CAGE peaks that match an lncRNA"""lncRNA['promoter_start'] = lncRNA['promoter_start'].apply(pd.to_numeric).astype('int32')lncRNA['promoter_stop'] = lncRNA['promoter_stop'].apply(pd.to_numeric).astype('int32')CAGE['peak_start'] = CAGE['peak_start'].apply(pd.to_numeric).astype('int32')CAGE['peak_stop'] = CAGE['peak_stop'].apply(pd.to_numeric).astype('int32')peak_matches = pd.DataFrame()for i, row in lncRNA.iterrows(): mask = ( (CAGE['chr'] == row['chr']) & \ (row['promoter_start'] <= CAGE['peak_start']) & \ (row['promoter_stop'] >= CAGE['peak_stop']) )#finds peaks in lncRNA promoters matches = CAGE[mask].dropna() #isolates only the peak matches if len(matches) == 0: #if no matches found continue continue merged = pd.merge( row.to_frame().T, matches, on=['chr'] ) #merges rows that meet mask conditions peak_matches = pd.concat( [peak_matches, merged], ignore_index=True ) #creates a new df from all the merged rows logging.debug('found peak matches')return (peak_matches)這是一個示例 lncRNA 數據集：name chr promoter_start promoter_stop infolnc1 1 1 10 xlnc2 1 11 20 ylnc3 1 21 30 z樣品籠：ID chr peak_start peak_stop peak1 1 3 7 peak2 1 15 17 peak3 1 4 6 peak4 2 6 9 真實的數據集每個包含大約 20 萬行，所以我當前的代碼花費的時間太長了。我正在嘗試合并峰值/啟動子具有相同 chr 值且峰值開始/停止落在啟動子啟動/停止之間的行。有什么優化建議嗎？我對 python 相當了解，所以我不知道最好的做事方式是什么。
2 回答
呼啦一陣風
TA貢獻1802條經驗 獲得超6個贊
在將數據放入pandas可以對需要進行的比較次數進行更多選擇的地方之前，您可能會更幸運地完成所有繁重的工作——盡管事實上您會放棄一些 numpy 加速劑在pandas。namedtuples為了方便起見，我編寫了下面的示例，并在制作數據框之前進行了所有比較。對于 200K x 200K 的虛假數據，它在我的機器上大約需要 30 秒就可以完成，并獲得 1000 萬行匹配項，這完全取決于我使用的隨機數據的多樣性。YMMV。
這里可能還有更多“留在地板上”。一些智能排序（除了我所做的按“chr”分箱）可能會更進一步。
import pandas as pd
from collections import namedtuple, defaultdict
from random import randint
from itertools import product
# structures
rna = namedtuple('rna', 'name chr promoter_start promoter_stop info')
cage = namedtuple('cage', 'ID chr peak_start peak_stop')
row = namedtuple('row', 'name chr promoter_start promoter_stop info ID peak_start peak_stop')
# some data entry from post to check...
rnas = [rna('inc1',1,1,10,'x'), rna('inc2',1,11,20,'y'), rna('inc1',1,21,30,'z')]
cages = [cage('peak1',1,3,7), cage('peak2',1,15,17), cage('peak3',1,4,6), cage('peak4',2,6,9)]
result_rows = [row(r.name, r.chr, r.promoter_start, r.promoter_stop, r.info, c.ID, c.peak_start, c.peak_stop)
for r in rnas for c in cages if
r.chr == c.chr and
r.promoter_start <= c.peak_start and
r.promoter_stop >= c.peak_stop]
df = pd.DataFrame(data=result_rows)
print(df)
print()
# stress test
# big fake data
rnas = [rna('xx', randint(1,1000), randint(1,50), randint(10,150), 'yy') for t in range(200_000)]
cages = [cage('pk', randint(1,1000), randint(1,50), randint(10,150)) for t in range(200_000)]
# group by chr to expedite comparisons
rna_dict = defaultdict(list)
cage_dict = defaultdict(list)
for r in rnas:
rna_dict[r.chr].append(r)
for c in cages:
cage_dict[c.chr].append(c)
print('fake data made')
# use the chr's that are keys in the rna dictionary and make all comparisions...
result_rows = []
for k in rna_dict.keys():
result_rows.extend([row(r.name, r.chr, r.promoter_start, r.promoter_stop, r.info, c.ID, c.peak_start, c.peak_stop)
for r in rna_dict.get(k) for c in cage_dict.get(k) if
r.promoter_start <= c.peak_start and
r.promoter_stop >= c.peak_stop])
df = pd.DataFrame(data=result_rows)
print(df.head(5))
print(df.info())
輸出：
name chr promoter_start promoter_stop info ID peak_start peak_stop
0 inc1 1 1 10 x peak1 3 7
1 inc1 1 1 10 x peak3 4 6
2 inc2 1 11 20 y peak2 15 17
fake data made
name chr promoter_start promoter_stop info ID peak_start peak_stop
0 xx 804 34 35 yy pk 36 11
1 xx 804 34 35 yy pk 39 11
2 xx 804 34 35 yy pk 37 14
3 xx 804 34 35 yy pk 34 28
4 xx 804 34 35 yy pk 39 20
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 10280046 entries, 0 to 10280045
Data columns (total 8 columns):
name object
chr int64
promoter_start int64
promoter_stop int64
info object
ID object
peak_start int64
peak_stop int64
dtypes: int64(5), object(3)
memory usage: 627.4+ MB
None
[Finished in 35.4s]
來自 DataFrame --> namedtuple
下面的幾個選項......研究了同樣的事情并選擇了幾個例子。您可以使用pd.itertuples下面的方法將它們剝離出來并將它們放入命名元組中。但是，它似乎只進行位置匹配。所以要小心。注意第二個例子是頂起的。Pandas 似乎也做自己的命名行事情，這可能同樣有效。（最后一個例子）。我沒有對它進行太多修改，但它似乎可以在內部通過名稱尋址，這就像 namedtuple 一樣好。
In [22]: df
Out[22]:
name chr promoter_start promoter_stop info
0 lnc1 1 1 10 x
1 lnc2 1 11 20 y
2 lnc3 1 21 30 z
In [23]: rna = namedtuple('rna', 'name chr promoter_start promoter_stop info')
In [24]: rows = [rna(*t) for t in df.itertuples(index=False)]
In [25]: rows
Out[25]:
[rna(name='lnc1', chr=1, promoter_start=1, promoter_stop=10, info='x'),
rna(name='lnc2', chr=1, promoter_start=11, promoter_stop=20, info='y'),
rna(name='lnc3', chr=1, promoter_start=21, promoter_stop=30, info='z')]
In [26]: rna = namedtuple('rna', 'name chr info promoter_start promoter_stop') # note: wrong
In [27]: rows = [rna(*t) for t in df.itertuples(index=False)]
In [28]: rows
Out[28]:
[rna(name='lnc1', chr=1, info=1, promoter_start=10, promoter_stop='x'),
rna(name='lnc2', chr=1, info=11, promoter_start=20, promoter_stop='y'),
rna(name='lnc3', chr=1, info=21, promoter_start=30, promoter_stop='z')]
In [29]: # note the above is mis-aligned!!!
In [32]: rows = [t for t in df.itertuples(name='row', index=False)]
In [33]: rows
Out[33]:
[row(name='lnc1', chr=1, promoter_start=1, promoter_stop=10, info='x'),
row(name='lnc2', chr=1, promoter_start=11, promoter_stop=20, info='y'),
row(name='lnc3', chr=1, promoter_start=21, promoter_stop=30, info='z')]
In [34]: type(rows[0])
Out[34]: pandas.core.frame.row
In [35]: rows[0].chr
Out[35]: 1
In [36]: rows[0].info
Out[36]: 'x'
守著星空守著你
TA貢獻1799條經驗 獲得超8個贊
您可以合并所有lncRNA和CAGE數據框，然后使用df.query.
例如：
df = lncRNA.merge(CAGE, on='chr')
df = df.query('(promoter_start <= peak_start) & (promoter_stop >= peak_stop)')
print(df)
印刷：
name chr promoter_start promoter_stop info ID peak_start peak_stop
0 lnc1 1 1 10 x peak1 3 7
2 lnc1 1 1 10 x peak3 4 6
4 lnc2 1 11 20 y peak2 15 17
添加回答
舉報
0/150
提交
取消