代写INFO 5304、代写Python程序设计

日期：2023-03-18 10:08

CS/INFO 5304 Assignment 1: Data Preparation

Credit: 94 points

Grade: 20% of final grade

Submission: Files that need to be submitted for are listed at the end of each question.

Please submit the assignment on Gradescope

Tip: Read the document fully before you begin working on the assignment

Due date: March 28th, 11:59PM

Question 1: Extract, Transform Load (ETL) (34 points)

Often, we are given different datasets that represent the same population, but each dataset

contains different information about this population. ETL pipelines can be used to extract each

data source, transform the data sources so we can map one dataset onto the other, and then

load them into a single data source that we can use for analytics.

In this question, we will create an ETL pipeline to combine two raw datasets from a sampled

population, and estimate the value of a parameter that exists in one dataset, but not the other.

The datasets we are looking at come from the U.S. Center for Disease Control (CDC). The first

dataset is called the Behavioral Risk Factor Surveillance Survey, or BRFSS, and the second

dataset is called the National Health Interview Survey, or NHIS. Within the question you will be

asked to perform the following data analysis:

● Extract the raw datasets into a spark SQL DataFrame

● Read the guidebooks for each of these datasets to understand how to map similar

features to each other

● Perform an exact join on these features, and approximate a disease prevalence statistic

within the US population

For this question, you are required to use Spark and will heavily use the Spark SQL API. You

may use any other Python functions/objects when performing map/reduce on sections of the

DataFrame. We recommend reading this entire section before beginning coding, so you can

get an idea about the direction of the problem.

Step 1: Load data (3 points)

Download data from Google Drive(a smaller dataset than the real one). There should be two

data files, called brfss_input.json and nhis_input.csv. We have also provided starter code

CS/INFO 5304 Assignment 1

for you, in p1.py. Complete the function called create_dataframe that takes in a path to a

file, the type of file to upload (e.g. “csv”), a spark session, and returns the spark DataFrame for

that file.

Step 2: Make test example (6 points)

When working with large datasets, it is often helpful to create a small subset as test example

that can be used to validate whether our code works, within a short runtime.

Analyze the columns of the BRFSS and NHIS data you downloaded, and identify what columns

represent similar information (for example, is there a column in BRFSS, and a respective

column in NHIS, that represent a person’s age?). To do this, you will need the codebooks for

each dataset, which can be found here:

● 2017 BRFSS codebook

● 2017 Sample Adult File NHIS codebook (Here is a copy on Drive in case you have any issues.)

After analyzing the columns, prepare three files that can be used as a test case:

● Download the 5-row BRFSS “dummy” dataset and the 5-row NHIS “dummy” dataset

from Google Drive. They are named test_xxx.

● Manually create a joined_test.csv file that represents the expected output of your

program with the above dummy input. Only exact matches should be kept, and all null’s

should be dropped. This file should essentially have:

a. All the BRFSS columns

b. The column(s) of the NHIS dataset that are not initially within the BRFSS dataset

You could use multiple columns to join. Do read the codebook to know what does each

value represent.

To map the values of NHIS data onto the BRFSS data, for example, IMPRACE = 4 = American

Indian/Alaskan Native, non-Hispanic; MRACBPI2 = 3 = Indian (American) (includes Eskimo,

Aleut); Map them to same value before joining.

Another example, they both have a column for age. If I had a three row BRFSS dataset with one

column representing age, and a two row NHIS dataset with two columns representing age and

gender, the expected joined DataFrame would look like the following:

BRFSS Age

Column

+

NHIS Age

Column

NHIS

Gender

=

Joined BRFSS Age Created BRFSS Gender

18-24 63 1 18-24 2

85+ 20 2 60-64 1

60-64 - - - -

CS/INFO 5304 Assignment 1

Step 3: Map data (12 points)

Next, based on the practice in step 2, you will create a function that maps the NHIS data onto

the BRFSS data. In other words, if you have columns in each dataset that represent similar

information, but with potentially different column names and values, we want you to write a

function that transforms the matching columns within the NHIS DataFrame to resemble the

BRFSS DataFrame.

Please complete the function transform_nhis_data(nhis_df) that inputs the NHIS

DataFrame, and returns a DataFrame where matching NHIS/BRFSS columns have been

mapped to BRFSS formatting.

Step 4: Join data (3 points)

Now that you have transformed columns in NHIS, we can actually join the two dataframes

together into one entire dataset. Please join together your two Spark DataFrames in function ,

such that only exact matches remain. (Hint: The end number of columns should be the |# of

BRFSS columns| +1). Please also drop any rows containing null values (this is not

necessarily correct to do in practice, but since we’re focused on ETL, we’ll save missing data

issues to the next problem).

To clarify, you would have mapped a new column onto your BRFSS dataset. This column

represents the prevalence of a specific disease throughout the U.S. (look at the codebook to

find out what disease!).

You can test and compare with your small data created in Step 2.

(Hint: there are 3 columns in both NHIS and BRFSS representing the same demographic

features. Join using all of them and assume each combination is unique while joining, i.e. there

is no duplication. Is the no duplication assumption correct? Do not need to write answer for

this, but think about this may be helpful for answering part 5.)

Step 5: Report prevalence (10 points)

Based on the joined data, report the prevalence of this disease by:

● Race and ethnic background

CS/INFO 5304 Assignment 1

● Gender

● BRFSS categorical age

Complete function report_summary_stats(joined_df) that finds these summary

statistics. The function should input your joined DataFrame, and does not need to have a return

value (you could just print the stats and record them):

In your write-up:

- record the found prevalence you calculated for each category

- research what the actual prevalence is

- write a short paragraph comparing your found prevalence within the joined dataset to

the actual prevalence, by gender, race/ethnic background, and age

- assess how you might improve the prevalence you calculated.

- Note: Does each row in the BRFSS dataset correspond to a single person in the

U.S.? Check-out the BRFSS documentation.

Turning in:

For this question, please turn in the following:

● Your code, in a file called p1.py

● The joined_test.csv of your created test case in “csv” format

● Your p1_write_up.pdf including the answer to step 5 as well as any documentation to

run your code.

● Please only submit the above files and do not zip your files

Note: You are not submitting the joined DataFrame for the actual BRFSS/NHIS data, just

reporting the prevalence statistics.

We should be able to run your p1.py file, from the command line using the command:

spark-submit p1.py /path/to/brfss.json /path/to/nhis.csv -o path_to_output

The arguments are as follows:

● /path/to/brfss.json: The path to an inputted BRFSS file

● /path/to/nhis.csv: The path to an inputted NHIS file

● path_to_output: Save the resulting joined DataFrame.

Please document any specific information we need to run your code/understand your outputs in

your written report. If you use a different coding language, document how to run your code.

CS/INFO 5304 Assignment 1

Question 2: Dealing with messy and missing data (40 points)

In this question, we will perform data cleaning and data filling with human-generated sensor

data. The intention is to let you practice exploratory data analysis using real-world data, making

sense of the data while cleaning it. The form of the submission is a report containing your

results and your reasoning.

We will be using a modified version of the Extrasensory dataset from UCSD. Here is the link to

it. In short, this dataset is from a study to perform context recognition with smartphone and

smartwatch sensors. Filling in data often requires domain expertise, and we choose this dataset

because we are certain all of you are domain experts at using a smart phone. :)

In this Question, you can use Pandas or Pandas API on Spark to load the data to Pandas

Dataframe or Spark dataframe respectively. All the functions in the pandas library are allowed.

Numpy, Scipy.stats, .etc are also allowed.

A JupyterNotebook template to answer this question is also in the data folder.

Understanding data structure

You are provided a dict of the modified dataset. There are 60 participants in this dataset. We

give you two pickle files: Extrasensory_individual_data.p and

Extrasensory_sensor_data.p.Please refer here on how to open pickle files.

Extrasensory_individual_data.p contains a DataFrame with the participant ID and the

following data:

Sensor Feature

demographic age

gender

phone_system

Phone usage self_reported_average_screen_time

actual_average_screen_time

hours_of_study

Extrasensory_sensor_data contains a dict with participant ID as the key of the provided

dict. The value contains a matrix of ~ 2000 - 6000 recorded samples of 10 features. The

number of records corresponds to the number of minutes that the participant underwent the

study. It contains the data from the following sensors:

CS/INFO 5304 Assignment 1

Sensor Feature

accelerometer raw_acc:3d:mean_x

raw_acc:3d:mean_y

raw_acc:3d:mean_z

location location:raw_latitude

location:raw_longitude

phone state discrete:app_state:is_active

discrete:app_state:is_inactive

discrete:app_state:is_background

discrete:app_state:missing

lf_measurements:battery_level

As with all studies that are deployed to humans in the wild, there's a lot of missing data. In this

dataset, some of the missing values are recorded as NaN or are left blank. Some other

missing values are recorded as -1 for non-negative values.

Please use one jupyterNotebook(template giving) to write your answers to all the

questions of Q2. Please include all the figures and code in the notebook.

Case 1: Actual screen time (15 points)

