代写ISOM 2007、代做 Python 程序设计
UNIVERSITY OF MACAU FACULTY OF BUSINESS ADMINISTRATION
ISOM 2007 - Programming for Business Analytics
2023/2024 (First Semester)
Assignment 2
Due date: December 8, 2023 (12:00, noon)
Instructions:
➢ Read the Assignment Requirements posted in the UMMoodle before attempting to solve the following problems with Python programs.
➢ Both the hardcopy and softcopy of your assignment should be submitted on time. All programs (.ipynb files) are to be compressed and uploaded to the UMMoodle under the “Submit Assignment 2” button. The compressed file should be named with your student number such as “ba12345_Ass2.zip”.
Before the questions:
This task revolves around analyzing a dataset containing information about universities in the United States. The dataset includes various attributes, such as graduation rates, enrollment statistics, and demographic data. The goal is to explore this dataset, answer a series of questions, and provide insights and comments based on the findings.
Throughout the analysis, you will be calculating correlations, ranking universities based on specific criteria, comparing different university types, and investigating the relationship between enrollment and the representation of different ethnic groups. By working through these questions, you will gain a deeper understanding of the data and identify trends and patterns that can inform decision-making in higher education.
Once you have completed the provided questions, consider what other data might be useful to enhance your analysis. For example, you could examine the relationship between tuition fees, financial aid, and student retention rates or investigate the impact of university size and location on the student experience. Explain that with additional data, how you can perform further analysis to support your comments and insights, enabling you to make more informed recommendations to
P.1
universities, policymakers, and prospective students.
Question 1: Graduation rate in states (30%)
Which states have the highest/lowest average graduation rates for Bachelor's degrees within 4 years?
• Read the dataset into a Pandas DataFrame. (2%)
• Group the data by the 'State abbreviation' column. (3%)
• Calculate the mean graduation rate for each state. (3%)
• Find the top 5 states with the highest average graduation rates. (6%)
• Find the bottom 5 states with the lowest average graduation rates. (6%)
• Compare the top 5 and bottom 5 states, discuss the possible reasons that why these states have
highest (lowest) graduation rates. (10%)
Sample Output:
The states with the highest average graduation rate: State abbreviation
Rhode Island Massachusetts Connecticut
District of Columbia Pennsylvania
56.875000 51.285714 50.588235
47.375000 46.911392
Name: Graduation rate - Bachelor degree within 4 years, dtype: float64
The states with the lowest average graduation rate: State abbreviation
Alaska
New Mexico
Montana
Hawaii
Alabama
Name: Graduation rate - Bachelor degree within 4 years, dtype: float64
Question 2: Women students in public/private universities (20%)
Compare the average percentage of undergraduate enrollment that are women for public and private universities.
• Filter the dataset into two separate DataFrames: one for public universities and one for private universities. (4%)
• Calculate the average percentage of undergraduate enrollment that are women for each DataFrame. (5%)
• Calculate the median percentage of undergraduate enrollment that are women for each DataFrame. (5%)
13.000000 16.750000 18.142857
18.250000 21.100000
P.2
•
Compare the results and discuss any observed differences. (6%)
Sample Output:
Public universities have an average of 54.88% women in undergraduate enrollment.
Private universities have an average of 59.07% women in undergraduate enrollment.
Public universities have a median of 56.00% women in undergraduate enrollment.
Private universities have a median of 58.00% women in undergraduate enrollment.
Question 3: University ranking (50%)
Students care much about university ranking. However, ranking university is not an easy task. It necessitates the consideration of numerous aspects and the assignment of appropriate significance. Predominantly, three factors are widely used: the admission rate, the graduation rate, and student selectivity. Below please analyze these factors and offer your astute observations and recommendations concerning university ranking.
• Calculate the correlation between the 'Applicants total' and 'Admissions total' columns in the dataset, also the correlation between the 'Applicants total' and Enrolled total' columns in the dataset. Interpret the correlation values and discuss any potential implications. (10%)
• Rank all the universities based on their graduation rate for Bachelor's degrees within 4 years and student selectivity using the 25th percentile SAT or ACT scores. First, add up the SAT Critical Reading, SAT Math, and SAT Writing 25th percentile scores, then normalize it by dividing 2400 (the full score of SAT). Normalize the ACT Composite 25th percentile score by 36 (the full score of ACT). In this way, both scores are in percentage and can be compared. Then, use the larger of these two values as the student selectivity criterion. Create a composite ranking by averaging the rankings for graduation rate and student selectivity. Discuss the ranking and identify the top 10 universities according to three criteria:
1, the graduation rate; (10%)
2, the student selectivity; (10%)
3, the composite criterion. (10%)
Discuss which criterion is more reliable, what are the caveats of the criteria, and what other factors (if have) may need to be included. (10%)
Sample Output (the indices may not necessarily be the same):
P.3
Bonus Question (10%)
Investigate the relationship between the total enrollment and the percentage of different ethnic groups (Asian, Black or African American, Hispanic/Latino, and White) at universities, and compare the results for private and public universities.
• Calculate the total enrollment for each ethnic group by multiplying the percentage of the total enrollment by the total enrollment for each university. Sum up the total enrollment for each ethnic group across all universities. Calculate the percentage of each ethnic group's enrollment in comparison to the overall total enrollment. Discuss the representation of each ethnic group in US universities based on the calculated percentages.
• Separately calculate the total enrollment and percentages of different ethnic groups for both private and public universities. Calculate the results.
• Select the private and public universities that rank in top 100 (ranking together) with criterion “student selectivity”, then calculate the results.
• Compare and discuss any observed differences in the representation of ethnic groups in the previous 3 results.
P.4