Dataset

This dataset was created by Derrick Mallison

Contents

PROJECT OBJECTIVE

We are a part of XYZ Co Pvt Ltd company who is in the business of organizing the sports events at international level. Countries nominate sportsmen from different departments and our team has been given the responsibility to systematize the membership roster and generate different reports as per business requirements.

Questions (KPIs)

TASK 1: STANDARDIZING THE DATASET

• Populate the FULLNAME consisting of the following fields ONLY, in the prescribed format: PREFIX FIRSTNAME LASTNAME.{Note: All UPPERCASE)
• Get the COUNTRY NAME to which these sportsmen belong to. Make use of LOCATION sheet to get the required data
• Populate the LANGUAGE_!poken by the sportsmen. Make use of LOCTION sheet to get the required data
• Generate the EMAIL ADDRESS for those members, who speak English, in the prescribed format :lastname.firstnamel@xyz .org {Note: All lowercase) and for all other members, format should be lastname.firstname@xyz.com (Note: All lowercase)
• Populate the SPORT LOCATION of the sport played by each player. Make use of SPORT sheet to get the required data

TASK 2: DATA FORMATING

• Display MEMBER IDas always 3 digit number {Note: 001,002 ...,D2D,..etc)
• Format the BIRTHDATE as dd mmm'yyyy (Prescribed format example: 09 May' 1986)
• Display the units for the WEIGHT column (Prescribed format example: 80 kg)
• Format the SALARY to show the data In thousands. If SALARY is less than 100,000 then display data with 2 decimal places else display data with one decimal place. In both cases units should be thousands (k) e.g. 87670 -> 87.67 k and 12 250 -> 123.2 k

TASK 3: SUMMARIZE DATA - PIVOT TABLE (Use SPORTSMEN worksheet after attempting TASK 1) • Create a PIVOT table in the worksheet ANALYSIS, starting at cell B3,with the following details:

• In COLUMNS; Group : GENDER.
• In ROWS; Group : COUNTRY (Note: use COUNTRY NAMES).
• In VALUES; calculate the count of candidates from each COUNTRY and GENDER type, Remove GRAND TOTALs.

TASK 4: SUMMARIZE DATA - EXCEL FUNCTIONS (Use SPORTSMEN worksheet after attempting TASK 1)

• Create a SUMMARY table in the worksheet ANALYSIS,starting at cell G4, with the following details:

• Starting from range RANGE H4; get the distinct GENDER. Use remove duplicates option and transpose the data.
• Starting from range RANGE GS; get the distinct COUNTRY (Note: use COUNTRY NAMES).
• In the cross table,get the count of candidates from each COUNTRY and GENDER type.

TASK 5: GENERATE REPORT - PIVOT TABLE (Use SPORTSMEN worksheet after attempting TASK 1)

• Create a PIVOT table report in the worksheet REPORT, starting at cell A3, with the following information:

• Change the report layout to TABULAR form.
• Remove expand and collapse buttons.
• Remove GRAND TOTALs.
• Allow user to filter the data by SPORT LOCATION.

Process

• Verify data for any missing values and anomalies, and sort out the same.
• Made sure data is consistent and clean with respect to data type, data format and values used.
• Created pivot tables according to the questions asked.

The link for the Excel project to download can be found on GitHub here. It includes the raw data, Pivot Tables, and an interactive dashboard with Pivot Charts and Slicers. The project also includes business questions and the formulas I used to answer. The image below is included for ease. https://www.googleapis.com/download/storage/v1/b/kaggle-user-content/o/inbox%2F12904052%2F61e460b5f6a1fa73cfaaa33aa8107bd5%2FBusinessQuestions.png?generation=1686190703261971&alt=media" alt=""> The link for the Tableau adjusted dashboard can be found here.

A screenshot of the interactive Excel dashboard is also included below for ease. https://www.googleapis.com/download/storage/v1/b/kaggle-user-content/o/inbox%2F12904052%2Fe581f1fce8afc732f7823904da9e4cce%2FScooter%20Dashboard%20Image.png?generation=1686190815608343&alt=media" alt="">

