library(tidyverse) # core package for data manipulation, including dplyr, readr, and moreOpenScience in R
Now that you know what computational notebooks are and why we should care about them, let’s start using them! This section introduces you to using R for manipulating tabular data. Please read through it carefully and pay attention to how ideas about manipulating data are translated into code. For this part, you can read directly from the course website, although it is recommended you follow the section interactively by running the code on your own.
Once you have read through, jump on the Do-It-Yourself section, which will provide you with a challenge that you should complete on your own, and will allow you to put what you have already learnt into practice.
Data wrangling
Real world datasets tend to be messy. There is no way around it: datasets have “holes” (missing data), the amount of formats in which data can be stored is endless, and the best structure to share data is not always the optimum to analyze them, hence the need to wrangle (manipulate, transform and structure) them. As has been correctly pointed out in many outlets (e.g.), much of the time spent in what is called (Geo-)Data Science is related not only to sophisticated modeling and insight, but to more basic and less exotic tasks such as obtaining data, processing, turning them into a shape that makes analysis possible, and exploring it to get to know their basic properties.
In this session, you will use a few real world datasets and learn how to process them in R so they can be transformed and manipulated, if necessary, and analyzed. For this, we will introduce some of the fundamental tools of data analysis and scientific computing. We use a prepared dataset that saves us much of the more intricate processing that goes beyond the introductory level the session is aimed at.
In this notebook, we discuss several patterns to clean and structure data properly, including tidying, subsetting, and aggregating; and we finish with some basic visualization. An additional extension presents more advanced tricks to manipulate tabular data.
Before we get our hands data-dirty, let us import all the additional libraries we will need to run the code:
Loading packages
We will start by loading the core package we need for data wrangling.
Datasets
We will be exploring some demographic characteristics in Liverpool. To do that, we will use a dataset that contains population counts, split by ethnic origin. These counts are aggregated at the Lower Layer Super Output Area (LSOA from now on). LSOAs are an official Census geography defined by the Office of National Statistics. You can think of them, more or less, as neighbourhoods. Many data products (Census, deprivation indices, etc.) use LSOAs as one of their main geographies.
To do this, we will download a data folder from github called census2021_ethn. You should place this in a data folder you will use throughout the course.
Import census data from csv
census2021 <- read.csv("data/census2021_ethn/liv_pop.csv", row.names = "GeographyCode")Let us stop for a minute to learn how we have read the file. Here are the main aspects to keep in mind:
We are using the method
read.csvfrom base R, you could also useread_csvfromlibrary("readr").Here the csv is based on a data file but it could also be a web address or sometimes you find data in packages.
The argument row.names is not strictly necessary but allows us to choose one of the columns as the index of the table. More on indices below.
We are using
read.csvbecause the file we want to read is in the csv format. However, many more formats can be read into an R environment. A full list of formats supported may be found here.To ensure we can access the data we have read, we store it in an object that we call
census2021. We will see more on what we can do with it below but, for now, just keep in mind that allows us to save the result ofread.csv.
Important
You need to store the data file on your computer, and read it locally. To do that, you can follow these steps:
Download the
census2021_ethnfile by right-clicking on this link and saving the filePlace the file in a data folder you have created where you intend to read it.
Your folder should have the following structure a. a gds folder (where you will save your quarto .qmd documents) b. a data folder c. the
census2021_ethnfolder inside your data folder.
TipDownload a folder on github
First go to https://download-directory.github.io/
Then go to the folder you need to today. So for example copy: https://github.com/GDSL-UL/gds/tree/main/data/London
Paste it in the green box… give it a few minutes
Check your downloads file and unzip
Data, sliced and diced
Now we are ready to start playing with and interrogating the dataset! What we have at our fingertips is a table that summarizes, for each of the LSOAs in Liverpool, how many people live in each, by the region of the world where they were born. We call these tables DataFrame objects, and they have a lot of functionality built-in to explore and manipulate the data they contain.
Structure
Let’s start by exploring the structure of a DataFrame. We can print it by simply typing its name:
census2021 Europe Africa Middle.East.and.Asia The.Americas.and.the.Caribbean
E01006512 910 106 840 24
E01006513 2225 61 595 53
E01006514 1786 63 193 61
E01006515 974 29 185 18
E01006518 1531 69 73 19
E01006519 1238 7 24 14
E01006520 1607 31 36 26
E01006521 1540 35 62 18
E01006522 1632 19 58 13
E01006523 1436 25 31 11
E01006524 2235 36 125 24
E01006525 1173 3 28 3
E01006526 1359 6 31 5
E01006527 1415 5 20 10
E01006528 1378 35 38 9
E01006529 1248 15 31 9
E01006530 1411 3 11 4
E01006531 1427 18 10 1
E01006532 1466 20 63 4
E01006533 1361 2 41 3
E01006534 1441 13 75 12
E01006535 1597 13 22 6
E01006536 1799 12 27 9
E01006537 2180 23 46 6
E01006538 1303 4 8 3
E01006539 1359 11 54 9
E01006540 1394 47 63 5
E01006541 1577 7 6 3
E01006542 1367 10 12 5
E01006543 1242 3 2 0
E01006544 1637 5 15 1
E01006545 1485 4 16 7
E01006546 1282 13 21 3
E01006547 1407 22 59 1
E01006548 1580 51 36 7
E01006549 1387 122 120 29
E01006550 1512 16 53 13
E01006551 1456 42 63 19
E01006552 1406 198 408 16
E01006553 1763 23 69 14
E01006554 1561 30 78 16
E01006556 1417 213 338 41
E01006557 1863 44 118 22
E01006558 1315 6 31 5
E01006560 1776 38 37 8
E01006562 1450 25 35 3
E01006563 1656 32 45 10
E01006564 1660 13 47 1
E01006565 1497 5 44 2
E01006566 1449 26 29 2
E01006567 1600 13 38 3
E01006568 1579 23 8 2
E01006569 1418 7 2 1
E01006570 1445 3 27 4
E01006571 1446 11 11 1
E01006572 1184 11 11 1
E01006573 1446 9 41 4
E01006574 1355 11 19 3
E01006575 1253 5 27 5
E01006576 1666 1 23 3
E01006577 1470 11 19 3
E01006578 1341 4 30 3
E01006579 1597 10 33 1
E01006580 1281 6 42 5
E01006581 1512 6 27 8
E01006582 1587 4 38 11
E01006583 1712 5 30 8
E01006584 1374 7 10 0
E01006585 1287 25 41 0
E01006586 1803 19 70 4
E01006587 1542 26 46 8
E01006588 1500 15 41 10
E01006589 1459 6 47 6
E01006590 1582 8 38 10
E01006591 1540 21 49 22
E01006592 1518 15 23 7
E01006593 1476 17 36 12
E01006594 1307 13 77 8
E01006595 1686 14 35 7
E01006596 1379 8 57 5
E01006597 1612 15 35 4
E01006598 1338 8 12 3
E01006599 1093 18 10 0
E01006600 1395 7 4 1
E01006603 1396 5 9 0
E01006604 1356 6 4 1
E01006605 1509 8 11 3
E01006606 1497 10 12 2
E01006607 1527 1 2 1
E01006608 1207 2 4 1
E01006609 1257 5 20 0
E01006610 1372 7 12 4
E01006611 1274 10 6 1
E01006612 1480 7 5 3
E01006613 1346 2 9 2
E01006614 1300 2 18 2
E01006615 1283 3 11 3
E01006616 1403 7 15 3
E01006617 1392 10 17 2
E01006618 1634 2 2 1
E01006619 1586 4 8 1
E01006620 1400 4 12 1
E01006621 1409 19 37 1
E01006622 1508 1 9 1
E01006623 1396 8 56 1
E01006624 1489 25 39 3
E01006625 1398 3 11 4
E01006626 1418 5 20 4
E01006627 1344 12 18 3
E01006628 1750 39 59 21
E01006629 1200 8 38 8
E01006630 1239 44 47 8
E01006632 1711 49 78 3
E01006633 1415 0 11 5
E01006637 1480 62 53 0
E01006638 1154 24 41 0
E01006639 1461 11 34 1
E01006640 1807 15 48 8
E01006641 1527 20 191 0
E01006642 1253 10 26 0
E01006643 1754 21 45 2
E01006644 1980 24 30 3
E01006645 1396 20 6 1
E01006646 1760 34 89 10
E01006647 1392 17 12 1
E01006648 2034 33 89 16
E01006651 1605 9 21 3
E01006652 1459 1 45 0
E01006653 1479 1 35 5
E01006654 1468 2 40 1
E01006655 1624 21 107 6
E01006656 1478 8 6 0
E01006657 1188 2 18 3
E01006658 1272 3 24 1
E01006659 1687 24 44 9
E01006660 1319 6 106 2
E01006661 1605 33 21 4
E01006662 1710 21 43 1
E01006663 1125 7 20 1
E01006664 1726 23 38 5
E01006665 1523 8 22 2
E01006666 1426 14 58 4
E01006667 1418 8 22 3
E01006668 1485 6 37 1
E01006669 1376 31 60 0
E01006670 1477 4 43 1
E01006671 1280 15 56 2
E01006672 1899 14 36 3
E01006673 1350 273 269 42
E01006674 1197 342 325 21
E01006675 1491 115 171 44
E01006676 1303 203 215 28
E01006677 813 100 119 7
E01006678 1906 92 108 18
E01006679 1353 484 354 31
E01006680 1528 8 30 1
E01006681 1513 7 60 4
E01006682 1282 3 17 6
E01006683 1481 2 24 3
E01006684 2074 10 42 8
E01006685 1451 14 13 5
E01006686 1389 14 20 10
E01006687 1824 53 61 11
E01006688 1486 10 15 10
E01006689 1613 14 13 7
E01006690 1309 85 82 14
E01006691 1147 170 172 10
E01006692 1979 115 73 11
E01006693 1401 27 76 6
E01006694 1452 306 97 7
E01006695 1525 166 114 16
E01006696 1438 135 165 4
E01006697 1506 145 175 18
E01006698 1694 6 12 4
E01006699 1565 23 4 2
E01006700 1392 9 31 5
E01006701 1347 5 23 5
E01006702 1561 22 35 0
E01006703 1509 35 15 2
E01006705 1368 22 16 1
E01006706 1323 9 8 6
E01006707 1298 11 7 7
E01006708 1791 3 6 5
E01006709 1423 1 8 2
E01006710 1474 9 5 7
E01006711 1444 30 55 8
E01006712 1292 0 45 1
E01006713 1329 10 32 6
E01006716 1496 12 74 3
E01006717 1381 19 27 8
E01006718 1533 14 34 3
E01006719 1518 2 14 1
E01006720 1670 153 263 14
E01006721 1480 39 118 17
E01006722 1505 83 185 16
E01006723 1655 49 141 27
E01006724 1641 37 90 18
E01006725 1446 25 58 5
E01006726 1365 63 121 13
E01006727 1360 28 40 9
E01006728 1497 34 78 14
E01006729 1373 16 7 2
E01006730 1303 10 8 1
E01006731 1160 9 23 1
E01006732 1615 10 13 5
E01006734 1371 19 17 2
E01006735 1262 9 33 4
E01006736 1423 20 13 3
E01006737 1409 19 7 0
E01006738 1484 9 6 2
E01006739 1726 19 20 4
E01006740 1226 5 41 8
E01006741 1796 4 7 7
E01006742 1381 4 6 2
E01006743 2102 15 45 11
E01006744 1461 8 9 3
E01006745 1838 4 30 7
E01006746 1327 44 136 4
E01006747 2551 163 812 24
E01006748 1558 60 272 40
E01006751 1843 139 568 21
E01006752 789 62 152 8
E01006753 1532 16 8 1
E01006754 1613 10 1 3
E01006755 1540 23 12 12
E01006756 1768 25 21 5
E01006757 1475 7 6 3
E01006758 1487 23 16 1
E01006759 1034 28 26 6
E01006760 1462 71 63 10
E01006761 1553 7 81 3
E01006762 1649 7 31 1
E01006763 1441 16 11 1
E01006764 1428 36 51 5
E01006765 1439 8 17 2
E01006766 1175 43 41 5
E01006767 1673 63 41 6
E01006768 1273 14 66 6
E01006769 1233 9 21 6
E01006770 977 2 17 6
E01006771 1407 9 8 0
E01006772 1059 6 22 2
E01006773 1354 5 15 4
E01006774 1340 13 83 7
E01006775 1295 3 14 6
E01006776 1637 13 20 4
E01006778 2107 51 37 9
E01006779 1949 6 11 2
E01006780 1442 7 10 4
E01006781 1454 0 10 1
E01006782 1535 5 20 0
E01006783 1516 6 22 2
E01006784 1325 4 18 0
E01006785 1718 34 12 3
E01006786 1795 5 15 5
E01006787 2187 53 75 13
E01006788 1788 6 26 3
E01006790 1556 15 12 3
E01006791 1620 24 18 12
E01006792 1319 16 29 3
E01006793 1428 14 41 5
E01006794 1521 15 36 16
E01006795 1370 1 24 10
E01006796 1461 6 103 19
E01006797 1469 7 11 7
E01006798 1367 7 27 9
E01006799 1779 11 19 8
E01006800 1323 14 55 7
E01006801 1321 4 26 3
E01032505 1254 4 53 4
E01032506 1349 11 35 4
E01032507 1496 11 14 14
E01032508 1602 22 93 4
E01032509 1385 18 27 3
E01032510 1752 18 33 5
E01032511 1355 11 9 1
E01033747 970 16 45 9
E01033748 1157 40 63 15
E01033749 1023 33 73 24
E01033750 1235 53 129 26
E01033751 1674 64 99 35
E01033752 1024 19 114 33
E01033753 869 24 129 22
E01033754 1262 37 112 32
E01033755 952 22 85 14
E01033756 886 31 221 42
E01033757 731 39 223 29
E01033758 1107 50 557 14
E01033759 1097 27 20 1
E01033760 1734 37 312 28
E01033761 1138 52 138 33
E01033762 1570 53 155 10
E01033763 1302 68 142 11
E01033764 2106 32 49 15
E01033765 1277 21 33 17
E01033766 1028 12 20 8
E01033767 1003 29 29 5
E01033768 1016 69 111 21
Antarctica.and.Oceania
E01006512 0
E01006513 7
E01006514 5
E01006515 2
E01006518 4
E01006519 3
E01006520 6
E01006521 5
E01006522 9
E01006523 5
E01006524 11
E01006525 2
E01006526 3
E01006527 4
E01006528 1
E01006529 1
E01006530 2
E01006531 2
E01006532 4
E01006533 1
E01006534 1
E01006535 1
E01006536 1
E01006537 2
E01006538 0
E01006539 4
E01006540 0
E01006541 0
E01006542 3
E01006543 0
E01006544 0
E01006545 0
E01006546 0
E01006547 1
E01006548 1
E01006549 4
E01006550 1
E01006551 1
E01006552 1
E01006553 11
E01006554 7
E01006556 2
E01006557 7
E01006558 0
E01006560 1
E01006562 1
E01006563 0
E01006564 0
E01006565 1
E01006566 0
E01006567 1
E01006568 5
E01006569 1
E01006570 0
E01006571 1
E01006572 0
E01006573 1
E01006574 0
E01006575 4
E01006576 0
E01006577 0
E01006578 2
E01006579 2
E01006580 1
E01006581 6
E01006582 2
E01006583 3
E01006584 2
E01006585 1
E01006586 3
E01006587 2
E01006588 3
E01006589 0
E01006590 4
E01006591 1
E01006592 2
E01006593 0
E01006594 5
E01006595 3
E01006596 2
E01006597 1
E01006598 0
E01006599 0
E01006600 0
E01006603 0
E01006604 0
E01006605 0
E01006606 0
E01006607 0
E01006608 1
E01006609 1
E01006610 2
E01006611 0
E01006612 0
E01006613 1
E01006614 0
E01006615 2
E01006616 0
E01006617 0
E01006618 0
E01006619 0
E01006620 0
E01006621 2
E01006622 0
E01006623 1
E01006624 0
E01006625 0
E01006626 1
E01006627 1
E01006628 5
E01006629 1
E01006630 0
E01006632 1
E01006633 1
E01006637 1
E01006638 0
E01006639 2
E01006640 2
E01006641 2
E01006642 1
E01006643 0
E01006644 3
E01006645 1
E01006646 6
E01006647 0
E01006648 3
E01006651 4
E01006652 0
E01006653 0
E01006654 2
E01006655 0
E01006656 1
E01006657 0
E01006658 0
E01006659 1
E01006660 0
E01006661 0
E01006662 0
E01006663 0
E01006664 3
E01006665 0
E01006666 2
E01006667 2
E01006668 5
E01006669 3
E01006670 1
E01006671 2
E01006672 3
E01006673 3
E01006674 0
E01006675 3
E01006676 4
E01006677 1
E01006678 3
E01006679 10
E01006680 2
E01006681 1
E01006682 2
E01006683 0
E01006684 4
E01006685 1
E01006686 0
E01006687 3
E01006688 3
E01006689 4
E01006690 2
E01006691 4
E01006692 2
E01006693 1
E01006694 2
E01006695 0
E01006696 2
E01006697 1
E01006698 0
E01006699 0
E01006700 1
E01006701 0
E01006702 0
E01006703 0
E01006705 2
E01006706 0
E01006707 0
E01006708 0
E01006709 4
E01006710 1
E01006711 1
E01006712 1
E01006713 0
E01006716 4
E01006717 1
E01006718 3
E01006719 0
E01006720 8
E01006721 1
E01006722 4
E01006723 3
E01006724 4
E01006725 3
E01006726 3
E01006727 4
E01006728 0
E01006729 2
E01006730 4
E01006731 3
E01006732 0
E01006734 3
E01006735 1
E01006736 1
E01006737 0
E01006738 0
E01006739 0
E01006740 0
E01006741 1
E01006742 1
E01006743 2
E01006744 1
E01006745 0
E01006746 2
E01006747 2
E01006748 6
E01006751 1
E01006752 1
E01006753 0
E01006754 0
E01006755 0
E01006756 1
E01006757 0
E01006758 0
E01006759 1
E01006760 5
E01006761 0
E01006762 4
E01006763 2
E01006764 2
E01006765 2
E01006766 2
E01006767 4
E01006768 0
E01006769 1
E01006770 0
E01006771 1
E01006772 2
E01006773 1
E01006774 2
E01006775 1
E01006776 3
E01006778 1
E01006779 0
E01006780 2
E01006781 5
E01006782 0
E01006783 2
E01006784 0
E01006785 2
E01006786 0
E01006787 2
E01006788 2
E01006790 1
E01006791 0
E01006792 1
E01006793 6
E01006794 5
E01006795 2
E01006796 2
E01006797 5
E01006798 0
E01006799 4
E01006800 2
E01006801 1
E01032505 2
E01032506 4
E01032507 4
E01032508 2
E01032509 1
E01032510 1
E01032511 2
E01033747 3
E01033748 2
E01033749 0
E01033750 5
E01033751 1
E01033752 6
E01033753 7
E01033754 9
E01033755 9
E01033756 5
E01033757 3
E01033758 2
E01033759 2
E01033760 4
E01033761 11
E01033762 3
E01033763 4
E01033764 0
E01033765 3
E01033766 7
E01033767 1
E01033768 6Since they represent a table of data, DataFrame objects have two dimensions: rows and columns. Each of these is automatically assigned a name in what we will call its index. When printing, the index of each dimension is rendered in bold, as opposed to the standard rendering for the content. In the example above, we can see how the column index is automatically picked up from the .csv file’s column names. For rows, we have specified when reading the file we wanted the column GeographyCode, so that is used. If we hadn’t specified any, base R will automatically generate row names as a sequence of integers starting at 1 and going all the way to the number of rows (note this is one difference with Python, where the default sequence starts at 0). This is the standard structure of a DataFrame object, so we will come to it over and over. Importantly, even when we move to spatial data, our datasets will have a similar structure.
One further feature of these tables is that they can hold columns with different types of data. In our example, this is not used as we have counts (or int, for integer, types) for each column. But it is useful to keep in mind we can combine this with columns that hold other type of data such as categories, text (str, for string), dates or, as we will see later in the course, geographic features.
Inspecting
We can check the top (bottom) X lines of the table by passing X to the method head (tail). For example, for the top/bottom five lines:
head(census2021) # read first 5 rows Europe Africa Middle.East.and.Asia The.Americas.and.the.Caribbean
E01006512 910 106 840 24
E01006513 2225 61 595 53
E01006514 1786 63 193 61
E01006515 974 29 185 18
E01006518 1531 69 73 19
E01006519 1238 7 24 14
Antarctica.and.Oceania
E01006512 0
E01006513 7
E01006514 5
E01006515 2
E01006518 4
E01006519 3tail(census2021) # read last 5 rows Europe Africa Middle.East.and.Asia The.Americas.and.the.Caribbean
E01033763 1302 68 142 11
E01033764 2106 32 49 15
E01033765 1277 21 33 17
E01033766 1028 12 20 8
E01033767 1003 29 29 5
E01033768 1016 69 111 21
Antarctica.and.Oceania
E01033763 4
E01033764 0
E01033765 3
E01033766 7
E01033767 1
E01033768 6
Tip
Unlike Python/Jupyter, R prints every top-level visible expression in a chunk, so head() and tail() can safely sit in the same code cell and both will display.
Summarise
We can get an overview of the values of the table:
summary(census2021) Europe Africa Middle.East.and.Asia
Min. : 731 Min. : 0.00 Min. : 1.00
1st Qu.:1331 1st Qu.: 7.00 1st Qu.: 16.00
Median :1446 Median : 14.00 Median : 33.50
Mean :1462 Mean : 29.82 Mean : 62.91
3rd Qu.:1580 3rd Qu.: 30.00 3rd Qu.: 62.75
Max. :2551 Max. :484.00 Max. :840.00
The.Americas.and.the.Caribbean Antarctica.and.Oceania
Min. : 0.000 Min. : 0.00
1st Qu.: 2.000 1st Qu.: 0.00
Median : 5.000 Median : 1.00
Mean : 8.087 Mean : 1.95
3rd Qu.:10.000 3rd Qu.: 3.00
Max. :61.000 Max. :11.00 Note how the output is also a DataFrame object, so you can do with it the same things you would with the original table (e.g. writing it to a file).
In this case, the summary might be better presented if the table is “transposed”:
t(summary(census2021))
Europe Min. : 731 1st Qu.:1331
Africa Min. : 0.00 1st Qu.: 7.00
Middle.East.and.Asia Min. : 1.00 1st Qu.: 16.00
The.Americas.and.the.Caribbean Min. : 0.000 1st Qu.: 2.000
Antarctica.and.Oceania Min. : 0.00 1st Qu.: 0.00
Europe Median :1446 Mean :1462
Africa Median : 14.00 Mean : 29.82
Middle.East.and.Asia Median : 33.50 Mean : 62.91
The.Americas.and.the.Caribbean Median : 5.000 Mean : 8.087
Antarctica.and.Oceania Median : 1.00 Mean : 1.95
Europe 3rd Qu.:1580 Max. :2551
Africa 3rd Qu.: 30.00 Max. :484.00
Middle.East.and.Asia 3rd Qu.: 62.75 Max. :840.00
The.Americas.and.the.Caribbean 3rd Qu.:10.000 Max. :61.000
Antarctica.and.Oceania 3rd Qu.: 3.00 Max. :11.00 Columns
Create new columns
We can generate new variables by applying operations on existing ones. For example, we can calculate the total population by area. Here is a couple of ways to do it:
Longer, hardcoded:
total <- census2021$Africa + census2021$Middle.East.and.Asia + census2021$Europe + census2021$The.Americas.and.the.Caribbean + census2021$Antarctica.and.Oceania
# Print the top of the variable
head(total)[1] 1880 2941 2108 1208 1696 1286One shot, using the package dplyr (part of the tidyverse!). More info can be found here:
census2021 <- census2021 %>%
mutate(Total_Population = rowSums(select(., Africa, Middle.East.and.Asia, Europe, The.Americas.and.the.Caribbean, Antarctica.and.Oceania)))
head(census2021) Europe Africa Middle.East.and.Asia The.Americas.and.the.Caribbean
E01006512 910 106 840 24
E01006513 2225 61 595 53
E01006514 1786 63 193 61
E01006515 974 29 185 18
E01006518 1531 69 73 19
E01006519 1238 7 24 14
Antarctica.and.Oceania Total_Population
E01006512 0 1880
E01006513 7 2941
E01006514 5 2108
E01006515 2 1208
E01006518 4 1696
E01006519 3 1286dplyr is an immensely useful package in R because it streamlines and simplifies the process of data manipulation and transformation. With its intuitive and consistent syntax, dplyr provides a set of powerful and efficient functions that make tasks like filtering, summarizing, grouping, and joining datasets much more straightforward. Whether you’re working with small or large datasets, dplyr’s optimized code execution ensures fast and efficient operations. Its ability to chain functions together using the pipe operator (%>%) allows for a clean and readable code structure, enhancing code reproducibility and collaboration. Overall, dplyr is an indispensable tool for data analysts and scientists working in R, enabling them to focus on their data insights rather than wrestling with complex data manipulation code.
A different spin on this is assigning new values: we can generate new variables with scalars, and modify those:
# New variable with all ones
census2021$ones <- 1
head(census2021) Europe Africa Middle.East.and.Asia The.Americas.and.the.Caribbean
E01006512 910 106 840 24
E01006513 2225 61 595 53
E01006514 1786 63 193 61
E01006515 974 29 185 18
E01006518 1531 69 73 19
E01006519 1238 7 24 14
Antarctica.and.Oceania Total_Population ones
E01006512 0 1880 1
E01006513 7 2941 1
E01006514 5 2108 1
E01006515 2 1208 1
E01006518 4 1696 1
E01006519 3 1286 1Delete columns
Permanently deleting variables is also within reach of one command. Using dplyr:
census2021 <- census2021 %>%
select(-ones)
head(census2021) Europe Africa Middle.East.and.Asia The.Americas.and.the.Caribbean
E01006512 910 106 840 24
E01006513 2225 61 595 53
E01006514 1786 63 193 61
E01006515 974 29 185 18
E01006518 1531 69 73 19
E01006519 1238 7 24 14
Antarctica.and.Oceania Total_Population
E01006512 0 1880
E01006513 7 2941
E01006514 5 2108
E01006515 2 1208
E01006518 4 1696
E01006519 3 1286Queries
Index-based queries
Here we explore how we can subset parts of a DataFrame if we know exactly which bits we want. For example, if we want to extract the total and European population of the first four areas in the table:
We can select with c(). If this structure is new to you have a look here.
eu_tot_first4 <- census2021 %>%
filter(rownames(census2021) %in% c('E01006512', 'E01006513', 'E01006514', 'E01006515')) %>%
select(Total_Population, Europe)
eu_tot_first4 Total_Population Europe
E01006512 1880 910
E01006513 2941 2225
E01006514 2108 1786
E01006515 1208 974Condition-based queries
However, sometimes, we do not know exactly which observations we want, but we do know what conditions they need to satisfy (e.g. areas with more than 2,000 inhabitants). For these cases, DataFrames support selection based on conditions. Let us see a few examples. Suppose we want to select…
Areas with more than 900 people in Total:
pop900 <- census2021 %>%
filter(Total_Population > 900)
pop900 Europe Africa Middle.East.and.Asia The.Americas.and.the.Caribbean
E01006512 910 106 840 24
E01006513 2225 61 595 53
E01006514 1786 63 193 61
E01006515 974 29 185 18
E01006518 1531 69 73 19
E01006519 1238 7 24 14
E01006520 1607 31 36 26
E01006521 1540 35 62 18
E01006522 1632 19 58 13
E01006523 1436 25 31 11
E01006524 2235 36 125 24
E01006525 1173 3 28 3
E01006526 1359 6 31 5
E01006527 1415 5 20 10
E01006528 1378 35 38 9
E01006529 1248 15 31 9
E01006530 1411 3 11 4
E01006531 1427 18 10 1
E01006532 1466 20 63 4
E01006533 1361 2 41 3
E01006534 1441 13 75 12
E01006535 1597 13 22 6
E01006536 1799 12 27 9
E01006537 2180 23 46 6
E01006538 1303 4 8 3
E01006539 1359 11 54 9
E01006540 1394 47 63 5
E01006541 1577 7 6 3
E01006542 1367 10 12 5
E01006543 1242 3 2 0
E01006544 1637 5 15 1
E01006545 1485 4 16 7
E01006546 1282 13 21 3
E01006547 1407 22 59 1
E01006548 1580 51 36 7
E01006549 1387 122 120 29
E01006550 1512 16 53 13
E01006551 1456 42 63 19
E01006552 1406 198 408 16
E01006553 1763 23 69 14
E01006554 1561 30 78 16
E01006556 1417 213 338 41
E01006557 1863 44 118 22
E01006558 1315 6 31 5
E01006560 1776 38 37 8
E01006562 1450 25 35 3
E01006563 1656 32 45 10
E01006564 1660 13 47 1
E01006565 1497 5 44 2
E01006566 1449 26 29 2
E01006567 1600 13 38 3
E01006568 1579 23 8 2
E01006569 1418 7 2 1
E01006570 1445 3 27 4
E01006571 1446 11 11 1
E01006572 1184 11 11 1
E01006573 1446 9 41 4
E01006574 1355 11 19 3
E01006575 1253 5 27 5
E01006576 1666 1 23 3
E01006577 1470 11 19 3
E01006578 1341 4 30 3
E01006579 1597 10 33 1
E01006580 1281 6 42 5
E01006581 1512 6 27 8
E01006582 1587 4 38 11
E01006583 1712 5 30 8
E01006584 1374 7 10 0
E01006585 1287 25 41 0
E01006586 1803 19 70 4
E01006587 1542 26 46 8
E01006588 1500 15 41 10
E01006589 1459 6 47 6
E01006590 1582 8 38 10
E01006591 1540 21 49 22
E01006592 1518 15 23 7
E01006593 1476 17 36 12
E01006594 1307 13 77 8
E01006595 1686 14 35 7
E01006596 1379 8 57 5
E01006597 1612 15 35 4
E01006598 1338 8 12 3
E01006599 1093 18 10 0
E01006600 1395 7 4 1
E01006603 1396 5 9 0
E01006604 1356 6 4 1
E01006605 1509 8 11 3
E01006606 1497 10 12 2
E01006607 1527 1 2 1
E01006608 1207 2 4 1
E01006609 1257 5 20 0
E01006610 1372 7 12 4
E01006611 1274 10 6 1
E01006612 1480 7 5 3
E01006613 1346 2 9 2
E01006614 1300 2 18 2
E01006615 1283 3 11 3
E01006616 1403 7 15 3
E01006617 1392 10 17 2
E01006618 1634 2 2 1
E01006619 1586 4 8 1
E01006620 1400 4 12 1
E01006621 1409 19 37 1
E01006622 1508 1 9 1
E01006623 1396 8 56 1
E01006624 1489 25 39 3
E01006625 1398 3 11 4
E01006626 1418 5 20 4
E01006627 1344 12 18 3
E01006628 1750 39 59 21
E01006629 1200 8 38 8
E01006630 1239 44 47 8
E01006632 1711 49 78 3
E01006633 1415 0 11 5
E01006637 1480 62 53 0
E01006638 1154 24 41 0
E01006639 1461 11 34 1
E01006640 1807 15 48 8
E01006641 1527 20 191 0
E01006642 1253 10 26 0
E01006643 1754 21 45 2
E01006644 1980 24 30 3
E01006645 1396 20 6 1
E01006646 1760 34 89 10
E01006647 1392 17 12 1
E01006648 2034 33 89 16
E01006651 1605 9 21 3
E01006652 1459 1 45 0
E01006653 1479 1 35 5
E01006654 1468 2 40 1
E01006655 1624 21 107 6
E01006656 1478 8 6 0
E01006657 1188 2 18 3
E01006658 1272 3 24 1
E01006659 1687 24 44 9
E01006660 1319 6 106 2
E01006661 1605 33 21 4
E01006662 1710 21 43 1
E01006663 1125 7 20 1
E01006664 1726 23 38 5
E01006665 1523 8 22 2
E01006666 1426 14 58 4
E01006667 1418 8 22 3
E01006668 1485 6 37 1
E01006669 1376 31 60 0
E01006670 1477 4 43 1
E01006671 1280 15 56 2
E01006672 1899 14 36 3
E01006673 1350 273 269 42
E01006674 1197 342 325 21
E01006675 1491 115 171 44
E01006676 1303 203 215 28
E01006677 813 100 119 7
E01006678 1906 92 108 18
E01006679 1353 484 354 31
E01006680 1528 8 30 1
E01006681 1513 7 60 4
E01006682 1282 3 17 6
E01006683 1481 2 24 3
E01006684 2074 10 42 8
E01006685 1451 14 13 5
E01006686 1389 14 20 10
E01006687 1824 53 61 11
E01006688 1486 10 15 10
E01006689 1613 14 13 7
E01006690 1309 85 82 14
E01006691 1147 170 172 10
E01006692 1979 115 73 11
E01006693 1401 27 76 6
E01006694 1452 306 97 7
E01006695 1525 166 114 16
E01006696 1438 135 165 4
E01006697 1506 145 175 18
E01006698 1694 6 12 4
E01006699 1565 23 4 2
E01006700 1392 9 31 5
E01006701 1347 5 23 5
E01006702 1561 22 35 0
E01006703 1509 35 15 2
E01006705 1368 22 16 1
E01006706 1323 9 8 6
E01006707 1298 11 7 7
E01006708 1791 3 6 5
E01006709 1423 1 8 2
E01006710 1474 9 5 7
E01006711 1444 30 55 8
E01006712 1292 0 45 1
E01006713 1329 10 32 6
E01006716 1496 12 74 3
E01006717 1381 19 27 8
E01006718 1533 14 34 3
E01006719 1518 2 14 1
E01006720 1670 153 263 14
E01006721 1480 39 118 17
E01006722 1505 83 185 16
E01006723 1655 49 141 27
E01006724 1641 37 90 18
E01006725 1446 25 58 5
E01006726 1365 63 121 13
E01006727 1360 28 40 9
E01006728 1497 34 78 14
E01006729 1373 16 7 2
E01006730 1303 10 8 1
E01006731 1160 9 23 1
E01006732 1615 10 13 5
E01006734 1371 19 17 2
E01006735 1262 9 33 4
E01006736 1423 20 13 3
E01006737 1409 19 7 0
E01006738 1484 9 6 2
E01006739 1726 19 20 4
E01006740 1226 5 41 8
E01006741 1796 4 7 7
E01006742 1381 4 6 2
E01006743 2102 15 45 11
E01006744 1461 8 9 3
E01006745 1838 4 30 7
E01006746 1327 44 136 4
E01006747 2551 163 812 24
E01006748 1558 60 272 40
E01006751 1843 139 568 21
E01006752 789 62 152 8
E01006753 1532 16 8 1
E01006754 1613 10 1 3
E01006755 1540 23 12 12
E01006756 1768 25 21 5
E01006757 1475 7 6 3
E01006758 1487 23 16 1
E01006759 1034 28 26 6
E01006760 1462 71 63 10
E01006761 1553 7 81 3
E01006762 1649 7 31 1
E01006763 1441 16 11 1
E01006764 1428 36 51 5
E01006765 1439 8 17 2
E01006766 1175 43 41 5
E01006767 1673 63 41 6
E01006768 1273 14 66 6
E01006769 1233 9 21 6
E01006770 977 2 17 6
E01006771 1407 9 8 0
E01006772 1059 6 22 2
E01006773 1354 5 15 4
E01006774 1340 13 83 7
E01006775 1295 3 14 6
E01006776 1637 13 20 4
E01006778 2107 51 37 9
E01006779 1949 6 11 2
E01006780 1442 7 10 4
E01006781 1454 0 10 1
E01006782 1535 5 20 0
E01006783 1516 6 22 2
E01006784 1325 4 18 0
E01006785 1718 34 12 3
E01006786 1795 5 15 5
E01006787 2187 53 75 13
E01006788 1788 6 26 3
E01006790 1556 15 12 3
E01006791 1620 24 18 12
E01006792 1319 16 29 3
E01006793 1428 14 41 5
E01006794 1521 15 36 16
E01006795 1370 1 24 10
E01006796 1461 6 103 19
E01006797 1469 7 11 7
E01006798 1367 7 27 9
E01006799 1779 11 19 8
E01006800 1323 14 55 7
E01006801 1321 4 26 3
E01032505 1254 4 53 4
E01032506 1349 11 35 4
E01032507 1496 11 14 14
E01032508 1602 22 93 4
E01032509 1385 18 27 3
E01032510 1752 18 33 5
E01032511 1355 11 9 1
E01033747 970 16 45 9
E01033748 1157 40 63 15
E01033749 1023 33 73 24
E01033750 1235 53 129 26
E01033751 1674 64 99 35
E01033752 1024 19 114 33
E01033753 869 24 129 22
E01033754 1262 37 112 32
E01033755 952 22 85 14
E01033756 886 31 221 42
E01033757 731 39 223 29
E01033758 1107 50 557 14
E01033759 1097 27 20 1
E01033760 1734 37 312 28
E01033761 1138 52 138 33
E01033762 1570 53 155 10
E01033763 1302 68 142 11
E01033764 2106 32 49 15
E01033765 1277 21 33 17
E01033766 1028 12 20 8
E01033767 1003 29 29 5
E01033768 1016 69 111 21
Antarctica.and.Oceania Total_Population
E01006512 0 1880
E01006513 7 2941
E01006514 5 2108
E01006515 2 1208
E01006518 4 1696
E01006519 3 1286
E01006520 6 1706
E01006521 5 1660
E01006522 9 1731
E01006523 5 1508
E01006524 11 2431
E01006525 2 1209
E01006526 3 1404
E01006527 4 1454
E01006528 1 1461
E01006529 1 1304
E01006530 2 1431
E01006531 2 1458
E01006532 4 1557
E01006533 1 1408
E01006534 1 1542
E01006535 1 1639
E01006536 1 1848
E01006537 2 2257
E01006538 0 1318
E01006539 4 1437
E01006540 0 1509
E01006541 0 1593
E01006542 3 1397
E01006543 0 1247
E01006544 0 1658
E01006545 0 1512
E01006546 0 1319
E01006547 1 1490
E01006548 1 1675
E01006549 4 1662
E01006550 1 1595
E01006551 1 1581
E01006552 1 2029
E01006553 11 1880
E01006554 7 1692
E01006556 2 2011
E01006557 7 2054
E01006558 0 1357
E01006560 1 1860
E01006562 1 1514
E01006563 0 1743
E01006564 0 1721
E01006565 1 1549
E01006566 0 1506
E01006567 1 1655
E01006568 5 1617
E01006569 1 1429
E01006570 0 1479
E01006571 1 1470
E01006572 0 1207
E01006573 1 1501
E01006574 0 1388
E01006575 4 1294
E01006576 0 1693
E01006577 0 1503
E01006578 2 1380
E01006579 2 1643
E01006580 1 1335
E01006581 6 1559
E01006582 2 1642
E01006583 3 1758
E01006584 2 1393
E01006585 1 1354
E01006586 3 1899
E01006587 2 1624
E01006588 3 1569
E01006589 0 1518
E01006590 4 1642
E01006591 1 1633
E01006592 2 1565
E01006593 0 1541
E01006594 5 1410
E01006595 3 1745
E01006596 2 1451
E01006597 1 1667
E01006598 0 1361
E01006599 0 1121
E01006600 0 1407
E01006603 0 1410
E01006604 0 1367
E01006605 0 1531
E01006606 0 1521
E01006607 0 1531
E01006608 1 1215
E01006609 1 1283
E01006610 2 1397
E01006611 0 1291
E01006612 0 1495
E01006613 1 1360
E01006614 0 1322
E01006615 2 1302
E01006616 0 1428
E01006617 0 1421
E01006618 0 1639
E01006619 0 1599
E01006620 0 1417
E01006621 2 1468
E01006622 0 1519
E01006623 1 1462
E01006624 0 1556
E01006625 0 1416
E01006626 1 1448
E01006627 1 1378
E01006628 5 1874
E01006629 1 1255
E01006630 0 1338
E01006632 1 1842
E01006633 1 1432
E01006637 1 1596
E01006638 0 1219
E01006639 2 1509
E01006640 2 1880
E01006641 2 1740
E01006642 1 1290
E01006643 0 1822
E01006644 3 2040
E01006645 1 1424
E01006646 6 1899
E01006647 0 1422
E01006648 3 2175
E01006651 4 1642
E01006652 0 1505
E01006653 0 1520
E01006654 2 1513
E01006655 0 1758
E01006656 1 1493
E01006657 0 1211
E01006658 0 1300
E01006659 1 1765
E01006660 0 1433
E01006661 0 1663
E01006662 0 1775
E01006663 0 1153
E01006664 3 1795
E01006665 0 1555
E01006666 2 1504
E01006667 2 1453
E01006668 5 1534
E01006669 3 1470
E01006670 1 1526
E01006671 2 1355
E01006672 3 1955
E01006673 3 1937
E01006674 0 1885
E01006675 3 1824
E01006676 4 1753
E01006677 1 1040
E01006678 3 2127
E01006679 10 2232
E01006680 2 1569
E01006681 1 1585
E01006682 2 1310
E01006683 0 1510
E01006684 4 2138
E01006685 1 1484
E01006686 0 1433
E01006687 3 1952
E01006688 3 1524
E01006689 4 1651
E01006690 2 1492
E01006691 4 1503
E01006692 2 2180
E01006693 1 1511
E01006694 2 1864
E01006695 0 1821
E01006696 2 1744
E01006697 1 1845
E01006698 0 1716
E01006699 0 1594
E01006700 1 1438
E01006701 0 1380
E01006702 0 1618
E01006703 0 1561
E01006705 2 1409
E01006706 0 1346
E01006707 0 1323
E01006708 0 1805
E01006709 4 1438
E01006710 1 1496
E01006711 1 1538
E01006712 1 1339
E01006713 0 1377
E01006716 4 1589
E01006717 1 1436
E01006718 3 1587
E01006719 0 1535
E01006720 8 2108
E01006721 1 1655
E01006722 4 1793
E01006723 3 1875
E01006724 4 1790
E01006725 3 1537
E01006726 3 1565
E01006727 4 1441
E01006728 0 1623
E01006729 2 1400
E01006730 4 1326
E01006731 3 1196
E01006732 0 1643
E01006734 3 1412
E01006735 1 1309
E01006736 1 1460
E01006737 0 1435
E01006738 0 1501
E01006739 0 1769
E01006740 0 1280
E01006741 1 1815
E01006742 1 1394
E01006743 2 2175
E01006744 1 1482
E01006745 0 1879
E01006746 2 1513
E01006747 2 3552
E01006748 6 1936
E01006751 1 2572
E01006752 1 1012
E01006753 0 1557
E01006754 0 1627
E01006755 0 1587
E01006756 1 1820
E01006757 0 1491
E01006758 0 1527
E01006759 1 1095
E01006760 5 1611
E01006761 0 1644
E01006762 4 1692
E01006763 2 1471
E01006764 2 1522
E01006765 2 1468
E01006766 2 1266
E01006767 4 1787
E01006768 0 1359
E01006769 1 1270
E01006770 0 1002
E01006771 1 1425
E01006772 2 1091
E01006773 1 1379
E01006774 2 1445
E01006775 1 1319
E01006776 3 1677
E01006778 1 2205
E01006779 0 1968
E01006780 2 1465
E01006781 5 1470
E01006782 0 1560
E01006783 2 1548
E01006784 0 1347
E01006785 2 1769
E01006786 0 1820
E01006787 2 2330
E01006788 2 1825
E01006790 1 1587
E01006791 0 1674
E01006792 1 1368
E01006793 6 1494
E01006794 5 1593
E01006795 2 1407
E01006796 2 1591
E01006797 5 1499
E01006798 0 1410
E01006799 4 1821
E01006800 2 1401
E01006801 1 1355
E01032505 2 1317
E01032506 4 1403
E01032507 4 1539
E01032508 2 1723
E01032509 1 1434
E01032510 1 1809
E01032511 2 1378
E01033747 3 1043
E01033748 2 1277
E01033749 0 1153
E01033750 5 1448
E01033751 1 1873
E01033752 6 1196
E01033753 7 1051
E01033754 9 1452
E01033755 9 1082
E01033756 5 1185
E01033757 3 1025
E01033758 2 1730
E01033759 2 1147
E01033760 4 2115
E01033761 11 1372
E01033762 3 1791
E01033763 4 1527
E01033764 0 2202
E01033765 3 1351
E01033766 7 1075
E01033767 1 1067
E01033768 6 1223Areas where there are no more than 750 Europeans:
euro750 <- census2021 %>%
filter(Europe < 750)
euro750 Europe Africa Middle.East.and.Asia The.Americas.and.the.Caribbean
E01033757 731 39 223 29
Antarctica.and.Oceania Total_Population
E01033757 3 1025Areas with exactly ten person from Antarctica and Oceania:
oneOA <- census2021 %>%
filter(`Antarctica.and.Oceania` == 10)
oneOA Europe Africa Middle.East.and.Asia The.Americas.and.the.Caribbean
E01006679 1353 484 354 31
Antarctica.and.Oceania Total_Population
E01006679 10 2232
TipTip with Title
These queries can grow in sophistication with almost no limits.
Combining queries
Now all of these queries can be combined with each other, for further flexibility. For example, imagine we want areas with more than 25 people from the Americas and Caribbean, but less than 1,500 in total:
ac25_l500 <- census2021 %>%
filter(The.Americas.and.the.Caribbean > 25, Total_Population < 1500)
ac25_l500 Europe Africa Middle.East.and.Asia The.Americas.and.the.Caribbean
E01033750 1235 53 129 26
E01033752 1024 19 114 33
E01033754 1262 37 112 32
E01033756 886 31 221 42
E01033757 731 39 223 29
E01033761 1138 52 138 33
Antarctica.and.Oceania Total_Population
E01033750 5 1448
E01033752 6 1196
E01033754 9 1452
E01033756 5 1185
E01033757 3 1025
E01033761 11 1372Sorting
Among the many operations DataFrame objects support, one of the most useful ones is to sort a table based on a given column. For example, imagine we want to sort the table by total population:
db_pop_sorted <- census2021 %>%
arrange(desc(Total_Population)) #sorts the dataframe by the "Total_Population" column in descending order
head(db_pop_sorted) Europe Africa Middle.East.and.Asia The.Americas.and.the.Caribbean
E01006747 2551 163 812 24
E01006513 2225 61 595 53
E01006751 1843 139 568 21
E01006524 2235 36 125 24
E01006787 2187 53 75 13
E01006537 2180 23 46 6
Antarctica.and.Oceania Total_Population
E01006747 2 3552
E01006513 7 2941
E01006751 1 2572
E01006524 11 2431
E01006787 2 2330
E01006537 2 2257Additional resources
A good introduction to data manipulation in R is the “Data wrangling” chapter in R for Data Science.
A good extension is Hadley Wickham’ “Tidy data” paper which presents a very popular way of organising tabular data for efficient manipulation.