getting started with processing data with python

Question

i have an excel spreadsheet of about 3 million cells. i asked the following question and i liked the answer about saving the spreadsheet as CSV and then processing it with python:

solution to perform lots of calculations on 3 million data points and make charts

is there a library that i can use that will read the csv into a matrix or should i write one myself?
does python speak with VBA at all?
after i am done processing the data, is it simple to put it back in the form of a CSV so that i can open it in excel for viewing?

possible duplicate of solution to perform lots of calculations on 3 million data points and make charts — S.Lott
– S.Lott, Commented Jul 26, 2010 at 20:58

S.Lott · Accepted Answer · 2010-07-26 20:58:00Z

3

is there a library that i can use that will read the csv into a matrix or should i write one myself?

The csv module handles just about everything you could want.

does python speak with VBA at all?

Iron Python might.

after i am done processing the data, is it simple to put it back in the form of a CSV so that i can open it in excel for viewing?

The csv module handles just about everything you could want.

Suggestion: Read this: http://docs.python.org/library/csv.html

answered Jul 26, 2010 at 20:58

S.Lott

393k83 gold badges521 silver badges791 bronze badges

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

Comments

ChrisC · Accepted Answer · 2010-07-26 20:14:22Z

3

I like NumPy's loadtxt for this sort of thing. Very configurable for reading CSVs. And savetxt for putting it back after manipulation. Or you could check out the built in csv module if you'd rather not install anything new.

answered Jul 26, 2010 at 20:14

ChrisC

1,2729 silver badges9 bronze badges

Comments

Redwood · Accepted Answer · 2010-07-26 23:32:13Z

1

If we speak pythonish, why not to use http://www.python-excel.org/ ?

Example of read file:

import xlrd
rb = xlrd.open_workbook('file.xls',formatting_info=True)
sheet = rb.sheet_by_index(0)
for rownum in range(sheet.nrows):
  row = sheet.row_values(rownum)
  for c_el in row:
    print c_el

Writing the new file:

import xlwt
from datetime import datetime

font0 = xlwt.Font()
font0.name = 'Times New Roman'
font0.colour_index = 2
font0.bold = True

style0 = xlwt.XFStyle()
style0.font = font0

style1 = xlwt.XFStyle()
style1.num_format_str = 'D-MMM-YY'

wb = xlwt.Workbook()
ws = wb.add_sheet('A Test Sheet')

ws.write(0, 0, 'Test', style0)
ws.write(1, 0, datetime.now(), style1)
ws.write(2, 0, 1)
ws.write(2, 1, 1)
ws.write(2, 2, xlwt.Formula("A3+B3"))

wb.save('example.xls')

There are other examples on the page.

edited Jul 26, 2010 at 23:32

Redwood

69.9k45 gold badges131 silver badges187 bronze badges

answered Jul 26, 2010 at 22:01

DominiCane

1,3033 gold badges16 silver badges30 bronze badges

1 Comment

John Machin Over a year ago

Using easyxf is ummm easier. E.g.

style0 = xlwt.easyxf("font: name Times New Roman, colour red, bold true"); style1 = xlwt.easyxf(num_format_str="d-mmm-yyyy")

Josh · Accepted Answer · 2010-07-26 23:38:56Z

1

If you don't want to deal with changing back and forth from CSV you can use win32com, which can be downloaded here. http://python.net/crew/mhammond/win32/Downloads.html

answered Jul 26, 2010 at 23:38

Josh

4,5775 gold badges31 silver badges29 bronze badges

Collectives™ on Stack Overflow

getting started with processing data with python

4 Answers 4

Comments

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related