For this project, I continued in the same direction as my first assignment, analyzing the most popular foreign cuisines as determined by SPL checkouts of food and drink-related Dewey media.
I queried the number of checkouts for each month between 2006 and 2018 for a total of 197 countries, not including the United States. I did a few test runs at first and noticed that there are a number of countries whose names make up other words that have been used in titles -- for example, the string "dutch" is part of "dutch oven", which has nothing to do with Dutch food, so the query below includes some AND NOT title LIKE '...' to account for instances that I noticed. I wasn't able to catch everything - I'm sure that some of the titles that include "Turkey" might be about the poultry and not the country, for example.
Code: Select all
SELECT
YEAR(cout),
MONTH(cout),
COUNT(IF(title LIKE '%afghan%', 1, NULL)) AS 'Afghanistan',
COUNT(IF(title LIKE '%albania%', 1, NULL)) AS 'Albania',
COUNT(IF(title LIKE '%algeri%', 1, NULL)) AS 'Algeria',
COUNT(IF(title LIKE '%andorra%', 1, NULL)) AS 'Andorra',
COUNT(IF(title LIKE '%angola%', 1, NULL)) AS 'Angola',
COUNT(IF(title LIKE '%anguilla%', 1, NULL)) AS 'Anguilla',
COUNT(IF(title LIKE '%antigua%'
OR title LIKE '%barbuda%',
1,
NULL)) AS 'Antigua & Barbuda',
COUNT(IF(title LIKE '%argentin%', 1, NULL)) AS 'Argentina',
COUNT(IF(title LIKE '%armenia%', 1, NULL)) AS 'Armenia',
COUNT(IF(title LIKE '%australia%', 1, NULL)) AS 'Australia',
COUNT(IF(title LIKE '%austria%', 1, NULL)) AS 'Austria',
COUNT(IF(title LIKE '%azerbaijan%'
OR title LIKE '%azeri%',
1,
NULL)) AS 'Azerbaijan',
COUNT(IF(title LIKE '%baham%', 1, NULL)) AS 'Bahamas',
COUNT(IF(title LIKE '%bahrain%', 1, NULL)) AS 'Bahrain',
COUNT(IF(title LIKE '%bangladesh%', 1, NULL)) AS 'Bangladesh',
COUNT(IF(title LIKE '%barbad%', 1, NULL)) AS 'Barbados',
COUNT(IF(title LIKE '%belarus%', 1, NULL)) AS 'Belarus',
COUNT(IF(title LIKE '%belgi%', 1, NULL)) AS 'Belgium',
COUNT(IF(title LIKE '%belize%', 1, NULL)) AS 'Belize',
COUNT(IF(title LIKE '%benin%', 1, NULL)) AS 'Benin',
COUNT(IF(title LIKE '%bermuda%', 1, NULL)) AS 'Bermuda',
COUNT(IF(title LIKE '%bhutan%', 1, NULL)) AS 'Bhutan',
COUNT(IF(title LIKE '%bolivia%', 1, NULL)) AS 'Bolivia',
COUNT(IF(title LIKE '%bosnia%'
OR title LIKE '%herzegovina%',
1,
NULL)) AS 'Bosnia & Herzegovina',
COUNT(IF(title LIKE '%botswana%', 1, NULL)) AS 'Botswana',
COUNT(IF(title LIKE '%brazil%'
OR title LIKE '%brasil%',
1,
NULL)) AS 'Brazil',
COUNT(IF(title LIKE '%brunei%', 1, NULL)) AS 'Brunei',
COUNT(IF(title LIKE '%bulgaria%', 1, NULL)) AS 'Bulgaria',
COUNT(IF(title LIKE '%burkina%', 1, NULL)) AS 'Burkina Faso',
COUNT(IF(title LIKE '%burundi%', 1, NULL)) AS 'Burundi',
COUNT(IF(title LIKE '%cambod%', 1, NULL)) AS 'Cambodia',
COUNT(IF(title LIKE '%cameroon%', 1, NULL)) AS 'Cameroon',
COUNT(IF(title LIKE '%canad%', 1, NULL)) AS 'Canada',
COUNT(IF(title LIKE '%cape verde%', 1, NULL)) AS 'Cape Verde',
COUNT(IF(title LIKE '%cayman%', 1, NULL)) AS 'Cayman Islands',
COUNT(IF(title LIKE '%central african republic%',
1,
NULL)) AS 'Central African Republic',
COUNT(IF(title LIKE '%chad%', 1, NULL)) AS 'Chad',
COUNT(IF(title LIKE '%chilean%', 1, NULL)) AS 'Chile',
COUNT(IF(title LIKE '%china%'
OR title LIKE '%chinese%',
1,
NULL)) AS 'China',
COUNT(IF(title LIKE '%hong kong%', 1, NULL)) AS 'Hong Kong',
COUNT(IF(title LIKE '%macau%'
OR title LIKE '%macanese%'
OR title LIKE '%macao%',
1,
NULL)) AS 'Macau',
COUNT(IF(title LIKE '%colombia%', 1, NULL)) AS 'Colombia',
COUNT(IF(title LIKE '%comoros%', 1, NULL)) AS 'Comoros',
COUNT(IF(title LIKE '%congo%'
AND NOT title LIKE '%democratic%',
1,
NULL)) AS 'Congo',
COUNT(IF(title LIKE '%drc%'
OR title LIKE '%democratic republic of congo%',
1,
NULL)) AS 'Democratic Republic of Congo',
COUNT(IF(title LIKE '%costa rica%', 1, NULL)) AS 'Costa Rica',
COUNT(IF(title LIKE '%croat%', 1, NULL)) AS 'Croatia',
COUNT(IF(title LIKE '%cuba%', 1, NULL)) AS 'Cuba',
COUNT(IF(title LIKE '%cypr%', 1, NULL)) AS 'Cyprus',
COUNT(IF(title LIKE '%czech%', 1, NULL)) AS 'Czech Republic',
COUNT(IF(title LIKE '%denmark%'
OR title LIKE '%danish%',
1,
NULL)) AS 'Denmark',
COUNT(IF(title LIKE '%djibouti%', 1, NULL)) AS 'Djibouti',
COUNT(IF(title LIKE '%dominica%'
OR title LIKE '%dominiquais%'
AND NOT title LIKE '%republic%'
AND NOT title LIKE '%dominicana%',
1,
NULL)) AS 'Dominica',
COUNT(IF(title LIKE '%dominican%', 1, NULL)) AS 'Dominican Republic',
COUNT(IF(title LIKE '%ecuador%', 1, NULL)) AS 'Ecuador',
COUNT(IF(title LIKE '%egypt%', 1, NULL)) AS 'Egypt',
COUNT(IF(title LIKE '%salvador%', 1, NULL)) AS 'El Salvador',
COUNT(IF(title LIKE '%equatorial%', 1, NULL)) AS 'Equatorial Guinea',
COUNT(IF(title LIKE '%eritrea%', 1, NULL)) AS 'Eritrea',
COUNT(IF(title LIKE '%estonia%', 1, NULL)) AS 'Estonia',
COUNT(IF(title LIKE '%ethiopia%', 1, NULL)) AS 'Ethiopia',
COUNT(IF(title LIKE '%fiji%', 1, NULL)) AS 'Fiji',
COUNT(IF(title LIKE '%finland%'
OR title LIKE '%finnish%',
1,
NULL)) AS 'Finland',
COUNT(IF(title LIKE '%france%'
OR title LIKE '%french%',
1,
NULL)) AS 'France',
COUNT(IF(title LIKE '%french guiana%',
1,
NULL)) AS 'French Guiana',
COUNT(IF(title LIKE '%gabon%'
AND NOT title LIKE '%vagabond%',
1,
NULL)) AS 'Gabon',
COUNT(IF(title LIKE '%gambia%', 1, NULL)) AS 'Gambia',
COUNT(IF(title LIKE '%georgia%', 1, NULL)) AS 'Georgia',
COUNT(IF(title LIKE '%german%', 1, NULL)) AS 'Germany',
COUNT(IF(title LIKE '%ghana%', 1, NULL)) AS 'Ghana',
COUNT(IF(title LIKE '%britain%'
OR title LIKE '%british%'
OR title LIKE '%united kingdom%'
AND NOT title LIKE '%british columbia%',
1,
NULL)) AS 'United Kingdom',
COUNT(IF(title LIKE '%greek%'
OR title LIKE '%greece%',
1,
NULL)) AS 'Greece',
COUNT(IF(title LIKE '%grenada%', 1, NULL)) AS 'Grenada',
COUNT(IF(title LIKE '%guadeloupe%', 1, NULL)) AS 'Guadeloupe',
COUNT(IF(title LIKE '%guatemala%', 1, NULL)) AS 'Guatemala',
COUNT(IF(title LIKE '%guinea%'
AND NOT title LIKE '%bissau%',
1,
NULL)) AS 'Guinea',
COUNT(IF(title LIKE '%bissau%', 1, NULL)) AS 'Guinea-Bissau',
COUNT(IF(title LIKE '%guyan%', 1, NULL)) AS 'Guyana',
COUNT(IF(title LIKE '%haiti%', 1, NULL)) AS 'Haiti',
COUNT(IF(title LIKE '%hondura%', 1, NULL)) AS 'Honduras',
COUNT(IF(title LIKE '%hungar%', 1, NULL)) AS 'Hungary',
COUNT(IF(title LIKE '%iceland%', 1, NULL)) AS 'Iceland',
COUNT(IF(title LIKE '%india%', 1, NULL)) AS 'India',
COUNT(IF(title LIKE '%indonesia%', 1, NULL)) AS 'Indonesia',
COUNT(IF(title LIKE '%iran%', 1, NULL)) AS 'Iran',
COUNT(IF(title LIKE '%iraq%', 1, NULL)) AS 'Iraq',
COUNT(IF(title LIKE '%ireland%'
OR title LIKE '%irish%',
1,
NULL)) AS 'Ireland',
COUNT(IF(title LIKE '%israel%', 1, NULL)) AS 'Israel',
COUNT(IF(title LIKE '%italy%'
OR title LIKE '%itali%',
1,
NULL)) AS 'Italy',
COUNT(IF(title LIKE '%ivory%'
OR title LIKE '%ivoire%'
AND NOT title LIKE '%carnivory%',
1,
NULL)) AS 'Ivory Coast',
COUNT(IF(title LIKE '%jamaica%', 1, NULL)) AS 'Jamaican',
COUNT(IF(title LIKE '%japan%', 1, NULL)) AS 'Japan',
COUNT(IF(title LIKE '%jordan%'
AND NOT title LIKE '%julie jordan%',
1,
NULL)) AS 'Jordan',
COUNT(IF(title LIKE '%kazakh%', 1, NULL)) AS 'Kazakhstan',
COUNT(IF(title LIKE '%kenya%', 1, NULL)) AS 'Kenya',
COUNT(IF(title LIKE '%korea%', 1, NULL)) AS 'Korea',
COUNT(IF(title LIKE '%kuwait%', 1, NULL)) AS 'Kuwait',
COUNT(IF(title LIKE '%kyrgyz%', 1, NULL)) AS 'Kyrgyzstan',
COUNT(IF(title LIKE '%laos%'
OR title LIKE '%lao roots%',
1,
NULL)) AS 'Laos',
COUNT(IF(title LIKE '%latvia%', 1, NULL)) AS 'Latvia',
COUNT(IF(title LIKE '%leban%', 1, NULL)) AS 'Lebanon',
COUNT(IF(title LIKE '%lesoth%', 1, NULL)) AS 'Lesotho',
COUNT(IF(title LIKE '%liberia%', 1, NULL)) AS 'Liberia',
COUNT(IF(title LIKE '%libya%', 1, NULL)) AS 'Libya',
COUNT(IF(title LIKE '%liechtenstein%',
1,
NULL)) AS 'Liechtenstein',
COUNT(IF(title LIKE '%lithuania%', 1, NULL)) AS 'Lithuania',
COUNT(IF(title LIKE '%luxembourg%', 1, NULL)) AS 'Luxembourg',
COUNT(IF(title LIKE '%macedonia%', 1, NULL)) AS 'Macedonia',
COUNT(IF(title LIKE '%madag%', 1, NULL)) AS 'Madagascar',
COUNT(IF(title LIKE '%malawi%', 1, NULL)) AS 'Malawi',
COUNT(IF(title LIKE '%malay%', 1, NULL)) AS 'Malaysia',
COUNT(IF(title LIKE '%maldiv%', 1, NULL)) AS 'Maldives',
COUNT(IF(title LIKE '% mali%'
AND NOT title LIKE '%somali%'
AND NOT title LIKE '%minimal%'
AND NOT title LIKE '%malibu%',
1,
NULL)) AS 'Mali',
COUNT(IF(title LIKE '%malta%'
OR title LIKE '%maltese%',
1,
NULL)) AS 'Malta',
COUNT(IF(title LIKE '%martiniqu%', 1, NULL)) AS 'Martinique',
COUNT(IF(title LIKE '%mauritania%', 1, NULL)) AS 'Mauritania',
COUNT(IF(title LIKE '%mauriti%', 1, NULL)) AS 'Mauritius',
COUNT(IF(title LIKE '%mayotte%', 1, NULL)) AS 'Mayotte',
COUNT(IF(title LIKE '%mexic%', 1, NULL)) AS 'Mexico',
COUNT(IF(title LIKE '%moldova%', 1, NULL)) AS 'Moldova',
COUNT(IF(title LIKE '%monaco%'
OR title LIKE '%monegasque%',
1,
NULL)) AS 'Monaco',
COUNT(IF(title LIKE '%mongol%', 1, NULL)) AS 'Mongolia',
COUNT(IF(title LIKE '%montegr%', 1, NULL)) AS 'Montenegro',
COUNT(IF(title LIKE '%montserrat%', 1, NULL)) AS 'Montserrat',
COUNT(IF(title LIKE '%morocc%', 1, NULL)) AS 'Morocco',
COUNT(IF(title LIKE '%mozambique%', 1, NULL)) AS 'Mozambique',
COUNT(IF(title LIKE '%burma%'
OR title LIKE '%burmese%'
OR title LIKE '%myanmar%',
1,
NULL)) AS 'Burma',
COUNT(IF(title LIKE '%namibia%', 1, NULL)) AS 'Namibia',
COUNT(IF(title LIKE '%nepal%', 1, NULL)) AS 'Nepal',
COUNT(IF(title LIKE '%netherlands%'
OR title LIKE '%holland%'
OR title LIKE '%dutch%'
AND NOT title LIKE '%dutch oven%',
1,
NULL)) AS 'The Netherlands',
COUNT(IF(title LIKE '%new zealand%', 1, NULL)) AS 'New Zealand',
COUNT(IF(title LIKE '%nicaragua%', 1, NULL)) AS 'Nicaragua',
COUNT(IF(title LIKE '%niger%'
AND NOT title LIKE '%nigeria%'
AND NOT title LIKE '%feniger%',
1,
NULL)) AS 'Niger',
COUNT(IF(title LIKE '%nigeria%', 1, NULL)) AS 'Nigeria',
COUNT(IF(title LIKE '%norw%', 1, NULL)) AS 'Norway',
COUNT(IF(title LIKE '% oman%', 1, NULL)) AS 'Oman',
COUNT(IF(title LIKE '%pakistan%', 1, NULL)) AS 'Pakistan',
COUNT(IF(title LIKE '%palestin%', 1, NULL)) AS 'Palestine',
COUNT(IF(title LIKE '%panama%', 1, NULL)) AS 'Panama',
COUNT(IF(title LIKE '%papua new guinea%',
1,
NULL)) AS 'Papua New Guinea',
COUNT(IF(title LIKE '%paraguay%', 1, NULL)) AS 'Paraguay',
COUNT(IF(title LIKE '%peru%', 1, NULL)) AS 'Peru',
COUNT(IF(title LIKE '%philippin%'
OR title LIKE '%filipin%',
1,
NULL)) AS 'Philippines',
COUNT(IF(title LIKE '%poland%'
OR title LIKE '%polish%',
1,
NULL)) AS 'Poland',
COUNT(IF(title LIKE '%portug%', 1, NULL)) AS 'Portugal',
COUNT(IF(title LIKE '%puerto ric%', 1, NULL)) AS 'Puerto Rico',
COUNT(IF(title LIKE '%qatar%', 1, NULL)) AS 'Qatar',
COUNT(IF(title LIKE '%romania%', 1, NULL)) AS 'Romania',
COUNT(IF(title LIKE '%russia%', 1, NULL)) AS 'Russia',
COUNT(IF(title LIKE '%rwanda%', 1, NULL)) AS 'Rwanda',
COUNT(IF(title LIKE '%saint kitts%', 1, NULL)) AS 'Saint Kitts and Nevis',
COUNT(IF(title LIKE '%saint lucia%', 1, NULL)) AS 'Saint Lucia',
COUNT(IF(title LIKE '%saint vincent%',
1,
NULL)) AS 'Saint Vincent and the Grenadines',
COUNT(IF(title LIKE '%samoa%', 1, NULL)) AS 'Samoa',
COUNT(IF(title LIKE '%sao tome%', 1, NULL)) AS 'Sao Tome and Principe',
COUNT(IF(title LIKE '%saudi arabia%', 1, NULL)) AS 'Saudi Arabia',
COUNT(IF(title LIKE '%senegal%', 1, NULL)) AS 'Senegal',
COUNT(IF(title LIKE '%serbia%', 1, NULL)) AS 'Serbia',
COUNT(IF(title LIKE '%seychell%', 1, NULL)) AS 'Seychelles',
COUNT(IF(title LIKE '%sierra leone%', 1, NULL)) AS 'Sierra Leone',
COUNT(IF(title LIKE '%singapor%', 1, NULL)) AS 'Singapore',
COUNT(IF(title LIKE '%slovakia%', 1, NULL)) AS 'Slovakia',
COUNT(IF(title LIKE '%slovenia%', 1, NULL)) AS 'Slovenia',
COUNT(IF(title LIKE '%solomon islands%',
1,
NULL)) AS 'Solomon Islands',
COUNT(IF(title LIKE '%somalia%', 1, NULL)) AS 'Somalia',
COUNT(IF(title LIKE '%south africa%', 1, NULL)) AS 'South Africa',
COUNT(IF(title LIKE '%spain%'
OR title LIKE '%spanish%',
1,
NULL)) AS 'Spain',
COUNT(IF(title LIKE '%sri lanka%', 1, NULL)) AS 'Sri Lanka',
COUNT(IF(title LIKE '%sudan%', 1, NULL)) AS 'Sudan',
COUNT(IF(title LIKE '%suriname%', 1, NULL)) AS 'Suriname',
COUNT(IF(title LIKE '%swazi%', 1, NULL)) AS 'Swaziland',
COUNT(IF(title LIKE '%sweden%'
OR title LIKE '%swedish%',
1,
NULL)) AS 'Sweden',
COUNT(IF(title LIKE '%switzerland%'
OR title LIKE '%swiss%',
1,
NULL)) AS 'Switzerland',
COUNT(IF(title LIKE '%syria%', 1, NULL)) AS 'Syria',
COUNT(IF(title LIKE '%taiwan%', 1, NULL)) AS 'Taiwan',
COUNT(IF(title LIKE '%tajik%', 1, NULL)) AS 'Tajikistan',
COUNT(IF(title LIKE '%tanzania%', 1, NULL)) AS 'Tanzania',
COUNT(IF(title LIKE '%thai%', 1, NULL)) AS 'Thailand',
COUNT(IF(title LIKE '%togo%'
AND NOT title LIKE '%prostogo%',
1,
NULL)) AS 'Togo',
COUNT(IF(title LIKE '%tonga%', 1, NULL)) AS 'Tonga',
COUNT(IF(title LIKE '%trinidad%'
OR title LIKE '%tobag%',
1,
NULL)) AS 'Trinidad & Tobago',
COUNT(IF(title LIKE '%tunis%', 1, NULL)) AS 'Tunisia',
COUNT(IF(title LIKE '%turkey%'
OR title LIKE '%turkish%',
1,
NULL)) AS 'Turkey',
COUNT(IF(title LIKE '%turkmen%', 1, NULL)) AS 'Turkmenistan',
COUNT(IF(title LIKE '%caicos%', 1, NULL)) AS 'Turks & Caicos',
COUNT(IF(title LIKE '%tuval%', 1, NULL)) AS 'Tuvalu',
COUNT(IF(title LIKE '%uganda%', 1, NULL)) AS 'Uganda',
COUNT(IF(title LIKE '%ukrain%', 1, NULL)) AS 'Ukraine',
COUNT(IF(title LIKE '%united arab emirates%'
OR title LIKE '%uae%',
1,
NULL)) AS 'United Arab Emirates',
COUNT(IF(title LIKE '%uruguay%', 1, NULL)) AS 'Uruguay',
COUNT(IF(title LIKE '%uzbek%', 1, NULL)) AS 'Uzbekistan',
COUNT(IF(title LIKE '%vanuat%', 1, NULL)) AS 'Vanuatu',
COUNT(IF(title LIKE '%venezuela%', 1, NULL)) AS 'Venezuela',
COUNT(IF(title LIKE '%viet%' AND NOT title LIKE '%soviet%', 1, NULL)) AS 'Vietnam',
COUNT(IF(title LIKE '%yemen%', 1, NULL)) AS 'Yemen',
COUNT(IF(title LIKE '%zambia%', 1, NULL)) AS 'Zambia',
COUNT(IF(title LIKE '%zimbabwe%', 1, NULL)) AS 'Zimbabwe'
FROM
spl_2016.inraw
WHERE
YEAR(cout) BETWEEN 2006 AND 2018
AND deweyClass >= 641
AND deweyClass < 642
GROUP BY MONTH(cout) , YEAR(cout)
ORDER BY YEAR(cout) , MONTH(cout)
This query took 215 seconds.
I then also queried the top 8 items for each country, along with the respective item type and number of check-ins. I'm not sure if there is a more elegant way to accomplish this, but I basically had to do (QUERY) UNION (QUERY) UNION (QUERY) ... (QUERY) for each country, where an example of such a QUERY would be (the entire thing is too long to paste here):
This query took 2.5 hours (9204 seconds).
The number of rows in the output is not 8 times the number of countries, as some countries don't have fewer than 8 different items or no related check-ins at all. I had to manually pad out the .csv file with empty rows so that each country had 8 (empty or non-empty) rows, to facilitate importing the result into Processing. I couldn't figure out if there's a way to do this in the MySQL query.
Edit 2/7/19: I also realized that if there was a title that matched multiple queries, it would only show up in the results for the first query. For instance, the top result of an individual query for Singapore is "Cradle of flavor home cooking from the spice islands of Indonesia Malaysia and Singapore", but this result won't show up under Singapore in the full query, as it will be part of the results for Indonesia. I would have to manually patch together several queries to get the correct set of top 8 items for each country.
My plan was to arrange the data using concentric circles. The outermost circle would represent the country with the highest total checkout counts, with each smaller circle representing the next most popular countries.
I also wanted to show, when a circle is hovered over, the corresponding country's number of counts per month as a curve wrapped around the circle. Lines radiating from the center would mark out each year.
I considered having the radius of each circle be proportional to the corresponding country's total number of checkouts; the radii proportional to the square root of the checkouts; and the radii evenly spaced apart.
I ended up choosing two modes: one with the radii evenly spaced apart, and one with the radii proportional to the square root of the checkouts. Pressing any key will toggle between the two modes.
Hovering over a circle will show the number of counts per month. In the lower portion of the screen, the top items (up to 8) are listed for the country, along with a colored bar to the left of the title. The length of the bar indicates the number of check-ins and the color of the bar indicates whether the item is a book, CD, DVD, or VHS. The bottom right corner shows the total number of checkouts for the country.
Unfortunately for some of the most popular cuisines (#1 Italy, #2 France, #3 India), the rollover graph goes off the screen. (I tried plotting the log counts or sqrt counts, but it smoothed out too much of the variation between months, so I decided not to do that.)
Some results that caught my eye included the noticeable peak of interest in Chinese food around 2008-2011 followed by a decline:
and interest in the United Kingdom jumped significantly in 2016. Moreover, the top 5 items for the UK are all related to the Great British Bake Off, and half of the most popular items are DVDs for that show:
Here is my Processing code and data.