Converting columns in a file into separate lists using python

Question

I have an excel file and I want to read each column and create separate lists for them. I want to then compare each element of each list with other corresponding lists I have. Is there a way I can convert the columns into lists using python?

for eg(i can get this file as a txt space separated file or an excel file)

HST_9578_02_ACS_WFC_F775W 245.8976441 -26.5255957 4339.570 1882.364
HST_10615_03_ACS_WFC_F435W 245.8976450 -26.5255138 2084.978 2101.122
HST_10120_02_ACS_WFC_F658N 245.8976758 -26.5255024 1778.055 1752.193
HST_10775_64_ACS_WFC_F606W 245.8977532 -26.5255296 2586.612 2603.519
HST_10775_64_ACS_WFC_F814W 245.8977532 -26.5255296 2586.612 2603.519
HST_9578_02_ACS_WFC_F775W 245.8978148 -26.5255491 4328.571 1885.712
HST_10120_02_ACS_WFC_F625W 245.8978053 -26.5254741 1769.711 1754.229
HST_10353_02_ACS_WFC_F435W 245.8976003 -26.5257784 3758.430 985.125
HST_10775_64_ACS_WFC_F606W 245.8979115 -26.5254936 2576.410 2606.114

Levon · Accepted Answer · 2012-06-29 15:25:32Z

3

This sounds like a job for Python's CSV module. Each row read is returned as a list of strings.

To borrow a short example from the documentation:

Each row read from the csv file is returned as a list of strings. No automatic data type conversion is performed.

A short usage example:

[this simply prints out each row]

import csv
with open('some.csv', 'rb') as f:
    reader = csv.reader(f)
    for row in reader:
        print row

You could get to specific columns by indexing into the rows with the index values as appropriate.

Or if you want to do this "manually" (each row is separated by a ,):

s = """HST_9578_02_ACS_WFC_F775W 245.8976441 -26.5255957 4339.570 1882.364,
HST_10615_03_ACS_WFC_F435W 245.8976450 -26.5255138 2084.978 2101.122,
HST_10120_02_ACS_WFC_F658N 245.8976758 -26.5255024 1778.055 1752.193,
HST_10775_64_ACS_WFC_F606W 245.8977532 -26.5255296 2586.612 2603.519,
HST_10775_64_ACS_WFC_F814W 245.8977532 -26.5255296 2586.612 2603.519,
HST_9578_02_ACS_WFC_F775W 245.8978148 -26.5255491 4328.571 1885.712,
HST_10120_02_ACS_WFC_F625W 245.8978053 -26.5254741 1769.711 1754.229,
HST_10353_02_ACS_WFC_F435W 245.8976003 -26.5257784 3758.430 985.125,
HST_10775_64_ACS_WFC_F606W 245.8979115 -26.5254936 2576.410 2606.114
"""

bl = [[],[],[],[],[]]
for r in s.split(','):
    for c in range(5):
        bl[c].append(r.split()[c])

gives:

bl[0]
['HST_9578_02_ACS_WFC_F775W', 'HST_10615_03_ACS_WFC_F435W', 'HST_10120_02_ACS_WFC_F658N', 'HST_10775_64_ACS_WFC_F606W', 'HST_10775_64_ACS_WFC_F814W', 'HST_9578_02_ACS_WFC_F775W', 'HST_10120_02_ACS_WFC_F625W', 'HST_10353_02_ACS_WFC_F435W', 'HST_10775_64_ACS_WFC_F606W']

bl[1]
['245.8976441', '245.897645', '245.8976758', '245.8977532', '245.8977532', '245.8978148', '245.8978053', '245.8976003', '245.8979115']

bl[2]
['-26.5255957', '-26.5255138', '-26.5255024', '-26.5255296', '-26.5255296', '-26.5255491', '-26.5254741', '-26.5257784', '-26.5254936']

bl[3]
['4339.57', '2084.978', '1778.055', '2586.612', '2586.612', '4328.571', '1769.711', '3758.43', '2576.41']

bl[4]
['1882.364', '2101.122', '1752.193', '2603.519', '2603.519', '1885.712', '1754.229', '985.125', '2606.114']

EDIT/UPDATE:

To combine the two approaches into one:

import csv

with open('so.csv') as f:
    bl = [[],[],[],[],[]]
    reader = csv.reader(f)
    for row in reader:
        for col in range(5):
            bl[col].append(row[col])

The advantage of using with to open the file is that it will be automatically closed for you when you are done or if an exception occurs.

edited Jun 29, 2012 at 15:25

answered Jun 29, 2012 at 13:52

Levon

144k35 gold badges205 silver badges194 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

user1491298 Over a year ago

Would the manual part still work if my input file(substituting for 's' in your manual part) is an excel file?

Levon Over a year ago

@user1491298 yes, since the cvs module returns each row as lists of strings. This in my code for r in s.split(','): basically simulates that.

user1491298 Over a year ago

It keeps telling me list index is out of range for the last line bl[c].append(r.split()[c]) and I also changed the split(',') to split('\t') for my file

user1491298 Over a year ago

