TIPS FOR SURVEY DATA ANALYSIS

1. Presented By: Dr. Michael Kaylen University of Missouri TIPS FOR SURVEY DATA ANALYSIS

2. Survey Data analysis involves transforming survey data into information. Data Information Introduction

3. DATA

4. Number of Travelers in MO • by State of Origin and Month. INFORMATION

5. Data Information • Tips Focus On • Excel Pivot Tables • Weighted Data • Application to Household Panel Data INTRODUCTION

6. HOUSEHOLD PANEL DATA • Monthly Surveys of Households • 3 Levels of Data • Household (demographics) • Trip (# traveling, states visited, etc.) • State (# nights by lodging type, expenditures, etc.) • Simulated Data

7. HOUSEHOLD PANEL DATA • Household Level Data (HOUSE! - 54,824 Observations) • Household ID • Month • # Trips • Origin State • Household Income Range • Two Weights

8. HOUSEHOLD PANEL DATA • Household Level Data • Trip Level Data (TRIP! - 21,144 Observations) • Household Level Data • # Household Members on Trip • Primary Trip Purpose • Primary Transportation Mode • (0/1) Code for Each State • Three Weights

9. HOUSEHOLD PANEL DATA • Household Level Data • Trip Level Data • State Level Data (STATE! - 23,225 Observations) • Household and Trip Level Data • Detailed State • # Nights by Lodging Type • Expenditures by Category • (0/1) Code for Activities • Three Weights

10. EXCEL PIVOT TABLES • Analyze Data Using 3 operations: • Group Data into Categories • Ex. - Create a PivotTable

11. Put cursor anywhere in data table, worksheet house. • Click on Insert Tab

12. Click on Pivot Table Icon

13. CLICK OK

14. To Group: Drag Fields to • Row/Column Labels

16. Cross-tab using both Row • and Column Labels

18. EXCEL PIVOT TABLES • Analyze Data Using 3 Operations: • Group Data into Categories • Summarize Data Using Calculations • Count, Sum, Average, Maximum, Minimum, Standard Deviation • Ex.- Look at number of households in sample, by state of origin and month.

20. Change the type of calculation by clicking on the drop-down menu

21. Click on “Value Field Settings”

22. Click on Count, then OK

24. EXCEL PIVOT TABLES Analyze Data Using 3 Operations: Group Data into Categories Summarize Data Using Calculations Filter Results Can be used to view a subset of results

25. WEIGHTED DATA • Weights are used to Project Sample Data to a Population • Ex. – A Household Weight of 10,000 means that particular household “represents” 10,000 households in the population

26. WEIGHTED DATA • The Design Weight of a sample element is the inverse of its inclusion probability • Ex. – If 20,000 households are chosen from a simple random sampling design from 100,000,000 households, the design weight is 100,000,000/20,000 = 5,000

27. WEIGHTED DATA • Calibration Weights - computed using Data on auxiliary variables (e.g., demographics) • “Balance” sample data. • Ex. – If studying travel to MO and sample under-represents neighboring states.

28. WEIGHTED DATA CALCULATIONS WITH WEIGHTS • To estimate population totals: Ex. – To estimate the total number of household trips, create a new variable: WT_HH * HH_Trips

30. PivotTable: Estimated Number of Household Trips, by Month

31. WEIGHTED DATA CALCULATIONS WITH WEIGHTS • To estimate population totals: • To estimate population averages:

32. PivotTable: Including Sum of Household Weights, by Month

33. Calculation of Avg. Number of Trips per Household

34. POTPOURRI • Monitor number of observations, by strata (e.g., month, state). • - Sample size is critical to accuracy. • Monitor sum of weights over all observations, by strata. • - Weight totals should reflect population numbers.

35. POTPOURRI • Be careful projecting to other than the sample design population. Ex. 1 – Sampled Households, but interested in Household Trips (e.g., What percent of all household trips included travel in MO?).

36. POTPOURRI - Already used data in HOUSE! to estimate 138,511,079 household trips taken during 3 months. - TRIP! contains detailed data on trips, each row (observation) corresponding to one trip. - Problem: household weights over all trips in TRIP! sum to only 124,116,209

37. POTPOURRI Why the discrepancy? Sampled households could only provide details for up to 3 trips, regardless of the number of trips actually taken. Solution: create a new weight WT_HHTrip =

38. Calculation of WT_HHTrip

39. PivotTable showing Sum of WT_HHTrip, grouped by TR_VisitMO About 2.9% of all HH trips included MO.

40. POTPOURRI • Be careful projecting to other than the sample design population. Ex. 1 – Sampled Households, but interested in Household Trips. Ex. 2 – Sampled Households, but interested in Travelers (e.g., What percent of all travelers visited MO?).

41. POTPOURRI - The original data set contains two numbers of potential interest for each detailed trip: the number of people in the travel party and the number of household members in the travel party. - Problem: which numbers to use?

42. POTPOURRI Solution: Since the sampling design was based on households, not travel parties, use the number of household members in the travel party. WT_PersTrip = WT_HHTrip * TR_HHMemTot

43. PivotTable showing Sum of WT_PersTrip, grouped by TR_VisitMO About 2.9% of all travelers visited MO.

44. Questions, Comments? Thank You!