7

I have a lot of csv files that I would like to read with Pandas (pd.read_csv), however, in some of the files there is added a column midway that does not have a header, like this example:

Apples, Pears
1, 2
3, 4
5, 6, 7

If using pd.read_csv(example_file) the following error is thrown "ParserError: Error tokenizing data. C error: Expected 2 fields in line 4, saw 3"

I would like to avoid having to skip the line and instead just add a dummy header name, like Unknown1, and get the following result:

Apples, Pears, Unknown1  
1, 2, np.nan
3, 4, np.nan
5, 6, 7
Improve this question

3 Answers 3

Reset to default
5

pandas needs to know the geometry in advance to build the dataframe. You can read the header line and add several dummy column names to supply the number of columns, then re-read the whole csv and discard the columns that weren't used after all.

>>> import pandas as pd
>>> names = list(pd.read_csv('foo.csv', nrows=0)) + ['unknown1', 'unknown2']
>>> df=pd.read_csv('foo.csv', names=names, skiprows=1).dropna(axis='columns', how='all')
>>> df
   Apples   Pears  unknown1
0       1       2       NaN
1       3       4       NaN
2       5       6       7.0

If there are many extra columns and you are worried about the memory footprint of the intermediate dataframe, you can use the csv module to scan the file and calculate the maximum number of rows. Unlike pandas, csv is quite happy to emit varying sized rows.

>>> with open('foo.csv', newline='') as in_fp:
...     reader = csv.reader(in_fp)
...     header = next(reader)
...     num_cols = max(len(row) for row in reader)
... 
>>> names = header + ['unknown{}'.format(i+1) for i in range(num_cols-len(header))]
>>> df = pd.read_csv('foo.csv', names=names, skiprows=1)
>>> df
   Apples   Pears  unknown1
0       1       2       NaN
1       3       4       NaN
2       5       6       7.0
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Excellent! Nice and simple, and only uses Pandas. Thank you! :)
I am afraid this is not scalable. What do you do if there are 10 missing column names?
@sudonym - It really depends on what level of scaling you need. Preprocessing the file adds to CPU cost. The memory footprint is not that big of a deal on modern hardware unless the files are quite large. OP has many files and few extra columns. It is likely a good solution.
I agree with you.
1

We can load the csv then fixed your out after that 

import io
t="""Apples, Pears
1, 2
3, 4
5, 6, 7"""
df = pd.read_csv(io.StringIO(t), sep='\t')

yourdf=df.iloc[:,0].str.split(', ',expand=True)
s=df.columns.str.split(', ').tolist()[0]
yourdf.columns=s+['unknow'+str(x+1) for x in range(yourdf.shape[1]-len(s))]


yourdf
Out[104]: 
  Apples Pears unknow1
0      1     2    None
1      3     4    None
2      5     6       7
Improve this answer

1 Comment

In the case of 't' being a path to the csv file, how would IO be used?
0

If you don't know the number of columns beforehand, you can determine the maximum number of columns across all rows beforehand using readlines(), which comes at the cost of loosing the known header names.

sep = ','                                                   # Define separator
lines = open("test.csv").readlines()                        # Open file and read lines
colcount = max([len(l.strip().split(sep)) for l in lines])  # Count separator
df = pd.read_csv("test.csv", names = range(colcount), skiprows = [0])
print df

   0  1    2
0  1  2  NaN
1  3  4  NaN
2  5  6  7.0

The colcount above can be applied to all other answers so far, too.

Edit: Beware of input files other than .csv (see comments)

Improve this answer

5 Comments

This doesn't work either. You've ignored any quoting and escapes for the column separator that a CSV processor would consider.
this works for me - did you copy and paste into jupyter? Would you mind to elaborate?
Quoting and escapes are part of the csv protocol. For instance, what if a column is "embedded comma, should not split this column"? CSV parses deal with this, which is why we use them instead of just splitting.
I have upvoted your answer and removed any reference from mine regarding scalability of yours. Thanks for your explanation.
If you use csv to handle the escaping ,then this solution is good.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.