Excel Worksheet

/in /by

A Skills Approach: Excel 2016 Chapter 8: Exploring Advanced Data Analysis

1 | Page Challenge Yourself 8.4 Last Updated 4/9/18

Challenge Yourself 8.4 In this project, you will use scenarios and a scenario summary to analyze car purchasing options. The data for

this project were downloaded from: https://www.fueleconomy.gov/feg/download.shtml You will use custom

filtering to find the data you are interested in and copy it from this very large data set to another worksheet.

You will then use Advanced Filter, conditional formatting, sorting, and subtotals to further analyze this subset

of data. You will manipulate a PivotTable created from the main vehicle data set. Finally, you will use Solver

to solve a business problem to help you pay for your new car.

Skills needed to complete this project: • Creating What-If Analysis Scenarios

• Creating Scenario Summary Reports

• Creating a Custom Filter

• Using Advanced Filter

• Creating New Conditional Formatting Rules

• Sorting Data on Multiple Criteria

• Adding Subtotals

• Changing the Look of a PivotTable

• Filtering PivotTable Data

• Activating the Solver Add-Inn

• Using Solver

1. Open the start file EX2016-ChallengeYourself-8-4. The file will be renamed automatically to include

your name. Change the project file name if directed to do so by your instructor, and save it.

2. If the workbook opens in Protected View, click the Enable Editing button in the Message Bar at the

top of the workbook so you can modify the workbook.

3. You want to purchase a new car. Your current car is worth $3,500 as a trade-in. The base sticker price on

the car you want is $24,595. The first financing offer from the dealer is 1.9% APR for 24 months, with

no cash back. The original financing offer terms are listed in cells A3:B9 on the Car Loan worksheet. Cell

B10 contains a formula with a PMT function to calculate the monthly payment. Use what-if analysis tools

to compare financing options. Create scenarios to compare financing options for the car loan.

a. Create names for the following cells on the Car Loan worksheet to make the scenarios easier to

follow:

Name cell B3: Price

Name cell B4: TradeIn

Name cell B5: CashBack

Name cell B6: Loan

Name cell B8: Months

Name cell B9: APR

Name cell B10: Payment

Name cell B12: TotalCost

Step 1

Download start file

 

 

A Skills Approach: Excel 2016 Chapter 8: Exploring Advanced Data Analysis

2 | Page Challenge Yourself 8.4 Last Updated 4/9/18

b. Create a scenario named Original Financing to save the original values in cells B3, B5, B8,

and B9.

c. Create a second scenario named Intermediate Car. Change the values as follows:

Cell B3: 33999

Cell B5: 1500

Cell B8: 36

Cell B9: 2.4%

d. Show the Intermediate Car scenario to verify your data entry.

e. Create a third scenario named Luxury Car. Change the values as follows:

Cell B3: 62700

Cell B5: 2500

Cell B8: 60

Cell B9: 3.9%

f. Show the Luxury Car scenario to verify your data entry.

4. Create a scenario summary report to compare the results for cells B10 (the monthly payment) and B12

(the total cost of the car including interest).

5. The data in the MPG Data worksheet were downloaded from the Web site

https://www.fueleconomy.gov/feg/download.shtml. The greenhouse gas scores range from 0 to 10,

where 10 is best. The vehicles with the best scores on both air pollution and greenhouse gas receive the

SmartWay designation. Use custom filtering to find cars in this data set that meet your criteria and then

copy the smaller data set to another worksheet where you can work with it further.

a. Go to the MPG Data worksheet, and display the AutoFilter arrows for the data set.

b. Create a custom number filter to show only rows where the greenhouse gas score is greater than 8.

c. Create another custom number filter to show only rows where the highway MPG is greater than 35.

d. Create a custom text filter to show only rows where the Trans column contains the word auto.

e. Create a custom text filter to show only rows where the value in the Fuel column equals Electricity

or Gasoline.

f. At this point, the list of more than 2,000 cars should be filtered down to just 48 records.

g. Copy the filtered data to cell A1 in the My Car Data worksheet. Be sure to include the heading row,

but do not include the All Models title. Paste keeping the source column widths.

6. On the My Car Data worksheet, use Advanced Filter to copy a subset of data to another location on this

worksheet.

a. Copy cells A1:P1 and paste them below the data set in row 52.

b. In cell A51, type: Criteria

c. Apply the Accent 4 cell style to cell A51.

d. Set up the criteria in the row(s) below row 52. Find cars where the value in the Fuel column is

Gasoline and the value in the Hwy MPG column is greater than 35.

 

 

A Skills Approach: Excel 2016 Chapter 8: Exploring Advanced Data Analysis

3 | Page Challenge Yourself 8.4 Last Updated 4/9/18

