我有一个表material
+--------+-----+-------------------+----------------+-----------+
| ID | REV | name | Description | curr |
+--------+-----+-------------------+----------------+-----------+
| 211-32 | 001 | Screw 1.0 | Used in MAT 1 | READY |
| 211-32 | 002 | Screw 2 plus | can be Used-32 | WITHDRAWN |
| 212-41 | 001 | Bolt H1 | Light solid | READY |
| 212-41 | 002 | BOLT H2+Form | Heavy solid | READY |
| 101-24 | 001 | HexHead 1-A | NOR-1 | READY |
| 101-24 | 002 | HexHead Spl | NOR-22 | READY |
| 423-98 | 001 | Nut Repair spare | NORM1 | READY |
| 423-98 | 002 | Nut Repair Part-C | NORM2 | WITHDRAWN |
| 423-98 | 003 | Nut SP-C | NORM2+NORM1 | NULL |
| 654-01 | 001 | Bar | Specific only | WITHDRAWN |
| 654-01 | 002 | Bar rod-S | Designed+Spe | WITHDRAWN |
| 654-01 | 003 | Bar OPG | Hard spec | NULL |
+--------+-----+-------------------+----------------+-----------+
在这里,每个ID可以有多个修订。我想采用最新修订(即001002003等的最高值(。但如果最新修订的curr
为NULL
(字符串(或WITHDRAWN
,则我必须采用以前的修订及其相应值。即使是curr
也是NULL
或WITHDRAWN
,我也必须再次进行上一次修订。如果所有的修订都有相同的问题,那么我们可以忽略它。所以预期的输出是
+--------+-----+------------------+---------------+-------+
| ID | REV | name | Description | curr |
+--------+-----+------------------+---------------+-------+
| 211-32 | 001 | Screw 1.0 | Used in MAT 1 | READY |
| 212-41 | 002 | BOLT H2+Form | Heavy solid | READY |
| 101-24 | 002 | HexHead Spl | NOR-22 | READY |
| 423-98 | 001 | Nut Repair spare | NORM1 | READY |
+--------+-----+------------------+---------------+-------+
我对Python很陌生。我已经尝试了以下代码,但我没有工作。任何建议都将不胜感激。
import pandas as pd
import numpy as np
mydata = pd.read_csv('C:/Myfolder/Python/myfile.csv')
mydata.sort_values(['ID','REV'], ascending=[True, False]).drop_duplicates('',keep=last)
我们可以创建一个psuedo列来获取最大值并返回其索引。
第一步是过滤掉我们想要忽略的值。
df1 = df.loc[
df[~df["curr"].isin(["WITHDRAWN", "NULL"])]
.assign(key=df["REV"].astype(int))
.groupby("ID")["key"]
.idxmax()
]
ID REV name Description curr
6 101-24 002 HexHead Spl NOR-22 READY
1 211-32 001 Screw 1.0 Used in MAT 1 READY
4 212-41 002 BOLT H2+Form Heavy solid READY
7 423-98 001 Nut Repair spare NORM1 READY
您可以使用isin
选择其中没有NULL或WITHDRAW的行,然后执行sort_values
和drop_duplicates
:
mydata = mydata[~mydata['curr'].isin(['NULL','WITHDRAW'])]
mydata = mydata.sort_values(['ID','REV']).drop_duplicates('ID',keep='last')
我认为首先应该从表中删除NULL或WITHDRAW。
mydata[mydata[curr] == 'Ready'] # this should do I think...
然后你可以尝试你的排序,取最大转速值。
mydata = mydata.sort_values(['ID','REV']).drop_duplicates('ID',keep='last')