Hardik Malhotra Hardik Malhotra - 1 year ago 107
Python Question

How to read irregular text file as a dataframe in Pandas

My file contains texts in the following format:
<string1> <string2> "some text as a paragraph" .
<string1> <string2> "some text as a paragraph" .
<string1> <string2> "some text as a paragraph" .
<string1> <string2> "some text as a paragraph" .

string1 and string2 contain no space while there is a single space after each. The text inside double quotes also contains single spaces.

I am unable to directly use pd.read_csv() with
sep = " "
as in that case the paragraph is split into irregular columns.

Is there a way to parse such file as a dataframe. Maybe something using regex.

Thank you

Following is the top 4 lines of the data where
pd.read_csv(file_name, sep = " ")
works and below it are the top lines of the data where same code doesn't. I know I can use rdflib to read it as an input and proceed but the purpose I am using pandas is that I only need to do very basic addition/ replacement of columns here.

<http://dbpedia.org/resource/Animalia_(book)> <http://www.w3.org/2000/01/rdf-schema#comment> "Animalia is an illustrated children's book by Graeme Base. It was originally published in 1986, followed by a tenth anniversary edition in 1996, and a 25th anniversary edition in 2012. Over three million copies have been sold. A special numbered and signed anniversary edition was also published in 1996, with an embossed gold jacket."@en .
<http://dbpedia.org/resource/Assistive_technology> <http://www.w3.org/2000/01/rdf-schema#comment> "Assistive technology is an umbrella term that includes assistive, adaptive, and rehabilitative devices for people with disabilities and also includes the process used in selecting, locating, and using them. Assistive technology promotes greater independence by enabling people to perform tasks that they were formerly unable to accomplish, or had great difficulty accomplishing, by providing enhancements to, or changing methods of interacting with, the technology needed to accomplish such tasks."@en .
<http://dbpedia.org/resource/A> <http://www.w3.org/2000/01/rdf-schema#comment> "A (named a /ˈeɪ/, plural aes) is the 1st letter and the first vowel in the ISO basic Latin alphabet. It is similar to the Ancient Greek letter alpha, from which it derives. The upper-case version consists of the two slanting sides of a triangle, crossed in the middle by a horizontal bar. The lower-case version can be written in two forms: the double-storey a and single-storey ɑ. The latter is commonly used in handwriting and fonts based on it, especially fonts intended to be read by children."@en .
<http://dbpedia.org/resource/Aristotle> <http://www.w3.org/2000/01/rdf-schema#comment> "Aristotle (/ˈærɪˌstɒtəl/; Greek: Ἀριστοτέλης [aristotélɛːs], Aristotélēs; 384 – 322 BC) was a Greek philosopher and scientist born in the Macedonian city of Stagira, Chalkidice, on the northern periphery of Classical Greece. His father, Nicomachus, died when Aristotle was a child, whereafter Proxenus of Atarneus became his guardian. At eighteen, he joined Plato's Academy in Athens and remained there until the age of thirty-seven (c. 347 BC)."@en .

Following gives irregular reading:

<http://dbpedia.org/resource/Big_Sounds_of_the_Drags> <http://www.w3.org/2000/01/rdf-schema#comment> "Big Sounds of the Drags is the second album by electronic music producer Junkie XL.\"Check Your Basic Groove\" has an unusual introduction. This portion begins with the sounds of various farm animals (cows for example), then more layers of sound effects are added (including a supercar) until the song segues to the music."@en .
<http://dbpedia.org/resource/Sydney_Roosters_Juniors> <http://www.w3.org/2000/01/rdf-schema#comment> "The Sydney Roosters Juniors is officially known as the Eastern Suburbs District Junior Rugby League. It is an affiliation of junior clubs in the Eastern Suburbs area, covering the Woollahra and Waverley local government areas (LGAs), the northern parts of the Randwick LGA and also the eastern areas of the City of Sydney LGA."@en .
<http://dbpedia.org/resource/A_Shot_at_Glory> <http://www.w3.org/2000/01/rdf-schema#comment> "A Shot at Glory is a film by Michael Corrente produced in 1999 and released in 2001, starring Robert Duvall and the Scottish football player Ally McCoist. It had limited commercial and critical success. The film features the fictional Scottish football club Kilnockie, as they attempt to reach their first Scottish Cup Final. The final game is against Rangers."@en .
<http://dbpedia.org/resource/Kumar_Ponnambalam> <http://www.w3.org/2000/01/rdf-schema#comment> "Kumar Ponnambalam (August 12, 1940 – January 5, 2000) was a prominent defence lawyer and a controversial minority Tamil nationalist politician from Sri Lanka. He was shot dead by unknown gunmen immediately after a suspected LTTE suicide bomb attack against the then president Chandrika Kumaratunga."@en .
<http://dbpedia.org/resource/Amalia_Mendoza> <http://www.w3.org/2000/01/rdf-schema#comment> "Amalia Mendoza García (10 July 1923 – 11 June 2001), nicknamed La Tariácuri, was a Mexican singer and actress. \"Échame a mi la culpa\" and \"Amarga navidad\" were some of her greatest hits."@en .

Answer Source

The read_csv() with backslash as an escape character actually works for me well on both your data samples:

df = pd.read_csv("input.txt", sep=" ", header=None, escapechar="\\").iloc[:, :-1]

The column slicing is just to avoid the last column containing dots only.