python pandas read_csv delimiter in column data

by

Dealing with unquoted delimiters is always a nuisance. In this case, since it looks like the broken text is known to be surrounded by three correctly-encoded columns, we can recover. TBH, I’d just use the standard Python reader and build a DataFrame once from that:

import csv
import pandas as pd

with open("semi.dat", "r", newline="") as fp:
    reader = csv.reader(fp, delimiter=";")
    rows = [x[:1] + [';'.join(x[1:-2])] + x[-2:] for x in reader] 
    df = pd.DataFrame(rows)

which produces

       0                                              1  2  3
0  12012               My Name is Mike. What is your's?  3  0
1   1522  In my opinion: It's cool; or at least not bad  4  0
2  21427                    Hello. I like this feature!  5  1

Then we can immediately save it and get something quoted correctly:

In [67]: df.to_csv("fixedsemi.dat", sep=";", header=None, index=False)

In [68]: more fixedsemi.dat
12012;My Name is Mike. What is your's?;3;0
1522;"In my opinion: It's cool; or at least not bad";4;0
21427;Hello. I like this feature!;5;1

In [69]: df2 = pd.read_csv("fixedsemi.dat", sep=";", header=None)

In [70]: df2
Out[70]: 
       0                                              1  2  3
0  12012               My Name is Mike. What is your's?  3  0
1   1522  In my opinion: It's cool; or at least not bad  4  0
2  21427                    Hello. I like this feature!  5  1

More Related Contents:

  1. Convert commas decimal separators to dots within a Dataframe
  2. pandas.read_csv from string or package data
  3. UnicodeDecodeError: ‘utf-8’ codec can’t decode byte 0x8b in position 1: invalid start byte, while reading csv file in pandas
  4. Pandas ParserError EOF character when reading multiple csv files to HDF5
  5. Import multiple csv files into pandas and concatenate into one DataFrame
  6. Pandas groupby with delimiter join
  7. Writing a pandas DataFrame to CSV file
  8. Pandas read_csv from url
  9. How to make separator in pandas read_csv more flexible wrt whitespace, for irregular separators?
  10. How to plot multiple Seaborn Jointplot in Subplot
  11. Python Pandas Error tokenizing data
  12. Conditionally fill column values based on another columns value in pandas
  13. No module named ‘pandas._libs.tslibs.timedeltas’ in PyInstaller
  14. pandas read_csv and filter columns with usecols
  15. import text to pandas with multiple delimiters
  16. Using numpy.genfromtxt to read a csv file with strings containing commas
  17. How to keep leading zeros in a column when reading CSV with Pandas?
  18. portable way to write csv file in python 2 or python 3
  19. How to read a column of csv as dtype list using pandas?
  20. How can I see the formulas of an excel spreadsheet in pandas / python?
  21. Pandas number rows within group in increasing order
  22. Read csv from Google Cloud storage to pandas dataframe
  23. Conditional row read of csv in pandas
  24. Load CSV to Pandas MultiIndex DataFrame
  25. pandas read csv with extra commas in column
  26. AttributeError: module ‘numpy’ has no attribute ‘__version__’
  27. bash: Python import – Command not found for pandas
  28. How to upload a CSV file in FastAPI and convert it into a Pandas Dataframe?
  29. pandas.read_csv FileNotFoundError: File b’\xe2\x80\xaa’ despite correct path
  30. Parsing a pipe-delimited file in Python

Leave a Comment