Data in csv with enclosed data in double quotation marks

Question

I am trying to retrieve data from an open online .csv file: http://www.anp.gov.br/arquivos/acesso-informacao/dp/2020-producao-mar.csv

I am using Anaconda + Spyder + Pandas. The command line I use is this:

FileList = ['http://www.anp.gov.br/arquivos/acesso-informacao/dp/2020-producao-mar.csv']
arq1 = FileList[0]
df1 = pd.read_csv(arq1, quotechar = '"')

Pandas is able to read file, but cannot properly parse the lines. The lines it can´t parse are the ones that contain data inside double quotes, such as:

'2020,01/2020,Bahia,Camamu,MANATI,7-MNT-3-BAS,Mar,PLATAFORMA DE MANATI 1,0,"241,729",0,"12257,70101","61,573",,,,,,,,'

I have tried this approach too:

file1 = pd.read_csv(arq1,sep=',\s*',skipinitialspace=True,quoting=csv.QUOTE_ALL,engine='python')

But this second approach leads to the following error:

UnicodeDecodeError: 'charmap' codec can't decode byte 0x81 in position 212: character maps to <undefined>

Can you give me suggestions?

Test Data

The file is included, but in the event the file is no longer available, the following is sufficient to reproduce the issues.
Store the following data in a csv file.

