Skip to content

Instantly share code, notes, and snippets.

@3catz

3catz/ixis_data_r.ipynb

Created February 2, 2022 00:04
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save 3catz/79e5dfac8fd54313e273ed6d349d96cc to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save 3catz/79e5dfac8fd54313e273ed6d349d96cc to your computer and use it in GitHub Desktop.
Download ZIP
IXIS_data_R.ipynb
Display the source blob
Display the rendered blob
Raw
ixis_data_r.ipynb
{
"nbformat": 4,
"nbformat_minor": 0,
"metadata": {
"colab": {
"name": "IXIS_data_R.ipynb",
"provenance": [],
"collapsed_sections": [],
"authorship_tag": "ABX9TyOul6X4UDelImkczJaFBC8t",
"include_colab_link": true
},
"kernelspec": {
"name": "ir",
"display_name": "R"
},
"language_info": {
"name": "R"
}
},
"cells": [
{
"cell_type": "markdown",
"metadata": {
"id": "view-in-github",
"colab_type": "text"
},
"source": [
"<a href=\"https://colab.research.google.com/gist/3catz/79e5dfac8fd54313e273ed6d349d96cc/ixis_data_r.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
]
},
{
"cell_type": "markdown",
"source": [
"# ***IXIS Data Analyst Exercises***\n"
],
"metadata": {
"id": "4FqIe6O3nazY"
}
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"id": "fNYQgd0zhGI6"
},
"outputs": [],
"source": [
"print(installed.packages())"
]
},
{
"cell_type": "markdown",
"source": [
"#Upload the two files to local (cloud) drive..."
],
"metadata": {
"id": "OcppYsBEnlWu"
}
},
{
"cell_type": "code",
"source": [
"#if using Google Colab you mount your drive this way \n",
"if (file.exists(\"/usr/local/lib/python3.6/dist-packages/google/colab/_ipython.py\")) {\n",
" install.packages(\"R.utils\")\n",
" library(\"R.utils\")\n",
" library(\"httr\")\n",
" my_check <- function() {return(TRUE)}\n",
" reassignInPackage(\"is_interactive\", pkgName = \"httr\", my_check) \n",
" options(rlang_interactive=TRUE)\n",
"}"
],
"metadata": {
"id": "UhOADktzhzlk"
},
"execution_count": null,
"outputs": []
},
{
"cell_type": "markdown",
"source": [
"# Imports"
],
"metadata": {
"id": "ICl3O054kArN"
}
},
{
"cell_type": "code",
"source": [
"install.packages(\"openxlsx\")\n",
"library('openxlsx')\n",
"library(\"dplyr\")\n",
"library(\"tidyr\")\n",
"library(\"lubridate\")"
],
"metadata": {
"id": "QJxhVkfrj6G8"
},
"execution_count": null,
"outputs": []
},
{
"cell_type": "markdown",
"source": [
"# Preprocessing: adding month name (abbreviated) as string"
],
"metadata": {
"id": "WRtg4Rk6kEAd"
}
},
{
"cell_type": "code",
"source": [
"df <- read.csv(\"DataAnalyst_Ecom_data_sessionCounts.csv\")\n",
"dtimes <- as.POSIXct(df$dim_date, format = \"%m/%d/%Y\", tz = \"UTC\")\n",
"df$month <- month(dtimes, label = TRUE)"
],
"metadata": {
"id": "tse-sZgIiI5f"
},
"execution_count": null,
"outputs": []
},
{
"cell_type": "code",
"source": [
"head(df)"
],
"metadata": {
"id": "NyGcf57-wK_m"
},
"execution_count": null,
"outputs": []
},
{
"cell_type": "markdown",
"source": [
"# Transaction Totals (Device * Month)"
],
"metadata": {
"id": "u7ztbAt32xpX"
}
},
{
"cell_type": "code",
"source": [
"transactions <- df %>% \n",
" group_by(month,dim_deviceCategory) %>% \n",
" summarize(transaction_totals = sum(transactions))"
],
"metadata": {
"colab": {
"base_uri": "https://localhost:8080/"
},
"id": "eK82msJI13qy",
"outputId": "907a3b10-975f-4187-9b30-6e896f4fb8bd"
},
"execution_count": null,
"outputs": [
{
"output_type": "stream",
"name": "stderr",
"text": [
"`summarise()` has grouped output by 'month'. You can override using the `.groups` argument.\n",
"\n"
]
}
]
},
{
"cell_type": "code",
"source": [
"head(transactions)"
],
"metadata": {
"id": "d2jA-REq3B2P"
},
"execution_count": null,
"outputs": []
},
{
"cell_type": "markdown",
"source": [
"# Session Totals (Device * Month)"
],
"metadata": {
"id": "yuJ7CQxW234-"
}
},
{
"cell_type": "code",
"source": [
"device_month <- df %>% \n",
" group_by(month,dim_deviceCategory) %>% \n",
" summarize(session_totals = sum(sessions),QTY_totals = sum(QTY), transaction_totals = sum(transactions))\n",
"\n",
"head(device_month,15)\n"
],
"metadata": {
"id": "ZkoavSWU277B"
},
"execution_count": null,
"outputs": []
},
{
"cell_type": "markdown",
"source": [
"## add ECR calculated measure into the mix "
],
"metadata": {
"id": "-ulKoDApkwlc"
}
},
{
"cell_type": "code",
"source": [
"device_month <- device_month %>% mutate(ECR = transaction_totals/session_totals)\n",
"head(device_month,10)"
],
"metadata": {
"id": "b4gaQBBN6kQJ"
},
"execution_count": null,
"outputs": []
},
{
"cell_type": "markdown",
"source": [
"# Second Sheet"
],
"metadata": {
"id": "6Ts1jFIp7DSB"
}
},
{
"cell_type": "markdown",
"source": [
"The last two months in the table are May and June of 2013.\n",
"Let's filter the data frame so we only have those 2 months."
],
"metadata": {
"id": "03ZfgmKb747D"
}
},
{
"cell_type": "code",
"source": [
"last2 <- filter(df, dtimes >= \"13-05-01\")\n",
"tail(last2, 5)"
],
"metadata": {
"colab": {
"base_uri": "https://localhost:8080/",
"height": 255
},
"id": "BaOIuekF9-UA",
"outputId": "63f74e65-9e6c-4ddf-8188-58376a58f1b5"
},
"execution_count": null,
"outputs": [
{
"output_type": "display_data",
"data": {
"text/plain": [
" dim_browser dim_deviceCategory dim_date sessions transactions QTY\n",
"1621 Internet Explorer tablet 6/30/13 6 0 0 \n",
"1622 error desktop 6/30/13 5 0 0 \n",
"1623 Edge mobile 6/30/13 4 0 0 \n",
"1624 SeaMonkey desktop 6/30/13 3 0 0 \n",
"1625 Puffin desktop 6/30/13 1 0 0 \n",
" month\n",
"1621 Jun \n",
"1622 Jun \n",
"1623 Jun \n",
"1624 Jun \n",
"1625 Jun "
],
"text/latex": "A data.frame: 5 × 7\n\\begin{tabular}{r|lllllll}\n & dim\\_browser & dim\\_deviceCategory & dim\\_date & sessions & transactions & QTY & month\\\\\n & <chr> & <chr> & <chr> & <int> & <int> & <int> & <ord>\\\\\n\\hline\n\t1621 & Internet Explorer & tablet & 6/30/13 & 6 & 0 & 0 & Jun\\\\\n\t1622 & error & desktop & 6/30/13 & 5 & 0 & 0 & Jun\\\\\n\t1623 & Edge & mobile & 6/30/13 & 4 & 0 & 0 & Jun\\\\\n\t1624 & SeaMonkey & desktop & 6/30/13 & 3 & 0 & 0 & Jun\\\\\n\t1625 & Puffin & desktop & 6/30/13 & 1 & 0 & 0 & Jun\\\\\n\\end{tabular}\n",
"text/markdown": "\nA data.frame: 5 × 7\n\n| <!--/--> | dim_browser &lt;chr&gt; | dim_deviceCategory &lt;chr&gt; | dim_date &lt;chr&gt; | sessions &lt;int&gt; | transactions &lt;int&gt; | QTY &lt;int&gt; | month &lt;ord&gt; |\n|---|---|---|---|---|---|---|---|\n| 1621 | Internet Explorer | tablet | 6/30/13 | 6 | 0 | 0 | Jun |\n| 1622 | error | desktop | 6/30/13 | 5 | 0 | 0 | Jun |\n| 1623 | Edge | mobile | 6/30/13 | 4 | 0 | 0 | Jun |\n| 1624 | SeaMonkey | desktop | 6/30/13 | 3 | 0 | 0 | Jun |\n| 1625 | Puffin | desktop | 6/30/13 | 1 | 0 | 0 | Jun |\n\n",
"text/html": [
"<table class=\"dataframe\">\n",
"<caption>A data.frame: 5 × 7</caption>\n",
"<thead>\n",
"\t<tr><th></th><th scope=col>dim_browser</th><th scope=col>dim_deviceCategory</th><th scope=col>dim_date</th><th scope=col>sessions</th><th scope=col>transactions</th><th scope=col>QTY</th><th scope=col>month</th></tr>\n",
"\t<tr><th></th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;int&gt;</th><th scope=col>&lt;int&gt;</th><th scope=col>&lt;int&gt;</th><th scope=col>&lt;ord&gt;</th></tr>\n",
"</thead>\n",
"<tbody>\n",
"\t<tr><th scope=row>1621</th><td>Internet Explorer</td><td>tablet </td><td>6/30/13</td><td>6</td><td>0</td><td>0</td><td>Jun</td></tr>\n",
"\t<tr><th scope=row>1622</th><td>error </td><td>desktop</td><td>6/30/13</td><td>5</td><td>0</td><td>0</td><td>Jun</td></tr>\n",
"\t<tr><th scope=row>1623</th><td>Edge </td><td>mobile </td><td>6/30/13</td><td>4</td><td>0</td><td>0</td><td>Jun</td></tr>\n",
"\t<tr><th scope=row>1624</th><td>SeaMonkey </td><td>desktop</td><td>6/30/13</td><td>3</td><td>0</td><td>0</td><td>Jun</td></tr>\n",
"\t<tr><th scope=row>1625</th><td>Puffin </td><td>desktop</td><td>6/30/13</td><td>1</td><td>0</td><td>0</td><td>Jun</td></tr>\n",
"</tbody>\n",
"</table>\n"
]
},
"metadata": {}
}
]
},
{
"cell_type": "markdown",
"source": [
"## Creating the Pivot Table we need "
],
"metadata": {
"id": "0Jm4HmZXMmrw"
}
},
{
"cell_type": "code",
"source": [
"last2b <- last2 %>% \n",
" group_by(dim_deviceCategory, month) %>% \n",
" summarize(transaction_totals = sum(transactions), session_totals = sum(sessions),QTY_totals = sum(QTY)) %>%\n",
" pivot_wider(names_from = \"month\", values_from = c(\"transaction_totals\",\"session_totals\",\"QTY_totals\"))\n",
"\n",
"head(last2b,15)"
],
"metadata": {
"colab": {
"base_uri": "https://localhost:8080/",
"height": 227
},
"id": "QV5yiNaPGRzP",
"outputId": "51956d75-aee0-443a-e3b3-c88ffd580160"
},
"execution_count": null,
"outputs": [
{
"output_type": "stream",
"name": "stderr",
"text": [
"`summarise()` has grouped output by 'dim_deviceCategory'. You can override using the `.groups` argument.\n",
"\n"
]
},
{
"output_type": "display_data",
"data": {
"text/plain": [
" dim_deviceCategory transaction_totals_May transaction_totals_Jun\n",
"1 desktop 18176 19370 \n",
"2 mobile 5413 7412 \n",
"3 tablet 4800 7756 \n",
" session_totals_May session_totals_Jun QTY_totals_May QTY_totals_Jun\n",
"1 526330 554940 33208 35146 \n",
"2 409796 526481 9790 13017 \n",
"3 228513 307413 8631 13728 "
],
"text/latex": "A grouped\\_df: 3 × 7\n\\begin{tabular}{lllllll}\n dim\\_deviceCategory & transaction\\_totals\\_May & transaction\\_totals\\_Jun & session\\_totals\\_May & session\\_totals\\_Jun & QTY\\_totals\\_May & QTY\\_totals\\_Jun\\\\\n <chr> & <int> & <int> & <int> & <int> & <int> & <int>\\\\\n\\hline\n\t desktop & 18176 & 19370 & 526330 & 554940 & 33208 & 35146\\\\\n\t mobile & 5413 & 7412 & 409796 & 526481 & 9790 & 13017\\\\\n\t tablet & 4800 & 7756 & 228513 & 307413 & 8631 & 13728\\\\\n\\end{tabular}\n",
"text/markdown": "\nA grouped_df: 3 × 7\n\n| dim_deviceCategory &lt;chr&gt; | transaction_totals_May &lt;int&gt; | transaction_totals_Jun &lt;int&gt; | session_totals_May &lt;int&gt; | session_totals_Jun &lt;int&gt; | QTY_totals_May &lt;int&gt; | QTY_totals_Jun &lt;int&gt; |\n|---|---|---|---|---|---|---|\n| desktop | 18176 | 19370 | 526330 | 554940 | 33208 | 35146 |\n| mobile | 5413 | 7412 | 409796 | 526481 | 9790 | 13017 |\n| tablet | 4800 | 7756 | 228513 | 307413 | 8631 | 13728 |\n\n",
"text/html": [
"<table class=\"dataframe\">\n",
"<caption>A grouped_df: 3 × 7</caption>\n",
"<thead>\n",
"\t<tr><th scope=col>dim_deviceCategory</th><th scope=col>transaction_totals_May</th><th scope=col>transaction_totals_Jun</th><th scope=col>session_totals_May</th><th scope=col>session_totals_Jun</th><th scope=col>QTY_totals_May</th><th scope=col>QTY_totals_Jun</th></tr>\n",
"\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;int&gt;</th><th scope=col>&lt;int&gt;</th><th scope=col>&lt;int&gt;</th><th scope=col>&lt;int&gt;</th><th scope=col>&lt;int&gt;</th><th scope=col>&lt;int&gt;</th></tr>\n",
"</thead>\n",
"<tbody>\n",
"\t<tr><td>desktop</td><td>18176</td><td>19370</td><td>526330</td><td>554940</td><td>33208</td><td>35146</td></tr>\n",
"\t<tr><td>mobile </td><td> 5413</td><td> 7412</td><td>409796</td><td>526481</td><td> 9790</td><td>13017</td></tr>\n",
"\t<tr><td>tablet </td><td> 4800</td><td> 7756</td><td>228513</td><td>307413</td><td> 8631</td><td>13728</td></tr>\n",
"</tbody>\n",
"</table>\n"
]
},
"metadata": {}
}
]
},
{
"cell_type": "markdown",
"source": [
"## alternative take "
],
"metadata": {
"id": "NV7XtnjrPzYw"
}
},
{
"cell_type": "markdown",
"source": [
"## adding the cart information "
],
"metadata": {
"id": "D6mSGn1tOCcw"
}
},
{
"cell_type": "code",
"source": [
"cart <-read.csv(\"DataAnalyst_Ecom_data_addsToCart.csv\")"
],
"metadata": {
"id": "cQpsmQtAOJ8a"
},
"execution_count": null,
"outputs": []
},
{
"cell_type": "code",
"source": [
"bob <- last2 %>% \n",
" group_by(month) %>% \n",
" summarize(transaction_totals = sum(transactions), session_totals = sum(sessions),QTY_totals = sum(QTY)) %>%\n",
" pivot_wider(names_from = \"month\", values_from = c(\"transaction_totals\",\"session_totals\",\"QTY_totals\"))\n",
"\n",
"bob$addCart_totals_May <- cart[11,3]\n",
"bob$addCart_totals_June <- cart[12,3]"
],
"metadata": {
"id": "ZdQ_Rm-pP1Mv"
},
"execution_count": null,
"outputs": []
},
{
"cell_type": "code",
"source": [
"bob"
],
"metadata": {
"colab": {
"base_uri": "https://localhost:8080/",
"height": 129
},
"id": "oGyDXeLFNV7O",
"outputId": "79b27a73-5990-42ac-fffe-5881d6caae9d"
},
"execution_count": null,
"outputs": [
{
"output_type": "display_data",
"data": {
"text/plain": [
" transaction_totals_May transaction_totals_Jun session_totals_May\n",
"1 28389 34538 1164639 \n",
" session_totals_Jun QTY_totals_May QTY_totals_Jun addCart_totals_May\n",
"1 1388834 51629 61891 136720 \n",
" addCart_totals_June\n",
"1 107970 "
],
"text/latex": "A tibble: 1 × 8\n\\begin{tabular}{llllllll}\n transaction\\_totals\\_May & transaction\\_totals\\_Jun & session\\_totals\\_May & session\\_totals\\_Jun & QTY\\_totals\\_May & QTY\\_totals\\_Jun & addCart\\_totals\\_May & addCart\\_totals\\_June\\\\\n <int> & <int> & <int> & <int> & <int> & <int> & <int> & <int>\\\\\n\\hline\n\t 28389 & 34538 & 1164639 & 1388834 & 51629 & 61891 & 136720 & 107970\\\\\n\\end{tabular}\n",
"text/markdown": "\nA tibble: 1 × 8\n\n| transaction_totals_May &lt;int&gt; | transaction_totals_Jun &lt;int&gt; | session_totals_May &lt;int&gt; | session_totals_Jun &lt;int&gt; | QTY_totals_May &lt;int&gt; | QTY_totals_Jun &lt;int&gt; | addCart_totals_May &lt;int&gt; | addCart_totals_June &lt;int&gt; |\n|---|---|---|---|---|---|---|---|\n| 28389 | 34538 | 1164639 | 1388834 | 51629 | 61891 | 136720 | 107970 |\n\n",
"text/html": [
"<table class=\"dataframe\">\n",
"<caption>A tibble: 1 × 8</caption>\n",
"<thead>\n",
"\t<tr><th scope=col>transaction_totals_May</th><th scope=col>transaction_totals_Jun</th><th scope=col>session_totals_May</th><th scope=col>session_totals_Jun</th><th scope=col>QTY_totals_May</th><th scope=col>QTY_totals_Jun</th><th scope=col>addCart_totals_May</th><th scope=col>addCart_totals_June</th></tr>\n",
"\t<tr><th scope=col>&lt;int&gt;</th><th scope=col>&lt;int&gt;</th><th scope=col>&lt;int&gt;</th><th scope=col>&lt;int&gt;</th><th scope=col>&lt;int&gt;</th><th scope=col>&lt;int&gt;</th><th scope=col>&lt;int&gt;</th><th scope=col>&lt;int&gt;</th></tr>\n",
"</thead>\n",
"<tbody>\n",
"\t<tr><td>28389</td><td>34538</td><td>1164639</td><td>1388834</td><td>51629</td><td>61891</td><td>136720</td><td>107970</td></tr>\n",
"</tbody>\n",
"</table>\n"
]
},
"metadata": {}
}
]
},
{
"cell_type": "markdown",
"source": [
"### adding ECR into this table "
],
"metadata": {
"id": "zRnJTqyZlrDL"
}
},
{
"cell_type": "code",
"source": [
"bob$ECR_total_May <- bob$transaction_totals_May/bob$session_totals_May\n",
"bob$ECR_total_Jun <- bob$transaction_totals_Jun/bob$session_totals_Jun"
],
"metadata": {
"id": "GEbpY_eNXIi9"
},
"execution_count": null,
"outputs": []
},
{
"cell_type": "markdown",
"source": [
"## Important: calculating absolute and relative change month to month "
],
"metadata": {
"id": "U-hpZF_6lw-h"
}
},
{
"cell_type": "code",
"source": [
"#bob$transaction_totals_Jun - bob$transaction_totals_May\n",
"bob$transaction_abs_change <- bob$transaction_totals_Jun - bob$transaction_totals_May\n",
"bob$transaction_rel_change <- bob$transaction_abs_change/bob$transaction_totals_May\n",
"bob$session_abs_change <- bob$session_totals_Jun - bob$session_totals_May\n",
"bob$session_rel_change <- bob$session_abs_change/bob$session_totals_May\n",
"bob$QTY_abs_change <- bob$QTY_totals_Jun - bob$QTY_totals_May\n",
"bob$QTY_rel_change <- bob$QTY_abs_change/bob$QTY_totals_May\n",
"\n",
"bob$addCart_abs_change <- bob$addCart_totals_June-bob$addCart_totals_May\n",
"bob$addCart_rel_change <- bob$addCart_abs_change/bob$addCart_totals_May\n",
"bob$ECR_abs_change <- bob$ECR_total_Jun - bob$ECR_total_May\n",
"bob$ECR_rel_change <- bob$ECR_abs_change / bob$ECR_total_May"
],
"metadata": {
"id": "_5hjFxM8QYzi"
},
"execution_count": null,
"outputs": []
},
{
"cell_type": "code",
"source": [
"bob"
],
"metadata": {
"colab": {
"base_uri": "https://localhost:8080/",
"height": 149
},
"id": "1mYwIlcZmUId",
"outputId": "816ff433-9e88-41dc-ab5d-7f7ba88687a5"
},
"execution_count": null,
"outputs": [
{
"output_type": "display_data",
"data": {
"text/plain": [
" transaction_totals_May transaction_totals_Jun session_totals_May\n",
"1 28389 34538 1164639 \n",
" session_totals_Jun QTY_totals_May QTY_totals_Jun addCart_totals_May\n",
"1 1388834 51629 61891 136720 \n",
" addCart_totals_June ECR_total_May ECR_total_Jun transaction_abs_change\n",
"1 107970 0.02437579 0.02486834 6149 \n",
" transaction_rel_change session_abs_change session_rel_change QTY_abs_change\n",
"1 0.216598 224195 0.1925017 10262 \n",
" QTY_rel_change addCart_abs_change addCart_rel_change ECR_abs_change\n",
"1 0.1987643 -28750 -0.2102838 0.0004925491 \n",
" ECR_rel_change\n",
"1 0.02020648 "
],
"text/latex": "A tibble: 1 × 20\n\\begin{tabular}{llllllllllllllllllll}\n transaction\\_totals\\_May & transaction\\_totals\\_Jun & session\\_totals\\_May & session\\_totals\\_Jun & QTY\\_totals\\_May & QTY\\_totals\\_Jun & addCart\\_totals\\_May & addCart\\_totals\\_June & ECR\\_total\\_May & ECR\\_total\\_Jun & transaction\\_abs\\_change & transaction\\_rel\\_change & session\\_abs\\_change & session\\_rel\\_change & QTY\\_abs\\_change & QTY\\_rel\\_change & addCart\\_abs\\_change & addCart\\_rel\\_change & ECR\\_abs\\_change & ECR\\_rel\\_change\\\\\n <int> & <int> & <int> & <int> & <int> & <int> & <int> & <int> & <dbl> & <dbl> & <int> & <dbl> & <int> & <dbl> & <int> & <dbl> & <int> & <dbl> & <dbl> & <dbl>\\\\\n\\hline\n\t 28389 & 34538 & 1164639 & 1388834 & 51629 & 61891 & 136720 & 107970 & 0.02437579 & 0.02486834 & 6149 & 0.216598 & 224195 & 0.1925017 & 10262 & 0.1987643 & -28750 & -0.2102838 & 0.0004925491 & 0.02020648\\\\\n\\end{tabular}\n",
"text/markdown": "\nA tibble: 1 × 20\n\n| transaction_totals_May &lt;int&gt; | transaction_totals_Jun &lt;int&gt; | session_totals_May &lt;int&gt; | session_totals_Jun &lt;int&gt; | QTY_totals_May &lt;int&gt; | QTY_totals_Jun &lt;int&gt; | addCart_totals_May &lt;int&gt; | addCart_totals_June &lt;int&gt; | ECR_total_May &lt;dbl&gt; | ECR_total_Jun &lt;dbl&gt; | transaction_abs_change &lt;int&gt; | transaction_rel_change &lt;dbl&gt; | session_abs_change &lt;int&gt; | session_rel_change &lt;dbl&gt; | QTY_abs_change &lt;int&gt; | QTY_rel_change &lt;dbl&gt; | addCart_abs_change &lt;int&gt; | addCart_rel_change &lt;dbl&gt; | ECR_abs_change &lt;dbl&gt; | ECR_rel_change &lt;dbl&gt; |\n|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|\n| 28389 | 34538 | 1164639 | 1388834 | 51629 | 61891 | 136720 | 107970 | 0.02437579 | 0.02486834 | 6149 | 0.216598 | 224195 | 0.1925017 | 10262 | 0.1987643 | -28750 | -0.2102838 | 0.0004925491 | 0.02020648 |\n\n",
"text/html": [
"<table class=\"dataframe\">\n",
"<caption>A tibble: 1 × 20</caption>\n",
"<thead>\n",
"\t<tr><th scope=col>transaction_totals_May</th><th scope=col>transaction_totals_Jun</th><th scope=col>session_totals_May</th><th scope=col>session_totals_Jun</th><th scope=col>QTY_totals_May</th><th scope=col>QTY_totals_Jun</th><th scope=col>addCart_totals_May</th><th scope=col>addCart_totals_June</th><th scope=col>ECR_total_May</th><th scope=col>ECR_total_Jun</th><th scope=col>transaction_abs_change</th><th scope=col>transaction_rel_change</th><th scope=col>session_abs_change</th><th scope=col>session_rel_change</th><th scope=col>QTY_abs_change</th><th scope=col>QTY_rel_change</th><th scope=col>addCart_abs_change</th><th scope=col>addCart_rel_change</th><th scope=col>ECR_abs_change</th><th scope=col>ECR_rel_change</th></tr>\n",
"\t<tr><th scope=col>&lt;int&gt;</th><th scope=col>&lt;int&gt;</th><th scope=col>&lt;int&gt;</th><th scope=col>&lt;int&gt;</th><th scope=col>&lt;int&gt;</th><th scope=col>&lt;int&gt;</th><th scope=col>&lt;int&gt;</th><th scope=col>&lt;int&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;int&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;int&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;int&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;int&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th></tr>\n",
"</thead>\n",
"<tbody>\n",
"\t<tr><td>28389</td><td>34538</td><td>1164639</td><td>1388834</td><td>51629</td><td>61891</td><td>136720</td><td>107970</td><td>0.02437579</td><td>0.02486834</td><td>6149</td><td>0.216598</td><td>224195</td><td>0.1925017</td><td>10262</td><td>0.1987643</td><td>-28750</td><td>-0.2102838</td><td>0.0004925491</td><td>0.02020648</td></tr>\n",
"</tbody>\n",
"</table>\n"
]
},
"metadata": {}
}
]
},
{
"cell_type": "markdown",
"source": [
"## slicing/reshaping the table "
],
"metadata": {
"id": "cjobJTaTMqHG"
}
},
{
"cell_type": "code",
"source": [
"transactions_cols <- c(\"transaction_totals_May\", \"transaction_totals_Jun\",\"transaction_abs_change\",\"transaction_rel_change\")\n",
"\n",
"transaction_df <- bob[,transactions_cols]"
],
"metadata": {
"id": "mQ4ipIjsTbv0"
},
"execution_count": null,
"outputs": []
},
{
"cell_type": "code",
"source": [
"sessions_cols <- c(\"session_totals_May\",\"session_totals_Jun\",\"session_abs_change\",\"session_rel_change\")\n",
"session_df <- bob[,sessions_cols]\n",
"\n"
],
"metadata": {
"id": "eRyK7lWxFYoA"
},
"execution_count": null,
"outputs": []
},
{
"cell_type": "code",
"source": [
"QTY_cols <- c(\"QTY_totals_May\",\"QTY_totals_Jun\",\"QTY_abs_change\",\"QTY_rel_change\")\n",
"QTY_df <- bob[, QTY_cols]\n",
"QTY_df"
],
"metadata": {
"colab": {
"base_uri": "https://localhost:8080/",
"height": 129
},
"id": "PSrWV1yFG5AF",
"outputId": "62f38497-18d9-4fc2-9aa5-63ff2b235724"
},
"execution_count": null,
"outputs": [
{
"output_type": "display_data",
"data": {
"text/plain": [
" QTY_totals_May QTY_totals_Jun QTY_abs_change QTY_rel_change\n",
"1 51629 61891 10262 0.1987643 "
],
"text/latex": "A tibble: 1 × 4\n\\begin{tabular}{llll}\n QTY\\_totals\\_May & QTY\\_totals\\_Jun & QTY\\_abs\\_change & QTY\\_rel\\_change\\\\\n <int> & <int> & <int> & <dbl>\\\\\n\\hline\n\t 51629 & 61891 & 10262 & 0.1987643\\\\\n\\end{tabular}\n",
"text/markdown": "\nA tibble: 1 × 4\n\n| QTY_totals_May &lt;int&gt; | QTY_totals_Jun &lt;int&gt; | QTY_abs_change &lt;int&gt; | QTY_rel_change &lt;dbl&gt; |\n|---|---|---|---|\n| 51629 | 61891 | 10262 | 0.1987643 |\n\n",
"text/html": [
"<table class=\"dataframe\">\n",
"<caption>A tibble: 1 × 4</caption>\n",
"<thead>\n",
"\t<tr><th scope=col>QTY_totals_May</th><th scope=col>QTY_totals_Jun</th><th scope=col>QTY_abs_change</th><th scope=col>QTY_rel_change</th></tr>\n",
"\t<tr><th scope=col>&lt;int&gt;</th><th scope=col>&lt;int&gt;</th><th scope=col>&lt;int&gt;</th><th scope=col>&lt;dbl&gt;</th></tr>\n",
"</thead>\n",
"<tbody>\n",
"\t<tr><td>51629</td><td>61891</td><td>10262</td><td>0.1987643</td></tr>\n",
"</tbody>\n",
"</table>\n"
]
},
"metadata": {}
}
]
},
{
"cell_type": "code",
"source": [
"ECR_cols <- c(\"ECR_total_May\",\"ECR_total_Jun\",\"ECR_abs_change\",\"ECR_rel_change\")\n",
"ECR_df <- bob[,ECR_cols]\n",
"ECR_df"
],
"metadata": {
"colab": {
"base_uri": "https://localhost:8080/",
"height": 129
},
"id": "G4RS6N-LHQVe",
"outputId": "48d88a98-7fd5-4ad8-d8d3-011516a283b4"
},
"execution_count": null,
"outputs": [
{
"output_type": "display_data",
"data": {
"text/plain": [
" ECR_total_May ECR_total_Jun ECR_abs_change ECR_rel_change\n",
"1 0.02437579 0.02486834 0.0004925491 0.02020648 "
],
"text/latex": "A tibble: 1 × 4\n\\begin{tabular}{llll}\n ECR\\_total\\_May & ECR\\_total\\_Jun & ECR\\_abs\\_change & ECR\\_rel\\_change\\\\\n <dbl> & <dbl> & <dbl> & <dbl>\\\\\n\\hline\n\t 0.02437579 & 0.02486834 & 0.0004925491 & 0.02020648\\\\\n\\end{tabular}\n",
"text/markdown": "\nA tibble: 1 × 4\n\n| ECR_total_May &lt;dbl&gt; | ECR_total_Jun &lt;dbl&gt; | ECR_abs_change &lt;dbl&gt; | ECR_rel_change &lt;dbl&gt; |\n|---|---|---|---|\n| 0.02437579 | 0.02486834 | 0.0004925491 | 0.02020648 |\n\n",
"text/html": [
"<table class=\"dataframe\">\n",
"<caption>A tibble: 1 × 4</caption>\n",
"<thead>\n",
"\t<tr><th scope=col>ECR_total_May</th><th scope=col>ECR_total_Jun</th><th scope=col>ECR_abs_change</th><th scope=col>ECR_rel_change</th></tr>\n",
"\t<tr><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th></tr>\n",
"</thead>\n",
"<tbody>\n",
"\t<tr><td>0.02437579</td><td>0.02486834</td><td>0.0004925491</td><td>0.02020648</td></tr>\n",
"</tbody>\n",
"</table>\n"
]
},
"metadata": {}
}
]
},
{
"cell_type": "code",
"source": [
"addCart_cols <- c(\"addCart_totals_May\",\"addCart_totals_June\",\"addCart_abs_change\",\"addCart_rel_change\")\n",
"addCart_df <- bob[, addCart_cols]\n"
],
"metadata": {
"id": "g00jFvSvH0O2"
},
"execution_count": null,
"outputs": []
},
{
"cell_type": "code",
"source": [
"standard_col_names <- c(\"May\",\"June\",\"Absolute Change\",\"Relative Change\")\n",
"colnames(addCart_df) <- standard_col_names\n",
"colnames(ECR_df) <- standard_col_names\n",
"colnames(session_df) <- standard_col_names\n",
"colnames(transaction_df) <-standard_col_names\n",
"colnames(QTY_df) <- standard_col_names\n"
],
"metadata": {
"id": "oHZXQOI7IH-4"
},
"execution_count": null,
"outputs": []
},
{
"cell_type": "markdown",
"source": [
"### We can now rbind the individual arrays together"
],
"metadata": {
"id": "Swl9Pa7qmrJs"
}
},
{
"cell_type": "code",
"source": [
"finaldf <-rbind(session_df,transaction_df,ECR_df,QTY_df,addCart_df)"
],
"metadata": {
"id": "K01Pj5-9Ld8V"
},
"execution_count": null,
"outputs": []
},
{
"cell_type": "code",
"source": [
"finaldf$Metrics <- c(\"Sessions\",\"Transactions\",\"ECR\",\"QTY\",\"AddCart\")"
],
"metadata": {
"id": "nWIMe8H3MLA5"
},
"execution_count": null,
"outputs": []
},
{
"cell_type": "code",
"source": [
"finaldf <- finaldf[,c(\"Metrics\",\"May\",\"June\",\"Absolute Change\",\"Relative Change\")]\n",
"finaldf"
],
"metadata": {
"colab": {
"base_uri": "https://localhost:8080/",
"height": 255
},
"id": "dbaxW_F2MgnT",
"outputId": "89eec143-8154-4fe5-f0cc-302b4a82bb33"
},
"execution_count": null,
"outputs": [
{
"output_type": "display_data",
"data": {
"text/plain": [
" Metrics May June Absolute Change Relative Change\n",
"1 Sessions 1.164639e+06 1.388834e+06 2.241950e+05 0.19250171 \n",
"2 Transactions 2.838900e+04 3.453800e+04 6.149000e+03 0.21659798 \n",
"3 ECR 2.437579e-02 2.486834e-02 4.925491e-04 0.02020648 \n",
"4 QTY 5.162900e+04 6.189100e+04 1.026200e+04 0.19876426 \n",
"5 AddCart 1.367200e+05 1.079700e+05 -2.875000e+04 -0.21028379 "
],
"text/latex": "A tibble: 5 × 5\n\\begin{tabular}{lllll}\n Metrics & May & June & Absolute Change & Relative Change\\\\\n <chr> & <dbl> & <dbl> & <dbl> & <dbl>\\\\\n\\hline\n\t Sessions & 1.164639e+06 & 1.388834e+06 & 2.241950e+05 & 0.19250171\\\\\n\t Transactions & 2.838900e+04 & 3.453800e+04 & 6.149000e+03 & 0.21659798\\\\\n\t ECR & 2.437579e-02 & 2.486834e-02 & 4.925491e-04 & 0.02020648\\\\\n\t QTY & 5.162900e+04 & 6.189100e+04 & 1.026200e+04 & 0.19876426\\\\\n\t AddCart & 1.367200e+05 & 1.079700e+05 & -2.875000e+04 & -0.21028379\\\\\n\\end{tabular}\n",
"text/markdown": "\nA tibble: 5 × 5\n\n| Metrics &lt;chr&gt; | May &lt;dbl&gt; | June &lt;dbl&gt; | Absolute Change &lt;dbl&gt; | Relative Change &lt;dbl&gt; |\n|---|---|---|---|---|\n| Sessions | 1.164639e+06 | 1.388834e+06 | 2.241950e+05 | 0.19250171 |\n| Transactions | 2.838900e+04 | 3.453800e+04 | 6.149000e+03 | 0.21659798 |\n| ECR | 2.437579e-02 | 2.486834e-02 | 4.925491e-04 | 0.02020648 |\n| QTY | 5.162900e+04 | 6.189100e+04 | 1.026200e+04 | 0.19876426 |\n| AddCart | 1.367200e+05 | 1.079700e+05 | -2.875000e+04 | -0.21028379 |\n\n",
"text/html": [
"<table class=\"dataframe\">\n",
"<caption>A tibble: 5 × 5</caption>\n",
"<thead>\n",
"\t<tr><th scope=col>Metrics</th><th scope=col>May</th><th scope=col>June</th><th scope=col>Absolute Change</th><th scope=col>Relative Change</th></tr>\n",
"\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th></tr>\n",
"</thead>\n",
"<tbody>\n",
"\t<tr><td>Sessions </td><td>1.164639e+06</td><td>1.388834e+06</td><td> 2.241950e+05</td><td> 0.19250171</td></tr>\n",
"\t<tr><td>Transactions</td><td>2.838900e+04</td><td>3.453800e+04</td><td> 6.149000e+03</td><td> 0.21659798</td></tr>\n",
"\t<tr><td>ECR </td><td>2.437579e-02</td><td>2.486834e-02</td><td> 4.925491e-04</td><td> 0.02020648</td></tr>\n",
"\t<tr><td>QTY </td><td>5.162900e+04</td><td>6.189100e+04</td><td> 1.026200e+04</td><td> 0.19876426</td></tr>\n",
"\t<tr><td>AddCart </td><td>1.367200e+05</td><td>1.079700e+05</td><td>-2.875000e+04</td><td>-0.21028379</td></tr>\n",
"</tbody>\n",
"</table>\n"
]
},
"metadata": {}
}
]
},
{
"cell_type": "markdown",
"source": [
"### Now that we have 2 dataframes, we export to Excel format, one table per sheet"
],
"metadata": {
"id": "uat3eQJym43B"
}
},
{
"cell_type": "code",
"source": [
""
],
"metadata": {
"colab": {
"base_uri": "https://localhost:8080/"
},
"id": "U5uRabWWbdUu",
"outputId": "f901bdb8-43a3-4578-c544-a42ab88552a5"
},
"execution_count": null,
"outputs": [
{
"output_type": "stream",
"name": "stderr",
"text": [
"Installing package into ‘/usr/local/lib/R/site-library’\n",
"(as ‘lib’ is unspecified)\n",
"\n"
]
}
]
},
{
"cell_type": "code",
"source": [
"workbook <- createWorkbook()\n",
"addWorksheet(wb = workbook, sheetName = \"Project1\", tabColour = \"#0413FC\")\n",
"addWorksheet(wb = workbook, sheetName = \"Project2\", tabColour = \"#0413FC\")\n",
"header_style <- createStyle(textDecoration = \"BOLD\", fontColour = \"#FFFFFF\", fontSize = 12, fontName = \"Calibri (Body)\", fgFill = \"#4F80BD\", halign = \"CENTER\")"
],
"metadata": {
"id": "E0o0HvjlLzBO"
},
"execution_count": null,
"outputs": []
},
{
"cell_type": "code",
"source": [
"writeData(wb = workbook, sheet = \"Project1\", x = sessions, startCol = 1, startRow = 1, colNames = TRUE, headerStyle = header_style, borders = \"all\", borderColour = \"#ADD8E6\", borderStyle = \"medium\", withFilter = TRUE)\n",
"writeData(wb = workbook, sheet = \"Project2\", x = finaldf, startCol = 1, startRow = 1, colNames = TRUE, headerStyle = header_style, borders = \"all\", borderColour = \"#ADD8E6\", borderStyle = \"medium\", withFilter = TRUE)"
],
"metadata": {
"id": "p7Oh6IMhO4sr"
},
"execution_count": null,
"outputs": []
},
{
"cell_type": "code",
"source": [
"saveWorkbook(workbook, \"IXIS_data.xlsx\", overwrite = TRUE)"
],
"metadata": {
"id": "bKzM8xUyPlAE"
},
"execution_count": null,
"outputs": []
},
{
"cell_type": "markdown",
"source": [
"# Download the Final XLSX file if you feel like it!"
],
"metadata": {
"id": "Biqwv_QanPb-"
}
}
]
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment