0

I have below dataset where I want to create a "New_Record_ID" column using the "Current_Record_ID" and "Stores". I'am trying to make "New_Record_ID" increment every 2 Stores based on "Current_Record_ID".

for example: If "Current_Record_ID" is having 2 stores then the record ID will remain same. But if it exceeds 2 Stores then a new record ID (increment '+1') will be assigned to it and similar will follow for the next record IDs in the column "Current_Record_ID".

Dummy dataframe:

df <- data.frame(Stores=c(1, 2, 3, 4, 5, 6, 7, 8, 9, 10,11,12),Current_Record_ID=c(1, 1, 2, 2, 2, 2, 3, 3, 3, 3,4,4))

Stores Current_Record_ID
1 1
2 1
3 2
4 2
5 2
6 2
7 3
8 3
9 3
10 3
11 4
12 4

Expected Result

Stores Current_Record_ID New_Record_ID
1 1 1
2 1 1
3 2 2
4 2 2
5 2 3
6 2 3
7 3 4
8 3 4
9 3 5
10 3 5
11 4 6
12 4 6

Second Part:

Sample dataframe:

df <- data.frame(Stores=c(1, 2, 3, 4, 5, 6, 7, 8, 9, 10,11,12,13,14,15,16,17,18,19,20),
                 Current_Record_ID=c(1, 1, 2, 3, 3, 3, 4, 4, 4, 4,4,4,4,5,5,6,7,7,7,8))

Expected Result:

Stores Current_Record_ID New_Record_ID
1 1 1
2 1 1
3 2 2
4 3 3
5 3 3
6 3 4
7 4 5
8 4 5
9 4 6
10 4 6
11 4 7
12 4 7
13 4 8
14 5 9
15 5 9
16 6 10
17 7 11
18 7 11
19 7 12
20 8 13

*Also if we have a larger data set of n stores and Currect_Record_ID and we want to fix stores/not to exceed 100 counts. How can we will create the New_record_ID .?

Shank
  • 17
  • 3
  • 1
    What does "Row_ID is having less than or equal to 2 Stores" mean? Do you mean 2 * the value in Stores? – SamR Jun 28 '22 at 14:05
  • Hi SamR, thanks for looking into this. I have done a edit of the previous version of my ask. Hope it helps. – Shank Jun 29 '22 at 13:11

2 Answers2

1

I'm not entirely sure what logic you're describing, but here's how to create a new column in a dataframe based on logic in R:

using with:

df$New_ID <- with(df, if (Row_ID < 2) {Stores} else {Row_ID})

using within:

df <- within(df, New_ID <- if (Row_ID < 2) {Stores} else {Row_ID})

using dplyr::mutate:

df <- df %>% dplyr::mutate(New_ID = if (Row_ID < 2) {Stores} else {Row_ID})

Like I said, I'm not clear on your logic, so you'll have to replace that and use the approach that makes the most sense given your project.

See this post and these docs for more details.

luke
  • 465
  • 1
  • 14
  • Thanks for your response Luke! :) I will explain the logic that I'm trying to built here: So I have two columns in the dataframe (as per the code provided). Now within the dataframe the Stores are unique and the Row_ID are repeating, so every repeating Row ID have unique stores. But if the store are more than 2 for any repeating Row_ID then the Row_ID shall change. So the new ID will be similar to Row ID but then it should not hold more than 2 stores. Sorry for my description. – Shank Jun 28 '22 at 14:42
  • No problem, Shank. It might be a good idea to edit your original question with this description just so that others will be able to easily see it (they might have better advice than me). If I'm understanding you correctly, you need to make ids increment every 2 IDs, is that correct? – luke Jun 28 '22 at 14:55
  • 1
    Yes Luke, that's correct! I have also edited my original question too. Hope it conves the right ask. – Shank Jun 28 '22 at 15:00
1

Thanks for the clarification. If I have understood now then this should do the trick:

df <- data.frame(Stores=c(1, 2, 3, 4, 5, 6, 7, 8, 9, 10,11,12),Current_Record_ID=c(1, 1, 2, 2, 2, 2, 3, 3, 3, 3,4,4)) 

df$New_Record_ID  <- rep(1:(nrow(df)/2), each = 2)

df
#    Stores Current_Record_ID New_Record_ID
# 1       1                 1             1
# 2       2                 1             1
# 3       3                 2             2
# 4       4                 2             2
# 5       5                 2             3
# 6       6                 2             3
# 7       7                 3             4
# 8       8                 3             4
# 9       9                 3             5
# 10     10                 3             5
# 11     11                 4             6
# 12     12                 4             6
SamR
  • 8,826
  • 3
  • 11
  • 33
  • Hi SamR thanks for your time and providing the solution. It works for me. But was wondering if the arrangement is lil different with different data sets, what can be the solution. I have edited my original post with a second part, if you can help me with that too. ?? Thanks in advance. – Shank Jun 30 '22 at 11:50