{"id":11946,"date":"2019-02-20T00:08:49","date_gmt":"2019-02-20T05:08:49","guid":{"rendered":"https:\/\/rud.is\/b\/?p=11946"},"modified":"2019-02-20T07:19:00","modified_gmt":"2019-02-20T12:19:00","slug":"i-just-wanted-the-data-turning-tableau-tidyverse-tears-into-smiles-with-base-r-an-encoding-detective-story","status":"publish","type":"post","link":"https:\/\/rud.is\/b\/2019\/02\/20\/i-just-wanted-the-data-turning-tableau-tidyverse-tears-into-smiles-with-base-r-an-encoding-detective-story\/","title":{"rendered":"I Just Wanted The Data : Turning Tableau & Tidyverse Tears Into Smiles with Base R (An Encoding Detective Story)"},"content":{"rendered":"

Those outside the Colonies may not know that Payless—a national chain that made footwear affordable for millions of ‘Muricans who can’t spare $100.00 USD for a pair of shoes their 7 year old will outgrow in a year— is closing<\/a>. CNBC also had a story<\/a> that featured a choropleth with a tiny button at the bottom that indicated one could get the data:<\/p>\n

\"\"<\/a><\/p>\n

I should have known this would turn out to be a chore since they used Tableau—the platform of choice when you want to take advantage of all the free software libraries they use to power their premier platform which, in turn, locks up all the data for you so others can’t adopt, adapt and improve. Go. Egregious. Predatory. Capitalism.<\/p>\n

Anyway.<\/em><\/p>\n

I wanted the data to do some real analysis vs produce a fairly unhelpful visualization (TLDR: layer in Census data for areas impacted, estimate job losses, compute nearest similar Payless stores to see impact on transportation-challenged homes, etc. Y’now, citizen data journalism-y things) so I pressed the button and watched for the URL in Chrome (aye, for those that remember I moved to Firefox et al in 2018, I switched back; more on that in March) and copied it to try to make this post actually reproducible (a novel concept for Tableau fanbois):<\/p>\n

library(tibble)\nlibrary(readr)\n\n# https:\/\/www.cnbc.com\/2019\/02\/19\/heres-a-map-of-where-payless-shoesource-is-closing-2500-stores.html\n\ntfil <- \"~\/Data\/Sheet_3_data.csv\"\n\ndownload.file(\n  \"https:\/\/public.tableau.com\/vizql\/w\/PAYLESSSTORECLOSINGS\/v\/Dashboard2\/vud\/sessions\/6A678928620645FF99C7EF6353426CE8-0:0\/views\/10625182665948828489_7202546092381496425?csv=true&showall=true\",\n  tfil\n)\n## trying URL 'https:\/\/public.tableau.com\/vizql\/w\/PAYLESSSTORECLOSINGS\/v\/Dashboard2\/vud\/sessions\/6A678928620645FF99C7EF6353426CE8-0:0\/views\/10625182665948828489_7202546092381496425?csv=true&showall=true'\n## Error in download.file(\"https:\/\/public.tableau.com\/vizql\/w\/PAYLESSSTORECLOSINGS\/v\/Dashboard2\/vud\/sessions\/6A678928620645FF99C7EF6353426CE8-0:0\/views\/10625182665948828489_7202546092381496425?csv=true&showall=true\",  : \n##   cannot open URL 'https:\/\/public.tableau.com\/vizql\/w\/PAYLESSSTORECLOSINGS\/v\/Dashboard2\/vud\/sessions\/6A678928620645FF99C7EF6353426CE8-0:0\/views\/10625182665948828489_7202546092381496425?csv=true&showall=true'\n## In addition: Warning message:\n## In download.file(\"https:\/\/public.tableau.com\/vizql\/w\/PAYLESSSTORECLOSINGS\/v\/Dashboard2\/vud\/sessions\/6A678928620645FF99C7EF6353426CE8-0:0\/views\/10625182665948828489_7202546092381496425?csv=true&showall=true\",  :\n##   cannot open URL 'https:\/\/public.tableau.com\/vizql\/w\/PAYLESSSTORECLOSINGS\/v\/Dashboard2\/vud\/sessions\/6A678928620645FF99C7EF6353426CE8-0:0\/views\/10625182665948828489_7202546092381496425?csv=true&showall=true': HTTP status was '410 Gone'\n<\/code><\/pre>\n
WAT<\/p>\n
Truth be told I expected a time-boxed URL of some sort (prior experience FTW). Selenium or Splash were potential alternatives but I didn’t want to research the legality of more forceful scraping (I just wanted the data) so I manually<\/em> downloaded the file (*the horror*) and proceeded to read it in. Well, try<\/em> to read it in:<\/p>\n
read_csv(tfil)\n## Parsed with column specification:\n## cols(\n##   A = col_logical()\n## )\n## Warning: 2092 parsing failures.\n## row col           expected actual                      file\n##   1   A 1\/0\/T\/F\/TRUE\/FALSE        '~\/Data\/Sheet_3_data.csv'\n##   2   A 1\/0\/T\/F\/TRUE\/FALSE        '~\/Data\/Sheet_3_data.csv'\n##   3   A 1\/0\/T\/F\/TRUE\/FALSE        '~\/Data\/Sheet_3_data.csv'\n##   4   A 1\/0\/T\/F\/TRUE\/FALSE        '~\/Data\/Sheet_3_data.csv'\n##   5   A 1\/0\/T\/F\/TRUE\/FALSE        '~\/Data\/Sheet_3_data.csv'\n## ... ... .................. ...... .........................\n## See problems(...) for more details.\n## \n## # A tibble: 2,090 x 1\n##    A    \n##    <lgl>\n##  1 NA   \n##  2 NA   \n##  3 NA   \n##  4 NA   \n##  5 NA   \n##  6 NA   \n##  7 NA   \n##  8 NA   \n##  9 NA   \n## 10 NA   \n## # \u2026 with 2,080 more rows\n<\/code><\/pre>\n
WAT<\/p>\n
Getting a single column back from readr::read_[ct]sv()<\/code> is (generally) a tell-tale sign that the file format is amiss. Before donning a deerstalker<\/a> (I just wanted the data!) I tried to just use good ol’ read.csv()<\/code>:<\/p>\n
read.csv(tfil, stringsAsFactors=FALSE)\n## Error in make.names(col.names, unique = TRUE) : \n##   invalid multibyte string at '<ff><fe>A'\n## In addition: Warning messages:\n## 1: In read.table(file = file, header = header, sep = sep, quote = quote,  :\n##   line 1 appears to contain embedded nulls\n## 2: In read.table(file = file, header = header, sep = sep, quote = quote,  :\n##   line 2 appears to contain embedded nulls\n## 3: In read.table(file = file, header = header, sep = sep, quote = quote,  :\n##   line 3 appears to contain embedded nulls\n## 4: In read.table(file = file, header = header, sep = sep, quote = quote,  :\n##   line 4 appears to contain embedded nulls\n## 5: In read.table(file = file, header = header, sep = sep, quote = quote,  :\n##   line 5 appears to contain embedded nulls\n<\/code><\/pre>\n
WAT<\/p>\n
Actually the “WAT” isn’t really<\/em> warranted since read.csv()<\/code> gave us some super-valuable info via invalid multibyte string at '<ff><fe>A'<\/code>. FF FE<\/code> is a big<\/em> signal1<\/a> 2<\/a><\/sup> we’re working with a file in another encoding as that’s a common “magic” sequence at the start of such files.<\/p>\n
But, I didn’t want to delve into my Columbo persona\u2026 I. Just. Wanted. The. Data<\/em>. So, I tried the mind-bendingly fast and flexible helper from data.table<\/code>:<\/p>\n
data.table::fread(tfil)\n## Error in data.table::fread(tfil) : \n##   File is encoded in UTF-16, this encoding is not supported by fread(). Please recode the file to UTF-8.\n<\/code><\/pre>\n
AHA<\/em>. UTF-16 (maybe<\/em>). Let’s poke at the raw file:<\/p>\n
x <- readBin(tfil, \"raw\", file.size(tfil)) ## also: read_file_raw(tfil)\n\nx[1:100]\n##   [1] ff fe 41 00 64 00 64 00 72 00 65 00 73 00 73 00 09 00 43 00\n##  [21] 69 00 74 00 79 00 09 00 43 00 6f 00 75 00 6e 00 74 00 72 00\n##  [41] 79 00 09 00 49 00 6e 00 64 00 65 00 78 00 09 00 4c 00 61 00\n##  [61] 62 00 65 00 6c 00 09 00 4c 00 61 00 74 00 69 00 74 00 75 00\n##  [81] 64 00 65 00 09 00 4c 00 6f 00 6e 00 67 00 69 00 74 00 75 00\n<\/code><\/pre>\n
There’s our ff fe<\/code> (which is the beginning of the possibility it’s UTF-16) but that 41 00<\/code> harkens back to UTF-16’s older sibling UCS-2. The 0x00<\/code>‘s are embedded nul<\/code>s (likely to get bytes aligned). And, there are alot<\/em> of 09<\/code>s. Y’know what they<\/em> are? They’re <tab><\/code>s. That’s right. Tableau named file full of TSV records in an unnecessary elaborate encoding as CSV<\/code>. Perhaps they broke the “T” on all their keyboards typing their product name so much.<\/p>\n
Living A Boy’s [Data] Adventure Tale<\/h3>\n
At this point we have:<\/p>\n