Ano,Mês/Ano,Estado,Bacia,Campo,Poço,Ambiente,Instalação,Produção de Óleo (m³),Produção de Condensado (m³),Produção de Gás Associado (Mm³),Produção de Gás Não Associado (Mm³),Produção de Água (m³),Injeção de Gás (Mm³),Injeção de Água para Recuperação Secundária (m³),Injeção de Água para Descarte (m³),Injeção de Gás Carbônico (Mm³),Injeção de Nitrogênio (Mm³),Injeção de Vapor de Água (t),Injeção de Polímeros (m³),Injeção de Outros Fluidos (m³)
2020,01/2020,Alagoas,Alagoas,PARU,4-ALS-39-AL,Mar,Não Informado,0,0,0,0,0,,,,,,,,
"2020,01/2020,Bahia,Camamu,MANATI,7-MNT-1-BAS,Mar,PLATAFORMA DE MANATI 1,0,""265,58"",0,""17605,52003"",""74,489"",,,,,,,,"
"2020,01/2020,Bahia,Camamu,MANATI,7-MNT-2-BAS,Mar,PLATAFORMA DE MANATI 1,0,""326,366"",0,""17810,97775"",""84,152"",,,,,,,,"
"2020,01/2020,Bahia,Camamu,MANATI,7-MNT-3-BAS,Mar,PLATAFORMA DE MANATI 1,0,""241,729"",0,""12257,70101"",""61,573"",,,,,,,,"
"2020,01/2020,Bahia,Camamu,MANATI,7-MNT-4-BAS,Mar,PLATAFORMA DE MANATI 1,0,""285,911"",0,""17013,25742"",""88,015"",,,,,,,,"
"2020,01/2020,Bahia,Camamu,MANATI,7-MNT-5D-BAS,Mar,PLATAFORMA DE MANATI 1,0,""173,078"",0,""20459,1769"",""68,169"",,,,,,,,"
"2020,01/2020,Bahia,Camamu,MANATI,7-MNT-6D-BAS,Mar,PLATAFORMA DE MANATI 1,0,""178,857"",0,""24557,04732"",""75,546"",,,,,,,,"
"2020,01/2020,Bahia,Recôncavo,CANDEIAS,7-C-173D-BA,Mar,Estação Pedra Branca,""95,742"",0,""82,24558"",0,""0,194"",,,,,,,,"
2020,01/2020,Bahia,Recôncavo,CANDEIAS,7-C-174D-BA,Mar,Não Informado,0,0,0,0,0,,,,,,,,
2020,01/2020,Bahia,Recôncavo,CANDEIAS,7-C-197D-BA,Mar,Não Informado,0,0,0,0,0,,,,,,,,
2020,01/2020,Bahia,Recôncavo,CANDEIAS,7-C-201D-BA,Mar,Não Informado,0,0,0,0,0,,,,,,,,
2020,01/2020,Bahia,Recôncavo,CANDEIAS,7-C-202D-BA,Mar,Não Informado,0,0,0,0,0,,,,,,,,
2020,01/2020,Bahia,Recôncavo,CANDEIAS,7-C-203D-BA,Mar,Não Informado,0,0,0,0,0,,,,,,,,
2020,01/2020,Bahia,Recôncavo,CANDEIAS,7-C-211D-BA,Mar,Não Informado,0,0,0,0,0,,,,,,,,
2020,01/2020,Bahia,Recôncavo,CANDEIAS,7-C-212D-BA,Mar,Não Informado,0,0,0,0,0,,,,,,,,
"2020,01/2020,Bahia,Recôncavo,DOM JOÃO,7-DJM-854H-BAS,Mar,Estação Marapé (Dom João Mar),""388,00158"",0,""3,10388"",0,""3221,81179"",,,,,,,,"
"2020,01/2020,Bahia,Recôncavo,DOM JOÃO,7-DJM-856H-BAS,Mar,Estação Marapé (Dom João Mar),""318,49041"",0,""2,54778"",0,""4814,03179"",,,,,,,,"
"2020,01/2020,Bahia,Recôncavo,DOM JOÃO,7-DJM-857H-BAS,Mar,Estação Marapé (Dom João Mar),""149,19484"",0,""1,19341"",0,""2641,14209"",,,,,,,,"
"2020,01/2020,Bahia,Recôncavo,DOM JOÃO,8-DJ-811H-BAS,Mar,Estação Marapé (Dom João Mar),,,,,,0,""5816,23328"",0,0,0,0,0,0"
"2020,01/2020,Bahia,Recôncavo,DOM JOÃO,8-DJM-858H-BAS,Mar,Estação Marapé (Dom João Mar),,,,,,0,""5396,07916"",0,0,0,0,0,0"
"2020,01/2020,Bahia,Recôncavo,DOM JOÃO,8-DJM-881H-BAS,Mar,Estação Marapé (Dom João Mar),""196,46254"",0,""1,57155"",0,""2268,57935"",,,,,,,,"
"2020,01/2020,Bahia,Recôncavo,DOM JOÃO MAR,7-DJM-854H-BAS,Mar,Estação Marapé (Dom João Mar),""56,69942"",0,""0,45345"",0,""470,80921"",,,,,,,,"
"2020,01/2020,Bahia,Recôncavo,DOM JOÃO MAR,7-DJM-856H-BAS,Mar,Estação Marapé (Dom João Mar),""46,54159"",0,""0,37222"",0,""703,48321"",,,,,,,,"
"2020,01/2020,Bahia,Recôncavo,DOM JOÃO MAR,7-DJM-857H-BAS,Mar,Estação Marapé (Dom João Mar),""21,80216"",0,""0,17426"",0,""385,95491"",,,,,,,,"
2020,01/2020,Bahia,Recôncavo,DOM JOÃO MAR,7-DJM-882H-BAS,Mar,Não Informado,0,0,0,0,0,,,,,,,,
"2020,01/2020,Bahia,Recôncavo,DOM JOÃO MAR,8-DJ-811H-BAS,Mar,Estação Marapé (Dom João Mar),,,,,,0,""849,93672"",0,0,0,0,0,0"
"2020,01/2020,Bahia,Recôncavo,DOM JOÃO MAR,8-DJM-858H-BAS,Mar,Estação Marapé (Dom João Mar),,,,,,0,""788,53884"",0,0,0,0,0,0"
"2020,01/2020,Bahia,Recôncavo,DOM JOÃO MAR,8-DJM-881H-BAS,Mar,Estação Marapé (Dom João Mar),""28,70946"",0,""0,22956"",0,""331,51165"",,,,,,,,"
2020,01/2020,Ceará,Ceará,ATUM,3-AT-8-CES,Mar,Não Informado,0,0,0,0,0,,,,,,,,
2020,01/2020,Ceará,Ceará,ATUM,3-CES-83-CE,Mar,Não Informado,0,0,0,0,0,,,,,,,,
2020,01/2020,Ceará,Ceará,ATUM,3-CES-86D-CE,Mar,Não Informado,0,0,0,0,0,,,,,,,,
2020,01/2020,Ceará,Ceará,ATUM,7-AT-10D-CES,Mar,Não Informado,0,0,0,0,0,,,,,,,,
2020,01/2020,Ceará,Ceará,ATUM,7-AT-13D-CES,Mar,Não Informado,0,0,0,0,0,,,,,,,,
2020,01/2020,Ceará,Ceará,ATUM,7-AT-16D-CES,Mar,Não Informado,0,0,0,0,0,,,,,,,,
2020,01/2020,Ceará,Ceará,ATUM,7-AT-17D-CES,Mar,Não Informado,0,0,0,0,0,,,,,,,,
2020,01/2020,Ceará,Ceará,ATUM,7-AT-18D-CES,Mar,Não Informado,0,0,0,0,0,,,,,,,,
2020,01/2020,Ceará,Ceará,ATUM,7-AT-19D-CES,Mar,Não Informado,0,0,0,0,0,,,,,,,,
2020,01/2020,Ceará,Ceará,ATUM,7-AT-21DP-CES,Mar,Não Informado,0,0,0,0,0,,,,,,,,
"2020,01/2020,Ceará,Ceará,ATUM,7-AT-22DP-CES,Mar,PLATAFORMA DE ATUM 2,""328,927"",0,""23,30745"",0,""761,164"",,,,,,,,"

