30:T5c5,df_summed={ var sumTable=[] var migrationB={} var migrationValB={} for (var i=0; i r.VAR=="B11", migrationCountry[countryBirth[i]]) migrationB[countryBirth[i][1]]=z.filter(r=> r.VAR=="B12", migrationCountry[countryBirth[i]]) migrationB[countryBirth[i][2]]=z.filter(r=> r.VAR=="B14", migrationCountry[countryBirth[i]]) migrationValB[countryBirth[i][0]]=z.getCol("Value", migrationB[countryBirth[i][0]]) migrationValB[countryBirth[i][1]]=z.getCol("Value", migrationB[countryBirth[i][1]]) migrationValB[countryBirth[i][2]]=z.getCol("Value", migrationB[countryBirth[i][2]]) //calculate the sum of the value column for each variables within a particulat country array, and push the results in the summary table as a new array. //since it is not reasonable to have non-integral number of people. I use "parseInt" function to turn all the values into integral before logging out. sumTable.push({"Country Code":countryBirth[i], "Inflows of foreign population by nationality":parseInt(migrationValB[countryBirth[i][0]].reduce(function(a, b) { return a + b; }, 0)) ,"Outflows of foreign population by nationality": parseInt(migrationValB[countryBirth[i][1]].reduce(function(a, b) { return a + b; }, 0)), "Stock of foreign-born population by country of birth":parseInt(migrationValB[countryBirth[i][2]].reduce(function(a, b) { return a + b; }, 0))}) } return sumTable }31:T40d,// aggregates the countries for birth by Variable df_sum2={ var migrationParse=z.parseNums(["Value"], migration) var migrationV=z.groupBy(x=>x.Variable,migrationParse) var variables=z.unique(z.getCol("Variable", migration)) var sumObject={} var aggregate=[] for (var i=0; i d.CO2, migrationV[variables[i]])) } //organize each country's information as an object for (var i=0; ix.group==countryBirth[i],sumObject[variables[j]]) //first loop the values of variables into an object, then pushed into the array obj[variables[j]]=parseInt(countrySrc[0]["sum"]) } //push that object into an array (data frame) aggregate.push(obj) } return aggregate } 32:[] 2e:{"title":"PSET 3","cells":[{"id":0,"value":"md`# PSET 3`","pinned":false,"hidden":false,"mode":"ojs"},{"id":10,"value":"md`## Data Management\nThis problem set is meant to create a foundation of data management using Zebras library and other Javascript manipulation. We emphasize code readability and reusability over performance. Code performance can be measured in many different ways, and is by no means the focus of this course, however, we expect your code to be non-repetitive, clean, and efficient enough to deal with medium-to-large datasets.\n\nYou can always look up [Zebras documentation](https://zebrasjs.com/) for help.\n\n## Submission Instructions\nSubmit the link of your assignment on Stellar. \\n\n**Remember to publish your notebook so it is publicly accesible!** \\n\n`","pinned":false,"hidden":false,"mode":"ojs"},{"id":19,"value":"md`## 1. Loading data\nLoad [\\`MIG_18022019163824069.csv\\`](https://gist.githubusercontent.com/cesandoval/b834ac93c07e03ec5205843b97f68017/raw/6dff0d45ed26352a7a3c7afb4ace0bad1ce8ba20/MIG_18022019163824069.csv)`\n","pinned":false,"hidden":false,"mode":"ojs"},{"id":183,"value":"// Loading necessary library here\nmigrationUrl = \"https://gist.githubusercontent.com/cesandoval/b834ac93c07e03ec5205843b97f68017/raw/6dff0d45ed26352a7a3c7afb4ace0bad1ce8ba20/MIG_18022019163824069.csv\"","pinned":true,"hidden":false,"mode":"ojs"},{"id":245,"value":"d3 = require(\"d3\")","pinned":false,"hidden":false,"mode":"ojs"},{"id":243,"value":"migration = d3.csv(migrationUrl)","pinned":true,"hidden":false,"mode":"ojs"},{"id":45,"value":"md`\n## 2. Unique value\n\nTo start us off, we need to determine how many different countries of birth we have registered in our dataset. Let's take a look at unique countries of birth in the data set.\n- Get the unique values for *Country of Birth*.\n`","pinned":false,"hidden":false,"mode":"ojs"},{"id":26,"value":"z = require('https://bundle.run/zebras@0.0.11')","pinned":true,"hidden":false,"mode":"ojs"},{"id":253,"value":"z.unique(z.getCol('Country of birth/nationality', migration))","pinned":true,"hidden":false,"mode":"ojs"},{"id":57,"value":"md`\n## 3. Grouping and sorting data\n\nThere are a lot of countries represented in our dataset! Let's explore which countries have the highest number of \\`Inflows of foreign population by nationality, Outflows of foreign population by nationality, Stock of foreign-born population by country of birth\\`.\n- Group your data and find the total number of \\`Inflows of foreign population by nationality, Outflows of foreign population by nationality, Stock of foreign-born population by country of birth\\` **per country**, and sum them in a Zebras table.\n- Call your new data frame \\`df_summed\\`.\n`","pinned":false,"hidden":false,"mode":"ojs"},{"id":425,"value":"//pre-treatment\nmigrationCountry={\n//only keep the varaibles we are interested in summarizing\nvar migrationFilter=z.filter(r=> r.VAR==\"B11\"|r.VAR==\"B12\"||r.VAR==\"B14\", migration)\n//turn values into numbers\nvar migrationParse=z.parseNums([\"Value\"], migrationFilter)\n//group by countries\nreturn z.groupBy(x => x.CO2, migrationParse)\n}","pinned":true,"hidden":false,"mode":"ojs"},{"id":440,"value":"countryBirth=z.unique(z.getCol('CO2', migration))","pinned":true,"hidden":false,"mode":"ojs"},{"id":505,"value":"$30","pinned":true,"hidden":false,"mode":"ojs"},{"id":565,"value":"z.head(5, df_summed)","pinned":false,"hidden":false,"mode":"ojs"},{"id":114,"value":"md`## 4. Joining data\nFor this question, we want to compare how the immigration stats differ across continents. You can find a list of Countries, County Codes, and Continents [here](https://gist.githubusercontent.com/cesandoval/b834ac93c07e03ec5205843b97f68017/raw/0c14fa1b1f65a610491417b2894fceecde5f4260/country-and-continent-codes-list-csv_csv.csv)\n\n- Load this data.\n- Create a data frame that only keeps the \\`Country Codes \\` and the \\`Continent\\`.\n- Join your datasets by \\`Country Code\\`\n- Create a new aggregated dataset that counts the number of \\`Inflows of foreign population by nationality, Outflows of foreign population by nationality, Stock of foreign-born population by country of birth\\` by \\`Continent\\`\n`","pinned":false,"hidden":false,"mode":"ojs"},{"id":91,"value":"// Your code here\nData2 = \"https://gist.githubusercontent.com/cesandoval/b834ac93c07e03ec5205843b97f68017/raw/0c14fa1b1f65a610491417b2894fceecde5f4260/country-and-continent-codes-list-csv_csv.csv\"","pinned":false,"hidden":false,"mode":"ojs"},{"id":590,"value":"migration2 = d3.csv(Data2)","pinned":true,"hidden":false,"mode":"ojs"},{"id":92,"value":"// create a data frame only keeps the Country Codes and the Continent.\nmigration2Subset=z.pickCols([\"Three_Letter_Country_Code\", \"Continent_Code\"], migration2)","pinned":true,"hidden":false,"mode":"ojs"},{"id":112,"value":"// join data set by country code\nmigrationJoined=z.merge(df_summed, migration2Subset, \"Country Code\", \"Three_Letter_Country_Code\")\n//Question: why we only join by country code? Some countries are in different continents thus the country codes are not unique in migration2Subset (our newly created dataset). ","pinned":true,"hidden":false,"mode":"ojs"},{"id":651,"value":"//delete those observations unable to be merged with the second dataset\nmigration2Filter=z.filter(r => r.Continent_Code != undefined, migrationJoined)","pinned":true,"hidden":false,"mode":"ojs"},{"id":621,"value":"newAggregate={\n var groupContinent={}\n var newSum=[]\n //first group by continents to calculate the sum of different variables of interest.\n groupContinent[[0]]=z.gbSum(\"Inflows of foreign population by nationality\", z.groupBy(d => d.Continent_Code, migration2Filter))\n groupContinent[[1]]=z.gbSum(\"Outflows of foreign population by nationality\", z.groupBy(d => d.Continent_Code, migration2Filter))\n groupContinent[[2]]=z.gbSum(\"Stock of foreign-born population by country of birth\", z.groupBy(d => d.Continent_Code, migration2Filter))\n //for each continent, push the information needed and organized in an array\n for (var i=0; i<6; i++){\n newSum.push({\"Continent Code\":groupContinent[[0]][i].group, \"Inflows of foreign population by nationality\":parseInt(groupContinent[[0]][i].sum),\"Outflows of foreign population by nationality\":parseInt(groupContinent[[1]][i].sum) ,\"Stock of foreign-born population by country of birth\":parseInt(groupContinent[[2]][i].sum)} )\n }\n return newSum\n}","pinned":true,"hidden":false,"mode":"ojs"},{"id":677,"value":"z.head(6, newAggregate)","pinned":true,"hidden":false,"mode":"ojs"},{"id":78,"value":"md`\n## 5. Filtering data\n\nThis is alot of values! It doesn't make much sense to show them all, let's group categories into three:\n\n\\`High Immigration\\`, \\`Medium Immigration\\`, or \\`Low Immigration\\`.\n\nWe can do this doing some grouping and by implementing the filtering conditions we've learned so far. \n- Create a single Zebras table that aggregates the countries for birth by \\`Variable (Flow Type)\\`.\n- Aggregate the total number of \\`Stock of foreign-born population by country of birth\\` for \\`2016\\`.\n- Create **three** new data frames which will have **three** different total number of \\`Stock of foreign-born population by country of birth\\` **levels** for \\`2016\\`.\n - \\`High Immigration\\` > 100,000\n - \\`Medium Immigration\\` > 50,000 & < 100,000\n - \\`Low Immigration\\` < 50,000\n`","pinned":false,"hidden":false,"mode":"ojs"},{"id":95,"value":"$31","pinned":true,"hidden":false,"mode":"ojs"},{"id":794,"value":"z.head(5,df_sum2)","pinned":false,"hidden":false,"mode":"ojs"},{"id":126,"value":"sumStock16={\n // filter the data frame for both variable and year\n var migrationParse=z.parseNums([\"Value\"], migration)\n var migration16Filter=z.filter(r => r.VAR == \"B14\" && r.Year == \"2016\", migrationParse)\n var migration16Sum=z.gbSum(\"Value\", z.groupBy(d => d.CO2, migration16Filter)) \n var Sum16=[]\n for (var i=0; ix.group==countryBirth[i],migration16Sum)\n //push the sum of each country into the final array\n Sum16.push({\"Country Code\":countryBirth[i], \"StockSum2016\": parseInt(countrySrc[0][\"sum\"])})\n }\n return Sum16\n}","pinned":true,"hidden":false,"mode":"ojs"},{"id":817,"value":"z.head(5,sumStock16)","pinned":true,"hidden":false,"mode":"ojs"},{"id":128,"value":"{\n// first generate three arrays by filter\n //generate object keys so that they can be used for filter\n var newData1=z.filter(x=>x.StockSum2016> 100000, sumStock16)\n var newData2=z.filter(x=>x.StockSum2016> 50000 && x.StockSum2016< 100000, sumStock16)\n var newData3=z.filter(x=>x.StockSum2016< 50000, sumStock16)\n //put all three arrays into an object, which includes three new data frames (optional)\n var newDataCombined={\"High Immigration\":newData1, \"medium Immigration\": newData2, \"low Immigration\":newData3}\n return newDataCombined\n}","pinned":true,"hidden":false,"mode":"ojs"},{"id":151,"value":"md `## 6. Comparing data\n\nFor this question, we ask you to compare the inflow and outflow of population by nationality.\n\n- Create a data frame that has a column called \\`inflow-outflow\\`, which subtracts the aggregated number of \\`Inflows of foreign population by nationality\\`** - **\\`Outflows of foreign population by nationality\\` by \\`Country of birth/nationality\\`.\n`","pinned":false,"hidden":false,"mode":"ojs"},{"id":172,"value":"// Your code here\ncompareArray={\n for(var i=0;ix.Country==countryBirth[i],df_summed)\n compareArray.push({\"Country Code\":countryBirth[i], \"Inflow-Outflow\":parseInt(countrySrc[0][\"Inflows of foreign population by nationality\"]- countrySrc[0][\"Outflows of foreign population by nationality\"])})\n }\n return compareArray\n}","pinned":true,"hidden":false,"mode":"ojs"},{"id":174,"value":"// Your code here\nz.head(5,compareArray)","pinned":false,"hidden":false,"mode":"ojs"},{"id":141,"value":"md`## Appendix`","pinned":false,"hidden":false,"mode":"ojs"},{"id":138,"value":"md` While code performance and optimization won't count, all the code should be highly readable, and reusable. Where possible, create functions, build helper functions where needed, and make sure the code is self-explanatory.`\n\n","pinned":false,"hidden":false,"mode":"ojs"},{"id":148,"value":"// Your code here\n","pinned":false,"hidden":false,"mode":"ojs"}],"files":"$W32","stdlib":"1","titling":"auto"}