NYC Open Data Project – Analyzing Service Requests in Queens (Single City)

INSTRUCTIONS TO CANDIDATES

ANSWER ALL QUESTIONS

this is an excel project needs to be done according to the guidelines. and it will ask about my specific city, mine is queens village.

 

 

NYCServiceRequestsSingleCity6MonthsV1.docx Page 1 of 4

NYC Open Data Project – Analyzing Service Requests in Queens (Single City)

Objective: For this project you will analyze 311 data for a single city in Queens and identify interesting insights

about the data.

Due Date: See Blackboard Grading Rubric: See Below

Instructions:

I. Identify the data you will use for your independent project.

• For this project you will analyze service request data for 6 months for single city in Queens.

1. Select your data

a. Explore the NYC Open Data; select the 311 data set. Click View Data to see the rows of data.

b. Filter the data to locate service requests that meet the following criteria:

i. Time Frame: Service requests that were CREATED (CREATED DATE) last year from July to Dec.

ii. To look at data for this time period, use the following filters: (Note: 12:00AM is midnight)

• Set Filter for CREATED DATE “IS AFTER” 7/1/last year; for example 2020

• Click the ADD NEW FILTER CONDITION button

• Set Filter for CREATED DATE “IS BEFORE” 1/1/this year;

• This will display the service request data for July through December from last year.

iii. Borough: Set Filter for BOROUGH “IS” Queens.

iv. Target City: Set Filter for CITY “IS” one area (city) in Queens.

I. You CAN NOT select Bayside as your city.

II. Each student must select a different city. Check the NYC Open Data link in our

Blackboard site (under Learning Modules) to see which cities have already been

selected.

v. In your dataset, scroll to the right so that the City column is visible. Take a screen shot, like

the one above, of your filters and the number of service requests. Save the screen shot image

to include in your project later.

2. Export the filtered data into Excel using the CSV for Excel option.

3. Save the file as an Excel workbook (select this as the File Type).

4. Name the file with your full name and the City in the file name.

NYCServiceRequestsSingleCity6MonthsV1.docx Page 2 of 4

Analyze your data by answering the Standard Questions

5. Answer the Standard Questions using Excel

c. Use Excel to answer the following Basic questions.

d. Use a separate sheet tab to answer each question.

e. Include the full question at the top of the worksheet.

i. What do people in your target city in Queens contact 311 about in the given time period?

I. Response must include a bar chart

ii. When do people in your target city in Queens contact 311 in the given time period?

I. Response must include a line chart

II. Individual Question #1 → Dig Deeper: Analyze your data by answering your own question

6. Answer your Individual Question #1 using Excel

a. Think of an additional question that can be answered using any of the data you selected.

b. Use a separate sheet tab to answer your Individual Question #1.

c. Include the full question at the top of the worksheet.

d. Response must show how you used Excel to answer the question.

e. Including a chart is optional.

III. Individual Question #2 → Dig Deeper: Analyze your data by answering your own question

7. Answer your Individual Question #2 using Excel

a. Think of an additional question that can be answered using any of the data you selected.

b. Use a separate sheet tab to answer your Individual Question #2.

c. Include the full question at the top of the worksheet.

d. Response must show how you used Excel to answer the question.

e. Including a chart is optional.

IV. Enhance your worksheet using Excel features

8. Use Excel features to demonstrate your proficiency with Excel and to create a visually appealing

representation of your data analysis.

a. You may use ANY columns in the service request data set to demonstrate your proficiency with

using the following Excel features.

b. You may also add any data that you need to apply the features appropriately.

c. Use a sheet tab labeled “Excel Features” to indicate where each feature below is applied in your

workbook.

i. Set up the Excel Features sheet as follows:

# Feature Name Sheet Name

where Feature

is used

Cell that

contains

Feature

Comment Snip of Feature

1. Merge and Center Type sheet

name

Type cell

reference

Not applicable Not applicable

2. Cell Style Type sheet

name

Type cell

reference

Indicate the name of

the style you applied

and why you selected it

Include snip of the cell

with the cell style

applied

NYCServiceRequestsSingleCity6MonthsV1.docx Page 3 of 4

