0

Previously I was working on reading the MACs and formatting the output. I'm trying to do it using Pandas and Excel file now with the macaddress module. I'm pretty close but hitting a snag at the end.

def readfile_excel():
    df = pd.read_excel('ap_macs.xlsx', usecols=['apmac'], index_col=False)
    df_csv = pd.DataFrame.to_string(df,index=0,header=None)
    df_split = df_csv.splitlines()
    #print(df_split)
    for i in df_split:
        data = (i)
        print(data)
        mac_data = macaddress.MAC(data)
        print(mac_data)

Getting the following error:

ValueError: ' D0D3E0C19A54' cannot be parsed as MAC

So it looks like when I split the lines, ' ' this extra stuff in there is not going away

[' D0D3E0C19A54', ' D0D3E0C19A55', ' D0D3E0C19A56']

Even though if I print out the "data" it doesn't show up there. Any tips/pointers? Thank you

Ali
  • 77
  • 6
  • 3
    `macaddress.MAC(data.strip())`? I believe your whole process could be improved though… using pandas to convert to string to then parse it manually seems like an antipattern – mozway Jul 31 '22 at 17:19
  • Oooffff you are a genius. I was going crazy trying to do all kinds of other stuff and it was right there lol. Thanks so much. – Ali Jul 31 '22 at 17:20
  • I concur, it's a learning process. I'll try to get it to do it in one step next. Will need to figure out removing the index and header or utilize it somehow. Just brain storming it. Thank you again. – Ali Jul 31 '22 at 21:13
  • 1
    It is ridiculous to convert the dataframe to a string, then to split it into lines, then parse each line. You already have ways to apply a function to each row of your dataframe. Check out the [`apply` function](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.apply.html), – Pranav Hosangadi Aug 01 '22 at 13:29
  • Thank you @PranavHosangadi, appreciate you sharing the function. Like I mentioned, it is a learning process and helps me understand different things. But doing it efficiently is crucial. I will try to use the apply function to make it more efficient now. Thank you. – Ali Aug 01 '22 at 16:37
  • @PranavHosangadi got any link that will help with skipping the column after I read the excel sheet? That has been a pain all along when I do "usecols=" I can't skip it then and it breaks my function. – Ali Aug 01 '22 at 17:37
  • I'm not sure what you mean by "skipping a column". Skipping a column for what? Did you just want to [drop a column from your dataframe?](https://stackoverflow.com/questions/13411544/delete-a-column-from-a-pandas-dataframe) – Pranav Hosangadi Aug 01 '22 at 18:28
  • Apologies, that was a little confusing. When I run the "pd.DataFrame.apply" function I get "Name: apmac, dtype: object has wrong type for MAC". "apmac" is actually the column header that I am using (See my code above). so my function breaks because it does not see it as the right type. Another reason I had to do a string, csv and split. – Ali Aug 01 '22 at 21:19

0 Answers0