pandas read csv use delimiter for a fixed amount of time

user_8046232 0
Viewed 1276

Suppose I have a log file structured as follow for each line:

$date $machine $task_name $loggedstuff

I hope to read the whole thing with pd.read_csv('blah.log', sep=r'\s+'). The problem is, $loggedstuff has spaces in it, is there any way to limit the delimiter to operate exactly 3 times so everything in loggedstuff will appear in the dataframe as a single column?

I've already tried using csv to parse it as list of list and then feed it into pandas, but that is slow, I'm wondering if there's a more direct way to do this. Thanks!

pandas csv logging python
3 Answers

Setup

tmp.txt

a b c d
1 2 3 test1 test2 test3
1 2 3 test1 test2 test3 test4

Code

df = pd.read_csv('tmp.txt', sep='\n', header=None)
cols = df.loc[0].str.split(' ')[0]
df = df.drop(0)

def splitter(s):
    vals = s.iloc[0].split(' ')
    d = dict(zip(cols[:-1], vals))
    d[cols[-1]] = ' '.join(vals[len(cols) - 1: ])
    return pd.Series(d)

df.apply(splitter, axis=1)

returns

   a  b  c                        d
1  1  2  3        test1 test2 test3
2  1  2  3  test1 test2 test3 test4
user_19538000

When using expand=True, the split elements will expand out into separate columns.

Parameter n can be used to limit the number of splits in the output.

Details about the same cane From pandas.Series.str.split

Pattern to use

df.str.split(pat=None, n=-1, expand=False) expand : bool, default False

Expand the splitted strings into separate columns.

If True, return DataFrame/MultiIndex expanding dimensionality.

If False, return Series/Index, containing lists of strings

df.str.split(' ', n=3, expand=True)
user_57048630

I think you can read each line of the csv file as a single string, then convert the resulted dataframe to 3 columns by regular expression.

df = pd.read_csv('./test.csv', sep='#', squeeze=True)
df = df.str.extract('([^\s]+)\s+([^\s]+)\s+(.+)')

in which you can change the separator to whatever not appeared in the document.

user_99334680
Related