The complete data set of annual utilization data reported by hospitals contains basic licensing information including bed classifications; patient demographics including occupancy rates, the number of discharges and patient days by bed classification, and the number of live births; as well as information on the type of services provided including the number of surgical operating rooms, number of surgeries performed (both inpatient and outpatient), the number of cardiovascular procedures performed, and licensed emergency medical services provided.

Summarize big data with pivot table and charts and slicers

A random sample of households were invited to participate in this survey. In the dataset, you will find the respondent level data in each row with the questions in each column. The numbers represent a scale option from the survey, such as 1=Excellent, 2=Good, 3=Fair, 4=Poor. The question stem, response option, and scale information for each field can be found in the var "variable labels" and "value labels" sheets. VERY IMPORTANT NOTE: The scientific survey data were weighted, meaning that the demographic profile of respondents was compared to the demographic profile of adults in Bloomington from US Census data. Statistical adjustments were made to bring the respondent profile into balance with the population profile. This means that some records were given more "weight" and some records were given less weight. The weights that were applied are found in the field "wt". If you do not apply these weights, you will not obtain the same results as can be found in the report delivered to the Bloomington. The easiest way to replicate these results is likely to create pivot tables, and use the sum of the "wt" field rather than a count of responses.

The Single-Family Portfolio Snapshot consists of a monthly data table and a report generator (Excel pivot table) that can be used to quickly create new reports of interest to the user from the data records. The data records themselves are loan level records using all of the categorical variables highlighted on the report generator table. Users may download and save the Excel file that contains the data records and the pivot table.The report generator sheet consists of an Excel pivot table that gives individual users some ability to analyze monthly trends on dimensions of interest to them. There are six choice dimensions: property state, property county, loan purpose, loan type, property product type, and downpayment source.Each report generator selection variable has an associated drop-down menu that is accessed by clicking once on the associated arrows. Only single selections can be made from each menu. For example, users must choose one state or all states, one county or all counties. If a county is chosen that does not correspond with the selected state, the result will be null values.The data records include each report generator choice variable plus the property zip code, originating mortgagee (lender) number, sponsor-lender name, sponsor number, nonprofit gift provider tax identification number, interest rate, and FHA insurance endorsement year and month. The report generator only provides output for the dollar amount of loans. Users who desire to analyze other data that are available on the data table, for example, interest rates or sponsor number, must first download the Excel file. See the data definitions (PDF in top folder) for details on each data element.Files switch from .zip to excel in August 2017.

This dataset contains annual Excel pivot tables that display summaries of the patients treated in each hospital-based and freestanding Ambulatory Surgery Clinic licensed by the California Department of Public Health (CDPH). The summary data includes discharge disposition, expected payer, preferred language spoken, age groups, race groups, sex, principal diagnosis groups, principal procedure groups, and principal external cause of injury/morbidity groups. The data can also be summarized statewide or for a specific facility county, type of control, and/or type of license (hospital or clinic). Note: Physician-owned ambulatory surgery clinics do not report their data to HCAI and, therefore, are not included in the statewide frequencies.

In the Europe bikes dataset, Extract the insight into sales in each country and each state of their countries using Excel.

Information on accidents casualites across Calderdale. Data includes location, number of people and vehicles involved, road surface, weather conditions and severity of any casualties.

Please note

• The Eastings and Northings are generated at the roadside where the accident occurred. Sometimes due to poor internet connectivity this data is may not be as accurate as it could be. If you notice any errors please contact accident.studies@leeds.gov.uk.

Due to the format of the report a number of figures in the columns are repeated, these are:

• Reference Number
• Easting
• Northing
• Number of Vehicles
• Accident Date
• Time (24hr)
• 1st Road Class
• Road Surface
• Lighting Conditions
• Weather Conditions

Reference Number

Grid Ref: Easting

Grid Ref: Northing

Number of vehicles

Accident Date

Time (24hr)

21G0539

427798

426248

5

16/01/2015

1205

21G0539

427798

426248

5

16/01/2015

1205

21G1108

431142

430087

1

16/01/2015

1732

21H0565

434602

436699>

1

17/01/2015

930

21H0638

434254

434318

2

17/01/2015

1315

21H0638

434254

434318

2

17/01/2015

1315

Therefore the number of vehicles involved in accident 21G0539 were 5, and in accident 21H0638 were 2. Overall in the example above a total of 9 vehicles were involved in accidents

