21. Data Analytics - Analyze Data to Answer Questions - Week 2

Learning how to format data with spreadsheet features and to get support during analysis.

 

Definition:

Text string // group of characters within a cell, most often composed of letters.

Openness (open data) // free access, usage, and sharing of data

Mental model // though process and the way you approach a problem

R // programming language frequently used for statistical analysis, visualization, and other data analysis


What tool to use?

        - Small data -> Spreadsheet

        - Lots of data -> SQL

        - More time debugging queries than analyzing data -> R


Best practices for searching online

        - Thinking skills

        - Data analytics terms

        - Basic knowledge of tools

        - Use the right terms


Incorrectly formatted data can:

        - Lead to mistakes

        - Take time to fix

        - Affect stakeholder's decision-making


Strings in Spreadsheet:

        - LEN(cell) // finds length of the string

        - LEFT(cell,length of left) // returns string left side of string

        - RIGHT(cell,length of right) // returns string right side of string

        - FIND("specificstring", cell) // returns the position of where specific string starts


Spreadsheet Data Validation Feature:

        - Controls what can and can't be entered into a worksheet

        Data Validation:

                - Add dropdown lists with predetermined options

                        - Available in Google Sheets > Data > Data Validation > List of Item Critera

                - Creating custom checkboxes

                        - Data validation menu, choose Checkbox.

                - Protect structured data and formulas

                        - Data validation menu, Reject input


Spreadsheet Conditional Formatting Feature:

        - Spreadsheet tool that changes how cells appear when values meet specific conditions.

        Conditional Formatting:

                - Format menu > conditional formatting > Format rules and style


Spreadsheet Format Tools:

        GOOGLE SHEETS

        - Format menu

        - CONVERT() formula

        EXCEL

        - DATEVALUE() Formula, turns date string to date format

        - VALUE() Formula, turns text string to date format

        - SUBSTITUTE() Formula, substitute or remove delimiters

        - CONCATENATE() Formula, merge/combine two cell datas


SQL Formatting:

        - CAST(COLUMN AS TYPE)

        - SAFE_CAST // used if you want to return a null if CAST is unsuccessful

Starting with

CAST function can convert to:

Numeric (number)

- Integer - Numeric (number) - Big number - Floating integer - String

String

- Boolean - Integer - Numeric (number) - Big number - Floating integer - String - Bytes - Date - Date time - Time - Timestamp

Date

- String - Date - Date time - Timestamp


SQL Concat:



SQL String Manipulation:

Function

Usage

Example

CONCAT

A function that adds strings together to create new text strings that can be used as unique keys

CONCAT (‘Google’, ‘.com’);

CONCAT_WS

A function that adds two or more strings together with a separator

CONCAT_WS (‘ . ’, ‘www’, ‘google’, ‘com’) *The separator (being the period) gets input before and after Google when you run the SQL function

CONCAT with +

Adds two or more strings together using the + operator

‘Google’ + ‘.com’


Additional Resources:

https://support.google.com/docs/answer/6055540?hl=en

https://www.ablebits.com/office-addins-blog/2015/03/26/excel-convert-text-date/#:~:text=Excel%20DATEVALUE%20function%20%2D%20change%20text,Excel%20recognizes%20as%20a%20date.&text=So%2C%20the%20formula%20to%20convert,stored%20as%20a%20text%20string.

https://www.ablebits.com/office-addins-blog/2018/07/18/excel-convert-text-to-number/

https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver15

https://dev.mysql.com/doc/refman/8.0/en/cast-functions.html

https://www.blendo.co/blog/how-to-sql-type-casting/

https://www.w3schools.com/sql/sql_ref_sqlserver.asp

https://www.w3schools.com/sql/sql_ref_keywords.asp


Excel Tips:

https://support.microsoft.com/en-us/office/keyboard-shortcuts-in-excel-1798d9d5-842a-42b8-9c99-9b7213f0040f?ui=en-US&rs=en-US&ad=US

https://exceljet.net/keyboard-shortcuts

https://exceljet.net/excel-functions

https://exceljet.net/formulas

https://learntocodewith.me/posts/excel-skills/

https://www.slideshare.net/markjhonoxillo/advanced-spreadsheet-skills

Comments

Post a Comment

Popular posts from this blog

2. FreeCodeCamp - Dynamic Programming - Learn to Solve Algorithmic Problems & Coding Challenges

Dynamic Programming - Parts      PART 1 - Memoization     P ART 2 - Tabulation Dynamic Programming with Fibonacci Example Recursion: Slow because time complexity is O(2^n) func fibR (x int ) int {     if x <= 2 {         return 1     }     return fibR (x- 1 ) + fibR (x- 2 ) } Dynamic Way Example Recursion with Stored Value:  // Fast because it removes duplicate processes by checking map. // Tail recursion-ish // Results are hitchhiking each recursive call func fibR2 (x int , y map [ int ] int ) int {     if _ , ok := y[x]; ok {         return y[x]     }     if x <= 2 {         return 1     }     y[x] = fibR2 (x- 1 , y) + fibR2 (x- 2 , y)     return y[x] }
Read more

20. Data Analytics - Analyze Data to Answer Questions - Week 1

Goal of analysis is to identify trends and relationships within data so you can accurately answer the questions you're asking. Most data is organized in tables, always have data in the right format. Definition : Analysis // process used to make sense of the data collected Sorting // involves arranging data into a meaningful order to make it easier to understand, analyze, visualize Outliers // data points that are very different from similarly collected data and might not be reliable values Filtering // extracting specific data that meet certain criteria while hiding the rest Customized sort order // when you sort data in a spreadsheet using multiple conditions The 4 phases of analysis:          1. Organize data // getting the right data in the right size and order                     - Sorting : arrange data in meaningful order                     - Filtering : can help find errors or outliers          2. Format and adjust data // filtering or sorting or compiling data in a better
Read more

3. Algorithms - Selection Sort

Selection sort sorts an array by finding the lowest value element and appending it to an empty array. The algorithm will shrink the unsorted array and repeat the process recursively until all unsorted elements are used up. package main func selectionSort (list *[] int ) {     sorted := make ([] int , 0 , len (*list))     unSorted := *list     for i := 0 ; i < len (*list); i++ {         min := unSorted[ 0 ]         index := 0         for i , e := range unSorted {             if e <= min {                 min = e                 index = i             }         }         sorted = append (sorted, (unSorted)[index])         if index == 0 {             unSorted = unSorted[ 1 :]         } else if index == ( len (*list) - 1 ) {             unSorted = unSorted[:index]         } else {             unSorted = append (unSorted[ 0 :index], unSorted[index+ 1 :]...)         }     }     * list = sorted } /*func main() {     x := []int{4, 6, 3, 2, 9, 7, 3, 5}     fmt.Print
Read more