dannguyen

How to use xsv (in Windows) to split up a CSV file too big for Excel

I wrote these instructions on how to install and use xsv – a powerful CSV-handling command-line tool, because someone asked how to deal with a data file that was too big to open in Excel or even Notepad. I didn't know how familiar the person was with installing/running downloadable .exe files or with Powershell, so I've tried to include some general instructions that hopefully are useful to even novices.

This mini-guide is not at all meant to be exhaustive as it basically shows just one of xsv's many useful functions. But if you're new to the idea of using command-line tools to do things, hopefully this can be a friendly intro to it.

---

Here's an example of a CSV that, at 3 million rows, is too big for Excel to open: https://burntsushi.net/stuff/worldcitiespop.csv

dannguyen

this goes in my bash profile:

XRESET='\[\033[00m\]'
PROMPT_PATH="\[\033[0;33m\]\W${XRESET} \[\033[1;37m\]\$${XRESET}"
PROMPT_GHOST="༼ つ\[\033[1;33m\]°${XRESET}\[\033[1;31m\]︻\[\033[1;33m\]゜${XRESET}༽つ🐕"

export PS1="${PROMPT_GHOST} ${PROMPT_PATH} "

dannguyen

Example Google Apps Script functions to normalize non-ASCII characters and insert a timestamp, when a new row is created.

Basically I sloppily added various Vietnamese accented characters to this Gist example: https://gist.github.com/akora/51b2933a2554776d7144#gistcomment-2936646

Blogpost about Apps Script in general, onEdit and timestamps here: http://blog.danwin.com/how-to-automatically-timestamp-a-new-row-in-google-sheets-using-apps-script/

dannguyen

test test test

dannguyen

How to automatically timestamp a column when a row in Google Sheets is created

Couldn't be bothered to look up the latest workout tracking app, so decided to stick to Google Sheets for now. One problem with this approach is that while it is easy on desktop GSheets to fill out a current timestamp (Command-Option-Shift-semicolon in MacOS), no such shortcut is available AFAIK in the iOS version of Google Sheets.

So I wrote a little custom function in Google Apps Script to do the following:

In an active Google Sheets spreadsheet, given a tab/sheet named myworkouts with a header named datetime, the following script updates a row's corresponding "datetime" column when a value is entered into a first column cell:

dannguyen

[
  {
    "id": 0,
    "name": "Adirondack Explorer",
    "homepageUrl": "https://www.adirondackexplorer.org",
    "supportUrl": "https://www.adirondackexplorer.org/subscription-center/new-combined-digital-and-print-subscription",
    "city": "Saranac Lake",
    "state": 36,
    "lat": "44.329496",
    "long": "-74.1312662"

dannguyen

dannguyen

Twitter's ad-impressions.js file, on how @mckinsey and @boba targeted their promoted tweets

The two JSON files in this gist are partial extracts from the ad-impressions.js file that Twitter gave me when I requested my personal data archive. This JSON file includes all the ads I expereinced on Twitter from December 8 2019 to Jan 8 2020.

I was just curious about ads from @McKinsey and @boba, particularly who they wanted to target, which is something that the ad-impressions.js file tells you.

tl;dr based on my personal data, I didn't really learn anything surprising. But read on for some details if you're kind of interested about social media data and ad targeting.

• More info about requesting your own twitter data: https://help.twitter.com/en/managing-your-account/how-to-download-your-twitter-archive

dannguyen

# Helpful resources:
# https://superuser.com/questions/1188772/mac-command-to-change-the-background-color-in-a-terminal
# https://apple.stackexchange.com/questions/348762/how-to-have-a-random-background-color-in-terminal-app
# https://stackoverflow.com/questions/8063228/how-do-i-check-if-a-variable-exists-in-a-list-in-bash
cbg(){
    local OCMD=""
    local RED=0; local GRN=0; local BLU=0;
    local THEMES="pro basic grass homebrew ocean" ## preset themes
    local HELPMSG; read -r -d '' HELPMSG <<EOF

dannguyen

This gist is meant just as remote storage for two text files, hello.txt and world.txt, as used by my data wrangling template project example:

https://github.com/dannguyen/pydataproject-template

Direct URLs for the files:

hello.txt: https://gist.github.com/dannguyen/df87d9d4cf42ed7b4f9549553ea5b8c8/raw/9bf95b5c6e234e491821cd355f6db1ec655cbe38/hello.txt

world.txt: https://gist.github.com/dannguyen/df87d9d4cf42ed7b4f9549553ea5b8c8/raw/9bf95b5c6e234e491821cd355f6db1ec655cbe38/world.txt