e. In cell A55, type: Filtered Data

f. Apply the Accent 4 cell style to cell A55.

g. Autofit column A.

h. Use Advanced Filter to find the rows that meet the criteria and copy the filtered data to another

location beginning in cell A56.

i. Sixteen rows should have been copied from the main data set to cells A57:P72.

7. Format the filtered data set (cells A56:P72) as a table. Use White, Table Style Light 1, or Table Style

Light 1. Be careful not to include row 55 in the table data range.

8. Insert a new column to the left of the Greenhouse Gas Score column. Hint: Be sure to add a column to

the worksheet, not just the table.

a. Copy the values from cells M57:M72 to N57:N72.

b. Type this column label in cell N56: Cmb MPG Icon

9. Apply a new custom conditional formatting rule to cells N57:N72 to show the 4 Ratings icon set. Show

the icon only.

10. Continue working with the filtered data that has the conditional formatting applied. Sort the table first

alphabetically by the values in the Model column and then by icon in the Cmb MPG Icon column so

the Signal Meter With Four Filled Bars icon appears at the top.

11. Go back to the main data set at the top of the My Car Data worksheet and add subtotals for each change

in Model to calculate the average for the following: Air Pollution Score, City MPG, Hwy MPG, and

Cmb MPG.

12. Collapse the data to show just the total rows.

13. Go to the MPG PivotTable worksheet. This sheet includes a PivotTable created from the data on the

MPG Data worksheet.

a. Refresh the PivotTable data.

b. Apply the White, Pivot Style Medium 1, or Pivot Style Medium 1 Quick Style to the PivotTable.

c. Display a slicer for the SmartWay field and show only data where the SmartWay value is Elite.

14. To pay for the new car, you’ve decided to start a bakery business out of your home kitchen. You have limited

capital and capacity, so you need to make wise decisions about which products to bake each day. Go to the

Bakery Business worksheet. Cell B1 uses a SUMPRODUCT formula to calculate profit. Carefully review the

assumptions and formulas in this worksheet before entering the following Solver parameters:

a. Find the maximum possible profit (cell B1) by changing the values in cells C7:C10.

b. The working hours required (B3) must be less than or equal to the number of working hours in

the day (D3).

c. The total cost of ingredients (B4) must be less than or equal to the available capital (D4).

 

 

A Skills Approach: Excel 2016 Chapter 8: Exploring Advanced Data Analysis

4 | Page Challenge Yourself 8.4 Last Updated 4/9/18

d. For each item in the range C7:C10, the number of items baked cannot exceed the maximum capacity

available per day as defined in cells F7:F10. This restriction requires four separate constraints in the

Solver Parameters dialog.

e. The items in the variable cell range C7:C10 must be whole numbers.

f. There should be a total of seven constraints.

g. Run Solver and accept the Solver solution.

15. Save and close the workbook.

16. Upload and save your project file

17. Submit project for grading.

Step 2

Upload & Save

Step 3

Grade my Project

 
Do you need a similar assignment done for you from scratch? Order now!
Use Discount Code "Newclient" for a 15% Discount!

Project 1: Vulnerability Process And Assessment Memo

/in /by

Project Scenario

 

Previously, the CEO told you about the breach at a rival company and his concerns for security at Mercury USA. In light of these developments, Mercury USA has decided to conduct vulnerability assessments. An outside firm has conducted penetration tests on Mercury’s systems and provided the reports. Now, it is up to you as the cyber threat analyst to look at those reports, apply them to the specific company setup and business needs, and recommend changes.

Mercury USA’s concerns:

· protecting customer information

· protecting proprietary business data

· protecting dangerous loads/hazardous materials during transportation

Before you came onboard, a third-party penetration tester was brought in to assess Mercury USA’s security posture and network. One of the findings from the pen test report was the realization that an important part of a VM process is a vulnerability scanner. The pen tester provided a scan report using the free tool Open Vulnerability Assessment Scanner (OpenVAS). Judy has provided you with the OpenVAS report for your initial analysis.

 
Do you need a similar assignment done for you from scratch? Order now!
Use Discount Code "Newclient" for a 15% Discount!

SWOT Analysis

/in /by

SWOT Analysis
As discussed in the chapter, the primary purpose of the SWOT analysis is to identify and assign significant factors that affect the business to one of the four categories to analyze where it stands in the market and help guide an organization’s strategic planning process.

Don’t worry about elaborating on the nature of the strengths, weaknesses, opportunities, and threats; bullet points are sufficient at this stage of the analysis. Just capture the factors that are relevant in each of the four areas using two or three words to describe them.

