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?
One way which seems to work (at least in 0.10.1 and 0.11.0.dev-fc8de6d):
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!cat ragged.csv
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.
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…
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)