I am trying to calculate the attendance in 3 courses.
The raw data in an Excel spreadsheet looks like this (“1” means attended, “0” means no):
What’s needed is to calculate:
In those who attended Course A, how many of them (%) attended Course B, and attended Course C. In those who attended Course B, how many of them (%) attended Course A, and attended Course C. In those who attended Course C, how many of them (%) attended Course A, and attended Course B.
The results I have from the codes are here.
They mean:
In those attended Course A, 100% attended Course A, 50% attended Course B, and 75% attended Course C.
In those attended Course B, 67% attended Course A, 100% attended Course B, and 100% attended Course C.
In those attended Course C, 75% attended Course A, 75% attended Course B, and 100% attended Course C.
Project A to Project A@100%
Project B to Project A@50%
Project C to Project A@75%
- - - - - - - - -
Project A to Project B@67%
Project B to Project B@100%
Project C to Project B@100%
- - - - - - - - -
Project A to Project C@75%
Project B to Project C@75%
Project C to Project C@100%
As you can see, the clumsy codes running isn’t very smart. And if the number of courses (columns) increased largely, for example to 100 columns, manual add-on is a tedious job.
What’s the smart way to do such calculations? Thank you.
from xlrd import open_workbook,cellname
import xlwt, xlrd
from xlutils.copy import copy
from xlwt import Workbook,easyxf,Formula
workbook = xlrd.open_workbook("C:\\Sheet1.xls")
old_sheet = workbook.sheet_by_index(0)
B1 = old_sheet.cell(0, 1).value
C1 = old_sheet.cell(0, 2).value
D1 = old_sheet.cell(0, 3).value
sum_of_Column_B = []
sum_of_Column_C = []
sum_of_Column_D = []
Column_B_B = []
Column_B_C = []
Column_B_D = []
Column_C_B = []
Column_C_C = []
Column_C_D = []
Column_D_B = []
Column_D_C = []
Column_D_D = []
for row_index in range(1, old_sheet.nrows):
# Column_A = old_sheet.cell(row_index, 0).value
Column_B = old_sheet.cell(row_index, 1).value
Column_C = old_sheet.cell(row_index, 2).value
Column_D = old_sheet.cell(row_index, 3).value
sum_of_Column_B.append(int(Column_B))
sum_of_Column_C.append(int(Column_C))
sum_of_Column_D.append(int(Column_D))
# Paragraph 1
if Column_B == 1 and Column_B == 1:
Column_B_B.append(1)
if Column_B == 1 and Column_C == 1:
Column_B_C.append(1)
if Column_B == 1 and Column_D == 1:
Column_B_D.append(1)
# Paragraph 2
if Column_C == 1 and Column_B == 1:
Column_C_B.append(1)
if Column_C == 1 and Column_C == 1:
Column_C_C.append(1)
if Column_C == 1 and Column_D == 1:
Column_C_D.append(1)
# Paragraph 3
if Column_D == 1 and Column_B == 1:
Column_D_B.append(1)
if Column_D == 1 and Column_C == 1:
Column_D_C.append(1)
if Column_D == 1 and Column_D == 1:
Column_D_D.append(1)
# Paragraph 1
B_over_B = float(sum(Column_B_B)) / float(sum(sum_of_Column_B))
C_over_B = float(sum(Column_B_C)) / float(sum(sum_of_Column_B))
D_over_B = float(sum(Column_B_D)) / float(sum(sum_of_Column_B))
# Paragraph 2
B_over_C = float(sum(Column_C_B)) / float(sum(sum_of_Column_C))
C_over_C = float(sum(Column_C_C)) / float(sum(sum_of_Column_C))
D_over_C = float(sum(Column_C_D)) / float(sum(sum_of_Column_C))
# Paragraph 3
B_over_D = float(sum(Column_D_B)) / float(sum(sum_of_Column_D))
C_over_D = float(sum(Column_D_C)) / float(sum(sum_of_Column_D))
D_over_D = float(sum(Column_D_D)) / float(sum(sum_of_Column_D))
# Paragraph 1
print B1 + " to " + B1 + " + {0:.0f}%".format(B_over_B * 100)
print C1 + " to " + B1 + " + {0:.0f}%".format(C_over_B * 100)
print D1 + " to " + B1 + " + {0:.0f}%".format(D_over_B * 100)
# Paragraph 2
print " - " * 20
print B1 + " to " + C1 + " + {0:.0f}%".format(B_over_C * 100)
print C1 + " to " + C1 + " + {0:.0f}%".format(C_over_C * 100)
print D1 + " to " + C1 + " + {0:.0f}%".format(D_over_C * 100)
# Paragraph 3
print " - " * 20
print B1 + " to " + D1 + " + {0:.0f}%".format(B_over_D * 100)
print C1 + " to " + D1 + " + {0:.0f}%".format(C_over_D * 100)
print D1 + " to " + D1 + " + {0:.0f}%".format(D_over_D * 100)