-4

I have a pivot table with 4 rows (Name, Grade, Subject, Marks). have a unique combination, but marks can have different values (based on mid term marks, final marks, etc.). While making pivot table, I get records in separate rows like , , and so on. I want to aggregate marks at last level, and make output as or or . Last level should take care of aggregation of marks based on previous levels. Is there a way of doing this? attaching an example here, where I want to aggregate marks at last level

V Gupta
  • 23
  • 1
  • 1
  • 6
  • 1
    Unclear, please show what your table looks like. – Spacedman Jun 15 '17 at 06:48
  • 1
    Please read the info about [how to ask a good question](http://stackoverflow.com/help/how-to-ask) and how to give a [reproducible example](http://stackoverflow.com/questions/5963269). This will make it much easier for others to help you. – zx8754 Jun 15 '17 at 06:50
  • If you are using an R library that makes such a display, then you should name it and produce code. If this is just an Excel question, you should remove the [r] tag, because there is a fair chance that you are just annoying R users with it. – IRTFM Jun 15 '17 at 18:33
  • @V_Gupta: Please respond to the comments and answers. – IRTFM Jun 17 '17 at 03:58

2 Answers2

2

The pivottabler package can produce the table illustrated in the question. (I'm the package author).

Sample Data

Name = c("Rahul", "Rahul", "Rahul", "Rahul", "Rahul", "Rahul", "Ram", "Ram", "Ram", "Ram")
Grade = c(10, 10, 11, 11, 11, 11, 10, 10, 11, 11)
Subject = c("English", "Hindi", "English", "Hindi", "Maths", "Science", "English", "Hindi", "English", "Hindi")
Marks = c(90, 91, 72, 65, 90, 95, 90, 80, 83, 81)
Results = data.frame(Name, Grade, Subject, Marks)

Listing each result separately

library(pivottabler)
pt <- PivotTable$new()
pt$addData(Results)
pt$addRowDataGroups("Name")
pt$addRowDataGroups("Grade", addTotal=FALSE)
pt$addRowDataGroups("Subject", addTotal=FALSE)
pt$addRowDataGroups("Marks", addTotal=FALSE)
pt$defineCalculation(calculationName="Totals", summariseExpression="n()")
pt # to output as plain text to console
pt$renderPivot() # to output as HTML table

enter image description here

Showing the mean of results

library(pivottabler)
pt <- PivotTable$new()
pt$addData(Results)
pt$addRowDataGroups("Name")
pt$addRowDataGroups("Grade", addTotal=FALSE)
pt$addRowDataGroups("Subject", addTotal=FALSE)
pt$defineCalculation(calculationName="Mean", summariseExpression="mean(Marks)")
pt$defineCalculation(calculationName="Totals", summariseExpression="n()")
pt # to output as plain text to console
pt$renderPivot() # to output as HTML table

enter image description here

cbailiss
  • 1,304
  • 11
  • 21
  • Doesn't look exactly like the image but close. Perhaps embedded in some sort of web-targeted display panel? – IRTFM Jun 15 '17 at 18:38
  • I think the picture in the original post is from the rpivotTable package (or the javascript equivalent) where pivot tables are created by drag/drop, e.g. see [here](https://cran.r-project.org/web/packages/rpivotTable/vignettes/rpivotTableIntroduction.html). The pictures in my answer are the HTML output produced by the pivottabler package, as displayed in the "Viewer" pane in R Studio, though the HTML can be exported, either just using R Studio (e.g. clicking on the "Show In New Window" button on the "Viewer" pane) or by using pt$saveHtml(). Hope that clarifies. Let me know if not – cbailiss Jun 16 '17 at 19:14
  • PS. I interpreted the original question to mean "How can I produce data output that looks like this image?" so I was aiming for matching the structure and numbers rather than styling/formatting. – cbailiss Jun 16 '17 at 19:18
1

You can use table

table(df$Name, df$Grade, df$Subject, df$Marks)

where df is your data frame

Ajay Ohri
  • 3,382
  • 3
  • 30
  • 60