In [1]:
import pandas
rev = pandas.DataFrame([[115,'Hui'],[199,'Xuli'],[24,'Krithika'],[666,'Jonathan']],columns=['rid','name'])
mov = pandas.DataFrame([[100,'The Godfather'],[98,'Spotlight'],[23,'Amelie'],[900,'HP I'],[902,'HP II']], columns=['mid','movie'])
rat = pandas.DataFrame([[115,100,4],[199,98,5],[24,23,4],[666,900,5],[666,902,3.8]],columns=['rid','mid','stars'])
In [2]:
rev
Out[2]:
rid name
0 115 Hui
1 199 Xuli
2 24 Krithika
3 666 Jonathan
In [3]:
mov
Out[3]:
mid movie
0 100 The Godfather
1 98 Spotlight
2 23 Amelie
3 900 HP I
4 902 HP II
In [4]:
rat
Out[4]:
rid mid stars
0 115 100 4.0
1 199 98 5.0
2 24 23 4.0
3 666 900 5.0
4 666 902 3.8
In [5]:
pandas.merge( mov, rat, on='mid')  # default join is inner
Out[5]:
mid movie rid stars
0 100 The Godfather 115 4.0
1 98 Spotlight 199 5.0
2 23 Amelie 24 4.0
3 900 HP I 666 5.0
4 902 HP II 666 3.8
In [6]:
pandas.merge( mov, rat, on='mid', how='left')  # left outer join 
Out[6]:
mid movie rid stars
0 100 The Godfather 115 4.0
1 98 Spotlight 199 5.0
2 23 Amelie 24 4.0
3 900 HP I 666 5.0
4 902 HP II 666 3.8
In [7]:
pandas.merge( rev, pandas.merge( mov, rat, on='mid', how='left'), on='rid', how='left' )
Out[7]:
rid name mid movie stars
0 115 Hui 100 The Godfather 4.0
1 199 Xuli 98 Spotlight 5.0
2 24 Krithika 23 Amelie 4.0
3 666 Jonathan 900 HP I 5.0
4 666 Jonathan 902 HP II 3.8
In [8]:
import sqlite3
con = sqlite3.connect('moviedb_jb.db')
# Get the metadata of the SQLite database
metadata = pandas.io.sql.read_sql('select * from sqlite_master', con)
#print type(metadata)
#print metadata.columns
#print metadata.index
# Get all the tables into a dictionary
db = {}
for i,row in metadata.iterrows():
         #print i,row['type']
         if row['type']=='table':
             db[ row['name'] ] = pandas.io.sql.read_sql('select * from ' + row['name'], con)
for tablename in db:
         print(tablename)
Reviewer
Movie
Rating
In [10]:
rev = db['Reviewer']
mov = db['Movie']
rat = db['Rating']
In [11]:
rev.head()
Out[11]:
rID name
0 17 Adhish
1 184 Sanjeevani Choudhery
2 100 John Ringland
3 419 Samuel Siegart
4 101 Dwayne The Rock Johnson
In [12]:
mov.head()
Out[12]:
mID title year director
0 56 Captain America-The First Avenger 2011 Joe Johnson
1 1 Birdman 2014 Alejandro González Iñárritu
2 2 Pulp Fiction 1994 Quentin Tarantino
3 3000 Bee Movie 2007 Steve Hickner
4 4 Jurassic Park 1993 Steven Spielberg
In [13]:
rat.head()
Out[13]:
rID mID stars ratingDate
0 17 56 4.8 2017-03-07
1 100 1 5.0 2017-03-07
2 666 3003 5.0 2017-03-07
3 999 133 5.0 2017-03-07
4 999 134 5.0 2017-03-07
In [14]:
#movies between 2008 and 2013 w/ rating >3
table1 = pandas.merge(mov,rat,on='mID')
table1[(table1['year']>2008)&(table1['year']<2013)&(table1['stars']>3)]
Out[14]:
mID title year director rID stars ratingDate
0 56 Captain America-The First Avenger 2011 Joe Johnson 17 4.8 2017-03-07
20 60 Zombieland 2009 Ruben Fleischer 13 4.0 2017-03-07
49 905 Harry Potter and The Half-Blood Prince 2009 David Yates 100 4.0 2017-03-07
52 232 Moon 2009 Duncan Jones 536 4.0 2017-03-07
In [15]:
# Highest Rated Directors

