2

For a stat. analysis in R I need to modify my raw table with a numeric column “SumBirds” = 4, 7, 12, 20, etc (table1) to a new table2 , where each segment of a column “bird” has so “1” (one bird) and all others lines of the raw table are duplicated as many times, as value has parameter “SumBird”.

How can I do it in R or in EXCEL (where I prepare my data)?

Examples: Table1 (raw table)

Sumbird     Temp    OutHum
4           28.7    69
6           22.3    58

Table 2 (modificated table)

bird    Temp    OutHum
1       28.7    69
1       28.7    69
1       28.7    69
1       28.7    69
1       22.3    58
1       22.3    58
1       22.3    58
1       22.3    58
1       22.3    58
1       22.3    58
luchaninov
  • 6,792
  • 6
  • 60
  • 75
natalia
  • 145
  • 1
  • 6

3 Answers3

3

You could also use some wrapper in order to make it easier

library(splitstackshape)
cbind(bird = 1, expandRows(df, "Sumbird"))
#     bird Temp OutHum
# 1      1 28.7     69
# 1.1    1 28.7     69
# 1.2    1 28.7     69
# 1.3    1 28.7     69
# 2      1 22.3     58
# 2.1    1 22.3     58
# 2.2    1 22.3     58
# 2.3    1 22.3     58
# 2.4    1 22.3     58
# 2.5    1 22.3     58
David Arenburg
  • 91,361
  • 17
  • 137
  • 196
1

You can try rep

 res <- cbind(bird=1,raw[,-1][rep(1:nrow(raw), raw$Sumbird),])
 row.names(res) <- NULL
 res
 #   bird Temp OutHum
 #1     1 28.7     69
 #2     1 28.7     69
 #3     1 28.7     69
 #4     1 28.7     69
 #5     1 22.3     58
 #6     1 22.3     58
 #7     1 22.3     58
 #8     1 22.3     58
 #9     1 22.3     58
 #10    1 22.3     58

data

 raw<- structure(list(Sumbird = c(4L, 6L), Temp = c(28.7, 22.3),
 OutHum = c(69L, 58L)), .Names = c("Sumbird", "Temp", "OutHum"), class = 
"data.frame", row.names = c(NA, -2L))

Note: The structure(list(.. output is the one you get by using dput. For your dataset, you read it using read.csv. i.e

 raw <- read.csv("C:\pathtofile\\rawtable.csv",sep=";")
akrun
  • 874,273
  • 37
  • 540
  • 662
  • Thank you! Please, could you add in scripts theirs beginnings (I am new in R, and can’t understand for this short note where I need to put my raw table). At first I open it: raw<-read.csv("C:\\*** \\rawtable.csv",sep=";") It has columns like in a table1 : Sumbird, Temp, OutHum. And then? What shall I do for go to the final scripts that you given? – natalia Dec 17 '14 at 15:15
  • Yes, I just found it by myself! Thank you, problem is solved. – natalia Dec 17 '14 at 15:25
0

So in short (I used here the first answer, but the second answer also works)

For the raw table (in my example - table1):

df<-read.csv("C:\\***\\rawtable.csv",sep=";")
df
library(splitstackshape)
test<-cbind(birds = 1, expandRows(df, "Sumbirds"))
test

Then I copied the resulting table (test) in the R-window to a txt file (Notepad) and then open it in EXEL (by "fixed width" format) that open txt file in ordinary xls format. (Don't know other way to move the resulting table from R to EXEL , but maybe they exist).

But if the resulting table (test) is too big I use this:

test[c(1:5000),] - represent first 5000 lines of all columns of the "test" table, etc.
natalia
  • 145
  • 1
  • 6