Manipulating a dataframe

user_13811872 0
Viewed 88

Still a beginner in R and would appreciate any help with two questions about manipulating a dataframe df that is structured like this:

structure(list(country = c("Brazil", "Brazil", "Brazil", "France", 
"France", "France"), date = structure(c(18353, 18354, 18355, 
18353, 18354, 18355), class = "Date"), group = c(1, 1, 1, 1, 
1, 1), share = c(0.00480432389150235, 0.00576122906219994, 0.00577170418006431, 
0.0883595235712141, 0.0959849212276397, 0.104823151125402), max_share = c(0.110235379599597, 
0.110235379599597, 0.110235379599597, 0.122052589288188, 0.122052589288188, 
0.122052589288188)), row.names = c(NA, 6L), class = "data.frame")

enter image description here

(1) The variable max_share is the maximum share for each country observed between the initial date and end date. I came up with a rather clumsy way to add this variable into the df and was wondering if there is a more efficient way of doing this. Here's what I did:

df1 <- df %>%
  group_by(country) %>%
  summarize(max_share=max(share)) %>%
  select(country, max_share)

df <- merge(df, df1, all=TRUE, by.x="country",by.y="country")

(2) I would like to create within the existing df two subgroups as follows.

I would like to add a second group variable called selection. I would like to pick those countries where max_share>0.10 and attribute for them the value 1 to selection.

Next, I would like to add to the df another country called Rest of the world. The Rest of the world observation will be labelled 2 under selection. Note that we will have several new observations as we have multiple dates for Rest of the world.

And then the bit I was struggling with. I would like that the share for these new observations is calculated as the sum of the share of the other countries under selection == 1.

I tried summarise and aggregate but could not identify how to keep the row at the same dimension, change the values for the non-numeric variables and only sum the numeric ones. There's probably a simple way to do this. Just couldn't figure it out.

In the end the df would contain the original set of countries, a new set of rows for the new observation Rest of the world (1 new row for each date), and a new column for the variable called selection.

Thanks for any help!

dplyr r
3 Answers

Answer to question 1:

df <- df %>%
  group_by(country) %>%
  mutate(max_share = max(share)) %>%
  ungroup()
user_72102500

Using aggregate to make your 'selection'

df$selection <- df$country %in% with(aggregate(share ~ country, data = df, FUN = max), country[share > 0.1]))

  country       date group       share selection
1  Brazil 2020-04-01     1 0.004804324         0
2  Brazil 2020-04-02     1 0.005761229         0
3  Brazil 2020-04-03     1 0.005771704         0
4  France 2020-04-01     1 0.088359524         1
5  France 2020-04-02     1 0.095984921         1
6  France 2020-04-03     1 0.104823151         1
user_104424230

Using dplyr::case_when and dplyr::replace_na:

df2<-structure(list(country = c("Rest of the world", "Rest of the world", "Rest of the world"), date = structure(c(18353, 18354, 18355), class = "Date"), group = c(1, 1, 1), share = c(NA,NA,NA)), row.names = c(NA, 3L), class = "data.frame")
df %>%
  bind_rows(df2) %>% 
  group_by(country) %>% 
  mutate(max_share=max(share),
         selection=case_when(max_share > 0.10 & country != "Rest of the world" ~ 1,
                             country=="Rest of the world" ~ 2,
                             TRUE~0)) %>% 
  ungroup() %>% 
  mutate(share=replace_na(max_share,sum(share[selection==1])))

Resulting df:

# A tibble: 9 x 6
  country           date       group   share max_share selection
  <chr>             <date>     <dbl>   <dbl>     <dbl>     <dbl>
1 Brazil            2020-04-01     1 0.00577   0.00577         0
2 Brazil            2020-04-02     1 0.00577   0.00577         0
3 Brazil            2020-04-03     1 0.00577   0.00577         0
4 France            2020-04-01     1 0.105     0.105           1
5 France            2020-04-02     1 0.105     0.105           1
6 France            2020-04-03     1 0.105     0.105           1
7 Rest of the world 2020-04-01     1 0.289    NA               2
8 Rest of the world 2020-04-02     1 0.289    NA               2
9 Rest of the world 2020-04-03     1 0.289    NA               2

With df2 being:

            country       date group share
1 Rest of the world 2020-04-01     1    NA
2 Rest of the world 2020-04-02     1    NA
3 Rest of the world 2020-04-03     1    NA

Is that what you want?

user_134606020
Related