Understanding Data Types

Data Types

• Gender (male, female) is categorical nominal data.
• Product type (cosmetics, furniture, etc.) is categorical nominal data.
• Cities (e.g., Cairo, Alex) are categorical nominal data.
• Grades (good, very good, excellent, etc.) are categorical ordinal data.
• Sizes (S, M, L) are categorical ordinal data.
• Volume (low, medium, high) is categorical ordinal data.
• Number of students in the class is numerical discrete (interval) data.
• Number of desks in the office is numerical discrete (interval) data.
• Sales of a shop is numerical continuous (ratio) data.
• Salary is numerical continuous (ratio) data.
• Number of days in the month is numerical discrete data.
• Number of people in the session is numerical discrete data.
• National ID number is categorical nominal data.
• Transaction ID is categorical nominal data.
• Serial ID is categorical nominal data.
• Order date is date data.
• Phone number is categorical nominal data.
• Product ID is categorical nominal data.
• Product price is numerical continuous data.
• Product quantity is numerical discrete data.
• Order date is date data.
• Time stamp is time data.
• Test mark is numerical continuous data.

Types of Data Analysis

• Descriptive analysis analyzes past data to understand current state and trend identification.
• Diagnostic analysis explores data to understand why certain events occurred, identifying patterns.
• Predictive analysis uses statistical models based on past data to forecast future outcomes and is used widely in finance, healthcare, and marketing.

Data Science Lifecycle

• Data collection and storage is the first stage in the data science life cycle.

Data and Business Analytics

• The main concern of both data analytics and business analytics is decision making.

Data Science

• The main concern of data science is future prediction.

Excel Functions

• The "freeze panes" function is used to fix the first row of titles, making them visible while scrolling. To make the first row of titles fixed: freeze panes >> freeze top row.
• Ctrl + right arrow is used to reach the last filled right cell in a sheet.
• Pressing "Ctrl + down arrow" twice in a filled sheet selects the last cell downwards in the sheet.
• Ctrl + Shift + arrows is used to select the whole row or column.
• If the first cell is selected and shift + ctrl+ left arrow+ up arrow are pressed together, the whole table will be selected.
• To calculate the sum of cells G1, G2, G3, G4, and G5, the formula is: =sum(G1:G5).
• The option to unify decimal places is: format cells >> numbers >> decimal places >> two decimal points.
• To copy and paste a number as a constant number, use "paste as value".
• To copy the formula but not the number, use "paste as formula".
• The data type can be changed through "data formatting".
• To make a column invisible, use the "hide" option.
• The formula to display today's date is =TODAY().
• To sort data by date (oldest to newest) and then by sales (largest to smallest): Select the data range > Go to Data > Sort > Sort by Date (Oldest to Newest) > Add Level > Then Sort by Sales (Largest to Smallest).
• To keep the header row visible while scrolling, select the row below the header row and go to: View > Freeze Panes > Freeze Top Row.
• The SUM function calculates the sum of a range of cells.
• The MAX function returns the largest value in a set of numbers.
• The COUNT function counts the number of cells that contain numbers within a range.
• The COUNTIF function counts cells based on a specific criterion.
• The formula =IF(A1>10, "Pass", "Fail") displays "Pass" if the value in A1 is greater than 10, otherwise "Fail".
• The VLOOKUP function is used to find a value in a table and return a corresponding value from another column.
• In the formula =VLOOKUP(A1, $B1$:$C10$, 2, FALSE), "2" represents the column index number.
• The formula to calculate the average of cells D1 to D20 is =AVERAGE(D1:D20).
• The formula to count the number of cells in the range A1:A10 that contain the word "Apple" is =COUNTIF(A1:A10, "Apple").
• The syntax of the IF function is =IF(logical_test, value_if_true, value_if_false).
• The FALSE argument in a VLOOKUP function means exact match.
• To extract the year from a date in cell C5, use the formula =year(C5)
• The result of =5*2+3 in Excel is 13.
• If cell A1 contains the number 10 and cell B1 contains the number 5, =A1/B1 will display 2.
• "#####" in a cell indicates that the column is not wide enough to display the entire number or date.
• The $ sign creates an absolute reference in VLOOKUP formulas, preventing the row or column from changing when the formula is copied, and is important for the table array to always reference the same lookup table.

Data Cleaning

• Data cleaning involves removing errors, inconsistencies, and inaccuracies from a dataset.
• An example of data inconsistency is one record showing "USA" as a country, and another showing "United States".
• An example of data inaccuracy is a person's age being recorded as 250.
• Data cleaning is important because it ensures accurate analysis and improves data quality.

Data visualization

• Data visualization is the graphical representation of information and data.
• Three common types of charts in Excel are pie, bar, and scatter plot.
• A pie chart is most appropriate when showing parts of a whole (percentages).
• A bar chart is most appropriate when comparing values across different categories.
• A line chart is suitable for showing trends over time.
• A chart title should accurately describe the data being presented.
• Data visualization can help in decision-making by identifying sales trends, and comparing performance across different regions.
• Considerations when choosing a chart type include the type of data, purpose of the visualization, audience, and number of variables.

Additional Questions

• Data Analysis involves collecting, transforming, and organizing data to draw conclusions.
• To copy the cell content or extend a series, use the fill handle (the small square at the bottom-right of a selected cell).

Excel Sheet Analysis

• A formula that calculates the total sales before discount =SUM(J2:J27)
• A formula that calculates the total sales after discount considering shipping cost.
• Calculate the average of sales =AVERAGE(J2:J27)
• There are two hidden columns, are they L, N
• What is the highest value of sales? =MAX(J2:J27)
• What is the lowest value of sales? =MIN(J2:J27)
• How many orders of "large" order type? =COUNTIF(H2:H27,"Large")
• Calculate the sum of all the shipping cost if "express air" =SUMIF(O2:O27,"express air",P2:P27)
• Column 'I" represents a lookup value formula from the table array exists in the 1st two columns, is VLOOKUP(G3,$A$2:$B$12,2,TRUE)
• To protect a book means that you are not able to add a new sheet to the book or delete and modify an existing sheet.
• To protect a sheet means that no modifications are allowed to the content of the protected sheet
• A pivot table is used to summarize and organize data.
• The pie chart helps visualize the results of a survey
• To display only the rows that meet specific criteria you need to filter data
• The Line Chart is a useful way for a shop owner to explore his sales among the last 3 year
• The bar and pie charts are used in representing categorical nominal data
• The Scatter plot is the way to represent the relation between x and y on a graph if I have two numerical variables X and Y that represent the following equation: x= 0.5y+3
• Give an example of how data science can benefit businesses by producing new insights and help in future predictions
• Business Analytics focuses on solving business problems using data analysis.
• Two tools commonly used for data analysis, SQL, Python, Excel, power BI
• Categorical ordinal data can be ordered, like sizes of a sandwich (S, M, L)
• a national ID number is considered nominal