英文:
Comparing two different dataframes, then if match, change value in column
问题
以下是代码部分的翻译：
# 导入pandas库
import pandas as pd
# 将scannedmacs.txt设置为读取文件
read_file = pd.read_csv (r'C:\...\input\scannedmacs.txt')
# 将read_file转换为CSV，将MAC作为标题
read_file.to_csv (r'C:\...\output\convertedmacs.csv', index=None, header=['MAC'])
# 将convertedmacs.csv设置为scan_file
scan_file = pd.read_csv(r'C:\...\output\convertedmacs.csv')
# 将预先存在的InventoryNOC.csv设置为inv_file
inv_file = pd.read_csv(r'C:\...\output\InventoryNOC.csv', error_bad_lines=False)
请注意，代码中的文件路径（例如，r'C:\...\input\scannedmacs.txt' 和 r'C:\...\output\convertedmacs.csv'）可能需要根据您的实际文件路径进行调整。此代码片段的作用是将文本文件中的MAC地址读取为DataFrame，然后将其转换为CSV文件，并读取另外一个CSV文件作为库存数据。
英文:
To start with, I'll give some backstory, so this makes more sense. I'm fairly fresh to coding, so forgive me if I'm missing some obvious answers.
So, I work for a smaller company that has a heavily manual system for doing inventory. We have more than 200 devices of many different types. We track these via MAC address (Not Serial Number, despite how I've suggested this makes more sense than MAC address), and each device needs to be manually read to then mark them as "In Stock" in an excel. I decided this needed to be done more automated. So far, I have a barcode reader to scan the MACs into a text file. Then I convert it to a CSV, which I need to compare to the existing Inventory file which is also converted to CSV. I want to then mark them as "In Stock" if the MAC in the barcode scanned CSV is within the Inventory CSV.
This is what I have so far, but each time I try to compare what I have, I run into a brick wall. Any thoughts?
import pandas as pd
#setting scannedmacs.txt as read_file
read_file = pd.read_csv (r'C:\...\input\scannedmacs.txt')
#converting read_file to CSV, setting MAC as a header
read_file.to_csv (r'C:\...\output\convertedmacs.csv', index=None , header = ['MAC'])
#setting convertedmacs.csv to scan_file
scan_file = pd.read_csv(r'C:\...\output\convertedmacs.csv')
#setting pre-existing InventoryNOC.csv as inv_file
inv_file = pd.read_csv(r'C:\...\output\InventoryNOC.csv', error_bad_lines=False)
答案1
得分: 0
我使用掩码解决了我的错误（在其他答案中提供了由'import random'在评论中提供的示例）。 非常感谢他！ 这是适用于我的新代码。
# 使用isin创建掩码
mask = inv_file.MAC.isin(scan_file.MAC.unique())
# 仅返回inv_file中inv_file MAC在scan_file MAC中的数据
inv_file[mask]
# 向inv_file数据框添加一列
inv_file['IN STOCK'] = mask
# 合并inv_file和scan_file，根据'MAC'列，使用'outer'方式合并
merged = inv_file.merge(scan_file, on='MAC', how='outer')
# 将合并后的数据保存为CSV文件
merged.to_csv('C:\...\InventoryFinal.csv', index=None)
希望这对你有所帮助。
英文:
I ended up using a mask to solve my errors (using examples within other answers provided by 'import random' in the comments). Many thanks to him! Here's my new code that works for me below.
# mask using isin
mask = inv_file.MAC.isin(scan_file.MAC.unique())
# return only the data from inv_file, where inv_file MAC isin scan_file MAC
inv_file[mask]
# add a column to the inv_file dataframe
inv_file['IN STOCK'] = mask
merged = inv_file.merge(scan_file, on='MAC', how='outer')
merged.to_csv('C:\...\InventoryFinal.csv', index=None)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论