30:T5ef,df_summed = { const sumTable = [] const migrationB = {} const migrationValB = {} for (let i = 0; i < countryBirth.length; i++){ migrationB[countryBirth[i][0]] = z.filter(r => 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 particular 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:T447,// aggregates the countries for birth by Variable df_sum2 = { const migrationParse = z.parseNums(["Value"], migration) const migrationV = z.groupBy(x => x.Variable, migrationParse) const variables = z.unique(z.getCol("Variable", migration)) const sumObject = {} const aggregate = [] for (let i = 0; i < variables.length; i++){ sumObject[variables[i]] = z.gbSum("Value", z.groupBy(d => d.CO2, migrationV[variables[i]])) } //organize each country's information as an object for (let i = 0; i < countryBirth.length; i++){ const obj = {} obj["Country Code"] = countryBirth[i] for(let j = 0; j < variables.length; j++) { //get the object of interests identified by country codes (countryBirth is the key of countries) const countrySrc = z.filter(x => x.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":984,"value":"md`There is ${z.unique(z.getCol('Country of birth/nationality', migration)).length} different countries registered in our dataset.`","pinned":false,"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\nconst migrationFilter = z.filter(r => r.VAR == \"B11\" || r.VAR == \"B12\" || r.VAR == \"B14\", migration)\n//turn values into numbers\nconst 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 const groupContinent = {}\n const 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 (let 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 const migrationParse = z.parseNums([\"Value\"], migration)\n const migration16Filter = z.filter(r => r.VAR == \"B14\" && r.Year == \"2016\", migrationParse)\n const migration16Sum = z.gbSum(\"Value\", z.groupBy(d => d.CO2, migration16Filter)) \n const Sum16 = []\n for (let i = 0; i < countryBirth.length; i++){\n //get the object of interests identified by country codes (countryBirth is the key of countries)\n const countrySrc = z.filter(x => x.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":"newDataCombined = {\n// first generate three arrays by filter\n //generate object keys so that they can be used for filter\n const newData1 = z.filter(x => x.StockSum2016 > 100000, sumStock16)\n const newData2 = z.filter(x => x.StockSum2016 > 50000 && x.StockSum2016 < 100000, sumStock16)\n const newData3 = z.filter(x => x.StockSum2016 < 50000, sumStock16)\n //put all three arrays into an object, which includes three new data frames (optional)\n const newDataCombined = {\"High Immigration\": newData1, \"Medium Immigration\": newData2, \"Low Immigration\": newData3}\n return newDataCombined\n}","pinned":true,"hidden":false,"mode":"ojs"},{"id":929,"value":"md`### High Immigration`\n","pinned":false,"hidden":false,"mode":"ojs"},{"id":921,"value":"z.head(5, newDataCombined[\"High Immigration\"])","pinned":false,"hidden":false,"mode":"ojs"},{"id":941,"value":"md`### Medium Immigration`","pinned":false,"hidden":false,"mode":"ojs"},{"id":951,"value":"z.head(5, newDataCombined[\"Medium Immigration\"])","pinned":false,"hidden":false,"mode":"ojs"},{"id":948,"value":"md`### Low Immigration`","pinned":false,"hidden":false,"mode":"ojs"},{"id":953,"value":"z.head(5, newDataCombined[\"Low Immigration\"])","pinned":false,"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 const compareArray = []\n for(let i = 0; i < countryBirth.length; i++){\n const countrySrc = z.filter(x => x[\"Country Code\"] == 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"}