Import data from file with different row length using Pandas

Question

I have a txt file containing some a certain amount of rows. Each row may contain a different amount of items.

Here is an example of what the input.txt looks like:

1,0,50,20,2,96,152,65,32,0
1,0,20,50,88,45,151
1,1,90,15,86,11,158,365,45
2,0,50,20,12,36,157,25
2,0,20,50,21,63,156,76,32,77
3,1,50,20,78,48,152,75,52,22,96

My goal is to store this data within a dataframe having the following structure:

• 5 columns
• columns from 1 to 4 contain the first 4 values contained of each row
• 5 column contains a list storing whatever is left for each row

The output therefore should be like so:

Out[8]: 
   A  B   C   D                              E
0  1  0  50  20        [2, 96, 152, 65, 32, 0]
1  1  0  20  50                  [88, 45, 151]
2  1  1  90  15         [86, 11, 158, 365, 45]
3  2  0  50  20              [12, 36, 157, 25]
4  2  0  20  50      [21, 63, 156, 76, 32, 77]
5  3  1  50  20  [78, 48, 152, 75, 52, 22, 96]

I have tried to use pandas.read_csv('input.txt') but it does not work since the rows do not have the same length.

Can you suggest me a smart and elegant way to achieve my goal?

Answer 1

You can use read_csv with some separator which is NOT in data - output is one column df:

import pandas as pd
from pandas.compat import StringIO

temp="""1,0,50,20,2,96,152,65,32,0
1,0,20,50,88,45,151
1,1,90,15,86,11,158,365,45
2,0,50,20,12,36,157,25
2,0,20,50,21,63,156,76,32,77
3,1,50,20,78,48,152,75,52,22,96"""
#after testing replace 'StringIO(temp)' to 'filename.csv'
df = pd.read_csv(StringIO(temp), sep="|", names=['A'])
print (df)
                                 A
0       1,0,50,20,2,96,152,65,32,0
1              1,0,20,50,88,45,151
2       1,1,90,15,86,11,158,365,45
3           2,0,50,20,12,36,157,25
4     2,0,20,50,21,63,156,76,32,77
5  3,1,50,20,78,48,152,75,52,22,96

Then use split:

cols = list('ABCDE')
df[cols] = df.A.str.split(',', n=4, expand=True)
df.E = df.E.str.split(',')
print (df)
   A  B   C   D                              E
0  1  0  50  20        [2, 96, 152, 65, 32, 0]
1  1  0  20  50                  [88, 45, 151]
2  1  1  90  15         [86, 11, 158, 365, 45]
3  2  0  50  20              [12, 36, 157, 25]
4  2  0  20  50      [21, 63, 156, 76, 32, 77]
5  3  1  50  20  [78, 48, 152, 75, 52, 22, 96]