Skip to content

Pivot Table "like" structure

4 messages · Bhupendrasinh Thakre, arun, John Kane +1 more

arun
# 
Hi,

Not very clear whether this is you want.
#dat1 -data
aggregate(cbind(dat1$State,dat1$Coutry,dat1$City),list(dat1$Char1,dat1$Char2,dat1$Char3),length)
#or
?ddply(dat1,.(Char1,Char2,Char3),colwise(length,c("State","Coutry","City")))
#? Char1 Char2? Char3 State Coutry City
#1???? A? ABCD? ASDFG???? 1????? 1??? 1
#2???? A? ABCD? DDDDD???? 1????? 1??? 1
#3???? B? EFGH? ASDFG???? 1????? 1??? 1
#4???? B? GGGG? ASDFG???? 1????? 1??? 1
#5???? C? EFGH EEEEEE???? 1????? 1??? 1
#6???? C? GGGG? DDDDD???? 1????? 1??? 1
#7???? D? FGHJ? ASDFG???? 1????? 1??? 1
#8???? M? EFGH EEEEEE???? 1????? 1??? 1
A.K.




----- Original Message -----
From: Bhupendrasinh Thakre <vickythakre at gmail.com>
To: "r-help at r-project.org help" <r-help at r-project.org>
Cc: 
Sent: Saturday, October 13, 2012 8:38 PM
Subject: [R] Pivot Table "like" structure

HI Team,

I am currently working on problem and stumped on "for" loop.

Data:

structure(list(Coutry = structure(c(3L, 3L, 3L, 3L, 2L, 2L, 1L, 
1L), .Label = c("J", "M", "U"), class = "factor"), State = structure(c(1L, 
1L, 4L, 2L, 5L, 5L, 3L, 6L), .Label = c("A", "C", "K", "O", "S", 
"T"), class = "factor"), City = structure(c(1L, 8L, 7L, 2L, 3L, 
6L, 5L, 4L), .Label = c("BEN", "HRD", "JKL", "KK", "KL", "KMM", 
"OKC", "TYU"), class = "factor"), Char1 = structure(c(1L, 2L, 
1L, 3L, 4L, 2L, 3L, 5L), .Label = c("A", "B", "C", "D", "M"), class = "factor"), 
? ? Char2 = structure(c(1L, 2L, 1L, 2L, 3L, 4L, 4L, 2L), .Label = c("ABCD", 
? ? "EFGH", "FGHJ", "GGGG"), class = "factor"), Char3 = structure(c(1L, 
? ? 1L, 2L, 3L, 1L, 1L, 2L, 3L), .Label = c("ASDFG", "DDDDD", 
? ? "EEEEEE"), class = "factor")), .Names = c("Coutry", "State", 
"City", "Char1", "Char2", "Char3"), row.names = c(NA, -8L), class = "data.frame")

Question:

I am trying to create a pivot table which will count the occurrences of Char1 : Char4
from the columns Coutry, State, City. I am not sure to use all the four columns and get something like

structure(list(Group.1 = structure(1:4, .Label = c("ABCD", "EFGH", 
"FGHJ", "GGGG"), class = "factor"), x = c(2L, 3L, 1L, 2L)), .Names = c("Group.1", 
"x"), row.names = c(NA, -4L), class = "data.frame")

Code which I tried to use with not best results:

aggregate(State, list(Char2), FUN="count")


Best Regards,


Bhupendrasinh Thakre





??? [[alternative HTML version deleted]]

______________________________________________
R-help at r-project.org mailing list
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
2 days later
John Kane
# 
Perhaps this but your results example did not include Char1.
ibrary(reshape2)
md  <-  structure(list(Coutry = structure(c(3L, 3L, 3L, 3L, 2L, 2L, 1L,
1L), .Label = c("J", "M", "U"), class = "factor"), State = structure(c(1L,
1L, 4L, 2L, 5L, 5L, 3L, 6L), .Label = c("A", "C", "K", "O", "S",
"T"), class = "factor"), City = structure(c(1L, 8L, 7L, 2L, 3L,
6L, 5L, 4L), .Label = c("BEN", "HRD", "JKL", "KK", "KL", "KMM",
"OKC", "TYU"), class = "factor"), Char1 = structure(c(1L, 2L,
1L, 3L, 4L, 2L, 3L, 5L), .Label = c("A", "B", "C", "D", "M"), class = "factor"),
    Char2 = structure(c(1L, 2L, 1L, 2L, 3L, 4L, 4L, 2L), .Label = c("ABCD",
    "EFGH", "FGHJ", "GGGG"), class = "factor"), Char3 = structure(c(1L,
    1L, 2L, 3L, 1L, 1L, 2L, 3L), .Label = c("ASDFG", "DDDDD",
    "EEEEEE"), class = "factor")), .Names = c("Coutry", "State",
"City", "Char1", "Char2", "Char3"), row.names = c(NA, -8L), class = "data.frame")

head(md)
str(md)

md1  <-  melt(md, id = c("Coutry", "State", "City"))

apply(md1, 2, count)


John Kane
Kingston ON Canada
____________________________________________________________
GET FREE SMILEYS FOR YOUR IM & EMAIL - Learn more at http://www.inbox.com/smileys
Works with AIM?, MSN? Messenger, Yahoo!? Messenger, ICQ?, Google Talk? and most webmails
David Winsemius
#
On Oct 13, 2012, at 5:38 PM, Bhupendrasinh Thakre wrote:

            

      
      
      
    

        
You are apparently using attach() on your dataframe. That will often  
create confusion. Better to use with():

?with

For your problem without `attach` these are available:

 > table(dat$Char2)

ABCD EFGH FGHJ GGGG
    2    3    1    2


 > aggregate(dat$Char2, dat['Char2'], length)
   Char2 x
1  ABCD 2
2  EFGH 3
3  FGHJ 1
4  GGGG 2

With attach() in effect for your dataframe this would have worked as  
well:

 > aggregate(Char2, list(Char2), length)
   Group.1 x
1    ABCD 2
2    EFGH 3
3    FGHJ 1
4    GGGG 2

'count' is not a base R function, although it may be available in some  
packages. If you have other packages you are loading, you should name  
them.

If you want to get tabulations of all the columns that have "Char" in  
their names

 > sapply(dat[ grep("Char", names(dat)) ], table)
$Char1

A B C D M
2 2 2 1 1

$Char2

ABCD EFGH FGHJ GGGG
    2    3    1    2

$Char3

  ASDFG  DDDDD EEEEEE
      4      2      2