movies=pandas.merge(mov,rat,on='mID')
directors=movies.groupby('director')
for director, group in directors:
    print(director)
    print(group)
Alejandro González Iñárritu
   mID    title  year                     director  rID  stars  ratingDate
2    1  Birdman  2014  Alejandro González Iñárritu  100    5.0  2017-03-07
Alfonso Cuaron
    mID                                     title  year        director  rID  \
45  902  Harry Potter and The Prisoner of Azkaban  2004  Alfonso Cuaron  666   
46  902  Harry Potter and The Prisoner of Azkaban  2004  Alfonso Cuaron  100   

    stars  ratingDate  
45    3.8  2017-03-16  
46    4.8  2017-03-16  
Andrew Niccol
    mID    title  year       director  rID  stars  ratingDate
66  555  Gattaca  1997  Andrew Niccol  555    5.0  2017-03-07
Barry Cook
    mID  title  year    director  rID  stars  ratingDate
51  410  Mulan  1998  Barry Cook    2    3.0  2017-03-07
Bruce Timm
    mID                         title  year    director  rID  stars  \
57  133  Batman: Mask of the Phantasm  1993  Bruce Timm  999    5.0   

    ratingDate  
57  2017-03-07  
Catherine Hardwicke
    mID     title  year             director  rID  stars  ratingDate
53  888  Twilight  2008  Catherine Hardwicke   20    5.0  2017-03-07
Chris Buck Jennifer Lee
    mID   title  year                 director  rID  stars  ratingDate
16    6  Frozen  2013  Chris Buck Jennifer Lee   67    5.0  2017-03-07
Chris Columbus
    mID                                title  year        director  rID  \
39  900     Harry Potter and Sorcerers Stone  2001  Chris Columbus  496   
40  900     Harry Potter and Sorcerers Stone  2001  Chris Columbus  100   
41  900     Harry Potter and Sorcerers Stone  2001  Chris Columbus  666   
42  901  Harry Potter and Chamber of Secrets  2002  Chris Columbus   13   
43  901  Harry Potter and Chamber of Secrets  2002  Chris Columbus  100   
44  901  Harry Potter and Chamber of Secrets  2002  Chris Columbus  666   

    stars  ratingDate  
39    4.0  2017-03-17  
40    4.8  2017-03-16  
41    5.0  2017-03-16  
42    4.0  2017-03-07  
43    3.5  2017-03-16  
44    4.1  2017-03-07  
Colin Trevorrow
    mID           title  year         director  rID  stars  ratingDate
14   41  Jurassic World  2015  Colin Trevorrow  999    5.0  2017-03-07
15   41  Jurassic World  2015  Colin Trevorrow   97    5.0  2017-03-07
Danny Boyle
    mID                title  year     director  rID  stars  ratingDate
26  746  Slumdog Millionaire  2008  Danny Boyle  425    5.0  2017-03-07
David Fincher
    mID       title  year       director  rID  stars  ratingDate
62   17  Fight Club  1999  David Fincher   17    5.0  2017-03-07
David Yates
    mID                                          title  year     director  \
48  904      Harry Potter and The Order of the Phoenix  2007  David Yates   
49  905         Harry Potter and The Half-Blood Prince  2009  David Yates   
50  906  Harry Potter and The Deathly Hallows - Part 1  2010  David Yates   

    rID  stars  ratingDate  
48  666    4.3  2017-03-07  
49  100    4.0  2017-03-07  
50  100    2.0  2017-03-16  
Duncan Jones
    mID title  year      director  rID  stars  ratingDate
52  232  Moon  2009  Duncan Jones  536    4.0  2017-03-07
Francis Ford Coppola
    mID          title  year              director  rID  stars  ratingDate
56  100  The Godfather  1974  Francis Ford Coppola  115    4.0  2017-03-07
Garth Davis
    mID title  year     director  rID  stars  ratingDate
