import os
from openpyxl import load_workbook
import numpy as np

wb = load_workbook(os.path.expanduser("~/Downloads/Infrastructure risk review.xlsx"))

import string

nums = list(range(6, 18))
alphas = [c.upper() for c in string.ascii_lowercase if ord(c.upper()) >= ord("C") and ord(c.upper()) <= ord("V")]

if "total" not in wb.sheetnames:
    tot = wb.create_sheet("total")

temp = wb["Template"]

for i in range(1, 19):
    for a in string.ascii_uppercase:
        key = "%s%d" % (a, i)
        tot[key] = temp[key].value

for i in nums:
    for a in alphas:
        key = "%s%d" % (a, i)
        tot[key] = 0.0
        num = 0
        for sheet in wb.sheetnames:
            if sheet not in ["Template", "total"] and wb[sheet][key].value is not None:
                tot[key] = tot[key].value + wb[sheet][key].value
                num += 1
        if num > 0:
            tot[key] = tot[key].value / num

import pandas as pd
pd.set_option('display.max_columns', None)
pd.set_option('display.max_rows', None)

data = pd.DataFrame(tot.values)