問題：我有 2 個數(shù)據(jù)框；df1 具有線圈 ID、樣本因子、序列。每個coil_id 有449 條記錄（范圍1-499）并且有大約1000 個唯一的coil_id。df2 具有線圈 ID、樣本、儀表。每個coil_id 大約有500 條記錄（范圍10-5000；可以更少）并且具有與df1 中相同的1000 個唯一coil_id。df1：+-------+-----------------|coil_id|sample_factor|SEQ+-------+-----------------|E101634|10.4066 | 1|E101634|20.8132 | 2|E101634|31.2198 | 3 |E101634|41.6264 | 4|E101634|5220.033 |449df2：+-------+------+------+--|coil_id|SAMPLE|GAUGE |+-------+------+------+--|E101634| 10|0.0565||E101634| 20|0.0569||E101634| 30|0.0567||E101634| 40|0.0561||E101634| 5000| 0.055|由于記錄數(shù)不同，我無法加入兩個表。如果我這樣做，我的樣本值和儀表會發(fā)生變化。所以我不應(yīng)該加入。接下來，我需要檢查df1.sample_factor是否位于 df2.sample 和 df2.sample+1 之間，然后對gauge進(jìn)行計算。示例：（如果 10.4 位于 10 和 20 之間，則0.0565+(((0.0569-0.0565)/10)*(10.4-10))）基本上按比例分配儀表。我想從 df1 中的 Sample_factor 迭代每一行，并檢查它是否位于 df2 中的 sample[i] 和 sample[i+1] 之間。然后對儀表執(zhí)行按比例計算并將結(jié)果添加到 df1。我試過這個：def new_gauge : for row in df1('sample_factor'): if df1['sample_factor'] > df2['sample'] and df1['sample_factor'] < df2['sample'] + 1: return df2['gauge']+(((df2['gauge']+1)-df2['gauge'])/10)*(df1['sample_factor']-df2['sample']))df1['new_gauge'] = df1.apply(new_gauge)我知道它在語法上絕對錯誤，只是為了了解我想要什么。任何幫助表示贊賞。謝謝：）輸出：
1 回答
慕少森
TA貢獻(xiàn)2019條經(jīng)驗 獲得超9個贊
這是與您的預(yù)期輸出相匹配的起始樣本數(shù)據(jù)
df1
coil_id sample_factor SEQ
0 E101634 10.4066 1
1 E101634 20.8132 2
2 E101634 31.2198 3
3 E101634 41.6264 4
4 E101634 52.0330 5
5 E101634 62.4396 6
6 E101634 5220.0330 449
df2
coil_id SAMPLE GAUGE
0 E101634 10 0.0550
1 E101634 20 0.0568
2 E101634 30 0.0543
3 E101634 40 0.0531
4 E101634 50 0.0529
5 E101634 60 0.0519
第一步是merge_asof將樣本因子帶到最接近的樣本。然后計算new_gauge每一行的列。但是，我們只會在 sample_factor 介于其當(dāng)前行和下一行的值之間并且線圈 ID 與其和下一行的值相同時才實際分配一個值。
import pandas as pd
merged = pd.merge_asof(df2.assign(SAMPLE = df2.SAMPLE.astype('float')).sort_values('SAMPLE'),
df1.sort_values('sample_factor'),
by='coil_id',
left_on='SAMPLE',
right_on='sample_factor',
direction='forward')
print(merged)
# coil_id SAMPLE GAUGE sample_factor SEQ
#0 E101634 10.0 0.0550 10.4066 1
#1 E101634 20.0 0.0568 20.8132 2
#2 E101634 30.0 0.0543 31.2198 3
#3 E101634 40.0 0.0531 41.6264 4
#4 E101634 50.0 0.0529 52.0330 5
#5 E101634 60.0 0.0519 62.4396 6
# Now perform your calculation:
new_gauge = (merged.GAUGE.shift(1)
+ ((merged.GAUGE - merged.GAUGE.shift(1))/10
* (merged.sample_factor - merged.SAMPLE.shift(1))))
# Assign it only where it makes sense
# Assumes df2 was sorted on ['coil_id', 'SAMPLE']
mask = (merged.sample_factor.between(merged.SAMPLE, merged.SAMPLE.shift(-1))
& (merged.coil_id == merged.coil_id.shift(-1)))
merged.loc[mask, 'new_gauge'] = new_gauge[mask]
輸出： merged
coil_id SAMPLE GAUGE sample_factor SEQ new_gauge
0 E101634 10.0 0.0550 10.4066 1 NaN
1 E101634 20.0 0.0568 20.8132 2 0.056946
2 E101634 30.0 0.0543 31.2198 3 0.053995
3 E101634 40.0 0.0531 41.6264 4 0.052905
4 E101634 50.0 0.0529 52.0330 5 0.052859
5 E101634 60.0 0.0519 62.4396 6 NaN
在這種情況下，我們沒有分配最后一行，因為您提供的子集中沒有樣本 > 60。
添加回答
舉報
0/150
提交
取消