BYU Student Author: @Jacob_Dutton
Reviewers: @Dallin_Gardner, @Kyle_Nilsen
Estimated Time to Solve: 25 Minutes
We provide the solution to this challenge using:
(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
Cleaned_Challenge182_Data.xlsx (21.0 KB)
This was a fun challenge that helped me improve my AI prompting skills. I enjoyed it!
30 minutes to complete. Easy level of difficulty. I really enjoyed this challenged, it introduced a whole new aspect of AI to me.
In the sheet attached below, you can see the chatgpt produced clean data on the left hand side, and I was then able to create two pivot tables on the right hand side of the sheet to easily address the prompted questions.
ChaptGPTCleaned_Challenge182_Data.xlsx (19.5 KB)
Time to complete: 20 minutes
Level of difficulty: Easy
It was very cool seeing how fast Chat GPT was able to clean the data, which took less than a minute once I provided it with prompts of what steps to take and shows how quickly technology can produce results that would take much more time when completed manually. In the sheet attached, you can see the clean data, as well as my two pivot tables showing the number of avalanches per region and the average depth of avalanches per region, created utilizing the cleaned data.
Processed_Challenge182_Data - Cleaned Version.xlsx (20.4 KB)
Took about 10 minutes to complete. Easy challenge, but great to display the speed of a growing tool!
Processed_Challenge182_Data- Cleaned.xlsx (17.8 KB)
time to complete: 30 mins
Level of difficulty: Easy
This helped with my ai prompting skills
Challenge182_MySolution.xlsx (23.9 KB)
Avalanche_Data_Summary.xlsx
Final_Cleaned_Challenge182_Data.xlsx (10.7 KB)
This took me around 25 minutes. It is really interesting to see how fast Chat GPT has improved over the course of a year. Super useful challenge.
It took me about 25 minute to finish. It was so interesting how fast the Chat GPT creates the output
Challenge182_Data_Cleaned.xlsx (14.4 KB)
Challenge 182 Final Copy.xlsx (20.9 KB)
Fun challenge, very interesting using AI to solve the question!
The assignment took me around 30 minutes to complete and really aided in my AI Prompting, and excel formatting skills. Overall, really enjoyed this assignment and hope others had a positive experience.
Avalanche .xlsx (16.1 KB)
Processed_Challenge182_Data .xlsx (14.4 KB)
This took me about 20 minutes and was intermediate. Good stuff!
This was a fun challenge and took roughly 20 minutes to complete. I would say the difficulty is intermediate level.
Challenge182_Finished.xlsx (24.3 KB)
This was a fun challenge, I liked it!
Challenge182_Data_MeganLouie.xlsx (33.5 KB)
Time to complete: 25 minutes
Rating: 7/10 in difficulty
Avalanches_by_Region_Pivot.xlsx (5.4 KB)
Average_Depth_by_Region_Pivot (1).xlsx (5.4 KB)