StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
primarykey
Id
20410804
data
AcceptedAnswerId
0
AnswerCount
0
ClosedDate
CommentCount
0
CommunityOwnedDate
CreationDate
2013-12-05T21:11:24.030
FavoriteCount
0
LastActivityDate
2013-12-05T21:11:24.030
LastEditDate
LastEditorUserId
0
OwnerUserId
2985007
ParentId
20380028
PostTypeId
2
Score
0
ViewCount
0
LastEditorDisplayName
text
Body
Sorry about this "second answer", but you really had two questions... @Ananda's solution for reshaping your data is extremely elegant. This is just another way to think about it. If you transpose the input matrix you get a new matrix, where the first column is country, the second column is city, the third column is "type" (for lack of a better term), and the actual data is in the other columns (so, there is one additional column for every "time"). So a different approach is to transpose first and then melt the new matrix. This avoids creating all the concatenated column names and splitting them back later. The problem is that <code>melt.data.frame</code> is exceptionally inefficient with a very large number of columns (which you would have here). So doing it this way would bbe 10X slower than @Ananda's approach. A solution is to use <code>melt.array</code> (just call <code>melt(...)</code> with an array rather than a data frame). As shown below, this approach is ~20X faster, with larger datasets (yours was 11MB). <pre><code>library(reshape) # for melt(...) library(microbenchmark) # for microbenchmark(...) # this is just to model your situation with more realistic size # create a large data frame (250 columns of country, city, type; 1000 rows of time) df <- rep(c("USA","UK","FR","CHN","GER"),each=50) # time + 250 columns df <- rbind(df,rep(c(c("NY","SF","CHI","BOS","LA")),each=10)) df <- rbind(df,rep(c("pork","peas","nuts","fruit","other"))) df <- rbind(df,matrix(sample(1:1000,250*1000,replace=T),ncol=250)) df <- cbind(c("time","","", as.character(as.Date(1:1000,origin="2010-01-01"))),df) df <- data.frame(df) # big warning here about duplicated row names; not important # @Ananda'a approach: transform.orig <- function(df){ B <- df[-(1:3),] Bnames <- df[1:3,] names(B) <- apply(Bnames, 2, function(x) paste(x[x != ""], collapse = "_")) BL <- melt(B, id.vars="time") final <- cbind(BL[c("time", "value")], colsplit(BL$variable, "_", c("country", "state", "product"))) return(final) } # transpose approach: transform.new <- function(df) { zz <- t(df) times <- t(zz[1,4:ncol(zz)]) colnames(zz) <- c("country","city","type", times) data <- melt(zz[-1,-(1:3)],varnames=c("id","time")) final <- cbind(country=rep(zz[-1,1],each=ncol(zz)-3), city =rep(zz[-1,2],each=ncol(zz)-3), type =rep(zz[-1,3],each=ncol(zz)-3), data[,-1]) return(final) } # benchmark microbenchmark(transform.orig(df),transform.new(df), times=5, unit="s") Unit: seconds expr min lq median uq max neval transform.orig(df) 9.2511679 9.6986330 9.889457 10.1518191 10.3354328 5 transform.new(df) 0.4383197 0.4724145 0.474212 0.5815531 0.6886383 5 </code></pre>
Tags
Title
singulars
PostAcceptedAnswerId
1. This table or related slice is empty.
PostParentId
1. POReformatting an excel sheet in R
 singulars
 PostTypePostTypeId
 PTQuestion
PostTypePostTypeId
1. PTAnswer
UserLastEditorUserId
1. This table or related slice is empty.
UserOwnerUserId
1. USjlhoward
plurals
PostLinksPostIdRelatedPostId
1. This table or related slice is empty.
PostLinksRelatedPostIdPostId
1. This table or related slice is empty.
PostsAcceptedAnswerId
1. This table or related slice is empty.
PostsParentIdCreationDate
1. This table or related slice is empty.
VotesPostIdCreationDate
1. This table or related slice is empty.
CommentsPostId
1. This table or related slice is empty.

Querying!

Guidance

A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.