How to make a column into column names in R

Viewed 51

I have a dataframe of 3 columns as below

    batsman1                 bowler1         timesOut
1   A Ashish Reddy          A Ashish Reddy        0
2   A Ashish Reddy              A Chandila        1
3   A Ashish Reddy             A Choudhary        3
4   A Ashish Reddy             A Dananjaya        0

I would like to transform the above dataframe where the second column becomes column names as below

    batsman1         A Ashish Reddy A Chandila A Choudhary A Danajaya

 A Ashish Reddy.         0             1         3             0
 A Chandila
 A Choudhary
 A Danajaya
3 Answers

A way might be to use xtabs.

xtabs(timesOut ~ ., x)
#                bowler1
#batsman1         1 A_Ashish_Reddy A_Chandila A_Choudhary A_Dananjaya
#  A_Ashish_Reddy 0              0          1           3           0
#  A_Chandila     0              0          0           0           0
#  A_Choudhary    0              0          0           0           0
#  A_Dananjaya    0              0          0           0           0

Data:

x <- read.table(text="    batsman1                 bowler1         timesOut
1   A_Ashish_Reddy          A_Ashish_Reddy        0
2   A_Ashish_Reddy              A_Chandila        1
3   A_Ashish_Reddy             A_Choudhary        3
4   A_Ashish_Reddy             A_Dananjaya        0")
x[1] <- factor(x[[1]], unique(unlist(x[1:2])))
x[2] <- factor(x[[2]], levels(x[,1]))

A possible solution, based on tidyr::pivot_wider:

library(tidyverse)

df %>% 
  pivot_wider(batsman1, names_from = bowler1, values_from = timesOut) %>% 
  bind_rows(list(batsman1 = df$bowler1)) %>% 
  filter(!duplicated(batsman1))

#> # A tibble: 4 × 5
#>   batsman1       `A Ashish Reddy` `A Chandila` `A Choudhary` `A Dananjaya`
#>   <chr>                     <int>        <int>         <int>         <int>
#> 1 A Ashish Reddy                0            1             3             0
#> 2 A Chandila                   NA           NA            NA            NA
#> 3 A Choudhary                  NA           NA            NA            NA
#> 4 A Dananjaya                  NA           NA            NA            NA

Here is another possible solution using reshape and plyr:

reshape2::dcast(df, batsman1 ~ bowler1, value.var = "timesOut") %>%
  plyr::rbind.fill(data.frame(batsman1 = df$bowler1[!df$bowler1 %in% df$batsman1]))

Output

        batsman1 A_Ashish_Reddy A_Chandila A_Choudhary A_Dananjaya
1 A_Ashish_Reddy              0          1           3           0
2     A_Chandila             NA         NA          NA          NA
3    A_Choudhary             NA         NA          NA          NA
4    A_Dananjaya             NA         NA          NA          NA

If you would rather have 0s instead of NA, then we can use mutate to clean those up.

reshape2::dcast(df, batsman1 ~ bowler1, value.var = "timesOut") %>%
  plyr::rbind.fill(data.frame(batsman1 = df$bowler1[!df$bowler1 %in% df$batsman1])) %>%
  dplyr::mutate(across(everything(), ~ replace(., is.na(.), 0)))

Output

        batsman1 A_Ashish_Reddy A_Chandila A_Choudhary A_Dananjaya
1 A_Ashish_Reddy              0          1           3           0
2     A_Chandila              0          0           0           0
3    A_Choudhary              0          0           0           0
4    A_Dananjaya              0          0           0           0
Related