0

I have data frame like this:

   Port   Type
1  Port1   ssh
2  Port1   ftp
3  Port2  http
4  Port1  http
5  Port2   ssh
6  Port3   ssh
7  Port3 https
8  Port4  http
9  Port2   ftp
10 Port3   ssh
11 Port3   ftp
12 Port4   ssh

I want to have a sum like this:

Port     ssh    ftp     http    https
Port1    1      1       1       0
Port2    1      1       1       0
Port3    2      1       0       1
Port4    1      0       0       1

I chose R because there are some other columns with numeric values, I could use R to compute mean/median/quantile quite conveniently. I searched and found this: Sum of rows based on column value, however the code there seems to be working only on numeric element.

Thanks a lot for the answer.

Community
  • 1
  • 1
Luke Huang
  • 13
  • 4

1 Answers1

2

We can just use table

table(df1)
#          Type
#Port    ftp http https ssh
#  Port1   1    1     0   1
#  Port2   1    1     0   1
#  Port3   1    0     1   2
#  Port4   0    1     0   1

Other option include dcast

library(reshape2)
dcast(df1, Port~Type, value.var='Type', length)
akrun
  • 874,273
  • 37
  • 540
  • 662
  • @LukeHuang If there are other columns, subset the columns of interest and do the `table`. i.e. `table(df1[c('Type', 'Port')])` – akrun Feb 10 '16 at 03:38
  • @LukeHuang Just subset the columns of interest as I mentioned earlier in the comments. – akrun Feb 10 '16 at 03:42
  • 1
    Perfectly works, Thanks @akrun. Sorry for the bad description. – Luke Huang Feb 10 '16 at 03:44
  • Hi @akrun, if number of https in all ports are 0, that is https is not in the data frame at all, but I still want to show it as all 0, what can I do? Thanks. – Luke Huang Feb 15 '16 at 20:51
  • @LukeHuang One option would be to convert the 'Type' column to `factor` class with all the `levels` specified and then do the `table`. i.e. `df1$Type <- factor(df1$Type, levels=c('ftp', 'http', 'https', 'ssh'));table(df1)` This will make sure that even if one level is absent, it will show all 0s' – akrun Feb 15 '16 at 21:02
  • great, works perfectly. Thanks a lot for the accurate answer and of course so quick response. Would you mind have a look at: http://stackoverflow.com/questions/35319824/generate-complicated-table-from-data-file, that question is related to this topic, I had a very ugly approach but need some expert's advices. Thanks so much. – Luke Huang Feb 15 '16 at 21:09