17    7  Lion  2016  Garth Davis   67    5.0  2017-03-07
George Lucas
    mID                                           title  year      director  \
34   42              Star Wars: Episode IV - A New Hope  1977  George Lucas   
35   42              Star Wars: Episode IV - A New Hope  1977  George Lucas   
36   42              Star Wars: Episode IV - A New Hope  1977  George Lucas   
37   42              Star Wars: Episode IV - A New Hope  1977  George Lucas   
38   42              Star Wars: Episode IV - A New Hope  1977  George Lucas   
54   43  Star Wars: Episode V - The Empire Strikes Back  1980  George Lucas   
55   43  Star Wars: Episode V - The Empire Strikes Back  1980  George Lucas   

    rID  stars  ratingDate  
34   20    0.0  2017-03-07  
35  555    5.0  2017-03-07  
36   97    4.0  2017-03-07  
37  410    5.0  2017-03-07  
38  100    2.5  2017-03-07  
54  666    4.3  2017-03-07  
55  666    1.0  2017-03-16  
Giuseppe Tornatore
    mID               title  year            director  rID  stars  ratingDate
64  466  The legend of 1900  1998  Giuseppe Tornatore  444    5.0  2017-03-07
Jean-Pierre Jeunet
    mID   title  year            director  rID  stars  ratingDate
33   23  Amelie  2001  Jean-Pierre Jeunet   24    4.0  2017-03-07
Joe Johnson
   mID                              title  year     director  rID  stars  \
0   56  Captain America-The First Avenger  2011  Joe Johnson   17    4.8   
1   56  Captain America-The First Avenger  2011  Joe Johnson  419    1.0   

   ratingDate  
0  2017-03-07  
1  2017-03-17  
Joe Johnston
    mID              title  year      director  rID  stars  ratingDate
13   39  Jurassic Park III  2001  Joe Johnston    2    3.0  2017-03-07
John Lasseter
    mID title  year       director  rID  stars  ratingDate
21  745  Cars  2006  John Lasseter  555    1.0  2017-03-07
22  745  Cars  2006  John Lasseter  425    3.0  2017-03-07
John Woo
     mID      title  year  director  rID  stars  ratingDate
63  3003  Red Cliff  2008  John Woo  666    5.0  2017-03-07
Jordan Peele
    mID    title  year      director  rID  stars  ratingDate
30   12  Get Out  2017  Jordan Peele  101    5.0  2017-03-07
31   12  Get Out  2017  Jordan Peele  466    5.0  2017-03-07
M. Night Shyamalan
    mID        title  year            director  rID  stars  ratingDate
65   72  The Village  2004  M. Night Shyamalan  164    5.0  2017-03-07
Michael Bay
    mID                               title  year     director  rID  stars  \
60  422  Transformers:Revenge of the Fallen  2009  Michael Bay  999    1.0   
61  422  Transformers:Revenge of the Fallen  2009  Michael Bay  555    0.0   

    ratingDate  
60  2017-03-07  
61  2017-03-07  
Mike Newell
    mID                                title  year     director  rID  stars  \
47  903  Harry Potter and The Goblet of Fire  2005  Mike Newell  100    4.0   

    ratingDate  
47  2017-03-07  
Quentin Tarantino
   mID         title  year           director  rID  stars  ratingDate
3    2  Pulp Fiction  1994  Quentin Tarantino  120    4.0  2017-03-17
4    2  Pulp Fiction  1994  Quentin Tarantino  555    5.0  2017-03-07
5    2  Pulp Fiction  1994  Quentin Tarantino   12    5.0   2017-3-07
Ruben Fleischer
    mID       title  year         director  rID  stars  ratingDate
20   60  Zombieland  2009  Ruben Fleischer   13    4.0  2017-03-07
Stanley Kubrick
    mID                  title  year         director  rID  stars  ratingDate
