用Python在Excel中计算值

Question

我刚开始学Python，遇到这个问题很头疼，因为这是我工作上需要做的事情。

先说说这个Excel文件的情况：里面有3列，大约100行。第一列（col1）里要么是A，要么是B。第二列（col2）里是1到10之间的任意数字。第三列（col3）里是任意的小数。

我希望程序能处理这些数据。col1和col2的组合会有很多重复的情况。比如说，(A, 1)可能出现在第1行、第5行、第20行、第98行等等。但col3里的数字会不同。所以我想要找出这些不同数字的平均值。

输出的结果应该像这样：

A, 1 = avg 4.32
A, 2 = avg 7.23
A, 3 = avg -9.12
etc etc (until number 10)
B, 1 = avg 3.76
B, 2 = avg -8.12
B, 3 = avg 1.56
etc etc (until number 10)

结果不需要完全按字母和数字的顺序排列，只要能打印出它找到的第一个组合就行了。但是我到目前为止写的代码，出于某种原因只打印出了3个组合，而不是全部。

import xlrd #import package

#opening workbook and reading first sheet
book = xlrd.open_workbook('trend.xls')
sheet = book.sheet_by_index(0)

#function to hold unique combos
unique_combinations = {}

#looping through data
for row_index in range(sheet.nrows):
    #declaring what group equals to what row
    col1 = sheet.cell(row_index, 0)
    col2 = sheet.cell(row_index, 1)
    col3 = sheet.cell(row_index, 2)

    unique_combo = (col1.value, col2.value)

    if unique_combinations.has_key(unique_combo):
        unique_combinations[unique_combo].append(col3.value)
    else:
        unique_combinations[unique_combo] = [col3.value]

for k in unique_combinations.keys():
    l = unique_combinations[k]
    average = sum(l) / len(l)
    print '%s: %s Mean = %s' % (k[0], k[1], average)

其实，这就是2个大组，每个大组里又有10个小组，而这些小组里存放的是属于它们的数字的平均值。

请帮帮我！非常感谢！

这是Excel文件的一个示例：

col1 | col2 | col3
A    |   1  | 3.12
B    |   9  | 4.12
B    |   2  | 2.43
A    |   1  | 9.54
B    |   8  | 2.43
A    |   2  | 1.08

程序的工作是，当它遇到第一个组合A, 1时，它会把3.12存到一个列表里，然后继续查看下一个组合，直到遇到一个重复的组合，也就是第四行。它也会把那个值存进去。最后，输出会显示A, 1 = avg (3.12 + 9.54 / 2)。这个例子只展示了A, 1的组合。但实际上，只有2个大组（像这个例子），而col2可以从1到10。会有很多重复的组合。

Answer 1

试试一下pandas这个库吧：

In [1]: import pandas as pd

In [2]: xls = pd.ExcelFile('test.xls')
   ...: df = xls.parse('Sheet1', header=None)
   ...: 

In [3]: df
Out[3]: 
   0  1     2
0  A  1  3.12
1  B  9  4.12
2  B  2  2.43
3  A  1  9.54
4  B  8  2.43
5  A  2  1.08

In [4]: groups = df.groupby([0,1])

In [5]: for k, g in groups:
   ...:     print k, g[2].mean()
   ...:     
(u'A', 1.0) 6.33  # your example (3.12 + 9.54) / 2
(u'A', 2.0) 1.08
(u'B', 2.0) 2.43
(u'B', 8.0) 2.43
(u'B', 9.0) 4.12

如果你想把所有的平均值都放在一个列表里，完整的代码如下：

import pandas as pd
df = pd.ExcelFile('test.xls').parse('Sheet1', header=None)
print [g[2].mean() for _, g in df.groupby([0,1])]
# out: [6.3300000000000001, 1.0800000000000001, 2.4300000000000002, 2.4300000000000002, 4.1200000000000001]

Answer 2

这个建议主要是教你如何弄清楚发生了什么，读起来比评论更容易理解。

我觉得加一些调试打印和异常处理是很有必要的。

我用OpenOffice和Python 2.7试过这个例子。如果在最后一个循环中发生了异常，而我在测试运行时把错误信息给忽略掉了，我就能复现你遇到的问题。比如：python test.py 2>nul

所以我建议你试试这个：

    import xlrd
    book = xlrd.open_workbook('trend.xls')
    sheet = book.sheet_by_index(0)
    unique_combinations = {}
    for row_index in range(sheet.nrows):
        col1 = sheet.cell(row_index, 0)
        col2 = sheet.cell(row_index, 1)
        col3 = sheet.cell(row_index, 2)

        unique_combo = (col1.value, col2.value)
        if unique_combinations.has_key(unique_combo):
            print 'Update: %r = %r' % (unique_combo, col3.value)
            unique_combinations[unique_combo].append(col3.value)
        else:
            print 'Add: %r = %r' % (unique_combo, col3.value)
            unique_combinations[unique_combo] = [col3.value]

    for k in unique_combinations.keys():
        l = unique_combinations[k]
        try:
          average = sum(l) / len(l)
          print '%s: %s Mean = %s' % (k[0], k[1], average)
        except Exception, e:
          print 'Ignoring entry[%r]==%r due to exception %r' % (k, l, e)

这样应该能帮助你搞清楚你遇到的“奇怪行为”。