Homework 4

Data Transfomration with pandas I

Author

Byeong-Hak Choe

Published

April 10, 2026

Modified

April 19, 2026

📌 Directions

  • Submit your Jupyter Notebook (*.ipynb) to Brightspace using this format:
    • danl-210-hw4-LASTNAME-FIRSTNAME.ipynb
      (e.g., danl-210-hw4-choe-byeonghak.ipynb)
  • Due: April 20, 2026, 10:30 A.M.
  • Questions? Email Prof. Choe:



Part 1. NYC Payroll Data



Below is nyc_payroll DataFrame that reads the file nyc_payroll.csv containing data of how the New York City’s budget is being spent on salary and overtime pay for all municipal employees (Source: NYC OpenData).

nyc_payroll = pd.read_csv('https://bcdanl.github.io/data/nyc_payroll_2025.zip')

Variable Description

  • Fiscal_Year: Fiscal Year;
  • Payroll_Number: Payroll Number;
  • Agency_Name: The Payroll agency that the employee works for;
  • Last_Name: Last name of employee;
  • First_Name: First name of employee;
  • Mid_Init: Middle initial of employee;
  • Agency_Start_Date: Date which employee began working for their current agency;
  • Work_Location_Borough: Borough of employee’s primary work location;
  • Title_Description: Civil service title description of the employee;
  • Leave_Status_as_of_June_30: Status of employee as of the close of the relevant fiscal year;
  • Base_Salary: Base Salary assigned to the employee;
  • Pay_Basis: Lists whether the employee is paid on an hourly, per diem or annual basis;
  • Regular_Hours: Number of regular hours employee worked in the fiscal year;
  • Regular_Gross_Paid: The amount paid to the employee for base salary during the fiscal year;
  • OT_Hours: Overtime Hours worked by employee in the fiscal year;
  • Total_OT_Paid: Total overtime pay paid to the employee in the fiscal year;
  • Total_Other_Pay: Includes any compensation in addition to gross salary and overtime pay, ie Differentials, lump sums, uniform allowance, meal allowance, retroactive pay increases, settlement amounts, and bonus pay, if applicable.

For more details in the variable description, see: Citywide Payroll Data (Fiscal Year) on NYC Open Data.



Question 1

Select “First_Name”, “Last_Name”, “Base_Salary”, and “Total_OT_Paid”, then sort the DataFrame with these selected variables by “Base_Salary” in descending order and display the top 10 entries.

Answer



Question 2

Using set_index(), change the DataFrame’s index to “Last_Name”, then locate the data for a specific last name, say “BROWN”, and display their “Agency_Name”, “Base_Salary”, and “Total_OT_Paid”.

Answer



Question 3

Find the 5 employees with the highest “Regular_Gross_Paid” and calculate their average “OT_Hours”. Also, reset the index if you have changed it previously.

Answer



Question 4

Sort the DataFrame by “Fiscal_Year” and “Total_Other_Pay” in descending order, then set “First_Name” as the index and use the loc accessor to retrieve the “Total_Other_Pay” for a specific first name, say “MICHAEL”.

Answer



Question 5

Sort the DataFrame first by “Work_Location_Borough” alphabetically, and then by “Total_Compensation” (sum of “Base_Salary” and “Total_OT_Paid”) in descending order within each borough.

Answer



Question 6

  • Select employees who have “OT_Hours” greater than 0, calculate their “OT_Rate” (“Total_OT_Paid” / “OT_Hours”), and then find the employee with the highest “OT_Rate”. Display their full name and “OT_Rate”.

Answer



Question 7

Create a new DataFrame that includes employees from the “DEPARTMENT OF EDUCATION ADMIN” agency where the variables are “First_Name”, “Last_Name”, “Title_Description”, “Base_Salary”, and “Total_OT_Paid”. Additionally, include a new variable “Total_Compensation” which is the sum of “Base_Salary” and “Total_OT_Paid”.

Answer



Question 8

  • How many employees have a “Base_Salary” within the top 10% of the DataFrame?

Answer



Question 9

Filter the DataFrame for employees who have “OT_Hours” greater than 0 but less than 100, and their “Leave_Status_as_of_June_30” is “ACTIVE”.

Answer



Question 10

Find the unique job titles in the “DEPARTMENT OF EDUCATION ADMIN” agency and count how many there are.

Answer



Question 11

  • Identify the employee(s) with the highest “Total_OT_Paid” in the DataFrame.
    • Include their “First_Name”, “Last_Name”, and “Total_OT_Paid”.

Answer



Question 12

  • What percentage of the values is missing for each variable?

Answer



Question 13

  • Fill missing values in the “Last_Name” variable with “UNKNOWN”.

Answer




Part 2. Spotify Data



Below is spotify DataFrame that reads the file spotify_all.csv containing data of Spotify users’ playlist information (Source: Spotify Million Playlist Dataset Challenge).

spotify = pd.read_csv('https://bcdanl.github.io/data/spotify_all.csv')


Variable Description

  • pid: playlist ID; unique ID for playlist
  • playlist_name: a name of playlist
  • pos: a position of the track within a playlist (starting from 0)
  • artist_name: name of the track’s primary artist
  • track_name: name of the track
  • duration_ms: duration of the track in milliseconds
  • album_name: name of the track’s album

Definition of a Song

  • In Part 2, a song is defined as a combination of a artist_name value and a track_name value.

  • E.g., the following provides the 12 distinct songs with the same track_name—I Love You:


Question 14

  • Write a Python code to identify the top five songs with the highest frequency of appearances in the spotify DataFrame.

Answer



Question 15

  • Write a Python code to create a DataFrame that contains information about how often each song occurs within the spotify DataFrame.
    • In this new DataFrame, each observation should represent a distinct song.
  • Then, write a Python code to identify top 5% songs based on their frequency of appearances.

Answer



Question 16

  • Write a Python code to list all artists who have more than 50 unique songs in the spotify DataFrame.

Answer



Question 17

  • Write a Python code to create a DataFrame that identifies all the playlists featuring the song “One Dance” by Drake.

Answer



Question 18

  • Write a Python code to identify the longest and shortest duration songs (based on duration_ms) for each unique artist.

Answer



Question 19

Write a Python code to find the same song(s) appearing more than once in the same playlist.

Answer



Question 20

  • Write a Python code to filter all songs that appear in more than 100 different playlists.

Answer



✅ End of Homework 4

Back to top