Reading a text file in python at variable spacing
I have the following data as a text file that I would like to load into python:
pclass survived name
0 1 1 Allen, Miss. Elisabeth Walton
1 1 1 Allison, Master. Hudson Trevor
2 1 0 Allison, Miss. Helen Loraine
3 1 0 Allison, Mr. Hudson Joshua Creighton
4 1 0 Allison, Mrs. Hudson J C (Bessie Waldo Daniels)
5 1 1 Anderson, Mr. Harry
6 1 1 Andrews, Miss. Kornelia Theodosia
7 1 0 Andrews, Mr. Thomas Jr
8 1 1 Appleton, Mrs. Edward Dale (Charlotte Lamson)
9 1 0 Artagaveytia, Mr. Ramon
10 1 0 Astor, Col. John Jacob
Since the white space is not a constant, and also since the last field (name) has a space in between, I am having trouble parsing it. I tried the following:
pd.read_csv("test.csv",sep = "\s+", header=0, index_col=0)
But this gives an error:
CParserError: Error tokenizing data. C error: Expected 7 fields in line 5, saw 8
+3
source to share
2 answers
You can use pandas.read_fwf
(aka: fixed-width format) to do this:
Code:
df = pd.read_fwf(StringIO(data), header=1, index_col=0)
Test code:
from io import StringIO
import pandas as pd
data = u"""
pclass survived name
0 1 1 Allen, Miss. Elisabeth Walton
1 1 1 Allison, Master. Hudson Trevor
2 1 0 Allison, Miss. Helen Loraine
3 1 0 Allison, Mr. Hudson Joshua Creighton
4 1 0 Allison, Mrs. Hudson J C (Bessie Waldo Daniels)
5 1 1 Anderson, Mr. Harry
6 1 1 Andrews, Miss. Kornelia Theodosia
7 1 0 Andrews, Mr. Thomas Jr
8 1 1 Appleton, Mrs. Edward Dale (Charlotte Lamson)
9 1 0 Artagaveytia, Mr. Ramon
10 1 0 Astor, Col. John Jacob"""
df = pd.read_fwf(StringIO(data), header=1, index_col=0)
print(df)
Results:
pclass survived name
0 1 1 Allen, Miss. Elisabeth Walton
1 1 1 Allison, Master. Hudson Trevor
2 1 0 Allison, Miss. Helen Loraine
3 1 0 Allison, Mr. Hudson Joshua Creighton
4 1 0 Allison, Mrs. Hudson J C (Bessie Waldo Daniels)
5 1 1 Anderson, Mr. Harry
6 1 1 Andrews, Miss. Kornelia Theodosia
7 1 0 Andrews, Mr. Thomas Jr
8 1 1 Appleton, Mrs. Edward Dale (Charlotte Lamson)
9 1 0 Artagaveytia, Mr. Ramon
10 1 0 Astor, Col. John Jacob
+2
source to share
'\s+'
takes one or more whitespace, which is still parsing your last column. Instead, use a regular expression that suggests two or more.
pd.read_csv("test.csv", sep="\s{2,}", header=0, index_col=0, engine='python')
The whole working example
from io import StringIO
import pandas as pd
txt = """ pclass survived name
0 1 1 Allen, Miss. Elisabeth Walton
1 1 1 Allison, Master. Hudson Trevor
2 1 0 Allison, Miss. Helen Loraine
3 1 0 Allison, Mr. Hudson Joshua Creighton
4 1 0 Allison, Mrs. Hudson J C (Bessie Waldo Daniels)
5 1 1 Anderson, Mr. Harry
6 1 1 Andrews, Miss. Kornelia Theodosia
7 1 0 Andrews, Mr. Thomas Jr
8 1 1 Appleton, Mrs. Edward Dale (Charlotte Lamson)
9 1 0 Artagaveytia, Mr. Ramon
10 1 0 Astor, Col. John Jacob
"""
pd.read_csv(StringIO(txt), sep="\s{2,}", header=0, index_col=0, engine='python')
pclass survived name
0 1 1 Allen, Miss. Elisabeth Walton
1 1 1 Allison, Master. Hudson Trevor
2 1 0 Allison, Miss. Helen Loraine
3 1 0 Allison, Mr. Hudson Joshua Creighton
4 1 0 Allison, Mrs. Hudson J C (Bessie Waldo Daniels)
5 1 1 Anderson, Mr. Harry
6 1 1 Andrews, Miss. Kornelia Theodosia
7 1 0 Andrews, Mr. Thomas Jr
8 1 1 Appleton, Mrs. Edward Dale (Charlotte Lamson)
9 1 0 Artagaveytia, Mr. Ramon
10 1 0 Astor, Col. John Jacob
+3
source to share