How to remove rows that have repeated elements?

user_12055151 0
Viewed 42

I have a dataframe that looks like this (but for every US county)

county state neighbor_county neighbor_state
Baldwin County AL Clarke County NA
Baldwin County AL Escambia County FL
Baldwin County AL Mobile County NA
Baldwin County AL Monroe County NA
Barbour County AL Dale County NA
Barbour County AL Henry County NA

I am only interested in what states neighbor a county, so I want to remove repeated data to get this (step 1):

county state neighbor_state
Baldwin County AL NA
Baldwin County AL FL
Barbour County AL NA

And then change sort the dataframe like this (step 2):

county state neighbor_state_1 neighbor_state_2 neighbor_state_3
Baldwin County AL FL NA NA
Baldwin County AL NA NA NA

In step 1 I've deleted the "neighbor_county" column; however, I've not managed to remove the duplicates in the column "neighbor_state" for each distinct county. I have tried using the unique function but I can't seem to make it work such that it only removes duplicates of each distinct county.

dataframe duplicates r
1 Answers

For your first step you could drop the neighbour_county column and the use unique():

df$neighbor_county <- NULL
unique(df)

returns

          county state neighbor_state
1 Baldwin_County    AL             NA
2 Baldwin_County    AL             FL
5 Barbour_County    AL             NA

An alternative using dplyr:

df %>% 
  select(-neighbor_county) %>% 
  distinct()

For your second step I make a suggestion:

library(tidyr)
library(dplyr)

df %>% 
  group_by(county) %>% 
  select(-neighbor_county) %>% 
  mutate(n = row_number()) %>% 
  pivot_wider(names_from=n, names_prefix="neighbor_state_", values_from=neighbor_state) %>% 
  ungroup()

returns

# A tibble: 2 x 6
  county         state neighbor_state_1 neighbor_state_2 neighbor_state_3 neighbor_state_4
  <chr>          <chr> <chr>            <chr>            <chr>            <chr>           
1 Baldwin_County AL    'NA'             'FL'             'NA'             'NA'            
2 Barbour_County AL    'NA'             'NA'             NA               NA

but I'm not sure, if this is what you are looking for.

For removing doubled NA-values, you could use

df %>% 
  group_by(county) %>% 
  select(-neighbor_county) %>% 
  distinct() %>% 
  mutate(n = row_number()) %>% 
  pivot_wider(names_from=n, names_prefix="neighbor_state_", values_from=neighbor_state) %>% 
  ungroup()

Data

structure(list(county = c("Baldwin_County", "Baldwin_County", 
"Baldwin_County", "Baldwin_County", "Barbour_County", "Barbour_County"
), state = c("AL", "AL", "AL", "AL", "AL", "AL"), neighbor_county = c("Clarke_County", 
"Escambia_County", "Mobile_County", "Monroe_County", "Dale_County", 
"Henry_County"), neighbor_state = c("'NA'", "'FL'", "'NA'", "'NA'", 
"'NA'", "'NA'")), problems = structure(list(row = 6L, col = "neighbor_state", 
    expected = "", actual = "embedded null", file = "literal data"), row.names = c(NA, 
-1L), class = c("tbl_df", "tbl", "data.frame")), class = "data.frame", row.names = c(NA, 
-6L), spec = structure(list(cols = list(county = structure(list(), class = c("collector_character", 
"collector")), state = structure(list(), class = c("collector_character", 
"collector")), neighbor_county = structure(list(), class = c("collector_character", 
"collector")), neighbor_state = structure(list(), class = c("collector_character", 
"collector"))), default = structure(list(), class = c("collector_guess", 
"collector")), skip = 1L), class = "col_spec"))
user_125052510
Related