
import pymongo
import pandas

client = pymongo.MongoClient()
db = client.test


pandas.DataFrame(db.restaurants.find(
    { "name": "Bareburger" },
    { "_id": 0, "borough": 1, "address.street": 1 }
))


pandas.DataFrame(db.restaurants.aggregate([
    { "$sortByCount": "$name" },
    { "$limit": 3 }
]))


pandas.DataFrame(db.restaurants.aggregate([
    { "$group": { "_id": "$name", "nb" : { "$sum": 1 } } },
    { "$sort": { "nb": -1 }},
    { "$limit": 3 }
]))


pandas.DataFrame(db.restaurants.aggregate([
    { "$sortByCount": "$cuisine" },
    { "$limit": 10 }
]))


pandas.DataFrame(db.restaurants.aggregate([
    { "$unwind": "$grades" },
    { "$group": {
        "_id": { "name": "$name", "id": "restaurant_id" },
        "score": { "$avg": "$grades.score" }
    }},
    { "$sort": { "score": -1 }},
    { "$limit": 10 }
]))


pandas.DataFrame(db.restaurants.aggregate([
    { "$addFields": { "eval": { "$first": "$grades" }}},
    { "$group": {
        "_id": "$borough",
        "NbRestaurants": { "$sum": 1 },
        "ScoreMoyen": { "$avg": "$eval.score" },
        "NbA": { "$sum": { "$cond": { "if": { "$eq": [ "$eval.grade", "A"]}, "then": 1, "else": 0 }}}
    }},
    { "$addFields": {
        "PctA": { "$multiply": [ { "$divide": [ "$NbA", "$NbRestaurants" ]}, 100]}
    }}
]))


pandas.DataFrame(db.restaurants.find(
    { "name": {"$regex": "union", "$options": "i" }},
    { "_id": 0, "name": 1, "address.street": 1 }
))


from datetime import datetime

pandas.DataFrame(db.restaurants.find(
    { "grades.date": datetime(2014, 2, 1) }
))


pandas.DataFrame(db.restaurants.find(
    { 
        "address.coord.0": { "$gte": -74.2, "$lte": -74.1 },
        "address.coord.1": { "$gte":  40.1, "$lte":  40.6 }
    }
))

	address	borough
0	{'street': '31 Avenue'}	Queens
1	{'street': 'Laguardia Place'}	Manhattan
2	{'street': '7 Avenue'}	Brooklyn
3	{'street': '31 Street'}	Queens
4	{'street': '8 Avenue'}	Manhattan
5	{'street': '2 Avenue'}	Manhattan
6	{'street': 'Austin Street'}	Queens
7	{'street': 'Bell Boulevard'}	Queens
8	{'street': 'Court St'}	Brooklyn
9	{'street': 'West 46 Street'}	Manhattan
10	{'street': '1 Avenue'}	Manhattan
11	{'street': 'William St'}	Manhattan

	_id	count
0	Subway	421
1	Starbucks Coffee	223
2	Mcdonald'S	208

	_id	nb
0	Subway	421
1	Starbucks Coffee	223
2	Mcdonald'S	208

	_id	count