After listing factors in all four areas, compare your lists side by side to get an overall picture of how the businesses is performing and what issues need to be addressed. Create four prioritized lists by prioritizing the issues by importance and ease of implementation by asking yourself “What needs to and can be addressed now?” and “What can and will have to wait until later?”

Finally, review the prioritized lists by asking:

How can we use our strengths to take advantage of the opportunities identified?
How can we use these strengths to overcome the threats identified?
What do we need to do to overcome the identified weaknesses to take advantage of the listed opportunities?
How can we minimize our weaknesses to overcome the identified threats?
When you have your finalized lists, the SWOT analysis is ready to guide the process of developing corporate and IT strategies. The value of a SWOT analysis depends on how well the analysis is performed. To gain the greatest value from performing a SWOT, carrying out the SWOT early on in the strategic planning process and following these rules helps:

Be realistic about the strengths and weaknesses of your organization.
Be realistic about the size of the opportunities and threats.
Be specific and keep the analysis simple or as simple as possible.
Evaluate your company’s strengths and weaknesses in relation to those of competitors (better than or worse than competitors).
Expect conflicting views because SWOT is subjective, forward-looking, and based on assumptions.
Have Fun with a Personal SWOT
To identify the actions you can take to best meet the requirements of the job or promotion you are seeking, conduct a personal SWOT analysis. To help you understand yourself, picture yourself as a competitive product in the marketplace, and list your strengths, weaknesses, opportunities, or threats from the perspective of a prospective “customer,” that is, your employer. Comparing your strengths and weaknesses to job requirements will help you identify gaps, prepare you to be the best candidate for the position you are seeking, and alert you to issues that could arise in the interview process.

The paper should be three to four pages long following APA format.

 
Do you need a similar assignment done for you from scratch? Order now!
Use Discount Code "Newclient" for a 15% Discount!

Graduate Professional Experience II DEC 1

/in /by

Week 4 Assignment Part 1:

Create a document that contains answers to the following questions:

1. My long-term career goal (3-5 years) is to be a (an):

a. Find this job on a company website or job board and include a link to it or copy and paste the job description.

b. To obtain this position, I foresee myself having to:

c. The date I have set to obtain this career goal is:

Week 4 Assignment Part 2:

2. My ultimate career goal is to be a (an):

a. Find this job on a company website or job board and include a link to it or copy and paste the job description.

b. In this position, I will perform the following tasks:

c. The date I have set to obtain this career tool is:

Your submission should be a minimum of two pages of content in length. Please type all work, save it as a doc or pdf and upload here. All work must be submitted in APA format.

Assignments with a similarity report greater than 10% will not be graded. 

 
Do you need a similar assignment done for you from scratch? Order now!
Use Discount Code "Newclient" for a 15% Discount!

Project 1: Vulnerability Process And Assessment Memo

/in /by

Project Scenario

 

Previously, the CEO told you about the breach at a rival company and his concerns for security at Mercury USA. In light of these developments, Mercury USA has decided to conduct vulnerability assessments. An outside firm has conducted penetration tests on Mercury’s systems and provided the reports. Now, it is up to you as the cyber threat analyst to look at those reports, apply them to the specific company setup and business needs, and recommend changes.

Mercury USA’s concerns:

· protecting customer information

· protecting proprietary business data

· protecting dangerous loads/hazardous materials during transportation

Before you came onboard, a third-party penetration tester was brought in to assess Mercury USA’s security posture and network. One of the findings from the pen test report was the realization that an important part of a VM process is a vulnerability scanner. The pen tester provided a scan report using the free tool Open Vulnerability Assessment Scanner (OpenVAS). Judy has provided you with the OpenVAS report for your initial analysis.

 
Do you need a similar assignment done for you from scratch? Order now!
Use Discount Code "Newclient" for a 15% Discount!

Network Week 3 Labs

/in /by

Network Pro Complete the following labs in LabSim:

  • 4.2.3 ReConnect to an Ethernet Network
  • 4.3.3 Connect Network Devices
  • 4.4.5 Exploring Physical Connectivity
  • 4.4.6 Troubleshoot Physical Connectivity 1
  • 4.4.7 Troubleshoot Physical Connectivity 2
  • 4.4.8 Troubleshoot Physical Connectivity 3
  • 4.4.9 Troubleshoot Physical Connectivity 4
  • 5.1.7 Configure IP Addresses
  • 5.1.8 Configure IP Addresses on Mobile Devices
  • 5.2.3 Configure Alternate Addressing
  • 5.3.2 Configure a DHCP Server
  • 5.3.4 Configure DHCP Options
  • 5.3.5 Create DHCP Exclusions
  • 5.3.6 Create DHCP Client Reservations
  • 5.3.8 Configure a DHCP Client
  • 5.4.3 Configure a DHCP Relay Agent
  • 5.4.4.Add a DHCP Server on Another Subnet
  • 5.5.4 Configure DNS Addresses
  • 5.5.5 Create Standard DNS Zones
  • 5.5.6 Create Reverse DNS Zones
  • 5.5.7 Create Host records
  • 5.5.8 Create CNAME Records
  • 5.5.9 Troubleshoot DNS Records
  • 5.6.9 Configure IPv6 Server
  • 5.8.5 Explore IP Configuration
  • 5.8.6 Troubleshoot IP Configuration Problems 1
  • 5.8.7 Troubleshoot IP Configuration Problems 2
  • 5.8.8 Troubleshoot IP Configuration Problems 3
  • 5.8.9 Troubleshoot IP Configuration Problems 4
  • 5.9.6 Exploring Network Communications
  • 5.10.4 Explore nslookup

