Reputation: 923
Hi I have a sample data frame shown below:
Policy_Holder_ID Insured_ID
<chr> <chr>
1 ID27343 ID215664
2 ID27310 ID27310
3 ID27343 ID205729
4 ID27343 ID205728
5 ID27348 ID205734
6 ID27348 ID205735
7 ID27315 ID205719
8 ID27315 ID27315
9 ID27345 ID205731
10 ID27345 ID205733
11 ID27345 ID27345
12 ID2731 ID2731
13 ID27310 ID205714
14 ID27310 ID205715
Sorry if it's not in dput
form. I tried to use this function but didn't get a right result for it
What I want to is to categorize this data frame into 3 different categories as listed below:
So the output should look like this:
Policy_Holder_ID Insured_ID group
<chr> <chr>
1 ID27343 ID215664 2
2 ID27310 ID27310 3
3 ID27343 ID205729 2
4 ID27343 ID205728 2
5 ID27348 ID205734 2
6 ID27348 ID205735 2
7 ID27315 ID205719 3
8 ID27315 ID27315 3
9 ID27345 ID205731 3
10 ID27345 ID205733 3
11 ID27345 ID27345 3
12 ID2731 ID2731 1
13 ID27310 ID205714 3
14 ID27310 ID205715 3
I was hoping that you can provide a time-saving solution other than using the for
loop for the data. My original data has more than 400000 rows so for loop doesn't help me.
Upvotes: 3
Views: 385
Reputation: 439
@Roozbeh
Did you try to split this file into several files to compute more faster each one?
If others answers didn't work, you can try this:
Try to use bigreadr package. Try to do the steps bellow:
Save your dataframe into a .csv file;
Use split_file function to split - like this:
split_file('your_file.csv', every_nlines = 'how much lines for each file' , prefix_out = 'output's name', repeat_header = T)
Read each file to do for loop; and
Do a rbind to regroup all files into one.
Upvotes: 0
Reputation: 101663
A data.table
option using nested ifelse
setDT(df)[
,
group := ifelse(
all(Policy_Holder_ID == Insured_ID),
1,
ifelse(
!unique(Policy_Holder_ID) %in% Insured_ID,
2,
3
)
),
Policy_Holder_ID
]
gives
> df
Policy_Holder_ID Insured_ID group
1: ID27343 ID215664 2
2: ID27310 ID27310 3
3: ID27343 ID205729 2
4: ID27343 ID205728 2
5: ID27348 ID205734 2
6: ID27348 ID205735 2
7: ID27315 ID205719 3
8: ID27315 ID27315 3
9: ID27345 ID205731 3
10: ID27345 ID205733 3
11: ID27345 ID27345 3
12: ID2731 ID2731 1
13: ID27310 ID205714 3
14: ID27310 ID205715 3
Data
> dput(df)
structure(list(Policy_Holder_ID = c("ID27343", "ID27310", "ID27343",
"ID27343", "ID27348", "ID27348", "ID27315", "ID27315", "ID27345",
"ID27345", "ID27345", "ID2731", "ID27310", "ID27310"), Insured_ID = c("ID215664",
"ID27310", "ID205729", "ID205728", "ID205734", "ID205735", "ID205719",
"ID27315", "ID205731", "ID205733", "ID27345", "ID2731", "ID205714",
"ID205715")), class = "data.frame", row.names = c("1", "2", "3",
"4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14"))
Upvotes: 2
Reputation: 887213
We can use case_when
after grouping by 'Policy_Holder_ID'. Based on the description, if we have all
elements of 'Insured_ID' matches the 'Policy_Holder_ID' , then return 1, if none of them matches (!=
-> again with all
), then return 2, and the default option should return 3.
library(dplyr)
df1 %>%
group_by(Policy_Holder_ID) %>%
mutate(group = case_when(all(Insured_ID == Policy_Holder_ID) ~ 1,
all(Insured_ID != Policy_Holder_ID)~ 2,
TRUE ~ 3)) %>%
ungroup
-output
# A tibble: 14 x 3
# Policy_Holder_ID Insured_ID group
# <chr> <chr> <dbl>
# 1 ID27343 ID215664 2
# 2 ID27310 ID27310 3
# 3 ID27343 ID205729 2
# 4 ID27343 ID205728 2
# 5 ID27348 ID205734 2
# 6 ID27348 ID205735 2
# 7 ID27315 ID205719 3
# 8 ID27315 ID27315 3
# 9 ID27345 ID205731 3
#10 ID27345 ID205733 3
#11 ID27345 ID27345 3
#12 ID2731 ID2731 1
#13 ID27310 ID205714 3
#14 ID27310 ID205715 3
df1 <- structure(list(Policy_Holder_ID = c("ID27343", "ID27310", "ID27343",
"ID27343", "ID27348", "ID27348", "ID27315", "ID27315", "ID27345",
"ID27345", "ID27345", "ID2731", "ID27310", "ID27310"), Insured_ID = c("ID215664",
"ID27310", "ID205729", "ID205728", "ID205734", "ID205735", "ID205719",
"ID27315", "ID205731", "ID205733", "ID27345", "ID2731", "ID205714",
"ID205715")), class = "data.frame", row.names = c("1", "2", "3",
"4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14"))
Upvotes: 3
Reputation: 472
# Find out which are same and which are not same
df1 <- df %>% filter(Policy_Holder_ID == Insured_ID) %>% mutate(group = 1)
df2 <- df %>% filter(Policy_Holder_ID != Insured_ID) %>% mutate(group = 2)
# find out which are the common ones
common_Policy_Holder_ID <- intersect(df1$Policy_Holder_ID, df2$Policy_Holder_ID)
# if they are in common, then change the value of group
df <- bind_rows(df1, df2) %>% if_else(common_Policy_Holder_ID == Policy_Holder_ID, group = 3, group = group)
Upvotes: 1