0	American	6183
1	Chinese	2418
2	Café/Coffee/Tea	1214
3	Pizza	1163
4	Italian	1069
5	Other	1011
6	Latin (Cuban, Dominican, Puerto Rican, South &...	850
7	Japanese	760
8	Mexican	754
9	Bakery	691

	_id	score
0	{'name': 'Juice It Health Bar', 'id': 'restaur...	75.0
1	{'name': 'Golden Dragon Cuisine', 'id': 'resta...	73.0
2	{'name': 'Palombo Pastry Shop', 'id': 'restaur...	69.0
3	{'name': 'Chelsea'S Juice Factory', 'id': 'res...	69.0
4	{'name': 'Koyla', 'id': 'restaurant_id'}	61.0
5	{'name': 'K & D Internet Inc', 'id': 'restaura...	61.0
6	{'name': 'Ivory D O S Inc', 'id': 'restaurant...	60.0
7	{'name': 'Ab Halal Restaurant', 'id': 'restaur...	58.0
8	{'name': 'Savour Sichuan', 'id': 'restaurant_id'}	56.0
9	{'name': 'Matcha Cafe Wabi', 'id': 'restaurant...	56.0

Introduction à MongoDB - évaluation¶

Restaurants¶

Lister tous les restaurants de la chaîne "Bareburger" (rue, quartier)¶

Lister les trois chaînes de restaurant les plus présentes¶

Donner les 10 styles de cuisine les plus présents dans la collection¶

Lister les 10 restaurants les moins bien notés (note moyenne la plus haute)¶

Lister par quartier le nombre de restaurants, le score moyen et le pourcentage moyen d'évaluation A¶

Questions complémentaires¶

Lister les restaurants (nom et rue uniquement) situés sur une rue ayant le terme “Union” dans le nom¶

Lister les restaurants ayant eu une visite le 1er février 2014¶

Lister les restaurants situés entre les longitudes -74.2 et -74.1 et les lattitudes 40.5 et 40.6¶

	_id	NbRestaurants	ScoreMoyen	NbA	PctA
0	Bronx	2338	10.672095	1926	82.378101
1	Brooklyn	6086	10.723014	4957	81.449228
2	Staten Island	969	10.773262	785	81.011352
3	Missing	51	8.520000	50	98.039216
4	Manhattan	10259	10.952729	8450	82.366702
5	Queens	5656	11.075630	4519	79.897454

	address	name
0	{'street': 'Henderson Avenue'}	Union Hall
1	{'street': 'East 69 Street'}	Union Club Of City Of New York
2	{'street': 'East 37 Street'}	The Union League Club
3	{'street': 'Union Street'}	Union Cafe Restaurant
4	{'street': 'Union Avenue'}	Union Pool
5	{'street': 'East 17 Street'}	Union Square Theater
6	{'street': '6 Avenue'}	Union Bank Of Switzerland
7	{'street': 'Union Street'}	Union Hall
8	{'street': '44 Road'}	U.N. Federal Credit Union
9	{'street': 'Corona Avenue'}	La Union Restaurant
10	{'street': 'Broadway'}	Regal Union Square Stadium 14
11	{'street': 'Kissena Boulevard'}	Queens College Student Union Sa Diner
12	{'street': 'Union Square West'}	Union Square Catering Facility Ballroom
13	{'street': 'North End Avenue'}	Union Square Events
14	{'street': 'East 16 Street'}	Union Square Cafe
15	{'street': 'Union St'}	Union St Pojangmacha
16	{'street': 'Smith Street'}	Union Grounds
17	{'street': 'Troutman Street'}	Union Pizza Works
18	{'street': 'Ashland Place'}	Union Square Sports & Entertainment At Theatre...
19	{'street': 'Spring Street'}	Union Bar & Kitchen
20	{'street': 'Union Avenue'}	Re.Union
21	{'street': '6 Avenue'}	Union Bank Of Switzerland

	_id	address	borough	cuisine	grades	name	restaurant_id
0	60006d6aa7aafd5a6d45ce69	{'building': '300', 'coord': [-73.8787725, 40....	Bronx	Hamburgers	[{'date': 2014-02-01 00:00:00, 'grade': 'A', '...	Mcdonald'S	40395653
1	60006d6aa7aafd5a6d45d0ec	{'building': '15705', 'coord': [-73.8400791000...	Queens	Juice, Smoothies, Fruit Salads	[{'date': 2015-01-15 00:00:00, 'grade': 'A', '...	Gold'S Gym	40534973
2	60006d6ba7aafd5a6d45dac1	{'building': '706', 'coord': [-73.8668038, 40....	Bronx	Chinese	[{'date': 2014-08-11 00:00:00, 'grade': 'A', '...	K & Q Restaruant	40947971
3	60006d6ba7aafd5a6d45dc0d	{'building': '427', 'coord': [-73.9740765, 40....	Brooklyn	Pizza	[{'date': 2014-07-10 00:00:00, 'grade': 'A', '...	Knapp Pizza Iii	40993361
4	60006d6ba7aafd5a6d45e1e3	{'building': '9718', 'coord': [-73.88871520000...	Brooklyn	Chinese	[{'date': 2014-02-01 00:00:00, 'grade': 'A', '...	Win Hing Chinese Restaurant	41152860
5	60006d6ba7aafd5a6d45e792	{'building': '14908', 'coord': [-73.8153339, 4...	Queens	Korean	[{'date': 2014-07-23 00:00:00, 'grade': 'B', '...	Chinese House Restaurant	41264537
6	60006d6ba7aafd5a6d45e7a0	{'building': '2087', 'coord': [-73.9615235, 40...	Brooklyn	Chinese/Japanese	[{'date': 2014-07-30 00:00:00, 'grade': 'A', '...	Sake	41265584
7	60006d6ca7aafd5a6d45ed39	{'building': '133-45', 'coord': [-73.832814, 4...	Queens	American	[{'date': 2014-08-19 00:00:00, 'grade': 'A', '...	Roosevelt Sports Bar	41361603
8	60006d6ca7aafd5a6d45f537	{'building': '1050', 'coord': [-73.86184589999...	Brooklyn	American	[{'date': 2014-02-01 00:00:00, 'grade': 'A', '...	Forbell Cafe (U.S. Post Office Cafeteria)	41460812
9	60006d6ca7aafd5a6d45f66d	{'building': '300', 'coord': [-73.989723, 40.7...	Manhattan	Hamburgers	[{'date': 2014-02-01 00:00:00, 'grade': 'A', '...	Shake Shack	41474503
10	60006d6ca7aafd5a6d45ff94	{'building': '13620', 'coord': [-73.8290939, 4...	Queens	Chinese	[{'date': 2014-08-25 00:00:00, 'grade': 'A', '...	New Flushing Bakery	41583780
11	60006d6da7aafd5a6d46022d	{'building': '136-20', 'coord': [-73.8290939, ...	Queens	Ice Cream, Gelato, Yogurt, Ices	[{'date': 2014-07-29 00:00:00, 'grade': 'B', '...	Haagen Dazs	41610419
12	60006d6da7aafd5a6d46052b	{'building': '43-45', 'coord': [-73.825705, 40...	Queens	Chinese	[{'date': 2015-01-07 00:00:00, 'grade': 'A', '...	First Hot Pot	41631489
13	60006d6da7aafd5a6d46070f	{'building': '88-01', 'coord': [-73.8747611, 4...	Queens	Donuts	[{'date': 2015-01-16 00:00:00, 'grade': 'A', '...	Dunkin' Donuts	41643866
14	60006d6da7aafd5a6d460719	{'building': '2707', 'coord': [-73.98181029999...	Brooklyn	Russian	[{'date': 2014-08-19 00:00:00, 'grade': 'A', '...	The Lights Of Baku	41644068
15	60006d6da7aafd5a6d4609c4	{'building': '35-64', 'coord': [-73.8724060999...	Queens	Spanish	[{'date': 2014-08-22 00:00:00, 'grade': 'A', '...	Emily Bar Restaurant	41664735
16	60006d6da7aafd5a6d460a19	{'building': '5902', 'coord': [-73.9007417, 40...	Queens	Café/Coffee/Tea	[{'date': 2014-02-01 00:00:00, 'grade': 'A', '...	Norma'S	41668438
17	60006d6da7aafd5a6d460bb3	{'building': '81-11', 'coord': [-73.8831919, 4...	Queens	American	[{'date': 2015-01-12 00:00:00, 'grade': 'A', '...	Dunkin Donuts, Baskin Robbins	41682237
18	60006d6da7aafd5a6d460db9	{'building': '749', 'coord': [-73.864991300000...	Bronx	Spanish	[{'date': 2014-02-01 00:00:00, 'grade': 'A', '...	Una Nueva Esperanza/ Herbalife	41696008
19	60006d6da7aafd5a6d460e0a	{'building': '136-21', 'coord': [-73.828780999...	Queens	Chinese	[{'date': 2015-01-15 00:00:00, 'grade': 'A', '...	Hot Pot House	41697472
20	60006d6da7aafd5a6d461030	{'building': '13660', 'coord': [-82.4899192, 2...	Queens	Bakery	[{'date': 2015-01-07 00:00:00, 'grade': 'Z', '...	Cake House Win	41707082
21	60006d6da7aafd5a6d461151	{'building': '13620', 'coord': [-73.8290939, 4...	Queens	Korean	[{'date': 2014-07-24 00:00:00, 'grade': 'A', '...	Dae Jang Geum	41712232
22	60006d6da7aafd5a6d4618f9	{'building': '4718', 'coord': [-73.8496036, 40...	Bronx	Other	[{'date': 2014-09-16 00:00:00, 'grade': 'A', '...	Kennedy Fried Chicken	50002531
23	60006d6da7aafd5a6d461b6b	{'building': '15203', 'coord': [-73.81267, 40....	Missing	Chicken	[{'date': 2014-09-08 00:00:00, 'grade': 'A', '...	Kfc	50004349

	_id	address	borough	cuisine	grades	name	restaurant_id
0	60006d6aa7aafd5a6d45cace	{'building': '73', 'coord': [-74.1178949, 40.5...	Staten Island	Delicatessen	[{'date': 2014-11-18 00:00:00, 'grade': 'A', '...	Plaza Bagels & Deli	40364286
1	60006d6aa7aafd5a6d45cad0	{'building': '203', 'coord': [-74.152359199999...	Staten Island	Delicatessen	[{'date': 2015-01-05 00:00:00, 'grade': 'A', '...	B & M Hot Bagel & Grocery	40364299
2	60006d6aa7aafd5a6d45cae5	{'building': '37', 'coord': [-74.138263, 40.54...	Staten Island	American	[{'date': 2014-04-22 00:00:00, 'grade': 'A', '...	Great Kills Yacht Club	40364610
3	60006d6aa7aafd5a6d45cb11	{'building': '3823', 'coord': [-74.16536339999...	Staten Island	American	[{'date': 2014-07-15 00:00:00, 'grade': 'B', '...	Joyce'S Tavern	40365692
4	60006d6aa7aafd5a6d45cb43	{'building': '135', 'coord': [-74.104655999999...	Staten Island	American	[{'date': 2014-07-10 00:00:00, 'grade': 'A', '...	Richmond County Country Club	40366928
...	...	...	...	...	...	...	...
327	60006d6ea7aafd5a6d462cf0	{'building': '14', 'coord': [-74.150459, 40.55...	Staten Island	Other	[]	Famous Falafel	50018576
328	60006d6ea7aafd5a6d462d14	{'building': '2224', 'coord': [-74.1033838, 40...	Staten Island	Other	[]	Ciminna Cafe	50018635
329	60006d6ea7aafd5a6d462d58	{'building': '365', 'coord': [-74.1102822, 40....	Staten Island	Other	[]		50018782
330	60006d6ea7aafd5a6d462d7f	{'building': '3237', 'coord': [-74.16969230000...	Staten Island	Other	[]	Jimmy John'S	50018887
331	60006d6ea7aafd5a6d462d99	{'building': '307', 'coord': [-74.1453806, 40....	Staten Island	Other	[]	Christine'S Restaurant	50018953