This study recorded the average screen time every 24 hours for each participant. Some data

are missing due to app permissions. Because this is an important reference for analysis later,

we need to fill in those missing values.

A) Observe this data or plot a simple plot. How are missing values represented for this

feature?

B) Ignore the missing values for now (i.e. mute them or temporarily remove them, so that

they don’t affect this analysis). Provide a histogram to illustrate this distribution of

actual_average_screen_time.

a) Does it have outliers?If so, how many?

An outlier is defined as being any point of data that lies over 1.5 IQRs below the first

quartile (Q1) or above the third quartile (Q3)in a data set.

High = (Q3) + 1.5 IQR

Low = (Q1) – 1.5 IQR

b) Is it skewed? If so, is it left skewed or right skewed? What’s the skewness?

C) Fix the missing data problem using these methods: 1) filling with median, 2) filling with mean,

3) filling with a random value within range of your choice. Reproduce the distribution figure

for this feature filled by all three methods. Overlay a figure with the original distribution and

the 3 new distributions. How did you choose the random value from method 3)? What do the

distributions look like after you implement the three filling methods? (Compare them)

CS/INFO 5304 Assignment 1

D) We could compare your filled distribution with the real distribution. Some Research shows

that American adults have an average screen time of 3.85 hours per day, with a standard

deviation of 1.25 hours. You could generate this distribution by:

np.random.normal(mean, std, number of samples)

Perform a t-test between the three distributions you filled in part C and this population

distribution. Report the three p-values. Which one of the filling methods reconstruct this

feature to be closest to the research distribution? Why do you think this is the case?

Case 2: Perceived average screen time (10 points)

In this study, the participants were asked to self-report how many hours they spend on their

phone. Participants could choose not to report if they do not know the number of hours, or if

they did not want to.

Let’s find out whether these missing values are MAR(missing at random) or MNAR(missing not

at random). Said another way, let’s see if participants did not report these values because they

do not know their screen time, or they did not report because they are self-conscious that they

might have spent too much time.

Let’s see if there’s a correlation between the actual_average_screen_time and

perceived_average_screen_time.

recap:

actual_average_screen_time is the screen time recorded by phone and can be missing due

to permission or technical reason

perceived_average_screen_time is the screen time recorded by the user itself and can be

missing or wrongly recorded for various reasons.b

A) Temporarily ignore the missing values. Plot a histogram of the perceived screen time.

a) Does it have outliers?If so, how many?

An outlier is defined as being any point of data that lies over 1.5 IQRs below the first

quartile (Q1) or above the third quartile (Q3)in a data set.

High = (Q3) + 1.5 IQR

Low = (Q1) – 1.5 IQR

b) Is it skewed? If so, is it left skewed or right skewed? What’s the skewness?

B) Let’s define an intense phone user as someone whose average screen time is at least

one standard deviation larger than the mean screen usage time. How many of them are

intense phone users? (Note: Do not remove outliers) (Note: think of which data should

be using actual_average_screen_time or perceived_average_screen_time)

C) Create two binomial distributions of A) missing perceived_average_screen_time

and B) intense phone users. In another word, generate a boolean array for A) and B).

Perform a Chi-square test on these two distributions. What is the p-value? Do you think they

are correlated? What does this mean? Do you think this feature is MAR or MNAR? (Note and

CS/INFO 5304 Assignment 1

hint: If the user’s actual screen time is missing, you should not count that user as either

intensive or non-intensive, you should filter out those users)

Case 3: Location (15 points)

Now let’s look at the stream for location data. Each participant has two sets of location data,

location:raw_latitude and location:raw_longitude. They are very similar, so for

ease sake we will only look at location:raw_latitude.

There are a two thoughts on why location data might be missing: 1) app recording

errors/technical errors, which are random or 2) some people turn off location services to save

battery when the battery level is low. We assume 2) is a consistent behavior. Let’s say people

configure their phone to turn off location service if the battery is below 20%.

A) (8 points) Identify these people who behave as 2) and report their uuid. Their

characteristics should be that their location data are consistently lost when their battery

level is below 20%. We need to find these people because if this was a real-world

analysis, their location traces give noise to downstream classification.

Explain how you find these people in the Notebook. For each of these individuals, how

many minutes of location data have we lost due to turning-off of location service?

Note: This is a half open ended question. There’s no standard solution for this, your

solution can be loose but you need to explain your solution.

Now, we will only proceed with people with uninterrupted location traces. Find subject

F50235E0-DD67-4F2A-B00B-1F31ADA998B9. We’ll start to do some filling for time series

