CSV Reader from one column whose element representing list/array not one value

Question

Below is the content from myfile.csv

  1st        2nd     3rd      4th                     5th
2061100   10638650  -8000     25         [1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0]
2061800   10639100  -8100     26         [2.0, 2.0, 2.0, 2.0, 2.0, 2.0, 2.0]
2061150   10638750  -8250     25         [3.0, 3.0, 3.0, 3.0, 3.0, 3.0, 3.0]
2061650   10639150  -8200     25         [4.0, 4.0, 4.0, 4.0, 4.0, 4.0, 4.0]
2061350   10638800  -8250     3          [5.0, 5.0, 5.0]
2060950   10638700  -8000     1          [1.0]
2061700   10639100  -8100     11         [2.0, 2.0, 2.0, 2.0, 2.0, 2.0, 2.0, 2.0]
2061050   10638800  -8250     6          [3.0, 3.0, 3.0, 3.0, 3.0, 3.0]
2061500   10639150  -8200     1          [4.0]
2061250   10638850  -8150     16         [5.0, 5.0, 5.0, 5.0]

My current code:

from numpy import genfromtxt
mydata = genfromtxt('myfile.csv', delimiter=',')
arr = np.array(mydata)
col5 = arr[:,4]

However, I want to read the 5th column as a list and then read all elements from the list to do further calculation. What shall I do?

The myfile.csv you show doesn't match the format you're reading (delimiter=','). If the actual file is comma-delimited in 1st-4th columns, then you'll have problems determining the actual boundary of the 5th column using the facilities of numpy.genfromtxt alone. — Dan Lenski
– Dan Lenski, Commented Jun 18, 2014 at 23:57

wwii · Accepted Answer · 2014-06-19 15:04:13Z

If the whitespace between the columns are tabs:

import csv, ast, pprint
result = list()
with open('in.txt') as in_file:
    reader = csv.reader(in_file, delimiter = '\t')
    for line in reader:
        line[:4] = map(int, line[:4])
        line[4] = ast.literal_eval(line[4])
        result.append(line)    

pprint.pprint(result)

>>> 
[[2061100, 10638650, -8000, 25, [1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0]],
 [2061800, 10639100, -8100, 26, [2.0, 2.0, 2.0, 2.0, 2.0, 2.0, 2.0]],
 [2061150, 10638750, -8250, 25, [3.0, 3.0, 3.0, 3.0, 3.0, 3.0, 3.0]],
 [2061650, 10639150, -8200, 25, [4.0, 4.0, 4.0, 4.0, 4.0, 4.0, 4.0]],
 [2061350, 10638800, -8250, 3, [5.0, 5.0, 5.0]],
 [2060950, 10638700, -8000, 1, [1.0]],
 [2061700, 10639100, -8100, 11, [2.0, 2.0, 2.0, 2.0, 2.0, 2.0, 2.0, 2.0]],
 [2061050, 10638800, -8250, 6, [3.0, 3.0, 3.0, 3.0, 3.0, 3.0]],
 [2061500, 10639150, -8200, 1, [4.0]],
 [2061250, 10638850, -8150, 16, [5.0, 5.0, 5.0, 5.0]]]
>>>

A variation on that theme:

with open('in.txt') as in_file:
    reader = csv.reader(in_file, delimiter = '\t')
    result = [[ast.literal_eval(item) for item in line] for line in reader]

dawg · Accepted Answer · 2014-06-19 01:42:27Z

I suppose I would be tempted to just do this manually:

with open(fn) as f:
    header=next(f).strip()
    print(header)
    for row in f:
        row=row.rstrip()
        lp,_,rp=row.partition('[')
        rp=rp.strip(']')
        lp_data=list(map(int, lp.split()))
        rp_data=list(map(float, rp.split(',')))
        print(lp_data+[rp_data])

Prints:

1st        2nd     3rd      4th                     5th
[2061100, 10638650, -8000, 25, [1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0]]
[2061800, 10639100, -8100, 26, [2.0, 2.0, 2.0, 2.0, 2.0, 2.0, 2.0]]
[2061150, 10638750, -8250, 25, [3.0, 3.0, 3.0, 3.0, 3.0, 3.0, 3.0]]
[2061650, 10639150, -8200, 25, [4.0, 4.0, 4.0, 4.0, 4.0, 4.0, 4.0]]
[2061350, 10638800, -8250, 3, [5.0, 5.0, 5.0]]
[2060950, 10638700, -8000, 1, [1.0]]
[2061700, 10639100, -8100, 11, [2.0, 2.0, 2.0, 2.0, 2.0, 2.0, 2.0, 2.0]]
[2061050, 10638800, -8250, 6, [3.0, 3.0, 3.0, 3.0, 3.0, 3.0]]
[2061500, 10639150, -8200, 1, [4.0]]
[2061250, 10638850, -8150, 16, [5.0, 5.0, 5.0, 5.0]]

elyase · Accepted Answer · 2014-06-19 16:56:19Z

1

Pandas can read fixed width files (in contrast to tab/comma delimited files) like yours:

import pandas as pd
import ast

df = pd.read_fwf('test.txt', colspecs=[(41,100)])['5th']\
       .apply(lambda x: ast.literal_eval(x))

You get:

>>> df

0         [1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0]
1         [2.0, 2.0, 2.0, 2.0, 2.0, 2.0, 2.0]
2         [3.0, 3.0, 3.0, 3.0, 3.0, 3.0, 3.0]
3         [4.0, 4.0, 4.0, 4.0, 4.0, 4.0, 4.0]
4                             [5.0, 5.0, 5.0]
5                                       [1.0]
6    [2.0, 2.0, 2.0, 2.0, 2.0, 2.0, 2.0, 2.0]
7              [3.0, 3.0, 3.0, 3.0, 3.0, 3.0]
8                                       [4.0]
9                        [5.0, 5.0, 5.0, 5.0]
Name: 5th, dtype: object

edited Jun 19, 2014 at 16:56

answered Jun 19, 2014 at 1:47

elyase

41.2k12 gold badges121 silver badges123 bronze badges

Collectives™ on Stack Overflow

CSV Reader from one column whose element representing list/array not one value

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related