30 Python Pandas training exercises solved in R Tidyverse
In the field of data science, both Python and R have their specific use cases. If there was ever any greater divide in the past, today both languages are slowly growing closer to each other. Even the IDE RStudio, widely used among data scientists working with R, has integrated Python into its environment, going as far as calling this step “A Data Science Love Story.”
While most people would argue for any data scientist to have at least a basic understanding of the other language, there are in fact too few training exercises out there that could help a beginner grasp both languages similarities, but also the apparent differences between R and Python.
To improve this situation at least a tiny bit, I have solved 30 out of 60 already existing Pandas exercises in R Tidyverse and R, so that one could tackle the training exercises side by side when preferred. Since I feel, at least until now, feel more familiar with Pandas than R Tidyverse, I am sure there my code still leaves room for improvement.
If you like to find out more about the differences and similarities between Pandas and R, I recommend checking out the Pandas Quick Reference and the article Anything you can do, I can do (kinda). Tidyverse pipes in Pandas by Steven Morse.
The easiest way to go through these exercises is solving the Pandas exercises inside an R notebook first and then comparing your solutions with the code provided here.
In case you find any mistakes or other, perhaps more efficient ways to solve any of these problems, please simply open an issue on Github.
Importing R Tidyverse
Getting started and checking your R Tidyverse setup
Difficulty: easy
1. Import the tidyverse package.
library(tidyverse)## -- Attaching packages --------------------------------------- tidyverse 1.3.0 --## v ggplot2 3.3.3 v purrr 0.3.4
## v tibble 3.1.0 v dplyr 1.0.5
## v tidyr 1.1.3 v stringr 1.4.0
## v readr 1.4.0 v forcats 0.5.1## Warning: Paket 'tibble' wurde unter R Version 4.0.4 erstellt## Warning: Paket 'tidyr' wurde unter R Version 4.0.4 erstellt## Warning: Paket 'dplyr' wurde unter R Version 4.0.4 erstellt## Warning: Paket 'forcats' wurde unter R Version 4.0.4 erstellt## -- Conflicts ------------------------------------------ tidyverse_conflicts() --
## x dplyr::filter() masks stats::filter()
## x dplyr::lag() masks stats::lag()2. Print the version of tidyverse that has been imported.
packageVersion("tidyverse")## [1] '1.3.0'3. Print out all the version information of the libraries that are included with the tidyverse library.
sessionInfo()## R version 4.0.3 (2020-10-10)
## Platform: x86_64-w64-mingw32/x64 (64-bit)
## Running under: Windows 10 x64 (build 19041)
##
## Matrix products: default
##
## locale:
## [1] LC_COLLATE=German_Germany.1252 LC_CTYPE=German_Germany.1252
## [3] LC_MONETARY=German_Germany.1252 LC_NUMERIC=C
## [5] LC_TIME=German_Germany.1252
##
## attached base packages:
## [1] stats graphics grDevices utils datasets methods base
##
## other attached packages:
## [1] forcats_0.5.1 stringr_1.4.0 dplyr_1.0.5 purrr_0.3.4
## [5] readr_1.4.0 tidyr_1.1.3 tibble_3.1.0 ggplot2_3.3.3
## [9] tidyverse_1.3.0
##
## loaded via a namespace (and not attached):
## [1] tidyselect_1.1.0 xfun_0.22 bslib_0.2.4 haven_2.3.1
## [5] colorspace_2.0-0 vctrs_0.3.7 generics_0.1.0 htmltools_0.5.1.1
## [9] yaml_2.2.1 utf8_1.2.1 rlang_0.4.10 jquerylib_0.1.3
## [13] pillar_1.5.1 withr_2.4.1 glue_1.4.2 DBI_1.1.1
## [17] dbplyr_2.1.0 modelr_0.1.8 readxl_1.3.1 lifecycle_1.0.0
## [21] munsell_0.5.0 blogdown_1.2 gtable_0.3.0 cellranger_1.1.0
## [25] rvest_1.0.0 evaluate_0.14 knitr_1.31 ps_1.6.0
## [29] fansi_0.4.2 broom_0.7.5 Rcpp_1.0.6 backports_1.2.1
## [33] scales_1.1.1 jsonlite_1.7.2 fs_1.5.0 hms_1.0.0
## [37] digest_0.6.27 stringi_1.5.3 bookdown_0.21 grid_4.0.3
## [41] cli_2.3.1 tools_4.0.3 magrittr_2.0.1 sass_0.3.1
## [45] crayon_1.4.1 pkgconfig_2.0.3 ellipsis_0.3.1 xml2_1.3.2
## [49] reprex_2.0.0 lubridate_1.7.10 rstudioapi_0.13 assertthat_0.2.1
## [53] rmarkdown_2.7 httr_1.4.2 R6_2.5.0 compiler_4.0.3DataFrame basics
A few of the fundamental routines for selecting, sorting, adding and aggregating data in DataFrames
Difficulty: easy
Consider the following Python dictionary data and Python list labels:
data = {'animal': ['cat', 'cat', 'snake', 'dog', 'dog', 'cat', 'snake', 'cat', 'dog', 'dog'],
'age': [2.5, 3, 0.5, np.nan, 5, 2, 4.5, np.nan, 7, 3],
'visits': [1, 3, 2, 3, 2, 3, 1, 1, 2, 1],
'priority': ['yes', 'yes', 'no', 'yes', 'no', 'no', 'no', 'yes', 'no', 'no']}
labels = ['a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j'](This is just some meaningless data I made up with the theme of animals and trips to a vet.)
4. Create an R tibble or dataframe called df from the (Python) data which has the index labels.
animal <-
c('cat',
'cat',
'snake',
'dog',
'dog',
'cat',
'snake',
'cat',
'dog',
'dog')
age <- c(2.5, 3, 0.5, NA, 5, 2, 4.5, NA, 7, 3)
visits <- c(1, 3, 2, 3, 2, 3, 1, 1, 2, 1)
priority <-
c('yes', 'yes', 'no', 'yes', 'no', 'no', 'no', 'yes', 'no', 'no')
df <- data.frame(age, animal, priority, visits)
row.names(df) <- c('a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j')
df <- as_tibble(df)
str(df)## tibble[,4] [10 x 4] (S3: tbl_df/tbl/data.frame)
## $ age : num [1:10] 2.5 3 0.5 NA 5 2 4.5 NA 7 3
## $ animal : chr [1:10] "cat" "cat" "snake" "dog" ...
## $ priority: chr [1:10] "yes" "yes" "no" "yes" ...
## $ visits : num [1:10] 1 3 2 3 2 3 1 1 2 15. Display a summary of the basic information about this DataFrame and its data (hint: there is a single method that can be called on the DataFrame).
summary(df)## age animal priority visits
## Min. :0.500 Length:10 Length:10 Min. :1.00
## 1st Qu.:2.375 Class :character Class :character 1st Qu.:1.00
## Median :3.000 Mode :character Mode :character Median :2.00
## Mean :3.438 Mean :1.90
## 3rd Qu.:4.625 3rd Qu.:2.75
## Max. :7.000 Max. :3.00
## NA's :2#glimpse(df)
#dim(df)
#str(df)
#print(df, quote = TRUE, row.names = FALSE)6. Return the first 3 rows of the DataFrame df.
head(df,3)## # A tibble: 3 x 4
## age animal priority visits
## <dbl> <chr> <chr> <dbl>
## 1 2.5 cat yes 1
## 2 3 cat yes 3
## 3 0.5 snake no 27. Select just the ‘animal’ and ‘age’ columns from the DataFrame df.
df %>%
select(animal, age)## # A tibble: 10 x 2
## animal age
## <chr> <dbl>
## 1 cat 2.5
## 2 cat 3
## 3 snake 0.5
## 4 dog NA
## 5 dog 5
## 6 cat 2
## 7 snake 4.5
## 8 cat NA
## 9 dog 7
## 10 dog 38. Select the data in rows [3, 4, 8] and in columns ['animal', 'age'].
df %>%
select(animal, age) %>%
slice(3, 4, 8)## # A tibble: 3 x 2
## animal age
## <chr> <dbl>
## 1 snake 0.5
## 2 dog NA
## 3 cat NA9. Select only the rows where the number of visits is greater than 3.
df %>%
filter(visits > 3)## # A tibble: 0 x 4
## # ... with 4 variables: age <dbl>, animal <chr>, priority <chr>, visits <dbl>10. Select the rows where the age is missing, i.e. it is NaN.
df[rowSums(is.na(df)) > 0,]## # A tibble: 2 x 4
## age animal priority visits
## <dbl> <chr> <chr> <dbl>
## 1 NA dog yes 3
## 2 NA cat yes 111. Select the rows where the animal is a cat and the age is less than 3.
df %>%
select(age,animal)## # A tibble: 10 x 2
## age animal
## <dbl> <chr>
## 1 2.5 cat
## 2 3 cat
## 3 0.5 snake
## 4 NA dog
## 5 5 dog
## 6 2 cat
## 7 4.5 snake
## 8 NA cat
## 9 7 dog
## 10 3 dog filter(df, animal == "cat") %>%
filter(age < 3)## # A tibble: 2 x 4
## age animal priority visits
## <dbl> <chr> <chr> <dbl>
## 1 2.5 cat yes 1
## 2 2 cat no 312.** Select the rows the age is between 2 and 4 (inclusive).
df %>%
filter(1 < age & age < 5)## # A tibble: 5 x 4
## age animal priority visits
## <dbl> <chr> <chr> <dbl>
## 1 2.5 cat yes 1
## 2 3 cat yes 3
## 3 2 cat no 3
## 4 4.5 snake no 1
## 5 3 dog no 113. Change the age in row ‘f’ to 1.5.
df[6, 1] = 1.5 #need to check whether row names were included14. Calculate the sum of all visits in df (i.e. find the total number of visits).
df %>%
select(visits) #integer column needs to be selected first## # A tibble: 10 x 1
## visits
## <dbl>
## 1 1
## 2 3
## 3 2
## 4 3
## 5 2
## 6 3
## 7 1
## 8 1
## 9 2
## 10 1 sum(visits)## [1] 1915. Calculate the mean age for each different animal in df.
df %>%
group_by(animal) %>%
summarise(m = mean(age)) #NAs are not ignored## # A tibble: 3 x 2
## animal m
## <chr> <dbl>
## 1 cat NA
## 2 dog NA
## 3 snake 2.516. Append a new row ‘k’ to df with your choice of values for each column. Then delete that row to return the original DataFrame.
k <- c(1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
with_and_without_k <- df %>%
add_column(k)# and then deleting the new row...
with_and_without_k %>%
select(-one_of("k"))## # A tibble: 10 x 4
## age animal priority visits
## <dbl> <chr> <chr> <dbl>
## 1 2.5 cat yes 1
## 2 3 cat yes 3
## 3 0.5 snake no 2
## 4 NA dog yes 3
## 5 5 dog no 2
## 6 1.5 cat no 3
## 7 4.5 snake no 1
## 8 NA cat yes 1
## 9 7 dog no 2
## 10 3 dog no 117. Count the number of each type of animal in df.
df %>%
count(animal)## # A tibble: 3 x 2
## animal n
## <chr> <int>
## 1 cat 4
## 2 dog 4
## 3 snake 218. Sort df first by the values in the ‘age’ in decending order, then by the value in the ‘visits’ column in ascending order (so row i should be first, and row d should be last).
df %>%
arrange(desc(age), visits)## # A tibble: 10 x 4
## age animal priority visits
## <dbl> <chr> <chr> <dbl>
## 1 7 dog no 2
## 2 5 dog no 2
## 3 4.5 snake no 1
## 4 3 dog no 1
## 5 3 cat yes 3
## 6 2.5 cat yes 1
## 7 1.5 cat no 3
## 8 0.5 snake no 2
## 9 NA cat yes 1
## 10 NA dog yes 319. The ‘priority’ column contains the values ‘yes’ and ‘no’. Replace this column with a column of boolean values: ‘yes’ should be True and ‘no’ should be False.
df %>%
mutate(boolean_values = case_when(priority == 'yes' ~ 1,
priority == 'no' ~ 0))## # A tibble: 10 x 5
## age animal priority visits boolean_values
## <dbl> <chr> <chr> <dbl> <dbl>
## 1 2.5 cat yes 1 1
## 2 3 cat yes 3 1
## 3 0.5 snake no 2 0
## 4 NA dog yes 3 1
## 5 5 dog no 2 0
## 6 1.5 cat no 3 0
## 7 4.5 snake no 1 0
## 8 NA cat yes 1 1
## 9 7 dog no 2 0
## 10 3 dog no 1 020. In the ‘animal’ column, change the ‘snake’ entries to ‘python’.
df %>%
select(animal)## # A tibble: 10 x 1
## animal
## <chr>
## 1 cat
## 2 cat
## 3 snake
## 4 dog
## 5 dog
## 6 cat
## 7 snake
## 8 cat
## 9 dog
## 10 dog recode(animal, snake = "python")## [1] "cat" "cat" "python" "dog" "dog" "cat" "python" "cat"
## [9] "dog" "dog"21. For each animal type and each number of visits, find the mean age. In other words, each row is an animal, each column is a number of visits and the values are the mean ages (hint: use a pivot table).
df %>%
group_by(animal) %>%
summarise(mean = mean(visits), n = n())## # A tibble: 3 x 3
## animal mean n
## <chr> <dbl> <int>
## 1 cat 2 4
## 2 dog 2 4
## 3 snake 1.5 2DataFrames: beyond the basics
Slightly trickier: you may need to combine two or more methods to get the right answer
Difficulty: medium
The previous section was tour through some basic but essential DataFrame operations. Below are some ways that you might need to cut your data, but for which there is no single “out of the box” method.
22. You have a DataFrame df with a column ‘A’ of integers. For example:
df = pd.DataFrame({'A': [1, 2, 2, 3, 4, 5, 5, 5, 6, 7, 7]})library(tidyverse)
A <- c(1, 2, 2, 3, 4, 5, 5, 5, 6, 7, 7)
df <- data.frame(A)
df <- as_tibble(df) How do you filter out rows which contain the same integer as the row immediately above?
You should be left with a column containing the following values:
1, 2, 3, 4, 5, 6, 7df %>%
distinct()## # A tibble: 7 x 1
## A
## <dbl>
## 1 1
## 2 2
## 3 3
## 4 4
## 5 5
## 6 6
## 7 723. Create a dataframe df with 5x3 of random numeric values
df = pd.DataFrame(np.random.random(size=(5, 3))) # this is a 5x3 DataFrame of float valuesdf = data.frame(replicate(5,runif(3, min=5, max=10),3))How do you subtract the row mean from each element in the row?
df_minus_row_mean <- sweep(df, MARGIN=1, STATS= rowMeans(df))
df_minus_row_mean## X1 X2 X3 X4 X5
## 1 0.4120193 -0.9561412 2.492796 -1.291351 -0.6573229
## 2 0.5441137 -1.4116169 -1.329254 1.595746 0.6010108
## 3 0.6777554 1.1990000 -1.104223 -1.476673 0.704140524. Suppose you have DataFrame with 10 columns of real numbers, for example:
df = data.frame(replicate(10,runif(10, min=5, max=10),3))df = data.frame(replicate(10,runif(10, min=5, max=10),3))Which column of numbers has the smallest sum? Return that column’s label.
sort(colSums(df), decreasing = FALSE)[1]## X4
## 71.0375425. How do you count how many unique rows a DataFrame has (i.e. ignore all rows that are duplicates)? As input, use a DataFrame of zeros and ones with 10 rows and 3 columns.
df = data.frame(replicate(3,sample(0:1,10,rep=TRUE)))
df %>%
distinct() %>%
nrow()## [1] 6The next three puzzles are slightly harder.
26. In the cell below, you have a DataFrame df that consists of 10 columns of floating-point numbers. Exactly 5 entries in each row are NaN values.
For each row of the DataFrame, find the column which contains the third NaN value.
You should return a Series of column labels: e, c, d, h, d
nan = np.nan
data = [[0.04, nan, nan, 0.25, nan, 0.43, 0.71, 0.51, nan, nan],
[ nan, nan, nan, 0.04, 0.76, nan, nan, 0.67, 0.76, 0.16],
[ nan, nan, 0.5 , nan, 0.31, 0.4 , nan, nan, 0.24, 0.01],
[0.49, nan, nan, 0.62, 0.73, 0.26, 0.85, nan, nan, nan],
[ nan, nan, 0.41, nan, 0.05, nan, 0.61, nan, 0.48, 0.68]]
columns = list('abcdefghij')
df = pd.DataFrame(data, columns=columns)# write a solution to the question here
library(tidyverse)
library(data.table)## Warning: Paket 'data.table' wurde unter R Version 4.0.4 erstellt##
## Attache Paket: 'data.table'## The following objects are masked from 'package:dplyr':
##
## between, first, last## The following object is masked from 'package:purrr':
##
## transposedata = list(
list(0.04, NaN, NaN, 0.25, NaN, 0.43, 0.71, 0.51, NaN, NaN),
list(NaN, NaN, NaN, 0.04, 0.76, NaN, NaN, 0.67, 0.76, 0.16),
list(NaN, NaN, 0.5 , NaN, 0.31, 0.4 , NaN, NaN, 0.24, 0.01),
list(0.49, NaN, NaN, 0.62, 0.73, 0.26, 0.85, NaN, NaN, NaN),
list(NaN, NaN, 0.41, NaN, 0.05, NaN, 0.61, NaN, 0.48, 0.68)
)
df = data.table::rbindlist(data)
letters_to_split = c(strsplit("abcdefghij", ""))
character_vectors_for_columns = unlist(letters_to_split)
colnames(df) <- character_vectors_for_columnsFor each row of the DataFrame, find the column which contains the third NaN value.
subscript <- apply(df, MARGIN =1, FUN = function(x) which(is.na(x))[3])
colnames(df)[subscript]## [1] "e" "c" "d" "h" "d"27. A DataFrame has a column of groups ‘grps’ and and column of integer values ‘vals’:
splitted_list = strsplit('aaabbcaabcccbbc', "")
vals = c(12,345,3,1,45,14,4,52,54,23,235,21,57,3,87)
df <- data.frame(splitted_list, vals)
colnames(df) <- c('grps', 'vals')For each group, find the sum of the three greatest values. You should end up with the answer as follows:
grps
a 409
b 156
c 345df %>%
group_by(grps) %>%
summarise(sum= sum(vals))## # A tibble: 3 x 2
## grps sum
## <chr> <dbl>
## 1 a 416
## 2 b 160
## 3 c 38028. The DataFrame df constructed below has two integer columns ‘A’ and ‘B’. The values in ‘A’ are between 1 and 100 (inclusive).
For each group of 10 consecutive integers in ‘A’ (i.e. (0, 10], (10, 20], …), calculate the sum of the corresponding values in column ‘B’.
The answer should be a Series as follows:
A
(0, 10] 635
(10, 20] 360
(20, 30] 315
(30, 40] 306
(40, 50] 750
(50, 60] 284
(60, 70] 424
(70, 80] 526
(80, 90] 835
(90, 100] 852df = data.frame(replicate(2,sample(0:100,100,rep=TRUE)))
x <- c("A", "B")
colnames(df) <- x# write a solution to the question here
sum_binned_columns = df %>%
mutate(bin = cut(A, breaks = c(Inf, 0, 10, 20, 30, 40, 50, 60, 70, 80, 90, 100))) %>%
group_by(bin) %>%
summarise(sum = sum(B), n = n())
sum_binned_columns## # A tibble: 11 x 3
## bin sum n
## <fct> <int> <int>
## 1 (0,10] 527 12
## 2 (10,20] 256 7
## 3 (20,30] 282 4
## 4 (30,40] 861 16
## 5 (40,50] 676 14
## 6 (50,60] 603 12
## 7 (60,70] 457 8
## 8 (70,80] 906 16
## 9 (80,90] 307 5
## 10 (90,100] 239 5
## 11 <NA> 38 1DataFrames: harder problems
These might require a bit of thinking outside the box…
…but all are solvable using just the usual pandas/NumPy methods (and so avoid using explicit for loops).
Difficulty: hard
29. Consider a DataFrame df where there is an integer column ‘X’:
df = pd.DataFrame({'X': [7, 2, 0, 3, 4, 2, 5, 0, 3, 4]})For each value, count the difference back to the previous zero (or the start of the Series, whichever is closer). These values should therefore be
[1, 2, 0, 1, 2, 3, 4, 0, 1, 2]Make this a new column ‘Y’
library(tidyverse)
integers = c(7, 2, 0, 3, 4, 2, 5, 0, 3, 4)
l = c(7, 2, 0, 3, 4, 2, 5, 0, 3, 4)
i = 0
`%+=%` = function(e1,e2) eval.parent(substitute(e1 <- e1 + e2))
for (element in l) {
if (element != 0) {
i %+=% 1
}
else {
i = 0
}
vector <- c(vector, i)
}30. Consider the DataFrame constructed below which contains rows and columns of numerical data.
Create a list of the column-row index locations of the 3 largest values in this DataFrame. In this case, the answer should be:
list(c(5, 7), c(6, 4), c(2, 5))df = data.frame(replicate(8,sample(1:101,8,rep=TRUE)))lst = c(tail(sort(unlist(df, use.names = FALSE)), 3))
for (element in lst) {
print(element)
print(which(df==element, arr.ind=TRUE))
}## [1] 98
## row col
## [1,] 8 8
## [1] 99
## row col
## [1,] 1 7
## [1] 101
## row col
## [1,] 3 8You can download the whole script on my Github.
