Handling Variable Number of Columns with Pandas - Python

Question

I have a data set that looks like this (at most 5 columns - but can be less)

1,2,3 1,2,3,4 1,2,3,4,5 1,2 1,2,3,4 .... 

I am trying to use pandas read_table to read this into a 5 column data frame. I would like to read this in without additional massaging.

If I try

import pandas as pd my_cols=['A','B','C','D','E'] my_df=pd.read_table(path,sep=',',header=None,names=my_cols) 

I get an error - "column names have 5 fields, data has 3 fields".

Is there any way to make pandas fill in NaN for the missing columns while reading the data?

Answer 1

One way which seems to work (at least in 0.10.1 and 0.11.0.dev-fc8de6d):

>>> !cat ragged.csv 1,2,3 1,2,3,4 1,2,3,4,5 1,2 1,2,3,4 >>> my_cols = ["A", "B", "C", "D", "E"] >>> pd.read_csv("ragged.csv", names=my_cols, engine='python')    A  B   C   D   E 0  1  2   3 NaN NaN 1  1  2   3   4 NaN 2  1  2   3   4   5 3  1  2 NaN NaN NaN 4  1  2   3   4 NaN 

Note that this approach requires that you give names to the columns you want, though. Not as general as some other ways, but works well enough when it applies.