ECON/POLI 5D: Data Analytics for the Social Sciences Homework 1 Download the cleaned data set hw1.xlsx (also available on Canvas). The file has data from 2015 on the total population, urban population, GDP, and carbon dioxide (CO2) emissions for 193 countries as reported by the World Bank. Please follow the instructions closely and format your answers as requested. To insert a screenshot into your Word document: 1. Open your Excel file, then go back to your Word document. 2. In your Word document, click “Insert,” then click on the “Screenshot” drop-down menu and click on “Screen Clipping.” 3. Select only the part of the Excel file you are being asked to insert, and it will show up on your Word document. Please write down the names of the people you have worked with (if any) in the box below: 1 ECON/POLI 5D: Data Analytics for the Social Sciences Question 1 (15 points) Create the following variables: a. GDP per capita (divide GDP by the total population) [5 points]. b. CO2 emissions per capita (divide CO2 emissions by the total population) [5 pts]. c. Percent urban (divide the urban population by the total population) [5 pts]. Insert a screenshot of rows 1-10 of the newly created variables in the box below: Question 2 (10 points) Find the median and the standard deviation GDP per capita. Copy-paste the numerical answers and functions in the table below: Median GDP per capita (number): Median GDP per capita (function): Standard deviation of GDP per capita (number): Standard deviation of GDP per capita (function): 2 ECON/POLI 5D: Data Analytics for the Social Sciences Question 3 (5 points) Create one more variable called “Urban/Rural” that equals “Urban” if percent urban > 50% and “Rural” if percent urban < 50%. Insert a screenshot of rows 1-10 of the newly created variable in the box below: Question 4 (5 points) Count the numbers of urban and rural countries using the COUNTIF function. Copy-paste the numerical answers and functions in the table below: Number of urban countries (number): Number of urban countries (function): Number of rural countries (number): Number of urban countries (function): 3 ECON/POLI 5D: Data Analytics for the Social Sciences Question 5 (5 points) Calculate the averages of the GDP per capita and CO2 emissions for urban and rural countries using the AVERAGEIF function. Copy-paste the numerical answers and functions in the table below: Average GDP pc, Urban countries (number): Average GDP pc, Urban countries (function): Average GDP pc, Rural countries (number): Average GDP pc, Rural countries (function): Average CO2 pc, Urban countries (number): Average CO2 pc, Urban countries (function): Average CO2 pc, Rural countries (number): Average CO2 pc, Rural countries (function): Question 6 (5 points) Create a bar graph comparing the average GDP per capita of urban and rural countries. Insert a screenshot of your bar graph in the box below: 4 ECON/POLI 5D: Data Analytics for the Social Sciences Question 7 (5 points) Now you are curious what C02 emissions are like in Greenland. Use VLOOKUP to find the amount of C02 emissions in Greenland. Copy-paste the numerical answer and function in the table below: CO2 emissions in Greenland (number): CO2 emissions in Greenland (function): Question 8 (10 points) Using the LOG function, create the following variables: a. Log of GDP per capita [5 pts]. b. Log of CO2 emissions per capita [5 pts]. Insert a screenshot of rows 1-10 of the newly created variables in the box below: 5 ECON/POLI 5D: Data Analytics for the Social Sciences Question 9 (10 points) Create a scatter plot with the log of GDP per capita on the x axis and the log of CO2 emissions per capita on the y axis. Be sure to label all axes, add a title, and add a trendline. Insert a screenshot of the scatter plot in the box below: Question 10 (30 points) Write a paragraph (max. 150 words) summarizing what the data are telling us about the relationship between economic development, urbanization, and CO2 emissions. On average, are urban countries more or less developed than rural countries? Do urban countries pollute more or less than rural countries? Finally, is there a correlation between economic development and CO2 emissions? If yes, is the correlation positive or negative? 6 Country Name United States China Japan Germany United Kingdom India Brazil Canada Korea, Rep. Russian Federation Australia Spain Mexico Turkey Indonesia Netherlands Switzerland Saudi Arabia Argentina Sweden Nigeria Poland Belgium Thailand Norway Iran, Islamic Rep. Austria United Arab Emirates Egypt, Arab Rep. South Africa Hong Kong SAR, China Singapore Philippines Denmark Malaysia Israel Colombia Ireland Pakistan Chile Finland Portugal Greece Bangladesh Vietnam Peru Country Code GDP (2015 US$) Population, total Urban population USA $18,224,704,440,000.00 320635163 261865944 CHN $11,061,553,079,876.40 1371220000 761027100 JPN $4,389,475,622,588.97 127141000 116182717 DEU $3,356,235,704,119.75 81686611 63062064 GBR $2,928,591,002,002.51 65116219 53802927 IND $2,103,587,817,041.78 1310152403 429428653 BRA $1,802,214,373,741.32 204471769 175375436 CAN $1,556,129,524,418.21 35702908 29011826 KOR $1,465,773,245,547.15 51014947 41645542 RUS $1,363,481,063,446.77 144096870 106703732 AUS $1,351,693,984,524.50 23815995 20410546 ESP $1,195,119,269,971.52 46444832 36971015 MEX $1,171,867,608,197.72 121858258 96615320 TUR $864,314,287,105.73 78529409 57806283 IDN $860,854,235,065.08 258383256 137751865 NLD $765,264,949,781.00 16939923 15275237 CHE $679,832,292,004.98 8282396 6105617 SAU $654,269,902,888.72 31717667 26382755 ARG $594,749,285,413.21 43131966 39467043 SWE $505,103,781,349.76 9799186 8481489 NGA $486,803,295,097.89 181137448 86652532 POL $477,811,911,394.08 37986412 22897449 BEL $462,149,679,343.82 11274196 11034732 THA $401,295,941,041.30 68714511 32772699 NOR $385,801,550,067.17 5188607 4207493 IRN $384,951,479,697.42 78492215 57580319 AUT $381,817,565,893.57 8642699 4988134 ARE $358,135,057,862.49 9262900 7935897 EGY $329,366,576,819.41 92442547 39551544 ZAF $317,620,522,794.83 55386367 35905874 HKG $309,383,627,028.56 7291300 7291300 SGP $308,004,146,057.61 5535002 5535002 PHL $306,445,871,632.26 102113212 47262079 DNK $302,673,070,846.86 5683483 4974525 MYS $301,354,803,994.37 30270962 22464989 ISR $299,813,121,135.46 8380100 7724692 COL $293,481,748,240.78 47520667 37904385 IRL $291,499,812,214.55 4701957 2940510 PAK $270,556,131,701.17 199426964 71845558 CHL $243,919,085,308.94 17969353 15698027 FIN $234,440,080,998.27 5479531 4669930 PRT $199,313,894,327.49 10358076 6578828 GRC $196,591,353,872.13 10820883 8445266 BGD $195,078,678,697.23 156256276 53608403 VNM $193,241,108,709.54 92677076 31333193 PER $189,805,300,841.60 30470734 23571246 Czech Republic Kazakhstan Romania Iraq New Zealand Algeria Qatar Hungary Angola Kuwait Morocco Ecuador Ukraine Slovak Republic Cuba Uzbekistan Sri Lanka Dominican Republic Oman Myanmar Ethiopia Kenya Guatemala Luxembourg Belarus Costa Rica Panama Uruguay Azerbaijan Bulgaria Lebanon Croatia Ghana Tanzania Cote d'Ivoire Macao SAR, China Tunisia Slovenia Yemen, Rep. Lithuania Serbia Jordan Congo, Dem. Rep. Paraguay Turkmenistan Bolivia Uganda CZE KAZ ROU IRQ NZL DZA QAT HUN AGO KWT MAR ECU UKR SVK CUB UZB LKA DOM OMN MMR ETH KEN GTM LUX BLR CRI PAN URY AZE BGR LBN HRV GHA TZA CIV MAC TUN SVN YEM LTU SRB JOR COD PRY TKM BOL UGA $188,033,050,459.88 $184,388,432,148.72 $177,729,210,874.50 $177,498,577,312.92 $177,467,529,071.29 $166,361,491,140.75 $161,739,955,577.75 $125,074,286,021.14 $116,193,649,124.15 $114,567,298,105.68 $101,179,808,076.36 $99,290,381,000.00 $91,030,959,454.70 $88,467,555,243.73 $87,133,000,000.00 $81,847,410,181.80 $80,604,080,688.58 $71,164,825,257.74 $68,399,663,321.93 $67,822,772,707.10 $64,589,334,978.80 $64,007,750,169.33 $62,186,186,575.74 $57,744,457,954.73 $56,454,734,396.58 $54,775,994,478.51 $54,091,700,000.00 $53,274,304,222.18 $53,074,370,486.04 $50,647,442,756.74 $49,939,374,832.70 $49,525,747,503.81 $48,564,863,888.44 $47,378,599,025.30 $45,814,637,971.47 $45,361,678,146.52 $43,173,480,831.97 $43,090,173,394.69 $42,450,620,875.65 $41,418,872,976.12 $39,655,958,842.55 $38,587,017,944.34 $37,917,704,900.08 $36,164,068,797.25 $35,799,714,285.71 $33,000,198,248.34 $32,248,116,583.68 10546059 17542806 19815616 35572261 4595700 39728025 2565710 9843028 27884381 3835591 34663603 16212020 45154036 5423801 11324781 31298900 20970000 10281680 4267348 52680726 100835458 47878336 15567419 569604 9489616 4847804 3968487 3412009 9649341 7177991 6532678 4203604 27849205 51482633 23226143 602085 11179949 2063531 26497889 2904910 7095383 9266575 76244544 6688746 5565287 10869730 38225453 7748928 10032906 10678041 24872481 3967973 28146511 2538642 6939335 17691524 3835591 21078590 10278096 31183829 2922832 8708304 15884192 3828283 8077905 3471488 15729411 19590313 12284623 7779195 513663 7324181 3726119 2646822 3242944 5279540 5310996 5755681 2360534 15062521 16277264 11483934 602085 7608626 1109788 9215171 1952971 3951845 8363640 32586918 4063413 2800285 7434134 8432535 Bahrain Cameroon Libya Latvia Trinidad and Tobago El Salvador Estonia Papua New Guinea Nepal Zambia Honduras Zimbabwe Afghanistan Cyprus Cambodia Senegal Iceland Bosnia and Herzegovina Mozambique Georgia Haiti Botswana Lao PDR Gabon Jamaica West Bank and Gaza Equatorial Guinea Mali Brunei Darussalam Nicaragua South Sudan Congo, Rep. Burkina Faso Mongolia Bahamas, The Mauritius Benin Albania Madagascar Namibia Malta Chad Armenia North Macedonia Niger Guinea Rwanda BHR CMR LBY LVA TTO SLV EST PNG NPL ZMB HND ZWE AFG CYP KHM SEN ISL BIH MOZ GEO HTI BWA LAO GAB JAM PSE GNQ MLI BRN NIC SSD COG BFA MNG BHS MUS BEN ALB MDG NAM MLT TCD ARM MKD NER GIN RWA $31,050,638,297.87 $30,928,653,813.85 $27,842,131,479.87 $27,239,653,843.50 $25,062,893,969.33 $23,438,240,000.00 $23,048,864,242.61 $21,723,531,182.14 $21,410,840,908.52 $21,243,339,048.24 $20,979,767,785.21 $19,963,120,600.00 $19,907,111,418.99 $19,842,404,304.89 $18,049,954,289.42 $17,774,766,636.05 $17,389,101,639.00 $16,211,541,820.24 $15,950,969,018.95 $14,953,950,557.44 $14,857,386,163.92 $14,420,593,484.06 $14,390,442,307.40 $14,383,107,714.04 $14,187,696,311.36 $13,972,400,000.00 $13,185,496,836.41 $13,104,802,016.20 $12,930,394,937.81 $12,756,706,583.31 $11,997,800,760.22 $11,953,249,529.74 $11,832,159,275.60 $11,749,620,619.60 $11,710,800,000.00 $11,692,287,066.38 $11,388,160,958.25 $11,386,846,319.16 $11,323,023,786.57 $11,272,143,472.62 $11,091,434,435.23 $10,950,392,219.91 $10,553,337,672.99 $10,064,515,432.03 $9,685,577,941.03 $8,794,202,443.67 $8,545,441,361.07 1371851 23298368 6418315 1977527 1370328 6325124 1315407 8107775 27015031 15879361 9112916 13814629 34413603 1160985 15521436 14578459 330815 3429361 27042002 3725276 10695542 2120716 6741164 1947686 2891021 4270092 1168568 17438778 414907 6223240 10715658 4856095 18110624 2998439 374206 1262605 10575952 2880703 24234088 2314904 445053 14110975 2925553 2079328 20001663 11432088 11369071 1220934 12715783 5087798 1344323 730645 4408611 899949 1054984 5013179 6654564 5027140 4473868 8535606 777233 3443896 6685973 309974 1617732 9302449 2140097 5607352 1424167 2231865 1716262 1585234 3218283 825196 6973942 318080 3602945 2020116 3182830 4985855 2045835 309640 517668 4832681 1654503 8528703 1085690 420192 3177086 1845585 1193701 3249670 4017350 1933197 Tajikistan TJK Moldova MDA Kyrgyz Republic KGZ Bermuda BMU Malawi MWI Liechtenstein LIE Mauritania MRT Suriname SUR Barbados BRB Cayman Islands CYM Fiji FJI Guyana GUY Sierra Leone SLE Togo TGO Maldives MDV Eswatini SWZ Montenegro MNE Liberia LBR Curacao CUW Burundi BDI Aruba ABW Andorra AND Faroe Islands FRO Greenland GRL Djibouti DJI Lesotho LSO Bhutan BTN St. Lucia LCA Belize BLZ Central African Republic CAF Cabo Verde CPV Timor-Leste TLS Gambia, The GMB Seychelles SYC Antigua and Barbuda ATG Solomon Islands SLB Guinea-Bissau GNB Grenada GRD Comoros COM Turks and Caicos Islands TCA St. Kitts and Nevis KNA Samoa WSM Vanuatu VUT St. Vincent and the Grenadines VCT Dominica DMA Tonga TON Micronesia, Fed. Sts. FSM $7,854,602,391.65 $7,745,231,660.33 $6,678,178,340.45 $6,654,541,000.00 $6,373,212,640.85 $6,268,391,521.20 $6,166,857,628.62 $4,787,367,928.12 $4,715,000,000.00 $4,708,336,756.09 $4,682,546,881.16 $4,279,840,361.74 $4,218,723,875.14 $4,180,866,177.04 $4,109,447,297.01 $4,062,660,889.83 $4,053,097,851.45 $3,177,000,000.00 $3,151,910,782.12 $3,104,394,858.12 $2,919,553,072.63 $2,789,870,187.51 $2,518,096,285.62 $2,499,115,623.00 $2,430,287,967.03 $2,372,280,102.52 $2,003,598,212.99 $1,808,079,888.89 $1,723,800,000.00 $1,695,825,708.46 $1,596,800,287.16 $1,594,410,800.00 $1,378,176,868.32 $1,377,495,054.04 $1,336,692,592.59 $1,307,061,543.71 $1,048,229,629.42 $997,007,925.93 $966,029,542.04 $942,070,000.00 $923,155,555.56 $788,307,330.56 $759,689,950.93 $755,400,000.00 $540,737,037.04 $437,006,227.16 $316,489,900.00 8454028 2834530 5956900 65237 16745303 37470 4046301 559136 285324 61724 868627 767432 7171914 7323158 454915 1104044 622159 4472230 157980 10160030 104341 78011 48051 56114 913993 2059021 727876 179126 360933 4493170 524743 1196302 2085860 93419 93566 603118 1737202 109599 777424 35981 51204 193513 271130 109148 71183 100781 108895 2260776 1204392 2131200 65237 2731661 5359 2067215 369343 89161 61724 475365 202917 2928221 2936586 175274 257242 409418 2228065 141158 1227128 44979 68919 20007 48298 707586 554041 281528 33165 163885 1809714 337410 352789 1235413 51754 23392 134857 731762 39452 221333 33173 15785 36601 67677 55626 49528 23457 24453 Sao Tome and Principe Palau Marshall Islands Kiribati Nauru Tuvalu STP PLW MHL KIR NRU TUV $316,065,369.89 $280,457,700.00 $184,599,600.00 $171,117,872.44 $86,779,266.16 $35,492,074.22 199432 17665 57439 110930 12475 11099 139949 13807 43545 57261 12475 6629 CO2 emissions (metric tons, total) 5126913374 10145004856 1163803124 727045089 405038485 2337749170 504388516 549430277 597354300 1698213369 365332209 250496437 482947567 350337846 512749276 172492013 35672576 647111823 201167953 38180804 115928538 285659300 99346364 284364849 47212625 650078426 61264569 194765371 224581748 463952507 42647210 61451586 117120313 30817468 234823679 66207685 94109888 35907264 188542472 83244567 42606873 49258811 65059914 81073703 188329786 52636118 99852410 242091673 71715519 167273872 34887838 153133920 106841712 44418371 34583477 92984119 61271903 43270600 196283509 31283177 29244325 102929023 20113495 24136194 65386277 22299027 13718247 17018547 15834106 9251841 58279631 7513683 11129345 6699609 37597751 44737400 24469891 17311907 16552838 12933509 10960663 2090190 30252750 12530139 13171864 13083856 43919659 26200715 2838258 6303573 70674091 20073158 5342819 32892990 7928054 56996181 7007637 46255538 6849956 16362154 7480680 6252235 4532412 10018244 12317453 9035488 6138558 8628451 10637967 1987514 18569688 6486923 9798224 2933600 5430827 9046489 5262145 7572355 3006940 6028548 3168288 7073643 5331818 1943510 3230627 3329636 23285450 2016850 4213383 6065218 4616753 3644998 3989696 1620814 1001091 4803770 7099312 2027851 2702579 960754 5005455 4943116 10447283 557384 1151438 51338 2893263 1723490 1254114 557384 2156196 2016850 1067097 2636573 1301785 982756 2240537 1239446 6395248 403370 898415 465709 608722 509713 627057 2346880 1067097 410704 579386 293360 495045 502379 524381 491378 546383 190684 278692 260357 168682 205352 234688 234688 132012 220020 176016 121011 143013 113677 223687 143013 62339 47671 11001 Median SD Number of countries Avg. CO2 Avg. GDP/capita emissions/capita GDP per capita Urban Rural Total CO2 Emissions Greenland 
Purchase answer to see full attachment