20
docx
keyboard_arrow_up
School
Moi University *
*We aren’t endorsed by this school
Course
ICT200
Subject
Information Systems
Date
Nov 24, 2024
Type
docx
Pages
4
Uploaded by grantjoshua
Using Excel to Manipulate Data for Snowdrop Luxe Data Set
Excel is one of the most useful tools in data analysis and visualization, especially when handling
data that is not very large. In this report, Excel is used to analyze and visualize Luxe Data Set.
The main objective of the analysis and visualization is to identify Locations in London that have
high-income earners where Luxe can set up a new store. The London ONS dataset provides the
data that can be analyzed to identify locations within London that have high-income earners. The
dataset shows the mean income from self-employment, employment, pension, total mean
income, and total mean tax for various regions within London. Excel is suitable for analyzing
this data for various reasons. Firstly, the data is organized in rows and columns, meaning that it is
easy to visualize it with Excel. Secondly, the data is not large. It only has 75 (entries) rows of
data, which can be easily analyzed and visualized in Excel. To manipulate the data, the London
ONS data was imported into an Excel Workbook.
Use of Queries to Summarise and Group Data
Once the London ONS data was successfully imported into the Excel Workbook, the Power
Query opened automatically as shown in Figure 1 below. The Power Query provides a platform
to transform the imported data in various ways, including removing columns, removing rows,
changing data types, and grouping data, among other transformations. In the case of the
transformation process focused on removing unnecessary columns from the data, using the
manage columns feature. Removing unnecessary columns ensured that the analysis and
visualization process focused on necessary data only. Once the data transformation process was
completed, the data was loaded into the workbook.
Fig 1: Power Query
The summarized data is shown in Figure 2 below with only three columns, which are the
parliamentary constituency codes, the parliamentary constituency, and total income: mean (upper
95% CI). With these columns, it is easy to identify the regions that have high-income earners.
Fig 2: Data Summarization
Visual Presentation to Summarise Data
The process of visualizing the data began by loading the transformed data into the workbook.
The next step involved adding a pivot table. The role of a pivot table is to allow large amounts of
data to be grouped into an easy-to-read table as shown in Figure 3 below. The pivot table only
shows the parliamentary location and sum of total income (Upper 95% CI).
Fig 3: Pivot Table
After adding the pivot table, the next step focused on visualizing the data using one of the
techniques supported by Excel. The technique that appeared useful was the use of vertical bar
charts (column charts). Bar charts made it possible to compare the incomes of different regions
in London. The ability to compare the income levels of different regions allowed the
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
identification of suitable for Luxe to invest in. Figure 4 below shows the vertical bar chart used
to visualize the London location data.
Fig 4: Visualized London OSN data
As shown in Figure 4 above, various locations have the highest income. They include
Kensington with a mean income of 182,000, Chelsea and Fulham with a mean income of
148,000, and the Cities of London and Westminster with a mean income of 144,000. Based on
these findings, Luxe should prioritize investing in Kensington. If other factors come into play
and Kensington is considered unsuitable, then the company can on Chelsea and Fulham, and
finally the Cities of London and Westminster.