Python,比较并计算列表中的数据

马克·K

我正在尝试计算3门课程的出勤率。

Excel电子表格中的原始数据如下所示(“ 1”表示已出席,“ 0”表示否):

在此处输入图片说明

需要计算的是:

在参加课程A的学生中,有多少(%)参加了课程B和课程C。在参加课程B的那些人中,有多少(%)参加了课程A和课程C。课程C,其中有多少(%)参加了课程A,然后参加了课程B。

我从代码中得到的结果在这里。

他们的意思:

在参加课程A的人员中,有100%参加了课程A,有50%参加了课程B,有75%参加了课程C。

在参加课程B的人员中,有67%参加了课程A,100%参加了课程B,并且100%参加了课程C。

在参加课程C的参与者中,有75%参加了课程A,有75%参加了课程B,还有100%参加了课程C。

Project A to Project [email protected]%
Project B to Project [email protected]%
Project C to Project [email protected]%
 -  -  -  -  -  -  -  -  -  
Project A to Project [email protected]%
Project B to Project [email protected]%
Project C to Project [email protected]%
 -  -  -  -  -  -  -  -  -
Project A to Project [email protected]%
Project B to Project [email protected]%
Project C to Project [email protected]%

如您所见,笨拙的代码运行不是很聪明。而且,如果课程(列)的数量大幅增加,例如增加到100列,则手动添加是一项繁琐的工作。

进行此类计算的明智方法是什么?谢谢。

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)
马丁·法布里(MaartenFabré)

虚拟数据

import pandas as pd
import itertools
import numpy as np

names = [f'student {i}' for i in range(1, 8)]
courses = [f'course {i}' for i in 'ABC']
df = pd.DataFrame(data = np.random.randint(0, 2, size=(len(names),len(courses))), index = names, columns=courses)

事实上

df = pd.read_excel(filename)
courses = df.columns

您可能需要调整一些参数,特别是index_colheader

df

    course A    course B    course C
student 1   0   0   1
student 2   1   1   0
student 3   1   0   0
student 4   0   1   0
student 5   1   0   1
student 6   1   0   1
student 7   1   0   1

比较方式

results = pd.DataFrame(columns=courses, index=courses)
for i, j in itertools.product(courses, repeat=2):
    attended = df[df[i] == 1]
    results.loc[i, j] = sum(attended[i] & attended[j]) / len(attended)

结果

    course A    course B    course C
course A    1   0.2     0.6
course B    0.5     1   0
course C    0.75    0   1

因此,参加课程C的人中有75%参加了课程A

本文收集自互联网,转载请注明来源。

如有侵权,请联系 [email protected] 删除。

编辑于
0

我来说两句

0 条评论
登录 后参与评论

相关文章