reshape a data frame

6 messages · javascriptart25, Jonathan, John Kane +2 more

Original

1

6

Jonathan

Wed, Jun 3, 2015 2:44 PM #

Hello,

I would like to ask for some advice in reformatting a data frame such as
the following one:


gIN <- c("A_1","A_2","A_3","A_4","B_1","B_2")
bc1 <- c(1219.79, 1486.84, 1255.80, 941.87, 588.19, 304.02)
bc2 <- c(319.79, 186.84, 125.80, 94.87, 1008.19, 314.02)
group <- c("A","A","A","A","B","B")

ex <- data.frame("gIN" = gIN, "bc1" = bc1, "bc2"=bc2, "group" = group)

gIN     bc1     bc2 group
1 A_1 1219.79  319.79     A
2 A_2 1486.84  186.84     A
3 A_3 1255.80  125.80     A
4 A_4  941.87   94.87     A
5 B_1  588.19 1008.19     B
6 B_2  304.02  314.02     B

I would like to reshape this data frame where all the columns that have
bc1, bc2,...etc are merged into a single column (call it bcX or something)
and the other variables are kept apart, the example solution follows:

gIN     bcX     group
1 A_1 1219.79       A
2 A_2 1486.84       A
3 A_3 1255.80       A
4 A_4  941.87        A
5 B_1  588.19      B
6 B_2  304.02       B
7 A_1 319.79       A
8 A_2 186.84       A
9 A_3 125.80       A
10 A_4 94.87       A
11 B_1 1008.19   B
12 B_2 314.02     B

Does anyone know of a package, and/or command to accomplish this?

Thank you

javascriptart25

Wed, Jun 3, 2015 3:02 PM #

You can change ex <- data.frame("gIN" = gIN, "bc1" = bc1, "bc2"=bc2,
"group" = group)

to

ex <- data.frame("gIN" = c(gIN,gIN), "bcX" = c(bc1,bc2), "group" =
c(group,group))


On Wed, Jun 3, 2015 at 2:27 PM, hedelhusk [via R] <

ml-node+s789695n4708145h17 at n4.nabble.com> wrote:

______________________________________________
[hidden email] <http:///user/SendEmail.jtp?type=node&node=4708145&i=0>
mailing list -- To UNSUBSCRIBE and more, see
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.


------------------------------
 If you reply to this email, your message will be added to the discussion
below:
http://r.789695.n4.nabble.com/reshape-a-data-frame-tp4708145.html
 To start a new topic under R help, email
ml-node+s789695n789696h98 at n4.nabble.com
To unsubscribe from R, click here
<http://r.789695.n4.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_code&node=789695&code=amF2YXNjcmlwdGFydDI1QGdtYWlsLmNvbXw3ODk2OTV8NTgxOTIwNTYy>
.
NAML
<http://r.789695.n4.nabble.com/template/NamlServlet.jtp?macro=macro_viewer&id=instant_html%21nabble%3Aemail.naml&base=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespace&breadcrumbs=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml>

--
View this message in context: http://r.789695.n4.nabble.com/reshape-a-data-frame-tp4708145p4708146.html
Sent from the R help mailing list archive at Nabble.com.

Jonathan

Wed, Jun 3, 2015 3:25 PM #

I found the gather function from the tidyr package, which worked nicely:

gather(ex,bcX,value, bc1:bc2)
   gIN group bcX value
1  A_1     A     bc1  1219.79
2  A_2     A     bc1  1486.84
3  A_3     A     bc1  1255.80
4  A_4     A     bc1   941.87
5  B_1     B     bc1   588.19
6  B_2     B     bc1   304.02
7  A_1     A     bc2   319.79
8  A_2     A     bc2   186.84
9  A_3     A     bc2   125.80
10 A_4     A     bc2    94.87
11 B_1     B     bc2  1008.19
12 B_2     B     bc2   314.02

Thanks.

On Wed, Jun 3, 2015 at 5:44 PM, Jon BR <jonsleepy at gmail.com> wrote:

