CODEWITHSUNDEEP
rstatistics
Carol.Kar
Carol.Kar

Reputation: 5355

Subsetting and counting values in columns in a data.frame

I have a data.frame with length 100000. Now I would like to count for different data.frame lengths(levels like 0.01 until 0.99) the positive and the negative values in this subset.

> dput(sumDF[1:100])
structure(c(3000, 2000, 5000, 4000, 1000, 4000, 0, 3000, 4000, 
2000, 2000, 3000, 1000, -3000, 2000, 0, 4000, 1000, 1000, 2000, 
2000, 2000, 2000, 1000, 3000, 1000, 4000, 3000, 2000, 3000, 1000, 
1000, 4000, 2000, 0, 1000, 2000, 5000, 3000, 3000, 0, 2000, 2000, 
3000, 1000, -1000, 2000, 1000, 2000, 3000, 2000, 3000, 2000, 
2000, 2000, 2000, 3000, 3000, 3000, 2000, 3000, 3000, 1000, 3000, 
1000, 2000, 1000, -1000, 0, 2000, 2000, 3000, 0, 3000, 2000, 
2000, 5000, 3000, 2000, 1000, 3000, 3000, 4000, 1000, 2000, 2000, 
3000, 0, 3000, 1000, 0, 4000, 4000, 2000, 3000, 0, 2000, 4000, 
0, 0), .Names = c("modelOutcome1", "modelOutcome2", "modelOutcome3", 
"modelOutcome4", "modelOutcome5", "modelOutcome6", "modelOutcome7", 
"modelOutcome8", "modelOutcome9", "modelOutcome10", "modelOutcome11", 
"modelOutcome12", "modelOutcome13", "modelOutcome14", "modelOutcome15", 
"modelOutcome16", "modelOutcome17", "modelOutcome18", "modelOutcome19", 
"modelOutcome20", "modelOutcome21", "modelOutcome22", "modelOutcome23", 
"modelOutcome24", "modelOutcome25", "modelOutcome26", "modelOutcome27", 
"modelOutcome28", "modelOutcome29", "modelOutcome30", "modelOutcome31", 
"modelOutcome32", "modelOutcome33", "modelOutcome34", "modelOutcome35", 
"modelOutcome36", "modelOutcome37", "modelOutcome38", "modelOutcome39", 
"modelOutcome40", "modelOutcome41", "modelOutcome42", "modelOutcome43", 
"modelOutcome44", "modelOutcome45", "modelOutcome46", "modelOutcome47", 
"modelOutcome48", "modelOutcome49", "modelOutcome50", "modelOutcome51", 
"modelOutcome52", "modelOutcome53", "modelOutcome54", "modelOutcome55", 
"modelOutcome56", "modelOutcome57", "modelOutcome58", "modelOutcome59", 
"modelOutcome60", "modelOutcome61", "modelOutcome62", "modelOutcome63", 
"modelOutcome64", "modelOutcome65", "modelOutcome66", "modelOutcome67", 
"modelOutcome68", "modelOutcome69", "modelOutcome70", "modelOutcome71", 
"modelOutcome72", "modelOutcome73", "modelOutcome74", "modelOutcome75", 
"modelOutcome76", "modelOutcome77", "modelOutcome78", "modelOutcome79", 
"modelOutcome80", "modelOutcome81", "modelOutcome82", "modelOutcome83", 
"modelOutcome84", "modelOutcome85", "modelOutcome86", "modelOutcome87", 
"modelOutcome88", "modelOutcome89", "modelOutcome90", "modelOutcome91", 
"modelOutcome92", "modelOutcome93", "modelOutcome94", "modelOutcome95", 
"modelOutcome96", "modelOutcome97", "modelOutcome98", "modelOutcome99", 
"modelOutcome100"))
> levels <- c(0.01, 0.05, 0.1, 0.2, 0.3, 0.4, 0.5, 0.6, 0.7, 0.8, 0.9, 0.95, 0.99)
> levelLength <- length(sumDF) * levels
> levelLength
 [1]  1000  5000 10000 20000 30000 40000 50000 60000 70000 80000 90000 95000 99000

