0

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)
Interior Goblin
  • 1
  • 3
  • 1
    How are you trying to compare the values? What errors are you encountering? Do [these answers](https://stackoverflow.com/a/49161313/2280890) help? – import random Mar 07 '23 at 14:47
  • Can you please provide the data in dictionaries using `your_file_name.to_dict()` and then copy here the dictionaries? – PythonLover Mar 07 '23 at 14:49
  • It looks like the following (Shortended because there is a significant amount of data in the Inventory file) {'MAC': {0: '0015AD18F370', 1: '0015AD18F520', 2: '0015AD1B0158', 3: '0016ad1b0158', 4: '0015ad140de8', 5: '0015AD18F578'}} {'TYPE': {0: 'Accedian MetroNID GT-S-DC', 1: 'Accedian MetroNID GT-S-DC', 2: 'Accedian MetroNID GT-S-DC', 3: 'Accedian MetroNID GT-S-DC'}, 'MAC': {0: '0015ad140de8', 1: '0015AD18F578', 2: '0015AD18F3A8', 3: '0015AD18F370', 4: '0015AD18F520', 5: '0015AD1B0158', 'IN STOCK': {0: nan, 1: nan, 2: nan, 3: nan, 'NOTES': {0: nan, 1: nan, 2: nan, 3: nan, 4: nan,}} – Interior Goblin Mar 07 '23 at 14:56
  • Thank you, import random, that was actually close to what I was looking for. It got me to my answer. My only issue now is that I'm getting random entries at the end of my final file that's being exported to CSV. Data that isn't in my inv_file is thrown at the bottom of the list, so that's my next challenge. Thanks again! – Interior Goblin Mar 07 '23 at 17:16
  • Glad to hear you've solved your problem, it's perfectly okay to [answer your own question](https://stackoverflow.com/help/self-answer), it may help others in the future. Longer term you might want to skip creation of `scannedmacs.txt` and conversion to CSV and scan MAC addresses and update your `inv_file` dataframe using [`input()`](https://docs.python.org/3/library/functions.html#input). You can use [regular expressions to validate the addresses you scan](https://stackoverflow.com/questions/26891833/python-regex-extract-mac-addresses-from-string). – import random Mar 08 '23 at 11:55
  • Thank you again! I'll take a look into that when I can scrape some time, haha. – Interior Goblin Mar 08 '23 at 20:16

1 Answers1

0

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)
Interior Goblin
  • 1
  • 3