Using SQL to join several tables from the dataset before visualization.
INTRODUCTION
STARTING OFF WITH BACKGROUND WORK
LETS TALK ABOUT HARRY POTTER MAJORLY AS A STAR CHARACTERNAME
HARRY POTTER, HOW ABOUT SMALL TALK?
HARRY POTTER, HOW ABOUT SOME SPELLS?
MOST PROMINENT MOVIE FOR HARRY POTTER
HARRY POTTER, HOW ABOUT SOME FAMILIAR PLACES?
YAYY THAT'S ALL!
It's no news who the star actor was in the harry potter series, right? if u didn't know it's okay, i am a big fan of harry potter who was a star character in harry potter series and he was unfazed despite several challenges he encountered as a neophyte wizard. In this documentation i would be profiling harry potter from a comprehensive dataset i obtained from Click here and to explore this dataset io order to derive key insights about harry potter.
First we do some query to join tables.
- using POSTGRESQL throughout my analysis
- Created a database on PSQL AND THEN CREATED A TABLE HEADER
- Made use of SQl shell to copy each csv files to my database on PGAdmin
There are several tables in the Harry Potter's dataset obtained from kaggle which includes characters,chapters,dialogues,place,movie and spells table, as a way of practice i would be using the PSQL shell to copy these tables individually to a database in postgresql.
CREATE DATABASE PROJECT.SQLCREATE TABLE CHARACTERS(CHARACTERID INT PRIMARY KEY,CHARACTERNAME VARCHAR(250),SPECIES VARCHAR(100),GENDER VARCHAR(150),HOUSE VARCHAR(150),PATRONUS VARCHAR(100),WANDWOOD VARCHAR(100),WANDCORE VARCHAR(100));\C COPY CHARACTERS FROM 'FILEPATH' (FORMAT CSV, HEADER TRUE, DELIMETER ',', ENCODING UTF8);
This type of commands carried out using my psql shell would be recreated for subsequent tables.
Hi, Harry Potter!
After copying the CSV files to a database in PGSQL through the psql shell, I will be reading into the file to obtain necessary information about harry potter:
Yeah there was the potential of Harry Potter joining the house Slytherin because of some perceived propensities to darkness, but I did know he ended up in house Gryffindor but I had no idea of his wand which is a holly wand with Phoenix feather as a core with excessive magical potential. let's have a SQL query to have more information;
In [1]:
SELECT characterid,species,gender,house,patronus,charactername,wandwood,wandcore
from characters
WHERE charactername = 'Harry Potter';
Out[1]:
characterid species gender house patronus charactername wandwood wandcore 1 Human Male Gryffindor Stag Harry Potter Holly Phoenix Feather
Ollivander also said "holly wood wands work most happily for those who may need help overcoming a tendency to anger and impetuosity". This is not strange with Harry potter, Holly wand is always drawn to witches or wizards who are destined for greatness and wizards who have this tendencies/inclination to dangerous quest.
According to a digital company owned by J.K ROWLING 'pottermore' :
Wands are regarded as the "singular most important tools" that help to channel the inner abilities of each witch or wizard. Knowing this, it's interesting to note what these wand features reveal about the book's key characters.
Its very important to know more about harry potter, more information could be obtained by joining the character table with the dialogue's table using some query in PSQL to join these tables. after making a join using the necessary SQL query language,then i can obtain everything that was said by harry potter:
Now to obtain some intriguing insights, I generated a of characters word cloud to see what harry potter says often and it was no surprises there are lots of words said by him.
Harry Potter is all about reaching out to people in search for knowledge, speaking on how he feels and what to do inorder to get better . His most used words are "Hermione" , "Professor", "know", "think", "Now", "One", "Now","Sir","look", "Draco malfoy and Voldemort" his long time adverseries . There are words which are also prominent like "come", "always", "thing", "brilliant", "really", "need", "Hagrid". Also Harry Potter goes to a place category named "Hogwarts" often.
We were also able to return the name of characters with the longest line of dialogue:
It is well documented on how powerful and sometimes dangerous he can be with his childhood encounter with Lord voldemort. he was something more than gifted, he is a special wizard but not too skilled with his spells. Let's find out the most spells that he pronounced often form his holly wand.
Harry potter uses ACCIO! a lot and it produces no light, It is a summoning charm which is often used to summon an object.
Also, Harry Potter made use of several spells: "Expelliarmus" to disarm an opponent, 'Alarte ascendare' to launch an object into the air, 'Patronum' to conjure a spirit guardian, "Momentum" to slow something velocity, 'Alohomora'' to unlock a charm,and 'Avada Kedavra' is a killing curse.
Now i want to obtain the movie Harry potter talked the most and least. To obtain this i must match the chapters table(the Dialogue table has a chapterkey) and the movies table to match the chapters to movie by id.
Finally, we want to determine the Locations Harry potter can be found most often, No surprises he can be found mostly at Hogwarts. It's the main stage for all the movies and obviously Harry Potter would be found mostly there. Therefore, i would be removing Hogwarts as a category from the query and the aim is to obtain other categories where Harry potter can be located.
The merging of the characters and places table has been done and we can obtain Harry potter most familiar places:
I will visualize to determine Harry Potter favourite spots in at least movie time:
The highest of them all is the "The Hog's head" with 22% of in which they are all non Hogwarts for Harry Potter. With 45 Dialogues!
The Second most important Location is shared with both Budleigh Babberton and Ollivanders having 20%
In This visualization i only returned the top 4 location name with Harry Potter having the highest Dialogues. Honeydukes and Quidditch World cup were not in the spotlight because they had lower percentages for where Harry potter could be found.Yes! we have obtained where Harry potter spent most of his time when out of hogwarts, Now we need to categorize them using a tree map:
Notice how Harry Potter visits mostly Dwellings with 73 appearances, with Other magical Locations(unspecified locations) as the next mostly visited with 37 and Diagon alley closely followed with 36 appearances. Hogsmeade came last in th locations mostly Visited by Harry Potter.
We have been able to Learn important information about Harry Potter, yeah it's a wrap!