CODEWITHSUNDEEP
rdataframe
user3664020
user3664020

Reputation: 3020

How to repeat rows of a data frame with a specific pattern?

I have a data frame like

col1    col2    col3    col4    col5
1        A       12      13      14
2        B       87      56      44
3        C       45      23      33
4        D       56      87      56

And a vector 

 c("E", "F", "G")

I have to get something like this:

col1    col2    col3    col4    col5
1        A       12      13      14
0        E       0       0       0
0        F       0       0       0
0        G       0       0       0
2        B       87      56      44
0        E       0       0       0
0        F       0       0       0
0        G       0       0       0
3        C       45      23      33
0        E       0       0       0
0        F       0       0       0
0        G       0       0       0
4        D       56      87      56
0        E       0       0       0
0        F       0       0       0
0        G       0       0       0

I can get this data frame using for loop but that would be tedious. Is there any neat and shorter way to achieve this?

Thanks in advance.

Upvotes: 1

Views: 98

Answers (4)

cdeterman
cdeterman

Reputation: 19950

You could create the structure of the table you want and then simply assign the rows from the original dataframe.

df <- read.table(header=T, text='
                 col1    col2    col3    col4    col5
1        A       12      13      14
2        B       87      56      44
3        C       45      23      33
4        D       56      87      56')

v1 <- c(levels(df$col2))
v2 <- c(LETTERS[5:7])
new_df <- do.call("rbind", sapply(1:length(v1), FUN=function(x) expand.grid(0,c(v1[x],v2),0,0,0), simplify=F))

new_df[seq(from=1,to=nrow(new_df), by=4),] <- df
colnames(new_df) <- colnames(df)

   col1 col2 col3 col4 col5
1     1    A   12   13   14
2     0    E    0    0    0
3     0    F    0    0    0
4     0    G    0    0    0
5     2    B   87   56   44
6     0    E    0    0    0
7     0    F    0    0    0
8     0    G    0    0    0
9     3    C   45   23   33
10    0    E    0    0    0
11    0    F    0    0    0
12    0    G    0    0    0
13    4    D   56   87   56
14    0    E    0    0    0
15    0    F    0    0    0
16    0    G    0    0    0

Upvotes: 0

David Arenburg
David Arenburg

Reputation: 92282

Here's a possible (vectorized) solution using row indexing. Assuming your data called df and your vector called V, could try

df2 <- df[rep(seq_len(nrow(df)), each = length(V) + 1), ]
df2[grep("[.]", row.names(df2)), ] <- 0
df2[grep("[.]", row.names(df2)), "col2"] <- V
# row.names(df2) <- seq_len(nrow(df2)) # Optional, if you don't like your row names
df2
#    col1 col2 col3 col4 col5
# 1     1    A   12   13   14
# 2     0    E    0    0    0
# 3     0    F    0    0    0
# 4     0    G    0    0    0
# 5     2    B   87   56   44
# 6     0    E    0    0    0
# 7     0    F    0    0    0
# 8     0    G    0    0    0
# 9     3    C   45   23   33
# 10    0    E    0    0    0
# 11    0    F    0    0    0
# 12    0    G    0    0    0
# 13    4    D   56   87   56
# 14    0    E    0    0    0
# 15    0    F    0    0    0
# 16    0    G    0    0    0

Upvotes: 2

user4113862
user4113862

Reputation:

You can try to generate a sequence using Map.

df <- data.frame(col1 = 1:4,
col2 = LETTERS[1:4],
col3 = c(12,87,45,56),
col4=c(13,56,23,87),
col5=c(14,44,33,56))

vec <- c("E","F","G")

df2 <- data.frame(col1 = 0, col2 = vec, col3=0, col4=0, col5=0)

#rbind
df3 <- rbind(df2, df)

#generate new sequence for rows
df4<-df3[unlist(Map(c, (nrow(df2)+1):nrow(df3), list(1:nrow(df2)))),]

Upvotes: 0

ilir
ilir

Reputation: 3224

Adding the new rows is trivial, but sorting them in the way you want is tricky. You can ignore sorting.vec if the final order is not important:

txt = "col1    col2    col3    col4    col5
1        A       12      13      14
2        B       87      56      44
3        C       45      23      33
4        D       56      87      56"

tmp <- read.table(text=txt, header=T, stringsAsFactors=F)
v2 <- c("E", "F", "G")

# add the mostly empty data
tmp2 <- data.frame(col1=0, col2=rep(v2, nrow(tmp)),
                   col3=0, col4=0, col5=0)

# encoding vector for sorting at the end (took some figuring out)
sorting.vec <- 1000* c(1:nrow(tmp), rep(1:nrow(tmp), each=length(v2))) +
               c(rep(0, nrow(tmp)), rep(1:length(v2), nrow(tmp)))

# stack and sort apropriately
final <- rbind(tmp, tmp2)[order(sorting.vec),]

Upvotes: 0

Related Questions