Handling Variable Number of Columns with Pandas - Python
PythonPandasPython Problem Overview
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?
Python Solutions
Solution 1 - Python
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.
Solution 2 - Python
I'd also be interested to know if this is possible, from the doc it doesn't seem to be the case. What you could probably do is read the file line by line, and concatenate each reading to a DataFrame:
import pandas as pd
df = pd.DataFrame()
with open(filepath, 'r') as f:
for line in f:
df = pd.concat( [df, pd.DataFrame([tuple(line.strip().split(','))])], ignore_index=True )
It works but not in the most elegant way, I guess...
Solution 3 - Python
Ok. Not sure how efficient this is - but here is what I have done. Would love to hear if there is a better way to do this. Thanks !
from pandas import DataFrame
list_of_dicts=[]
labels=['A','B','C','D','E']
for line in file:
line=line.rstrip('\n')
list_of_dicts.append(dict(zip(labels,line.split(','))))
frame=DataFrame(list_of_dicts)