here.

We introduce the following naive methods:

● Forward filling: Assume the missing value occurs at tn, fill in the value using tn-1.

● Backward filling: Assume the missing value occurs at tn, fill in the value using tn+1.

● Linear interpolation: For each chunk of missing values, perform linear interpolation with

the value of one sample before the missing chunk, and one value after the missing

chunk. (If you want to implement more sophisticated linear interpolation, note it in your

write-up).

B) (7 points) Perform a forward filling, backward filling and linear interpolation on the

location:raw_latitude trace. Produce a figure with 4 traces overlay on each other

(4 traces including the original, unfilled trace and trace produced by your three filling

methods). Compare the 4 traces. What do you see? If you were to use this dataset for

further analysis, which filling method will you choose?

CS/INFO 5304 Assignment 1

Turning it in:

● Your write-up, including answers to the above questions and figures to help

support your answers. One Jupyter Notebook file(q2.ipynb) that includes

write-up and code and figures is required.

Question 3: Outlier Detection (10 points)

Your task is to analyze the data and detect the outliers in the dataset. For this activity, let us use

the following dataset given below. You do not need to use Spark for this question. You can

use Numpy, Pandas, etc. Please wrap up your code and output graphs in one Jupyter

Notebook.

Input Dataset:

● Top 270 Computer Science / Programming Books. High rated book information in the

field of computer science and programing

● Download link

Task 1: Univariate Outlier detection (4 points)

Box plots are efficient in identifying the outliers in numerical values. For this dataset, use the

IQR method to identify any univariate outliers in this dataset. Plot the outliers in the context of the

feature's distribution for all the four numerical features (Rating, reviews, number of pages,

price). Your output should be the box plots of each feature.

Task 2: Multivariate Outlier detection (6 points)

Lets use the DBSCAN Method for multivariate outlier detection. For this exercise, use the

numerical and categorical columns to fit DBSCAN.

Your first task is to perform a bivariate analysis on all possible pairs of the above features and

identify any outliers. The output should be all the DBSCAN plots and the outlier data

row(index&value) for each combination.

Your second task is to look for all combinations of three variables in the above dataset to identify

multivariate outliers. The output should again be all the dbscan plots(3D) and the outlier data

row for all combinations.

CS/INFO 5304 Assignment 1

Use the following hints for answering this question:

● For categorical variables(“Types”), you can convert them into numerical features for the

analysis.

● Try scaling the data first.

● Tune the eps and min_samples.

Turning in

1) A Jupyter Notebook(q3.ipynb) with all code, output and graphs.

2) Please export your q3.ipynb to a q3.py and upload this q3.py as well. We will need to run

a plagiarism check on the code. (do not zip these two files)

Question 4: Data Visualization (10 points)

Your task is to design a visualization that you believe effectively communicates the data and

provide a short write-up describing your design. You can use any data visualization tool you

want.

Start with the dataset weather.csv which contains weather measurements nearest to Cornell

Tech every day from 1950 to the present.

Notice that the temperature (Ktemp) is in Kelvin rather than in Fahrenheit. Define a variable that

expresses the temperature in Fahrenheit, using the following formula:

Ftemp = (Ktemp ? 273.15) * (9/5) + 32

Part A) For every month of the year, plot the average temperature (in Fahrenheit) using a

scatter plot or line plot. Your visual should be configurable so that a user can easily look at a

specific year’s weather curve (use a sliding scale filter). (6 points)

Part B) Based on all of the historical data, when is the first day where the historical average

temperature passes 60 degrees (when will Cornell Tech finally be warm again?) (2 points)

Part C) Create a new sheet where you do something creative through data visualization.

Express something about the temperature over time(e.g. Look in the cycle of temperature over

seasons, etc ) using this dataset, or add a new dataset(any available dataset online is fine) and

find some correlation with the temperature(e.g. Number of some kind of fish in the ocean, etc,

does the number of it go up and down following the temperature trend? Etc.). (2 points)

You will be graded based on the quality of completion. A simple plot of temperature in Celsius

will not get you full points.

CS/INFO 5304 Assignment 1

Note: Your visualization should be interpretable and accompanied by a short write-up (you do

not need to write more than a sentence or two). Do not forget to include title, axis labels, or

legends as needed!

Turning it in:

1. Export everything to one pdf file q4.pdf and submit it in gradescope. If there’s any

dynamic visualization, please include the URL to the visualization in the pdf.

(consider graders as newspaper readers, do not expect graders to have all the

tools installed. For example, if using tableau, do not submit the .twb file, but push

the visualization to tableau public and include an url)