iterating through data and creating new data frame

Question

I am working with a data frame that comes from the database in the following way:

username    elements
username1   """interfaces"".""dual()"""
username1   """interfaces"".""f_capitalaccrualcurrentyear"""
username2   """interfaces"".""dnow_completion"",""interfaces"".""dnow_s_daily_prod_ta"""
username2   """interfaces"".""dnow_completion"",""interfaces"".""dnow_s_daily_prod_ta"""
username2   """interfaces"".""dnow_completion"",""interfaces"".""dnow_s_daily_prod_ta"""
username4   """interfaces"".""dnow_s_downtime_stat_with_lat_long"""
username3   """interfaces"".""dnow_completion"",""interfaces"".""dnow_s_daily_prod_ta"""

So, two columns, a "username" and "elements". So there can be one element or several elements the user has used in one transaction. When multiple elements, they are separated with a comma in a transaction. I need to have the elements separated, one per row, but still tagged with the user name. At the end I'd like it to be like so:

username    elements
username1   """interfaces"".""dual()"""
username1   """interfaces"".""f_capitalaccrualcurrentyear"""
username2   """interfaces"".""dnow_completion""
username2   ""interfaces"".""dnow_s_daily_prod_ta"""
username2   """interfaces"".""dnow_completion""
username2   ""interfaces"".""dnow_s_daily_prod_ta"""
username2   """interfaces"".""dnow_completion""
username2   ""interfaces"".""dnow_s_daily_prod_ta"""
username4   """interfaces"".""dnow_s_downtime_stat_with_lat_long"""
username3   """interfaces"".""dnow_completion""
username3   ""interfaces"".""dnow_s_daily_prod_ta"""

I have been trying to iterate through the data frame, split the elements that have commas and then put them back together with the respective user name.

I have been trying the code below but it is super inefficient. I am new to "R" so my guess is that there has to be a more efficient way to do this.

interface.data <-data.frame(
    username = c(),
    elements = c()
)
for (row in 1:nrow(input)) { ##input is the frame that comes from the database
     myrowbrk<-input[row,"elements"]
     myrowelements<-chartr(",", "\n", myrowbrk)      
     user<-input[row,"username"]
     interface.newdata <- data.frame(
         username = user,
         elements = c(myrowelements)         
     )
     interface.final<- rbind(interface.data,interface.newdata )
}

output<-interface.final

Try tidyr::separate_rows(input, elements, sep = ",") ?

Ronak Shah
– Ronak Shah

2020-01-03 00:14:26 +00:00
Commented Jan 3, 2020 at 0:14 — Ronak Shah
– Ronak Shah, Commented Jan 3, 2020 at 0:14
Awesome! This solved it. Thank you!

Luis Garcia
– Luis Garcia

2020-01-03 13:39:43 +00:00
Commented Jan 3, 2020 at 13:39 — Luis Garcia
– Luis Garcia, Commented Jan 3, 2020 at 13:39

Jonathan V. Solórzano · Accepted Answer · 2020-01-03 01:49:51Z

1

You could use the tidyrpackage to do that. My solution uses two steps to obtain the data in the desired format: 1) separate the elements column using the comma character and 2) changing the format from wide to long.

library(tidyr)

#Separate the 'elements' column from your 'df' data frame using the comma character
#Set the new variable names as a sequence of 1 to the max number of expected columns
df2 <- separate(data = df, 
                   col = elements, 
                   into = as.character(seq(1,2,1)),
                   sep = ",")
#This code gives a warning because not every row has a string with a comma. 
#Empty entries are filled with NA

#Then change from wide to long format, dropping NA entries
#Drop the column that indicates the name of the column from which the elements entry was obtained (i.e., 1 or 2)
df2 <- df2 %>%
  pivot_longer(cols = "1":"2",
               values_to = "elements",
               values_drop_na = TRUE) %>%
  select(-name)

answered Jan 3, 2020 at 1:49

Jonathan V. Solórzano

5,78516 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Luis Garcia Over a year ago

Thank you for your response! Much appreciated!

Collectives™ on Stack Overflow

iterating through data and creating new data frame

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related