A useful tool to analyse the data is Excel pivot tables, these help summarise large amounts of data in a easy to view table, for further information on pivot tables visit here.

Information on accidents across Leeds. Data includes location, number of people and vehicles involved, road surface, weather conditions and severity of any casualties.

Please note

• The Eastings and Northings are generated at the roadside where the accident occurred. Sometimes due to poor internet connectivity this data is may not be as accurate as it could be. If you notice any errors please contact accident.studies@leeds.gov.uk.

Due to the format of the report a number of figures in the columns are repeated, these are:

• Reference Number
• Easting
• Northing
• Number of Vehicles
• Accident Date
• Time (24hr)
• 1st Road Class
• Road Surface
• Lighting Conditions
• Weather Conditions

Reference Number

Grid Ref: Easting

Grid Ref: Northing

Number of vehicles

Accident Date

Time (24hr)

21G0539

427798

426248

5

16/01/2015

1205

21G0539

427798

426248

5

16/01/2015

1205

21G1108

431142

430087

1

16/01/2015

1732

21H0565

434602

436699>

1

17/01/2015

930

21H0638

434254

434318

2

17/01/2015

1315

21H0638

434254

434318

2

17/01/2015

1315

Therefore the number of vehicles involved in accident 21G0539 were 5, and in accident 21H0638 were 2. Overall in the example above a total of 9 vehicles were involved in accidents

A useful tool to analyse the data is Excel pivot tables, these help summarise large amounts of data in a easy to view table, for further information on pivot table visit here.

Further Information

• Please see the guidance document for further information on categories.

The complete data set of annual utilization data reported by primary care clinics contains basic clinic identification information including community services, clinic staffing data, and patient and staff language data; financial information including gross revenue, itemized write-offs by program, an income statement, and selected capital project items; and information on encounters by service, principal diagnosis, and procedure codes (CPT codes). These products provide trend utilization information for primary care clinics in the form of tables and pivot tables. The primary care clinic trends resource includes information on the number of clinics by type, the number of patients (by race, ethnicity, gender and age), the number of encounters by payer source; and revenues by payer source including the average revenue per encounter.

The City of Bloomington contracted with National Research Center, Inc. to conduct the 2019 Bloomington Community Survey. This was the second time a scientific citywide survey had been completed covering resident opinions on service delivery satisfaction by the City of Bloomington and quality of life issues. The first was in 2017. The survey captured the responses of 610 households from a representative sample of 3,000 residents of Bloomington who were randomly selected to complete the survey. VERY IMPORTANT NOTE: The scientific survey data were weighted, meaning that the demographic profile of respondents was compared to the demographic profile of adults in Bloomington from US Census data. Statistical adjustments were made to bring the respondent profile into balance with the population profile. This means that some records were given more "weight" and some records were given less weight. The weights that were applied are found in the field "wt". If you do not apply these weights, you will not obtain the same results as can be found in the report delivered to the City of Bloomington. The easiest way to replicate these results is likely to create pivot tables, and use the sum of the "wt" field rather than a count of responses.

This spreadsheet contains a number of sheets.Three sheets contain the main datasets from each of the first three RAAAP surveys.In addition there is a combined sheet containing data from all three sheets (where the semantics are the same) with an additional field indicating which survey it is from. This sheet has fewer colunms as it only has the shared variables.There is also a sheet for each survey listing the variables, and one showing the mappings between surveys, and one showing the common variables.Finally there is an example pivot table to show how the data can be easily visualised.This spreasheet was developed for the RAAAP workshop delivered at the 2023 INORMS Conference in May 2023 in Durban, South Africa.This spreadsheet contains all of the common data from the first 3 RAAAP surveys.These data are presented on separate