Exam

 
Do you need a similar assignment done for you from scratch? Order now!
Use Discount Code "Newclient" for a 15% Discount!

Capstone Ethical Case Analysis

/in /by

Capstone Ethical Case Analysis

Being able to recognize professional responsibilities and make informed judgements in computing practice based on legal and ethical principles is an important student learning outcome for any ABET accredited computing program. To see how well you are able to apply ethical reasoning, we have put together a case for each team member to complete. Complete your responses to these questions and submit your work to the Blackboard Assignment Folder. Faculty will evaluate your responses against our ABET rubric to see how well our program is training students to think ethically as well as technically.

 

Question 1 – What is your Ethical Self-Awareness? What codes of conduct/professional guidelines/legislation guide your thinking? Check out ACM Code of Ethics and Professional Conduct – https://www.acm.org/code-of-ethics for one example of professional ethics.

· Are there any other professional codes of conduct, legislation, or business conduct guidelines that influence you?

· Can you describe the history of how your professional beliefs/conduct/guidelines that you follow can into being?

· Any thoughts as to why professional conduct is important?

 

 

Next, Read the case presented below.

 

 

The Copyright Concerns Case by Clare Bartlett – Obtained from: https://www.scu.edu/ethics/focus-areas/more/engineering-ethics/engineering-ethics-cases/copyright-concerns/

 

SDX Alliance is a large company that sells computers, computer components, and software. Ralph is hired as an entry-level software engineer at SDX Alliance. His first project was to assist in writing the code for SDX Alliance’s new hard disc controller. He had previously worked on a similar system interning at a start-up and had written a code which greatly enhanced the performance of their product. Ralph quietly re-uses this same code in the SDX Alliance product, and does not think to tell anyone that he has used the code from his last job. His manager is thrilled with the speed improvements this code brings to the product.

 

Before the product is released, it has to undergo a four-month long quality assurance process review. During the review of the product, it was found the code which Ralph developed had been copyrighted by the startup he had previously worked for. Even though Ralph had developed the code, his previous company still owned the intellectual property rights to it.

 

When his manager informed Ralph of the problem, Ralph admits he did not realize he had made a mistake because he was not familiar with copyright laws. Ralph then goes on to explain that the start-up he used to work for is now out of business and is unsure if SDX Alliance would be able to get in contact with the owner of the copyright. If SDX Alliance can’t use Ralph’s code, then it will have to rewrite the entire code of the product, delaying its release by many months.

 

What should they do?

 

 

Question 2 – Describe the Ethical Issues in this case. What are the ethical/socio-technical issues that you observed in this case? Some examples of ethical issues are privacy, security, intellectual property, potential for abuse of power, honesty, quality of life (work life), and sustainability?

 

Question 3 – Describe your Ethical Approach or Reasoning Strategy. This is a two part question.

Part A – What alternatives are available for resolving this case? Think about what options are available for addressing these ethical/social-technical issues for this case.

