182|CHATGPT – Avalanche Analysis

BYU Student Author: @Jacob_Dutton
Reviewers: @Dallin_Gardner, @Kyle_Nilsen
Estimated Time to Solve: 25 Minutes

We provide the solution to this challenge using:

  • ChatGPT 4.0

(This challenge cannot be completed with the standard Chat GPT 3.5)
Need a program? Click here.

Overview
You are new data analyst at the Utah avalanche center. You have been tasked with analyzing the 200 most recent avalanches in the state. You have been provided an excel file with this avalanche data, but it is not cleaned or ready to be analyzed. You have decided to use ChatGPT to help you clean the data, and then excel to create pivot tables. Your boss wants to know how many avalanches have happened in each region, as well as the average width of an avalanche in each region.

Instructions

  • Load the data into ChatGPT.
  • Instruct ChatGPT to extract the region from the “Region and Location Column”
  • Instruct ChatGPT to remove any row with missing, width, or depth values.
  • Instruct ChatGPT to split width and depth into two separate columns (make sure that only the number values end up in the column.
  • Instruct ChatGPT to output the results as an Excel file.
  • Create two pivot tables to display the desired metrics to display avalanches by region and average depth of avalanche for each region.

Data Files

Suggestions and Hints
  • It is often helpful to make sure that Chat GPT knows your final objective or desired output before you start giving it data.
  • If you are unable to upload files, make sure that you are using GPT 4.0, as GPT 3.0 will not work for this challenge.
  • Ask ChatGPT to provide you with a sample of the first five rows between each request. This will ensure that mistakes are caught quickly.

Solution