Create new column using sqldf in R and COUNT with multiple criteria

Question

I need to count every different truck that pass in the same cod, on the same period, and create a new column which contains the division between arrivestops/(the count calculated). So the number of this column be the same for one cod in the same period for all the trucks that pass in that period for that cod.

data frame is truck and below is my data

  Cod  |   trucks  | route   | startrip |arrivestops| period
-------|-----------|---------|--------- |-----------|---------
RM-0378|   CJRL-68 |T248 00R |  22:59:16|   33      |   46  


structure(list(Cod = structure(c(5L, 10L, 4L, 6L, 6L, 13L, 3L, 
12L, 14L, 6L, 6L, 10L, 10L, 10L, 16L, 6L, 6L, 12L, 3L, 1L, 7L, 
17L, 3L, 12L, 2L, 2L, 3L, 12L, 4L, 9L, 10L, 10L, 15L, 12L, 3L, 
2L, 7L, 1L, 8L, 4L, 12L, 3L, 11L, 6L, 6L), .Label = c("RM-0035", 
"RM-0251", "RM-0370", "RM-0374", "RM-0378", "RM-0471", "RM-0532", 
"RM-0550", "RM-0595", "RM-0598", "RM-0732", "RM-0764", "RM-0774", 
"RM-0779", "RM-0780", "RM-0782", "RM-0783"), class = "factor"), 
    trucks = structure(c(18L, 33L, 32L, 29L, 27L, 39L, 3L, 3L, 
    19L, 25L, 28L, 37L, 1L, 12L, 20L, 30L, 26L, 7L, 7L, 36L, 
    23L, 16L, 5L, 5L, 38L, 35L, 9L, 9L, 22L, 2L, 13L, 15L, 17L, 
    4L, 4L, 34L, 24L, 14L, 21L, 31L, 6L, 6L, 8L, 11L, 10L), .Label = c("BBKB-12", 
    "BBKD-15", "BJFP-33", "BJFS-62", "BJFT-31", "BJFT-59", "BJFV-84", 
    "BJFW-52", "BJFX-52", "BKWK-93", "CJRB-53", "CJRG-55", "CJRG-56", 
    "CJRG-62", "CJRG-71", "CJRK-87", "CJRL-29", "CJRL-68", "CJRL-69", 
    "CJRL-93", "CJRW-22", "CJRY-86", "FLXF-31", "FLXF-53", "FLXG-86", 
    "FLXJ-59", "FLXJ-62", "FLXJ-64", "FLXJ-68", "FLXJ-84", "FLXP-93", 
    "FLXR-12", "FLXR-84", "WA-9478", "WA-9488", "WU-5547", "WU-5556", 
    "ZN-6184", "ZN-6266"), class = "factor"), route = structure(c(18L, 
    12L, 23L, 11L, 3L, 19L, 22L, 22L, 17L, 2L, 2L, 9L, 10L, 8L, 
    15L, 11L, 3L, 25L, 25L, 1L, 30L, 16L, 28L, 28L, 33L, 32L, 
    26L, 26L, 24L, 7L, 6L, 13L, 14L, 25L, 25L, 33L, 29L, 5L, 
    31L, 21L, 27L, 27L, 20L, 11L, 4L), .Label = c("F01 C0 00I", 
    "F05 00I", "F06 00I", "F08 00I", "F10 00R", "F13 00R", "F14 00R", 
    "F15 00R", "F16 00I", "F19 00R", "F20 00I", "F22 00I", "F25 00R", 
    "T238 00R", "T243 00I", "T245 00I", "T246 00I", "T248 00R", 
    "T252 00R", "T301 C0 00I", "T321 00I", "T323 00I", "T333 00I", 
    "T337 00R", "T355 00R", "T357 00I", "T358 00R", "T363 00R", 
    "T381 00R", "T382 00R", "T384 00R", "T401 00R", "T405 00R"
    ), class = "factor"), startrip = structure(c(35L, 31L, 37L, 
    18L, 12L, 34L, 36L, 36L, 38L, 11L, 14L, 29L, 30L, 28L, 32L, 
    13L, 9L, 33L, 33L, 21L, 1L, 23L, 20L, 20L, 3L, 2L, 24L, 24L, 
    27L, 6L, 10L, 8L, 19L, 26L, 26L, 4L, 17L, 7L, 16L, 25L, 26L, 
    26L, 22L, 5L, 15L), .Label = c("21:05:23", "21:10:34", "21:14:36", 
    "21:19:55", "21:39:11", "21:48:51", "21:58:25", "21:59:50", 
    "22:03:14", "22:09:11", "22:13:00", "22:14:04", "22:15:30", 
    "22:16:27", "22:17:21", "22:22:05", "22:22:31", "22:24:35", 
    "22:25:00", "22:25:15", "22:26:29", "22:27:52", "22:28:19", 
    "22:28:32", "22:29:03", "22:30:26", "22:31:32", "22:40:25", 
    "22:41:51", "22:44:40", "22:51:24", "22:55:23", "22:57:41", 
    "22:58:36", "22:59:16", "23:00:03", "23:00:36", "23:01:57"
    ), class = "factor"), arrivestops = c(33L, 249L, 205L, 283L, 
    283L, 104L, 44L, 178L, 59L, 283L, 283L, 249L, 249L, 249L, 
    96L, 283L, 283L, 178L, 44L, 290L, 384L, 48L, 68L, 216L, 178L, 
    178L, 68L, 216L, 256L, 379L, 253L, 253L, 88L, 216L, 68L, 
    178L, 384L, 290L, 172L, 256L, 216L, 68L, 255L, 341L, 341L
    ), period = c(46L, 46L, 46L, 46L, 46L, 46L, 46L, 46L, 46L, 
    46L, 46L, 46L, 46L, 46L, 46L, 46L, 46L, 46L, 46L, 45L, 45L, 
    45L, 45L, 45L, 45L, 45L, 45L, 45L, 45L, 45L, 45L, 45L, 45L, 
    45L, 45L, 45L, 45L, 45L, 45L, 45L, 45L, 45L, 45L, 45L, 45L
    )), .Names = c("Cod", "trucks", "route", "startrip", "arrivestops", 
"period"), class = "data.frame", row.names = c(NA, -45L))

I try to use sqldf with SELECT and COUNT, but I do not know well how to use multiple conditions with count and create a new column with the result. For the COUNT I need to group by but I do not want that group the rows in the result, I need that all remains.

Just an example:

  Cod  |   trucks  | route   | startrip |arrivestops| period   |arrivesindv
  -----|-----------|---------|--------- |-----------|--------- |----------
RM-0378|   CJRL-68 |T248 00R |  22:59:16|   30      |   46     |    6

Which 6 in arrivesind is the result of 30/5, assuming that for that cod in that period pass 5 different trucks.

Answer 1

Based on your original question and the details in your comment on this answer, I have updated the solution as follows:

tmp <- sqldf("select Cod, period,
              ((arrivestops*1.0)/((count(trucks)*1.0))) as arrivesindv 
              from truck group by Cod,period")
tmp2 <- sqldf("select a.*, b.arrivesindv from truck a left join tmp b on a.Cod = b.Cod and a.period = b.period")

tmp2

       Cod  trucks       route startrip arrivestops period arrivesindv
1  RM-0378 CJRL-68    T248 00R 22:59:16          33     46    33.00000
2  RM-0598 FLXR-84     F22 00I 22:51:24         249     46    62.25000
3  RM-0374 FLXR-12    T333 00I 23:00:36         205     46   205.00000
4  RM-0471 FLXJ-68     F20 00I 22:24:35         283     46    47.16667
5  RM-0471 FLXJ-62     F06 00I 22:14:04         283     46    47.16667
6  RM-0774 ZN-6266    T252 00R 22:58:36         104     46   104.00000
7  RM-0370 BJFP-33    T323 00I 23:00:03          44     46    22.00000
8  RM-0764 BJFP-33    T323 00I 23:00:03         178     46    89.00000
9  RM-0779 CJRL-69    T246 00I 23:01:57          59     46    59.00000
10 RM-0471 FLXG-86     F05 00I 22:13:00         283     46    47.16667
11 RM-0471 FLXJ-64     F05 00I 22:16:27         283     46    47.16667
12 RM-0598 WU-5556     F16 00I 22:41:51         249     46    62.25000
13 RM-0598 BBKB-12     F19 00R 22:44:40         249     46    62.25000
14 RM-0598 CJRG-55     F15 00R 22:40:25         249     46    62.25000
15 RM-0782 CJRL-93    T243 00I 22:55:23          96     46    96.00000
16 RM-0471 FLXJ-84     F20 00I 22:15:30         283     46    47.16667
17 RM-0471 FLXJ-59     F06 00I 22:03:14         283     46    47.16667
18 RM-0764 BJFV-84    T355 00R 22:57:41         178     46    89.00000
19 RM-0370 BJFV-84    T355 00R 22:57:41          44     46    22.00000
20 RM-0035 WU-5547  F01 C0 00I 22:26:29         290     45   145.00000
21 RM-0532 FLXF-31    T382 00R 21:05:23         384     45   192.00000
22 RM-0783 CJRK-87    T245 00I 22:28:19          48     45    48.00000
23 RM-0370 BJFT-31    T363 00R 22:25:15          68     45    17.00000
24 RM-0764 BJFT-31    T363 00R 22:25:15         216     45    54.00000
25 RM-0251 ZN-6184    T405 00R 21:14:36         178     45    59.33333
26 RM-0251 WA-9488    T401 00R 21:10:34         178     45    59.33333
27 RM-0370 BJFX-52    T357 00I 22:28:32          68     45    17.00000
28 RM-0764 BJFX-52    T357 00I 22:28:32         216     45    54.00000
29 RM-0374 CJRY-86    T337 00R 22:31:32         256     45   128.00000
30 RM-0595 BBKD-15     F14 00R 21:48:51         379     45   379.00000
31 RM-0598 CJRG-56     F13 00R 22:09:11         253     45   126.50000
32 RM-0598 CJRG-71     F25 00R 21:59:50         253     45   126.50000
33 RM-0780 CJRL-29    T238 00R 22:25:00          88     45    88.00000
34 RM-0764 BJFS-62    T355 00R 22:30:26         216     45    54.00000
35 RM-0370 BJFS-62    T355 00R 22:30:26          68     45    17.00000
36 RM-0251 WA-9478    T405 00R 21:19:55         178     45    59.33333
37 RM-0532 FLXF-53    T381 00R 22:22:31         384     45   192.00000
38 RM-0035 CJRG-62     F10 00R 21:58:25         290     45   145.00000
39 RM-0550 CJRW-22    T384 00R 22:22:05         172     45   172.00000
40 RM-0374 FLXP-93    T321 00I 22:29:03         256     45   128.00000
41 RM-0764 BJFT-59    T358 00R 22:30:26         216     45    54.00000
42 RM-0370 BJFT-59    T358 00R 22:30:26          68     45    17.00000
43 RM-0732 BJFW-52 T301 C0 00I 22:27:52         255     45   255.00000
44 RM-0471 CJRB-53     F20 00I 21:39:11         341     45   170.50000
45 RM-0471 BKWK-93     F08 00I 22:17:21         341     45   170.50000