Melting dataframe in R

Question

I have the following R dataframe :

foo <- data.frame("Department" = c('IT', 'IT', 'Sales'),
                  "Name.boy" = c('John', 'Mark', 'Louis'), 
                  "Age.boy" = c(21,23,44), 
                  "Name.girl" = c('Jane', 'Charlotte', 'Denise'), 
                  "Age.girl" = c(16,25,32))

which looks like the following :

    
Department Name.boy Age.boy Name.girl Age.girl
IT         John     21      Jane      16
IT         Mark     23      Charlotte 25
Sales      Louis    44      Denise    32

How do I 'melt' the dataframe, so that for a given Department, I have three columns : Name, Age, and Sex ?

Department Name       Age   Sex
IT         John       21    Boy
IT         Jane       16    Girl
IT         Mark       23    Boy
IT         Charlotte  25    Girl
Sales      Louis      44    Boy
Sales      Denise     32    Girl

score 3 · Answer 1 · answered Jun 23 '20 at 22:33

3

We can use pivot_longer from tidyr

library(tidyr)
pivot_longer(foo, cols = -Department, names_to = c(".value", "Sex"), 
   names_sep="\\.")
# A tibble: 6 x 4
#  Department Sex   Name        Age
#  <chr>      <chr> <chr>     <dbl>
#1 IT         boy   John         21
#2 IT         girl  Jane         16
#3 IT         boy   Mark         23
#4 IT         girl  Charlotte    25
#5 Sales      boy   Louis        44
#6 Sales      girl  Denise       32

answered Jun 23 '20 at 22:33

akrun

874,273
37
540
662

Great, I'll try this now, thank you very much. While we're on the matter, as you can see my dataframe is actually the result of a `merge()` between two `girl` and `boy` dataframes, with `Department` as join. Is there a way to merge the dataframes vertically from the beginning, taking `Department` as join column ? Thank you a lot for your time. – Louis GRIMALDI Jun 23 '20 at 22:54
1

@LouisGRIMALDI If your original dataframes each have columns Department, Sex, Name, Age, then they could be combined using `dplyr::bind_rows()` rather than `merge`. – neilfws Jun 23 '20 at 23:14

Edward · Answer 2 · 2020-06-24T00:33:52.537

2

Using reshape:

reshape(foo, direction="long", varying=2:5, tiemvar="Sex")

       Department  Sex      Name Age id
1.boy          IT  boy      John  21  1
2.boy          IT  boy      Mark  23  2
3.boy       Sales  boy     Louis  44  3
1.girl         IT girl      Jane  16  1
2.girl         IT girl Charlotte  25  2
3.girl      Sales girl    Denise  32  3

edited Jun 24 '20 at 00:33

answered Jun 24 '20 at 00:22

Edward

10,360
2
11
26

Melting dataframe in R

2 Answers2