Add column name for every row in data frame

Question

I want to add the column name as a parameter entry for row associated to that column. This is what I have tried:

x <- matrix(c(101:104,101:104,105:106,1,2,3,3,4,5,4,5,7,5), nrow=10, ncol=2) 

     V1   V2
 1  101    1
 2  102    2
 3  103    3
 4  104    3
 5  101    4
 6  102    5
 7  103    4
 8  104    5
 9  105    7
10  106    5

Converting to table:

as.data.frame(table(x))

Which gives me:

     x Freq
1    1    1
2    2    1
3    3    2
4    4    2
5    5    3
6    7    1
7  101    2
8  102    2
9  103    2
10 104    2
11 105    1
12 106    1

What should I do so I get this:

    V Val Freq
1  V2   1    1
2  V2   2    1
3  V2   3    2
4  V2   4    2
5  V2   5    3
6  V2   7    1
7  V1 101    2
8  V1 102    2
9  V1 103    2
10 V1 104    2
11 V1 105    1
12 V1 106    1

I am relatively new to R. So if there is any alternate method that I can use then please let me know.

Rich Scriven · Accepted Answer · 2015-11-01 02:47:43Z

3

You didn't add any column names to the original matrix x to begin with. Matrices don't automatically come with column names like data frames do. But that's okay, we can use a vector for simulating them. Then we can match up the tabled values with simulated column names.

a <- as.data.frame(table(x))
cbind(V = c("V1", "V2")[col(x)[match(a$x, x)]], a)
#     V   x Freq
# 1  V2   1    1
# 2  V2   2    1
# 3  V2   3    2
# 4  V2   4    2
# 5  V2   5    3
# 6  V2   7    1
# 7  V1 101    2
# 8  V1 102    2
# 9  V1 103    2
# 10 V1 104    2
# 11 V1 105    1
# 12 V1 106    1

edited Nov 1, 2015 at 2:47

answered Oct 31, 2015 at 22:54

Rich Scriven

99.8k11 gold badges191 silver badges252 bronze badges

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

Comments

Jaap · Accepted Answer · 2018-06-02 08:52:01Z

3

You could use the data.table package for this as well:

library(data.table)
melt(as.data.table(x), measure.vars=1:2)[, .N, by = .(variable, value)]

which gives the desired result:

    variable value N
 1:       V1   101 2
 2:       V1   102 2
 3:       V1   103 2
 4:       V1   104 2
 5:       V1   105 1
 6:       V1   106 1
 7:       V2     1 1
 8:       V2     2 1
 9:       V2     3 2
10:       V2     4 2
11:       V2     5 3
12:       V2     7 1

edited Jun 2, 2018 at 8:52

answered Nov 1, 2015 at 7:01

Jaap

83.7k36 gold badges190 silver badges203 bronze badges

Comments

Carl · Accepted Answer · 2015-11-01 03:32:28Z

1

Try this:

pt1<-data.frame(V="V1",table(x[,1]))
pt2<-data.frame(V="V2",table(x[,2]))
final<-rbind(pt1,pt2)

edit:

countcols<-function(df) {
  colname<-colnames(df)
  # create empty dataframe with structure we want
  counts<-data.frame(V="temp",table(df[,1]))[0,]
for (i in colname) {
  counts<-rbind(counts,data.frame(V=i,table(df[,i])))
}
  return(counts)
}

Keep in mind you need to name the object.

countcols(x) gives a count of each column in a Tidy output

edit:

> pander::pander(countcols(mtcars))

------------------
 V    Var1   Freq 
---- ------ ------
mpg   10.4    2   

mpg   13.3    1   

mpg   14.3    1   

mpg   14.7    1   

mpg    15     1   

mpg   15.2    2   

mpg   15.5    1   

mpg   15.8    1   

mpg   16.4    1   

mpg   17.3    1   

mpg   17.8    1   

mpg   18.1    1   

mpg   18.7    1   

mpg   19.2    2   

mpg   19.7    1   

mpg    21     2   

mpg   21.4    2   

mpg   21.5    1   

mpg   22.8    2   

mpg   24.4    1   

mpg    26     1   

mpg   27.3    1   

mpg   30.4    2   

mpg   32.4    1   

mpg   33.9    1   

cyl    4      11  

cyl    6      7   

cyl    8      14  

disp  71.1    1   

disp  75.7    1   

disp  78.7    1   

disp   79     1   

disp  95.1    1   

disp  108     1   

disp 120.1    1   

disp 120.3    1   

disp  121     1   

disp 140.8    1   

disp  145     1   

disp 146.7    1   

disp  160     2   

disp 167.6    2   

disp  225     1   

disp  258     1   

disp 275.8    3   

disp  301     1   

disp  304     1   

disp  318     1   

disp  350     1   

disp  351     1   

disp  360     2   

disp  400     1   

disp  440     1   

disp  460     1   

disp  472     1   

 hp    52     1   

 hp    62     1   

 hp    65     1   

 hp    66     2   

 hp    91     1   

 hp    93     1   

 hp    95     1   

 hp    97     1   

 hp   105     1   

 hp   109     1   

 hp   110     3   

 hp   113     1   

 hp   123     2   

 hp   150     2   

 hp   175     3   

 hp   180     3   

 hp   205     1   

 hp   215     1   

 hp   230     1   

 hp   245     2   

 hp   264     1   

 hp   335     1   

drat  2.76    2   

drat  2.93    1   

drat   3      1   

drat  3.07    3   

drat  3.08    2   

drat  3.15    2   

drat  3.21    1   

drat  3.23    1   

drat  3.54    1   

drat  3.62    1   

drat  3.69    1   

drat  3.7     1   

drat  3.73    1   

drat  3.77    1   

drat  3.85    1   

drat  3.9     2   

drat  3.92    3   

drat  4.08    2   

drat  4.11    1   

drat  4.22    2   

drat  4.43    1   

drat  4.93    1   

 wt  1.513    1   

 wt  1.615    1   

 wt  1.835    1   

 wt  1.935    1   

 wt   2.14    1   

 wt   2.2     1   

 wt   2.32    1   

 wt  2.465    1   

 wt   2.62    1   

 wt   2.77    1   

 wt   2.78    1   

 wt  2.875    1   

 wt   3.15    1   

 wt   3.17    1   

 wt   3.19    1   

 wt  3.215    1   

 wt  3.435    1   

 wt   3.44    3   

 wt   3.46    1   

 wt   3.52    1   

 wt   3.57    2   

 wt   3.73    1   

 wt   3.78    1   

 wt   3.84    1   

 wt  3.845    1   

 wt   4.07    1   

 wt   5.25    1   

 wt  5.345    1   

 wt  5.424    1   

qsec  14.5    1   

qsec  14.6    1   

qsec 15.41    1   

qsec  15.5    1   

qsec 15.84    1   

qsec 16.46    1   

qsec  16.7    1   

qsec 16.87    1   

qsec  16.9    1   

qsec 17.02    2   

qsec 17.05    1   

qsec  17.3    1   

qsec  17.4    1   

qsec 17.42    1   

qsec  17.6    1   

qsec 17.82    1   

qsec 17.98    1   

qsec   18     1   

qsec  18.3    1   

qsec 18.52    1   

qsec  18.6    1   

qsec 18.61    1   

qsec  18.9    2   

qsec 19.44    1   

qsec 19.47    1   

qsec  19.9    1   

qsec   20     1   

qsec 20.01    1   

qsec 20.22    1   

qsec  22.9    1   

 vs    0      18  

 vs    1      14  

 am    0      19  

 am    1      13  

gear   3      15  

gear   4      12  

gear   5      5   

carb   1      7   

carb   2      10  

carb   3      3   

carb   4      10  

carb   6      1   

carb   8      1   
------------------

edited Nov 1, 2015 at 3:32

answered Oct 31, 2015 at 22:52

Carl

5,7897 gold badges45 silver badges83 bronze badges

5 Comments

AngryPanda Over a year ago

This works! Thanks. But is there a more generic solution to this, for instance - using colnames(x) instead of hard coding it?

Carl Over a year ago

See my edit. You can generalize but you need to make sure your objects have names

David Arenburg Over a year ago

Just FYI growing objects in a for loop is a very bad practice in R and thus should be avoided at all cost. See Circle #2 here.

Carl Over a year ago

Agreed that it wouldn't scale well but you can't really perform a vectorized operation across columns like OP asked.

Carl Over a year ago

sapply wouldn't do what OP wanted

Collectives™ on Stack Overflow

Add column name for every row in data frame

3 Answers 3

Comments

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related