Dealing with special characters in pandas Data Frame´s column Name

Question

I am importing an excel worksheet that has the following columns name:

N° Pedido
   1234
   6424
   4563

The column name ha a special character (°). Because of that, I can´t merge this with another Data Frame or rename the column. I don´t get any error message just the name stays the same. What should I do?

This is the code I am using and the result of the Dataframes:

    import pandas as pd
    import numpy as np
    # Importando Planilhas
    CRM = pd.ExcelFile(r'C:\Users\Michel\Desktop\Relatorio de 
    Vendas\relatorio_vendas_CRM.xlsx', encoding= 'utf-8')
    protheus = pd.ExcelFile(r'C:\Users\Michel\Desktop\Relatorio de 
    Vendas\relatorio_vendas_protheus.xlsx', encoding= 'utf-8')
    #transformando em Data Frame
    df_crm = CRM.parse('190_pedido_export (33)')
    df_protheus = protheus.parse('Relatorio de Pedido de Venda')]
    # Transformando Campos em float o protheus
    def turn_to_float(x):
    return np.float(x)

    df_protheus["TES"] = df_protheus["TES"].apply(turn_to_float)
    df_protheus["Qtde"] = df_protheus["Qtde"].apply(turn_to_float)
    df_protheus["Valor"] = df_protheus["Valor"].apply(turn_to_float)
    #Tirando Tes de não venda do protheus
    # tirando valores com código errado 6
    df_protheus_1 = df_protheus[df_protheus.TES != 513.0]
    df_protheus_2 = df_protheus_1[df_protheus_1.TES != 576.0]

    **df_crm.columns = df_crm.columns.str.replace('N° Pedido', 'teste')
    df_crm.columns**


    Orçamento Origem    N° Pedido   Nº Pedido ERP   Estabelecimento Tipo de 
    Pedido  Classificação(Tipo) Aplicação   Conta   CNPJ/CPF    Contato ... 
    Aprovação Parcial   Antecipa Entrega    Desconto da Tabela de Preço 
    Desconto do Cliente Desconto Informado  Observações Observações NF  Vl 
    Total Bruto Vl Total    Completo
    0   20619.0 23125   NaN Optitex 1 - Venda   NaN Industrialização/Revenda    
    XAVIER E ARAUJO LTDA ME 7970626000170   NaN ... N   N   0   0   0

Note that I used other codes for the bold part with the same result:

#renomeando tabela para dar Merge
#df_crm['proc'] = df_crm['N\xc2\xb0 Pedido']

#df_crm['N Pedido'] = df_crm['N° Pedido']
#df_crm.drop('N° Pedido',inplace=True,axis=1)
#df_crm

#df_crm['N Pedido'] = df_crm['N° Pedido']
#df.drop('N° Pedido',inplace=True,axis=1)
#df_crm

#df_crm_1 = df_crm.rename(columns={"N°Pedido": "teste"})
#df_crm_1

I created a dataframe using the data sample you provided and I'm able to rename columns without any issues. `df = pd.DataFrame(np.random.uniform(0, 1, [3, 4]), columns = ['N° Pedido', '1234', '6424', '4563'])` `df.columns = ['col1', 'col2', 'col3', 'col4']` — doktakay, Dec 08 '17 at 17:51
At what point does the error occur? Can you import the Excel file into pandas? If so, what column names are shown in pandas? — Evan, Dec 08 '17 at 17:56
The columns are importing in Pandas. if I do df.head() I can see the whole file. The problem occurs when I do df_crm['N Pedido'] = df_crm['N° Pedido'] — user1922364, Dec 08 '17 at 18:12
Look at this it may be helpful: https://stackoverflow.com/questions/9623029/python-xlrd-unsupported-format-or-corrupt-file — Anup, Dec 08 '17 at 18:18
Can you just save that sheet as a `csv` and then try to read it as `df = pd.read_csv(path_to_csv, encoding='utf-16')`?? Wanted to see if that happens to `pd.read_csv` too — i.n.n.m, Dec 08 '17 at 18:55

Evan · Accepted Answer · 2017-12-08T19:01:45.653

3

Thanks for posting the link to the Google Sheet. I downloaded it and loaded it via pandas:

df = pd.read_excel(r'~\relatorio_vendas_CRM.xlsx', encoding = 'utf-8')
df.columns = df.columns.str.replace('°', '')
df.columns = df.columns.str.replace('º', '')

Note that the two replace statements are replacing different characters, although they look very similar.

Help from: Why do I get a SyntaxError for a Unicode escape in my file path?

edited Dec 08 '17 at 19:01

answered Dec 08 '17 at 17:55

Evan

2,121
14
27

Still didn´t work:Index([u'Orçamento Origem', u'N° Pedido', u'Nº Pedido – user1922364 Dec 08 '17 at 18:10
Does the solution here help at all? https://stackoverflow.com/questions/36462852/how-to-read-utf-8-files-with-pandas – Evan Dec 08 '17 at 18:12
No, I am using something very similar: CRM = pd.ExcelFile(r'C:\Users\Michel Spiero\Desktop\Relatorio de Vendas\relatorio_vendas_CRM.xlsx', encoding= 'utf-8') – user1922364 Dec 08 '17 at 18:14
Can you post a sample file or data? Or more info about the file format or encoding you are dealing with? – Evan Dec 08 '17 at 18:23
https://drive.google.com/file/d/171fac4SYOGjl4dlk1_7KcRC-MC9xdr7E/view?usp=sharing – user1922364 Dec 08 '17 at 18:41
Edited my answer; if you try to replace the characters, what happens? – Evan Dec 08 '17 at 19:36
This is the CSV: https://drive.google.com/a/damisconsultoria.com.br/file/d/1wiO-zAZBTaKE6bQmx4r0ci6dSnNg39PW/view?usp=sharing – user1922364 Dec 09 '17 at 22:25
If I try to replace the character, nothing happens. I know it is not the code I am using because if I rename or replace any other column it is working. Except this one – user1922364 Dec 09 '17 at 22:26
Please post code you are using, and the input (dataframe) and output (dataframe). – Evan Dec 11 '17 at 18:48
Hi Evan, just posted it on the question. Tks for the help. – user1922364 Dec 12 '17 at 10:36

score 1 · Answer 2 · answered Dec 08 '17 at 17:52

1

I was able to copy the values into another column. You could try that

df['N Pedido'] = df['N° Pedido']
df.drop('N° Pedido',inplace=True,axis=1)

answered Dec 08 '17 at 17:52

Alex Zisman

411
2
9

Have you tried setting the encoding on read? pd.read_excel(fname, encoding='utf-8') – Alex Zisman Dec 08 '17 at 18:01
Yes! and I am still getting that – user1922364 Dec 08 '17 at 18:09

Dealing with special characters in pandas Data Frame´s column Name

2 Answers2