The Tate Collection Here we present the metadata for around 70,000 artworks that Tate owns or jointly owns with the National Galleries of Scotland as part of ARTIST ROOMS. Metadata for around 3,500 associated artists is also included. The metadata here is released under the Creative Commons Public Domain CC0 licence. Please see the enclosed LICENCE file for more detail. Images are not included and are not part of the dataset. Use of Tate images is covered on the Copyright and permissions page. You may also license images for commercial use. Please review the full usage guidelines. Repository Contents We offer two data formats: A richer dataset is provided in the JSON format, which is organised by the directory structure of the Git repository. JSON supports more hierarchical or nested information such as subjects. We also provide CSVs of flattened data, which is less comprehensive but perhaps easier to grok. The CSVs provide a good introduction to overall contents of the Tate metadata and create opportunities for artistic pivot tables. JSON Artists Each artist has his or her own JSON file. They are found in the artists folder, then filed away by first letter of the artist’s surname. Artworks Artworks are found in the artworks folder. They are filed away by accession number. This is the unique identifier given to artworks when they come into the Tate collection. In many cases, the format has significance. For example, the ar accession number prefix indicates that the artwork is part of ARTIST ROOMS collection. The n prefix indicates works that once were part of the National Gallery collection. CSV There is one CSV file for artists (artist_data.csv) and one (very large) for artworks (artwork_data.csv), which we may one day break up into more manageable chunks. The CSV headings should be helpful. Let us know if not. Entrepreneurial hackers could use the CSVs as an index to the JSON collections if they wanted richer data. Usage guidelines for open data These usage guidelines are based on goodwill. They are not a legal contract but Tate requests that you follow these guidelines if you use Metadata from our Collection dataset. The Metadata published by Tate is available free of restrictions under the Creative Commons Zero Public Domain Dedication. This means that you can use it for any purpose without having to give attribution. However, Tate requests that you actively acknowledge and give attribution to Tate wherever possible. Attribution supports future efforts to release other data. It also reduces the amount of ‘orphaned data’, helping retain links to authoritative sources. Give attribution to Tate Make sure that others are aware of the rights status of Tate and are aware of these guidelines by keeping intact links to the Creative Commons Zero Public Domain Dedication. If for technical or other reasons you cannot include all the links to all sources of the Metadata and rights information directly with the Metadata, you should consider including them separately, for example in a separate document that is distributed with the Metadata or dataset. If for technical or other reasons you cannot include all the links to all sources of the Metadata and rights information, you may consider linking only to the Metadata source on Tate’s website, where all available sources and rights information can be found, including in machine readable formats. Metadata is dynamic When working with Metadata obtained from Tate, please be aware that this Metadata is not static. It sometimes changes daily. Tate continuously updates its Metadata in order to correct mistakes and include new and additional information. Museum collections are under constant study and research, and new information is frequently added to objects in the collection. Mention your modifications of the Metadata and contribute your modified Metadata back Whenever you transform, translate or otherwise modify the Metadata, make it clear that the resulting Metadata has been modified by you. If you enrich or otherwise modify Metadata, consider publishing the derived Metadata without reuse restrictions, preferably via the Creative Commons Zero Public Domain Dedication. Be responsible Ensure that you do not use the Metadata in a way that suggests any official status or that Tate endorses you or your use of the Metadata, unless you have prior permission to do so. Ensure that you do not mislead others or misrepresent the Metadata or its sources Ensure that your use of the Metadata does not breach any national legislation based thereon, notably concerning (but not limited to) data protection, defamation or copyright. Please note that you use the Metadata at your own risk. Tate offers the Metadata as-is and makes no representations or warranties of any kind concerning any Metadata published by Tate. The writers of these guidelines are deeply indebted to the Smithsonian Cooper-Hewitt, National Design Museum; and Europeana.

Plant species collected throughout Benin were published on GBIF site. Data concerning those species were downloaded from GBIF site. Using Excel dynamic pivot table we derived and achieved the checklist of plant species of Benin from the dataset downloaded.

Abstract

The purpose of the WASH KAP survey was to collect primary data on several indicators related to the WASH Program implemented in the refugee and host communities of Palabek Settlement, Uganda. The survey aimed at assessing the level of improvement on the accessibility of WASH facilities after a 2 year intervention project.

The survey used cross-sectional design used and both qualitative and quantitative techniques such as use of UNHCR standard WASH questionnaires, field visits and observations were employed during the study. In the 2019/20, the LWF provided WASH services to both refugee settlements and host community living in and around Palabek settlement. In order to gauge the coverage, the LWF conducted this KAP survey. The respondents were drawn from the host community (238 households) and the refugee settlement (446 households).

Geographic coverage

Palabek

Analysis unit

Households

Universe

Refugees and host community

Kind of data

Sample survey data [ssd]

Sampling procedure

