Using an Excel table format in python

I have an Excel table saved as a CSV that looks something like (when it's in Excel) this just longer:

    Monday  Tuesday     Wednesday   Thursday    Friday
marsbar 9   8   0   6   5
reeses  0   0   0   9   0
twix    2   3   0   5   6
snickers    9   8   0   6   5

(The format isnt perfect, but you get the gist--candy bars/day). I want to write a program for python in which I could input a list of candy bar names in the program

>>> ['twix', 'reeses']

and it would give me the days of the week on which those candy bars were eaten and how many candy bars in the list were eaten on that day. The result would look something like this:

Monday 2
Tuesday 3
Wednesday 0
Thursday 14
Friday 6

could someone help me write such a program?开发者_如何转开发

We can help you write such a program, yes.

We will not write the program for you (that's not what StackOverflow is about).

Python comes with a CSV module, which will read in an Excel-saved CSV file.

You can store your data in a dictionary, which will make it easier to retrieve.

Good luck!

Your best bet is to use the csv package built into python. It will let you loop over the rows, giving you a list each time, like so:

import csv
candy_reader = csv.reader(open('candy.csv', 'rb'))

for row in candy_reader:
  if row[0] in candy_to_find:
    monday_total += row[1]
    # etc.    

I didn't initialize several variables, but I think this should give you the basic idea.

Maybe with the csv module

I suppose the delimiter is the tab character (\t)

import csv
reader = csv.reader(open('csv_file.csv', 'rb'), delimiter='\t')
for row in reader:
    print row

>>>['Monday','Tuesday', 'Wednesday', 'Thursday', 'Friday']
['marsbar', '9', '8','0', '6', '5']
['reeses', '0', '0', '0', '9', '0']
['twix', '2', '3', '0', '5', '6']
['snickers', '9', '8', '0',  '6',  '5']

In addition to the other answers, you may also want to take a look at the xlrd module, as it can deal with the excel spreadsheets directly if you'd rather not deal with CSV business. The module allows you to read entire (or ranges) of rows/columns at a time as well as grabbing individual cells and you can iterate through everything to search for what you want and then return whatever related 'values' you want in the same row/column from where you found your search criteria.

Some useful bits:

If you have a list of lists like

[['marsbar', 0, 1, 2, 3, 4], ['twix', 3, 4, 5, 6, 7]]

(which you should be able to get by using the csv module)

You will probably want to convert it to a dictionary, where the first item of each list is used for the key, and the rest make up the value. You can do this with something like

dict((x[0], x[1]) for x in list_of_lists)

You can look up multiple keys with a list comprehension as well:

[the_dict[key] for key in key_list]

That gives you a list of lists, where you want to sum the first elements of each list, the second elements, etc. To do that, we 'zip' the lists to make a list of lists with all the first elements, all the second elements etc., and then sum the inner lists.

[sum(x) for x in zip(*the_requested_candybars)]

The zip() function takes multiple arguments; the * here turns the list of lists into several list arguments.

We can zip again to match up the week names with the sums.