John Kane

Wed, Jun 3, 2015 4:21 PM #

And I think this will do it too.

library(reshape2)
 
melt(ex, id.vars= c("gIN", "group"), 
       variable.name = "bc",
       value.name = "value", 
       na.rm = FALSE) 

John Kane
Kingston ON Canada

-----Original Message-----
From: jonsleepy at gmail.com
Sent: Wed, 3 Jun 2015 18:25:02 -0400
To: r-help at r-project.org
Subject: Re: [R] reshape a data frame

I found the gather function from the tidyr package, which worked nicely:

gather(ex,bcX,value, bc1:bc2)
   gIN group bcX value
1  A_1     A     bc1  1219.79
2  A_2     A     bc1  1486.84
3  A_3     A     bc1  1255.80
4  A_4     A     bc1   941.87
5  B_1     B     bc1   588.19
6  B_2     B     bc1   304.02
7  A_1     A     bc2   319.79
8  A_2     A     bc2   186.84
9  A_3     A     bc2   125.80
10 A_4     A     bc2    94.87
11 B_1     B     bc2  1008.19
12 B_2     B     bc2   314.02

Thanks.





On Wed, Jun 3, 2015 at 5:44 PM, Jon BR <jonsleepy at gmail.com> wrote:

Hello,

I would like to ask for some advice in reformatting a data frame such as
the following one:


gIN <- c("A_1","A_2","A_3","A_4","B_1","B_2")
bc1 <- c(1219.79, 1486.84, 1255.80, 941.87, 588.19, 304.02)
bc2 <- c(319.79, 186.84, 125.80, 94.87, 1008.19, 314.02)
group <- c("A","A","A","A","B","B")

ex <- data.frame("gIN" = gIN, "bc1" = bc1, "bc2"=bc2, "group" = group)

ex

  gIN     bc1     bc2 group
1 A_1 1219.79  319.79     A
2 A_2 1486.84  186.84     A
3 A_3 1255.80  125.80     A
4 A_4  941.87   94.87     A
5 B_1  588.19 1008.19     B
6 B_2  304.02  314.02     B

I would like to reshape this data frame where all the columns that have
bc1, bc2,...etc are merged into a single column (call it bcX or
something)
and the other variables are kept apart, the example solution follows:

ex_reshaped

  gIN     bcX     group
1 A_1 1219.79       A
2 A_2 1486.84       A
3 A_3 1255.80       A
4 A_4  941.87        A
5 B_1  588.19      B
6 B_2  304.02       B
7 A_1 319.79       A
8 A_2 186.84       A
9 A_3 125.80       A
10 A_4 94.87       A
11 B_1 1008.19   B
12 B_2 314.02     B

Does anyone know of a package, and/or command to accomplish this?

Thank you

	[[alternative HTML version deleted]]

______________________________________________
R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see
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.

____________________________________________________________
Can't remember your password? Do you need a strong and secure password?
Use Password manager! It stores your passwords & protects your account.

Bert Gunter

Thu, Jun 4, 2015 6:57 AM #

Yes. This is basic stuff, and it seems unnecessary to run to packages for
it, Knowledge of base R should suffice. It would appear that the OP would
benefit by going through an R tutorial or two.

Slightly more economical and more general -- and trickier -- than explicit
concatenation, which could get to be a drag with a lot of columns, is this:

gIN group     bcx
bc11 A_1     A 1219.79
bc12 A_2     A 1486.84
bc13 A_3     A 1255.80
bc14 A_4     A  941.87
bc15 B_1     B  588.19
bc16 B_2     B  304.02
bc21 A_1     A  319.79
bc22 A_2     A  186.84
bc23 A_3     A  125.80
bc24 A_4     A   94.87
bc25 B_1     B 1008.19
bc26 B_2     B  314.02

Cheers,
Bert

Bert Gunter

"Data is not information. Information is not knowledge. And knowledge is
certainly not wisdom."
   -- Clifford Stoll

On Wed, Jun 3, 2015 at 3:02 PM, javascriptart25 <javascriptart25 at gmail.com>
wrote:

______________________________________________
[hidden email] <http:///user/SendEmail.jtp?type=node&node=4708145&i=0>
mailing list -- To UNSUBSCRIBE and more, see
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.


------------------------------
 If you reply to this email, your message will be added to the discussion
below:
http://r.789695.n4.nabble.com/reshape-a-data-frame-tp4708145.html
 To start a new topic under R help, email
ml-node+s789695n789696h98 at n4.nabble.com
To unsubscribe from R, click here
<

______________________________________________
R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see
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.

David L Carlson

Thu, Jun 4, 2015 8:42 AM #

Here are two ways using stack() and reshape() from base R. The "col" variable indicates which column the bcX value came from. Both are easy to scale up to multiple columns:

gIn     bcX col group
1  A_1 1219.79 bc1     A
2  A_2 1486.84 bc1     A
3  A_3 1255.80 bc1     A
4  A_4  941.87 bc1     A
5  B_1  588.19 bc1     B
6  B_2  304.02 bc1     B
7  A_1  319.79 bc2     A
8  A_2  186.84 bc2     A
9  A_3  125.80 bc2     A
10 A_4   94.87 bc2     A
11 B_1 1008.19 bc2     B
12 B_2  314.02 bc2     B

+ direction="long")

gIN     bcX col group
1  A_1 1219.79   1     A
2  A_2 1486.84   1     A
3  A_3 1255.80   1     A
4  A_4  941.87   1     A
5  B_1  588.19   1     B
6  B_2  304.02   1     B
7  A_1  319.79   2     A
8  A_2  186.84   2     A
9  A_3  125.80   2     A
10 A_4   94.87   2     A
11 B_1 1008.19   2     B
12 B_2  314.02   2     B

To get bc1, bc2 instead of 1, 2 in the col field:

+	times=colnames(ex)[2:3], direction="long")

-------------------------------------
David L Carlson
Department of Anthropology
Texas A&M University
College Station, TX 77840-4352


-----Original Message-----
From: R-help [mailto:r-help-bounces at r-project.org] On Behalf Of Bert Gunter
Sent: Thursday, June 4, 2015 8:58 AM
To: javascriptart25
Cc: r-help at r-project.org
Subject: Re: [R] reshape a data frame

Yes. This is basic stuff, and it seems unnecessary to run to packages for
it, Knowledge of base R should suffice. It would appear that the OP would
benefit by going through an R tutorial or two.

Slightly more economical and more general -- and trickier -- than explicit
concatenation, which could get to be a drag with a lot of columns, is this:

gIN group     bcx
bc11 A_1     A 1219.79
bc12 A_2     A 1486.84
bc13 A_3     A 1255.80
bc14 A_4     A  941.87
bc15 B_1     B  588.19
bc16 B_2     B  304.02
bc21 A_1     A  319.79
bc22 A_2     A  186.84
bc23 A_3     A  125.80
bc24 A_4     A   94.87
bc25 B_1     B 1008.19
bc26 B_2     B  314.02

Cheers,
Bert

Bert Gunter

"Data is not information. Information is not knowledge. And knowledge is
certainly not wisdom."
   -- Clifford Stoll

On Wed, Jun 3, 2015 at 3:02 PM, javascriptart25 <javascriptart25 at gmail.com>
wrote:

______________________________________________
[hidden email] <http:///user/SendEmail.jtp?type=node&node=4708145&i=0>
mailing list -- To UNSUBSCRIBE and more, see
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.


------------------------------
 If you reply to this email, your message will be added to the discussion
below:
http://r.789695.n4.nabble.com/reshape-a-data-frame-tp4708145.html
 To start a new topic under R help, email
ml-node+s789695n789696h98 at n4.nabble.com
To unsubscribe from R, click here
<

______________________________________________
R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see
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.

______________________________________________
R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see
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.