python - 如何连接两个数据框并保留每个数据框的某些列？

Question

这个问题已经有答案了:

奥 git _a (8 个回答)

已关闭 4 年前。

我有两个数据帧，names 和 claims:

names = pd.DataFrame({
    'UniqueID': 'A B C D E F'.split(),
    'Name':['Susie', 'George Foreman', 'Charles', 'Nicole', 'Peter Piper', 'Penelope Cruz'],
    'Address':['111 3rd St', '123 Bank St', '555 Square Sq', '9 Charlton Ave', 'PO Box 1', 'The White House'], 
    'Phone number':['2032218686', '2032032203', '8048048804', '2232645879', '2564544469', '8005865555']})

  UniqueID            Name          Address Phone number
0        A           Susie       111 3rd St   2032218686
1        B  George Foreman      123 Bank St   2032032203
2        C         Charles    555 Square Sq   8048048804
3        D          Nicole   9 Charlton Ave   2232645879
4        E     Peter Piper         PO Box 1   2564544469
5        F   Penelope Cruz  The White House   8005865555


claims = pd.DataFrame({
    'ClaimNo':range(29,38),
    'ClaimDetails':['Slip and fall','Clmt slipped and fell','Thunderstorms are scary','Hail storm damage',
                   'Property fire','Arson','Shooting','Shooting and fatality','Slip and fall'],
    'PolicyNo':['00058566-0','00056455-5','00058588-8','00011111-2','00088787-0','00045658-0','00012345-6','00065432-1','00088080-4'],
    'UniqueID':'A F F D E A D E E'.split()})

   ClaimNo             ClaimDetails    PolicyNo UniqueID
0       29            Slip and fall  00058566-0        A
1       30    Clmt slipped and fell  00056455-5        F
2       31  Thunderstorms are scary  00058588-8        F
3       32        Hail storm damage  00011111-2        D
4       33            Property fire  00088787-0        E
5       34                    Arson  00045658-0        A
6       35                 Shooting  00012345-6        D
7       36    Shooting and fatality  00065432-1        E
8       37            Slip and fall  00088080-4        E

我想创建一个新的 DataFrame，仅包含 names 中的 UniqueID 出现在 claims 中的行。我不确定它们是否应该合并或过滤。我一直在尝试不同类型的合并，但我似乎无法得到我想要的结果，它应该如下所示:

  UniqueID           Name          Address Phone number
0        A          Susie       111 3rd St   2032218686
1        D         Nicole   9 Charlton Ave   2232645879
2        E    Peter Piper         PO Box 1   2564544469
3        F  Penelope Cruz  The White House   8005865555

Answer 1

这对我来说似乎是最简单的方法:

names[names.UniqueID.isin(claims['UniqueID'].to_numpy())]

编辑:对于其他正在回答的人，这里有一些我用来回答OP问题的辅助字典/数据框变量:

data1 = {"UniqueID": {"0": "A", "1": "B", "2": "C", "3": "D", "4": "E", "5": "F"}, "Name": {"0": "Susie", "1": "George Foreman", "2": "Charles", "3": "Nicole", "4": "Peter Piper", "5": "Penelope Cruz"}, "Address": {"0": "111 3rd St", "1": "123 Bank St", "2": "555 Square Sq", "3": "9 Charlton Ave", "4": "PO Box 1", "5": "The White House"}, "Phone number": {"0": 2032218686, "1": 2032032203, "2": 8048048804, "3": 2232645879, "4": 2564544469, "5": 8005865555}}
names = pd.DataFrame.from_dict(data1)

data2 = {"ClaimNo": {"0": 29, "1": 30, "2": 31, "3": 32, "4": 33, "5": 34, "6": 35, "7": 36, "8": 37}, "ClaimDetails": {"0": "Slip and fall", "1": "Clmt slipped and fell", "2": "Thunderstorms are scary", "3": "Hail storm damage", "4": "Property fire", "5": "Arson", "6": "Shooting", "7": "Shooting and fatality", "8": "Slip and fall"}, "PolicyNo": {"0": "00058566-0", "1": "00056455-5", "2": "00058588-8", "3": "00011111-2", "4": "00088787-0", "5": "00045658-0", "6": "00012345-6", "7": "00065432-1", "8": "00088080-4"}, "UniqueID": {"0": "A", "1": "F", "2": "F", "3": "D", "4": "E", "5": "A", "6": "D", "7": "E", "8": "E"}}
claims = pd.DataFrame.from_dict(data2)

OP:如果您下次提供这些变量将会很有帮助，我必须使用 pd.read_fwf 将您的固定宽度格式表读入字典对象🥴