Add a column with numbers of rows per day

Question

I simply would like to add a column (NbRowsPerDays) in my dataframe with the number of rows I have for each day. My df is thousand of rows length.

Which means :

device_id   UTC_date     UTC_time   datatype NbRowsPerDays
   182207   2018-08-31   05:40:59      GPS        2
   182207   2018-08-31   05:42:00      GPS        2
   182207   2018-09-01   05:44:00      GPS        1
   182207   2018-10-02   05:46:00      GPS        5
   182207   2018-10-02   05:48:00      GPS        5
   182207   2018-10-02   05:49:59      GPS        5
   182207   2018-10-02   05:40:59      GPS        5
   182207   2018-10-02   05:42:00      GPS        5
   182207   2018-11-06   05:44:00      GPS        2
   182207   2018-11-06   05:46:00      GPS        2
   182207   2018-12-15   05:48:00      GPS        1
   182207   2018-12-26   05:49:59      GPS        1

UTC_date is a factor. I know how to find numbers of rows for each day but I don't know how to put those values in a new column, with same value for several rows. Hope someone could help me. Thanks !

you can use `data %>% add_count(UTC_date, name = "NbRowsPerDays")` with `dplyr`-package — Humpelstielzchen, Apr 27 '20 at 09:13
I tried : Df %>% add_count(UTC_date) but nothing change in my Df. Do you have any ideas ? — Manon Billard, Apr 27 '20 at 09:23
did you assign `->` the result? Because otherwise it's just printed to the console. — Humpelstielzchen, Apr 27 '20 at 09:24

score 0 · Accepted Answer · answered Apr 27 '20 at 09:26

You can use ave to add a column with numbers of rows per day with the function length and grouping by UTC_date:

x$NbRowsPerDays <- ave(seq_len(nrow(x)), x$UTC_date, FUN=length)
x
#   device_id   UTC_date UTC_time datatype NbRowsPerDays
#1     182207 2018-08-31 05:40:59      GPS             2
#2     182207 2018-08-31 05:42:00      GPS             2
#3     182207 2018-09-01 05:44:00      GPS             1
#4     182207 2018-10-02 05:46:00      GPS             5
#5     182207 2018-10-02 05:48:00      GPS             5
#6     182207 2018-10-02 05:49:59      GPS             5
#7     182207 2018-10-02 05:40:59      GPS             5
#8     182207 2018-10-02 05:42:00      GPS             5
#9     182207 2018-11-06 05:44:00      GPS             2
#10    182207 2018-11-06 05:46:00      GPS             2
#11    182207 2018-12-15 05:48:00      GPS             1
#12    182207 2018-12-26 05:49:59      GPS             1

Data:

x <- read.table(header=TRUE, text="device_id   UTC_date     UTC_time   datatype NbRowsPerDays
   182207   2018-08-31   05:40:59      GPS        2
   182207   2018-08-31   05:42:00      GPS        2
   182207   2018-09-01   05:44:00      GPS        1
   182207   2018-10-02   05:46:00      GPS        5
   182207   2018-10-02   05:48:00      GPS        5
   182207   2018-10-02   05:49:59      GPS        5
   182207   2018-10-02   05:40:59      GPS        5
   182207   2018-10-02   05:42:00      GPS        5
   182207   2018-11-06   05:44:00      GPS        2
   182207   2018-11-06   05:46:00      GPS        2
   182207   2018-12-15   05:48:00      GPS        1
   182207   2018-12-26   05:49:59      GPS        1")

Add a column with numbers of rows per day

1 Answers1