Dataset
For my fourth viz of Dashboard Week, we were handed quite a complex dataset all about crossword puzzles over the years. Not something I ever thought I’d analyse, but it was surprisingly fun. The data was scrapped and loaded into .csv files and some of them were massive. I had five datasets to play with, and I chose to focus my attention on the parsed puzzles which listed puzzle-level metadata (like publication date, outlet, crossword size and editor) and puzzle info tables which broke down the details of every clue for each puzzle over the years.
At first glance, it felt like a lot of text. Having primarily worked with data that contains a lot of measures and quantitative information this was not very forthcoming in that regard. But like a puzzle once I started exploring and joining the pieces in Power BI an interesting timeline of how crosswords evolved over the decades began to emerge.
Power Query
In Power Query, the first step was merging the Parsed Puzzle table with the Puzzle Info table using the common key in puzzleID. This helped me match the details of the clues given to the users to solve a particular crossword puzzle over the years.
I then added a couple of calculated columns to see if I could provide a quantitative lens to this wordplay through the use of number words in a clue and the length of the clue by characters. These became useful later to see if puzzles were getting easier or harder over time.
I also created a date table so I could analyse puzzles by year, decade, and even weekday. After filtering out blanks and removing some redundant columns, I had a clean, linked dataset ready for Power BI.
Story
Once the dataset was ready, I did some quick desktop research to help me formulate my hypothesis for this dashboard. When I saw that these crosswords were most prominent in newspapers particularly the New York times, I wanted to create a dashboard that emulated the reporting style of a newspaper. As I started exploring the dashboard I started noting some interesting patterns emerged as crosswords grew from niche brain teasers in newspapers and evolving with technology in the digital space. Some of the key takeaways from this exploration was:
- Crosswords date back to the 1910s, but things only took off in the 1940s. Between 1950 and 1990, it was steady growth. But from the 2000s onward, it exploded thanks to software and online platforms that made puzzle-making easier than ever.
- The number of unique authors spiked after 2000, showing a clear shift from professional only authors/editors in newspapers to crowd sourced creativity embracing the digital age.
- The size of the crossword puzzle gets established early in the 1940s and does not have any evolutionary change over the ages.
- All interesting titbits about the humble history of the crossword puzzle which you can explore in more detail by clicking into my dashboard.