1

I am currently doing a small project for a club in my school. The idea is to filter the GPA of students if they are above a cutoff point based on their class (Freshmen, Sophomore, Junior, and Senior).

Here's an easy example; let's say I have the following .xlsx file:

GPA1

So based on their credit hours they are part of a class. If they have x < 30 Credits they are a Freshmen, x =< 59 && x >= 30 they are a sophomore, x =< 89 && x >= 60 they are a junior, and if they have x >= 90 credits they are a senior.

So the cut off for the GPA is different for each class. These are the cut offs:

  • (x < 30)Freshmen: 1.5
  • (x =< 59 && x >= 30)Sophomore: 2.0
  • (x =< 89 && x >= 60)Junior: 2.5
  • (x >= 90)Senior: 3.0

So in our case if we were to filter this data our output would be:

GPA2

I've been learning how to use xlsxwriter but I'm not sure if there's an easier way to filter something this simple.

I just need to understand how to read the data from this inputted Excel file and read the correct column to filter.

This is a way I believe could work in order to filter (just a pseudo code I mocked up): 

 for row_line in credits_column:
        grade_level = column_line[1] #This would be our Credits Column
        #check the grade level
        #Check if they're a Senior
        if grade_level >= 90:
            if gpa_level >= 3.0:
                #Keep this row!
            else:
                #Hide/Delete this row!
        #Check if they're a Junior
        elif grade_level =< 89 && grade_level >= 60:
            if gpa_level >= 2.5:
                #Keep this row!
            else:
                #Hide/Delete this row!

        elif grade_level =< 59 && grade_level >= 30:
            if gpa_level >= 2.0:
                #Keep this row!
            else:
                #Hide/Delete this row!

        elif grade_level < 30:
            if gpa_level >= 1.5:
                #Keep this row!
            else:
                #Hide/Delete this row.
        # Move on to the next worksheet row.
        row += 1

How could I grab the information on both the Credit Column and GPA column correctly, then filter?

JeanP
  • 406
  • 9
  • 27
  • Why aren't you just doing this in Excel? Using python seems like an awkward approach. This kind of stuff is best done in a spreadsheet frankly. – RobertB Oct 30 '15 at 18:22
  • The idea for the project is to use Input/Output using Python, I know can do this in Excel without an issue just using Excel's Built in feature but for the sake of the idea I want to use python. – JeanP Oct 30 '15 at 18:22
  • Well your pseudo code is confusing. You refer to `row_line[0]` as grade level, but in your grid, it is a Student Name. This is too general of an ask I think for SO. – RobertB Oct 30 '15 at 18:25
  • I meant column_line it was a mistake. I fixed it. – JeanP Oct 30 '15 at 18:27
  • Not that that is more clear since I don't know what column line is. Unfortunately, SO is not a code writing service. Write the code yourself and if you have problems, come back. Your code should comply with MCVE I think. http://stackoverflow.com/help/mcve – RobertB Oct 30 '15 at 18:31
  • I do have the code. Im just asking how do i read an excel file... The pseudo code i wrote is just a general idea of what am trying to do. – JeanP Oct 30 '15 at 18:34
  • @JeanP Have you read any of the questions that are linked to on the right side of your question? E.g. "[using-python-write-an-excel-file-with-columns-copied-from-another-excel-file](https://stackoverflow.com/questions/16560289/using-python-write-an-excel-file-with-columns-copied-from-another-excel-file?rq=1)" should tell you the answer to your question, which was "I just need to understand how to read the data from this inputted Excel file and read the correct column to filter.", right? – Oliver W. Oct 30 '15 at 22:20
  • No i had not just saw this thanks to you i will follow the questions answer and apply it to my mine thank you – JeanP Oct 30 '15 at 22:21

1 Answers1

2

You can use a module named xlrd. You can install xldd using below command:

pip install xlrd

Below are steps to read the data from the xlsx file. I assume you know the index of columns in the file. For this exercise I assume they are in columns A, B and C:

 import xlrd

 #First open the workbook
 wb = xlrd.open_workbook('path to your file.xlsx')

 #Then select the sheet. Replace the sheet1 with name of your sheet
 sheet = wb.sheet_by_name('Sheet1')

 #Then get values of each column. Excuse first item which is header
 names = sheet.col_values(0)[1:]
 credits = sheet.col_values(1)[1:]
 GPA = sheet.col_values(2)[1:]
Shahram
  • 814
  • 6
  • 10