import csv with different number of columns per row using Pandas

Question

What is the best approach for importing a CSV that has a different number of columns for each row using Pandas or the CSV module into a Pandas DataFrame.

"H","BBB","D","Ajxxx Dxxxs"
"R","1","QH","DTR"," "," ","spxxt rixxls, raxxxd","1"

Using this code:

import pandas as pd
data = pd.read_csv("smallsample.txt",header = None)

the following error is generated

Error tokenizing data. C error: Expected 4 fields in line 2, saw 8

Answer 1

Supplying a list of columns names in the read_csv() should do the trick.

ex: names=['a', 'b', 'c', 'd', 'e']

https://github.com/pydata/pandas/issues/2981

Edit: if you don't want to supply column names then do what Nicholas suggested

Answer 2

You can dynamically generate column names as simple counters (0, 1, 2, etc).

Dynamically generate column names

# Input
data_file = "smallsample.txt"

# Delimiter
data_file_delimiter = ','

# The max column count a line in the file could have
largest_column_count = 0

# Loop the data lines
with open(data_file, 'r') as temp_f:
    # Read the lines
    lines = temp_f.readlines()

    for l in lines:
        # Count the column count for the current line
        column_count = len(l.split(data_file_delimiter)) + 1
        
        # Set the new most column count
        largest_column_count = column_count if largest_column_count < column_count else largest_column_count

# Generate column names (will be 0, 1, 2, ..., largest_column_count - 1)
column_names = [i for i in range(0, largest_column_count)]

# Read csv
df = pandas.read_csv(data_file, header=None, delimiter=data_file_delimiter, names=column_names)
# print(df)

Missing values will be assigned to the columns which your CSV lines don't have a value for.

Answer 3

Polished version of P.S. answer is as follows. It works. Remember we have inserted lot of missing values in the dataframe.

### Loop the data lines
with open("smallsample.txt", 'r') as temp_f:
    # get No of columns in each line
    col_count = [ len(l.split(",")) for l in temp_f.readlines() ]

### Generate column names  (names will be 0, 1, 2, ..., maximum columns - 1)
column_names = [i for i in range(0, max(col_count))]

### Read csv
df = pd.read_csv("smallsample.txt", header=None, delimiter=",", names=column_names)

Answer 4

If you want something really concise without explicitly giving column names, you could do this:

• Make a one column DataFrame with each row being a line in the .csv file
• Split each row on commas and expand the DataFrame

df = pd.read_fwf('<filename>.csv', header=None)
df[0].str.split(',', expand=True)

Answer 5

Error tokenizing data. C error: Expected 4 fields in line 2, saw 8

The error gives a clue to solve the problem "Expected 4 fields in line 2", saw 8 means length of the second row is 8 and first row is 4.

import pandas as pd
# inside range set the maximum value you can see in "Expected 4 fields in line 2, saw 8"
# here will be 8 
data = pd.read_csv("smallsample.txt",header = None,names=range(8))

Use range instead of manually setting names as it will be cumbersome when you have many columns.

You can use shantanu pathak's method to find longest row length in your data.

Additionally you can fill up the NaN values with 0, if you need to use even data length. Eg. for clustering (k-means)

new_data = data.fillna(0)

Answer 6

We could even use pd.read_table() method to read csv file which converts it into type DataFrame of single columns which can be read and split by ','

Answer 7

Manipulate your csv and in the first row, put the row that has the most elements, so that all next rows have less elements. Pandas will create as much columns as the first row has.