Pandas Python Vlookup从两个数据范围过滤数据，并使用此过滤器从特定列中替换数据

发布于 2025-02-13 05:09:27 字数 912 浏览 0 评论 0原文

我有两个类似于下面的数据范围。

d = {'serial#': ['AAA111', 'BBB222', 'CCC333', 'DDD444', 'EEE555'], 
'Status': ['Compatible', 'Compatible', 'Not compatible', 'Not compatible', 'Not compatible']}
df1 = pd.DataFrame(data=d)

d2 = {'serial#': ['DDD444', 'EEE555'], 'Model': ['printer', 'printer']}
df2 = pd.DataFrame(data=d2)

print(df1)
print(df2)

  serial#          Status
0  AAA111      Compatible
1  BBB222      Compatible
2  CCC333  Not compatible
3  DDD444  Not compatible
4  EEE555  Not compatible

  serial#    Model
0  DDD444  printer
1  EEE555  printer

我需要从DF1修改列“状态”。为此，我需要使用每个数据框的“序列＃”列制作过滤器。如果“有符合DF2”序列＃'列上的项目，则“ DF1”列下的线条下的线将进行修改。

假设我需要通过字符串“报告”替换过滤行中的数据，所需的DF1如下。

  serial#          Status
0  AAA111      Compatible
1  BBB222      Compatible
2  CCC333  Not compatible
3  DDD444       Reporting
4  EEE555       Reporting

我该怎么做？任何帮助都受到欢迎。

原文

I have two dataframes like below.

d = {'serial#': ['AAA111', 'BBB222', 'CCC333', 'DDD444', 'EEE555'], 
'Status': ['Compatible', 'Compatible', 'Not compatible', 'Not compatible', 'Not compatible']}
df1 = pd.DataFrame(data=d)

d2 = {'serial#': ['DDD444', 'EEE555'], 'Model': ['printer', 'printer']}
df2 = pd.DataFrame(data=d2)

print(df1)
print(df2)

  serial#          Status
0  AAA111      Compatible
1  BBB222      Compatible
2  CCC333  Not compatible
3  DDD444  Not compatible
4  EEE555  Not compatible

  serial#    Model
0  DDD444  printer
1  EEE555  printer

I need to modify the column 'Status' from df1. For this I need to make a filter using the 'serial#' columns of each of the dataframes. The lines under the 'Status on df1' column will be modified if ' there are items that match on df2 'serial#' column.

Suppose I need to replace data in the filtered rows by the string "reporting", the df1 desired is as below.

  serial#          Status
0  AAA111      Compatible
1  BBB222      Compatible
2  CCC333  Not compatible
3  DDD444       Reporting
4  EEE555       Reporting

How can I do that? Any help is welcomed.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

白芷 2025-02-20 05:09:31

使用ISIN从df2检查序列＃，然后将其用作布尔人掩码进行设置status to'Reportering'：

df1.loc[df1['serial#'].isin(df2['serial#']), 'Status'] = 'Reporting'
print(df1)

# Output
  serial#          Status
0  AAA111      Compatible
1  BBB222      Compatible
2  CCC333  Not compatible
3  DDD444       Reporting
4  EEE555       Reporting

Use isin to check serial# from df2 then use it as boolean mask to set Status to 'Reporting':

df1.loc[df1['serial#'].isin(df2['serial#']), 'Status'] = 'Reporting'
print(df1)

# Output
  serial#          Status
0  AAA111      Compatible
1  BBB222      Compatible
2  CCC333  Not compatible
3  DDD444       Reporting
4  EEE555       Reporting

回复收藏 0 原文

ま柒月 2025-02-20 05:09:31

您可以在状态列上执行两者之间的左键，然后在有条件的术语之间执行：

import pandas as pd
import numpy as np

d = {'serial#': ['AAA111', 'BBB222', 'CCC333', 'DDD444', 'EEE555'], 'Status': ['Compatible', 'Compatible', 'Not compatible', 'Not compatible', 'Not compatible']}
df1 = pd.DataFrame(data=d)

d2 = {'serial#': ['DDD444', 'EEE555'], 'Model': ['printer', 'printer']}
df2 = pd.DataFrame(data=d2)

df3 = df1.merge(df2, on='serial#', how='left')
df3['Status'] = np.where(df3['Model'].notnull(), 'Reporting', df3['Status'])
df3 = df3.drop('Model', axis=1)

df3

df3

You can perform a left join between both and then a conditional term on Status column:

import pandas as pd
import numpy as np

d = {'serial#': ['AAA111', 'BBB222', 'CCC333', 'DDD444', 'EEE555'], 'Status': ['Compatible', 'Compatible', 'Not compatible', 'Not compatible', 'Not compatible']}
df1 = pd.DataFrame(data=d)

d2 = {'serial#': ['DDD444', 'EEE555'], 'Model': ['printer', 'printer']}
df2 = pd.DataFrame(data=d2)

df3 = df1.merge(df2, on='serial#', how='left')
df3['Status'] = np.where(df3['Model'].notnull(), 'Reporting', df3['Status'])
df3 = df3.drop('Model', axis=1)

df3

df3

回复收藏 0 原文

~没有更多了~