Loop to add columns from dynamically created dataframe in R

Question

I am new to R and I will try to be as clear as possible for my request.

I have a dataframe named data_count, which looks something like that:

A    B    C   D
1    2    3   4
5    6    5   4
3    2    1   2
3    4    5   6
5    4    3   2

First of all I want to add how many times a certain value appears in two consecutive rows, and I want this to be done column by column. To do that, I have thought of looping through data_count in order to create dataframes of 2 consecutive rows (let's call them data_dynamic), on which I simply perform a count function. So this is roughly what happens:

for (i in 1:(nrow(data_count))){
   limit <- i+1   # this is how I get the two rows
   data_dynamic <- data[i:limit,]  # this is how I get the 2-row dataframe

So far, all good. But now I want to count, within data_dynamic, how often the numbers 1,2,3,4,5 and 6 appear.

This is so I can write the following dataframe data_count2:

#1    #2    #3    #4    #5    #6
1     1     1     2     2     1
1     2     1     1     2     1
....

In which the first row tells you: "In the first two rows of data_count, the number 1 appears once, while the number 4 appears twice".

for (i in 1:(nrow(data_count))){
  limit <- i+1
  data_dynamic <- data[i:limit,]
    for (k in 1:6){
      count_example <- c(count_example,sum(data_dynamic == k))) 
      data_count2[,(k+1)] <- count_example
    }
}

count_example should be supposed to be a column of data_count2.

For some reasons, this does not work. Can anyone help me? I've been struggling on it for a while, any help would be very much appreciated, thanks!

table(unlist(data_count[1:2,])) will get you the tallies for the first group of rows; if applied over whole data frame should give you all the numbers you are looking for. — mysteRious
– mysteRious, Commented Apr 13, 2018 at 22:00
thanks, this is an interesting command I did not know. However, I have noticed that if a value is missing, table(unlist()) will not write "0", but it will just skip the column. This is an issue sadly — WhelanG
– WhelanG, Commented Apr 13, 2018 at 22:26
there's a way to get the zeroes... let me try to remember/figure it out. I've done it before. — mysteRious
– mysteRious, Commented Apr 13, 2018 at 22:28

mysteRious · Accepted Answer · 2018-04-13 23:15:50Z

2

> for (i in 1:(nrow(data_count)-1)) ( print(tabulate(unlist(data_count[i:(i+1),]))))
[1] 1 1 1 2 2 1
[1] 1 2 1 1 2 1
[1] 1 2 2 1 1 1
[1] 0 1 2 2 2 1

Or put it in a data frame:

> d <- data.frame()
> for (i in 1:(nrow(data_count)-1)) ( d <- rbind(d, 
    (tabulate(unlist(data_count[i:(i+1),])))) )
> names(d) <- c(1,2,3,4,5,6)
> d
  1 2 3 4 5 6
1 1 1 1 2 2 1
2 1 2 1 1 2 1
3 1 2 2 1 1 1
4 0 1 2 2 2 1

edited Apr 13, 2018 at 23:15

answered Apr 13, 2018 at 22:27

mysteRious

4,3322 gold badges20 silver badges39 bronze badges

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

Comments

Eric Leung · Accepted Answer · 2018-04-13 21:56:57Z

0

I think this may help. Good luck!

# Original data
data_count <- read.table(textConnection("
    A    B    C   D
    1    2    3   4
    5    6    5   4
    3    2    1   2
    3    4    5   6
    5    4    3   2"
    ), header = TRUE)

# Keep track of counts
counts <- data.frame(
    values = 1:6,
    counts = 0
)

# Loop through data frame
for (i in 1:(nrow(data_count) - 1)) {
    # Get two rows
    data_dynamic <- data_count[i:(i + 1), ]

    # Calculate difference and get boolean check
    bool_check <- data_dynamic[1, ] == data_dynamic[2, ]

    # Get values that are the same in consecutive row
    counted_consecutive <- data_dynamic[1, bool_check]

    # Check if you got any true values
    if (sum(bool_check) == 0) {
        next
    } else {
        increment <- counts[counted_consecutive, "counts"] + 1
        counts[counted_consecutive, "counts"] <- increment
    }
}
counts
#>   values counts
#> 1      1      0
#> 2      2      0
#> 3      3      1
#> 4      4      2
#> 5      5      0
#> 6      6      0

answered Apr 13, 2018 at 21:56

Eric Leung

2,6521 gold badge18 silver badges25 bronze badges

1 Comment

WhelanG Over a year ago

thanks, not exactly what I was looking for but it will help!

Collectives™ on Stack Overflow

Loop to add columns from dynamically created dataframe in R

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related