Convert multiple rows to multiple columns (long to wide format does not work (?))

user_13676900 0
Viewed 171

I have a dataset with multiple journal articles in it. The different articles all have different identification codes (WoS_No). Different articles are on different rows.

These articles have different numbers of authors. If a paper has more than 1 author, the identification code gets duplicated over multiple rows, with one row per author.

There is other information in the df, some of which relates to the paper (and is the same for all rows with the same WoS_No code. But, some relates to the authors only (like their faculty) which is then printed out over rows.

Please see example below:

# Original df
df <- data.frame("WoS_No" = matrix(c("WOS:000352315900021", "WOS:000352315900021", "WOS:000352315900021", "WOS:000352315900021", "WOS:000362644700013", "WOS:000362644700013", "WOS:000382460200025", "WOS:000381736200014", "WOS:000371540200019"), 9, 1))
df$Author <- c("CHENEVIX, Georg", "CHENEVIX, Georg", "DOLCE, Ric", "DOLCE, Ric", "CLOUST, A", "STEVEN, A", "WANG, Zhi", "COIN, L", "BARL, Kare")
df$Faculty <- c("Medicine", NA, "HASS", NA, "HABS", "Medicine", "Medicine", "IMB", NA)
df$CNCI <- c(10.51, 10.51, 10.51, 10.51, 37.47, 37.47,  0.84,  8.05, 29.41)
sapply(data2, class)

Original_df_format

I would really like to have the df arranged so there is only 1 row per article (i.e., one WoS_No per row).

I would like the author names to be split out into different columns (see 'Author1', 'Author2' columns below). I tried converting from long to wide format, but it did not work, possibly because the authors are different on most articles - so it gave each name a new column (which I cannot have as there are about 20,000 names)

If this is too fiddly, I would be happy with all author names collapsed into one string in an 'Authors' column, with all names separated by a semicolon (meaning I could just strsplit them later when needed). See 'Faculties' column below.

# New df options

dfnew <- data.frame("WoS_No" = matrix(c("WOS:000352315900021", "WOS:000362644700013", "WOS:000382460200025", "WOS:000381736200014", "WOS:000371540200019"), 5, 1))
dfnew$Author1 <- c("CHENEVIX, Georg", "CLOUST, A", "WANG, Zhi", "COIN, L", "BARL, Kare")
dfnew$Author2 <- c("DOLCE, Ric", "STEVEN, A", "", "", "")
dfnew$Faculties <- c("Medicine; NA; HASS; NA", "HABS; Medicine", "Medicine", "IMB", "NA")
dfnew$CNCI <- c(10.51, 37.47,  0.84,  8.05, 29.41)

New_df_options

I tried for looping through each WoS_No and collapsing one by one, but because I have 68,000 WoS_No's this was failing to finish in a sensible time.

I am really stumped and would very much appreciate any help anyone could give me.

reshape rows dataframe r
4 Answers

You can first keep only unique rows using distinct, group_by WoS_No to create an unique identifier column and get data in wide format.

library(dplyr)

df %>%
  distinct(WoS_No, Author, .keep_all = TRUE) %>%
  group_by(WoS_No) %>%
  mutate(row = row_number()) %>%
  tidyr::pivot_wider(names_from = row, values_from = c(Author, Faculty))

#  WoS_No               CNCI Author_1        Author_2   Faculty_1 Faculty_2
#  <chr>               <dbl> <chr>           <chr>      <chr>     <chr>    
#1 WOS:000352315900021 10.5  CHENEVIX, Georg DOLCE, Ric Medicine  HASS     
#2 WOS:000362644700013 37.5  CLOUST, A       STEVEN, A  HABS      Medicine 
#3 WOS:000382460200025  0.84 WANG, Zhi       NA         Medicine  NA       
#4 WOS:000381736200014  8.05 COIN, L         NA         IMB       NA       
#5 WOS:000371540200019 29.4  BARL, Kare      NA         NA        NA

Note that, I also transformed Faculty into different columns. If you want to keep them in one column as shown in your expected output you can do it with minimal change in the code.

user_39629140

A humble base R solution using beautiful reshape():

data <- df[!duplicated(df[, c("WoS_No", "Author")]),]
data$grp.id <- ave(data$WoS_No, data$WoS_No, FUN = seq_along)

reshaped_data  <- reshape(data, idvar= "WoS_No", timevar= "grp.id",
                          v.names=c("Author", "Faculty"), direction="wide")

               WoS_No  CNCI        Author.1 Faculty.1   Author.2 Faculty.2
1 WOS:000352315900021 10.51 CHENEVIX, Georg  Medicine DOLCE, Ric      HASS
5 WOS:000362644700013 37.47       CLOUST, A      HABS  STEVEN, A  Medicine
7 WOS:000382460200025  0.84       WANG, Zhi  Medicine       <NA>      <NA>
8 WOS:000381736200014  8.05         COIN, L       IMB       <NA>      <NA>
9 WOS:000371540200019 29.41      BARL, Kare      <NA>       <NA>      <NA>

idvar identifies the groups within which we want to spread.

timevar identifies the observations within the groups. We needed to create grp.id for that.

v.names names the columns which we want to spread.

direction tells us to convert to wide format.

user_109948550

This is a solution in which you have Authors as well as Faculties separated by a semicolon, like in your expected output.

library(dplyr)

df %>% 
  group_by(WoS_No) %>% 
  mutate(
    Authors = paste(unique(Author), collapse = "; "),
    Faculties = paste(Faculty, collapse = "; ")
    ) %>% 
  select(WoS_No, Authors, Faculties, CNCI) %>% 
  distinct()

# A tibble: 5 x 4
# Groups:   WoS_No [5]
#   WoS_No              Authors                     Faculties               CNCI
#   <chr>               <chr>                       <chr>                  <dbl> 
# 1 WOS:000352315900021 CHENEVIX, Georg; DOLCE, Ric Medicine; NA; HASS; NA 10.5 
# 2 WOS:000362644700013 CLOUST, A; STEVEN, A        HABS; Medicine         37.5  
# 3 WOS:000382460200025 WANG, Zhi                   Medicine                0.84
# 4 WOS:000381736200014 COIN, L                     IMB                     8.05
# 5 WOS:000371540200019 BARL, Kare                  NA                     29.4
user_74654620

here is a data.table-approach

library( data.table )
#make df a data.table
setDT(df)
#first, paste the (unique) authors together by WoS_No
ans <- df[, .( authors = paste0( unique(Author), collapse = ";" ),
               Faculty = paste0( Faculty, collapse = ";" ),
               CNCI = unique(CNCI) ), by = WoS_No][]
#                 WoS_No                    authors             Faculty  CNCI
# 1: WOS:000352315900021 CHENEVIX, Georg;DOLCE, Ric Medicine;NA;HASS;NA 10.51
# 2: WOS:000362644700013        CLOUST, A;STEVEN, A       HABS;Medicine 37.47
# 3: WOS:000382460200025                  WANG, Zhi            Medicine  0.84
# 4: WOS:000381736200014                    COIN, L                 IMB  8.05
# 5: WOS:000371540200019                 BARL, Kare                  NA 29.41

#split the author column
ans[, paste0( "Author", 1:length(tstrsplit( ans$authors, ";" ) )) := tstrsplit( authors, ";")]
#                 WoS_No                    authors             Faculty  CNCI         Author1    Author2
# 1: WOS:000352315900021 CHENEVIX, Georg;DOLCE, Ric Medicine;NA;HASS;NA 10.51 CHENEVIX, Georg DOLCE, Ric
# 2: WOS:000362644700013        CLOUST, A;STEVEN, A       HABS;Medicine 37.47       CLOUST, A  STEVEN, A
# 3: WOS:000382460200025                  WANG, Zhi            Medicine  0.84       WANG, Zhi       <NA>
# 4: WOS:000381736200014                    COIN, L                 IMB  8.05         COIN, L       <NA>
# 5: WOS:000371540200019                 BARL, Kare                  NA 29.41      BARL, Kare       <NA>
user_63562780
Related