Why does .decode() result in NaN values for byte strings that resulted from the pd.read_sql_query function?

Question

I have a MS SQL database that I want to query with Python. I use the following snippet:

cnxn = pypyodbc.connect("Driver={SQL Server Native Client 11.0};"
                        "Server=xxxx;"
                        "Database=xxxx;"
                        "Trusted_Connection=yes;")

query = 'select * from orders'
df = pd.read_sql_query(query)

This returns the following dataframe:

                      id    ordernumber
0  b'xxxx-xxxx-xxxx-xxxx'   123456789
1  b'xxxx-xxxx-xxxx-xxxx'   123456789
2  b'xxxx-xxxx-xxxx-xxxx'   123456789

The columns are both of type object. The id column is supposed to be a string, but it is a byte string in this output. How do I transform this column to a regular string object? I tried the following (from here):

df['id'].str.decode('utf-8')

... but that transforms the entire id column into np.nan values. How do I properly transform my DataFrame id column into a regular string object?

Dict to recreate this DataFrame:

{'id': {0: "b'DE91EBA3-313D-463C-B948-00010AA26136'",
  1: "b'316E587F-7FDD-4FBA-8778-0001E7783025'",
  2: "b'F6E50A95-A3E8-45D3-8E79-000210CCA14A'",
  3: "b'EE20A958-0CD6-4144-9743-00024D3E703F'",
  4: "b'58AFA9B9-4B10-47D3-8840-000AAD9CBF00'"},
 'ordernumber': {0: '500358152',
  1: '500489603',
  2: '500454759',
  3: '500512969',
  4: '500517135'}}

SQL Server doesn't have `string` data types, per-se, it uses `char`, `nchar`, `nvarchar` and `varchar`. Can you include the table schema in your question because it seems like `id` could actually be `uniqueidentifier`? — AlwaysLearning, Jun 29 '22 at 12:30
@AlwaysLearning Thanks, I did not know that. Unfortunately I cannot share the table schema, but the `id` column is indeed a uniqueidentifier in this table. — sander, Jun 29 '22 at 12:32
it seems you get it as string, not as bytes - so you need only to remove `b'` and `'` — furas, Jun 29 '22 at 15:18

furas · Accepted Answer · 2022-06-29T15:25:34.757

You don't get bytes b'DE91EBA3-313D-463C-B948-00010AA26136'
but string "b'DE91EBA3-313D-463C-B948-00010AA26136'" and this makes difference.

You have to only remove b' ' from string

df['id'].str.replace("b'", "").str.replace("'", "")

or simpler

df['id'].str[2:-1]

Minimal working code:

import pandas as pd

df = pd.DataFrame({
  'id': {
    0: "b'DE91EBA3-313D-463C-B948-00010AA26136'",
    1: "b'316E587F-7FDD-4FBA-8778-0001E7783025'",
    2: "b'F6E50A95-A3E8-45D3-8E79-000210CCA14A'",
    3: "b'EE20A958-0CD6-4144-9743-00024D3E703F'",
    4: "b'58AFA9B9-4B10-47D3-8840-000AAD9CBF00'"
  },
  'ordernumber': {
    0: '500358152',
    1: '500489603',
    2: '500454759',
    3: '500512969',
    4: '500517135'
  }
})

#df['id'] = df['id'].str[2:-1]
df['id'] = df['id'].str.replace("b'", "").str.replace("'", "")

print(df)

Result:

                                     id ordernumber
0  DE91EBA3-313D-463C-B948-00010AA26136   500358152
1  316E587F-7FDD-4FBA-8778-0001E7783025   500489603
2  F6E50A95-A3E8-45D3-8E79-000210CCA14A   500454759
3  EE20A958-0CD6-4144-9743-00024D3E703F   500512969
4  58AFA9B9-4B10-47D3-8840-000AAD9CBF00   500517135

Oh wow, that makes perfect sense. Can't believe I didn't try that. Thanks! — sander, Jun 30 '22 at 08:21

Why does .decode() result in NaN values for byte strings that resulted from the pd.read_sql_query function?

1 Answers1