What is the encoding of the file? 0x81 is not valid Latin-1, nor UTF-8, nor a number of other popular encodings. — tripleee, Nov 17 '20 at 17:26
You first need to read the file without pandas and clean the rows that are in `"..."`. Some rows are enclose and some are not. Once cleaned, then you can read with pandas. I would probably use `str.endswith` and `str.startswith`. — Trenton McKinney, Nov 17 '20 at 17:46
I downloaded the file and it seems to have incorrect contents starting from the third line. You can *probably* repair it with something like `if line.startswith('"'): line = line.strip('"\n').replace('""', '"')` but obviously this is just guesswork. Getting the provider of this data to repair it and upload a correct version would be better. — tripleee, Nov 17 '20 at 17:47

Trenton McKinney · Answer 1 · 2020-11-17T20:12:38.080

There are 5735 rows that are not in "..."
The beginning and end '"' have been removed from the other 10735 rows, and some values are in 2 sets of double quotes (e.g. ""...""), which has be fixed because is was causing issues with uneven row lengths.
When creating the dataframe, use decimal=',' so the numbers will be properly parsed to float type.

import pandas as pd

# file location
file = 'e:/PythonProjects/stack_overflow/data/2020-producao-mar.csv'

# open the original file to read and open a file to write to
with open(file, mode='r', encoding='utf-8') as f, open('cleaned.csv', mode='w', encoding='utf-8') as f1:
    
    # read the lines
    lines = f.readlines()
    
    # parse each line
    for line in lines:
        
        # remove newlines from the end of the rows
        line = line.strip()
        
        # find rows with beginning and ending quotes
        if (line.startswith('"') == True) and (line.endswith('"') == True):
            
            # extract the string between the beginning and end quote
            line = line[1:-1]
            
            # replace 2 double quotes for 1 double quote
            line = line.replace('""', '"')
            
        # write the rows to cleaned.csv
        f1.writelines(line + '\n')

# create a dataframe with original.csv
df1 = pd.read_csv('cleaned.csv', encoding='utf-8', quotechar='"', decimal=',')

