In [11]:
import MySQLdb
import pandas as pd
import numpy as np
In [12]:
db = MySQLdb.connect(
"db.fastcamp.us",
"root",
"dkstncks",
"sakila",
charset='utf8'
)
In [18]:
film_df = pd.read_sql("SELECT * FROM film;",db)
film_df.head(1)
Out[18]:
film_id
title
description
release_year
language_id
original_language_id
rental_duration
rental_rate
length
replacement_cost
rating
special_features
last_update
0
1
ACADEMY DINOSAUR
A Epic Drama of a Feminist And a Mad Scientist...
2006
1
None
6
0.99
86
20.99
PG
Deleted Scenes,Behind the Scenes
2006-02-15 05:03:42
In [22]:
is_year = film_df.release_year.isin([2006,2007])
is_grade = film_df.rating.isin(["PG","G"])
film_df[is_year & is_grade]
Out[22]:
film_id
title
description
release_year
language_id
original_language_id
rental_duration
rental_rate
length
replacement_cost
rating
special_features
last_update
0
1
ACADEMY DINOSAUR
A Epic Drama of a Feminist And a Mad Scientist...
2006
1
None
6
0.99
86
20.99
PG
Deleted Scenes,Behind the Scenes
2006-02-15 05:03:42
1
2
ACE GOLDFINGER
A Astounding Epistle of a Database Administrat...
2006
1
None
3
4.99
48
12.99
G
Trailers,Deleted Scenes
2006-02-15 05:03:42
3
4
AFFAIR PREJUDICE
A Fanciful Documentary of a Frisbee And a Lumb...
2006
1
None
5
2.99
117
26.99
G
Commentaries,Behind the Scenes
2006-02-15 05:03:42
4
5
AFRICAN EGG
A Fast-Paced Documentary of a Pastry Chef And ...
2006
1
None
6
2.99
130
22.99
G
Deleted Scenes
2006-02-15 05:03:42
5
6
AGENT TRUMAN
A Intrepid Panorama of a Robot And a Boy who m...
2006
1
None
3
2.99
169
17.99
PG
Deleted Scenes
2006-02-15 05:03:42
10
11
ALAMO VIDEOTAPE
A Boring Epistle of a Butler And a Cat who mus...
2006
1
None
6
0.99
126
16.99
G
Commentaries,Behind the Scenes
2006-02-15 05:03:42
11
12
ALASKA PHANTOM
A Fanciful Saga of a Hunter And a Pastry Chef ...
2006
1
None
6
0.99
136
22.99
PG
Commentaries,Deleted Scenes
2006-02-15 05:03:42
12
13
ALI FOREVER
A Action-Packed Drama of a Dentist And a Croco...
2006
1
None
4
4.99
150
21.99
PG
Deleted Scenes,Behind the Scenes
2006-02-15 05:03:42
18
19
AMADEUS HOLY
A Emotional Display of a Pioneer And a Technic...
2006
1
None
6
0.99
113
20.99
PG
Commentaries,Deleted Scenes,Behind the Scenes
2006-02-15 05:03:42
21
22
AMISTAD MIDSUMMER
A Emotional Character Study of a Dentist And a...
2006
1
None
6
2.99
85
10.99
G
Commentaries,Behind the Scenes
2006-02-15 05:03:42
24
25
ANGELS LIFE
A Thoughtful Display of a Woman And a Astronau...
2006
1
None
3
2.99
74
15.99
G
Trailers
2006-02-15 05:03:42
25
26
ANNIE IDENTITY
A Amazing Panorama of a Pastry Chef And a Boat...
2006
1
None
3
0.99
86
15.99
G
Commentaries,Deleted Scenes
2006-02-15 05:03:42
36
37
ARIZONA BANG
A Brilliant Panorama of a Mad Scientist And a ...
2006
1
None
3
2.99
121
28.99
PG
Trailers,Deleted Scenes
2006-02-15 05:03:42
38
39
ARMAGEDDON LOST
A Fast-Paced Tale of a Boat And a Teacher who ...
2006
1
None
5
0.99
99
10.99
G
Trailers
2006-02-15 05:03:42
40
41
ARSENIC INDEPENDENCE
A Fanciful Documentary of a Mad Cow And a Woma...
2006
1
None
4
0.99
137
17.99
PG
Trailers,Deleted Scenes,Behind the Scenes
2006-02-15 05:03:42
42
43
ATLANTIS CAUSE
A Thrilling Yarn of a Feminist And a Hunter wh...
2006
1
None
6
2.99
170
15.99
G
Behind the Scenes
2006-02-15 05:03:42
45
46
AUTUMN CROW
A Beautiful Tale of a Dentist And a Mad Cow wh...
2006
1
None
3
4.99
108
13.99
G
Trailers,Commentaries,Deleted Scenes,Behind th...
2006-02-15 05:03:42
49
50
BAKED CLEOPATRA
A Stunning Drama of a Forensic Psychologist An...
2006
1
None
3
2.99
182
20.99
G
Commentaries,Behind the Scenes
2006-02-15 05:03:42
51
52
BALLROOM MOCKINGBIRD
A Thrilling Documentary of a Composer And a Mo...
2006
1
None
6
0.99
173
29.99
G
Commentaries,Deleted Scenes
2006-02-15 05:03:42
54
55
BARBARELLA STREETCAR
A Awe-Inspiring Story of a Feminist And a Cat ...
2006
1
None
6
2.99
65
27.99
G
Behind the Scenes
2006-02-15 05:03:42
55
56
BAREFOOT MANCHURIAN
A Intrepid Story of a Cat And a Student who mu...
2006
1
None
6
2.99
129
15.99
G
Trailers,Commentaries
2006-02-15 05:03:42
57
58
BEACH HEARTBREAKERS
A Fateful Display of a Womanizer And a Mad Sci...
2006
1
None
6
2.99
122
16.99
G
Deleted Scenes,Behind the Scenes
2006-02-15 05:03:42
60
61
BEAUTY GREASE
A Fast-Paced Display of a Composer And a Moose...
2006
1
None
5
4.99
175
28.99
G
Trailers,Commentaries
2006-02-15 05:03:42
62
63
BEDAZZLED MARRIED
A Astounding Character Study of a Madman And a...
2006
1
None
6
0.99
73
21.99
PG
Trailers,Deleted Scenes,Behind the Scenes
2006-02-15 05:03:42
64
65
BEHAVIOR RUNAWAY
A Unbelieveable Drama of a Student And a Husba...
2006
1
None
3
4.99
100
20.99
PG
Trailers,Deleted Scenes,Behind the Scenes
2006-02-15 05:03:42
71
72
BILL OTHERS
A Stunning Saga of a Mad Scientist And a Foren...
2006
1
None
6
2.99
93
12.99
PG
Trailers,Commentaries
2006-02-15 05:03:42
73
74
BIRCH ANTITRUST
A Fanciful Panorama of a Husband And a Pioneer...
2006
1
None
4
4.99
162
18.99
PG
Trailers,Commentaries,Deleted Scenes
2006-02-15 05:03:42
74
75
BIRD INDEPENDENCE
A Thrilling Documentary of a Car And a Student...
2006
1
None
6
4.99
163
14.99
G
Commentaries,Behind the Scenes
2006-02-15 05:03:42
76
77
BIRDS PERDITION
A Boring Story of a Womanizer And a Pioneer wh...
2006
1
None
5
4.99
61
15.99
G
Trailers,Behind the Scenes
2006-02-15 05:03:42
77
78
BLACKOUT PRIVATE
A Intrepid Yarn of a Pastry Chef And a Mad Sci...
2006
1
None
7
2.99
85
12.99
PG
Trailers,Deleted Scenes
2006-02-15 05:03:42
...
...
...
...
...
...
...
...
...
...
...
...
...
...
913
914
TROUBLE DATE
A Lacklusture Panorama of a Forensic Psycholog...
2006
1
None
6
2.99
61
13.99
PG
Trailers,Commentaries,Behind the Scenes
2006-02-15 05:03:42
914
915
TRUMAN CRAZY
A Thrilling Epistle of a Moose And a Boy who m...
2006
1
None
7
4.99
92
9.99
G
Trailers,Commentaries
2006-02-15 05:03:42
915
916
TURN STAR
A Stunning Tale of a Man And a Monkey who must...
2006
1
None
3
2.99
80
10.99
G
Commentaries,Behind the Scenes
2006-02-15 05:03:42
917
918
TWISTED PIRATES
A Touching Display of a Frisbee And a Boat who...
2006
1
None
4
4.99
152
23.99
PG
Trailers,Commentaries,Deleted Scenes
2006-02-15 05:03:42
918
919
TYCOON GATHERING
A Emotional Display of a Husband And a A Shark...
2006
1
None
3
4.99
82
17.99
G
Trailers,Commentaries,Deleted Scenes
2006-02-15 05:03:42
919
920
UNBREAKABLE KARATE
A Amazing Character Study of a Robot And a Stu...
2006
1
None
3
0.99
62
16.99
G
Deleted Scenes,Behind the Scenes
2006-02-15 05:03:42
923
924
UNFORGIVEN ZOOLANDER
A Taut Epistle of a Monkey And a Sumo Wrestler...
2006
1
None
7
0.99
129
15.99
PG
Trailers,Commentaries,Behind the Scenes
2006-02-15 05:03:42
927
928
UPTOWN YOUNG
A Fateful Documentary of a Dog And a Hunter wh...
2006
1
None
5
2.99
84
16.99
PG
Commentaries
2006-02-15 05:03:42
931
932
VALLEY PACKER
A Astounding Documentary of a Astronaut And a ...
2006
1
None
3
0.99
73
21.99
G
Commentaries,Deleted Scenes
2006-02-15 05:03:42
949
950
VOLUME HOUSE
A Boring Tale of a Dog And a Woman who must Me...
2006
1
None
7
4.99
132
12.99
PG
Commentaries
2006-02-15 05:03:42
951
952
WAGON JAWS
A Intrepid Drama of a Moose And a Boat who mus...
2006
1
None
7
2.99
152
17.99
PG
Deleted Scenes,Behind the Scenes
2006-02-15 05:03:42
953
954
WAKE JAWS
A Beautiful Saga of a Feminist And a Composer ...
2006
1
None
7
4.99
73
18.99
G
Trailers,Commentaries,Deleted Scenes,Behind th...
2006-02-15 05:03:42
954
955
WALLS ARTIST
A Insightful Panorama of a Teacher And a Teach...
2006
1
None
7
4.99
135
19.99
PG
Trailers,Behind the Scenes
2006-02-15 05:03:42
956
957
WAR NOTTING
A Boring Drama of a Teacher And a Sumo Wrestle...
2006
1
None
7
4.99
80
26.99
G
Deleted Scenes,Behind the Scenes
2006-02-15 05:03:42
957
958
WARDROBE PHANTOM
A Action-Packed Display of a Mad Cow And a Ast...
2006
1
None
6
2.99
178
19.99
G
Trailers,Commentaries
2006-02-15 05:03:42
958
959
WARLOCK WEREWOLF
A Astounding Yarn of a Pioneer And a Crocodile...
2006
1
None
6
2.99
83
10.99
G
Commentaries,Behind the Scenes
2006-02-15 05:03:42
959
960
WARS PLUTO
A Taut Reflection of a Teacher And a Database ...
2006
1
None
5
2.99
128
15.99
G
Commentaries,Behind the Scenes
2006-02-15 05:03:42
961
962
WASTELAND DIVINE
A Fanciful Story of a Database Administrator A...
2006
1
None
7
2.99
85
18.99
PG
Trailers,Deleted Scenes,Behind the Scenes
2006-02-15 05:03:42
962
963
WATCH TRACY
A Fast-Paced Yarn of a Dog And a Frisbee who m...
2006
1
None
5
0.99
78
12.99
PG
Trailers,Deleted Scenes,Behind the Scenes
2006-02-15 05:03:42
963
964
WATERFRONT DELIVERANCE
A Unbelieveable Documentary of a Dentist And a...
2006
1
None
4
4.99
61
17.99
G
Behind the Scenes
2006-02-15 05:03:42
964
965
WATERSHIP FRONTIER
A Emotional Yarn of a Boat And a Crocodile who...
2006
1
None
6
0.99
112
28.99
G
Commentaries
2006-02-15 05:03:42
965
966
WEDDING APOLLO
A Action-Packed Tale of a Student And a Waitre...
2006
1
None
3
0.99
70
14.99
PG
Trailers
2006-02-15 05:03:42
967
968
WEREWOLF LOLA
A Fanciful Story of a Man And a Sumo Wrestler ...
2006
1
None
6
4.99
79
19.99
G
Trailers,Behind the Scenes
2006-02-15 05:03:42
968
969
WEST LION
A Intrepid Drama of a Butler And a Lumberjack ...
2006
1
None
4
4.99
159
29.99
G
Trailers
2006-02-15 05:03:42
979
980
WIZARD COLDBLOODED
A Lacklusture Display of a Robot And a Girl wh...
2006
1
None
4
4.99
75
12.99
PG
Commentaries,Deleted Scenes,Behind the Scenes
2006-02-15 05:03:42
982
983
WON DARES
A Unbelieveable Documentary of a Teacher And a...
2006
1
None
7
2.99
105
18.99
PG
Behind the Scenes
2006-02-15 05:03:42
984
985
WONDERLAND CHRISTMAS
A Awe-Inspiring Character Study of a Waitress ...
2006
1
None
4
4.99
111
19.99
PG
Commentaries
2006-02-15 05:03:42
986
987
WORDS HUNTER
A Action-Packed Reflection of a Composer And a...
2006
1
None
3
2.99
116
13.99
PG
Trailers,Commentaries,Deleted Scenes
2006-02-15 05:03:42
990
991
WORST BANGER
A Thrilling Drama of a Madman And a Dentist wh...
2006
1
None
4
2.99
185
26.99
PG
Deleted Scenes,Behind the Scenes
2006-02-15 05:03:42
995
996
YOUNG LANGUAGE
A Unbelieveable Yarn of a Boat And a Database ...
2006
1
None
6
0.99
183
9.99
G
Trailers,Behind the Scenes
2006-02-15 05:03:42
372 rows × 13 columns
In [26]:
is_099 = film_df.rental_rate == 0.99
is_boring = film_df.description.str.contains("Boring")
film_df[is_099 & is_boring][["title","description","rental_rate"]]
Out[26]:
title
description
rental_rate
10
ALAMO VIDEOTAPE
A Boring Epistle of a Butler And a Cat who mus...
0.99
39
ARMY FLINTSTONES
A Boring Saga of a Database Administrator And ...
0.99
81
BLOOD ARGONAUTS
A Boring Drama of a Explorer And a Man who mus...
0.99
127
CATCH AMISTAD
A Boring Reflection of a Lumberjack And a Femi...
0.99
167
COMANCHEROS ENEMY
A Boring Saga of a Lumberjack And a Monkey who...
0.99
199
CURTAIN VIDEOTAPE
A Boring Reflection of a Dentist And a Mad Cow...
0.99
375
GRAPES FURY
A Boring Yarn of a Mad Cow And a Sumo Wrestler...
0.99
386
GUN BONNIE
A Boring Display of a Sumo Wrestler And a Husb...
0.99
481
JEOPARDY ENCINO
A Boring Panorama of a Man And a Mad Cow who m...
0.99
491
JUNGLE CLOSER
A Boring Character Study of a Boy And a Woman ...
0.99
524
LOATHING LEGALLY
A Boring Epistle of a Pioneer And a Mad Scient...
0.99
537
LOVERBOY ATTACKS
A Boring Story of a Car And a Butler who must ...
0.99
559
MARS ROMAN
A Boring Drama of a Car And a Dog who must Suc...
0.99
598
MOTHER OLEANDER
A Boring Tale of a Husband And a Boy who must ...
0.99
624
NONE SPIKING
A Boring Reflection of a Secret Agent And a As...
0.99
635
OLEANDER CLUE
A Boring Story of a Teacher And a Monkey who m...
0.99
687
POLISH BROOKLYN
A Boring Character Study of a Database Adminis...
0.99
794
SIEGE MADRE
A Boring Tale of a Frisbee And a Crocodile who...
0.99
850
STRAIGHT HOURS
A Boring Panorama of a Secret Agent And a Girl...
0.99
890
TIMBERLAND SKY
A Boring Display of a Man And a Dog who must R...
0.99
909
TREATMENT JEKYLL
A Boring Story of a Teacher And a Student who ...
0.99
In [51]:
film_group = film_df.groupby("rating")
film_group.agg({"film_id": np.size,"rental_rate":np.mean})
Out[51]:
film_id
rental_rate
rating
G
178
2.888876
NC-17
210
2.970952
PG
194
3.051856
PG-13
223
3.034843
R
195
2.938718
In [ ]:
In [ ]:
Content source: rrbb014/fastcampus_dss
Similar notebooks: