{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "# Advanced Tabular Manipulation\n", "\n", "{ref}`👨‍🏫 [Sync] `" ] }, { "cell_type": "code", "execution_count": 1, "metadata": {}, "outputs": [], "source": [ "%matplotlib inline\n", "\n", "import pandas" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "````{tabbed} Local files\n", "\n", "Assuming you have [the file](https://github.com/darribas/data_science_studio/raw/master/content/data/paris_abb.csv.zip) downloaded on the path `../data/`: \n", "\n", "```python\n", "db = pandas.read_csv(\"../data/paris_abb.csv.zip\")\n", "```\n", "````\n", "\n", "````{tabbed} Online read\n", "\n", "If you're online, you can do:\n", "\n", "```python\n", "db = pandas.read_csv(\"https://github.com/darribas/data_science_studio/raw/master/content/data/paris_abb.csv.zip\")\n", "```\n", "````" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "tags": [ "remove-cell" ] }, "outputs": [], "source": [ "db = pandas.read_csv(\"../data/paris_abb.csv.zip\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Sorting" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "* By values" ] }, { "cell_type": "code", "execution_count": 2, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
idneighbourhood_cleansedproperty_typeroom_typeaccommodatesbathroomsbedroomsbedsbed_typePrice
2567620291987PassyApartmentEntire home/apt31.00.02.0Real Bed0.0
2569720313940TempleApartmentEntire home/apt21.00.01.0Real Bed0.0
2689921422028PopincourtApartmentEntire home/apt32.02.03.0Real Bed0.0
2555820219162Buttes-ChaumontApartmentEntire home/apt11.00.01.0Real Bed0.0
2510619974916Buttes-MontmartreCondominiumEntire home/apt41.01.02.0Real Bed0.0
\n", "
" ], "text/plain": [ " id neighbourhood_cleansed property_type room_type \\\n", "25676 20291987 Passy Apartment Entire home/apt \n", "25697 20313940 Temple Apartment Entire home/apt \n", "26899 21422028 Popincourt Apartment Entire home/apt \n", "25558 20219162 Buttes-Chaumont Apartment Entire home/apt \n", "25106 19974916 Buttes-Montmartre Condominium Entire home/apt \n", "\n", " accommodates bathrooms bedrooms beds bed_type Price \n", "25676 3 1.0 0.0 2.0 Real Bed 0.0 \n", "25697 2 1.0 0.0 1.0 Real Bed 0.0 \n", "26899 3 2.0 2.0 3.0 Real Bed 0.0 \n", "25558 1 1.0 0.0 1.0 Real Bed 0.0 \n", "25106 4 1.0 1.0 2.0 Real Bed 0.0 " ] }, "execution_count": 2, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Top-5 cheapes properties\n", "db.sort_values(\"Price\")\\\n", " .head(5)" ] }, { "cell_type": "code", "execution_count": 3, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
idneighbourhood_cleansedproperty_typeroom_typeaccommodatesbathroomsbedroomsbedsbed_typePrice
4757436402651Batignolles-MonceauHouseEntire home/apt123.04.05.0Real Bed10000.0
101137225849Buttes-MontmartreApartmentEntire home/apt82.02.05.0Real Bed9379.0
3529127608896ObservatoireApartmentPrivate room11.00.01.0Real Bed9059.0
112868093890PassyApartmentEntire home/apt31.02.02.0Real Bed8721.0
3219025448670VaugirardApartmentEntire home/apt11.00.01.0Real Bed8576.0
\n", "
" ], "text/plain": [ " id neighbourhood_cleansed property_type room_type \\\n", "47574 36402651 Batignolles-Monceau House Entire home/apt \n", "10113 7225849 Buttes-Montmartre Apartment Entire home/apt \n", "35291 27608896 Observatoire Apartment Private room \n", "11286 8093890 Passy Apartment Entire home/apt \n", "32190 25448670 Vaugirard Apartment Entire home/apt \n", "\n", " accommodates bathrooms bedrooms beds bed_type Price \n", "47574 12 3.0 4.0 5.0 Real Bed 10000.0 \n", "10113 8 2.0 2.0 5.0 Real Bed 9379.0 \n", "35291 1 1.0 0.0 1.0 Real Bed 9059.0 \n", "11286 3 1.0 2.0 2.0 Real Bed 8721.0 \n", "32190 1 1.0 0.0 1.0 Real Bed 8576.0 " ] }, "execution_count": 3, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Top-5 most expensive properties\n", "db.sort_values(\"Price\", ascending=False)\\\n", " .head(5)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "* By index" ] }, { "cell_type": "code", "execution_count": 4, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
idneighbourhood_cleansedroom_typeaccommodatesbathroomsbedroomsbedsbed_typePrice
property_type
Aparthotel31728955VaugirardHotel room21.01.01.0Real Bed141.0
Aparthotel13003443VaugirardEntire home/apt41.00.03.0Real Bed75.0
Aparthotel31733851TempleHotel room41.01.03.0Real Bed141.0
Aparthotel17630233BourseHotel room41.02.02.0Real Bed330.0
Aparthotel24387710ObservatoirePrivate room125.05.011.0Real Bed698.0
\n", "
" ], "text/plain": [ " id neighbourhood_cleansed room_type accommodates \\\n", "property_type \n", "Aparthotel 31728955 Vaugirard Hotel room 2 \n", "Aparthotel 13003443 Vaugirard Entire home/apt 4 \n", "Aparthotel 31733851 Temple Hotel room 4 \n", "Aparthotel 17630233 Bourse Hotel room 4 \n", "Aparthotel 24387710 Observatoire Private room 12 \n", "\n", " bathrooms bedrooms beds bed_type Price \n", "property_type \n", "Aparthotel 1.0 1.0 1.0 Real Bed 141.0 \n", "Aparthotel 1.0 0.0 3.0 Real Bed 75.0 \n", "Aparthotel 1.0 1.0 3.0 Real Bed 141.0 \n", "Aparthotel 1.0 2.0 2.0 Real Bed 330.0 \n", "Aparthotel 5.0 5.0 11.0 Real Bed 698.0 " ] }, "execution_count": 4, "metadata": {}, "output_type": "execute_result" } ], "source": [ "tmp = db.set_index(\"property_type\")\\\n", " .sort_index()\n", "tmp.head()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "(Useful for quick subsetting:)" ] }, { "cell_type": "code", "execution_count": 5, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
idneighbourhood_cleansedPrice
property_type
Tiny house20976623Panthéon110.0
Tiny house6838781EntrepĂ´t60.0
Tiny house29322690Passy30.0
Tiny house18919023Louvre100.0
Tiny house4191080Louvre80.0
Tiny house9582468Popincourt80.0
Tiny house37312602MĂ©nilmontant40.0
Tiny house2555221Buttes-Montmartre114.0
Tiny house34572791Passy35.0
Tiny house37174177Vaugirard50.0
Tiny house26292760MĂ©nilmontant75.0
Tiny house36864294Passy38.0
Tiny house20273867Panthéon100.0
Tiny house34816113Observatoire40.0
Tiny house768986Temple79.0
Tiny house13220094Buttes-Chaumont160.0
Tiny house5699102Buttes-Montmartre71.0
Tiny house36048024Buttes-Montmartre70.0
Tiny house20688679Buttes-Montmartre73.0
Tiny house15157047Temple42.0
Tiny house12588228Passy75.0
\n", "
" ], "text/plain": [ " id neighbourhood_cleansed Price\n", "property_type \n", "Tiny house 20976623 Panthéon 110.0\n", "Tiny house 6838781 Entrepôt 60.0\n", "Tiny house 29322690 Passy 30.0\n", "Tiny house 18919023 Louvre 100.0\n", "Tiny house 4191080 Louvre 80.0\n", "Tiny house 9582468 Popincourt 80.0\n", "Tiny house 37312602 Ménilmontant 40.0\n", "Tiny house 2555221 Buttes-Montmartre 114.0\n", "Tiny house 34572791 Passy 35.0\n", "Tiny house 37174177 Vaugirard 50.0\n", "Tiny house 26292760 Ménilmontant 75.0\n", "Tiny house 36864294 Passy 38.0\n", "Tiny house 20273867 Panthéon 100.0\n", "Tiny house 34816113 Observatoire 40.0\n", "Tiny house 768986 Temple 79.0\n", "Tiny house 13220094 Buttes-Chaumont 160.0\n", "Tiny house 5699102 Buttes-Montmartre 71.0\n", "Tiny house 36048024 Buttes-Montmartre 70.0\n", "Tiny house 20688679 Buttes-Montmartre 73.0\n", "Tiny house 15157047 Temple 42.0\n", "Tiny house 12588228 Passy 75.0" ] }, "execution_count": 5, "metadata": {}, "output_type": "execute_result" } ], "source": [ "tmp.loc[\"Tiny house\", \n", " [\"id\", \"neighbourhood_cleansed\", \"Price\"]\n", " ]" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Joinning" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "- Additional data (linked through ID!)" ] }, { "cell_type": "code", "execution_count": 6, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
idreview_scores_ratingreview_scores_accuracyreview_scores_cleanlinessreview_scores_checkinreview_scores_communicationreview_scores_locationreview_scores_value
03109100.010.010.010.010.010.010.0
1539690.09.08.09.09.010.08.0
2739794.010.09.010.010.010.010.0
3796496.010.010.010.010.010.010.0
4995298.010.010.010.010.010.010.0
\n", "
" ], "text/plain": [ " id review_scores_rating review_scores_accuracy \\\n", "0 3109 100.0 10.0 \n", "1 5396 90.0 9.0 \n", "2 7397 94.0 10.0 \n", "3 7964 96.0 10.0 \n", "4 9952 98.0 10.0 \n", "\n", " review_scores_cleanliness review_scores_checkin \\\n", "0 10.0 10.0 \n", "1 8.0 9.0 \n", "2 9.0 10.0 \n", "3 10.0 10.0 \n", "4 10.0 10.0 \n", "\n", " review_scores_communication review_scores_location review_scores_value \n", "0 10.0 10.0 10.0 \n", "1 9.0 10.0 8.0 \n", "2 10.0 10.0 10.0 \n", "3 10.0 10.0 10.0 \n", "4 10.0 10.0 10.0 " ] }, "execution_count": 6, "metadata": {}, "output_type": "execute_result" } ], "source": [ "reviews = pandas.read_csv(\"../data/paris_abb_review.csv.zip\")\n", "reviews.head()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "* Join to original table:" ] }, { "cell_type": "code", "execution_count": 7, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
idneighbourhood_cleansedproperty_typeroom_typeaccommodatesbathroomsbedroomsbedsbed_typePricereview_scores_ratingreview_scores_accuracyreview_scores_cleanlinessreview_scores_checkinreview_scores_communicationreview_scores_locationreview_scores_value
03109ObservatoireApartmentEntire home/apt21.00.01.0Real Bed60.0100.010.010.010.010.010.010.0
15396HĂ´tel-de-VilleApartmentEntire home/apt21.00.01.0Pull-out Sofa115.090.09.08.09.09.010.08.0
27397HĂ´tel-de-VilleApartmentEntire home/apt41.02.02.0Real Bed119.094.010.09.010.010.010.010.0
37964OpéraApartmentEntire home/apt21.01.01.0Real Bed130.096.010.010.010.010.010.010.0
49952PopincourtApartmentEntire home/apt21.01.01.0Real Bed75.098.010.010.010.010.010.010.0
\n", "
" ], "text/plain": [ " id neighbourhood_cleansed property_type room_type accommodates \\\n", "0 3109 Observatoire Apartment Entire home/apt 2 \n", "1 5396 Hôtel-de-Ville Apartment Entire home/apt 2 \n", "2 7397 Hôtel-de-Ville Apartment Entire home/apt 4 \n", "3 7964 Opéra Apartment Entire home/apt 2 \n", "4 9952 Popincourt Apartment Entire home/apt 2 \n", "\n", " bathrooms bedrooms beds bed_type Price review_scores_rating \\\n", "0 1.0 0.0 1.0 Real Bed 60.0 100.0 \n", "1 1.0 0.0 1.0 Pull-out Sofa 115.0 90.0 \n", "2 1.0 2.0 2.0 Real Bed 119.0 94.0 \n", "3 1.0 1.0 1.0 Real Bed 130.0 96.0 \n", "4 1.0 1.0 1.0 Real Bed 75.0 98.0 \n", "\n", " review_scores_accuracy review_scores_cleanliness review_scores_checkin \\\n", "0 10.0 10.0 10.0 \n", "1 9.0 8.0 9.0 \n", "2 10.0 9.0 10.0 \n", "3 10.0 10.0 10.0 \n", "4 10.0 10.0 10.0 \n", "\n", " review_scores_communication review_scores_location review_scores_value \n", "0 10.0 10.0 10.0 \n", "1 9.0 10.0 8.0 \n", "2 10.0 10.0 10.0 \n", "3 10.0 10.0 10.0 \n", "4 10.0 10.0 10.0 " ] }, "execution_count": 7, "metadata": {}, "output_type": "execute_result" } ], "source": [ "dbj1 = db.join(reviews.set_index(\"id\"),\n", " on = \"id\"\n", " )\n", "dbj1.head()" ] }, { "cell_type": "code", "execution_count": 8, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
neighbourhood_cleansedproperty_typeroom_typeaccommodatesbathroomsbedroomsbedsbed_typePricereview_scores_ratingreview_scores_accuracyreview_scores_cleanlinessreview_scores_checkinreview_scores_communicationreview_scores_locationreview_scores_value
id
3109ObservatoireApartmentEntire home/apt21.00.01.0Real Bed60.0100.010.010.010.010.010.010.0
5396HĂ´tel-de-VilleApartmentEntire home/apt21.00.01.0Pull-out Sofa115.090.09.08.09.09.010.08.0
7397HĂ´tel-de-VilleApartmentEntire home/apt41.02.02.0Real Bed119.094.010.09.010.010.010.010.0
7964OpéraApartmentEntire home/apt21.01.01.0Real Bed130.096.010.010.010.010.010.010.0
9952PopincourtApartmentEntire home/apt21.01.01.0Real Bed75.098.010.010.010.010.010.010.0
\n", "
" ], "text/plain": [ " neighbourhood_cleansed property_type room_type accommodates \\\n", "id \n", "3109 Observatoire Apartment Entire home/apt 2 \n", "5396 Hôtel-de-Ville Apartment Entire home/apt 2 \n", "7397 Hôtel-de-Ville Apartment Entire home/apt 4 \n", "7964 Opéra Apartment Entire home/apt 2 \n", "9952 Popincourt Apartment Entire home/apt 2 \n", "\n", " bathrooms bedrooms beds bed_type Price review_scores_rating \\\n", "id \n", "3109 1.0 0.0 1.0 Real Bed 60.0 100.0 \n", "5396 1.0 0.0 1.0 Pull-out Sofa 115.0 90.0 \n", "7397 1.0 2.0 2.0 Real Bed 119.0 94.0 \n", "7964 1.0 1.0 1.0 Real Bed 130.0 96.0 \n", "9952 1.0 1.0 1.0 Real Bed 75.0 98.0 \n", "\n", " review_scores_accuracy review_scores_cleanliness \\\n", "id \n", "3109 10.0 10.0 \n", "5396 9.0 8.0 \n", "7397 10.0 9.0 \n", "7964 10.0 10.0 \n", "9952 10.0 10.0 \n", "\n", " review_scores_checkin review_scores_communication \\\n", "id \n", "3109 10.0 10.0 \n", "5396 9.0 9.0 \n", "7397 10.0 10.0 \n", "7964 10.0 10.0 \n", "9952 10.0 10.0 \n", "\n", " review_scores_location review_scores_value \n", "id \n", "3109 10.0 10.0 \n", "5396 10.0 8.0 \n", "7397 10.0 10.0 \n", "7964 10.0 10.0 \n", "9952 10.0 10.0 " ] }, "execution_count": 8, "metadata": {}, "output_type": "execute_result" } ], "source": [ "dbj2 = db.set_index(\"id\")\\\n", " .join(reviews.set_index(\"id\"))\n", "dbj2.head()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Note:\n", "\n", "- Left can choose index/column, right needs to be index (results \"almost\" the same)\n", "- For more flexibility, check out `merge`:\n", "\n", "> [https://pandas.pydata.org/pandas-docs/stable/user_guide/merging.html](https://pandas.pydata.org/pandas-docs/stable/user_guide/merging.html)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Grouping" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "- Get the mean price for \"Louvre\":" ] }, { "cell_type": "code", "execution_count": 9, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "175.2634194831014" ] }, "execution_count": 9, "metadata": {}, "output_type": "execute_result" } ], "source": [ "db.query(\"neighbourhood_cleansed == 'Louvre'\")\\\n", " [\"Price\"]\\\n", " .mean()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "- Get the mean price for \"Luxembourg\":" ] }, { "cell_type": "code", "execution_count": 10, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "160.02262142381903" ] }, "execution_count": 10, "metadata": {}, "output_type": "execute_result" } ], "source": [ "db.query(\"neighbourhood_cleansed == 'Luxembourg'\")\\\n", " [\"Price\"]\\\n", " .mean()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "- Get the mean price for \"Palais-Bourbon\":" ] }, { "cell_type": "code", "execution_count": 11, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "169.8526690391459" ] }, "execution_count": 11, "metadata": {}, "output_type": "execute_result" } ], "source": [ "db.query(\"neighbourhood_cleansed == 'Palais-Bourbon'\")\\\n", " [\"Price\"]\\\n", " .mean()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "* For every neighbourhood???" ] }, { "cell_type": "code", "execution_count": 12, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "neighbourhood_cleansed\n", "Batignolles-Monceau 103.355239\n", "Bourse 138.898017\n", "Buttes-Chaumont 76.971745\n", "Buttes-Montmartre 84.867211\n", "Entrepôt 99.130493\n", "Gobelins 80.000000\n", "Hôtel-de-Ville 152.547723\n", "Louvre 175.263419\n", "Luxembourg 160.022621\n", "Ménilmontant 71.074504\n", "Observatoire 98.907340\n", "Opéra 122.495921\n", "Palais-Bourbon 169.852669\n", "Panthéon 124.683662\n", "Passy 149.237946\n", "Popincourt 90.832782\n", "Reuilly 84.225256\n", "Temple 147.904592\n", "Vaugirard 106.269580\n", "Élysée 194.158416\n", "Name: Price, dtype: float64" ] }, "execution_count": 12, "metadata": {}, "output_type": "execute_result" } ], "source": [ "db.groupby(\"neighbourhood_cleansed\")\\\n", " [\"Price\"]\\\n", " .mean()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## `MultiIndex` Tables" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Grouping can be based on more than one variable only..." ] }, { "cell_type": "code", "execution_count": 13, "metadata": {}, "outputs": [], "source": [ "nr = dbj1.groupby([\"neighbourhood_cleansed\", \"room_type\"])\\\n", " [[\"Price\", \"review_scores_rating\"]]\\\n", " .mean()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "This generates a `MultiIndex`:" ] }, { "cell_type": "code", "execution_count": 14, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
Pricereview_scores_rating
neighbourhood_cleansedroom_type
Batignolles-MonceauEntire home/apt103.50192292.484096
Hotel room343.66071493.607143
Private room58.69830595.064407
Shared room53.14285790.214286
BourseEntire home/apt139.61802091.608503
\n", "
" ], "text/plain": [ " Price review_scores_rating\n", "neighbourhood_cleansed room_type \n", "Batignolles-Monceau Entire home/apt 103.501922 92.484096\n", " Hotel room 343.660714 93.607143\n", " Private room 58.698305 95.064407\n", " Shared room 53.142857 90.214286\n", "Bourse Entire home/apt 139.618020 91.608503" ] }, "execution_count": 14, "metadata": {}, "output_type": "execute_result" } ], "source": [ "nr.head()" ] }, { "cell_type": "code", "execution_count": 15, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "MultiIndex([('Batignolles-Monceau', 'Entire home/apt'),\n", " ('Batignolles-Monceau', 'Hotel room'),\n", " ('Batignolles-Monceau', 'Private room'),\n", " ('Batignolles-Monceau', 'Shared room'),\n", " ( 'Bourse', 'Entire home/apt'),\n", " ( 'Bourse', 'Hotel room'),\n", " ( 'Bourse', 'Private room'),\n", " ( 'Bourse', 'Shared room'),\n", " ( 'Buttes-Chaumont', 'Entire home/apt'),\n", " ( 'Buttes-Chaumont', 'Hotel room'),\n", " ( 'Buttes-Chaumont', 'Private room'),\n", " ( 'Buttes-Chaumont', 'Shared room'),\n", " ( 'Buttes-Montmartre', 'Entire home/apt'),\n", " ( 'Buttes-Montmartre', 'Hotel room'),\n", " ( 'Buttes-Montmartre', 'Private room'),\n", " ( 'Buttes-Montmartre', 'Shared room'),\n", " ( 'Entrepôt', 'Entire home/apt'),\n", " ( 'Entrepôt', 'Hotel room'),\n", " ( 'Entrepôt', 'Private room'),\n", " ( 'Entrepôt', 'Shared room'),\n", " ( 'Gobelins', 'Entire home/apt'),\n", " ( 'Gobelins', 'Hotel room'),\n", " ( 'Gobelins', 'Private room'),\n", " ( 'Gobelins', 'Shared room'),\n", " ( 'Hôtel-de-Ville', 'Entire home/apt'),\n", " ( 'Hôtel-de-Ville', 'Hotel room'),\n", " ( 'Hôtel-de-Ville', 'Private room'),\n", " ( 'Hôtel-de-Ville', 'Shared room'),\n", " ( 'Louvre', 'Entire home/apt'),\n", " ( 'Louvre', 'Hotel room'),\n", " ( 'Louvre', 'Private room'),\n", " ( 'Louvre', 'Shared room'),\n", " ( 'Luxembourg', 'Entire home/apt'),\n", " ( 'Luxembourg', 'Hotel room'),\n", " ( 'Luxembourg', 'Private room'),\n", " ( 'Luxembourg', 'Shared room'),\n", " ( 'Ménilmontant', 'Entire home/apt'),\n", " ( 'Ménilmontant', 'Hotel room'),\n", " ( 'Ménilmontant', 'Private room'),\n", " ( 'Ménilmontant', 'Shared room'),\n", " ( 'Observatoire', 'Entire home/apt'),\n", " ( 'Observatoire', 'Hotel room'),\n", " ( 'Observatoire', 'Private room'),\n", " ( 'Observatoire', 'Shared room'),\n", " ( 'Opéra', 'Entire home/apt'),\n", " ( 'Opéra', 'Hotel room'),\n", " ( 'Opéra', 'Private room'),\n", " ( 'Opéra', 'Shared room'),\n", " ( 'Palais-Bourbon', 'Entire home/apt'),\n", " ( 'Palais-Bourbon', 'Hotel room'),\n", " ( 'Palais-Bourbon', 'Private room'),\n", " ( 'Palais-Bourbon', 'Shared room'),\n", " ( 'Panthéon', 'Entire home/apt'),\n", " ( 'Panthéon', 'Hotel room'),\n", " ( 'Panthéon', 'Private room'),\n", " ( 'Panthéon', 'Shared room'),\n", " ( 'Passy', 'Entire home/apt'),\n", " ( 'Passy', 'Hotel room'),\n", " ( 'Passy', 'Private room'),\n", " ( 'Passy', 'Shared room'),\n", " ( 'Popincourt', 'Entire home/apt'),\n", " ( 'Popincourt', 'Hotel room'),\n", " ( 'Popincourt', 'Private room'),\n", " ( 'Popincourt', 'Shared room'),\n", " ( 'Reuilly', 'Entire home/apt'),\n", " ( 'Reuilly', 'Hotel room'),\n", " ( 'Reuilly', 'Private room'),\n", " ( 'Reuilly', 'Shared room'),\n", " ( 'Temple', 'Entire home/apt'),\n", " ( 'Temple', 'Hotel room'),\n", " ( 'Temple', 'Private room'),\n", " ( 'Temple', 'Shared room'),\n", " ( 'Vaugirard', 'Entire home/apt'),\n", " ( 'Vaugirard', 'Hotel room'),\n", " ( 'Vaugirard', 'Private room'),\n", " ( 'Vaugirard', 'Shared room'),\n", " ( 'Élysée', 'Entire home/apt'),\n", " ( 'Élysée', 'Hotel room'),\n", " ( 'Élysée', 'Private room'),\n", " ( 'Élysée', 'Shared room')],\n", " names=['neighbourhood_cleansed', 'room_type'])" ] }, "execution_count": 15, "metadata": {}, "output_type": "execute_result" } ], "source": [ "nr.index" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "These indices allow us to do several more things than single index objects. For example:" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "- One-level queries:" ] }, { "cell_type": "code", "execution_count": 16, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
Pricereview_scores_rating
room_type
Entire home/apt139.61802091.608503
Hotel room321.24489893.367347
Private room71.08871093.225806
Shared room35.06250090.875000
\n", "
" ], "text/plain": [ " Price review_scores_rating\n", "room_type \n", "Entire home/apt 139.618020 91.608503\n", "Hotel room 321.244898 93.367347\n", "Private room 71.088710 93.225806\n", "Shared room 35.062500 90.875000" ] }, "execution_count": 16, "metadata": {}, "output_type": "execute_result" } ], "source": [ "nr.xs(\"Bourse\", level=\"neighbourhood_cleansed\")" ] }, { "cell_type": "code", "execution_count": 17, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
Pricereview_scores_rating
neighbourhood_cleansed
Batignolles-Monceau53.14285790.214286
Bourse35.06250090.875000
Buttes-Chaumont25.22857190.400000
Buttes-Montmartre42.55555689.333333
EntrepĂ´t30.45833390.458333
Gobelins35.60000092.533333
HĂ´tel-de-Ville64.00000095.375000
Louvre72.00000093.400000
Luxembourg55.00000088.666667
MĂ©nilmontant34.92307790.730769
Observatoire41.00000097.142857
Opéra44.12500094.500000
Palais-Bourbon35.40000095.000000
Panthéon73.80000095.200000
Passy39.78571493.785714
Popincourt31.88095289.261905
Reuilly30.30000093.200000
Temple30.83333392.833333
Vaugirard51.33333394.750000
Élysée70.00000096.000000
\n", "
" ], "text/plain": [ " Price review_scores_rating\n", "neighbourhood_cleansed \n", "Batignolles-Monceau 53.142857 90.214286\n", "Bourse 35.062500 90.875000\n", "Buttes-Chaumont 25.228571 90.400000\n", "Buttes-Montmartre 42.555556 89.333333\n", "Entrepôt 30.458333 90.458333\n", "Gobelins 35.600000 92.533333\n", "Hôtel-de-Ville 64.000000 95.375000\n", "Louvre 72.000000 93.400000\n", "Luxembourg 55.000000 88.666667\n", "Ménilmontant 34.923077 90.730769\n", "Observatoire 41.000000 97.142857\n", "Opéra 44.125000 94.500000\n", "Palais-Bourbon 35.400000 95.000000\n", "Panthéon 73.800000 95.200000\n", "Passy 39.785714 93.785714\n", "Popincourt 31.880952 89.261905\n", "Reuilly 30.300000 93.200000\n", "Temple 30.833333 92.833333\n", "Vaugirard 51.333333 94.750000\n", "Élysée 70.000000 96.000000" ] }, "execution_count": 17, "metadata": {}, "output_type": "execute_result" } ], "source": [ "nr.xs(\"Shared room\", level=\"room_type\")" ] }, { "cell_type": "code", "execution_count": 18, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "Price 35.0625\n", "review_scores_rating 90.8750\n", "Name: (Bourse, Shared room), dtype: float64" ] }, "execution_count": 18, "metadata": {}, "output_type": "execute_result" } ], "source": [ "nr.loc[(\"Bourse\", \"Shared room\"), :]" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "But also \"unstack\" it so we can cross-tab:" ] }, { "cell_type": "code", "execution_count": 19, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
Pricereview_scores_rating
room_typeEntire home/aptHotel roomPrivate roomShared roomEntire home/aptHotel roomPrivate roomShared room
neighbourhood_cleansed
Batignolles-Monceau103.501922343.66071458.69830553.14285792.48409693.60714395.06440790.214286
Bourse139.618020321.24489871.08871035.06250091.60850393.36734793.22580690.875000
Buttes-Chaumont82.568452117.94736845.86666725.22857193.07780691.36842193.52820590.400000
Buttes-Montmartre87.478750118.34210555.21789942.55555692.94081190.25000093.72957289.333333
EntrepĂ´t101.621981311.73684255.14489330.45833392.99504687.50877293.32066590.458333
Gobelins86.284188106.96153853.23867135.60000091.85897491.34615493.30513692.533333
HĂ´tel-de-Ville154.687543412.29166779.37288164.00000093.12319390.00000093.74576395.375000
Louvre165.077367426.830508102.82894772.00000091.76327991.45762794.50000093.400000
Luxembourg164.863531204.74666787.22656255.00000092.26985492.18666792.51562588.666667
MĂ©nilmontant75.727787128.20000045.13842534.92307793.33279592.80000092.07637290.730769
Observatoire95.354978306.52500090.52140141.00000092.86456493.15000093.50194697.142857
Opéra115.688708287.08965575.56190544.12500093.12614489.17241493.70476294.500000
Palais-Bourbon170.620910450.72222276.14414435.40000092.75738293.52777894.18018095.000000
Panthéon129.989522141.92500069.24675373.80000092.20694290.57500094.00649495.200000
Passy155.033317331.76666777.49767439.78571491.81070292.60000093.66046593.785714
Popincourt95.073171138.33333353.34860631.88095293.04240894.23809593.54183389.261905
Reuilly89.299656104.72727353.46816530.30000093.08209091.63636494.44194893.200000
Temple150.560496241.97727377.47445330.83333392.37879494.29545593.04379692.833333
Vaugirard108.483296288.89189258.14572951.33333392.63251793.06756893.77889494.750000
Élysée194.715942300.59036194.83516570.00000091.54782689.86747092.78022096.000000
\n", "
" ], "text/plain": [ " Price \\\n", "room_type Entire home/apt Hotel room Private room Shared room \n", "neighbourhood_cleansed \n", "Batignolles-Monceau 103.501922 343.660714 58.698305 53.142857 \n", "Bourse 139.618020 321.244898 71.088710 35.062500 \n", "Buttes-Chaumont 82.568452 117.947368 45.866667 25.228571 \n", "Buttes-Montmartre 87.478750 118.342105 55.217899 42.555556 \n", "Entrepôt 101.621981 311.736842 55.144893 30.458333 \n", "Gobelins 86.284188 106.961538 53.238671 35.600000 \n", "Hôtel-de-Ville 154.687543 412.291667 79.372881 64.000000 \n", "Louvre 165.077367 426.830508 102.828947 72.000000 \n", "Luxembourg 164.863531 204.746667 87.226562 55.000000 \n", "Ménilmontant 75.727787 128.200000 45.138425 34.923077 \n", "Observatoire 95.354978 306.525000 90.521401 41.000000 \n", "Opéra 115.688708 287.089655 75.561905 44.125000 \n", "Palais-Bourbon 170.620910 450.722222 76.144144 35.400000 \n", "Panthéon 129.989522 141.925000 69.246753 73.800000 \n", "Passy 155.033317 331.766667 77.497674 39.785714 \n", "Popincourt 95.073171 138.333333 53.348606 31.880952 \n", "Reuilly 89.299656 104.727273 53.468165 30.300000 \n", "Temple 150.560496 241.977273 77.474453 30.833333 \n", "Vaugirard 108.483296 288.891892 58.145729 51.333333 \n", "Élysée 194.715942 300.590361 94.835165 70.000000 \n", "\n", " review_scores_rating \\\n", "room_type Entire home/apt Hotel room Private room \n", "neighbourhood_cleansed \n", "Batignolles-Monceau 92.484096 93.607143 95.064407 \n", "Bourse 91.608503 93.367347 93.225806 \n", "Buttes-Chaumont 93.077806 91.368421 93.528205 \n", "Buttes-Montmartre 92.940811 90.250000 93.729572 \n", "Entrepôt 92.995046 87.508772 93.320665 \n", "Gobelins 91.858974 91.346154 93.305136 \n", "Hôtel-de-Ville 93.123193 90.000000 93.745763 \n", "Louvre 91.763279 91.457627 94.500000 \n", "Luxembourg 92.269854 92.186667 92.515625 \n", "Ménilmontant 93.332795 92.800000 92.076372 \n", "Observatoire 92.864564 93.150000 93.501946 \n", "Opéra 93.126144 89.172414 93.704762 \n", "Palais-Bourbon 92.757382 93.527778 94.180180 \n", "Panthéon 92.206942 90.575000 94.006494 \n", "Passy 91.810702 92.600000 93.660465 \n", "Popincourt 93.042408 94.238095 93.541833 \n", "Reuilly 93.082090 91.636364 94.441948 \n", "Temple 92.378794 94.295455 93.043796 \n", "Vaugirard 92.632517 93.067568 93.778894 \n", "Élysée 91.547826 89.867470 92.780220 \n", "\n", " \n", "room_type Shared room \n", "neighbourhood_cleansed \n", "Batignolles-Monceau 90.214286 \n", "Bourse 90.875000 \n", "Buttes-Chaumont 90.400000 \n", "Buttes-Montmartre 89.333333 \n", "Entrepôt 90.458333 \n", "Gobelins 92.533333 \n", "Hôtel-de-Ville 95.375000 \n", "Louvre 93.400000 \n", "Luxembourg 88.666667 \n", "Ménilmontant 90.730769 \n", "Observatoire 97.142857 \n", "Opéra 94.500000 \n", "Palais-Bourbon 95.000000 \n", "Panthéon 95.200000 \n", "Passy 93.785714 \n", "Popincourt 89.261905 \n", "Reuilly 93.200000 \n", "Temple 92.833333 \n", "Vaugirard 94.750000 \n", "Élysée 96.000000 " ] }, "execution_count": 19, "metadata": {}, "output_type": "execute_result" } ], "source": [ "unstacked = nr.unstack()\n", "unstacked" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "This in turn creates a `MultiIndex` on the columns instead, which works similarly:" ] }, { "cell_type": "code", "execution_count": 20, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
room_typeEntire home/aptHotel roomPrivate roomShared room
neighbourhood_cleansed
Batignolles-Monceau103.501922343.66071458.69830553.142857
Bourse139.618020321.24489871.08871035.062500
Buttes-Chaumont82.568452117.94736845.86666725.228571
Buttes-Montmartre87.478750118.34210555.21789942.555556
EntrepĂ´t101.621981311.73684255.14489330.458333
Gobelins86.284188106.96153853.23867135.600000
HĂ´tel-de-Ville154.687543412.29166779.37288164.000000
Louvre165.077367426.830508102.82894772.000000
Luxembourg164.863531204.74666787.22656255.000000
MĂ©nilmontant75.727787128.20000045.13842534.923077
Observatoire95.354978306.52500090.52140141.000000
Opéra115.688708287.08965575.56190544.125000
Palais-Bourbon170.620910450.72222276.14414435.400000
Panthéon129.989522141.92500069.24675373.800000
Passy155.033317331.76666777.49767439.785714
Popincourt95.073171138.33333353.34860631.880952
Reuilly89.299656104.72727353.46816530.300000
Temple150.560496241.97727377.47445330.833333
Vaugirard108.483296288.89189258.14572951.333333
Élysée194.715942300.59036194.83516570.000000
\n", "
" ], "text/plain": [ "room_type Entire home/apt Hotel room Private room Shared room\n", "neighbourhood_cleansed \n", "Batignolles-Monceau 103.501922 343.660714 58.698305 53.142857\n", "Bourse 139.618020 321.244898 71.088710 35.062500\n", "Buttes-Chaumont 82.568452 117.947368 45.866667 25.228571\n", "Buttes-Montmartre 87.478750 118.342105 55.217899 42.555556\n", "Entrepôt 101.621981 311.736842 55.144893 30.458333\n", "Gobelins 86.284188 106.961538 53.238671 35.600000\n", "Hôtel-de-Ville 154.687543 412.291667 79.372881 64.000000\n", "Louvre 165.077367 426.830508 102.828947 72.000000\n", "Luxembourg 164.863531 204.746667 87.226562 55.000000\n", "Ménilmontant 75.727787 128.200000 45.138425 34.923077\n", "Observatoire 95.354978 306.525000 90.521401 41.000000\n", "Opéra 115.688708 287.089655 75.561905 44.125000\n", "Palais-Bourbon 170.620910 450.722222 76.144144 35.400000\n", "Panthéon 129.989522 141.925000 69.246753 73.800000\n", "Passy 155.033317 331.766667 77.497674 39.785714\n", "Popincourt 95.073171 138.333333 53.348606 31.880952\n", "Reuilly 89.299656 104.727273 53.468165 30.300000\n", "Temple 150.560496 241.977273 77.474453 30.833333\n", "Vaugirard 108.483296 288.891892 58.145729 51.333333\n", "Élysée 194.715942 300.590361 94.835165 70.000000" ] }, "execution_count": 20, "metadata": {}, "output_type": "execute_result" } ], "source": [ "unstacked[\"Price\"]" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "---\n", "\n", "**EXERCISE**\n", "\n", "Create a table that shows the average price for properties by room and property type\n", "\n", "---" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "More at:\n", "\n", "> [https://pandas.pydata.org/pandas-docs/stable/user_guide/advanced.html](https://pandas.pydata.org/pandas-docs/stable/user_guide/advanced.html)" ] } ], "metadata": { "kernelspec": { "display_name": "Python 3", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.8.6" } }, "nbformat": 4, "nbformat_minor": 4 }