18   70            The Shining  1980  Stanley Kubrick   13    4.0  2017-03-07
19   70            The Shining  1980  Stanley Kubrick  542    5.0  2017-03-07
23   71  2001: A Space Odyssey  1968  Stanley Kubrick  410    5.0  2017-03-07
24   71  2001: A Space Odyssey  1968  Stanley Kubrick  419    4.0  2017-03-07
25   71  2001: A Space Odyssey  1968  Stanley Kubrick  100    2.2  2017-03-07
Steve Barron
    mID                         title  year      director  rID  stars  \
58  134  Teenage Mutant Ninja Turtles  1990  Steve Barron  999    5.0   
59  134  Teenage Mutant Ninja Turtles  1990  Steve Barron  555    5.0   

    ratingDate  
58  2017-03-07  
59  2017-03-07  
Steve Hickner
    mID      title  year       director  rID  stars  ratingDate
6  3000  Bee Movie  2007  Steve Hickner  419    5.0  2017-03-07
7  3000  Bee Movie  2007  Steve Hickner  100    4.9  2017-03-07
Steven Spielberg
    mID                          title  year          director  rID  stars  \
8     4                  Jurassic Park  1993  Steven Spielberg    2    5.0   
9     4                  Jurassic Park  1993  Steven Spielberg  555    3.0   
10    5  The Lost World: Jurassic Park  1997  Steven Spielberg  999    2.0   
11    5  The Lost World: Jurassic Park  1997  Steven Spielberg    2    1.0   
12    5  The Lost World: Jurassic Park  1997  Steven Spielberg   67    2.0   

    ratingDate  
8   2017-03-07  
9   2017-03-07  
10  2017-03-07  
11  2017-03-07  
12  2017-03-07  
Theodore Melfi
    mID           title  year        director  rID  stars  ratingDate
27   96  Hidden Figures  2016  Theodore Melfi    3    5.0  2017-03-07
28   96  Hidden Figures  2016  Theodore Melfi   67    5.0  2017-03-07
Tim Burton
    mID              title  year    director  rID  stars  ratingDate
29   97  Batman: The Movie  1966  Tim Burton    3    1.0  2017-03-07
Tom McCarthy
    mID      title  year      director  rID  stars  ratingDate
32   98  Spotlight  2016  Tom McCarthy  199    5.0  2017-03-07
In [17]:
avgstars=directors.agg({'stars':'mean'})
avgstars
Out[17]:
stars
director
Alejandro González Iñárritu 5.000000
Alfonso Cuaron 4.300000
Andrew Niccol 5.000000
Barry Cook 3.000000
Bruce Timm 5.000000
Catherine Hardwicke 5.000000
Chris Buck Jennifer Lee 5.000000
Chris Columbus 4.233333
Colin Trevorrow 5.000000
Danny Boyle 5.000000
David Fincher 5.000000
David Yates 3.433333
Duncan Jones 4.000000
Francis Ford Coppola 4.000000
Garth Davis 5.000000
George Lucas 3.114286
Giuseppe Tornatore 5.000000
Jean-Pierre Jeunet 4.000000
Joe Johnson 2.900000
Joe Johnston 3.000000
John Lasseter 2.000000
John Woo 5.000000
Jordan Peele 5.000000
M. Night Shyamalan 5.000000
Michael Bay 0.500000
Mike Newell 4.000000
Quentin Tarantino 4.666667
Ruben Fleischer 4.000000
Stanley Kubrick 4.040000
Steve Barron 5.000000
Steve Hickner 4.950000
Steven Spielberg 2.600000
Theodore Melfi 5.000000
Tim Burton 1.000000
Tom McCarthy 5.000000
In [18]:
avgstars[avgstars['stars']==avgstars['stars'].max()]
Out[18]:
stars
director
Alejandro González Iñárritu 5.0
Andrew Niccol 5.0
Bruce Timm 5.0
Catherine Hardwicke 5.0
Chris Buck Jennifer Lee 5.0
Colin Trevorrow 5.0
Danny Boyle 5.0
David Fincher 5.0
Garth Davis 5.0
Giuseppe Tornatore 5.0
John Woo 5.0
Jordan Peele 5.0
M. Night Shyamalan 5.0
Steve Barron 5.0
Theodore Melfi 5.0
Tom McCarthy 5.0
In [ ]: