R Data Frame, combine columns by name and create new column

Question

My issue is that data is stored in the column names. NAME_X_Y_OTHER. I need to combine multiple columns into one, but I need to keep the X and Y values.

The original data would look like this.

my_df <- data.frame(MISC=c("a","a","b"),
                NAME_10_03_OTHER=c(1,2,3),
                NAME_10_04_OTHER=c(4,5,6),
                NAME_11_04_OTHER=c(7,8,9))


  MISC NAME_10_03_OTHER NAME_10_04_OTHER NAME_11_04_OTHER
1    a                1                4                7
2    a                2                5                8
3    b                3                6                9

I would like to transform it to this.

my_new_df <- data.frame(MISC=c("a","a","b","a","a","b","a","a","b"),
                    NAME_OTHER=c(1,2,3,4,5,6,7,8,9),
                    x=c(10,10,10,10,10,10,11,11,11),
                    y=c(3,3,3,3,3,3,4,4,4))
  MISC NAME_OTHER  x y
1    a          1 10 3
2    a          2 10 3
3    b          3 10 3
4    a          4 10 3
5    a          5 10 3
6    b          6 10 3
7    a          7 11 4
8    a          8 11 4
9    b          9 11 4

I can combine the columns with c(t(my_df)), but I lose the X and Y values.

Ananta · Accepted Answer · 2013-12-20 20:26:25Z

1

my_df <- data.frame(MISC=c("a","a","b"),
                    NAME_10_03_OTHER=c(1,2,3),
                    NAME_10_04_OTHER=c(4,5,6),
                    NAME_11_04_OTHER=c(7,8,9))
#require(reshape2)
mydf2<-melt(my_df)
mydf2$x=as.numeric(unlist(lapply(strsplit(as.character(mydf2$variable),"_", fixed=T),"[[",2)))
mydf2$y=as.numeric(unlist(lapply(strsplit(as.character(mydf2$variable),"_", fixed=T),"[[",3)))

mydf2

  MISC         variable value  x y
1    a NAME_10_03_OTHER     1 10 3
2    a NAME_10_03_OTHER     2 10 3
3    b NAME_10_03_OTHER     3 10 3
4    a NAME_10_04_OTHER     4 10 4
5    a NAME_10_04_OTHER     5 10 4
6    b NAME_10_04_OTHER     6 10 4
7    a NAME_11_04_OTHER     7 11 4
8    a NAME_11_04_OTHER     8 11 4
9    b NAME_11_04_OTHER     9 11 4

answered Dec 20, 2013 at 20:26

Ananta

3,7113 gold badges24 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

SethB Over a year ago

Thanks. This does everything I need.

Prasanna Nandakumar · Accepted Answer · 2013-12-20 20:27:22Z

1

You can make use of the below code:

s = "NAME_10_03_OTHER"
s1 = unlist(strsplit(s, split='_', fixed=TRUE))[2]
s2 = unlist(strsplit(s, split='_', fixed=TRUE))[3]

answered Dec 20, 2013 at 20:27

Prasanna Nandakumar

4,33539 silver badges65 bronze badges

Collectives™ on Stack Overflow

R Data Frame, combine columns by name and create new column

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related