Handling bad lines when reading csv files with pd.read_csv()

You should try to filter the file first, by looking for an odd number of quotes (") or counting the number of commas in each line, or using any other method you prefer. After filtering you can do:

data = pd.read_csv('bad_data.csv', header=0,names=["Authors","Title","ID","URN"], delimiter=',', quotechar='"', doublequote=False, encoding='utf-8')
print(data)

Or you can try something like this code, which filters the lines, separates the bad ones and prints them, saves the good ones in a dictionary, and then creates the DataFrame at the end.

Either way you will need to create your own filter.

import pandas as pd
# Manual filter
with open('bad_data.csv', mode='r', encoding='utf-8') as f:
    data_csv = f.readlines()

dict_filtered = {"Authors":[], "Title":[], "ID": [], "URN": []}
for line in data_csv:
    if "Authors" not in line: # Skip header line
        # Some handle to recognize bad data
        # Example -> data with more than 5 commas 
        list_data = line.split(',')
        if len(list_data) > 5:
            print(f'Bad line:{line}')
        else:
            
            author = f"{list_data[0]}, {list_data[1]}"
            title = list_data[2]
            ID = list_data[3]
            URN = list_data[4]

            dict_filtered['Authors'].append(author)
            dict_filtered['Title'].append(title)
            dict_filtered['ID'].append(ID)
            dict_filtered['URN'].append(URN)

print("DATAFRAME")
df = pd.DataFrame(dict_filtered)
print(df)

So the problem here as far as I understand is that the CSV file has some rows with issues — things like unmatched quotes or commas inside quoted fields — which mess up parsing when using pd.read_csv() and instead of letting pd.read_csv() throw errors or skip those bad rows, you want to handle them manually. Here's the solution that I think serves your purpose.

import pandas as pd
import csv

data = []
bad_lines = []

with open("path.csv", mode="r", encoding="utf-8") as file:
    reader = csv.reader(file, delimiter=",", quotechar='"')
    headers = next(reader)  

    for i, row in enumerate(reader, start=2):
        try:
            if row.count('"') % 2 != 0:
                raise ValueError("Unmatched quotes in row")
        
            if any(',' in field and '"' in field for field in row): #(indicating possible unescaped commas)
                raise ValueError("Commas inside quoted field.")
        
            data.append(row) #append if no issues exist
    
        except Exception as e:
            # Store bad lines
            bad_lines.append((i, row, str(e)))

uncorrupted_df = pd.DataFrame(data, columns=headers)

# Print bad lines encountered
print("Following bad lines encountered:")
for line in bad_lines:
    print(f"Line {line[0]}: {line[1]} (Error: {line[2]})")

This approach will catch and handle malformed rows instead of letting read_csv() silently skip them or throw errors etc. I hope this works for you!

TLDR:

Looking at the pandas code for 1.4.1 and I assume for 1.4.0 There seems to be a bug that simply bypasses the callable specification. You can verify that by putting a break point inside your method and see that it is never called.

Apparently this has been resolved in newer (v2) pandas and the pyarrow engine.

So the solution is likely to be upgrade to a newer version of pandas or patch your version.

Background:

In python_parser.py (about line 744) we find this method that works for the on_bad_lines "error" and "warn" conditions but fails to handle the callable path and thus simply returns None in the event of a parse error. When this method returns None the caller (_next_line(self)) just ignores that line.

I'm guessing here that this is because getting the actual data from line = next(self.data) is tricky as self.data is a csv.reader object and this iterator throws without exposing the actual data.

    def _next_iter_line(self, row_num: int) -> list[Scalar] | None:
        """
        Wrapper around iterating through `self.data` (CSV source).

        When a CSV error is raised, we check for specific
        error messages that allow us to customize the
        error message displayed to the user.

        Parameters
        ----------
        row_num: int
            The row number of the line being parsed.
        """
        try:
            # assert for mypy, data is Iterator[str] or None, would error in next
            assert self.data is not None
            line = next(self.data)  ### <---- This throws with your data
            # for mypy
            assert isinstance(line, list)
            return line
        except csv.Error as e:
            if (
                self.on_bad_lines == self.BadLineHandleMethod.ERROR
                or self.on_bad_lines == self.BadLineHandleMethod.WARN
            ):
                msg = str(e)

                if "NULL byte" in msg or "line contains NUL" in msg:
                    msg = (
                        "NULL byte detected. This byte "
                        "cannot be processed in Python's "
                        "native csv library at the moment, "
                        "so please pass in engine='c' instead"
                    )

                if self.skipfooter > 0:
                    reason = (
                        "Error could possibly be due to "
                        "parsing errors in the skipped footer rows "
                        "(the skipfooter keyword is only applied "
                        "after Python's csv library has parsed "
                        "all rows)."
                    )
                    msg += ". " + reason

                self._alert_malformed(msg, row_num)
            return None