Ian Stevens is a human resource analyst working for the city of Seattle. He is performing a compensation analysis of city employees. The accompanying data set contains three variables: Department, Job Title, and Hourly Rate (in $). A few hourly rates are missing in the data.

Department	Job Title	Hourly Rate
Public Utilities	Res&Eval Asst	32.98
Sustainability & Environ Dept	StratAdvsr3,Exempt	62.05
Public Utilities	Capital Prjts Coord, Asst	42.95

a. Split the data set into a number of subsets based on Department. How many subsets created.................

b. Which subset contains missing values? How many missing values are in that data set?

The subset that contains missing values......................

The number of missing values................

c. Use the imputation strategy to replace the missing values with the median of the variable. What is the average hourly rate for each subset?(Round your answer to two decimal places.)

Average hourly rate for the subset with missing values..................

Here is excel data file for this problem:

Department	Job Title	Hourly Rate
Public Utilities	Res&Eval Asst	32.98
Sustainability & Environ Dept	StratAdvsr3,Exempt	62.05
IT Dept	Info Technol Prof A *	56.34
Public Utilities	Wtr Systs Supv	53.3
Public Utilities	Laborer *	22.13
IT Dept	Manager2,Info Technol	59.92
IT Dept	Mgmt Systs Anlyst,Sr	48.05
IT Dept	Info Technol Prof B-BU *	53.7
Public Utilities	Info Technol Prof C-BU	46.59
Public Utilities	Wtr Syst Op	42.63
Public Utilities	Drainage&Wstwtr Coll CC	41.02
Public Utilities	Fin Anlyst	38.88
IT Dept	Info Technol Prof A,Exempt	64.62
Public Utilities	Fin Anlyst,Sr	46.09
IT Dept	Info Technol Prof B-BU	58.31
Public Utilities	Executive2	73.83
Public Utilities	Manager3,Engrng&Plans Rev	66.43
Public Utilities	Wtr Meter Repairer	31.16
Public Utilities	Grounds Maint CC	37.49
Public Utilities	Truck Drvr,Heavy	32.68
Public Utilities	Util Act Rep Trne	24.09
Public Utilities	Wtr Pipe Wkr Sr-WDM II	37.52
IT Dept	Cmputr Op,Sr *	24.61
IT Dept	Info Technol Prof A,Exempt	56.76
IT Dept	Info Technol Prof A,Exempt	57.49
IT Dept	Info Technol Prof B-BU	52.27
Public Utilities	Util Act Rep I	26.24
Public Utilities	Manager3,Utils	59.78
Public Utilities	Envrnmtl Anlyst,Sr	48.02
Public Utilities	StratAdvsr2,Engrng&Plans Rev	58.96
Public Utilities	Util Act Rep Trne	23.18
Public Utilities	StratAdvsr1,Exempt	52.52
IT Dept	Info Technol Prof B-BU	49.85
Public Utilities	Util Act Rep Trne	23.67
IT Dept	Info Technol Prof B-BU	52.15
Public Utilities	Util Act Rep I	29.41
Public Utilities	Civil Engrng Spec,Assoc	38.46
Dept Of HR	StratAdvsr1,Exempt	48.83
IT Dept	Info Technol Prof B-BU	56.78
IT Dept	Info Technol Prof B-BU	56.91
Public Utilities	Truck Drvr,Heavy	31.37
Public Utilities	StratAdvsr1,Utils	42.88
Public Utilities	Wtr Pipe Wkr Sr-WDM II	37.82
Public Utilities	Real Property Agent	36.84
IT Dept	Info Technol Systs Anlyst *	38.07
Public Utilities	Civil Engr,Sr	58.58
Public Utilities	Civil Engrng Spec,Assoc	39.93
Public Utilities	Wtr Quality Anlyst	31.87
IT Dept	Info Technol Prof C-BU *	46.08
Public Utilities	Constr&Maint Equip Op,Sr	35.81
Public Utilities	Constr&Maint Equip Op	34.33
Public Utilities	Civil Engr,Asst I	34.24
Public Utilities	Civil Engr,Sr	58.55
Public Utilities	Cust Svc Rep *	25.6
Public Utilities	Manager1,Fin,Bud,&Actg	55.54
Public Utilities	Civil Engrng Spec,Sr	49.86
Sustainability & Environ Dept	StratAdvsr2,Exempt	50.88
Public Utilities	Wtr Pipe Wkr,Sr	36.4
Public Utilities	Civil Engrng Spec,Sr	50.32
IT Dept	Info Technol Prof B-BU	55.7
Public Utilities	Executive2	74.05
Public Utilities	Constr&Maint Equip Op,Sr	35.49
Public Utilities	Drainage&Wstwtr Coll Wkr *	29.23
Public Utilities	Civil Engr,Asst III	39.65
Public Utilities	Warehouser,Sr-BU	29.42
Public Utilities	Executive2	75.87
IT Dept	Info Technol Prof B-BU	52.9
IT Dept	Info Technol Prof A *	55.65
Public Utilities	Meter Reader,Sr	31.46
IT Dept	Info Technol Prof B-BU	56.98
Public Utilities	Envrnmtl Fld Spec	35.57
IT Dept	Info Technol Prof C-BU	44.46
Public Utilities	StratAdvsr2,Utils	53.86
Public Utilities	Admin Staff Anlyst	40.56
Public Utilities	Civil Engrng Spec,Asst II	37.32
Dept Of HR	Manager1 *	52.79
IT Dept	Executive2	71.66
IT Dept	Info Technol Prof A,Exempt	67.73
Public Utilities	Actg Tech III-BU	30.07
Public Utilities	Actg Tech II-BU *	24.14
Public Utilities	Civil Engrng Spec,Sr	50.35
Public Utilities	Util Act Rep II	32.85
Public Utilities	Elecl Engr,Sr	58.39
Public Utilities	Civil Engr,Assoc	47.34
Public Utilities	Laborer *	23.59
Public Utilities	Civil Engrng Spec,Assoc	44.27
Public Utilities	Accountant,Prin	45.81
Public Utilities	Wtr Treatment Equip Tech	35.53
Public Utilities	Constr&Maint Equip Op	33.62
IT Dept	Info Technol Prof A,Exempt	64.43
IT Dept	Info Technol Prof C-BU *	35.11
IT Dept	Info Technol Prof C-BU	42.7
Dept Of HR	StratAdvsr2 *	55.27
Dept Of HR	Muni Government Intern *	20.61
Public Utilities	Util Maint Spec-SPU	35.61
Public Utilities	Civil Engr,Sr	56.35
Public Utilities	Drainage&Wstwtr Lead Wkr CII	35.05
Public Utilities	Manager3,Utils	65.17
Public Utilities	Wtr Pipe Wkr Sr-WDM II	37.24
Public Utilities	Truck Drvr,Heavy	32.03
Public Utilities	Civil Engr,Sr	54.24
IT Dept	Manager2,Info Technol	61.92
Public Utilities	Wtr Quality Anlyst	37.33
IT Dept	StratAdvsr2,Info Technol	56.48
Dept Of HR	StratAdvsr1,Exempt	49.12
Public Utilities	Manager3,General Govt	63.54
Public Utilities	Solid Wst Fld Rep,Lead	40.96
Dept Of HR	Personnel Anlyst	39.31
IT Dept	Info Technol Prof B-BU *	40.15
Public Utilities	Publc Ed Prgm Spec	34.87
Public Utilities	Truck Drvr,Heavy *	31.02
Public Utilities	StratAdvsr3,General Govt	63.16
IT Dept	Info Technol Prof B-BU	51.82
IT Dept	Info Technol Prof B-BU	56.27
Public Utilities	Wtr Laboratory Tech	26.98
Dept Of HR	Accountant,Sr	40.74
IT Dept	Info Technol Prof B-BU	51.22
Public Utilities	StratAdvsr2,Utils	58.51
IT Dept	Manager3,Info Technol	64.91
IT Dept	Info Technol Prof B-BU	59.53
IT Dept	Info Technol Prof B-BU	52.38
IT Dept	Cooperative Intern *	16.48
Public Utilities	Wtr Pipe Wkr	33.97