MAC format from CSV file

July 26, 2022

Based on the following link:

I can easily format a single MAC. But I’m having an issue trying to do multiple from a csv file. When I run the file, it converts them but the script will convert each one like 6 times. If I add "return" then it only converts the first one 6 times.

def readfile_csv():
    with open('ap_macs.csv', 'r',encoding='utf-8-sig') as csv_file:
        csv_reader = csv.reader(csv_file, delimiter=',')
        for lines in csv_reader:
            data = (lines[0])
            for i in range(0,12,2):
                 format_mac = ':'.join(data[i:i + 2] for i in range(0, 12, 2))
                 print(format_mac.swapcase())

Ideally, I’d love to be able to do this with Pandas and Excel but the indexing is killing me. Appreciate any help. Thank you.

    ap_macs
A1B2C3D4E5F6
A1B2C3D4E5F7
A1B2C3D4E5F8
A1B2C3D4E5F9
a1b2c3d4e5f6
a1b2c3d4e5f7
a1b2c3d4e5f8
a1b2c3d4e5f9

>Solution :

You could use pandas for this:

df = pd.read_csv('ap_macs.csv')

chunks = [df["ap_macs"].str[i:i+2] for i in range(2, 12, 2)]

df["MAC"] = df['ap_macs'].str[0:2].str.cat(chunks, ":")

which gives:

        ap_macs                MAC
0  A1B2C3D4E5F6  A1:B2:C3:D4:E5:F6
1  A1B2C3D4E5F7  A1:B2:C3:D4:E5:F7
2  A1B2C3D4E5F8  A1:B2:C3:D4:E5:F8
3  A1B2C3D4E5F9  A1:B2:C3:D4:E5:F9
4  a1b2c3d4e5f6  a1:b2:c3:d4:e5:f6
5  a1b2c3d4e5f7  a1:b2:c3:d4:e5:f7
6  a1b2c3d4e5f8  a1:b2:c3:d4:e5:f8
7  a1b2c3d4e5f9  a1:b2:c3:d4:e5:f9

Of course, you can overwrite the ap_macs column if you want, but I created a new column for this demonstration.

If you want to use your csv.reader approach, you need to create your string first, and then print it.

def readfile_csv():
    # csv_data = []
    with open('ap_macs.csv', 'r',encoding='utf-8-sig') as csv_file:
        csv_reader = csv.reader(csv_file, delimiter=',')
        for row in csv_reader:
            data = row[0]
            for i in range(0,12,2):
                 format_mac = ':'.join(data[i:i + 2] for i in range(0, 12, 2)).swapcase()
            print(format_mac)
            # csv_data.append(format_mac)         
    # return csv_data

which will print:

a1:b2:c3:d4:e5:f6
a1:b2:c3:d4:e5:f7
a1:b2:c3:d4:e5:f8
a1:b2:c3:d4:e5:f9
A1:B2:C3:D4:E5:F6
A1:B2:C3:D4:E5:F7
A1:B2:C3:D4:E5:F8
A1:B2:C3:D4:E5:F9

Note that printing is not the same as returning data, and if you actually want to use this data outside the function, you’ll have to return it (uncomment the commented lines)