CODEWITHSUNDEEP
rloops
Ben
Ben

Reputation: 1154

Using a column index and loop to transform dataframe

I am trying to write a function that uses indices that takes key-value pairs and stacks them.

Here is my data:

mydata<-structure(list(groupA = c("Rugby for Chipmunks", "Rugby for Chipmunks", "Rugby for Chipmunks", "Chafing Explained"), First = c(5, 3.57142857142857, 5, 4.5), groupB = c("Pylons for Priests", "Eating Creosote", "Eating Creosote", "Eating Creosote"), Second = c(4, 4, 3.16666666666667, 2.1666667), groupC = c("Wow for YOU!", "Advanced Cats for Bears", "Blue Paint Only", "Mockingbirds"), Third = c(5, 3, NaN, 4), groupD = c("How to Sell Pigeons", "How to Sell Pigeons", "How to Sell Pigoens", "Larger Boulders"), Fourth = c(4.3, 3, 4.1, 3.4), groupE = c("Making Money with Pears", "Making Money with Pears", "Why Walnuts?", "Responding to Idiots Part II"), Fifth = c(5, 3, 5, 4.16666666666667)), row.names = c(NA, -4L), class = c("tbl_df", "tbl", "data.frame"))

I want to use indices because my future tasks will have dataframes with different column names and widths. My approach uses a function to determine if a column is odd/even, and then extract pairs of columns until I reach the last odd numbered column. Note that the function must account for required order of odd-even indices for each extraction (group name and corresponding score):

odd <- function(x) x%%2 != 0
outfile<-list()

moveit<-function(df){
  for (i in 1:dim(df)[2])    # define number of loops
    if (  i==dim(df)[2]-1  )  {break} # stop at least odd-numbered column
    if ( odd(i)==FALSE) {next}  # skip when i is not an odd numbered index 
  print(i)
  outfile[[i+1]]<-df[ ,c(i,i+1)]
}

result<-moveit(mydata)
str(result)

You can see the result is only the last key-value pair. Why? How can I adjust the function to extract all key-value pairs into one dataframe?

Upvotes: 1

Views: 174

Answers (2)

G. Grothendieck
G. Grothendieck

Reputation: 270248

1) reshape reshape can do that assuming that what you want is to reshape the data frame into long form. No packages are used.

nc <- ncol(mydata)
ig <- seq(1, nc, 2)  # indexes of key columns
reshape(as.data.frame(mydata), dir = "long", 
  varying = list(ig, -ig), v.names = c("key", "value"))

giving:

    time                          key    value id
1.1    1          Rugby for Chipmunks 5.000000  1
2.1    1          Rugby for Chipmunks 3.571429  2
3.1    1          Rugby for Chipmunks 5.000000  3
4.1    1            Chafing Explained 4.500000  4
1.2    2           Pylons for Priests 4.000000  1
2.2    2              Eating Creosote 4.000000  2
3.2    2              Eating Creosote 3.166667  3
4.2    2              Eating Creosote 2.166667  4
1.3    3                 Wow for YOU! 5.000000  1
2.3    3      Advanced Cats for Bears 3.000000  2
3.3    3              Blue Paint Only      NaN  3
4.3    3                 Mockingbirds 4.000000  4
1.4    4          How to Sell Pigeons 4.300000  1
2.4    4          How to Sell Pigeons 3.000000  2
3.4    4          How to Sell Pigoens 4.100000  3
4.4    4              Larger Boulders 3.400000  4
1.5    5      Making Money with Pears 5.000000  1
2.5    5      Making Money with Pears 3.000000  2
3.5    5                 Why Walnuts? 5.000000  3
4.5    5 Responding to Idiots Part II 4.166667  4

2) pivot_longer This can alternately be done with pivot_longer

library)(dplyr)
library(tidyr)

v.names <- c("key", "value")
mydata %>%
  setNames(outer(v.names, 1:(ncol(.)/2), paste)) %>%
  mutate(id = 1:n()) %>%
  pivot_longer(cols = -id, names_to = c(".value", "no"), names_sep = " ") %>%
  arrange(no, id)

Note that this is similar to the use of pivot_longer here: Pivot by group for unequal data size

Revised movit

Here is your code revised.

moveit <- function(df) {
  outfile <- list()
  for(i in seq_along(df)) if (odd(i)) outfile[[(i+1)/2]] <- df[c(i, i+1)]
  outfile
}

Upvotes: 3

akrun
akrun

Reputation: 887881

We can create a numeric index with gl and split the dataset into list of data.frame, rename the list elements with map and join it rowwise

library(dplyr)
library(purrr)
split.default(mydata, as.integer(gl(ncol(mydata), 2, ncol(mydata)))) %>% 
      map_dfr(~ .x %>% 
                  rename_all(~ c('group', 'value')))

The above can also be made into a No package zone

lst1 <-  split.default(mydata, as.integer(gl(ncol(mydata), 2, ncol(mydata)))) 
do.call(rbind, lapply(lst1, setNames, c("group", "value")))

In the OP's code, the 'outfile' list is initialized with length 0. Instead it can be

odd <- function(x) x%%2 != 0
outfile <- vector('list', ncol(mydata))

moveit<-function(df){
  for (i in seq_along(df)) {   
    if(odd(i)){  
      outfile[[i]]<-df[ ,c(i,i+1)]
    }
 }
 Filter(Negate(is.null), outfile)
}

result <- moveit(mydata)

Also, the main issue is that the 'outfile' is not returned at the end

odd <- function(x) x%%2 != 0
outfile<-list()
moveit<-function(df){
  for (i in 1:dim(df)[2]) {   # define number of loops
    if (  i==dim(df)[2]-1  )  {break} # stop at least odd-numbered column
    if ( odd(i)==FALSE) {next}  # skip when i is not an odd numbered index 
  print(i)
  outfile[[i+1]]<-df[ ,c(i,i+1)]
 }
 outfile
}

result<-moveit(mydata)

NOTE: No packages are used here as well

Upvotes: 1

Related Questions