我有一個 csv 如下+-----+---------+-----------+------------+| ID | version | Name | State |+-----+---------+-----------+------------+| 101 | 1 | Nut | In-Transit || 101 | 1 | Nut | Cancelled || 101 | 1 | Nut | Delivered || 101 | 2 | Nut 2.0 | In-Transit || 102 | 1 | Screw | Shipped || 102 | 1 | Screw | In-Transit || 102 | 2 | Screw 2.0 | Shipped || 102 | 2 | Screw 2.0 | Cancelled |+-----+---------+-----------+------------+現在我想在每個 ID 和版本組合的所有可用狀態中采用最高狀態（基于以下優先級）。我的定制訂單發表在途中發貨取消預期產出+-----+---------+-----------+------------+| ID | version | Name | State |+-----+---------+-----------+------------+| 101 | 1 | Nut | Delivered || 101 | 2 | Nut 2.0 | In-Transit || 102 | 1 | Screw | In-Transit || 102 | 2 | Screw 2.0 | Shipped |+-----+---------+-----------+------------+我試過下面的查詢但沒有工作。我是 python 的新手，我不確定如何解決這個問題。import pandas as pdmydata = pd.read_csv('C:/Mypython/Newyork',encoding = "ISO-8859-1")mydata['state'] = pd.Categorical(mydata['state'], ["Delivered","In-Transit","Shipped","Cancelled"])mydate.sort_values('state').drop_duplicates(['ID','VERSION'],keep='first')
2 回答
蝴蝶刀刀
TA貢獻1801條經驗 獲得超8個贊
對我來說工作正常，似乎沒有分配回新變量：
mydata['State'] = pd.Categorical(mydata['State'],
["Delivered", "In-Transit", "Shipped", "Cancelled"],
ordered=True)
#keep='first'is default value, so should be omitted
mydata = mydata.sort_values('state').drop_duplicates(['ID','version'])
print (mydata)
ID version Name state
2 101 1 Nut Delivered
3 101 2 Nut 2.0 In-Transit
5 102 1 Screw In-Transit
6 102 2 Screw 2.0 Shipped
此外，如果想要按 排序輸出ID，version請按多列添加排序：
mydata['State'] = pd.Categorical(mydata['State'],
["Delivered", "In-Transit", "Shipped", "Cancelled"],
ordered=True)
mydata = mydata.sort_values(['ID','version','state']).drop_duplicates(['ID','version'])
12345678_0001
TA貢獻1802條經驗 獲得超5個贊
使用pd.Categoricalwithordered=True創建一個分類變量，然后sort_values在這個分類變量上使用groupbyonID, version和aggusing first：
mydata['State'] = pd.Categorical(mydata['State'], ["Delivered", "In-Transit", "Shipped", "Cancelled"], ordered=True)
df = mydata.sort_values('State').groupby(['ID', 'version'], as_index=False).first()
結果：
ID version Name State
0 101 1 Nut Delivered
1 101 2 Nut 2.0 In-Transit
2 102 1 Screw In-Transit
3 102 2 Screw 2.0 Shipped
添加回答
舉報
0/150
提交
取消