Search code examples
rdataframecountcalculated-columns

R: how to create a new column in a dataframe where is cardinally counted how many times an observation has the same value for a variable

I have a R dataframe of more than 15,000 rows like the following one:

+------------------------------------+-------+
|  Authors                           | IDs   |
+------------------------------------+-------+
|  Abad J., Cabrera H.R., Medina A.  | 16400 |
|  Abad J., Cabrera H.R., Medina A.  | 70058 |
|  Abad J., Cabrera H.R., Medina A.  | 71030 |
|  A Banuls V., Salmeron J.L.        | 57196 |
|  A Banuls V., Salmeron J.L.        | 56372 |
+------------------------------------+-------+

What i want to obtain is the following new column:

+------------------------------------+-------+-------+
|  Authors                           | IDs   |Order  |
+------------------------------------+-------+-------+
|  Abad J., Cabrera H.R., Medina A.  | 16400 |   1   |
|  Abad J., Cabrera H.R., Medina A.  | 70058 |   2   |
|  Abad J., Cabrera H.R., Medina A.  | 71030 |   3   |
|  A Banuls V., Salmeron J.L.        | 57196 |   1   | 
|  A Banuls V., Salmeron J.L.        | 56372 |   2   |
+------------------------------------+-------+-------+

Basically i want a new column where is counted the number of observations that have the same Authors.

Any guess ?

Solution

  • We can do a group by 'Authors' and get the row_number()

    library(dplyr)
df1 %>%
  group_by(Authors)%>%
  mutate(order = row_number())

    Or with ave

    df1$order <- with(df1, ave(seq_along(Authors), Authors, FUN = seq_along))

    Or if the 'Authors' are arranged alphabetically

    df1$order <- sequence(table(df1$Authors))