Counting the number of unique values in a column

General
#1

Hello,

I want to count the number of times a zip code shows up in a column. I have three columns of data: State, City and Zip Code. I've been able to group the data by State and City. Each city has multiple zip codes. I want to be able to count how many times zip code 70000 shows up and how many times zip code 70001 shows up, etc. I'm thinking a new column would need to be created to do this: State, City, Zip Code, Zip Code Count, where Zip Code Count would not count how many zip codes are in the city, but how many TIMES each zip code appears in each city.

I've used the sum function but it is treating the zip code like a number and is calculating the value. I want to be able to count how many times each zip code appears in each group.

Here's my code so far (that gets me a calculated value, which is NOT what I want):
grouped_zips<-brazilcust %>%
group_by(customer_state, customer_city) %>%
dplyr::summarise(total_orders_by_city=sum(customer_zip_code_prefix))

Any feedback and suggestion is greatly appreciated.

#2

Does a zipcode occur in more than one city? I am thinking no. So wouldn't table(df$"Zip Code") work?

#3

Is this what you want?

library(tidyverse)

df <- tibble(state = c("US","US","US","US","US","US","US","US","US","US"),
             city = c("A","A","A","A","A","B","B","B","B","B"),
             zip = c(700,800,650,700,700,300,300,400,100,100))

# A tibble: 10 × 3
   state city    zip
   <chr> <chr> <dbl>
 1 US    A       700
 2 US    A       800
 3 US    A       650
 4 US    A       700
 5 US    A       700
 6 US    B       300
 7 US    B       300
 8 US    B       400
 9 US    B       100
10 US    B       100

code

df %>% 
  mutate(zip = factor(zip)) %>% 
  count(state,city,zip)

result

# A tibble: 6 × 4
  state city  zip       n
  <chr> <chr> <fct> <int>
1 US    A     650       1
2 US    A     700       3
3 US    A     800       1
4 US    B     100       2
5 US    B     300       2
6 US    B     400       1
2 Likes
#4

Thanks. I'll give it a try!

#5

if it worked, click the solved button to close.

#6

Thank you for the reminder. I have not tried it yet, but once I do, I will let you and the community know what the results were.

#7

This worked perfectly! Thanks so much for your help. It is greatly appreciated. Now, I'm going to see if I can visualize it using Google Maps.

#8

n_distinct() this function is used to distinguish unique values of a variable.