CSC-40054 assessment brief

Faculty of Natural Sciences

Related Post

CSC-40039_Assessment_2024-25

May 10, 2025

CS5071NPProfessionalandEthicalIssuesSpring25MainSitCWQP_08f31e25-c3ca-45ec-8c05-46e300dc52fe_89407_

April 10, 2025

TNE_MGT101 Assessment 2024_5_NEW

April 9, 2025

BM3506 AFS assignment 24_25

April 9, 2025

School of Computer Science and Mathematics

Assessment Brief

1. What is the task for this assessment?

One report should be submitted that covers the following two tasks:

• Part I: Database management

• Part II: Data analytics

2. What is required of me in this assessment?

3. What is the purpose of this assessment?

The following table shows which of the module learning outcomes are being assessed in this assignment. Use this table to help you see where and how to transfer feedback from one assignment to another. Note that your feedback may mention some of these outcomes, but that you will not receive a ‘mark’ against each one.

4. What resources might I use to get started?

Guidelines for Tasks

For the coursework assignment, you will be working on a dataset from a car-sharing company. The dataset contains information about the customers’ demand rate between January 2017 and August 2018. The data were collected on an hourly basis and included the time data such as date, hour, and season as well as weather data such as the weather condition, temperature, humidity, and wind speed. The ‘demand’ column represents the customer’s willingness for renting a car for a specific time. Higher demand rates show that customers are more willing to rent a car and vice versa. A complete description of the data is also shown in Table I.

IMPORTANT NOTICE: You should complete the Part I (database management) tasks using only the sqlite3 python module and SQL statements. You shouldn’t use any other python modules for these tasks. However, you can use any modules for Part II (data analytics) tasks or for importing and exporting data.

Download the dataset “CarSharing” from the KLE and complete the tasks.

Part I: Database Management (50%)

1. Create an SQLite database and import the data into a table named “CarSharing”. Create a backup table and copy the whole table into it. [5%]

2. Add a column to the CarSharing table named “temp_category”. This column should contain three string values. If the “feels-like” temperature is less than 10 then the corresponding value in the temp_category column should be “Cold”, if the feels-like temperature is between 10 and 25, the value should be “Mild”, and if the feels-like

temperature is greater than 25, then the value should be “Hot”. [5%]

3. Create another table named “temperature” by selecting the temp, temp_feel, and temp_category columns. Then drop the temp and temp_feel columns from the CarSharing table. [5%]

4. Find the distinct values of the weather column and assign a number to each value. Add another column named “weather_code” to the table containing each row’s assigned weather code. [5%]

5. Create a table called “weather” and copy the columns “weather” and “weather_code” to this table. Then drop the weather column from the CarSharing table. [5%]

6. Create a table called time with four columns containing each row’s timestamp, hour, weekday name, and month name (Hint: you can use the surftime() function for this

purpose).

[5%]

7. Assume it’s the first day you have started working at this company and your boss Linda sends you an email as follows: “

Hello, welcome to the team. I hope you enjoy working at this company. Could you please give me a report containing the following information:

a) Please tell me which date and time we had the highest demand rate in 2017. [5%]

b) Give me a table containing the name of the weekday, month, and season in which we had the highest and lowest average demand rates throughout 2017. Please include the calculated average demand values as well. [5%]

c) For the weekday selected in (b), please give me a table showing the average demand rate we had at different hours of that weekday throughout 2017. Please sort the results in descending order based on the average demand rates.

[5%]

d) Please tell me what the weather was like in 2017. Was it mostly cold, mild, or hot? Which weather condition (shown in the weather column) was the most prevalent in 2017? What was the average, highest, and lowest wind speed and humidity for each month in 2017? Please organise this information in two tables for the wind speed and humidity. Please also give me a table showing the average demand rate for each cold, mild, and hot weather in 2017 sorted in descending order based on their average demand rates. [5%]

e) Give me another table showing the information requested in (d) for the month we had the highest average demand rate in 2017 so that I can compare it with other months. [5%]

NOTICE: Full marks for task 7 will be given to solutions that use the CarSharing table after all changes in tasks 1-6 have been made to it.

Part II: Data Analytics (50%)

1. Import the CarSharing table into a CSV file and preprocess it with python. You need to drop duplicate rows and deal with null values using appropriate methods. [5%]

2. Using appropriate hypothesis testing, determine if there is a significant relationship between each column (except the timestamp column) and the demand rate. Report the tests’ results. [5%]

3. Please describe if you see any seasonal or cyclic pattern in the temp, humidity, windspeed, or demand data in 2017. Describe your answers. [5%]

4. Use an ARIMA model to predict the weekly average demand rate. Consider 30 percent of data for testing. [7.5%]

5. Use a random forest regressor and a deep neural network to predict the demand rate and report the minimum square error for each model. Which one is working better? Why? Please describe the reason. [10%]

6. Categorize the demand rate into the following two groups: demand rates greater than the average demand rate and demand rates less than the average demand rate. Use labels 1 and 2 for the first and the second groups, respectively. Now, use three different classifiers to predict the demand rates’ labels and report the accuracy of all models. Use 30 percent of data for testing. [10%]

7. Assume k is the number of clusters. Set k=2, 3, 4, and 12 and use 2 methods to cluster the temp data in 2017. Which k gives the most uniform clusters? (Clusters are called uniform when the number of samples falling into each cluster is close.) [7.5%]

NOTICE: You should use the original CarSharing dataset for Part II, not the dataset you have modified in Part II.

Guidelines for Preparing the Appendix

The following example shows a well-formatted appendix.

As you can see, PART I and PART II are clearly separated, and the code can be copied and pasted. The following steps show how you can copy your code and paste it into your report in the same way as the above example:

1. Select the code in your IDE using the mouse.

2. Use CTRL+C to copy the code.

3. Use CTRL+V to paste the code into your report.

Assessment Criteria

The criteria are adapted from the University assessment criteria and relate to all parts of this assessment.