CODEWITHSUNDEEP
rstring
Daniel
Daniel

Reputation: 395

Differentiate ID according to another variable

I need to differentiate the column ID (with a letter) if they are different in other variable ("Art" in this case). Like this:

Id<-c("RoLu1976", "RoLu1976", "AlBlKyFy1989", "ThSa1996", "AlBlKyFy1989","ThSa1996")
Art<-c("Econometric Policy Evaluation", "Policy Right", "Rules", "Expectations", "Nonneutrality of money","Expectations")
Yr<-c(1976, 1976, 1989, 1996, 1989, 1996)
df<-data.frame(Id,Art,Yr)

In the above the Ids should be:

Id             Art                                Yr
RoLu1976a      Econometric Policy Evaluation     1976
RoLu1976b      Policy Right                      1976
AlBlKyFi1989a  Rules                             1989
ThSa1996       Expectations                      1996
AlBlKyFi1989b  Nonneutrality of money            1989
ThSa1996       Expectations                      1996

In this case, column ID are identical in some cases (for example with RoLu1976) but different in "Art" column.

Upvotes: 1

Views: 70

Answers (4)

Haboryme
Haboryme

Reputation: 4761

With dplyr:

df%>%group_by(Id)%>%
  mutate(nb_art=length(unique(Art)))%>%
  mutate(lettre=letters[seq(nb_art)])%>%
  mutate(Id_letters=paste0(Id,ifelse(nb_art>1,lettre,"")))%>%
  ungroup()%>%
  mutate(Id=Id_letters)%>%
  select(Id,Art,Yr)

This can be shortened but it makes it very clear to read (I hope).

# A tibble: 7 x 3
             Id                           Art    Yr
          <chr>                        <fctr> <dbl>
1     RoLu1976a Econometric Policy Evaluation  1976
2     RoLu1976b                  Policy Right  1976
3 AlBlKyFy1989a                         Rules  1989
4      ThSa1996                  Expectations  1996
5 AlBlKyFy1989b        Nonneutrality of money  1989
6      ThSa1996                  Expectations  1996

Upvotes: 1

eipi10
eipi10

Reputation: 93821

Using the dplyr package:

library(dplyr)

df %>%
  arrange(Id, Art) %>%
  group_by(Id) %>%
  mutate(Id2 = if(length(unique(Art)) > 1) paste0(Id, "_", letters[as.numeric(factor(Art))]) else as.character(Id)) %>%
  ungroup %>%
  select(Id=Id2, everything(), -Id)

              Id                           Art    Yr
1 AlBlKyFy1989_a        Nonneutrality of money  1989
2 AlBlKyFy1989_b                         Rules  1989
3     RoLu1976_a Econometric Policy Evaluation  1976
4     RoLu1976_b                  Policy Right  1976
5       ThSa1996                  Expectations  1996
6       ThSa1996                  Expectations  1996

Upvotes: 4

shosaco
shosaco

Reputation: 6165

With for loops:

df$Id <- as.character(df$Id)

# loop through Ids
for(id in unique(df$Id)){
  sub <- unique(df[df$Id == id,])
  # check if this Id needs to be manipulated
  if(nrow(sub) > 1){
    # assign unique Ids
    for(j in 1:nrow(sub)){
      sub[j,1] <- paste0(sub[j,1],letters[j])
    }
    # replace old Ids with new Ids
    df[df$Id == id, ] <- sub
  } 
}

Upvotes: 1

Allen Wang
Allen Wang

Reputation: 2502

A data.table solution

library(data.table)

setDT(df)
df[, tmp := seq(uniqueN(Art)), by = Id]
df[, addition := ifelse(.N>1, "",letters[tmp]), by = .(Id, Art)]
df[, Id := paste0(Id, addition)]
df[, c("tmp", "addition") := NULL]

Upvotes: 1

Related Questions