{
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "# Cleaning-operations\n",
        "\n",
        "Author: Dr. Hickman"
      ],
      "id": "f712d262-05f7-4b26-9071-5deee9174036"
    },
    {
      "cell_type": "code",
      "execution_count": 8,
      "metadata": {
        "vscode": {
          "languageId": "r"
        }
      },
      "outputs": [],
      "source": [
        "# knitr::opts_chunk$set(echo = TRUE)"
      ],
      "id": "cell-2"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Gapminder example"
      ],
      "id": "8c8cba1a-901c-4d44-b127-56db12a0873f"
    },
    {
      "cell_type": "code",
      "execution_count": 9,
      "metadata": {
        "vscode": {
          "languageId": "r"
        }
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "[1] \"----ORIGINAL----\""
          ]
        },
        {
          "output_type": "stream",
          "name": "stderr",
          "text": [
            "Rows: 1704 Columns: 6\n",
            "-- Column specification --------------------------------------------------------\n",
            "Delimiter: \",\"\n",
            "chr (2): country, continent\n",
            "dbl (4): year, lifeExp, pop, gdpPercap\n",
            "\n",
            "i Use `spec()` to retrieve the full column specification for this data.\n",
            "i Specify the column types or set `show_col_types = FALSE` to quiet this message."
          ]
        },
        {
          "output_type": "display_data",
          "metadata": {},
          "data": {}
        }
      ],
      "source": [
        "library('tidyverse')\n",
        "\n",
        "print(\"----ORIGINAL----\")\n",
        "gap <- read_csv('data/gapminder.csv')\n",
        "head(gap)"
      ],
      "id": "cell-4"
    },
    {
      "cell_type": "code",
      "execution_count": 10,
      "metadata": {
        "vscode": {
          "languageId": "r"
        }
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "[1] \"----GROUP BY CONTINENT----\""
          ]
        },
        {
          "output_type": "display_data",
          "metadata": {},
          "data": {}
        },
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "[1] \"----AVERAGE LIFE EXPECTANCY BY CONTINENT----\""
          ]
        },
        {
          "output_type": "display_data",
          "metadata": {},
          "data": {}
        },
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "[1] \"----AVERAGE LIFE EXPECTANCY BY COUNTRY----\""
          ]
        },
        {
          "output_type": "display_data",
          "metadata": {},
          "data": {}
        },
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "[1] \"----AVERAGE LIFE EXPECTANCY BY CONTINENT AND YEAR----\""
          ]
        },
        {
          "output_type": "stream",
          "name": "stderr",
          "text": [
            "`summarise()` has grouped output by 'continent'. You can override using the\n",
            "`.groups` argument."
          ]
        },
        {
          "output_type": "display_data",
          "metadata": {},
          "data": {}
        },
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "[1] \"----AVERAGE+MEDIAN EXPECTANCY BY YEAR----\""
          ]
        },
        {
          "output_type": "display_data",
          "metadata": {},
          "data": {}
        }
      ],
      "source": [
        "print(\"----GROUP BY CONTINENT----\")\n",
        "gap %>% group_by(continent)\n",
        "\n",
        "print(\"----AVERAGE LIFE EXPECTANCY BY CONTINENT----\")\n",
        "gap %>% group_by(continent) %>% summarize(avgLifeExp = mean(lifeExp))\n",
        "\n",
        "\n",
        "print(\"----AVERAGE LIFE EXPECTANCY BY COUNTRY----\")\n",
        "gap %>% group_by(country) %>% summarize(avgLifeExp = mean(lifeExp))\n",
        "\n",
        "print(\"----AVERAGE LIFE EXPECTANCY BY CONTINENT AND YEAR----\")\n",
        "gap %>% group_by(continent, year) %>% summarize(mean(lifeExp))\n",
        "\n",
        "print(\"----AVERAGE+MEDIAN EXPECTANCY BY YEAR----\")\n",
        "gap %>% group_by(year) %>% summarize(means = mean(lifeExp), medians = median(lifeExp))"
      ],
      "id": "cell-5"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## JOINING"
      ],
      "id": "7115da65-0f6b-4f52-becf-75c7a2fff7db"
    },
    {
      "cell_type": "code",
      "execution_count": 11,
      "metadata": {
        "vscode": {
          "languageId": "r"
        }
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stderr",
          "text": [
            "Rows: 3 Columns: 3\n",
            "-- Column specification --------------------------------------------------------\n",
            "Delimiter: \",\"\n",
            "chr (3): x, y, w\n",
            "\n",
            "i Use `spec()` to retrieve the full column specification for this data.\n",
            "i Specify the column types or set `show_col_types = FALSE` to quiet this message.\n",
            "Rows: 4 Columns: 4\n",
            "-- Column specification --------------------------------------------------------\n",
            "Delimiter: \",\"\n",
            "chr (4): x, y, w, z\n",
            "\n",
            "i Use `spec()` to retrieve the full column specification for this data.\n",
            "i Specify the column types or set `show_col_types = FALSE` to quiet this message."
          ]
        },
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "[1] \"----ORIGINAL----\""
          ]
        },
        {
          "output_type": "display_data",
          "metadata": {},
          "data": {}
        },
        {
          "output_type": "display_data",
          "metadata": {},
          "data": {}
        },
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "[1] \"----INNER JOIN----\""
          ]
        },
        {
          "output_type": "stream",
          "name": "stderr",
          "text": [
            "Joining with `by = join_by(x, y, w)`"
          ]
        },
        {
          "output_type": "display_data",
          "metadata": {},
          "data": {}
        },
        {
          "output_type": "display_data",
          "metadata": {},
          "data": {}
        },
        {
          "output_type": "display_data",
          "metadata": {},
          "data": {}
        },
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "[1] \"----LEFT JOIN----\""
          ]
        },
        {
          "output_type": "stream",
          "name": "stderr",
          "text": [
            "Joining with `by = join_by(x, y, w)`"
          ]
        },
        {
          "output_type": "display_data",
          "metadata": {},
          "data": {}
        },
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "[1] \"----RIGHT JOIN----\""
          ]
        },
        {
          "output_type": "stream",
          "name": "stderr",
          "text": [
            "Joining with `by = join_by(x, y, w)`"
          ]
        },
        {
          "output_type": "display_data",
          "metadata": {},
          "data": {}
        },
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "[1] \"----FULL JOIN----\""
          ]
        },
        {
          "output_type": "stream",
          "name": "stderr",
          "text": [
            "Joining with `by = join_by(x, y, w)`"
          ]
        },
        {
          "output_type": "display_data",
          "metadata": {},
          "data": {}
        }
      ],
      "source": [
        "#LOAD DATA\n",
        "df1 <- read_csv('data/toy-1.csv')\n",
        "df2 <- read_csv('data/toy-2.csv')\n",
        "\n",
        "print(\"----ORIGINAL----\")\n",
        "head(df1)\n",
        "head(df2)\n",
        "\n",
        "print(\"----INNER JOIN----\")\n",
        "df1 %>% inner_join(df2) %>% head()\n",
        "df1 %>% inner_join(df2, by = c('x')) %>% head()\n",
        "df1 %>% inner_join(df2, by = c('x' , 'w')) %>% head()\n",
        "\n",
        "print(\"----LEFT JOIN----\")\n",
        "df1 %>% left_join(df2) %>% head()\n",
        "# df1 %>% left_join(df2,by = c('x' , 'y')) %>% head()\n",
        "\n",
        "print(\"----RIGHT JOIN----\")\n",
        "df1 %>% right_join(df2) %>% head()\n",
        "\n",
        "print(\"----FULL JOIN----\")\n",
        "df1 %>% full_join(df2) %>% head()"
      ],
      "id": "cell-7"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Mutate, select, filter, arrange, convert"
      ],
      "id": "f08fd046-a2e2-4e73-bd63-83b2ee59cb9f"
    },
    {
      "cell_type": "code",
      "execution_count": 12,
      "metadata": {
        "vscode": {
          "languageId": "r"
        }
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "[1] \"----READ----\""
          ]
        },
        {
          "output_type": "stream",
          "name": "stderr",
          "text": [
            "New names:\n",
            "* `` -> `...1`\n",
            "Rows: 234 Columns: 12\n",
            "-- Column specification --------------------------------------------------------\n",
            "Delimiter: \",\"\n",
            "chr (6): manufacturer, model, trans, drv, fl, class\n",
            "dbl (6): ...1, displ, year, cyl, cty, hwy\n",
            "\n",
            "i Use `spec()` to retrieve the full column specification for this data.\n",
            "i Specify the column types or set `show_col_types = FALSE` to quiet this message."
          ]
        },
        {
          "output_type": "display_data",
          "metadata": {},
          "data": {}
        },
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "[1] \"----MUTATE----\""
          ]
        },
        {
          "output_type": "display_data",
          "metadata": {},
          "data": {}
        },
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "[1] \"----MUTATE----\""
          ]
        },
        {
          "output_type": "display_data",
          "metadata": {},
          "data": {}
        },
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "[1] \"----SELECT----\""
          ]
        },
        {
          "output_type": "display_data",
          "metadata": {},
          "data": {}
        },
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "[1] \"----FILTER----\""
          ]
        },
        {
          "output_type": "display_data",
          "metadata": {},
          "data": {}
        },
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "[1] \"----ARRANGE----\""
          ]
        },
        {
          "output_type": "display_data",
          "metadata": {},
          "data": {}
        },
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "[1] \"----FILTER THEN ARRANGE----\""
          ]
        },
        {
          "output_type": "display_data",
          "metadata": {},
          "data": {}
        },
        {
          "output_type": "display_data",
          "metadata": {},
          "data": {}
        }
      ],
      "source": [
        "#NOTES:\n",
        "\n",
        "# %>% (similar to | in linux) is called the forward pipe operator in R.\n",
        "# It provides a mechanism \n",
        "# for chaining commands with a new forward-pipe operator, %>%. \n",
        "# This operator will forward a value, or the result of an expression,\n",
        "# into the next function call/expression.\n",
        "# It is defined by the package magrittr (CRAN) and is heavily used by dplyr (CRAN).\n",
        "\n",
        "\n",
        "#DEFINITIONS\n",
        "# MUTATE  = TRANSFORM A COLUMN WITH SOME FUNCTION\n",
        "# SELECT  = SELECT SOME COLUMNS IN THE DATA\n",
        "# FILTER  = KEEP ONLY ROWS THAT MEET SOME DATA CRITERION\n",
        "# ARRANGE = ORDER THE DATA FRAME BY VALUES OF A COLUMN(S)\n",
        "\n",
        "#CONVERSION\n",
        "# CONVERT THE CITY AND HIGHWAY FUEL \n",
        "# EFFICIENCY TO KM/L, AND\n",
        "# FIND AVERAGE FUEL EFFICIENCY\n",
        "# 1 mile=1.60934KM\n",
        "# 1 gallon=3.7854 liter\n",
        "\n",
        "\n",
        "#-----------------------------\n",
        "print(\"----READ----\")\n",
        "#-----------------------------\n",
        "mpg1 <- read_csv('data/mpg.csv')\n",
        "head(mpg1)\n",
        "\n",
        "#-----------------------------\n",
        "print(\"----MUTATE----\")\n",
        "#-----------------------------\n",
        "# OPTION-1: SEND MPG TO MUTATE\n",
        " mpg2 <- mutate(mpg1, cty = cty * 1.6/3.8,\n",
        "         hwy = hwy * 1.6/3.8, \n",
        "         avg = (cty + hwy)/2)\n",
        "head(mpg2); #print(mpg2['avg'])\n",
        "\n",
        "#-----------------------------\n",
        "print(\"----MUTATE----\")\n",
        "#-----------------------------\n",
        "#OPTION-2: PIPE MPG INTO MUTATE \n",
        "mpg1 <- mpg1 %>% mutate(cty = cty * 1.6/3.8,\n",
        "         hwy = hwy * 1.6/3.8, \n",
        "         avg = (cty + hwy)/2)\n",
        "head(mpg1)\n",
        "\n",
        "#-----------------------------\n",
        "print(\"----SELECT----\")\n",
        "#-----------------------------\n",
        "mpg2 <- mpg1 %>% select(year, manufacturer, avg)\n",
        "head(mpg2)\n",
        "\n",
        "#-----------------------------\n",
        "print(\"----FILTER----\")  \n",
        "#-----------------------------\n",
        "mpg2 <- mpg1 %>% filter(manufacturer=='audi')\n",
        "head(mpg2)\n",
        "\n",
        "#-----------------------------\n",
        "print(\"----ARRANGE----\")  \n",
        "#-----------------------------\n",
        "mpg2 <-mpg1 %>% arrange(-year)\n",
        "head(mpg2)\n",
        "\n",
        "#-----------------------------\n",
        "print(\"----FILTER THEN ARRANGE----\")  \n",
        "#-----------------------------\n",
        "#CHAIN TOGETHER \n",
        "mpg2 <-mpg1 %>% filter(manufacturer=='audi') %>% arrange(-year)  \n",
        "head(mpg2,3)\n",
        "\n",
        "#-----------------------------\n",
        "#CONVERT TO FACTOR \n",
        "#-----------------------------\n",
        "mpg2['model']=as.factor(mpg2$model)\n",
        "head(mpg2,3)"
      ],
      "id": "cell-9"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Melting and pivoting"
      ],
      "id": "11d39a9b-1510-4423-bf49-64f5ac5e3cff"
    },
    {
      "cell_type": "code",
      "execution_count": 13,
      "metadata": {
        "vscode": {
          "languageId": "r"
        }
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stderr",
          "text": [
            "Rows: 18 Columns: 11\n",
            "-- Column specification --------------------------------------------------------\n",
            "Delimiter: \",\"\n",
            "chr  (1): religion\n",
            "dbl (10): <$10k, $10-20k, $20-30k, $30-40k, $40-50k, $50-75k, $75-100k, $100...\n",
            "\n",
            "i Use `spec()` to retrieve the full column specification for this data.\n",
            "i Specify the column types or set `show_col_types = FALSE` to quiet this message."
          ]
        },
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "# A tibble: 18 x 11\n",
            "   religion      `<$10k` $10-2~1 $20-3~2 $30-4~3 $40-5~4 $50-7~5 $75-1~6 $100-~7\n",
            "   <chr>           <dbl>   <dbl>   <dbl>   <dbl>   <dbl>   <dbl>   <dbl>   <dbl>\n",
            " 1 \"Agnostic\"         27      34      60      81      76     137     122     109\n",
            " 2 \"Atheist\"          12      27      37      52      35      70      73      59\n",
            " 3 \"Buddhist\"         27      21      30      34      33      58      62      39\n",
            " 4 \"Catholic\"        418     617     732     670     638    1116     949     792\n",
            " 5 \"Don\\u2019t ~      15      14      15      11      10      35      21      17\n",
            " 6 \"Evangelical~     575     869    1064     982     881    1486     949     723\n",
            " 7 \"Hindu\"             1       9       7       9      11      34      47      48\n",
            " 8 \"Historicall~     228     244     236     238     197     223     131      81\n",
            " 9 \"Jehovah's W~      20      27      24      24      21      30      15      11\n",
            "10 \"Jewish\"           19      19      25      25      30      95      69      87\n",
            "11 \"Mainline Pr~     289     495     619     655     651    1107     939     753\n",
            "12 \"Mormon\"           29      40      48      51      56     112      85      49\n",
            "13 \"Muslim\"            6       7       9      10       9      23      16       8\n",
            "14 \"Orthodox\"         13      17      23      32      32      47      38      42\n",
            "15 \"Other Chris~       9       7      11      13      13      14      18      14\n",
            "16 \"Other Faith~      20      33      40      46      49      63      46      40\n",
            "17 \"Other World~       5       2       3       4       2       7       3       4\n",
            "18 \"Unaffiliate~     217     299     374     365     341     528     407     321\n",
            "# ... with 2 more variables: `>150k` <dbl>, `Don't know/refused` <dbl>, and\n",
            "#   abbreviated variable names 1: `$10-20k`, 2: `$20-30k`, 3: `$30-40k`,\n",
            "#   4: `$40-50k`, 5: `$50-75k`, 6: `$75-100k`, 7: `$100-150k`\n",
            "# A tibble: 180 x 3\n",
            "   religion income_groups      counts\n",
            "   <chr>    <chr>               <dbl>\n",
            " 1 Agnostic <$10k                  27\n",
            " 2 Agnostic $10-20k                34\n",
            " 3 Agnostic $20-30k                60\n",
            " 4 Agnostic $30-40k                81\n",
            " 5 Agnostic $40-50k                76\n",
            " 6 Agnostic $50-75k               137\n",
            " 7 Agnostic $75-100k              122\n",
            " 8 Agnostic $100-150k             109\n",
            " 9 Agnostic >150k                  84\n",
            "10 Agnostic Don't know/refused     96\n",
            "# ... with 170 more rows"
          ]
        },
        {
          "output_type": "display_data",
          "metadata": {},
          "data": {}
        }
      ],
      "source": [
        "pew <- read_csv('data/pew.csv')\n",
        "print(pew)\n",
        "\n",
        "# WE NEED TO MELT THIS DATASET TO MAKE IT TIDY\n",
        "pew1 <- pew %>% pivot_longer(cols = c(-religion), names_to = 'income_groups', values_to = 'counts')\n",
        "print(pew1)\n",
        "\n",
        "# There is a difference in row-order between the R and Python solutions.\n",
        "# R sorts by the id variable(s), Python sorts on the column containing\n",
        "# the old column headers. Analytically, this can be fixed, and is often not important for visualization.\n",
        "\n",
        "\n",
        "#WE REVERSE THIS PROCESS BY PIVOTING THE DATA SET\n",
        "pew2 <- pew1 %>% pivot_wider(id_cols = c(religion), names_from = 'income_groups', values_from = 'counts')\n",
        "head(pew2,2)"
      ],
      "id": "cell-11"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Splitting"
      ],
      "id": "2a08bde8-f055-4bb4-8ae4-5b77797c0970"
    },
    {
      "cell_type": "code",
      "execution_count": 14,
      "metadata": {
        "vscode": {
          "languageId": "r"
        }
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stderr",
          "text": [
            "Rows: 3000 Columns: 16\n",
            "-- Column specification --------------------------------------------------------\n",
            "Delimiter: \",\"\n",
            "chr  (1): iso2\n",
            "dbl (15): year, m014, m1524, m2534, m3544, m4554, m5564, m65, f014, f1524, f...\n",
            "\n",
            "i Use `spec()` to retrieve the full column specification for this data.\n",
            "i Specify the column types or set `show_col_types = FALSE` to quiet this message."
          ]
        },
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "[1] \"ORIGINAL\""
          ]
        },
        {
          "output_type": "display_data",
          "metadata": {},
          "data": {}
        },
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "[1] \"AFTER MELT\""
          ]
        },
        {
          "output_type": "display_data",
          "metadata": {},
          "data": {}
        },
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "[1] \"AFTER SPLIT \"\n",
            "# A tibble: 3 x 5\n",
            "  iso2   year gender age   counts\n",
            "  <chr> <dbl> <chr>  <chr>  <dbl>\n",
            "1 AF     2000 m      014       52\n",
            "2 AF     2000 m      1524     228\n",
            "3 AF     2000 m      2534     183"
          ]
        }
      ],
      "source": [
        "#READ\n",
        "tb <- read_csv('data/tb.csv')\n",
        "print(\"ORIGINAL\")\n",
        "head(tb, 3)\n",
        "\n",
        "#MELT\n",
        "tb1 <- tb %>% pivot_longer(cols=m014:f65, names_to='demo', values_to='counts')\n",
        "print(\"AFTER MELT\")\n",
        "head(tb1, 3)\n",
        "\n",
        "#SPLIT\n",
        "tb2 <- tb1 %>% separate(demo, c('gender','age'), sep = 1)\n",
        "print(\"AFTER SPLIT \")\n",
        "print(head(tb2, 3))\n",
        "\n",
        "# TB <-  read_csv('data/tb1.csv') %>% \n",
        "#   pivot_longer(cols = starts_with('new'),names_to='demo', values_to='counts')\n",
        "# head(TB, 3)\n",
        "\n",
        "# TB1 <- TB %>% extract(demo, c('type','gender','age'),  regex = 'new[_]*([a-z]+)_([mf])([0-9]+$)')\n",
        "# head(TB1)"
      ],
      "id": "cell-13"
    }
  ],
  "nbformat": 4,
  "nbformat_minor": 5,
  "metadata": {
    "kernelspec": {
      "name": "ir",
      "display_name": "R",
      "language": "R"
    },
    "language_info": {
      "name": "R",
      "codemirror_mode": "r",
      "file_extension": ".r",
      "mimetype": "text/x-r-source",
      "pygments_lexer": "r",
      "version": "4.2.2"
    }
  }
}