Storing the columns of a spreadsheet in a Python dictionary

Question

I have a table stored in an Excel file as follows:

Species     Garden Hedgerow Parkland Pasture Woodland
Blackbird       47       10      40        2        2
Chaffinch       19        3       5        0        2
Great Tit       50        0      10        7        0
House Sparrow   46       16       8        4        0
Robin            9        3       0        0        2
Song Thrush      4        0       6        0        0

I am using the xlrd Python library for reading these data. I have no problem reading it into a list of lists (with each line of the table stored as a list), using the code below:

from xlrd import open_workbook
wb = open_workbook("Sample.xls")
headers = []
sdata = []
for s in wb.sheets():
    print "Sheet:",s.name
    if s.name.capitalize() == "Data":
        for row in range(s.nrows):
            values = []
            for col in range(s.ncols):
                data = s.cell(row,col).value
                if row == 0:
                    headers.append(data)
                else:   
                    values.append(data)
            sdata.append(values)

As is probably obvious, headers is a simple list storing the column headers and sdata contains the table data, stored as a list of lists. Here is what they look:

headers:

[u'Species', u'Garden', u'Hedgerow', u'Parkland', u'Pasture', u'Woodland']

sdata:

[[u'Blackbird', 47.0, 10.0, 40.0, 2.0, 2.0], [u'Chaffinch', 19.0, 3.0, 5.0, 0.0, 2.0], [u'Great Tit', 50.0, 0.0, 10.0, 7.0, 0.0], [u'House Sparrow', 46.0, 16.0, 8.0, 4.0, 0.0], [u'Robin', 9.0, 3.0, 0.0, 0.0, 2.0], [u'Song Thrush', 4.0, 0.0, 6.0, 0.0, 0.0]]

But I want to store these data into a Python dictionary, with each column as the key for a list containing all values for each column. For example (only part of the data is shown to save space):

dict = {
    'Species': ['Blackbird','Chaffinch','Great Tit'], 
    'Garden': [47,19,50], 
    'Hedgerow': [10,3,0], 
    'Parkland': [40,5,10], 
    'Pasture': [2,0,7], 
    'Woodland': [2,2,0]
} 

So, my question is: how can I achieve this? I know I could read the data by columns instead of by rows as in the code snippet above, but I could not figure out how to store the columns in a dictionary.

Thanks in advance for any assistance you can provide.

Answer 1

1 . XLRD

I would highly recommend using defaultdict from collections library. The value of each key will be initiated with the default value, an empty list in this case. I did not put that much exception catch there, you might want to add in exception detection based on your use case.

import xlrd
import sys
from collections import defaultdict
result = defaultdict(list)
workbook = xlrd.open_workbook("/Users/datafireball/Desktop/stackoverflow.xlsx")
worksheet = workbook.sheet_by_name(workbook.sheet_names()[0])

headers = worksheet.row(0)
for index in range(worksheet.nrows)[1:]:
    try:
        for header, col in zip(headers, worksheet.row(index)):
            result[header.value].append(col.value)
    except:
        print sys.exc_info()

print result

Output:

defaultdict(<type 'list'>, 
{u'Garden': [47.0, 19.0, 50.0, 46.0, 9.0, 4.0], 
u'Parkland': [40.0, 5.0, 10.0, 8.0, 0.0, 6.0], 
u'Woodland': [2.0, 2.0, 0.0, 0.0, 2.0, 0.0], 
u'Hedgerow': [10.0, 3.0, 0.0, 16.0, 3.0, 0.0], 
u'Pasture': [2.0, 0.0, 7.0, 4.0, 0.0, 0.0], 
u'Species': [u'Blackbird', u'Chaffinch', u'Great Tit', u'House Sparrow', u'Robin', u'Song Thrush']})

2 . Pandas

import pandas as pd
xl = pd.ExcelFile("/Users/datafireball/Desktop/stackoverflow.xlsx")
df = xl.parse(xl.sheet_names[0])
print df

Output, and you cannot imagine how much flexibility you can gain using dataframe.

             Species  Garden  Hedgerow  Parkland  Pasture  Woodland
0      Blackbird      47        10        40        2         2
1      Chaffinch      19         3         5        0         2
2      Great Tit      50         0        10        7         0
3  House Sparrow      46        16         8        4         0
4          Robin       9         3         0        0         2
5    Song Thrush       4         0         6        0         0

Answer 2

Once you have the columns, it's fairly easy:

dict(zip(headers, sdata))

Actually, it looks like sdata in your example may be the row data, even so, that's still fairly easy, you can transpose the table with zip as well:

dict(zip(headers, zip(*sdata)))

One of these two is what you are asking for.

Answer 3

I will contribute myself, providing yet another answer for my own question!

Just after I posted my question, I found out pyexcel -- a pretty little Python library which acts as a wrapper for other spreadsheet-handling packages (namely, xlrd and odfpy). It has a nice to_dict method which does exactly what I want (even without the need to transpose the table)!

Here is an exemple, using the data above:

from pyexcel import SeriesReader
from pyexcel.utils import to_dict

sheet = SeriesReader("Sample.xls")
print sheet.series() #--- just the headers, stored in a list
data = to_dict(sheet)
print data #--- the full dataset, stored in a dictionary

Output:

u'Species', u'Garden', u'Hedgerow', u'Parkland', u'Pasture', u'Woodland']
{u'Garden': [47.0, 19.0, 50.0, 46.0, 9.0, 4.0], u'Hedgerow': [10.0, 3.0, 0.0, 16.0, 3.0, 0.0], u'Pasture': [2.0, 0.0, 7.0, 4.0, 0.0, 0.0], u'Parkland': [40.0, 5.0, 10.0, 8.0, 0.0, 6.0], u'Woodland': [2.0, 2.0, 0.0, 0.0, 2.0, 0.0], u'Species': [u'Blackbird', u'Chaffinch', u'Great Tit', u'House Sparrow', u'Robin', u'Song Thrush']}

Hope it also helps!