I might be doing something wrong here, sorry, I have only just started coding in python But it still gives me the same error that list index is out of range Does it have anything to do with my source file? I can have my source file either as space separated values(it doesn't do comma separated) or in the form of an excel file). The only way it can be converted to a csv is manually and it's not feasible as my file is couple thousand lines long

user1491298 Over a year ago

@LevonIt does work! I realized that I had made a spelling mistake in the path of the .csv file! Thanks a lot!!! and just to check , if I want to print the 3th element of the first column I should use bl[0:2] right?

|

Emmanuel · Accepted Answer · 2012-06-29 13:55:59Z

>>> s = """HST_9578_02_ACS_WFC_F775W 245.8976441 -26.5255957 4339.570 1882.364
HST_10615_03_ACS_WFC_F435W 245.8976450 -26.5255138 2084.978 2101.122
HST_10120_02_ACS_WFC_F658N 245.8976758 -26.5255024 1778.055 1752.193
HST_10775_64_ACS_WFC_F606W 245.8977532 -26.5255296 2586.612 2603.519
HST_10775_64_ACS_WFC_F814W 245.8977532 -26.5255296 2586.612 2603.519
HST_9578_02_ACS_WFC_F775W 245.8978148 -26.5255491 4328.571 1885.712
HST_10120_02_ACS_WFC_F625W 245.8978053 -26.5254741 1769.711 1754.229
HST_10353_02_ACS_WFC_F435W 245.8976003 -26.5257784 3758.430 985.125
HST_10775_64_ACS_WFC_F606W 245.8979115 -26.5254936 2576.410 2606.114
"""
>>> from collections import defaultdict
>>> cols = defaultdict(list)
>>> for line in s.split('\n'):
    for index, val in enumerate(line.split()):
        cols[index].append(val)


>>> cols
defaultdict(<type 'list'>, {0: ['HST_9578_02_ACS_WFC_F775W', 'HST_10615_03_ACS_WFC_F435W', 'HST_10120_02_ACS_WFC_F658N', 'HST_10775_64_ACS_WFC_F606W', 'HST_10775_64_ACS_WFC_F814W', 'HST_9578_02_ACS_WFC_F775W', 'HST_10120_02_ACS_WFC_F625W', 'HST_10353_02_ACS_WFC_F435W', 'HST_10775_64_ACS_WFC_F606W'], 1: ['245.8976441', '245.8976450', '245.8976758', '245.8977532', '245.8977532', '245.8978148', '245.8978053', '245.8976003', '245.8979115'], 2: ['-26.5255957', '-26.5255138', '-26.5255024', '-26.5255296', '-26.5255296', '-26.5255491', '-26.5254741', '-26.5257784', '-26.5254936'], 3: ['4339.570', '2084.978', '1778.055', '2586.612', '2586.612', '4328.571', '1769.711', '3758.430', '2576.410'], 4: ['1882.364', '2101.122', '1752.193', '2603.519', '2603.519', '1885.712', '1754.229', '985.125', '2606.114']})
>>> cols[0]
['HST_9578_02_ACS_WFC_F775W', 'HST_10615_03_ACS_WFC_F435W', 'HST_10120_02_ACS_WFC_F658N', 'HST_10775_64_ACS_WFC_F606W', 'HST_10775_64_ACS_WFC_F814W', 'HST_9578_02_ACS_WFC_F775W', 'HST_10120_02_ACS_WFC_F625W', 'HST_10353_02_ACS_WFC_F435W', 'HST_10775_64_ACS_WFC_F606W']
>>>

Ashwini Chaudhary · Accepted Answer · 2012-06-29 14:00:46Z

0

with open('data.txt') as f:
   lis=[x.split() for x in f]
cols=[x for x in zip(*lis)]
for x in cols:
    print(x)

output:

 ('HST_9578_02_ACS_WFC_F775W', 'HST_10615_03_ACS_WFC_F435W', 'HST_10120_02_ACS_WFC_F658N', 'HST_10775_64_ACS_WFC_F606W', 'HST_10775_64_ACS_WFC_F814W', 'HST_9578_02_ACS_WFC_F775W', 'HST_10120_02_ACS_WFC_F625W', 'HST_10353_02_ACS_WFC_F435W', 'HST_10775_64_ACS_WFC_F606W')
('245.8976441', '245.8976450', '245.8976758', '245.8977532', '245.8977532', '245.8978148', '245.8978053', '245.8976003', '245.8979115')
('-26.5255957', '-26.5255138', '-26.5255024', '-26.5255296', '-26.5255296', '-26.5255491', '-26.5254741', '-26.5257784', '-26.5254936')
('4339.570', '2084.978', '1778.055', '2586.612', '2586.612', '4328.571', '1769.711', '3758.430', '2576.410')
('1882.364', '2101.122', '1752.193', '2603.519', '2603.519', '1885.712', '1754.229', '985.125', '2606.114')

edited Jun 29, 2012 at 14:00

answered Jun 29, 2012 at 13:52

Ashwini Chaudhary

252k60 gold badges478 silver badges519 bronze badges

4 Comments

Emmanuel Over a year ago

I think the OP wanted columns, not lines.

user1491298 Over a year ago

Is there a way to read columns instead of rows such that I have all of the HST names in one list, the next column in another list and so on?

user1491298 Over a year ago

@Ashwini thanks! But instead of just creating an output is it also possible to create 5 separate lists for each of the columns?

Ashwini Chaudhary Over a year ago

@user1491298 instead of creating five separate lists, I'd prefer a list of lists or list of tuples, which is what i've done here.

Winston Ewert · Accepted Answer · 2012-06-29 15:35:52Z

0

One liner version:

with open('go.txt') as input:
    print zip(*(line.split() for line in input))

Or with csv

with open('go.txt') as input:
     print zip(*csv.reader(input, delimiter = ' '))

zip(*) ends up doing the transformation you need, converting each column into its own list. zip does the opposite transformation, combining the columns back into a list.

answered Jun 29, 2012 at 15:35

Winston Ewert

45.2k10 gold badges70 silver badges86 bronze badges

Collectives™ on Stack Overflow

Converting columns in a file into separate lists using python

4 Answers 4

9 Comments

Comments

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

9 Comments

Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related