Homework answers / question archive / Data sets • Conduct Exploratory Data Analysis (EDA) to prepare data for further analysis

Data sets • Conduct Exploratory Data Analysis (EDA) to prepare data for further analysis

MS Excel

Share With

---

Data sets • Conduct Exploratory Data Analysis (EDA) to prepare data for further analysis. • Analyze data for relationships and/or trends using an Excel spreadsheet with PivotTables. • Create at least five visualizations. • Create a PowerPoint presentation to communicate the results of your data analysis.
 

Create a table from the data and save it as Excel. You will want to save multiple versions of the data as you work through the EDA in case you make a change that you realize affects your data in later steps.        <Complete this step in Excel.>

Examine each field (columns), are there any fields that are blank or empty? You can find this by clicking on the filter arrow next to each field and scrolling down to see if there are any blanks.           

Continuing your examination of the fields, do you have any categorical fields? Which fields are categorical? How did you know that they were categorical?   

                <Detailed examination of fields in the next section. Add rows to following tables as needed.>

 

Examination of Categorical Fields

Name of field    Brief description               Number of categories

                               

                               

Examination of Continuous (Numerical) Fields (With the Assistance of the Excel Data Analysis ToolPak)

Name of field    Brief description               Minimum value Maximum value               Average               Standard deviation

                                                                               

                                                                               

                                                                                      

                                                         Examination of Additional Fields, Neither Categorical nor Continuous (If Any)

Name of field    Brief description               Type of data

                               

                               

                               

                               

                               

                               

 

Review of Original Goals and Optional Comments/Observations

Now that you know about the variables in your dataset, you need to go back to your original problem that you are trying to solve. Review your goals again.

                <Comments/Observations>

 

 

Initial Trend Analysis: First Steps Using a PivotTable in Excel

First, we are going to create a PivotTable for different data combinations. Let's see if visualizing the data will help you to start to see trends.

 

Task       Response (if applicable)

If your data within Excel is not in the form of an Excel table, you should first transform the data into an Excel table.                 <No response required here.>

 

Highlight the entire Excel table of your data and click on "Insert," then select PivotTable.               <No response required here.>

 

Now, you will want to create this PivotTable on its own Worksheet, so let it make a new one for you.     <No response required here.>

 

In the Rows area, drag a (any) categorical variable, in the values area, drag one (any) of the continuous variables.

 

Note: You will likely need to repeat this step several times, with different variables and different combinations of variables. If you find a combination of interest, feel free to create a new PivotTable from the original data in a new Worksheet and continue to experiment.

                <No response required here.>

 

The default calculation performed on the values will be a summation, but this might not be helpful for the problem that you are trying to solve. Click on the down arrow next to "sum of xxx" and look at the options. What is a better calculation? Average? Count?    <Response required here.>

Now, you should have a PivotTable created in your new sheet, change the name of the worksheet to a label that will help you to keep your work organized. Don't forget to save.       <No response required here.>

 

Now, examine the PivotTable that you created. What patterns are you finding? Is there anything that is interesting or surprising to you? Is it what you expected based on your initial examination? Do you need to make any changes? Maybe you picked Average and you really wanted to count the records?              <Response required here.>

Try adding a filter (can be categorical or continuous). Does a filter help you get closer to solving your problem?                <Response required here.>

Keep experimenting with your PivotTable, maybe you need to add a column to help you get more information.                 <No response required here.>

 

Data Visualization

When you feel you are getting a little closer to finding an answer to your problem, create a visualization. Your work in the following section will take place entirely in Excel.

 

1.            Click on the PivotTable and go to "Insert" then "PivotChart." You will have multiple options for visualizations. Based on your work in previous modules, you should choose a visualization that is most appropriate for your data.

2.            Now is your chance to experiment and try different combinations, try different variables, and different graph charts. Use the "recommended chart" option to see which would be the most helpful. You will likely create many visualizations and combinations. When you create one you like, move it to its own chart worksheet so that you can save it for later. Make sure you save often!

3.            Pick at least five visualizations that help you get closer to solving your problem. You should have a bar/column chart, scatterplot, line graph, a histogram, and the last is any that you choose. Make sure that each chart is well labeled and will make sense to your audience.

 

 

Data Communication and Story

For the last step, you will pull it all together to tell a story in a PowerPoint presentation.  Your work in the following section will take place entirely in PowerPoint. Feel free to use the second column in the table below for notes, but keep in mind that none of the responses in this section are required. Your PowerPoint presentation should stand on its own to convey your analysis and work in this section as well as your overall work on this project.

 

 

Task       Notes for Preparing Your PowerPoint

Explain the problem      

Articulate the questions that you are trying to answer   

Provide background on the data, how many records, fields, types of data (include the three tables you created). What were the outliers, averages, and ranges?

Insert each of the five visualizations that you created and provide an explanation of each visualization. How does the visualization help answer the question?               

Provide a final analysis and conclusion, what do you want your audience to have learned from your work?          

What recommendations do you have to continue solving this problem? What data would help make better decisions?          

 

 

 

 

 

Final Instructions on Submitting Your Assignment

Once you have the shareable link in the table on page 1 of this document, you will be ready to submit all of your assignment files. You will submit three files:

•             this Data Analysis Project Template (completed, with shareable link);

•             your Excel file with data, relevant PivotTable(s), and visualizations; and

•             your PowerPoint presentation.