Python, compare and calculate data in lists
I am trying to calculate attendance for 3 courses.
The raw data in an Excel spreadsheet looks like this ("1" means "present", "0" means "no"):
You need to calculate:
In those who studied in 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 studied in course C, how many of them (%) attended course A and attended course B.
The results obtained from the codes are given here.
They mean:
In those who attended course A, 100% attended course A, 50% attended course B, and 75% attended course C.
In those who attended course B, 67% were in course A, 100% attended course B, and 100% attended course C.
Of those who 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 are not very smart. And if the number of courses (columns) has increased significantly, for example to 100 columns, adding manually is a tedious job.
What's the smart way to do this kind of calculation? Thank.
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)
source to share
Bogus data
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)
in the reality
df = pd.read_excel(filename)
courses = df.columns
You may need to change some of the arguments, especially index_col
andheader
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
Comparison
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)
results
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
Thus, 75% of those who attended course C attended course A
source to share
my csv looks like this:
Name;Course A;Course B;Course C David;1;0;1 Kate;0;1;1 Tom;1;1;1 Andrew;1;0;0 Jason;0;0;0 Peter;1;1;1
import data like this:
data = pd.read_csv('test.csv',sep=';')
columns = data.columns.tolist ()
columns.remove('Name')
Here is a function that takes a course as input and gives you what you want as output:
def assistance(cour):
print("100 percent of student who assisted {}".format(cour))
for Course in columns:
if Course != cour:
assistance = data.groupby(cour).mean().loc[1, Course] * 100
print ("assisted {0} at {1} percent".format(Course, assistance))
Output
> assistance('Course A')
100 percent of student who assisted Course A
assisted Course B at 50.0 percent
assisted Course C at 75.0 percent
To get all the information in a DataFrame:
df = pd.DataFrame(index=columns, columns=columns)
for row in columns:
for c in columns:
if row != c:
df.loc[row,c] = data.groupby(row).mean().loc[1,c] * 100
else:
df.loc[row,c] = float(100)
Output
print(df)
Course A Course B Course C
Course A 100 50 75
Course B 66.6667 100 100
Course C 75 75 100
source to share