BIO 2V9R - Analyzing HPV Survey Data & Mining Twitter Content

• Part 1: Cleaning Data (~20 minutes)
  • Power Query Editor (PQE)
    • PQE is Microsoft's data cleaning and transformation tool. We will use PQE to process the Likert scale text to ordinal values and split the Activities that can spread HPV.
    • Functions in the PQE that we will use:
      • Process Likert Columns
        • Add an Index column
        • Unpivot all Likert columns
        • Rename Attribute to Likert Questions and rename Values to Likert Values
        • Trim Likert Values
        • Enter Data and paste table from below
        • Rename pasted table to Ordinal
        • Trim the Text column of Ordinal table
        • Merge Ordinal to Table1
        • Select only the Ordinal column
        • Add Conditional Column named Intensity (ordinal greater then 3 = high, greater than 0 = low, else other)
      • Process Activities that can spread HPV Column
        • Split column by delimeter
        • Select the split columns and unpivot
        • Rename Values to Activities that can spread
        • Trim Activities that can spread
  • Close & Load, done with PQE
• Part 2: Compare & Measure Proportions (~10 minutes)
  • Proportions with Slicers
    • Table1 - Insert Pivot Chart (be sure to click add this to the Data Model)
    • Drag Index to Values and adjust to Distinct Count
    • Drag Ordinal to Axis
    • Right-click Likert Questions and Add As Slicer
    • Probably will want to adjust placement and sizes of slicer and chart
    • Select a question in the Slicer to adjust the chart (we worked hard for this, hopefully it was worth the work?)
    • Right-click Activities that can spread and Add as Slicer
    • To see the Intensity instead of ordinal values, replace the Axis (categories) with Intensity
  • Analyzing Various Groups
    • Drag to Legend (e.g., race or gender columns)
• Part 3: Scatter Plot (~10 minutes)
  • Create a Pivot Table from Table1 (be sure to click add this to the Data Model)
    • Likert Questions to columns
    • Ordinal to rows
    • Index as Distinct Coount to values
  • Create Scatter Plot using first two columns
  • Create slicer from Likert Questions to filter for two columns for scatter
  • Choose Quick Layout 9 for trend line, trend line formula, and r-squared
• Part 4: Additional Statistical Measurements
  •  

We will use a dictionary table to code the original text values to the assigned ordinal values.