Part B – What decision thought process(s) or criteria did you decide to use in order to evaluate the various alternatives available for addressing the ethical/legal issues. Check out the Ethical Decision Making Page (https://www.scu.edu/ethics/ethics-resources/ethical-decision-making/) from the Markkula Center for Applied Ethics at Santa Clara University for some approaches to consider.

 

Question 4 – Describe your Application of Ethical Approach/Reasoning Strategy in order to evaluate your Options. For each of the alternatives that you identified as part of Question 3, describe the results of your evaluation process. What are the pros and cons of each alternative?

 

Question 5 – Present your Final Position – What was your ultimate decision in how to address those ethical/social-technical issues for this case? Why do you feel this decision was the most appropriate one to follow?

 
Do you need a similar assignment done for you from scratch? Order now!
Use Discount Code "Newclient" for a 15% Discount!

MIS 180 SIMNET ASSIGNMENTS

/in /by

Excel 2013 Chapter 2 Working with Formulas and Functions Last Updated: 2/4/15 Page 1

USING MICROSOFT EXCEL 2013 Independent Project 2-5

Independent Project 2-5 When San Diego Sailing received its end-of-year financial reports, its financial officers decided to evaluate the rental rates schedule for boats. You have been asked to create a spreadsheet to compare three different proposed rate changes that the group is considering implementing in the new year and to update information on the boat fleet.

Skills Covered in This Project

 Create and copy formulas.

 Apply mathematical order of operations.

 Use relative, absolute, and mixed cell references.

 Apply the IF function.

 Name cells and use the name range in a

formula.

 Apply the TODAY function.

 

 

1. Open the SanDiego-02.xlsx start file. The file will be renamed automatically to include your name. Change the project file name if directed to do so by your instructor, and save it.

NOTE: If the workbook opens in Protected View, click the Enable Editing button in the Message Bar at the

top of the workbook so you can modify it.

2. Select H4 and create an IF function formula to determine which boats include a stove with the galley. Boats

must be able to seat 8 or more people to have a stove in the galley.

a. Use “Yes” for the value_if_true argument.

b. Use “No” for the value_if_false argument.

c. Copy the formula in H4 to H5:H18.

3. Insert a worksheet and name the sheet Data.

4. Type the following information in the new worksheet:

 

5. Format the table text on the Data sheet.

a. Merge and Center the text in A3 over A3:B3.

b. Apply Accent 1 cell style to A3 and apply bold format.

c. Select A3:B6 and apply the All Borders format.

6. Select A4:B6 and assign range names using the Create from Selection button on the

Formulas tab.

NOTE: To ensure accurate grading, you must complete step 6 correctly before continuing

with the project. Check your work carefully.

7. Select J4 on the Fleet worksheet, and create a formula to calculate a 5% increase for the data

in the “4 Hr. Rate” column using the range name assigned on the Data sheet.

a. Type =. b. Select F4 and type *. c. Press F3 to open the Paste Name dialog box.

d. Select _ 5_Increase and click OK.

e. Press Enter. Copy the formula to row 18.

 

Step 1

Download start file

 

 

 

 

Excel 2013 Chapter 2 Working with Formulas and Functions Last Updated: 2/4/15 Page 2

USING MICROSOFT EXCEL 2013 Independent Project 2-5

8. Create formulas for K4:O4 and copy the formulas to row 18.

9. Select the amounts in columns F, G, and J:O and apply the Number format.

10. Select A1 and type San Diego Sailing.

11. Format A1 using the Title cell style and apply bold format. Adjust the column width.

12. Insert a row above the current row 2. Clear the formatting of the new row 2.

NOTE: To ensure accurate grading, you must complete step 12. Check your work carefully.

13. Insert the TODAY() function in A2. Format the date as 11 pt. bold and align left.

14. Change the date format to display the month as a word followed by the day and year.

15. Apply the All Borders format to rows 4 through 19.

16. Click the Insert tab and click the Header & Footer button.

a. Switch to the Footer area.

b. Click the right text box and type Page followed by a space. c. Click the Page Number button on the Header & Footer Tools Design tab.

d. Click the worksheet and return to Normal view.

17. Paste range names in a worksheet.

a. Click the Data sheet tab and select cell A15.

b. Click the Use in Formula button [Formulas tab, Defined Names group].

c. Click Paste Names.

d. Click the Paste List button in the Paste Name dialog box.

e. Click OK.

18. Save and close the workbook (Figure 2-74).

19. Upload and save your project file.

20. Submit project for grading.

 

 

Step 2

Upload & Save

Step 3

Grade my Project

2-74 Excel 2-5 completed

 
Do you need a similar assignment done for you from scratch? Order now!
Use Discount Code "Newclient" for a 15% Discount!

Unit 4 Discussion: Web Services And Web Pages

/in /by

POST I HAVE TO ANSWER

Web services and web pages are very different entities. It is important to understand how they work together as part of a Cloud solution. What are the differences between web pages and web services? Describe how they work together and why they are important to each other. Is one more important than the other? Back up your opinion with facts!

Be sure to use at least 2 external sources and to include all of your sources by providing citations/links to the Web pages you used in APA format.

 

 

POST 1

What are the differences between web pages and web services?

A Web service is a web application component that uses standardized format like XML to interact with other web applications over internet. Whereas a website is a collection of webpages put together which are accessed by a web browser.

The difference between a web service and a website or webpage are as follows:

Web Service

A web service doesn’t have a user interface.

Web services are meant for other applications to be interacted with over internet.

Web services are platform independent as they use open protocols.

Web services are accessed by HTTP methods – GET, POST, PUT, DELETE etc.

Example – Google maps API is a web service that can be used by websites to display Maps by passing coordinates to it.

Website or page

A website has a user interface or GUI.

Websites are meant for use by humans.

Websites are cross platform as they require tweaking to operate on different browsers, operating systems etc.

Websites are accessed by using their GUI components – buttons, text boxes, forms etc.

 

Describe how they work together and why they are important to each other. Is one more important than the other?

Web services are the primary way to for businesses to communicate with each other and with clients. Allowing them to communicate without intimate knowledge of each other’s IT systems behind the firewall. Both web services and websites work together, one is informational and the other is interactive, one is not more important than the other.

References

N.A. (N.D.). What is the difference between webpages, website, web server, and search engine? MDN web docs. Retrieved from https://developer.mozilla.org/en-US/docs/Learn/Common_questions/Pages_sites_servers_and_search_engines (Links to an external site.)Links to an external site.

Segue Technologies. (2013, March 13). What Are Web Services and Where Are They Used? Segue III Technologies. Retrieved from https://www.seguetech.com/web-services/ (Links to an external site.)Links to an external site.

 

POST 2

Web Pages

Web pages are used to display the company site, which would say amazon or google chrome. This document is created for the visitors that come to look at the services provided, because you could have a document that shows every available service that is offered. This would be called a web site, because It would have multiple pages tied together. The real power of the page come from the links, which would redirect you to the services available. In conclusion a web page is just a document that displays the company in a web browser.

Web services

Web services would be classified as IaaS, SaaS, GaaS, and IDaas. Web services provide a service through multiple servers, which would consist of virtual machines with software installed. This is just one way you could provide a service, because your email client would not work without the VM. The next service would be data storage, because many companies like to have an offsite backup. This would be another service offered as a web services. There are many web services that can be provided, so web services offer customers a service and the web page is just a document displaying the company information.

How do they work together

I will a brief minute and explain how the web page works with the web services. The web page would show the company logo, which is tied to the services as well. Once you reach the web page, then it would contain links to the services and have any information about the company. Some companies would have multiple pages, because they want you to know where they started from. The main purpose for having a web page, consist of the name, information about the company, links to the services, and the company history.

References

MDN.(2019).What is the between webpage, website, web server, and search engine. Retrieve by:

https://developer.mozilla.org/en-US/docs/Learn/Common_questions/Pages_sites_servers_and_search_engines (Links to an external site.)Links to an external site.

 

Margaret.R(2013).AWS. Retrieved by:

 https://searchaws.techtarget.com/definition/Amazon-Web-Services (Links to an external site.)Links to an external site.

 
Do you need a similar assignment done for you from scratch? Order now!
Use Discount Code "Newclient" for a 15% Discount!

Principal Components And Factor Analysis Using Python Code In 40 Hours

/in /by

#This assignment is a tutorial so have a bit of fun with it

#If you would like to explore some additional options give it a try

 

#Goal is to provide some meaningful info to the restaurant owner

 

#Some notes below

#Both PCA and FA provide useful summary info for multivariate data, but

#all of the original variables are needed for their calculation, so

#the big question is can we use them to find a subset of variables to

#predict overall score?

#Also,trying to give meaningful labels to components is really hard.

#When the variables are on different scales you need to work with the

#correlation matrix. For this assignment they are on same scale so

#we will work with the raw data.

#PCA only helps if the original variables are correlated, if they

#are independent PCA will not help.

 

#Approach takes two steps

#First step find the dimensionality of the data, that is the

#number of original variables to be retained

#Second step find which ones, more on this below

 

# import packages for this example

import pandas as pd

import numpy as np # arrays and math functions

import matplotlib.pyplot as plt # static plotting

from sklearn.decomposition import PCA, FactorAnalysis

import statsmodels.formula.api as smf # R-like model specification

 

#Set some display options

pd.set_option(‘display.notebook_repr_html’, False)

pd.set_option(‘display.max_columns’, 40)

pd.set_option(‘display.max_rows’, 10)

pd.set_option(‘display.width’, 120)

 

#Read in the restaurant dataset

food_df = pd.read_csv(‘C:/Users/Jahee Koo/Desktop/MSPA/2018_Winter_410_regression/HW03 PCA/FACTOR1.csv’)

#A good step to take is to convert all variable names to lower case

food_df.columns = [s.lower() for s in food_df.columns]

print(food_df)

 

print(”)

print(‘—– Summary of Input Data —–‘)

print(”)

 

# show the object is a DataFrame

print(‘Object type: ‘, type(food_df))

 

# show number of observations in the DataFrame

print(‘Number of observations: ‘, len(food_df))

 

# show variable names

print(‘Variable names: ‘, food_df.columns)

 

# show descriptive statistics

print(food_df.describe())

 

# show a portion of the beginning of the DataFrame

print(food_df.head())

 

#look at correlation structure

cdata = food_df.loc[:,[‘overall’,’taste’,’temp’,’freshness’,’wait’,’clean’,’friend’,’location’,’parking’,’view’]]

corr = cdata[cdata.columns].corr()

print(corr)

#Use the correlation matrix to help provide advice to the restaurant owner

 

#Look at four different models and compare them

#Which model do you think is best and why?

#Model 1 full regression model

#Model 2 select my reduced regression model taste, wait and location

#Model 3 Full PCA model

#Model 4 Reduced PCA model with parking, taste and clean

#Model 5 FA model

 

#First find the PCA

#Second find the FA

#Run the models

#Compare the models and show VIF for each model

 

#PCA

print(”)

print(‘—– Principal Component Analysis —–‘)

print(”)

pca_data = food_df.loc[:,[‘taste’,’temp’,’freshness’,’wait’,’clean’,’friend’,’location’,’parking’,’view’]]

pca = PCA()

P = pca.fit(pca_data)

print(pca_data)

np.set_printoptions(threshold=np.inf)

np.around([pca.components_], decimals=3)

#Note per Everett p209 pick the three variables with the largest

#absolute coefficient on the component not already picked

#So, choose parking, taste and clean for the PCA variable reduction model

 

# show summary of pca solution

pca_explained_variance = pca.explained_variance_ratio_

print(‘Proportion of variance explained:’, pca_explained_variance)

 

# note that principal components analysis corresponds

# to finding eigenvalues and eigenvectors of the pca_data

pca_data_cormat = np.corrcoef(pca_data.T)

eigenvalues, eigenvectors = np.linalg.eig(pca_data_cormat)

np.around([eigenvalues], decimals=3)

 

print(‘Linear algebra demonstration: Proportion of variance explained: ‘,

eigenvalues/eigenvalues.sum())

np.around([eigenvectors], decimals=3)

 

# show the plot for the pricipal component analysis

plt.bar(np.arange(len(pca_explained_variance)), pca_explained_variance,

color = ‘grey’, alpha = 0.5, align = ‘center’)

plt.title(‘PCA Proportion of Total Variance’)

plt.show()

 

# show a scree plot

d = {‘eigenvalues’: eigenvalues }

df1 = pd.DataFrame(data=d)

df2 =pd.Series([1,2,3,4,5,6,7,8,9])

#df2 = {‘factors’: factors}

# merge eigenvalues with # of factors

result = pd.concat([df1, df2], axis=1, join_axes=[df2.index])

print (result)

 

def scat(dataframe,var1,var2):

dataframe[var2].plot()

plt.title(‘Scree Plot’)

plt.xlabel(‘# of factors’)

plt.ylabel(‘Eigenvalues’)

 

scat(result,’0′,’eigenvalues’)

plt.show()

 

# provide partial listing of variable loadings on principal components

# transpose for variables by components listing

pca_loadings = pca.components_.T

 

# provide full formatted listing of loadings for first three components

# print loadings while rounding to three digits

# and suppress printing of very small numbers

# but do not suppress printing of zeroes

np.set_printoptions(precision = 3, suppress = True,

formatter={‘float’: ‘{: 0.3f}’.format})

print(pca_loadings[:,0:3])

 

# compute full set of principal components (scores)

C = pca.transform(pca_data)

print(C)

# add first three principal component scores to the original data frame

pca_data[‘pca1’] = C[:,0]

pca_data[‘pca2’] = C[:,1]

pca_data[‘pca3’] = C[:,2]

print(pca_data)

# add first three principal component scores to the food_df

food_df[‘pca1’] = C[:,0]

food_df[‘pca2’] = C[:,1]

food_df[‘pca3’] = C[:,2]

print(food_df)

 

# explore relationships between pairs of principal components

# working with the first three components only

pca_scores = pca_data.loc[:,[‘pca1′,’pca2’, ‘pca3’]]

pca_model_cormat = \

np.corrcoef(pca_scores.as_matrix().transpose()).round(decimals=3)

print(pca_model_cormat)

#Looks like that worked

 

#Factor Analysis

print(”)

print(‘—– Factor Analysis (Unrotated) —–‘)

print(”)

 

# assume three factors will be sufficient

# this is an unrotated orthogonal solution

# maximum likelihood estimation is employed

# for best results set tolerance low and max iterations high

fa = FactorAnalysis(n_components = 3, tol=1e-8, max_iter=1000000)

 

#the unrotated solution

fa.fit(pca_data)

 

# retrieve the factor loadings as an array of arrays

# transpose for variables by factors listing of loadings

fa_loadings = fa.components_.T

print(fa_loadings)

 

# show the loadings of the variables on the factors

# for the unrotated maximum likelihood solution

# print loadings while rounding to three digits

# and suppress printing of very small numbers

# but do not suppress printing of zeroes

np.set_printoptions(precision = 3, suppress = True,

formatter={‘float’: ‘{: 0.3f}’.format})

print(fa_loadings)

 

# compute full set of factor scores

F = fa.transform(pca_data)

print(F)

# add factor scores to the original data frame

food_df[‘fa1’] = F[:,0]

food_df[‘fa2’] = F[:,1]

food_df[‘fa3’] = F[:,2]

print(food_df)

 

#Look at five different models and compare them

#Which model do you think is best and why?

#Model 1 full regression model

#Model 2 select my reduced regression model taste, wait and location

#Model 3 Full PCA model

#Model 4 Reduced PCA model with parking, taste and clean

#Model 5 FA model

 

#Run the Models

#Model 1 full model

regress_model_fit = smf.ols(formula = ‘overall~taste+temp+freshness+wait+clean+friend+location+parking+view’, data = food_df).fit()

# summary of model fit

print(regress_model_fit.summary())

 

#Model 2

#Note, Model 2 is a choice from looking at the correlation, you may choose a

#different selection for this if you like, just explain why

regress_model_fit = smf.ols(formula = ‘overall~taste+wait+location’, data = food_df).fit()

# summary of model fit

print(regress_model_fit.summary())

 

#Model 3

#regress the response overall on principal components

pca_model_fit = smf.ols(formula = ‘overall~pca1+pca2+pca3’, data = food_df).fit()

# summary of model fit

print(pca_model_fit.summary())

 

#Model 4

#regress the response overall on principal components

pca_model_fit = smf.ols(formula = ‘overall~parking+taste+clean’, data = food_df).fit()

# summary of model fit

print(pca_model_fit.summary())

 

#Model 5

#regress the response overall on factor scores

fa_model_fit = smf.ols(formula = ‘overall~fa1+fa2+fa3’, data = food_df).fit()

# summary of model fit

print(fa_model_fit.summary())

 

#next look at VIF to see what the full, choice, PCA and FA models did

# Break into left and right hand side; y and X then find VIF for each model

import statsmodels.formula.api as sm

from patsy import dmatrices

from statsmodels.stats.outliers_influence import variance_inflation_factor

y = food_df.loc[:,[‘overall’]]

X = food_df.loc[:,[‘taste’,’temp’,’freshness’,’wait’,’clean’,’friend’,’location’,’parking’,’view’]]

y, X = dmatrices(‘overall ~ taste+temp+freshness+wait+clean+friend+location+parking+view ‘, data=food_df, return_type=”dataframe”)

 

# For each Xi, calculate VIF

vif = [variance_inflation_factor(X.values, i) for i in range(X.shape[1])]

print(”)

print(‘—– VIF for Full Regression Model —–‘)

print(”)

print(vif)

 

#VIF for choice model

y = food_df.loc[:,[‘overall’]]

X = food_df.loc[:,[‘taste’,’clean’,’location’]]

y, X = dmatrices(‘overall ~ taste+clean+location ‘, data=food_df, return_type=”dataframe”)

vif = [variance_inflation_factor(X.values, i) for i in range(X.shape[1])]

print(”)

print(‘—– VIF for Choice Model —–‘)

print(”)

print(vif)

 

#VIF for PCA

y = food_df.loc[:,[‘overall’]]

X = food_df.loc[:,[‘pca1′,’pca2′,’pca3’]]

y, X = dmatrices(‘overall ~ pca1+pca2+pca3 ‘, data=food_df, return_type=”dataframe”)

vif = [variance_inflation_factor(X.values, i) for i in range(X.shape[1])]

print(”)

print(‘—– VIF for PCA Model —–‘)

print(”)

print(vif)

 

#VIF for FA

y = food_df.loc[:,[‘overall’]]

X = food_df.loc[:,[‘fa1′,’fa2′,’fa3’]]

y, X = dmatrices(‘overall ~ fa1+fa2+fa3 ‘, data=food_df, return_type=”dataframe”)

vif = [variance_inflation_factor(X.values, i) for i in range(X.shape[1])]

print(”)

print(‘—– VIF for FA Model —–‘)

print(”)

print(vif)

 

#Which model do you like best and why?

#For the full regression model sum the coefficients for each three variable

#grouping, taste, temp freshness group 1

#wait, clean, friend group 2

# location, parking, view group 3

#How do you interpret this info?

#Compare with the choice model

 
Do you need a similar assignment done for you from scratch? Order now!
Use Discount Code "Newclient" for a 15% Discount!