{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "# Adding New Columns\n", "\n", "In the second chapter we will learn how to add new columns to the tables based on a set of functions. This is similar to the Functions that are available in Excel with _=FUNC(A1)_\n", "\n", "[![Open In Studio Lab](https://studiolab.sagemaker.aws/studiolab.svg)](https://studiolab.sagemaker.aws/import/github/aiola-lab/from-excel-to-pandas/blob/master/notebooks/02.01_Adding_New_Columns.ipynb)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "We will start with loading a csv file that is hosted in data.world on the \"Median Value Per Sq ft per zip code in the US\"" ] }, { "cell_type": "code", "execution_count": 1, "metadata": {}, "outputs": [], "source": [ "import pandas as pd" ] }, { "cell_type": "code", "execution_count": 3, "metadata": {}, "outputs": [], "source": [ "us_median_sq_ft_value = (\n", " pd\n", " .read_csv('https://query.data.world/s/xrfy7fb7oq55gpzh6bvs6jtonv32lk')\n", ")" ] }, { "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", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
RegionIDRegionNameCityStateMetroCountyNameSizeRank1996-041996-051996-06...2016-122017-012017-022017-032017-042017-052017-062017-072017-082017-09
06163910025New YorkNYNew YorkNew York1NaNNaNNaN...1306.0129012771272127112771281127912741270
18465460657ChicagoILChicagoCook2134.0134.0133.0...291.0294296295293294294294294295
26163710023New YorkNYNew YorkNew York3NaNNaNNaN...1602.0159715801567156915701560154315291521
38461660614ChicagoILChicagoCook4149.0150.0150.0...330.0333333331330330329328329329
49314479936El PasoTXEl PasoEl Paso550.051.050.0...81.0818181818182828282
\n", "

5 rows × 265 columns

\n", "
" ], "text/plain": [ " RegionID RegionName City State Metro CountyName SizeRank \\\n", "0 61639 10025 New York NY New York New York 1 \n", "1 84654 60657 Chicago IL Chicago Cook 2 \n", "2 61637 10023 New York NY New York New York 3 \n", "3 84616 60614 Chicago IL Chicago Cook 4 \n", "4 93144 79936 El Paso TX El Paso El Paso 5 \n", "\n", " 1996-04 1996-05 1996-06 ... 2016-12 2017-01 2017-02 2017-03 \\\n", "0 NaN NaN NaN ... 1306.0 1290 1277 1272 \n", "1 134.0 134.0 133.0 ... 291.0 294 296 295 \n", "2 NaN NaN NaN ... 1602.0 1597 1580 1567 \n", "3 149.0 150.0 150.0 ... 330.0 333 333 331 \n", "4 50.0 51.0 50.0 ... 81.0 81 81 81 \n", "\n", " 2017-04 2017-05 2017-06 2017-07 2017-08 2017-09 \n", "0 1271 1277 1281 1279 1274 1270 \n", "1 293 294 294 294 294 295 \n", "2 1569 1570 1560 1543 1529 1521 \n", "3 330 330 329 328 329 329 \n", "4 81 81 82 82 82 82 \n", "\n", "[5 rows x 265 columns]" ] }, "execution_count": 4, "metadata": {}, "output_type": "execute_result" } ], "source": [ "us_median_sq_ft_value.head()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Constant Value\n", "\n", "The simplest way to add a column is to put a constant value. Since we know that this data is only for USA we can add a column with the value _USA_ to a new column called _country_. We will be able to later merge this table with data from other countries and then this new column will be useful. \n", "\n", "For setting a value in a column we will use the [assign](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.assign.html) function of Pandas. You will need to scroll all the way to the right of the output to see the new _country_ column." ] }, { "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", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
RegionIDRegionNameCityStateMetroCountyNameSizeRank1996-041996-051996-06...2017-012017-022017-032017-042017-052017-062017-072017-082017-09country
06163910025New YorkNYNew YorkNew York1NaNNaNNaN...129012771272127112771281127912741270USA
18465460657ChicagoILChicagoCook2134.0134.0133.0...294296295293294294294294295USA
26163710023New YorkNYNew YorkNew York3NaNNaNNaN...159715801567156915701560154315291521USA
38461660614ChicagoILChicagoCook4149.0150.0150.0...333333331330330329328329329USA
49314479936El PasoTXEl PasoEl Paso550.051.050.0...818181818182828282USA
..................................................................
147507394036564FairhopeALDaphneBaldwin14751NaNNaNNaN...230238243245244245246244240USA
14751591073293WoodstockNHClaremontGrafton1475258.059.061.0...129129128129130130130130131USA
147528239655713BuhlMNDuluthSaint Louis14753NaNNaNNaN...727374747373747779USA
147536688121405AnnapolisMDBaltimoreAnne Arundel14754146.0149.0151.0...470462457457455454455458460USA
147549481585220Apache JunctionAZPhoenixPinal14755NaNNaNNaN...117119119119120121122124124USA
\n", "

14755 rows × 266 columns

\n", "
" ], "text/plain": [ " RegionID RegionName City State Metro CountyName \\\n", "0 61639 10025 New York NY New York New York \n", "1 84654 60657 Chicago IL Chicago Cook \n", "2 61637 10023 New York NY New York New York \n", "3 84616 60614 Chicago IL Chicago Cook \n", "4 93144 79936 El Paso TX El Paso El Paso \n", "... ... ... ... ... ... ... \n", "14750 73940 36564 Fairhope AL Daphne Baldwin \n", "14751 59107 3293 Woodstock NH Claremont Grafton \n", "14752 82396 55713 Buhl MN Duluth Saint Louis \n", "14753 66881 21405 Annapolis MD Baltimore Anne Arundel \n", "14754 94815 85220 Apache Junction AZ Phoenix Pinal \n", "\n", " SizeRank 1996-04 1996-05 1996-06 ... 2017-01 2017-02 2017-03 \\\n", "0 1 NaN NaN NaN ... 1290 1277 1272 \n", "1 2 134.0 134.0 133.0 ... 294 296 295 \n", "2 3 NaN NaN NaN ... 1597 1580 1567 \n", "3 4 149.0 150.0 150.0 ... 333 333 331 \n", "4 5 50.0 51.0 50.0 ... 81 81 81 \n", "... ... ... ... ... ... ... ... ... \n", "14750 14751 NaN NaN NaN ... 230 238 243 \n", "14751 14752 58.0 59.0 61.0 ... 129 129 128 \n", "14752 14753 NaN NaN NaN ... 72 73 74 \n", "14753 14754 146.0 149.0 151.0 ... 470 462 457 \n", "14754 14755 NaN NaN NaN ... 117 119 119 \n", "\n", " 2017-04 2017-05 2017-06 2017-07 2017-08 2017-09 country \n", "0 1271 1277 1281 1279 1274 1270 USA \n", "1 293 294 294 294 294 295 USA \n", "2 1569 1570 1560 1543 1529 1521 USA \n", "3 330 330 329 328 329 329 USA \n", "4 81 81 82 82 82 82 USA \n", "... ... ... ... ... ... ... ... \n", "14750 245 244 245 246 244 240 USA \n", "14751 129 130 130 130 130 131 USA \n", "14752 74 73 73 74 77 79 USA \n", "14753 457 455 454 455 458 460 USA \n", "14754 119 120 121 122 124 124 USA \n", "\n", "[14755 rows x 266 columns]" ] }, "execution_count": 5, "metadata": {}, "output_type": "execute_result" } ], "source": [ "(\n", " us_median_sq_ft_value\n", " .assign(country=\"USA\")\n", ")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Excel Functions Equivalent\n", "\n", "Most of the functions that you are used to use in Excel have a direct Equivalent in Pands. We will try a few of them in the following examples. We will only look at the _City_ column in the dataset.\n", "\n", "For example: ```=TRIM('cell for trimming')``` can be replaced by _strip_. \n", "We will:\n", "* Take only the _City_ column from the table \n", "* Add a column that strips spaces from around the string (_str_) of the _City_ column\n", "* Show the head (first 5 lines) of the new sub-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", "
Citytrimmed
0New YorkNew York
1ChicagoChicago
2New YorkNew York
3ChicagoChicago
4El PasoEl Paso
\n", "
" ], "text/plain": [ " City trimmed\n", "0 New York New York\n", "1 Chicago Chicago\n", "2 New York New York\n", "3 Chicago Chicago\n", "4 El Paso El Paso" ] }, "execution_count": 7, "metadata": {}, "output_type": "execute_result" } ], "source": [ "(\n", " us_median_sq_ft_value[['City']]\n", " .assign(trimmed = us_median_sq_ft_value.City.str.strip())\n", " .head()\n", ")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "or to remove spaces between words, you will need to run 'Find and Replace' option in Excel and in Pandas we can use the _replace_ function:\n", "* Take only the _City_ column from the table \n", "* Add a column that is removes every space (' ') from the string of the _City_ column\n", "* Show the head of the new sub-table" ] }, { "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", "
Cityno_space
0New YorkNewYork
1ChicagoChicago
2New YorkNewYork
3ChicagoChicago
4El PasoElPaso
\n", "
" ], "text/plain": [ " City no_space\n", "0 New York NewYork\n", "1 Chicago Chicago\n", "2 New York NewYork\n", "3 Chicago Chicago\n", "4 El Paso ElPaso" ] }, "execution_count": 8, "metadata": {}, "output_type": "execute_result" } ], "source": [ "(\n", " us_median_sq_ft_value[['City']]\n", " .assign(no_space = us_median_sq_ft_value.City.str.replace(' ',''))\n", " .head()\n", ")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### More Textual Functions\n", "\n", "Another commonly used Excel functions are LEFT and RIGHT (```=LEFT(Cell, number of digits)```). In Pandas, we can use the '```:```', which is used for _until_ or _from_:\n", "* Take only the _City_ column from the table \n", "* Add a column that takes the first 5 characters (=LEFT) from the string of the _City_ column\n", "* Add a column that takes the last 5 characters (=RIGHT) from the string of the _City_ column\n", "* Show the head of the new sub-table\n" ] }, { "cell_type": "code", "execution_count": 9, "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", "
Citycity_leftcity_right
0New YorkNew YYork
1ChicagoChicaicago
2New YorkNew YYork
3ChicagoChicaicago
4El PasoEl PaPaso
\n", "
" ], "text/plain": [ " City city_left city_right\n", "0 New York New Y York\n", "1 Chicago Chica icago\n", "2 New York New Y York\n", "3 Chicago Chica icago\n", "4 El Paso El Pa Paso" ] }, "execution_count": 9, "metadata": {}, "output_type": "execute_result" } ], "source": [ "(\n", " us_median_sq_ft_value[['City']]\n", " .assign(city_left = us_median_sq_ft_value.City.str[:5]) # until the 5th character\n", " .assign(city_right = us_median_sq_ft_value.City.str[-5:]) # from the 5th characters from the end (=right)\n", " .head()\n", ")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Using Lambda\n", "\n", "Lambda is a bit confusing at first glance, however, it is used to define a the function that we want to apply on a each row or columns of the table. For example, let's define two new columns: one that is calculating the quantile of the region compare to all the regions, and one that is checking if the state is one of ['NY','NJ']\n", "\n", "We will focus only on the _State_ and the last month in the data and add the two new columns to it.\n", "* Take only the _State_ and the values of the last period columns from the table \n", "* Add a column that checks if the _State_ is either _'NY'_ or _'NJ'_\n", "* Add a column that calculates the ratio of the average price of the row to the maximum price (quantile)" ] }, { "cell_type": "code", "execution_count": 10, "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", "
State2017-09is_NY_or_NJlast_quantile
0NY1270True0.702434
1IL295False0.163164
2NY1521True0.841261
3IL329False0.181969
4TX82False0.045354
...............
14750AL240False0.132743
14751NH131False0.072456
14752MN79False0.043695
14753MD460False0.254425
14754AZ124False0.068584
\n", "

14755 rows × 4 columns

\n", "
" ], "text/plain": [ " State 2017-09 is_NY_or_NJ last_quantile\n", "0 NY 1270 True 0.702434\n", "1 IL 295 False 0.163164\n", "2 NY 1521 True 0.841261\n", "3 IL 329 False 0.181969\n", "4 TX 82 False 0.045354\n", "... ... ... ... ...\n", "14750 AL 240 False 0.132743\n", "14751 NH 131 False 0.072456\n", "14752 MN 79 False 0.043695\n", "14753 MD 460 False 0.254425\n", "14754 AZ 124 False 0.068584\n", "\n", "[14755 rows x 4 columns]" ] }, "execution_count": 10, "metadata": {}, "output_type": "execute_result" } ], "source": [ "(\n", " us_median_sq_ft_value[['State','2017-09']]\n", " .assign(is_NY_or_NJ=lambda x : x.State.isin(['NY','NJ']))\n", " .assign(last_quantile=lambda x : x['2017-09']/max(x['2017-09'])) \n", ")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Multiple Data Sources\n", "\n", "We can do a more complicated analysis that is based on additional external sources. For example, we want to calculate the differences between red states and blue states. We can then ask various questions regarding the impacts or correlation between the house prices and the election results and voting patterns. \n", "\n", "First we will load data regarding the voting in various states across the years. A quick internet search finds an interesting table in the following Wikipedia page. Since there are multiple tables on that page, we can filter them using the _match_ option, and using the word _Year_ as the filter.\n", "* Read the HTML tables in the wikipedia entry on the topic of red and blue states\n", "* retrive on the ones that have the word 'Year'" ] }, { "cell_type": "code", "execution_count": 11, "metadata": {}, "outputs": [], "source": [ "red_blue_states_wikipedia_entry = 'https://en.wikipedia.org/wiki/Red_states_and_blue_states'\n", "wikipedia_page_tables = (\n", " pd\n", " .read_html(\n", " red_blue_states_wikipedia_entry, \n", " match='Year'\n", " )\n", ")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "As we saw in the loading data from web site, the list can includes multiple tables. However, we filtered it and only have one table, and we can access it with the _[0]_ modifier." ] }, { "cell_type": "code", "execution_count": 12, "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", "
Year1972197619801984198819921996200020042008201220162020
Democratic Republican (lighter shading indicates win ≤5%) Winner did not receive a majority of the popular vote Winner did not receive a majority of the popular vote and lost the popular vote Winner chosen by the House of Representatives Electoral votes invalidatedDemocratic Republican (lighter shading indicates win ≤5%) Winner did not receive a majority of the popular vote Winner did not receive a majority of the popular vote and lost the popular vote Winner chosen by the House of Representatives Electoral votes invalidatedDemocratic Republican (lighter shading indicates win ≤5%) Winner did not receive a majority of the popular vote Winner did not receive a majority of the popular vote and lost the popular vote Winner chosen by the House of Representatives Electoral votes invalidatedDemocratic Republican (lighter shading indicates win ≤5%) Winner did not receive a majority of the popular vote Winner did not receive a majority of the popular vote and lost the popular vote Winner chosen by the House of Representatives Electoral votes invalidatedDemocratic Republican (lighter shading indicates win ≤5%) Winner did not receive a majority of the popular vote Winner did not receive a majority of the popular vote and lost the popular vote Winner chosen by the House of Representatives Electoral votes invalidatedDemocratic Republican (lighter shading indicates win ≤5%) Winner did not receive a majority of the popular vote Winner did not receive a majority of the popular vote and lost the popular vote Winner chosen by the House of Representatives Electoral votes invalidatedDemocratic Republican (lighter shading indicates win ≤5%) Winner did not receive a majority of the popular vote Winner did not receive a majority of the popular vote and lost the popular vote Winner chosen by the House of Representatives Electoral votes invalidatedDemocratic Republican (lighter shading indicates win ≤5%) Winner did not receive a majority of the popular vote Winner did not receive a majority of the popular vote and lost the popular vote Winner chosen by the House of Representatives Electoral votes invalidatedDemocratic Republican (lighter shading indicates win ≤5%) Winner did not receive a majority of the popular vote Winner did not receive a majority of the popular vote and lost the popular vote Winner chosen by the House of Representatives Electoral votes invalidatedDemocratic Republican (lighter shading indicates win ≤5%) Winner did not receive a majority of the popular vote Winner did not receive a majority of the popular vote and lost the popular vote Winner chosen by the House of Representatives Electoral votes invalidatedDemocratic Republican (lighter shading indicates win ≤5%) Winner did not receive a majority of the popular vote Winner did not receive a majority of the popular vote and lost the popular vote Winner chosen by the House of Representatives Electoral votes invalidatedDemocratic Republican (lighter shading indicates win ≤5%) Winner did not receive a majority of the popular vote Winner did not receive a majority of the popular vote and lost the popular vote Winner chosen by the House of Representatives Electoral votes invalidatedDemocratic Republican (lighter shading indicates win ≤5%) Winner did not receive a majority of the popular vote Winner did not receive a majority of the popular vote and lost the popular vote Winner chosen by the House of Representatives Electoral votes invalidatedDemocratic Republican (lighter shading indicates win ≤5%) Winner did not receive a majority of the popular vote Winner did not receive a majority of the popular vote and lost the popular vote Winner chosen by the House of Representatives Electoral votes invalidated
0Democratic candidateGeorge McGovernJimmy CarterJimmy CarterWalter MondaleMichael DukakisBill ClintonBill ClintonAl GoreJohn KerryBarack ObamaBarack ObamaHillary ClintonJoe Biden
1Republican candidateRichard NixonGerald FordRonald ReaganRonald ReaganGeorge H. W. BushGeorge H. W. BushBob DoleGeorge W. BushGeorge W. BushJohn McCainMitt RomneyDonald TrumpDonald Trump
2National popular voteNixonCarterReaganReaganBushClintonClintonGoreBushObamaObamaClintonBiden
3AlabamaNixonCarterReaganReaganBushBushDoleBushBushMcCainRomneyTrumpTrump
4AlaskaNixonFordReaganReaganBushBushDoleBushBushMcCainRomneyTrumpTrump
\n", "
" ], "text/plain": [ " \n", "3 Reagan \n", "4 Reagan \n", "\n", " 1984 \\\n", " Democratic Republican (lighter shading indicates win ≤5%) Winner did not receive a majority of the popular vote Winner did not receive a majority of the popular vote and lost the popular vote Winner chosen by the House of Representatives Electoral votes invalidated \n", "0 Walter Mondale \n", "1 Ronald Reagan \n", "2 Reagan \n", "3 Reagan \n", "4 Reagan \n", "\n", " 1988 \\\n", " Democratic Republican (lighter shading indicates win ≤5%) Winner did not receive a majority of the popular vote Winner did not receive a majority of the popular vote and lost the popular vote Winner chosen by the House of Representatives Electoral votes invalidated \n", "0 Michael Dukakis \n", "1 George H. W. Bush \n", "2 Bush \n", "3 Bush \n", "4 Bush \n", "\n", " 1992 \\\n", " Democratic Republican (lighter shading indicates win ≤5%) Winner did not receive a majority of the popular vote Winner did not receive a majority of the popular vote and lost the popular vote Winner chosen by the House of Representatives Electoral votes invalidated \n", "0 Bill Clinton \n", "1 George H. W. Bush \n", "2 Clinton \n", "3 Bush \n", "4 Bush \n", "\n", " 1996 \\\n", " Democratic Republican (lighter shading indicates win ≤5%) Winner did not receive a majority of the popular vote Winner did not receive a majority of the popular vote and lost the popular vote Winner chosen by the House of Representatives Electoral votes invalidated \n", "0 Bill Clinton \n", "1 Bob Dole \n", "2 Clinton \n", "3 Dole \n", "4 Dole \n", "\n", " 2000 \\\n", " Democratic Republican (lighter shading indicates win ≤5%) Winner did not receive a majority of the popular vote Winner did not receive a majority of the popular vote and lost the popular vote Winner chosen by the House of Representatives Electoral votes invalidated \n", "0 Al Gore \n", "1 George W. Bush \n", "2 Gore \n", "3 Bush \n", "4 Bush \n", "\n", " 2004 \\\n", " Democratic Republican (lighter shading indicates win ≤5%) Winner did not receive a majority of the popular vote Winner did not receive a majority of the popular vote and lost the popular vote Winner chosen by the House of Representatives Electoral votes invalidated \n", "0 John Kerry \n", "1 George W. Bush \n", "2 Bush \n", "3 Bush \n", "4 Bush \n", "\n", " 2008 \\\n", " Democratic Republican (lighter shading indicates win ≤5%) Winner did not receive a majority of the popular vote Winner did not receive a majority of the popular vote and lost the popular vote Winner chosen by the House of Representatives Electoral votes invalidated \n", "0 Barack Obama \n", "1 John McCain \n", "2 Obama \n", "3 McCain \n", "4 McCain \n", "\n", " 2012 \\\n", " Democratic Republican (lighter shading indicates win ≤5%) Winner did not receive a majority of the popular vote Winner did not receive a majority of the popular vote and lost the popular vote Winner chosen by the House of Representatives Electoral votes invalidated \n", "0 Barack Obama \n", "1 Mitt Romney \n", "2 Obama \n", "3 Romney \n", "4 Romney \n", "\n", " 2016 \\\n", " Democratic Republican (lighter shading indicates win ≤5%) Winner did not receive a majority of the popular vote Winner did not receive a majority of the popular vote and lost the popular vote Winner chosen by the House of Representatives Electoral votes invalidated \n", "0 Hillary Clinton \n", "1 Donald Trump \n", "2 Clinton \n", "3 Trump \n", "4 Trump \n", "\n", " 2020 \n", " Democratic Republican (lighter shading indicates win ≤5%) Winner did not receive a majority of the popular vote Winner did not receive a majority of the popular vote and lost the popular vote Winner chosen by the House of Representatives Electoral votes invalidated \n", "0 Joe Biden \n", "1 Donald Trump \n", "2 Biden \n", "3 Trump \n", "4 Trump " ] }, "execution_count": 12, "metadata": {}, "output_type": "execute_result" } ], "source": [ "red_blue_states = wikipedia_page_tables[0]\n", "red_blue_states.head()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "We will start with creating a new list of states that voted for a specific candidate in a specific electio. First, let's use Trump in the 2016 elections. \n", "\n", "We are using here a few new functions such as [rename](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.rename.html), [query](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.query.html) and [iloc](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.iloc.html), which we will discuss in more details in the next sections." ] }, { "cell_type": "code", "execution_count": 13, "metadata": {}, "outputs": [], "source": [ "red_candidate = 'Trump'" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "We will:\n", "* Take the large table of the multiple elections results\n", "* Remove the complicated multi level header from the table (_droplevel_)\n", "* Filter to only states that have the name of the red candidate, we defined above, in the column of the election year (_2020_, for example). Note, we need to surround the name of the column with ` as it starts with a number\n", "* Take only the first column from the result table, which holds the name of the state (_iloc[:,0]_). " ] }, { "cell_type": "code", "execution_count": 14, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "3 Alabama\n", "4 Alaska\n", "6 Arkansas\n", "12 Florida\n", "15 Idaho\n", "17 Indiana\n", "18 Iowa\n", "19 Kansas\n", "20 Kentucky\n", "21 Louisiana\n", "29 Mississippi\n", "30 Missouri\n", "31 Montana\n", "39 North Carolina\n", "40 North Dakota\n", "41 Ohio\n", "42 Oklahoma\n", "46 South Carolina\n", "47 South Dakota\n", "48 Tennessee\n", "49 Texas\n", "50 Utah\n", "54 West Virginia\n", "56 Wyoming\n", "Name: Year, dtype: object" ] }, "execution_count": 14, "metadata": {}, "output_type": "execute_result" } ], "source": [ "red_states_2020 = (\n", " red_blue_states\n", " .droplevel(1, axis=1) \n", " .query(\"`2020` == @red_candidate\")\n", " .iloc[:,0]\n", ")\n", "red_states_2020" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Now that we have the list of the red states of 2020, we need to match them to the states that we have in our house prices data set. We see that we have to match the short version in the data set with the long version in the red state list. \n", "\n", "We will use a another option to create a data frame using a Dictionary. It is not hard to find a list of mapping of states names and abbreviations. " ] }, { "cell_type": "code", "execution_count": 15, "metadata": {}, "outputs": [], "source": [ "us_state_abbrev = {\n", " 'Alabama': 'AL',\n", " 'Alaska': 'AK',\n", " 'American Samoa': 'AS',\n", " 'Arizona': 'AZ',\n", " 'Arkansas': 'AR',\n", " 'California': 'CA',\n", " 'Colorado': 'CO',\n", " 'Connecticut': 'CT',\n", " 'Delaware': 'DE',\n", " 'District of Columbia': 'DC',\n", " 'Florida': 'FL',\n", " 'Georgia': 'GA',\n", " 'Guam': 'GU',\n", " 'Hawaii': 'HI',\n", " 'Idaho': 'ID',\n", " 'Illinois': 'IL',\n", " 'Indiana': 'IN',\n", " 'Iowa': 'IA',\n", " 'Kansas': 'KS',\n", " 'Kentucky': 'KY',\n", " 'Louisiana': 'LA',\n", " 'Maine': 'ME',\n", " 'Maryland': 'MD',\n", " 'Massachusetts': 'MA',\n", " 'Michigan': 'MI',\n", " 'Minnesota': 'MN',\n", " 'Mississippi': 'MS',\n", " 'Missouri': 'MO',\n", " 'Montana': 'MT',\n", " 'Nebraska': 'NE',\n", " 'Nevada': 'NV',\n", " 'New Hampshire': 'NH',\n", " 'New Jersey': 'NJ',\n", " 'New Mexico': 'NM',\n", " 'New York': 'NY',\n", " 'North Carolina': 'NC',\n", " 'North Dakota': 'ND',\n", " 'Northern Mariana Islands':'MP',\n", " 'Ohio': 'OH',\n", " 'Oklahoma': 'OK',\n", " 'Oregon': 'OR',\n", " 'Pennsylvania': 'PA',\n", " 'Puerto Rico': 'PR',\n", " 'Rhode Island': 'RI',\n", " 'South Carolina': 'SC',\n", " 'South Dakota': 'SD',\n", " 'Tennessee': 'TN',\n", " 'Texas': 'TX',\n", " 'Utah': 'UT',\n", " 'Vermont': 'VT',\n", " 'Virgin Islands': 'VI',\n", " 'Virginia': 'VA',\n", " 'Washington': 'WA',\n", " 'West Virginia': 'WV',\n", " 'Wisconsin': 'WI',\n", " 'Wyoming': 'WY'\n", "}" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "and we can use the dictionary to create a table (DataFrame) from it:\n", "* Create a new dataframe table\n", "* Using the dictionary of the us state abbreviation above\n", "* with row for each entry\n", "* and name the column of the value 'Abbreviation' " ] }, { "cell_type": "code", "execution_count": 16, "metadata": {}, "outputs": [], "source": [ "us_state_abbrev_df = (\n", " pd\n", " .DataFrame\n", " .from_dict(us_state_abbrev, \n", " orient='index',\n", " columns=['Abbreviation'])\n", ")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Before we continue, we can keep this table as file to be used in future analyses. We could also create this file in Excel and load it as we will do in the future with this file. " ] }, { "cell_type": "code", "execution_count": 17, "metadata": {}, "outputs": [], "source": [ "us_state_abbrev_df.to_csv('../data/us_state_abbrev.csv')" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Next, we will:\n", "* Start with the abbreviation list of all the US states\n", "* Take only the ones that also appear in the list of red states in the 2020 election\n", "* Take all the rows left, and the index column (country name) with the abbreviation column" ] }, { "cell_type": "code", "execution_count": 18, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "Alabama AL\n", "Alaska AK\n", "Arkansas AR\n", "Florida FL\n", "Idaho ID\n", "Indiana IN\n", "Iowa IA\n", "Kansas KS\n", "Kentucky KY\n", "Louisiana LA\n", "Mississippi MS\n", "Missouri MO\n", "Montana MT\n", "North Carolina NC\n", "North Dakota ND\n", "Ohio OH\n", "Oklahoma OK\n", "South Carolina SC\n", "South Dakota SD\n", "Tennessee TN\n", "Texas TX\n", "Utah UT\n", "West Virginia WV\n", "Wyoming WY\n", "Name: Abbreviation, dtype: object" ] }, "execution_count": 18, "metadata": {}, "output_type": "execute_result" } ], "source": [ "red_state_abbrev = (\n", " us_state_abbrev_df\n", " .loc[red_states_2020]\n", " .loc[:,'Abbreviation']\n", ")\n", "red_state_abbrev" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Finally:\n", "* Start with the table of the median value of sqr feet per zip code\n", "* Add a new column (_is_red_) that checks if the _State_ in each row is in the list of red states that we calculated above. \n", "* Create two groups for red and blue states and the a value in the last period in the table (September 2017)\n", "* Calculate the average (_mean_) price for each of the two groups\n", "* Plot the two values\n", "* Title the graph with \"Average square feet value between blue and red states\"" ] }, { "cell_type": "code", "execution_count": 19, "metadata": { "tags": [] }, "outputs": [ { "data": { "image/png": "iVBORw0KGgoAAAANSUhEUgAAAXcAAAEmCAYAAACZEtCsAAAAOXRFWHRTb2Z0d2FyZQBNYXRwbG90bGliIHZlcnNpb24zLjMuMiwgaHR0cHM6Ly9tYXRwbG90bGliLm9yZy8vihELAAAACXBIWXMAAAsTAAALEwEAmpwYAAAdcklEQVR4nO3de5wcZZ3v8c83CYT7NQNCgATZyBG8RJ0DevDCCq6AHFF35aIIETTwEo7XdQXXVXBhxQug+9IFgyAgd0GEXVkFEciiBBkwIlclGEhiSIYAGm6ahN/543naVJrumZ7p6enkme/79ZrXVD1VXfWr6upvVz99KUUEZmZWlnHdLsDMzEaew93MrEAOdzOzAjnczcwK5HA3MyuQw93MrEAOd3sRSadIelzSY92upRlJUyWFpAkdWPZ8SfuO9HLXFnm//U2TaTMk3TraNQ2VpJslfahDy+7YsTWa1rpwz3fak5ImdruWsUjSTsCngN0i4iVtLmudCIqRVPoTgzUn6SRJFw1h/r0lLexUPWtVuEuaCrwJCOCdHVj+Ov1MPBzD2OadgGURsbQT9ZiNtrH4uAcgItaaP+DzwM+BM4D/ym0TgaeAV1Tm6wGeA7bJ4wcCc/N8vwBeVZl3PvAZ4G7gz8AE4ARgHrAcuA94d2X+8cDpwOPA74HjSU82E/L0zYFzgcXAIuAUYHyT7dkD6AP+BCwBzqhM+wDwCLAM+Odc57552vnAKZV59wYWVsYHqn9G3odn5mWfkvfh14BHcx1nAxs2qHffvF9fAJ4Gzs/tr8/79Sng18Delds03B/Ay4HngVV5WU81WN8hQF9d2yeAa/PwO4Bf5f23ADipMt/Uuvvlr/svj58EXFQZb7oNDeqaD5yY9+2TwHeBDSrTGx5vwPfyvnsub/M/ARcAn8rTJ+eaj8vjuwBPAONaOI63B64C+knH5UfrtvUK4MJ8TNwL9A6wfQF8FHiYdJx/tVLDDODWRvs4t90MfKgyfhRwf95PPwGmDLDe7wOPAX8EZgO7V6adD3wL+FHehtuBXSrT3wY8kG/7TeCWah116zkJuBK4iHTsfIgBHrek4/VreV88DBxXv911y/9MXsZy4EFgH2A/4C/Ainzf/zrP+8G8f5bnZR+T2zdmzcfa0/k+Hsfqx/eyfL9ulW+zQd6mZfkYuQPYtun+7nRgD+UPeAj4CPC6vJO2ze3nAadW5jsO+HEefg2wFNgz30lHkh6cEysP1LnAjuRAA95b2ZGHAM8A2+Vpx5Ie1DsAWwI/Zc0QuRr4dr5ztgF+WbvDGmzPbcAH8vAmwOvz8G75znwzKXjPAFbSergPVP+MvKz/R3oi25AU9NcCWwGbAv8JfKlJzfXrmpwPpgPy+t6Wx3sG2x9UgqLJujYiHfTTKm13AIdWanllXu+rSE9M72oUPAwQ7oNtQ4O65gP3kI6ZrUhPlqcM4Xir1nEU8J95+H2kB+3llWnXDLbcXPOdpJOf9YGXkoLi7ZVtfT5v33jgS8CcAfZ7ADflbdsJ+C05KBlCuAMHkR6zLycda58DfjHAeo8iHX8Tga8DcyvTzs/3yR55WRcDl+Vpk0jHyT8A65FOAFYycLivAN6V992GDHycHkt64qjd3zfVb3dl2buSTjS2r+yjXeqPucr87yA9iQt4C/As8NpGj7Xc9jFgDil/JuaaL83TjiE9djfK9/PrgM2a7u/RCO1W/oA35jtkUh5/APhEHt4XmFeZ9+fAEXn4LOBf65b1IPCWyoPtqEHWPRc4KA//jEpY53VHPuC2JZ39b1iZfhhwU5PlzgZOrm1Tpf3ztQM3j29MetZvKdwHqX8G8GhlmkjhXz0LegPw+ybLWmNdpLOU79XN8xNS+Ay4Pxgk3PM8FwGfz8PTSA/ijZrM+3XgzMqDqtVwb7oNTdYzHzi2Mn5A7fhr8Xir1rEL6ax2HOkV0zG1/Us6q//kYMslBf6jddNOBL5b2dafVqbtBjw3wD4PYL/K+EeAG+vvs/p9nNtuZnW4/zdwdGXaOFJ4TRnoPs/zbpGXvXnlmP9O3T5/IA8fQeXJinRML2TgcJ9dGR/sOP1Z3f39d/XbXZn2N6Qn4X2B9Rqs96JGNVXm+SHwsUaPtdx2P7BPZXw7Ui5OID05rvGKbqC/tanP/Ujg+oh4PI9fktsgPZNuJGnP3C8/nfRMDDAF+JSkp2p/pGfg7SvLXlBdkaQjJM2tzP8K0tkB+XYLmtx2CunMYXHltt8mnQk0cjTwMuABSXdIOrDROiLiGdJZS0sGqb++5h7SM/2dlfl/nNtbMQV4b93+fSPpoBvq/mjkEtIDDdKZ7Q8j4tm8nXtKuklSv6Q/ks6wJjVZznC3oZnqPnyE1cdTK8fbX0XEPNKT63TS+0n/BfxB0q6k4L6lheVOAbavm/ZZUmjVVD/Z9CywwSB9zc22byimAN+o1PQEKXgn188oabyk0yTNk/Qn0pMgrHl/1m/DJnm4/vESdfU3MpTHbf1j/pFmC42Ih4CPk4J8qaTLJDXdd5L2lzRH0hN5vQcw8DE8Bbi6Uuf9pK7NbUndfj8BLpP0B0lfkbReswWtFW80SNoQOBgYX/n43URgC0mvjohfS7qCFAJLSP3xy/N8C0hdNqcOsIqorGsKcA6pn+y2iFglaS7poITUJ7dD5bY7VoYXkM4AJkXEysG2KyJ+BxwmaRzwHuBKSVvndby8UtNGwNaVmz5DCuSal1TmHaz+NbaX1I/4HKl/c9FgNTewgHTW++H6CZK2Y+D9EQ3a6t0A9EiaTrp/P1GZdgmpf3X/iHhe0tdp/sBous8G2oYBVO/3nYA/VJY10PHWaJtvIXUprB8RiyTdQjpx2ZL0qmvA5UqqvdKaNoT6B7MjqW8e1ty+qmfy/41Ifdfw4v16akRc3ML63kfqxtmXFOybk17RaIDb1Cymcn9IEmveP41U74fBHrdrLJ+0P5ovOOIS4BJJm5GeJL5Meg9tjfs+f+LvKtIrj2siYoWkH7J6mxsdKwtIPQ0/b7L6k4GT80nudaRXd+c2mnFtOXN/F+nZaTfSGc50Uvj9D2nHQHqgHwK8Pw/XnAMcm8/yJGljSe+QtGmTdW1M2qn9AJI+SDrzrbkC+JikyZK2IL2kByAiFgPXA6dL2kzSOEm7SHpLoxVJOlxST0S8QHoDBNIbKFcCB0p6o6T1gS+y5n0xFzhA0laSXkI6U2i1/jXkdZ8DnClpm3ybyZLe3uw2dS4C/q+kt+ezrw3yR7h2aGF/LAF2yNvYrL4VpDfavkrq77yhMnlT4Ikc7HuQAqKZucChktaT1EsK00G3YYDlHSdpB0lbkd7wvjy3D3a8LSH1iVfdQnpjfnYevzmP3xoRq1pY7i+B5ZI+I2nDvA2vkPS/B6h/MJ+WtKWkHUn9vJfXzxAR/aQ3Dg/P6zyK1M1UczZwoqTdASRtLum9Tda3KSlgl5GeLP5tCLX+CNhd0nvyq5GPsuaTzIBaOE6vAD6a7+8tSW9oNiRpV0lvzcH9PKvfFIV030/NJ3OQ3h+ZSHqsrpS0P6nLh8r8W0vavNJ2NnBqPolDUo+kg/Lw30p6paTxpCfbFZV1v8jaEu5HkvoPH42Ix2p/pLO290uaEBG3k84ktif19QEQEX3Ah/O8T5Le4JnRbEURcR/p0zC3kXbuK0l9+DXnkA6Eu0mf1LiO9OZN7UF4BOlOq32S4kqav7zfD7hX0tPAN0hvFD4XEfeS3hS+hHTW8CSpD7Hme6RPdMzPtfz1gddC/Y18hrRf5ii9JP4p6Y2hQUXEAtIZ12dJB+kC4NOsPnYG2h8/I50dPibpcZq7hHRG9/26M6uPAF+UtJz0PsUVAyzjX1jdv30ylROAFrahWU3Xk964nEf6dEUrx9uXgM8pvaz+x9x2CyncauF+KyngauMDLjc/ARxIOun5PenV2HdIZ7/DdQ3pTdq5pPBsePaXa/o0KZR3J/X51mq+mnTWelk+ru4B9m+ynAtJ3R2LSMfKnFYLzV217wVOy3VMY/Bjvt5Ax+k5pO6OXwN3AT8YYDkTcx2Pk7qRtiG9/wHpJAVgmaS7cu/CR0nH7ZOkk5NrK9v1AHAp8HA+XrYn5cS1wPX5uJ9Des8F0hPalaRgv590XH2vWaHKnfbWRH62PTsipnR4PfNJbxD9tJPrMbOxYW05c19r5Je9B0iaIGky8AVWv3lrZrZOcLi/mEgv658kdcvcT+oSMDNbZ7hbxsysQD5zNzMr0FrxOfdJkybF1KlTu12Gmdk65c4773w8Ihp+IXGtCPepU6fS19fX7TLMzNYpkpp+m9bdMmZmBXK4m5kVyOFuZlagQcNd0o5Kv8x3n6R7JX0st28l6QZJv8v/t8ztkvTvkh6SdLek13Z6I8zMbE2tnLmvJF1JZjfS1WyOk7Qb6cd1bsy/VHcjq39sZ3/Sbz9MA2aSfqfazMxG0aDhHhGLI+KuPLyc9I3NyaQfYrogz3YB6Zcdye0XRjKH9LO9A/1utpmZjbAh9bkr/Ybwa0jXN9w2/5QmpF9Hq104YDJr/vD9Qhr/eP9MSX2S+vr7+4dat5mZDaDlcJe0CemH5z8eEX+qTstXRhnS7xhExKyI6I2I3p6eVi8KZGZmrWgp3JUu5XQVcHFE1H7reEmtuyX/X5rbF7HmVU12yG1mZjZKBv2GqiSRfsj//og4ozLpWtJFNk7L/6+ptB8v6TLSj8z/sdJ9s06besKPul1CUeaf9o5ul2BWrFZ+fmAv0vUBf6N0rU5IV7Q5DbhC0tGkK6wcnKddR7oI7EOki9x+cCQLNjOzwQ0a7hFxK80vYrtPg/mDdAk5MzPrEn9D1cysQA53M7MCOdzNzArkcDczK5DD3cysQA53M7MCOdzNzArkcDczK5DD3cysQA53M7MCOdzNzArkcDczK5DD3cysQA53M7MCOdzNzArkcDczK5DD3cysQA53M7MCDRruks6TtFTSPZW2yyXNzX/za9dWlTRV0nOVaWd3sHYzM2uilQtknw98E7iw1hARh9SGJZ0O/LEy/7yImD5C9ZmZ2TC0coHs2ZKmNpomScDBwFtHuC4zM2tDu33ubwKWRMTvKm07S/qVpFskvanZDSXNlNQnqa+/v7/NMszMrKrdcD8MuLQyvhjYKSJeA3wSuETSZo1uGBGzIqI3Inp7enraLMPMzKqGHe6SJgDvAS6vtUXEnyNiWR6+E5gHvKzdIs3MbGjaOXPfF3ggIhbWGiT1SBqfh18KTAMebq9EMzMbqlY+CnkpcBuwq6SFko7Okw5lzS4ZgDcDd+ePRl4JHBsRT4xgvWZm1oJWPi1zWJP2GQ3argKuar8sMzNrh7+hamZWIIe7mVmBHO5mZgVyuJuZFcjhbmZWIIe7mVmBHO5mZgVyuJuZFcjhbmZWIIe7mVmBHO5mZgVyuJuZFcjhbmZWIIe7mVmBHO5mZgVyuJuZFcjhbmZWoFYus3eepKWS7qm0nSRpkaS5+e+AyrQTJT0k6UFJb+9U4WZm1lwrZ+7nA/s1aD8zIqbnv+sAJO1Gurbq7vk2/1G7YLaZmY2eQcM9ImYDrV7k+iDgsoj4c0T8HngI2KON+szMbBja6XM/XtLdudtmy9w2GVhQmWdhbnsRSTMl9Unq6+/vb6MMMzOrN9xwPwvYBZgOLAZOH+oCImJWRPRGRG9PT88wyzAzs0aGFe4RsSQiVkXEC8A5rO56WQTsWJl1h9xmZmajaFjhLmm7yui7gdonaa4FDpU0UdLOwDTgl+2VaGZmQzVhsBkkXQrsDUyStBD4ArC3pOlAAPOBYwAi4l5JVwD3ASuB4yJiVUcqNzOzpgYN94g4rEHzuQPMfypwajtFmZlZe/wNVTOzAjnczcwK5HA3MyuQw93MrEAOdzOzAjnczcwK5HA3MyuQw93MrEAOdzOzAjnczcwK5HA3MyuQw93MrEAOdzOzAjnczcwK5HA3MyuQw93MrEAOdzOzAg0a7pLOk7RU0j2Vtq9KekDS3ZKulrRFbp8q6TlJc/Pf2R2s3czMmmjlzP18YL+6thuAV0TEq4DfAidWps2LiOn579iRKdPMzIZi0HCPiNnAE3Vt10fEyjw6B9ihA7WZmdkwjUSf+1HAf1fGd5b0K0m3SHpTsxtJmimpT1Jff3//CJRhZmY1bYW7pH8GVgIX56bFwE4R8Rrgk8AlkjZrdNuImBURvRHR29PT004ZZmZWZ9jhLmkGcCDw/ogIgIj4c0Qsy8N3AvOAl41AnWZmNgTDCndJ+wH/BLwzIp6ttPdIGp+HXwpMAx4eiULNzKx1EwabQdKlwN7AJEkLgS+QPh0zEbhBEsCc/MmYNwNflLQCeAE4NiKeaLhgMzPrmEHDPSIOa9B8bpN5rwKuarcoMzNrj7+hamZWIIe7mVmBHO5mZgVyuJuZFcjhbmZWIIe7mVmBHO5mZgVyuJuZFcjhbmZWIIe7mVmBHO5mZgVyuJuZFcjhbmZWIIe7mVmBHO5mZgVyuJuZFcjhbmZWoEGvxAQg6TzSxbCXRsQrcttWwOXAVGA+cHBEPKl03b1vAAcAzwIzIuKukS/dzKqmnvCjbpdQjPmnvaPbJbSt1TP384H96tpOAG6MiGnAjXkcYH/ShbGnATOBs9ov08zMhqKlcI+I2UD9ha4PAi7IwxcA76q0XxjJHGALSduNQK1mZtaidvrct42IxXn4MWDbPDwZWFCZb2FuW4OkmZL6JPX19/e3UYaZmdUbkTdUIyKAGOJtZkVEb0T09vT0jEQZZmaWtRPuS2rdLfn/0ty+CNixMt8Ouc3MzEZJO+F+LXBkHj4SuKbSfoSS1wN/rHTfmJnZKGj1o5CXAnsDkyQtBL4AnAZcIelo4BHg4Dz7daSPQT5E+ijkB0e4ZjMzG0RL4R4RhzWZtE+DeQM4rp2izMysPf6GqplZgRzuZmYFcribmRXI4W5mViCHu5lZgRzuZmYFcribmRXI4W5mViCHu5lZgRzuZmYFcribmRXI4W5mViCHu5lZgRzuZmYFcribmRXI4W5mViCHu5lZgVq6ElMjknYFLq80vRT4PLAF8GGgP7d/NiKuG+56zMxs6IYd7hHxIDAdQNJ4YBFwNemaqWdGxNdGokAzMxu6keqW2QeYFxGPjNDyzMysDSMV7ocCl1bGj5d0t6TzJG3Z6AaSZkrqk9TX39/faBYzMxumtsNd0vrAO4Hv56azgF1IXTaLgdMb3S4iZkVEb0T09vT0tFuGmZlVjMSZ+/7AXRGxBCAilkTEqoh4ATgH2GME1mFmZkMwEuF+GJUuGUnbVaa9G7hnBNZhZmZDMOxPywBI2hh4G3BMpfkrkqYDAcyvm2ZmZqOgrXCPiGeArevaPtBWRWZm1jZ/Q9XMrEAOdzOzAjnczcwK5HA3MyuQw93MrEAOdzOzAjnczcwK5HA3MyuQw93MrEAOdzOzAjnczcwK5HA3MyuQw93MrEAOdzOzAjnczcwK5HA3MyuQw93MrEBtXYkJQNJ8YDmwClgZEb2StgIuB6aSLrV3cEQ82e66zMysNSN15v63ETE9Inrz+AnAjRExDbgxj5uZ2SjpVLfMQcAFefgC4F0dWo+ZmTUwEuEewPWS7pQ0M7dtGxGL8/BjwLb1N5I0U1KfpL7+/v4RKMPMzGra7nMH3hgRiyRtA9wg6YHqxIgISVF/o4iYBcwC6O3tfdF0MzMbvrbP3CNiUf6/FLga2ANYImk7gPx/abvrMTOz1rUV7pI2lrRpbRj4O+Ae4FrgyDzbkcA17azHzMyGpt1umW2BqyXVlnVJRPxY0h3AFZKOBh4BDm5zPWZmNgRthXtEPAy8ukH7MmCfdpZtZmbD52+ompkVyOFuZlYgh7uZWYEc7mZmBXK4m5kVyOFuZlYgh7uZWYEc7mZmBXK4m5kVyOFuZlYgh7uZWYEc7mZmBXK4m5kVyOFuZlYgh7uZWYEc7mZmBXK4m5kVaNjhLmlHSTdJuk/SvZI+lttPkrRI0tz8d8DIlWtmZq1o5zJ7K4FPRcRd+SLZd0q6IU87MyK+1n55ZmY2HMMO94hYDCzOw8sl3Q9MHqnCzMxs+Eakz13SVOA1wO256XhJd0s6T9KWTW4zU1KfpL7+/v6RKMPMzLK2w13SJsBVwMcj4k/AWcAuwHTSmf3pjW4XEbMiojcient6etotw8zMKtoKd0nrkYL94oj4AUBELImIVRHxAnAOsEf7ZZqZ2VC082kZAecC90fEGZX27SqzvRu4Z/jlmZnZcLTzaZm9gA8Av5E0N7d9FjhM0nQggPnAMW2sw8zMhqGdT8vcCqjBpOuGX46ZmY0Ef0PVzKxADnczswI53M3MCuRwNzMrkMPdzKxADnczswI53M3MCuRwNzMrkMPdzKxADnczswI53M3MCuRwNzMrkMPdzKxADnczswI53M3MCuRwNzMrkMPdzKxADnczswJ1LNwl7SfpQUkPSTqhU+sxM7MX60i4SxoPfAvYH9iNdNHs3TqxLjMze7FOnbnvATwUEQ9HxF+Ay4CDOrQuMzOrM6FDy50MLKiMLwT2rM4gaSYwM48+LenBDtUyFk0CHu92EYPRl7tdgXWBj82RNaXZhE6F+6AiYhYwq1vrL5mkvojo7XYdZvV8bI6eTnXLLAJ2rIzvkNvMzGwUdCrc7wCmSdpZ0vrAocC1HVqXmZnV6Ui3TESslHQ88BNgPHBeRNzbiXVZQ+7usrWVj81Roojodg1mZjbC/A1VM7MCOdzNzArkcDczK5DD3cw6Rsnhkj6fx3eStEe36xoLHO6FkLSRpH+RdE4enybpwG7XZWPefwBvAA7L48tJvztlHeZwL8d3gT+THkiQvjR2SvfKMQNgz4g4DngeICKeBNbvbkljg8O9HLtExFeAFQAR8Syg7pZkxor8K7EBIKkHeKG7JY0NDvdy/EXShqx+EO1COpM366Z/B64GtpF0KnAr8G/dLWls8JeYCiHpbcDnSL+ffz2wFzAjIm7uZl1mkv4XsA/pleSNEXF/l0saExzuBZG0NfB60oNoTkSs9T+tamWTtFOj9oh4dLRrGWsc7oWQtBcwNyKekXQ48FrgGxHxSJdLszFM0m9IXYUCNgB2Bh6MiN27WtgY4D73cpwFPCvp1cAngXnAhd0tyca6iHhlRLwq/59Gukrbbd2uayxwuJdjZaSXYQcB34qIbwGbdrkmszVExF3UXZXNOqNrV2KyEbdc0onA4cCbJY0D1utyTTbGSfpkZXQcqbvwD10qZ0zxmXs5DiF99PHoiHiMdPWrr3a3JDM2rfxNBH5EenVpHeY3VM2sI/KXl74cEf/Y7VrGInfLrOMkLSd/cal+EhARsdkol2SGpAn5imx7dbuWscpn7mY24iTdFRGvlXQWMBn4PvBMbXpE/KBrxY0RPnMvjKRtSJ8nBvxlEeu6DYBlwFtZ/Xn3ABzuHeZwL4SkdwKnA9sDS4EpwP2Avyxi3bBN/qTMPawO9Rp3F4wCf1qmHP9K+umB30bEzqTf8pjT3ZJsDBsPbJL/Nq0M1/6sw3zmXo4VEbFM0jhJ4yLiJklf73ZRNmYtjogvdruIsczhXo6nJG0CzAYulrSUyhtYZqPM1xLoMn9aZh0naaeIeFTSxsBzpK629wObAxdHxLKuFmhjkqStIuKJbtcxljnc13G1j5zl4asi4u+7XZOZdZ/fUF33VV/+vrRrVZjZWsXhvu6LJsNmNoa5W2YdJ2kV6Y1TARsCz9Ym4Z8fMBuzHO5mZgVyt4yZWYEc7mZmBXK4m5kVyOFuY4akX4zSemZI+uZorMusGYe7jRkR8X/auX2+spDZOsHhbmOGpKfz/+0kzZY0V9I9kt400G0knS7p18AbJB0u6Zf5tt+uBb6kD0r6raRfAr76kHWdw93GovcBP4mI6cCrgbkDzLsxcHtEvJp00YlDgL3ybVcB75e0HXAyKdTfCOzWscrNWuRfhbSx6A7gPEnrAT+MiLkDzLsKuCoP7wO8DrhDEqQvjS0F9gRujoh+AEmXAy/rTOlmrfGZu405ETEbeDOwCDhf0hEDzP58RKzKwwIuiIjp+W/XiDipw+WaDYvD3cYcSVOAJRFxDvAd4LUt3vRG4B/ydWqRtFVe1u3AWyRtnV8NvLcTdZsNhbtlbCzaG/i0pBXA08BAZ+5/FRH3SfoccL2kccAK4LiImCPpJOA24CkG7sM3GxX+bRkzswK5W8bMrEDuljEDJN0OTKxr/kBE/KYb9Zi1y90yZmYFcreMmVmBHO5mZgVyuJuZFcjhbmZWoP8PPpv3SlymhpsAAAAASUVORK5CYII=", "image/svg+xml": "\n\n\n\n \n \n \n \n 2021-01-10T10:42:45.830371\n image/svg+xml\n \n \n Matplotlib v3.3.2, https://matplotlib.org/\n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n\n", "text/plain": [ "
" ] }, "metadata": { "needs_background": "light" }, "output_type": "display_data" } ], "source": [ "(\n", " us_median_sq_ft_value\n", " .assign(is_red=lambda x : x.State.isin(red_state_abbrev))\n", " .groupby('is_red')['2017-09']\n", " .mean()\n", " .plot\n", " .bar(title=\"Average square feet value between blue and red states\")\n", ");" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "We can now see that the average value of square feet in the blue states (_is_red = False_) is about twice the average of red states (_is_red = True_)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [] } ], "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.3-final" } }, "nbformat": 4, "nbformat_minor": 2 }