BYU Student Author: @Kylie_Larsen17
Reviewers: @Saul_Esplin, @Kiya_Smith
Estimated Time to Solve: 30 Minutes
We provide the solution to this challenge using:
- ChatGPT 4.0
- Excel
- PowerPoint
- Word
(This challenge cannot be completed with the standard ChatGPT 3.5)
Need a program? Click here.
Overview
You are a junior data analyst at EcoInsights, a startup that helps cities become more sustainable. Recently, EcoInsights ran a pilot project in several cities to reduce energy use, water consumption, and waste, while cutting costs. The project produced a messy dataset from various sources. The data has dates in different formats, numbers mixed with text (like units and symbols), and extra characters in some fields. Your task is to use ChatGPT to clean up and standardize the data, calculate extra performance metrics, and create a clear summary and slide deck that illustrates your findings.
Instructions
-
Look over the provided dataset so that you know what you’re working with.
-
Import the Excel file (ChallengeX_EcoInsights_Dataset.xlsx) into ChatGPT.
Using ChatGPT, perform the following data cleaning procedures:
-
Convert all entries in the “Implementation Date” column into the YYYY-MM-DD format.
-
Remove any non-numeric characters (such as “kWh”, “%”, “dollars”) from the “Energy Savings (kWh)”, “Water Reduction (%)”, “Waste Reduction (%)”, and “Cost Savings ($)” columns, leaving only the numeric values.
-
Identify and remove any rows with missing or invalid numeric values (e.g., cells containing “?”).
-
Ensure that the percentage columns are converted into numbers only (e.g., “5%” should become 5).
Now that the data is cleaned, we want to perform additional analysis on the data to better understand the results. Using ChatGPT, compete the following:
-
Create a new column called “Performance Score” by computing the average of the water reduction and waste reduction percentages for each initiative.
-
Add a new column named “Savings Efficiency” calculated as the ratio of energy savings to cost savings.
-
Export the cleaned and enhanced dataset as a new Excel file.
-
Look over the Excel file that ChatGPT produced. Ensure that it completed all the data transformations that you prompted it to. If necessary, edit or add to your prompt until you get the desired output.
-
Use ChatGPT to produce a one-page summary of your findings as a Word Document, highlighting which initiatives are most effective and offering recommendations for future projects. Look over the Word Document that Chat produced. If necessary, edit or add to your prompt until you get the desired output. Once you are happy with the result, download the Word Document.
-
Use ChatGPT to generate a five slide PowerPoint presentation that summarizes your findings. Specify a color theme that you want ChatGPT to use and include any other specifications for your presentation. The more specific your prompt, the better your output will be. You may need to revise your prompt multiples times to create an acceptable output. Once you are happy with the result, download the PowerPoint.
-
Post your ChatGPT generated summary and PowerPoint, along with something new you learned by completing this challenge.
Data Files
Solution