CODEWITHSUNDEEP
rparsingcsvsplit
Mario Stoilov
Mario Stoilov

Reputation: 3447

Converting csv values to table in R

I have some data from a poll which looks like this:

                                    Freetime_activities
1                       Travelling, On the PC, Clubbing
2                           Sports, On the PC, Clubbing
3                                              Clubbing
4                                             On the PC
5                       Travelling, On the PC, Clubbing
6                                             On the PC
7                               Watching TV, Travelling

I want to get the count of each value (how many times Travelling/On the PC/etc.), but I'm having trouble splitting the values. Is there a function in R that can do for example:

split("A,B,C") -> 
1 A
2 B
3 C

Or is there a straight forward solution to counting the values directly from the column?

Upvotes: 2

Views: 67

Answers (1)

akrun
akrun

Reputation: 886968

We can use strsplit to split the column by the delimiter ", "), unlist the list output and then use table to get the frequency

 tbl <- table(unlist(strsplit(as.character(df1$Freetime_activities),
                                          ", ")))
 as.data.frame(tbl)
 #         Var1 Freq
 #1    Clubbing    4
 #2   On the PC    5
 #3      Sports    1
 #4  Travelling    3
 #5 Watching TV    1

NOTE: Here is used as.character in case the column is a factor as strsplit can take only character vectors.

Or another option would be to use scan to extract the elements, and then with table get the frequency.

 table(trimws(scan(text = as.character(df1$Freetime_activities),
                   what = "", sep = ",")))

Or using read.table with unlist and table

table(unlist(read.table(text = as.character(df1$Freetime_activities), 
           sep = ",", fill = TRUE, strip.white = TRUE)))

EDIT: Based on @David Arenburg's comments.

data

df1 <- structure(list(Freetime_activities = c("Travelling, On the PC, 
  Clubbing", 
"Sports, On the PC, Clubbing", "Clubbing", "On the PC", "Travelling, 
 On the PC, Clubbing", 
"On the PC", "Watching TV, Travelling")), 
 .Names = "Freetime_activities", 
 class = "data.frame", row.names = c("1", 
"2", "3", "4", "5", "6", "7"))

Upvotes: 5

Related Questions