How to deal with multi-value lineterminators in pandas

Question

I have the \x02\n as a line terminator in a csv file I'm trying to parse. However, I cannot use two characters in pandas, it only allows one, for example:

>>> data = pd.read_csv(file, sep="\x01", lineterminator="\x02")
>>> data.loc[100].tolist()
['\n1475226000146', '1464606', 'Juvenile', '1', 'http://itunes.apple.com/artist/juvenile/id1464606?uo=5', '1']

Or:

data = pd.read_csv(file, sep="\x01", lineterminator="\n")
 >>> data.loc[100].tolist()
['1475226000146', '1464606', 'Juvenile', '1', 'http://itunes.apple.com/artist/juvenile/id1464606?uo=5', '1\x02']

Here we can see that the \n hasn't been chopped off correctly. What would be the best way to read the csv file in pandas with the above separator?

@AndreiOdegov that's the delimiter, not the lineterminator. Is it the same? — David542, Dec 19 '18 at 05:11
@coldspeed yes, in this file. But I do have other files with different multi-value separators, so I don't just want to do a find-all/replace, etc. — David542, Dec 19 '18 at 05:11
That complicates things. There isn't any real support for this as of now. — cs95, Dec 19 '18 at 05:11
@coldspeed I suppose the best thing to do here would be to use the first character as the line terminator, and the second to strip from the first value in the column? — David542, Dec 19 '18 at 05:13
That would've been my answer, but it will not work for your other CSVs... — cs95, Dec 19 '18 at 05:15

cs95 · Accepted Answer · 2018-12-19T05:27:16.580

5

As of v0.23, pandas does not support multi-character line-terminators. Your code currently returns:

s = "this\x01is\x01test\x02\nthis\x01is\x01test2\x02"
df = pd.read_csv(
    pd.compat.StringIO(s), sep="\x01", lineterminator="\x02", header=None)

df
        0   1      2
0    this  is   test
1  \nthis  is  test2

Your only option (as of now) is to remove the leading whitespace from the first column. You can do this with str.lstrip.

df.iloc[:, 0] = df.iloc[:, 0].str.lstrip()
# Alternatively,
# df.iloc[:, 0] = [s.lstrip() for s in df.iloc[:, 0]]

df

      0   1      2
0  this  is   test
1  this  is  test2

If you have to handle stripping of multiple other kinds of line-terminators (besides just the newline), you can pass a string of them:

line_terminators = ['\n', ...]
df.iloc[:, 0] = df.iloc[:, 0].str.lstrip(''.join(line_terminators))

edited Dec 19 '18 at 05:27

answered Dec 19 '18 at 05:19

cs95

379,657
97
704
746

thanks man. Any interest in a job in a job or internship (our company is in the LA area)? – David542 Dec 19 '18 at 05:20
instead of the blank `lstrip`, I used the characters in the line_terminator I had, so something like: `df.iloc[:, 0] = df.iloc[:, 0].str.lstrip(''.join(self.line_terminator[1:]))` – David542 Dec 19 '18 at 05:24
@David542 Are you David from Premier Digital? :-) I really appreciate your offer, but I already have an on-campus job for the coming spring, and will be joining Google once I graduate. I am not currently looking to interview for any positions, but if that changes I'll be sure to give you a ping! – cs95 Dec 19 '18 at 05:25
@David542 Okay, so you have a list of line-terminators? I can modify my solution. – cs95 Dec 19 '18 at 05:26

How to deal with multi-value lineterminators in pandas

1 Answers1