rdplyrtidyrsplitstackshape

Creating new column based on condition

I have subsetted the data so it is easier to demonstrate what I am attempting to do. I am trying to create a data frame with a new row for the value in the column "MaxRounds". At first MaxRounds was in a column like so:

   library(dplyr);library(tidyr);library(splitstackshape)

structure(list(power = c(0.800962297001584, 0.804719517260326, 
0.808410477932415, 0.812036218849852, 0.803164810470566, 0.815597767274311
), nights = c(20L, 20L, 20L, 20L, 19L, 20L), sites = c(78L, 79L, 
80L, 81L, 81L, 82L), NonRoundedMaxRounds = c(3, 3, 3, 3, 3.15789473684211, 
3), MaxRounds = c(3, 3, 3, 3, 3, 3)), row.names = c(NA, 6L), class = "data.frame")

I then created new rows that are dependent on the MaxRounds column = creating duplicate rows dependent on the number of MaxRounds. For example, if the MaxRounds are 2, then 1-2 rows are created, if the MaxRounds are 5 then 5 rows are created).

The code creates a unique ID row name: x, x.1, x.2, x.3 etc.

data = expandRows(data, "MaxRounds")

structure(list(power = c(0.800962297001584, 0.800962297001584, 
0.800962297001584, 0.804719517260326, 0.804719517260326, 0.804719517260326
), nights = c(20L, 20L, 20L, 20L, 20L, 20L), sites = c(78L, 78L, 
78L, 79L, 79L, 79L), NonRoundedMaxRounds = c(3, 3, 3, 3, 3, 3
)), row.names = c("1", "1.1", "1.2", "2", "2.1", "2.2"), class = "data.frame")

I then created a new column based on the row names:

data$RowID = rownames(data)

structure(list(power = c(0.800962297001584, 0.800962297001584, 
0.800962297001584, 0.804719517260326, 0.804719517260326, 0.804719517260326
), nights = c(20L, 20L, 20L, 20L, 20L, 20L), sites = c(78L, 78L, 
78L, 79L, 79L, 79L), NonRoundedMaxRounds = c(3, 3, 3, 3, 3, 3
), RowID = c("1", "1.1", "1.2", "2", "2.1", "2.2")), row.names = c("1", 
"1.1", "1.2", "2", "2.1", "2.2"), class = "data.frame")

Next I am attempting to group together all of the rows that have the same x value (despite the decimal point) and number them sequentially. For example:

I am attempting to group by the column "RowID" using:

data %>% group_by(RowID) %>% mutate(id = row_number())

But I get this error:

enter image description here

Solution

  • Creating unique Row ID's can be done by_group or independently with dplyr, here is an example using mtcars

    mtcars %>% group_by(cyl) %>% mutate(
        id = row_number()
)

    # Groups:   cyl [3]
    mpg   cyl  disp    hp  drat    wt  qsec    vs    am  gear  carb    id
  <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <int>
1  21       6   160   110  3.9   2.62  16.5     0     1     4     4     1
2  21       6   160   110  3.9   2.88  17.0     0     1     4     4     2
3  22.8     4   108    93  3.85  2.32  18.6     1     1     4     1     1
4  21.4     6   258   110  3.08  3.22  19.4     1     0     3     1     3
5  18.7     8   360   175  3.15  3.44  17.0     0     0     3     2     1
6  18.1     6   225   105  2.76  3.46  20.2     1     0     3     1     4

    And without grouping,

    mtcars %>% mutate(
        id = row_number()
) 

                       mpg cyl disp  hp drat    wt  qsec vs am gear carb id
Mazda RX4         21.0   6  160 110 3.90 2.620 16.46  0  1    4    4  1
Mazda RX4 Wag     21.0   6  160 110 3.90 2.875 17.02  0  1    4    4  2
Datsun 710        22.8   4  108  93 3.85 2.320 18.61  1  1    4    1  3
Hornet 4 Drive    21.4   6  258 110 3.08 3.215 19.44  1  0    3    1  4
Hornet Sportabout 18.7   8  360 175 3.15 3.440 17.02  0  0    3    2  5
Valiant           18.1   6  225 105 2.76 3.460 20.22  1  0    3    1  6

    row_number() numbers each rows sequentially, either by group or not. For example, the 4th row in the grouped example has id=3 as it is the 3rd row in the group of 6 cyl(inders).