Question

I am working on a data frame using pandas with some of the column names (PCTFLOAN, SATMTMID, STATE, INSTITUTION_NAME). Some explanation, Column name STATE has state abbreviations for each school in that particular state.

a. Data grouping. For each state in dataframe, find the 5 institutes that have the lowest loanpercentage (PCTFLOAN). Ignore all the missing values.

b. Data summarizing. For each state in dataframecalculate the average of the median SAT math scores (SATMTMID) for the 5 low loan institutes that you find in question (a). Ignore all the missing values when you calculate the average.

PYTHON PANDAS PLEASE

Answer 1

#import pandas package alias pd for reading the data

import pandas as pd

#import numpy to calculate the median of any column in data frame

from numpy import median

#read the data from csv (comma separated value) using pd.read_csv("path to file directory/file name")

dataframe = pd.read_csv("path_to_file/file_name.csv")

#selecting only PCTFLOAN column from dataframe

data_loan = dataframe["PCTFLOAN"]

#nsmallest(n,column="column_name") function is used to find the smallest n column values

lst = data_loan.nsmallest(5)

#type casting to the list

lst = list(lst)

#getting the names of all five states with lowest PCTFLOAN using retriving based on the condition based retrival isin list check for the loan PCTFLOAN value is in lst are not and based on the it returns the row from that we are retriving the dataframe with state name

df=dataframe["STATE"][dataframe["PCTFLOAN"].isin(lst)]

# df is a data frame that contains the column named STATE containing the 5 state with the lowest PCTFLOAN

sum = 0 #initial sum variable to find the average as average is equal to (sum of all medain of PCTFLOAN/num of states)

#looping through the all the states

for state in df:

satmtmid_list = df["SATMTMID"][df["STATE"]==state] #retriving the SATMTMID colums based on state value

sum+=median(satmtmid_list) #finding up the median and it to the sum repeatdly for each state

#average of median of pctfloan of five state with lowest loan per

average = sum/len(df)