CODEWITHSUNDEEP
rtidyr
Frank B.
Frank B.

Reputation: 1883

Pivoting Data in R Dataframe

I have data in the form of: 

df_original <- 
  tibble(
    unique_id = c(1, 1, 2, 2), 
    player_name = c('A', 'B', 'C', 'D'), 
    player_points = c(5, 7, 3, 4)
  )

  unique_id player_name player_points
      <dbl> <chr>               <dbl>
1         1 A                       5
2         1 B                       7
3         2 C                       3
4         2 D                       4

I would like to transform it so the resulting dataframe looks like this: 

  unique_id player_name player_points_scored player_points_allowed
      <dbl> <chr>                      <dbl>                 <dbl>
1         1 A                              5                     7
2         1 B                              7                     5
3         2 C                              3                     4
4         2 D                              4                     3

Essentially I want to "group" by the unique_id and then transform it. I'm assuming the pivot_wider function in tidyr is the solution but the syntax is not intuitive to me and I cannot figure out how to solve it.

Upvotes: 1

Views: 62

Answers (2)

akrun
akrun

Reputation: 887831

We can reverse by index

library(dplyr)
df_original %>%
      group_by(unique_id) %>%
      mutate(player_points_allowed = player_points[n():1])

Or with data.table

library(data.table)
setDT(df_original)[, player_points_allowed := player_points[.N:1], unique_id]

Upvotes: 0

Ronak Shah
Ronak Shah

Reputation: 389235

We can use rev to reverse player_points by unique_id.

library(dplyr)
df_original %>% 
    group_by(unique_id) %>% 
    mutate(player_points_allowed = rev(player_points))

#  unique_id player_name player_points player_points_allowed
#      <dbl> <chr>               <dbl>                 <dbl>
#1         1 A                       5                     7
#2         1 B                       7                     5
#3         2 C                       3                     4
#4         2 D                       4                     3

The same can be implemented in base R

df_original$player_points_allowed <- with(df_original, ave(player_points,
                                           unique_id, FUN = rev))

and data.table as well. 

library(data.table)
setDT(df_original)[, player_points_allowed := rev(player_points), unique_id]

Upvotes: 3

Related Questions