3. Absolute Reference Type sheet

name

Type cell

reference

Explain why an absolute

reference was needed

Include snip of the cell

with the absolute

reference

4. Formula Type sheet

name

Type cell

reference

Indicate what the

formula is calculating

Include snip of the cell

with the formula

5. IF Function Type sheet

name

Type cell

reference

Explain the purpose the

IF function is serving in

the worksheet

Include snip of the cell

with the IF function

6. Conditional

Formatting

Type sheet

name

Type cell

reference

Indicate the type of

Conditional Formatting

you applied and why

you selected it

Include a screen shot of

your Conditional

Formatting Manager

window

7. Pie Chart -OR-Column

Chart

Type sheet

name

Not

needed

Not applicable Not applicable

d. Excel Features that must be included in your workbook:

I. Worksheet Formatting – Visual appeal

1. Merge and center

2. Cell style

II. Calculations

3. Absolute cell referencing – used logically and in a meaningful way in a context related to

the data

4. Formula – an equation that you build that includes arithmetic operators; arithmetic

operators are:

• Addition → +

• Subtraction → -

• Multiplication → *

• Division → /

III. Conditional Processing

5. IF Function (Not COUNTIF) – used logically and in a meaningful way in a context related

to the data

6. Conditional Formatting; include a sentence that explains the data condition and the

specific formatting applied

IV. Data Analysis

7. Pie Chart or Column Chart; must include data labels

What to hand in:

Submit 1 Excel workbook with the following sheets:

1. A sheet named with your city name that includes the ORIGINAL FULL DATA set for your city.

2. A sheet named “What Data” and a sheet named “What Analysis” that include the data for your

“What” question.

3. A chart sheet named “What Chart” that includes the answer to your “What” question.

4. A sheet named “When Data” that includes the data for your “When” question.

NYCServiceRequestsSingleCity6MonthsV1.docx Page 4 of 4

5. A chart sheet named “When Chart” that includes the answer to your “When” question.

6. A sheet named “Ind Question 1” that includes the data for your “Individual Question #1” and the

answer. Make sure the QUESTION is clearly stated and the ANSWER is clearly identified.

7. A sheet named “Ind Question 2” that includes the data for your “Individual Question #2” and the

answer. Make sure the QUESTION is clearly stated and the ANSWER is clearly identified.

8. A sheet named “Filters” that includes a screen shot of the NYC Open Data filters that were used to

select your data set. This must include your date selection, borough selection, and city selection.

The number of service requests must also be included in the image.

9. Excel Features sheet filled in.

Additionally:

• Your workbook may have additional sheets if needed.

• All worksheets must be visually appealing and properly formatted.

• All charts must include 3 titles (one title for Pie charts) and an effective use of data labels.

Rubric:

Category Points

Excel Features 21 points

Worksheet Formatting 2

Calculations 7

Conditional Processing 7

Data Analysis 5

Required Content 29 points 0

What Question Data and Chart 5

When Question Data and Chart 5

Individual Question #1 and Answer 5

Individual Question #2 and Answer 5

Filter Criteria Image 3

Excel Features Sheet 3

Quality and Professionalism 3

Total 50

Attachments:

Expert's Answer

284 Times Downloaded

Related Questions

. Introgramming & Unix Fall 2018, CRN 44882, Oakland University Homework Assignment 6 - Using Arrays and Functions in C

DescriptionIn this final assignment, the students will demonstrate their ability to apply two ma

. The standard path finding involves finding the (shortest) path from an origin to a destination, typically on a map. This is an

Path finding involves finding a path from A to B. Typically we want the path to have certain properties,such as being the shortest or to avoid going t

. Develop a program to emulate a purchase transaction at a retail store. This program will have two classes, a LineItem class and a Transaction class. The LineItem class will represent an individual

Develop a program to emulate a purchase transaction at a retail store. Thisprogram will have two classes, a LineItem class and a Transaction class. Th

. SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of Sea Ports. Here are the classes and their instance variables we wish to define:

1 Project 1 Introduction - the SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of

. Project 2 Introduction - the SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of Sea Ports. Here are the classes and their instance variables we wish to define:

1 Project 2 Introduction - the SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of