R: Create group id in sparklyr dataframe with varying levels of aggregation

Adriana LE 0
Viewed 38

I have a sparklyr dataframe similar to the one generated with the code below.

# Load libraries
library(sparklyr)
library(dplyr)
library(magrittr)

# Connect to spark
sc <- spark_connect(master = "local")

# Creating example data
df <- read.table(text = "firm sector chapter
A         x     11
B         x     12
C         y     21
D         x     11
E         z     31
F         y     22
G         z     32", header=TRUE)

# Copying data to spark instance
sdf <- copy_to(sc,df, name = "sdf", overwrite=TRUE)

I want to create a group_id by sector such that:

  1. firms in sector y each belong to their own group, and
  2. firms in other sectors are grouped into a single id,

resulting in the following table

# Source: spark<sdf2> [?? x 4]
  firm  sector chapter group
  <chr> <chr>    <int> <int>
1 A     x           11     1
2 B     x           12     1
3 C     y           21     2
4 D     x           11     1
5 E     z           31     4
6 F     y           22     3
7 G     z           32     4

But so far, I'm struggling even with creating a group_id at the sector level. I have tried:

  • sdf %>% mutate(group_id = group_indices(sector))
  • sdf %>% group_by(sector) %>% mutate(group_id = cur_group_id())
  • sdf %>% group_by(sector) %>% mutate(group_id = seq_along(sector))

But I get error messages. How can I achieve this in SparklyR?

sparklyr grouping dplyr dataframe r
1 Answers

In the old days the way to make a numeric group id was to convert a factor to numeric so this should still work (without any group_by(.) ) but factors are not a Spark data type so perhaps not correct, unless the operation sdf$(...) returns a character vector to R for processing.

#  sdf$group_id <- as.numeric( factor(sdf$sector) )

This has been requested in the past at least in the tidyverse word and this thread (https://github.com/tidyverse/dplyr/issues/1185) suggests this might work:

 df$group_id <- sdf %>% group_by(sector) %>% group_indices

Neither of these is tested. I started to install Spark but the directions seemed incomplete. They did imply that I needed to install Hadoop which had similarly incomplete installation docs. So you audience might be limited.

IRTFM0
Related