Chinese Culture Impact in Seattle Library
MAT 259, 2022
Ziyu Zhang
Concept
Through the analysis of the data in Seattle Public Library, I want to find
out the Chinese cultural impact on the people in the United States from 2006 till today. With
the continuous development of culture and technology in China, the main content and symbols of
Chinese culture exposure have also kept pace with times and made corresponding changes. Setting
books as the object of analysis, I first collected statistics on the types of books people
borrowed the most. Taking 2006-2010\2011-2015\2016-2020 as the time boundary, I planned to
analyze the trend of Chinese cultural exposure through changes in the number of times people
borrowed related books in three time periods. The project 2 is the extension of my project 1,
which visualizes the trend of the check-out times of the top 10 popular Chinese culture related books in 3 different time periods.
Query
SELECT
title, bibNumber, COUNT(bibNumber) AS counts, itemtype
FROM
spl_2016.outraw
WHERE
(itemtype like '%bk')
AND (title LIKE '%Chinese%'
OR title LIKE '%China%')
AND(Year(cout) >= 2006 and Year(cout) <= 2010)
GROUP BY bibNumber , itemtype , title
ORDER BY counts DESC
SELECT
title,
bibNumber,
COUNT(bibNumber) AS counts,
SUM(CASE
WHEN (YEAR(cout) = 2006 AND MONTH(cout) = 1) THEN 1
ELSE 0
END) AS '2006-1',
SUM(CASE
WHEN (YEAR(cout) = 2006 AND MONTH(cout) = 2) THEN 1
ELSE 0
END) AS '2006-2',
...
FROM
spl_2016.outraw
WHERE
(bibNumber = '2381540')
OR (bibNumber = '2404748')
OR (bibNumber = '2471718')
OR (bibNumber = '2347391')
OR (bibNumber = '2280147')
OR (bibNumber = '2174575')
OR (bibNumber = '1100731')
OR (bibNumber = '2067046')
OR (bibNumber = '2484700')
OR (bibNumber = '2530375')
GROUP BY bibNumber , itemtype , title
ORDER BY counts DESC
title, bibNumber, COUNT(bibNumber) AS counts, itemtype
FROM
spl_2016.outraw
WHERE
(itemtype like '%bk')
AND (title LIKE '%Chinese%'
OR title LIKE '%China%')
AND(Year(cout) >= 2006 and Year(cout) <= 2010)
GROUP BY bibNumber , itemtype , title
ORDER BY counts DESC
SELECT
title,
bibNumber,
COUNT(bibNumber) AS counts,
SUM(CASE
WHEN (YEAR(cout) = 2006 AND MONTH(cout) = 1) THEN 1
ELSE 0
END) AS '2006-1',
SUM(CASE
WHEN (YEAR(cout) = 2006 AND MONTH(cout) = 2) THEN 1
ELSE 0
END) AS '2006-2',
...
FROM
spl_2016.outraw
WHERE
(bibNumber = '2381540')
OR (bibNumber = '2404748')
OR (bibNumber = '2471718')
OR (bibNumber = '2347391')
OR (bibNumber = '2280147')
OR (bibNumber = '2174575')
OR (bibNumber = '1100731')
OR (bibNumber = '2067046')
OR (bibNumber = '2484700')
OR (bibNumber = '2530375')
GROUP BY bibNumber , itemtype , title
ORDER BY counts DESC
Process
This is the origianl version of my project visualization that I gave up. Because it's too concrete for the data visualization and kind of
limit the shape and development of variable data.
Final result
The wave lines in the visualization represent the trend of the check-out times of Chinese culture related books. Different combination of colors represent different time zones. And I renewed some controls and functions which can allow viewers to set different values range and position of the data.
Code