Reading from a .txt file to a pandas dataframe with newline as a separator

user_10586681 0
Viewed 3306

I want to extract some data from a text file to a dataframe :

the text file look like this

URL: http://www.nytimes.com/2016/06/30/sports/baseball/washington-nationals-max-scherzer-baffles-mets-completing-a-sweep.html

WASHINGTON — Stellar .... stretched thin.
“We were going t......e do anything.”
Wednesday’s ... starter.
“We’re n... work.”
The Mets did not scor....their 40-37 record.

URL: http://www.nytimes.com/2016/06/30/nyregion/mayor-de-blasios-counsel-to-leave-next-month-to-lead-police-review-board.html

Mayor Bill de .... Department.
The move.... April.
A civil ... conversations.
More... administration.

URL: http://www.nytimes.com/2016/06/30/nyregion/three-men-charged-in-killing-of-cuomo-administration-lawyer.html

In the early..., the Folk Nation.
As hundreds ... wounds.
For some...residents.
On Wednesd...killing.
One ...murder.

It contains the URL and the text from new york times articles, I want to create a dataframe of 2 columns, the first one being the URL and the second one being the text.

The issue I have is that I couldn't deal with the Delimiters as there are two new lines between the URL and the corresponding text. But there are single new lines also in the text itself.

I tried using this code, but instead of getting a 2 column dataframe, I got a single column with a new row for each newline used, so it is also separating the text into multiple paragraphs, I am using dask btw :

df_csv = dd.read_csv(filename,sep="\n\n",header=None,engine='python')
dask-dataframe pandas python-3.x
2 Answers
# read file
file = open('ny.txt', encoding="utf8").read()

url = []
text = []

# split text at every 2-new-lines
# elements at 'odd' positions are 'urls'
# elements at 'even' positions are 'text/content'
for ind, line in enumerate(file.split('\n\n')):
    if ind%2==0:
        url.append(line)
    else:
        text.append(line)

# save to a dataframe
df = pd.DataFrame({'url':url, 'text':text})
df
    url                                                 text
0   URL: http://www.nytimes.com/2016/06/30/sports/...   WASHINGTON — Stellar .... stretched thin.\n“We...
1   URL: http://www.nytimes.com/2016/06/30/nyregio...   Mayor Bill de .... Department.\nThe move.... A...
2   URL: http://www.nytimes.com/2016/06/30/nyregio...   In the early..., the Folk Nation.\nAs hundreds...

# ADDITIONAL : Remove the characters 'URL: ' with empty string
df['url'] = df['url'].str.replace('URL: ', '')
df
    url                                                 text
0   http://www.nytimes.com/2016/06/30/sports/baseb...   WASHINGTON — Stellar .... stretched thin.\n“We...
1   http://www.nytimes.com/2016/06/30/nyregion/may...   Mayor Bill de .... Department.\nThe move.... A...
2   http://www.nytimes.com/2016/06/30/nyregion/thr...   In the early..., the Folk Nation.\nAs hundreds...
user_89174260

You can do it easily in the following way:

import pandas as pd

text = '''URL: http://www.nytimes.com/2016/06/30/sports/baseball/washington-nationals-max-scherzer-baffles-mets-completing-a-sweep.html

WASHINGTON — Stellar .... stretched thin.
“We were going t......e do anything.”
Wednesday’s ... starter.
“We’re n... work.”
The Mets did not scor....their 40-37 record.

URL: http://www.nytimes.com/2016/06/30/nyregion/mayor-de-blasios-counsel-to-leave-next-month-to-lead-police-review-board.html

Mayor Bill de .... Department.
The move.... April.
A civil ... conversations.
More... administration.

URL: http://www.nytimes.com/2016/06/30/nyregion/three-men-charged-in-killing-of-cuomo-administration-lawyer.html

In the early..., the Folk Nation.
As hundreds ... wounds.
For some...residents.
On Wednesd...killing.
One ...murder.
'''

# 1) Extract the text to lines list
text = text.replace('\n', '')  # delete all the single '\n'
text = text.replace('\n\n', '')  # delete all the '\n\n'
lines = text.split('URL: ')[1:]  # to drop the first match of ''

# 2) Create pandas.DataFrame object and populate it with the extracted lines list from (1)
df = pd.DataFrame(dict(lines=lines))

# 3) Extract the URLs into a new column
df.loc[:, 'URL'] = df.loc[:, 'lines'].str.extract(r'(http:[^,]+.html)', expand=False)

# 4) Extract the message into a new column
df.loc[:, 'Text'] = df.loc[:, 'lines'].str.extract(r'(?<=\.html)([^$]+)', expand=False)

# 4) Delete the original lines column
df.drop('lines', axis='columns', inplace = True)

Output:

    URL                                                 Text
0   http://www.nytimes.com/2016/06/30/sports/baseb...   WASHINGTON — Stellar .... stretched thin.“We w...
1   http://www.nytimes.com/2016/06/30/nyregion/may...   Mayor Bill de .... Department.The move.... Apr...
2   http://www.nytimes.com/2016/06/30/nyregion/thr...   In the early..., the Folk Nation.As hundreds ....

Cheers!

user_45960780
Related