0

I am very new to R and this is my first stack overflow question so I expect this may be a little rough. I have a data frame (from a .csv) in the following structure:

FeatureName     Uuid     Count 

ClickHeadline   ABC1     17 
ChangeSetting   ABC1     3  
ClickHeadline   CBA2     5 
ChangeSetting   CBA2     7 
SomethingElse   CBA2     5

I am trying to figure out how to make a new data frame in which the unique values of FeatureName, the factors ClickHeadline, ChangeSetting, SomethingElse are now variables summing over the Count for each Uuid. So the new data frame I want would be:

Uuid    ClickHeadline    ChangeSetting    SomethingElse
ABC1    17               3                0
CBA2    5                7                5

I feel like I should be able to do this over the aggregate function, but I can't figure out how to tell it to look sum over the counts by a variable. I know I'm in way over my head but can anybody help me figure this out?

steeles
  • 169
  • 1
  • 11
  • A popular set of answers for summing over factors is at https://stackoverflow.com/questions/3505701/grouping-functions-tapply-by-aggregate-and-the-apply-family – Henry May 16 '18 at 18:30

1 Answers1

1

There are many possibilities

If you require a sum you could also use the reshape2 package dcast function

df <- read.table(header=T, text='
                 FeatureName     Uuid     Count 

ClickHeadline   ABC1     17 
ChangeSetting   ABC1     3  
ClickHeadline   CBA2     5 
ChangeSetting   CBA2     7 
SomethingElse   CBA2     5
                 ')

library(reshape2)
dcast(df, Uuid ~ FeatureName, value.var="Count", sum)

  Uuid ChangeSetting ClickHeadline SomethingElse
1 ABC1             3            17             0
2 CBA2             7             5             5

If you dataset is limited to the scope you provided you just can use the base reshape function

out <- reshape(df, idvar="Uuid", timevar="FeatureName", v.names="Count", direction="wide")
out[is.na(out)] = 0
out
  Uuid Count.ClickHeadline Count.ChangeSetting Count.SomethingElse
1 ABC1                  17                   3                   0
3 CBA2                   5                   7                   5

Another base R alternative is xtabs without need for removing NA

xtabs(Count ~ Uuid+FeatureName, df)
      FeatureName
Uuid   ChangeSetting ClickHeadline SomethingElse
  ABC1             3            17             0
  CBA2             7             5             5

tidyr package solution with spread

library(tidyr)
spread(df, key=FeatureName, value=Count, fill=0)
  Uuid ChangeSetting ClickHeadline SomethingElse
1 ABC1             3            17             0
2 CBA2             7             5             5
cdeterman
  • 19,630
  • 7
  • 76
  • 100