FOUNDATION OF ANALYTICS 2 ANGELIQUE ZERINGUE & SHANTI GREENE DATABASE SYSTEMS AND SQL RELATIONAL DATABASES Data is related Table structure Logical and physical structure are separate DATA MODEL / ER DIAGRAM JOINS SQL Structured query language Send commands to a relational database Some keywords: SELECT, FROM, JOIN, WHERE EXAMPLE SQL – OVERDUE CUSTOMERS SELECT c.email FROM customer c JOIN rental r ON c.customer_id = r.customer_id JOIN inventory I ON r.inventory_id = i.inventory_id JOIN film f ON i.film_id = f.film_id WHERE r.return_date IS NULL AND EXTRACT (DAY FROM CURRENT_DATE – r.rental_date) > f.rental_duration NOSQL DATA STORES Not Only SQL Non-relational Schemaless Elastic Key Value Stores (often in-memory) Document Columnar (wide column) Graph CAP THEOREM A distributed data store cannot simultaneously offer more than two of three established guarantees: Consistency:The data within the database remains consistent, even after an operation has been executed. For instance, after updating a system, all clients will see the same data. Availability: The system is constantly on (always available), with no downtime. Partition Tolerance: Even if communication among the servers is no longer reliable, the system will continue to function. This is because the servers can be partitioned off, into multiple groups which can’t communicate with each other. STRUCTURED DATA FORMATS JSON Delimited CSV Tab XLSX OTHER DATA FORMATS AND SOURCES APIs Serialization Streams Lakes DATA MANIPULATION IN PYTHON WITH PANDAS DATAFRAME Data organized into rows and columns Similar to a table Lives in memory 1D array is a series Easy to read in data from files (read_csv) Can access elements directly with indices Defaults to a soft copy Supports grouping, pivoting DATA TYPES Categorical (Qualitative) Nominal Ordinal Numerical (Quantitative) Discrete Continuous Interval Ratio DATA TYPES 15 TRANSACTIONAL INFORMATION Data that is collected from one or more exchanges When did it happen Recency How many times has it happened Frequency What was it worth Value What category(ies) were involved Variety DESCRIBING WITH DATA Demographics Examples: Age, income, race, sex, number of children Can describe a person, group, or area (geodemographics) People with similar demographics purchase more similarly than people with dissimilar demographics Psychographics Examples: golfer, audiophile, listens to classic rock, watches Luther, donates to AMA Describes a persons hobbies, attitudes, or values People with similar psychographics purchase more similarly than people with dissimilar psychographics DESCRIBING WITH DATA (PART 2) Exographics Examples: avg. commute time in MSA, no. golf courses in County, avg. annual snowfall Describes factors outside the level of the person External factors may be a strong influence in purchase decisions Fluxographics Examples: recently moved, recently had a baby, recently graduated college Describes life changes Life changes often precipitate purchases DESCRIPTIVE STATISTICS DESCRIPTIVE STATISTICS Range (min, max) Averages (mean, median, mode) Variances Counts and Sums Histograms DESCRIPTIVE EXAMPLE 1: TRANSACTION AMOUNT metric value Min transaction amount $1 Max transaction amount $820 Sum transaction amount $2,731,061 Count of transactons 100,000 Median transaction amount $20 Mean transaction amount $27.31 Mode transaction amount $9 Variance transaction amount $716.26 VISUALIZING DATA IN PYTHON GRAPHICAL EXCELLENCE Is the well-designed presentation of interesting data – a matter of substance, of statistics, and of design. Consists of complex ideas communicated with clarity, precision and efficiency. Gives to the viewer the greatest number of ideas in the shortest time with the least ink in the smallest space. Is nearly always multivariate. Requires telling the truth about the data. GRAPHICAL INTEGRITY PRINCIPLES Representations of numbers should match their true proportions. Labeling should be clear, detailed and thorough. Designs should show only data variations, not design variation. Standardized units are best when representing money. The number of dimensions visualized should equal the number of dimensions in the data. Representations should not imply a subjective or false context. SEABORN EXAMPLE sns.histplot(data=filmCategoryDf, x=’rental_duration’, stat=’proportion’, binwidth=1, kde=True, color=’Blue’) THE GOAL DICTATES THE APPROACH THE PROCESS OF MODEL BUILDING 26 STEPS IN MODEL BUILDING 27 1 Identify & clarify the problem you are solving 2 Learn background information about the problem 3 Select variables 4 Acquire data 5 Choose a statistical modeling approach 6 Conduct exploratory data analysis / assumptions testing 7 Fit the model 8 Conduct model diagnostics 9 Address an
y deficiencies in the model 10 Interpret and communicate how the results address the problem ITERATIVE PROCESS 28 Inputs Subject matter theories Model Data Statistical techniques Auxiliary assumptions Calculate / Compute Outputs Parameter estimates Confidence regions Test statistics Graphical displays Diagnose, validate, & criticize STEP 1. IDENTIFY THE PROBLEM Most people are terrible at specifying the problem. Before you build a model, ask them the important questions What do you want to know What is the goal What do you want to do with that information What background information is needed to understand the problem Is the problem one you can realistically address 29 WHAT IS THE GOAL 30 Description Describe key features of data Centrality, spread, distribution, etc Describe relationships in data 01 Inference Test hypotheses / causal theories Explain relationships in the data 02 Prediction Make informed guesses about future data 03 IS THE GOAL REALISTIC 31 Is what the client requested consistent with their stated goals Does it address the problem they described What are the available resources to work on this problem Can the problem be solved in the time frame allotted Does the data already exist or will it have be collected THIS REALLY HAPPENS.. The client: “I need some operational analytics.” Data scientist: “What do mean ” The client: “I just need some metrics.” Data scientist: “What kind of metrics ” The client: “You’re the data scientist. You’re supposed to tell me.” A real conversation: What to do: Ask lots of questions Explain why you are asking those questions Listen carefully Take lots of notes 32 STEP 2. GET THE BACKGROUND INFO 33 You probably don’t The client knows a lot about the problem Ask them for resources to learn more Understand the main clinical issues Identify known relationships between data elements Identify variables you need to include in the model that the client forgot to tell you about Learn about the quirks and limitations of using certain measures Find out the standard approaches people use with that type of data Do your own background research to.. This can save you a lot of pain STEP 3. SELECT VARIABLES 34 If you have several different variables that measure approximately the same thing, which do you choose Do you keep the variables as-is or will you have to modify them As they explain the problem & the background information, listen for clues about What is the outcome What is the distribution of the outcome What is the main relationship they are interested in examining What other things will impact the outcome and have to be taken into account EXAMPLE 35 “We .. um .. started a program to better detect sepsis. We want to know if the program worked and we have reduced instances of septic shock. We think we think patients with sepsis will also have reduced length of stay in the hospital, if we catch this earlier. Sepsis is an infection in the blood. Patients with compromised immunity are at greater risk, as are the elderly, IV drug users, and those on dialysis. As part of the program, we track people who come in with sepsis.” From this, can we identify the problem, some clinical background info, outcomes, other variables, & potential data sources “We .. um .. started a program to better detect sepsis. We want to know if the program worked and we have reduced instances of septic shock. We think we think patients with sepsis will also have reduced length of stay in the hospital, if we catch this earlier. Sepsis is an infection in the blood. Patients with compromised immunity are at greater risk, as are the elderly, IV drug users, and those on dialysis. As part of the program, we track people who come in with sepsis.” Two Outcomes: One continuous & one binary Main predictor: People in the program vs people not in the program (or before vs after) Other risk factors EXAMPLE 36 Are there existing sources of the data or will it have to be collected If it has to be collected, work with people who have research background to do this properly If it exits already, is the data in a single source or do data need to come from multiple sources STEP 4. ACQUIRE THE DATA 80-90% of time spend assembling and wrangling data 10-20% Doing analyses 37 NEXT STEPS 38 Model fitting is iterative, so expect to jump back and forth between different steps Step 5, choosing a model; Step 7, fitting the model; and Step 10, interpreting results, will covered throughout the course Step 6, exploratory data analysis, and Step 8, model diagnostics will be covered in a future module Step 9, addressing model deficiencies will be in a future module As part of this, we will loop back to step 3 and talk more about variable selection