我有這樣的數據集number Shipment Date service desc amount182692345 2/12/19 DUTIES & TAXES IMPORT EXPORT DUTIES 561.01 IMPORT EXPORT TAXES 600.471827975839 2/12/19 DUTIES & TAXES IMPORT EXPORT DUTIES 160.193229475633 2/12/19 DUTIES & TAXES IMPORT EXPORT TAXES 600.47 IMPORT EXPORT DUTIES 561.015733894261 29/04/2020 Express DUTIES TAXES PAID 25 FUEL SURCHARGE 3.281826995520 2/12/19 DUTIES & TAXES IMPORT EXPORT TAXES 600.47 IMPORT EXPORT DUTIES 561.012998455062 4/5/20 Express FUEL SURCHARGE 0.72在 pic 格式中，它如下所示：我想要的是，對于存在 number 和 shipment_date 的行，我們檢查“Express”所在的服務。然后對于這樣的行，我想將 desc col 中的“燃油附加費”行拉到與數字相同的行以及shipment_date相應的金額值。所以像下面這樣：number Shipment Date service desc amount182692345 2/12/19 DUTIES & TAXES IMPORT EXPORT DUTIES 561.01 IMPORT EXPORT TAXES 600.471827975839 2/12/19 DUTIES & TAXES IMPORT EXPORT DUTIES 160.193229475633 2/12/19 DUTIES & TAXES IMPORT EXPORT TAXES 600.47 IMPORT EXPORT DUTIES 561.015733894261 29/04/2020 Express FUEL SURCHARGE 3.28 DUTIES TAXES PAID 25 1826995520 2/12/19 DUTIES & TAXES IMPORT EXPORT TAXES 600.47 IMPORT EXPORT DUTIES 561.012998455062 4/5/20 Express FUEL SURCHARGE 0.72看起來像下面的圖片格式。最后我只關心服務是“Express”的行，所以如果我們去掉所有服務不是 express 的行并獲得上面的格式（僅適用于 Express 值），那將是理想的。我認為 pandasffill()和 transform 將是主要工具。所以我正在嘗試以下內容：df1=df.copy()df1[['number', 'shipment_date']]=df1[['number', 'shipment_date']].ffill()df1.desc=df1.desc.fillna('')df1.amount= df1.amount.fillna('')s= df1.groupby(['number', 'shipment_date']).amount.transform(lambda x: ' '.join(str(x)))df.loc[df.shipment_date.notnull(),'amount']=sdf.loc[df.shipment_date.isnull(),'amount']=''
3 回答
慕婉清6462132
TA貢獻1804條經驗 獲得超2個贊
空行填寫fillna(method='ffill')，由服務提取，由 獲取shift(-1)。這是否符合問題的意圖？
df['service'] = df['service'].fillna(method='ffill')
df = df[df['service'] == 'Express']
df[['number','Shipment Date']] = df[['number','Shipment Date']].fillna(method='ffill')
df[['desc','amount']] = df[['desc','amount']].shift(-1)
df
number Shipment Date service desc amount
8 5.733894e+09 29/04/2020 Express DUTIES TAXES PAID 25.00
9 5.733894e+09 29/04/2020 Express FUEL SURCHARGE 3.28
10 5.733894e+09 29/04/2020 Express NaN NaN
14 2.998455e+09 4/5/20 Express FUEL SURCHARGE 0.72
15 2.998455e+09 4/5/20 Express NaN NaN
慕碼人8056858
TA貢獻1803條經驗 獲得超6個贊
從邏輯上講，您有一個經典的主/詳細數據集。您的詳細數據集沒有主記錄的外鍵。添加 FK，然后您可以對 master 進行過濾條件，對 detail 進行過濾條件并將 FK 加入 PK
已經修改了源數據，使得從中構建 DF 變得簡單
填充詳細記錄的 FK
fillna選擇主記錄和明細記錄并將它們加入 PK/FK
import numpy as np
data = '''number Shipment Date service desc amount
182692345 2/12/19 DUTIES & TAXES
- - IMPORT EXPORT DUTIES 561.01
- - IMPORT EXPORT TAXES 600.47
1827975839 2/12/19 DUTIES & TAXES
- - IMPORT EXPORT DUTIES 160.19
3229475633 2/12/19 DUTIES & TAXES
- - IMPORT EXPORT TAXES 600.47
- - IMPORT EXPORT DUTIES 561.01
5733894261 29/04/2020 Express
- - DUTIES TAXES PAID 25
- - FUEL SURCHARGE 3.28
1826995520 2/12/19 DUTIES & TAXES
- - IMPORT EXPORT TAXES 600.47
- - IMPORT EXPORT DUTIES 561.01
2998455062 4/5/20 Express
- - FUEL SURCHARGE 0.72'''
da = [[i for i in re.split("[ ][ ]+", l)] for l in data.split("\n")]
dfall = pd.DataFrame(da[1:], columns=da[0])
dfall["number"][dfall["number"]==""] = np.NaN
dfall = dfall.fillna(method="ffill")
pd.concat([dfall[dfall["desc"]=="FUEL SURCHARGE"], dfmaster[dfall["service"]=="Express"] ],
join="inner", keys="number"
).sort_values(by=["number","service"], ascending=[True,False])
慕娘9325324
TA貢獻1783條經驗 獲得超4個贊
您可以向前填充service列中的缺失值，然后比較列表中的Express和最后shift匹配的行和列 by DataFrame.shiftand DataFrame.loc：
mask = df['service'].ffill().eq('Express')
df.loc[mask, ['desc','amount']] = df.loc[mask, ['desc','amount']].shift(-1)
print (df)
number Shipment Date service desc \
0 182692345 2/12/19 DUTIES & TAXES
1 NaN NaN IMPORT EXPORT DUTIES
2 NaN NaN IMPORT EXPORT TAXES
3 1827975839 2/12/19 DUTIES & TAXES
4 NaN NaN IMPORT EXPORT DUTIES
5 3229475633 2/12/19 DUTIES & TAXES
6 NaN NaN IMPORT EXPORT TAXES 600.47
7 NaN NaN IMPORT EXPORT DUTIES
8 5733894261 29/04/2020 Express DUTIES TAXES PAID
9 NaN NaN FUEL SURCHARGE
10 NaN NaN
11 1826995520 2/12/19 DUTIES & TAXES
12 NaN NaN IMPORT EXPORT TAXES
13 NaN NaN IMPORT EXPORT DUTIES
14 2998455062 4/5/20 Express FUEL SURCHARGE
15 NaN NaN NaN
amount
0 None
1 561.01
2 600.47
3 None
4 160.19
5 None
6 None
7 561.01
8 25
9 3.28
10 None
11 None
12 600.47
13 561.01
14 0.72
15 NaN
添加回答
舉報
0/150
提交
取消