A sample size of 578 respondents was determined using the sample size calculator for the survey with a 4% margin of error and an 95% confidence level. Through the entire exercise, the survey process however reached a total of 684 respondents. Analysis, interpretation, and presentation of data were undertaken using Microsoft Excel Pivot tables and chats.

Mode of data collection

Computer Assisted Personal Interview [capi]

Task Categories

  1. Data Preparation and Hygiene (29 tasks)

De-duplication, type normalization, time parsing, joins/FX conversions, pivot tables

  2. Derivations & Extraction (16 tasks)

Correlations, z-scores, grouping logic, compliance filters (e.g., 1099)

  3. Modeling & Forecasts (5 tasks)

Revenue/breakeven projections, amortization schedules, depreciation calculations, scenario tables

  Example Task

For the ticker that has the greatest… See the full description on the dataset page: https://huggingface.co/datasets/hud-evals/SheetBench-50.

Original dataset by user Abdallah Wagih Ibrahim https://www.kaggle.com/datasets/abdallahwagih/company-employees/data

I created a pivot table visualizing the relationship between annual salary and job rate(performance) by region.

https://www.googleapis.com/download/storage/v1/b/kaggle-user-content/o/inbox%2F21036995%2F0ae505c2b2c7262a7fbda9acd9e90d2d%2FEmployeesPivotTable.png?generation=1723379896090719&alt=media" alt="">

Analyzing Coffee Shop Sales: Excel Insights 📈

In my first Data Analytics Project, I Discover the secrets of a fictional coffee shop's success with my data-driven analysis. By Analyzing a 5-sheet Excel dataset, I've uncovered valuable sales trends, customer preferences, and insights that can guide future business decisions. 📊☕

DATA CLEANING 🧹

• REMOVED DUPLICATES OR IRRELEVANT ENTRIES: Thoroughly eliminated duplicate records and irrelevant data to refine the dataset for analysis.

• FIXED STRUCTURAL ERRORS: Rectified any inconsistencies or structural issues within the data to ensure uniformity and accuracy.

• CHECKED FOR DATA CONSISTENCY: Verified the integrity and coherence of the dataset by identifying and resolving any inconsistencies or discrepancies.

DATA MANIPULATION 🛠️

• UTILIZED LOOKUPS: Used Excel's lookup functions for efficient data retrieval and analysis.

• IMPLEMENTED INDEX MATCH: Leveraged the Index Match function to perform advanced data searches and matches.

• APPLIED SUMIFS FUNCTIONS: Utilized SumIFs to calculate totals based on specified criteria.

• CALCULATED PROFITS: Used relevant formulas and techniques to determine profit margins and insights from the data.

PIVOTING THE DATA 𝄜

• CREATED PIVOT TABLES: Utilized Excel's PivotTable feature to pivot the data for in-depth analysis.

• FILTERED DATA: Utilized pivot tables to filter and analyze specific subsets of data, enabling focused insights. Specially used in “PEAK HOURS” and “TOP 3 PRODUCTS” charts.

VISUALIZATION 📊

• KEY INSIGHTS: Unveiled the grand total sales revenue while also analyzing the average bill per person, offering comprehensive insights into the coffee shop's performance and customer spending habits.

• SALES TREND ANALYSIS: Used Line chart to compute total sales across various time intervals, revealing valuable insights into evolving sales trends.

• PEAK HOUR ANALYSIS: Leveraged Clustered Column chart to identify peak sales hours, shedding light on optimal operating times and potential staffing needs.

• TOP 3 PRODUCTS IDENTIFICATION: Utilized Clustered Bar chart to determine the top three coffee types, facilitating strategic decisions regarding inventory management and marketing focus.

*I also used a Timeline to visualize chronological data trends and identify key patterns over specific times.

While it's a significant milestone for me, I recognize that there's always room for growth and improvement. Your feedback and insights are invaluable to me as I continue to refine my skills and tackle future projects. I'm eager to hear your thoughts and suggestions on how I can make my next endeavor even more impactful and insightful.

THANKS TO: WsCube Tech Mo Chen Alex Freberg

TOOLS USED: Microsoft Excel

DataAnalytics #DataAnalyst #ExcelProject #DataVisualization #BusinessIntelligence #SalesAnalysis #DataAnalysis #DataDrivenDecisions