Group column values by a set numeric difference in R (big dataset)

Becky Heath 0
Viewed 32

I want a script to put column values in groups with a maximum range of 10. So that all values in the group are <10 different from each other. I'm trying to get a script that defines the groups, counts how many values are in each group, and find the mean.

So if I had this df:

cat1 = c(85, 60, 60, 55, 55, 15, 0, 35, 35 )
cat2 = c("a","a","a","a","a","a","a","a","a")
df <- data.frame(cat1, cat2)

  cat1 cat2
1   85    a
2   60    a
3   60    a
4   55    a
5   55    a
6   15    a
7    0    a
8   35    a 
9   35    a

the output would be:

numValues cat1avg 
      1    85
      4    57.5
      1    15
      1     0
      2    35

I followed the top-rated answer here, but I'm getting weird outputs on some of my groups. Specifically, it doesn't seem like the script is properly adding the number of values in each group.

The only way I can think to do it is through for loops with a million if statements, and I have 1,000+ of these little dfs that I need to summarise.

I was also thinking of doing a fuzzy count. But I haven't been able to find anything about that anywhere either.

I also can't just cut up the cat1 range into groups of 10 and just allocate them all into a bin because it's less about the level and more about how close they are to each other.

Thanks!

data-wrangling tidyr dplyr dataframe r
1 Answers

I'd suggest working at this in the opposite order. Instead of assigning groups based on distance and seeing how many groups there are, we could specify a number of groups (k) and ask R to pick the most distinct clusterings, and compare how well those clusterings fit our purpose. There is a built-in algorithm in R, kmeans, to do this for us.

Let's say we expect between 1 and 6 groups. There are only 6 unique values when I run unique(cat1) so it can't be more than that. We can then use map from purrr in tidyverse to use each of 1:6 in a kmeans algorithm, and we can use augment from broom to extract the output from that in a tidy way.

library(tidyverse); library(broom)
kclusts <- tibble(k = 1:6) %>%
  mutate(kclust = map(k, ~kmeans(cat1, .x)),
         augmented = map(kclust, augment, df)
  )

This will create a nested table with the results we want inside the augmented column. Let's pull those out:

assignments <- kclusts %>% 
  unnest(cols = c(augmented))

We could visualize these like so. Note that with k = 1, everything is in cluster 1. With k = 5, the 55 + 60s are paired. I think the trivial k = 6 case is just left out.

ggplot(assignments, aes(x = cat1, y = cat2)) +
  geom_point(aes(color = .cluster), alpha = 0.8) + 
  facet_wrap(~ k)

enter image description here

We could see how much range is in each cluster produced in each case, and find the widest range cluster for each k. We see that dividing into four groups would have at least 15 range (see the 4 facet in the chart above), but 5 groups would be adequate to keep the within-cluster range under 5.

assignments %>%
  group_by(k, .cluster) %>%
  summarize(range = max(cat1) - min(cat1)) %>%
  summarize(max_range = max(range))

# A tibble: 6 × 2
      k max_range
  <int>     <dbl>
1     1        85
2     2        35
3     3        30
4     4        15
5     5         5
6     6         0

And finally:

assignments %>%
  filter(k == 5) %>%
  group_by(.cluster) %>%
  summarize(numValues = n(),
            cat1avg = mean(cat1))


  .cluster numValues cat1avg
  <fct>        <int>   <dbl>
1 1                1    85  
2 2                1    15  
3 3                2    35  
4 4                4    57.5
5 5                1     0
Jon Spring0
Related