CODEWITHSUNDEEP
pythonpandas
gaurav gurnani
gaurav gurnani

Reputation: 2859

parsing text file into pandas dataframe

i have a text file with continuous data. The following text file contains 2 lines Example:

123@#{} 456@$%
1 23

Also, I have column lengths given as 2,3,4 for 3 columns that i need in my data frame. I want to parse the file into a pandas data frame such that the first column gets the first 2 letters, the second column gets the next 3 letters and so on as per the column lengths given (2,3,4).. the next set of letters should form the next row and so on... so my pandas data frame should look like:

colA    colB       colC
12       3@#       {} 4    
56       @$%       1 23

Can anyone suggest something?

Upvotes: 2

Views: 6051

Answers (2)

EdChum
EdChum

Reputation: 394469

There isn't a built in method to do this, so what I'd do is parse and split the lines and append to a list based on your entire row length:

In [216]:

t = '123@#{} 456@$%1 23'
l = [t[x:x+9] for x in range(len(t))[::9]]
l
Out[216]:
['123@#{} 4', '56@$%1 23']
In [218]:
# constuct a df
df = pd.DataFrame({'data':l})
df
Out[218]:
        data
0  123@#{} 4
1  56@$%1 23
In [220]:
# now call the vectorised str methods to split the text data into 3 columns
df['colA'] = df.data.str[0:2]
df['colB'] = df.data.str[2:5]
df['colC'] = df.data.str[5:9]
df
Out[220]:
        data colA colB  colC
0  123@#{} 4   12  3@#  {} 4
1  56@$%1 23   56  @$%  1 23
In [221]:
# drop the data column
df = df.drop('data', axis=1)
df
Out[221]:
  colA colB  colC
0   12  3@#  {} 4
1   56  @$%  1 23

EDIT

To handle your updated data file I've added some code to parse your text file to populate a dict:

In [35]:

d={'data':[]}
line_len=9
skip=True
with open(r'c:\data\date.csv') as f:
    temp = ''
    for line in f:
        if len(line) == 0:
            pass
        if len(line) >= 9:
            d['data'].append(line[:line_len])
        # consume the rest of the line
        if len(temp) !=line_len:
            if len(line) >= 9:
                temp = line[line_len:].rstrip('\n')
            else:
                temp += line.rstrip('\n') 
        if len(temp) == line_len:
            d['data'].append(temp)
            temp=''

    df = pd.DataFrame(d)
df['colA'] = df.data.str[0:2]
df['colB'] = df.data.str[2:5]
df['colC'] = df.data.str[5:9]
df = df.drop('data', axis=1)
df
Out[35]:
  colA colB  colC
0   12  3@#  {} 4
1   56  @$%  1 23
2   12  3@#  {} 4
3   56  @$%  1 23

Upvotes: 1

Dmitriy Kuznetsov
Dmitriy Kuznetsov

Reputation: 366

Split the line into equal-sized parts and use read_fwf:

lines = [data[i:i+row_length]  for i in xrange(0, len(data), row_length)]
buf = StringIO.StringIO("\n".join(lines))
df = pd.read_fwf(buf, colspecs=[(0,2), (2,5), (5,9)], header=None)
print df

The result will be:

    0    1     2
0  12  3@#  {} 4
1  56  @$%  1 23

But I think, that the direct approach without pandas would be easier.

Upvotes: 1

Related Questions