Read CSV into a dataFrame with varying row lengths using Pandas

Question

So I have a CSV that looks a bit like this:

1 | 01-01-2019 | 724
2 | 01-01-2019 | 233 | 436
3 | 01-01-2019 | 345
4 | 01-01-2019 | 803 | 933 | 943 | 923 | 954
5 | 01-01-2019 | 454
...

And when I try to use the following code to generate a dataFrame..

df = pd.read_csv('data.csv', header=0, engine='c', error_bad_lines=False)

It only adds rows with 3 columns to the df (rows 1, 3 and 5 from above)

The rest are considered 'bad lines' giving me the following error:

Skipping line 17467: expected 3 fields, saw 9

How do I create a data frame that includes all data in my csv, possibly just filling in the empty cells with null? Or do I have to declare the max row length prior to adding to the df?

Thanks!

you will need your rows that are below the max length to have empty columns so in a normal `csv` that would be something like `a,b,c,,,,` — gold_cy, Mar 12 '19 at 19:52

score 14 · Answer 1 · answered Mar 12 '19 at 19:57

14

If using only pandas, read in lines, deal with the separator after.

import pandas as pd

df = pd.read_csv('data.csv', header=None, sep='\n')
df = df[0].str.split('\s\|\s', expand=True)

   0           1    2     3     4     5     6
0  1  01-01-2019  724  None  None  None  None
1  2  01-01-2019  233   436  None  None  None
2  3  01-01-2019  345  None  None  None  None
3  4  01-01-2019  803   933   943   923   954
4  5  01-01-2019  454  None  None  None  None

answered Mar 12 '19 at 19:57

ALollz

57,915
7
66
89

1

very compact! Would there maybe a simple way to even handle rows with text containing the separator? I'm thinking of comma as separator and text containing commas, or as in OP's case something like `2 | 01-01-2019 | "Two | | lines" | 123 | text` – theozh Mar 15 '19 at 21:44

score 13 · Answer 2 · answered Mar 15 '19 at 18:50

If you know that the data contains N columns, you can tell Pandas in advance how many columns to expect via the names parameter:

import pandas as pd
df = pd.read_csv('data', delimiter='|', names=list(range(7)))
print(df)

yields

   0             1    2      3      4      5      6
0  1   01-01-2019   724    NaN    NaN    NaN    NaN
1  2   01-01-2019   233  436.0    NaN    NaN    NaN
2  3   01-01-2019   345    NaN    NaN    NaN    NaN
3  4   01-01-2019   803  933.0  943.0  923.0  954.0
4  5   01-01-2019   454    NaN    NaN    NaN    NaN

If you have an the upper limit, N, on the number of columns, then you can have Pandas read N columns and then use dropna to drop completely empty columns:

import pandas as pd
df = pd.read_csv('data', delimiter='|', names=list(range(20))).dropna(axis='columns', how='all')
print(df)

yields

   0             1    2      3      4      5      6
0  1   01-01-2019   724    NaN    NaN    NaN    NaN
1  2   01-01-2019   233  436.0    NaN    NaN    NaN
2  3   01-01-2019   345    NaN    NaN    NaN    NaN
3  4   01-01-2019   803  933.0  943.0  923.0  954.0
4  5   01-01-2019   454    NaN    NaN    NaN    NaN

Note that this could drop columns from the middle of the data set (not just columns from the right-hand side) if they are completely empty.

The other solutions didn't work for me for some reason, but adding the `names` argument did the trick, cheers! — Amon, Nov 27 '19 at 21:50
Adding names worked for me where I had irregular columns in a large csv. Thanks! — Aditya, Jul 30 '21 at 12:31

It_is_Chris · Answer 3 · 2019-03-12T20:22:49.303

Read fixed width should work:

from io import StringIO

s = '''1  01-01-2019  724
2  01-01-2019  233  436
3  01-01-2019  345
4  01-01-2019  803  933  943  923  954
5  01-01-2019  454'''


pd.read_fwf(StringIO(s), header=None)

   0           1    2      3      4      5      6
0  1  01-01-2019  724    NaN    NaN    NaN    NaN
1  2  01-01-2019  233  436.0    NaN    NaN    NaN
2  3  01-01-2019  345    NaN    NaN    NaN    NaN
3  4  01-01-2019  803  933.0  943.0  923.0  954.0
4  5  01-01-2019  454    NaN    NaN    NaN    NaN

or with a delimiter param

s = '''1 | 01-01-2019 | 724
2 | 01-01-2019 | 233 | 436
3 | 01-01-2019 | 345
4 | 01-01-2019 | 803 | 933 | 943 | 923 | 954
5 | 01-01-2019 | 454'''


pd.read_fwf(StringIO(s), header=None, delimiter='|')

   0             1    2      3      4      5      6
0  1   01-01-2019   724    NaN    NaN    NaN    NaN
1  2   01-01-2019   233  436.0    NaN    NaN    NaN
2  3   01-01-2019   345    NaN    NaN    NaN    NaN
3  4   01-01-2019   803  933.0  943.0  923.0  954.0
4  5   01-01-2019   454    NaN    NaN    NaN    NaN

note that for your actual file you will not use StringIO you would just replace that with your file path: pd.read_fwf('data.csv', delimiter='|', header=None)

score 1 · Answer 4 · answered Mar 12 '19 at 20:03

add extra columns (empty or otherwise) to the top of your csv file. Pandas will takes the first row as the default size, and anything below it will have NaN values. Example:

file.csv:

a,b,c,d,e
1,2,3
3
2,3,4

code:

>>> import pandas as pd
>>> pd.read_csv('file.csv')
   a    b    c   d   e
0  1  2.0  3.0 NaN NaN
1  3  NaN  NaN NaN NaN
2  2  3.0  4.0 NaN NaN

Rich Andrews · Answer 5 · 2019-03-12T21:49:40.620

Consider using Python csv to do the lifting for importing data and format grooming. You can implement a custom dialect to handle varying csv-ness.

import csv
import pandas as pd

csv_data = """1 | 01-01-2019 | 724
2 | 01-01-2019 | 233 | 436
3 | 01-01-2019 | 345
4 | 01-01-2019 | 803 | 933 | 943 | 923 | 954
5 | 01-01-2019 | 454"""

with open('test1.csv', 'w') as f:
    f.write(csv_data)

csv.register_dialect('PipeDialect', delimiter='|')
with open('test1.csv') as csvfile:
    data = [row for row in csv.reader(csvfile, 'PipeDialect')]
df = pd.DataFrame(data = data)

Gives you a csv import dialect and the following DataFrame:

    0             1      2      3      4      5     6
0  1    01-01-2019     724   None   None   None  None
1  2    01-01-2019    233     436   None   None  None
2  3    01-01-2019     345   None   None   None  None
3  4    01-01-2019    803    933    943    923    954
4  5    01-01-2019     454   None   None   None  None

Left as an exercise is handling the whitespace padding in the input file.

note to self: pandas.read_csv(engine=) will use python's csv module if specified. — Rich Andrews, Mar 12 '19 at 21:51

score 0 · Answer 6 · answered Mar 12 '19 at 23:59

0

colnames= [str(i) for i in range(9)]
df = pd.read_table('data.csv', header=None, sep=',', names=colnames)

Change 9 in colnames to number x if code gives the error

Skipping line 17467: expected 3 fields, saw x

answered Mar 12 '19 at 23:59

phoenixio

41
3

If you have different files with different unknown columns you certainly don't want to adjust your code every time according to the error message. – theozh Apr 26 '23 at 05:13

Read CSV into a dataFrame with varying row lengths using Pandas

6 Answers6

Linked

Related