Writing
subscribe via RSS
-
TIL: SORT, UNIQUE, VSTACK
How to use three formulas to combine and sort the unique values from two different lists (arrays)
Imagine two very long lists of unique codes (names, id numbers, any unique identifier). You need a single list of the unique codes. There are several approaches but I learned about
VSTACKrecently, have wanted to use it, and had to look it up again to apply it, so I am writing this as a TIL - today I learned.Use the two lists to combine (
VSTACK) them into a single list of unique values (UNIQUE) that is sorted (SORT). -
Peaking behind the curtain for International Women's Day
Behind F1’s Velvet Curtain, by Kate Wagner ⇥ web.archive.org
Tagline: If you wanted to turn someone into a socialist you could do it in about an hour by taking them for a spin around the paddock of a Formula 1 race. The kind of money I saw will haunt me forever.
Kate Wagner
It’s 2024. We still do not have equality. We still need to fight for it daily. For this International Women’s Day I want to amplify a voice that was recently suppressed by those with more power. This is a case study in speaking truth to power, holding yourself and each other accountable, and maintaining your convictions viewed through the lens of the failure of the media industry and the deepening class divide.
-
Asset management programs face ongoing maintenance deficits
“Another flaw in the human character is that everybody wants to build and nobody wants to do maintenance.”
Kurt Vonnegut, Hocus Pocus
Viewed through the lens of total cost of facility ownership, the initial 20 to 30% of life-time costs for a constructed asset (building, road, bridge, etc.) occur in the first 2 to 4 years of existence accounting for planning, design, construction, and start-up. What about the remaining 70 to 80% of costs? The remaining 80% represents the majority of the cost an owner will incur – for operating, maintaining, recapitalizing, and disposing. Despite that, every client I have ever worked with faces significant funding shortfalls for maintenance and recapitalization. We celebrate ribbon cuttings; no one celebrates ongoing maintenance.
-
How to write ChatGPT prompts 5x better using Markdown
Want to improve your LLM performance? Get 5x better ChatGPT performance with these simple #hacks.
-
Which fonts?
Which fonts to use for your charts and tables - Datawrapper Blog
I’ve been reading Lisa Charlotte Muth’s writing on Datawrapper and elsewhere for a while and recently stumbled on this post again. I like that instead of just listing the top X things you should do for Y, she covers all the tips by explaining why and providing examples of not ideal and better solutions along with plenty of examples from mainstream sites.
Next time you are working on a presentation with graphs and tables, keep these tips in mind.
-
Chart recreations–iPhone Success
iPhone more successful than all other Apple products
I used to have an entire series of graph recreations but they’ve been lost to bit rot. I was reading another of Lisa Charlotte Rost’s posts over on Datawrapper, Better Charts, and wanted to try my hand at recreating the final iPhone graph in Tableau. I was mostly interested in seeing if I could reproduce it as closely as possible, and if I could get the highlighted time period in there.
Here’s my take on Tableau Public:
I think I nailed it.
-
Masking up
There are fewer COVID-19 cases reported in states with higher rates of mask use. According to the data science behind the analysis and the data visualization this is a powerful argument for wearing a mask.
-
Producing Small Multiples of COVID-19 case rates by state
Inspired by my love for small multiples, Horace Dediu’s recent work on Asymco, and the pandemic the world is experiencing I decided to put my Python skills to test and produce a small multiples plot of case rates for the United States.
-
Where is Excel's startup folder (XLStart)?
Here’s a quick tip on using Excel and the Visual Basic Editor (VBE) to determine the location of Excel’s startup folder. Any workbook in this location is opened automatically every time you launch Excel. This is where Excel stores personal workbooks, like
personal.xlsb, that can be launched everytime you use Excel. I think Excel will also store customized workbook templates,Book.xltx, here. -
That time I ran faster than a pro trail runner
King of the Mountain no more
I was once faster than Jim Walmsley. It’s true. But no longer. The other day I got a notification from Strava that my King of the Mountain (course record) for the South Kaibab Trail Climb had fallen by 13 seconds to Jim Walmsley who is a professional-ultra-trail runner.
-
I, (not) Robot, or am I
Passing (or failing?) a reverse Turing test
Maybe instead of being simultaneously alive and dead like Schrödinger’s cat I am simultaneously human and robot?
-
Regex - Don't Fear It
Bottom line up front: I’ve seen 55% of the top movies from 2000 - 2018.
-
Movies I Haven't Seen
OK, I will play along. I stumbled upon Pat Murray’s post who stumbled upon Maro Arment’s 2015 post titled “Popular movies I haven’s seen in the last 15 years”. Pat’s post “Popular Movies I Haven’t Seen: Or trends in movie viewing habits” struck a nerve because I was just asked to name my favorite 2018 movie during one of those “introduce yourself” moments that occurs at the start of some meetings or classes so eveyone can get to know one another. Problem was, I was stumped. I eventually settled on The Incredibles 2 but I am not sure that is really the answer.
-
Collatz Conjecture Redux
Remember the Collatz Conjecture which posits that any positive integer, n, will eventually reduce to one if you divide the integer by 2 (n/2) if the integer is even and multiply by 3 and add 1 (3n+1) if the integer is odd? The result is repeated until eventually you land on 1. Turns out that in addition to being an interesting exercise for an Excel workbook and some simple VBA code the Collatz Conjecture lends itself to being a python programming exercise.
-
Selecting the NCAA Men's Lacrosse Tournament
Every year there is a lot written about the NCAA Men’s Lacrosse Tournament selections. Since 1994 when the Bucknell Bison were undefeated and not selected for a post-season appearance a lot has changed but there are still difficult decisions made and teams left outside. It happened to Bucknell again this year in the 50th anniversary of the program. At least it was not as painful as the undefeated 1996 season which eventually led to the creation of the automatic qualifiers. Sid Jamieson, Bucknell’s coach at the time said:
-
Growing the game with the Lasersharks
I really enjoyed this episode of Justin Skaggs’ Woodshop Series featuring PJ Martin. I met PJ for the first time in Kentucky when we played for the New Jersey Jesters against the Kentucky Stickhorses in a North American Lacrosse League (NALL) exhibition game. I believe it was January 2012. I’ve since come to call PJ a close friend and I have been fortunate to get the opportunity to run with the Lasersharks in several of their early outings (KMF ‘14, exhibitions, etc.) as well as recent events (LASNAI ‘17, Redmen’s Open ‘18).
-
Everything you need to know about life can be learned from the Velveteen Rabbit
Today is Easter. My niece and nephew were visiting for the weekend and I was reminded of a story that I have long enjoyed. In today’s contentious times I think more of us could do well to work on becoming Real. I am working on it.
-
Making of Welcome Home
Apple recently released an ad for the HomePod called Welcome Home that is over 4 minutes for the full length spot. I liked it at first but once I watched it a few times and saw this behind the scenes video of the production, I loved it. The ad was directed by Spike Jonze and stars FKA Twigs, music is ‘Til It’s Over by Anderson .Paak.
-
Data Analysis and Interpretation: Writing about data
Learning continues with a third module, ‘Regression Modeling in Practice’. This week’s assignment is to demonstrate the ability to write about data using a specific framework. As a data analyst, an important skill to master is the ability to describe data so others are able to understand the population the sample came from, the procedures used to collect the data, and the measures used in the statistical analyses. This background information is critical for understanding who or what is being analyzed and how. It also helps to understand the limitation of the analysis. My assignment for this week follows.
-
Data Analysis and Interpretation: Moderation
Previous ANOVA analysis of the ‘Outlook on Life’ survey data on the question of trust in the legal system (W1_K1_C - how much do you think you can trust the legal system) revealed statistical significance for age (PPAGECT4) and education level (PPEDUCAT). See the bottom of this post for a summary of the variables. Duncan post hoc comparisons revealed that younger people (18-29) are statistically more likely to distrust the legal system than the other age groups. Post hoc comparisons also showed statistical significance for education levels with those having more education slightly more likely to trust the legal system.
-
Loving our parks to death
I often get asked ‘how many parks are there in the National Park Service?’ and I almost always hedge my answer because there are 417 sites (at the time of writing) but what most people are referring to when they ask the question are the 59 ‘park’ units (think headliner National Parks - if it does not have ‘National Park’ in the name it is a different kind of unit). Other types of sites or units in the NPS are parkways (Blue Ridge Parkway), recreation areas (Lake Mead National Recreation Area), seashores (Cape Cod National Seashore), battlefields (Gettysburg National Battlefield), and more.
-
Excel Based Contest Simulator
Occasionally as a modeler with strong Excel and VBA skills, one gets asked to create something outside of the realm of regular client work. These requests can often be fun and I find myself learning things with every solution developed. I was recently asked to help select a random winner of a contest for a clothing brand’s Instagram account. I have seen many Instagram posts where the winner is determined by a lot of random scrolling up and down on the contest post until the selector decides to place their finger on the screen and “select” the winner. Being an analyst that is Excel and VBA minded, I wanted something a little more robust, random, repeatable, easy to implement, and accomplished with every data analyst’s favorite tool, Excel (he said only somewhat sarcastically)!
-
Data Analysis and Interpretation: Pearson Correlation
I am using the ‘Outlook on Life’ survey data to investigate if age or education play a role in whether people think blacks and other minorities are treated the same as whites in the criminal justice system (
W1_K4in the codebook) or in whether they have trust in the legal system (W1_K1_Cin the codebook). Both of these categorical response variables have multiple levels, as do the explanatory variables. Since the Pearson correlation is a measure of the linear correlation between two X and Y quantitative variables I am going to complete this assignment using the GapMinder data instead. -
Olympic Moments
Kikkan Randall and Jesse Diggions winning the U.S. women’s cross-country medal was one of my favorite moments.
-
Data Analysis and Interpretation: Chi-Squared Analysis
I am using the ‘Outlook on Life’ survey data to investigate if age or education play a role in whether people think blacks and other minorities are treated the same as whites in the criminal justice system (
W1_K4in the codebook) or in whether they have trust in the legal system (W1_K1_Cin the codebook). Since both of these categorical response variables have multiple levels, as do the explanatory variables, they need to be collapsed into fewer groups.W1_K4(belief in equal treatment in criminal justice system) will be collapsed from 7 levels to 3 levels - Recieve equal treatment, No opinion, and Do not receive equal treatment.W1_K1_C(trust in legal system) will be collapsed from 4 levels to 2 levels - Trust and Mistrust. -
Data Analysis and Interpretation: ANOVA Analysis
I am using the ‘Outlook on Life’ survey data to investigate if age or education play a role in whether people think blacks and other minorities are treated the same as whites in the criminal justice system (
W1_K4in the codebook) or in whether they have trust in the legal system (W1_K1_Cin the codebook). -
How to make a lacrosse stick
Did you ever wonder how to make a wooden lacrosse stick?
-
Go For 2
When To Go For 2, For Real | FiveThirtyEight
Although NFL coaches have a level of expertise about the game of football that most of us will never approach, it’s hard to give them the benefit of the doubt when they’ve collectively demonstrated an inability to master basic tactical decisions — like when you should go for 2 points when your team is down 2 points (spoiler: pretty much always).
-
NLL Notebook
NLL Notebook: Rush Sweep, Del Bianco Shines and Byrne Breaks Loose | US Lacrosse Magazine
-
Coursework: Visualizing Data
The univariate graph of trust in the legal system is shown below. The graph is unimodal with the peak at the median category of 3 (‘Only some of the time’). It seems to be skewed left as there are higher frequencies in the higher than the lower categories.
-
Coursework: Making Data Management Decisions
I enrolled in a Coursera course titled ‘Data Science + Analytics’. The third week, Making Data Management Decisions requires a blog post describing the data management decisions made for your project, so this is my post. See below for the program and output.
-
Coursework: Running a SAS Program
I enrolled in a Coursera course titled ‘Data Science + Analytics’. The second lesson, Software Setup and Supporting Materials requires a blog post for running your first program, so this is my post. See below for the program and output.
-
Coursework: Developing A Research Question
I enrolled in a Coursera course titled ‘Data Science + Analytics’ to gain some training. The first lesson, Steps in Data Analysis requires a blog post for getting a research project started so this is my post.
-
Micro-Blogs and the IndieWeb
So I have been hearing and reading about micro-blogging and the IndieWeb a bit lately. I signed up for micro.blog/sprestrige and am now trying to figure it out. I’m thinking it is a way to roll your own Twitter and own all of your writing.
-
Growing the Game in Turku, Finland
Growing the game of lacrosse came full circle for me today as I tuned into the 2017 European Box Lacrosse Championship taking place in Turku, Finland through 15 July and listened to Stephen Stamp call the game between host Finland and the Czech Republic in the opening day’s final match. I have known several people involved for going on 10 years now and it is all because of lacrosse.
-
Preferred Method - Dynamic Data Validation in Excel
There is almost always more than one way to do something in Excel and sometimes an even better way. Last week after writing up two methods for dynamic data validation in Excel a colleague pointed out that a non-volatile solution could use the
INDEXfunction and the range operator to define a named range that would be dynamic because of the formula used to define it. Another colleague pointed out that a simpler example was in order. The file, Dynamic Picklists.xlsx, is that example. -
Stick Review: East Coast Dyes Mirage
Two things recently reminded me that I should write a review of the East Coast Dyes Mirage - an Inside Lacrosse post which reviewed the StringKing Mark 2V and a video from Greg Kenneally at ECD on box-pinching a Mirage using the LaxRoom box head pinch rig. Why did the Mark 2V review remind me? Because I play in the same old-man league as Tom Huppmann (@24sevenlax), one of the reviewers and I loved his line:
-
Dynamic Data Validation in Excel
UPDATE: Preferred method described a week later in this post. It involves using the
INDEXfunction on either side of the:range operator to define a named range that will be dynamic becuase it is based on the formula. I have left the following here for posterity. -
Persistent Default Values in An Excel Model
Hat tip to my colleague, Dan Schriever, for this post sparked by one of his posts on Yammer.
-
Pour one out for TapCellar
I am not really sure where I learned of TapCellar or of Gabe Weatherhead and Jeff Hunsberger’s Nerds on Draft podcast but I did and my beer drinking and podcast listening life has been better for it. Unfortunately in today’s app store environment this wonderful beer app had to be retired.
-
Git for Grownups
I found an article in the 2013 “24 ways to impress your friends” about learning and understanding Git titled ‘Git for Grownups’. The image about Git workflow really helped me to understand a bit more what Git is doing as a version control system.
-
Lock Down an Excel Workbook
Sometimes we are asked by clients to set something up where after a certain date access to the file or tool is limited. Maybe data in the tool becomes outdated and they would like the opportunity to update it and lock users out from using it past the expiration or maybe they just don’t want end users to have the full functionality of the tool beyond a certain date. This example workbook provides a method for giving a client just that. I have to credit Dan Schriever for putting the workbook together and the macros – I believe the majority of it was his work which he later showed me and I modified in order to put this example and post together.
-
Simple Excel Based Contest Simulator
Occasionally as a modeler with strong Excel and VBA skills one gets asked to create something outside of the realm of regular client work. These requests can often be fun and I find myself learning things with every solution developed. I was recently asked to help select a random winner of a contest for a clothing brand’s Instagram account. I have seen many Instagram posts where the winner is determined by a lot of random scrolling up and down on the contest post until the selector decides to place their finger on the screen and “select” the winner. Being Excel and VBA minded, I wanted something a little more robust, random, repeatable, easy to implement, and accomplished with every data analyst’s favorite tool (he said somewhat sarcastically), Excel!
-
Fun with the Collatz Conjecture
I had heard about this mathematical tidbit in grad school and forgotten all about it. For some reason it has been making the rounds on the Internet and social media recently and I stumbled upon it again. I think it was this YouTube video I saw most recently from the Numberphile:
-
Even New York City has National Parks - Gateway National Recreation Area
Battery Weed at Fort Wadsworth | S. L. Prestridge
Yesterday, April 16th, marked the start of National Park Week which runs through April 24th and is about celebrating a centennial of America’s best idea by exploring amazing places, discovering open spaces, and enjoying affordable get-aways in our national parks. Throughout National Park Week admission to every unit in the nation is free so there’s really no excuse not to find some of the parks nearest you and visit.
-
SUMPRODUCT and Double Unary Operators
I learned about SUMPRODUCT and Double Unary Operators recently. Any test used in array formulas returns an array of TRUE/FALSE values and in order to use the results of that test in a calculation an operator is required to convert those TRUE/FALSE values to 1/0’s. SUMPRODUCT is a built-in Excel function which manages to perform the operation without the use of an array formula. Traditionally I would use an array formula to figure out the total sales of Luke Skywalker in the West region. The formula would look like this:
-
You Can Run America's National Parks
From Salt Marsh | S.L. Prestridge
Did you know there is a scientific term for that calm, meditative feeling you get when you’re on a hike, trail run, or kayaking mellow waters and your mind is completely at ease, taking in the scenery, and maybe daydreaming a little? I call if ‘flow’ and most athletes probably have experienced it and scientists have termed it “soft fascination”. I most often achieve this state of flow while running and it is especially easy for me to reach when trail running — something to do with our primal instincts from hunter-gatherer days and being active in nature I suppose.
-
Exploring Prince William Forest Park
Sun-up at Turkey Run Ampitheater | S. L. Prestridge
Last week I spent time in another of our national treasure’s, a park just outside the nation’s capital. This national park is the largest protected natural area in the Washington, D.C. metropolitan region at over 7,700 ha (19,000 acres). I hiked miles of trails and fire roads with our dog, Jammer, went on a perfect trail run on many of those same single track trails and secluded fire roads, explored one of the cabin camps, and visited the new exhibits being installed at the renovated visitor center. Get outside, do and see more, and enjoy the places that move you; Prince William Forest Park is one of mine.
-
Hidden Gem in Western Maryland - Monocacy National Battlefield
Remembering Worthington Farm | S. L. Prestridge
I have lived more or less within a 40 mile radius of Frederick, MD for 20 years and until Friday had never been to the national park there. Monocacy National Battlefield is a unit of the National Park Service (NPS) having been approved by Congress in 1934 and is located just southeast of Frederick, MD. It would be over 50 years before Congress finally appropriated funds and the land was acquired in the late 1970s and turned over the the NPS for interpretation and maintenance.
-
Parks in unexpected places - Dred Scott, Old Courthouse, and the Gateway Arch
Old Courthouse and Gateway Arch | S. L. Prestridge
I had an English teacher in high school that gave everyone nicknames at the beginning of the school year that he would use during role call at the start of each class and sometimes to call on people when class discussions took place. Mine was Dred as in “Dred Scott” but initially I thought it he meant “Dread Scott”. I needed to educate myself because my public school education certainly neglected aspects of American history as it relates to BIPOC (Black, Indigenous, People of Color).
-
Sometimes parks are in your back yard - Morristown National Historic Park
Source: NPS. This model is a theoretical representation of the Upper Redoubt built in 1777 by George Washington’s Army, today this area of the park is known as Fort Nonsense. http://www.nps.gov/morr/learn/historyculture/fortnonsense.htm
Growing up my family would spend most Sundays at my grand parent’s house in Morristown, NJ. The house had an upstairs apartment that my great uncle and great aunt lived in. Just about every week my great uncle would take me out into the woods across the street for a hike and some outdoor activity — I always thought it was to get some of the kids out of everyone’s hair for a bit but looking back I think it was because I liked hiking and being outside and my uncle realized that.
-
Finding yourself in Joshua Tree National Park
Joshua Tree | S. L. Prestridge
There is no moment of delight in any pilgrimage like the beginning of it.
Charles Dudley Warner
-
Chesapeake & Ohio Canal National Historic Park
The Chesapeake and Ohio Canal (C&O Canal) stretches over 184 miles along the Potomac River from Washington, DC to Cumberland, MD. The Chesapeake and Ohio Canal National Historic Park (CHOH) is charged with preserving and protecting this cultural resource for our enjoyment. Along the length of the canal are 74 lift locks, numerous lockhouses, 11 aqueducts, a turning basin, a railroad lift bridge, and one of the only remaining Bollman Iron Truss bridges in the area.
-
Martin Luther King, Jr. Memorial
MLK | S. L. Prestridge
Many people do not realize that most of the downtown area in Washington, D.C. is part of a national park, the National Mall and Memorial Parks (NAMA). Often referred to as our nation’s front yard, the mall stretches from the U.S. Capitol on the east to the Potomac River and the Lincoln Memorial on the west end and helps the park to protect many iconic monuments and memorials in addition to providing green space in the heart of downtown.
-
Cape Cod National Seashore
From Salt Marsh | S.L. Prestridge
For over 20 years my friends and family have been fortunate to vacation on Cape Cod in Eastham, MA. It is a special place and will forever have a hold on my heart and soul. What many do not realize is that much of the outer cape, from Eastham to Provincetown, is part of our national park system:
”…the best idea we ever had. Absolutely American, absolutely democratic, they reflect us at our best rather than our worst.”
Wallace Stegner
-
Acadia National Park
Sunrise on Eagle Lake | S. L. Prestridge
I am fortunate, I am well traveled. Name a national park off the top of your head and I have likely spent time there. In 2013 I visited Acadia NP for the first time. Highly recommend a visit. Until then, enjoy this short video from More Than Just Parks.
-
Excel Heat Map w/ Pivot Table
Ever want to use Excel and Pivot Tables to display a heat map? Well now you can. Via Nathan Yau (@flowingdata):
-
Dice Words, Excel, and Secure Passphrases
I am not entirely sure how I first heard about developing secure passphrases using random numbers and a table of words, it was likely a blog post from the makers of 1Password, but in any event I stumbled upon this Dr. Drang post over the weekend that revisits the topic in a uniquely nerdy fashion. As I am not as command-line-competent as Dr. Drang, I had already developed my own solution using Excel when I first read about this method of developing passphrases. See my solution in this Excel file and feel free to use it as you see fit. I think Dr. Drang’s post and the posts he links to cover the theory so if you are interested, please read up there, I’ll wait.
-
I never really understood Bayes' Theorem until now
If you studied statistics in college you probably learned about Bayes’ Theorem which is a useful tool for calculating conditional probabilities but as it is generally stated can be intimidating unless you are a world-class statistician. Legos to the rescue! That’s right, everything I know about Bayes’ Theorem I learned from Lego bricks and this blog post which elegantly explains Bayes’ Theorem using Legos and also derives the theorem conceptually. Enjoy.
-
Identifying Cheaters in Test Results
A professor at McMaster University in Canada suspected his students of cheating on the final exam. He used R and a plot of all ~18k pairs of students and their answers to identify suspect tests.
-
The Impact of Vaccines
I’m always on the lookout for interesting and impactful visualizations and the WSJ Graphics usually does not disappoint. Recently they put together a series of visualizations demonstrating the impact of vaccinations on various diseases.
-
Big Data
Big data gets a lot of ink these days including at Booz Allen. What interests me is answering real-world questions with big data.
-
Facility Management: Three Metrics Steer Investment Decisions
I collaborated with a few colleagues, Doug Kincaid, Tim Harvey, and Mary Hudson, to write an article for Building Operating Management about the National Park Service’s (NPS) investment metrics. The article follows below or view a PDF.
-
Pie Charts Suck! NBC Olympics coverage too.
I recently came across the article “How We Hate NBC’s Olympics Coverage: A Statistical Breakdown” and meant to write about the use of graphs in the article. The author (or editors) decided to lead with a donut chart - possibly the only graph I despise more than a pie graph and I immediately recognized that it was not even an original chart, they had simply used the default settings in Excel 2007 to produce the graph.
-
I hate pie
…graphs, not the food variety.
Apple pie, pumpkin pie, blueberry pie, lemon meringue pie…I like them all. Pie graphs…I hate pie graphs.
-
2007 GrowTheGame - Lacrosse in Sweden
This piece was originally published by e-lacrosse.com on June 7, 2007 and was written by Bill Curtis, Rebel Lacrosse Wear.
