Convert bytes in a pandas dataframe column into hexadecimals-CodePudding

There is a problem when pandas reads read_sql bytes column. If you look at the sql request through DBeaver, then the bytes column shows differently, but if you look at it through read_sql, it seems that pandas translates the value into a hex.

For example, pandas shows column value -

b'\x80\xbc\x10`K\xa8\x95\xd8\x11\xe5K\xf9\xe7\xd7\x8cq'

I need -

0x80BC10604BA895D811E54BF9E7D78C71

If I use, in sql,

CONVERT(varchar(max),guid,1)

pandas give correct values. But I need to convert column in python not in sql.

CodePudding user response：

It looks like the column (which I call 'col' below) contains bytes. There's the .hex() method that you can map to each item in the column to convert them into hexadecimal strings.

df['col'] = df['col'].map(lambda e: e.hex())

This produces

80bc10604ba895d811e54bf9e7d78c71

It seems the specific output you want is to have the "0x" prefix and have upper-case letters for the digits above 9, which you can do by calling .upper() on the hex string and prepend '0x'.

df['col'] = df['col'].map(lambda e: '0x'   e.hex().upper())

This produces

0x80BC10604BA895D811E54BF9E7D78C71