CODEWITHSUNDEEP
rxtabs
Corina Roca
Corina Roca

Reputation: 546

Alternative to "pivot_wider" in R base (and a bit of dplyr)

I had to deliver an output table for a Sankey diagram and pivot_wide (tidyr library) was great. But when writing my code, I had no permissions to install any other libraries in the environment. So I only had dplyr and R base.

My task: Do Pivot_Wider on a table like this:

df1 <- structure(list(SEZ = c("1_1_1", "1_1_1", "1_1_2", "1_1_3", "1_1_3", 
                              "1_1_4", "1_1_5", "1_2_1", "1_2_1"),
                      Class = c(1L, 5L, 5L, 1L,   5L, 1L, 2L, 1L, 5L), 
                      Val = c("AB", "AB", "AB", "B", "AB", "B", "CD", "CD", "AB")), 
                 row.names = c(NA, -9L), class = "data.frame")

enter image description here

I found a great solution from @Akrun in Stackoverflow here. But It raised an error because the "Val" needs to be integer or numeric, not a text, like in my table.

enter image description here

I found my workaround (Posted also in that Stackoverflow question line) , but maybe somebody else has a even shortes solution? It would be great to know!

My current one is as follows:

#We need to convert the Output column "Val" into numeric, and later return it back to their text.
#This script do the work (**R version 4.3.1 (2023-06-16)** & **dplyr 1.1.2**): 
# create list to translate
# create list to translate
list_mapTclass <- seq(1:length(unique(df1$Val)))
print(length(list_mapTclass))
names(list_mapTclass) <-unique(df1$Val)

list_mapTclass[1:3]

df1$idxVal <- sapply(df1$Val, function(x) list_mapTclass[[x]] )

#essential! conver to factor
#df1$idxVal <- factor(df1$idxVal)

#df1

#xtabs(idxVal ~ SEZ + Class, df1)
wide_df_matrix <-xtabs(idxVal ~ SEZ + Class, df1)
 



#print(list_mapTclassInverted[1:3])
#--- add subid
wide_df0 <- as.data.frame.matrix( wide_df_matrix) %>% 
  setNames(paste0('col_', names(.))) #%>%

wide_df0
# reconvert values into the therapyclass content

# inverted list:

list_mapTclassInverted <- setNames(names(list_mapTclass), list_mapTclass)
# sometimes if you dont have 0s , you have to include it in the "list_mapTclassInverted" , as "Unknown"
#list_mapTclassInverted[['0']] <- 'Unknown'
print(list_mapTclassInverted)
wide_df0



# Next piece of code is to go through the full table and convert all the integers into their text
### VERY USEFUL! (Thanks to: @Martin Morgan - https://stackoverflow.com/questions/7547597/dictionary-style-replace-multiple-items )

wide_df00 <- wide_df0
wide_df00[] <- list_mapTclassInverted[unlist(wide_df00)]

wide_df00
## convert to a dataframe

wide_df0$SEZ <- row.names(wide_df0) 
row.names(wide_df0) <- NULL

# see the output:
wide_df0

enter image description here

I hope this save also some of your time!

Upvotes: 1

Views: 267

Answers (1)

uke
uke

Reputation: 856

Here is an approach using reshape(), which is available in base R.

Note that I am converting Val to numeric using @Wimpel's approach of first converting the column to factor and then to numeric.

df1$Val <- df1$Val |> as.factor() |> as.numeric()

reshape(df1, 
        direction = "wide",
        idvar = "SEZ",
        timevar = "Class")

Result:

    SEZ Val.1 Val.5 Val.2
1 1_1_1     1     1    NA
3 1_1_2    NA     1    NA
4 1_1_3     2     1    NA
6 1_1_4     2    NA    NA
7 1_1_5    NA    NA     3
8 1_2_1     3     1    NA

For comparison: pivot_wider() code that produces an equivalent table (because that is what is usually used if you are not restricted to base R)

tidyr::pivot_wider(df1, values_from = Val, names_from = Class)

Upvotes: 1

Related Questions