My problem is that I get "how long the data.frame" should be, but I do not get the count of the "winners" and the "losers" in the data.frame. Hence, the values of the 1 dimensional data.frame, which are greater than 0, winners, or smaller or equal then 0, losers.

To show this as an example, my data.frame has length 100000. At the 1% level it's length is only 1000. From this 1000 elements, as an example, are 800 above 0 and 200 below or equal to 0. How to get the 800 and the 200?

I tried the following:

countWin <- length(sumDF[1:levelLength > 0])
Warning message:
In 1:levelLength : numerical expression has 13 elements: only the first used

Any suggestions, how to get from my vectors only a certain count of elements?

I appreciate your replies.

UPDATE

Example:

My data.frame sumDF looks like that:

> sumDF[1:3]
modelOutcome1 modelOutcome2 modelOutcome3 
         3000          2000          5000

My data.frame sumDF has the length of 100000

I want to subset my data.frame sumDF with the following level lengths.

> levelLength
 [1]  1000  5000 10000 20000 30000 40000 50000 60000 70000 80000 90000 95000 99000

So for levelLength 1000 I want to subset sumDF from 0 to 1000.

Furthermore, in this subset I want to count all vals >0, my winners and all which are <=0, my losers.

My final data.frame should look like that:

"levels" "winners" "losers"
0.01         900      100
0.05         2400     2600
0.10         6000     4000
0.20          .         .
0.30          .         .
0.40         
0.50         
0.60         
0.70         
0.80         
0.90         
0.95         
0.99

Upvotes: 0

Views: 88

Answers (1)

akrun
akrun

Reputation: 887511

The dput output is a vector. To get the sum of values that are less than 0,

  sum(sumDF<0)
  #[1] 3

We can also use table to get the frequency of losers and winners

  table(sumDF <0)
  #FALSE  TRUE 
  # 97     3

If we have a data.frame or matrix

  colSums(sumDF <0)

Not sure I understand the recent edit, perhaps we get the frequency of 'sumDF' after cutting the object into different bins. Using cut, we can get those groups by specifying the breaks.

   levellength <-  c(1, 5, seq(10, 90, by=10), 95, 99)
   tbl <- table(cut(sumDF, breaks=levellength), sumDF)

Suppose, if we need to get the cumulative sum for each group, use cumsum after looping through the columns of 'tbl' with apply.

   tbl1 <- apply(tbl, 2, cumsum)

The labels (rownames) can be changed by using sub to match the numbers that follow the parentheses ((), and replace it with 1.

   rownames(tbl1) <- sub('(?<=\\()\\d+', '1', rownames(tbl1), perl=TRUE)
   tbl1
   #    sumDF
   #       -3000 -1000 0 1000 2000 3000 4000 5000
   #(1,5]      0     0 0    0    0    0    0    0
   #(1,10]     0     0 0    0    0    0    0    0
   #(1,20]     0     0 0    0    0    0    0    0
   #(1,30]     0     0 0    0    0    0    0    0
   #(1,40]     0     0 0    0    0    0    0    0
   #(1,50]     0     0 0    0    0    0    0    0
   #(1,60]     0     0 0    0    0    0    0    0
   #(1,70]     0     0 0    0    0    0    0    0
   #(1,80]     0     0 0    0    0    0    0    0
   #(1,90]     0     0 0    0    0    0    0    0
   #(1,95]     0     0 0    0    0    0    0    0
   #(1,99]     0     0 0    0    0    0    0    0

NOTE: The frequencies are all 0 based on the dput example.

We could also change the labels within the cut itself by making use of labels argument. We create a custom label ('lvls') and use that in the cut. Other than that the code below is similar to above.

  lvls <- paste0('(', '1,', c(5,seq(10,90, by=10), 95, 99), ']')
  tbl <- table(sumDF, cut(sumDF, breaks=levellength, labels=lvls))
  apply(tbl, 1, cumsum)

Upvotes: 1

Related Questions