# display(df1.head())
    Ano  Mês/Ano   Estado    Bacia   Campo         Poço Ambiente              Instalação  Produção de Óleo (m³)  Produção de Condensado (m³)  Produção de Gás Associado (Mm³)  Produção de Gás Não Associado (Mm³)  Produção de Água (m³)  Injeção de Gás (Mm³)  Injeção de Água para Recuperação Secundária (m³)  Injeção de Água para Descarte (m³)  Injeção de Gás Carbônico (Mm³)  Injeção de Nitrogênio (Mm³)  Injeção de Vapor de Água (t)  Injeção de Polímeros (m³)  Injeção de Outros Fluidos (m³)
0  2020  01/2020  Alagoas  Alagoas    PARU  4-ALS-39-AL      Mar           Não Informado                    0.0                        0.000                              0.0                              0.00000                  0.000                   NaN                                               NaN                                 NaN                             NaN                          NaN                           NaN                        NaN                             NaN
1  2020  01/2020    Bahia   Camamu  MANATI  7-MNT-1-BAS      Mar  PLATAFORMA DE MANATI 1                    0.0                      265.580                              0.0                          17605.52003                 74.489                   NaN                                               NaN                                 NaN                             NaN                          NaN                           NaN                        NaN                             NaN
2  2020  01/2020    Bahia   Camamu  MANATI  7-MNT-2-BAS      Mar  PLATAFORMA DE MANATI 1                    0.0                      326.366                              0.0                          17810.97775                 84.152                   NaN                                               NaN                                 NaN                             NaN                          NaN                           NaN                        NaN                             NaN
3  2020  01/2020    Bahia   Camamu  MANATI  7-MNT-3-BAS      Mar  PLATAFORMA DE MANATI 1                    0.0                      241.729                              0.0                          12257.70101                 61.573                   NaN                                               NaN                                 NaN                             NaN                          NaN                           NaN                        NaN                             NaN
4  2020  01/2020    Bahia   Camamu  MANATI  7-MNT-4-BAS      Mar  PLATAFORMA DE MANATI 1                    0.0                      285.911                              0.0                          17013.25742                 88.015                   NaN                                               NaN                                 NaN                             NaN                          NaN                           NaN                        NaN                             NaN

score 0 · Answer 2 · answered Nov 17 '20 at 19:56

Thank you all very much!

The code worked this way:

import numpy as np
import matplotlib.pyplot as plt
import pandas as pd
import csv
import urllib
print('\014')




FileList = ['http://www.anp.gov.br/arquivos/acesso-informacao/dp/2020-producao-mar.csv']


file = FileList[0]

urllib.request.urlretrieve (file, "inputfile.csv")    

with open('inputfile.csv', mode='r', encoding='utf-8') as f, open('cleaned.csv', mode='w', encoding='utf-8') as f1:

    # read the lines
    lines = f.readlines()

    # parse each line
    for line in lines:

        # remove newlines from the end of the rows
        line = line.strip()

        # find rows with beginning and ending quotes and write those rows to cleaned.csv
        if (line.startswith('"') == True) and (line.endswith('"') == True):

            # extract the string between the beginning and end quote
            line = line[1:-1]
            line = line.replace('""', '"')

            f1.writelines(line + '\n')

        # write the remaining rows to original.csv
        else:
            line = line.replace('""', '"')
            f1.writelines(line + '\n')


# create a dataframe with original.csv
df1 = pd.read_csv('cleaned.csv', decimal=',' , encoding='utf-8', quotechar='"')

If you look at my answer, which was updated just as you posted this, I also did `line = line.replace('""', '"')`. This was causing the issue with uneven row lengths. — Trenton McKinney, Nov 17 '20 at 19:56
Also note the use of `df1 = pd.read_csv('cleaned.csv', encoding='utf-8', quotechar='"', decimal=',')` to properly read the numbers as decimals, which is required for the values to be recognized as numeric instead of strings. — Trenton McKinney, Nov 17 '20 at 20:06

Data in csv with enclosed data in double quotation marks

Test Data

2 Answers2