Getting Started with CUNYsports

Christian Martinez

library(CUNYsports)
library(dplyr)
library(ggplot2)

Introduction

Welcome to CUNYsports, an R package for accessing and exploring collegiate athletics data from the City University of New York (CUNY).

CUNY athletics data are publicly available across individual college athletics websites, but working with these data at scale can require substantial scraping, cleaning, standardization, and organization.

CUNYsports provides a centralized interface for working with cleaned and organized CUNY athletics data directly in R.

The package includes an SQLite database containing information about teams, players, games, seasons, schools, opponents, and other aspects of CUNY athletics. Rather than requiring users to interact with the database directly, CUNYsports provides functions for discovering, documenting, and retrieving the available data.

The basic workflow is:

Discover → Understand → Retrieve → Analyze

The initial release of CUNYsports focuses on CUNY men’s and women’s basketball, with the database designed to support additional sports in the future.

Discovering Available Data

The first step when using CUNYsports is figuring out what data are available.

The cuny_table_names() function

The simplest way to explore the database is with cuny_table_names().

cuny_table_names()
#> # A tibble: 26 × 1
#>    table_name                                         
#>    <chr>                                              
#>  1 basketball_all_cat_leaders_final                   
#>  2 basketball_all_seasons_player_averages_final       
#>  3 basketball_all_seasons_player_conference_final     
#>  4 basketball_all_seasons_player_overall_final        
#>  5 basketball_all_seasons_player_scoring_final        
#>  6 basketball_all_seasons_player_team_averages_final  
#>  7 basketball_all_seasons_player_team_conference_final
#>  8 basketball_all_seasons_player_team_overall_final   
#>  9 basketball_all_seasons_player_team_scoring_final   
#> 10 basketball_all_seasons_team_final                  
#> # ℹ 16 more rows

This returns the names of all tables currently available in the CUNY Sports database.

The database contains both analytical tables and dimension tables. Basketball tables begin with basketball_, while dimension tables such as dim_players and dim_schools provide information used throughout the database.

The cuny_find_tables() function

As the database grows, users may not know the exact name of the table they need.

The cuny_find_tables() function allows users to search the table catalog using a keyword.

For example, we can search for tables related to player data:

cuny_find_tables("player")
#> # A tibble: 14 × 11
#>    table_name              display_name description grain season_coverage source
#>    <chr>                   <chr>        <chr>       <chr> <chr>           <chr> 
#>  1 dim_players             Player Dime… Lookup tab… One … 2012-13 to pre… Deriv…
#>  2 dim_player_year         Player Years Bridge tab… One … All player-sea… Deriv…
#>  3 basketball_all_seasons… All Seasons… Season-lev… One … All seasons re… Scrap…
#>  4 basketball_all_seasons… All Seasons… Season-lev… One … All seasons re… Scrap…
#>  5 basketball_all_seasons… All Seasons… Season-lev… One … All conference… Scrap…
#>  6 basketball_all_seasons… All Seasons… Season-lev… One … All conference… Scrap…
#>  7 basketball_all_seasons… All Seasons… Season-lev… One … All seasons re… Scrap…
#>  8 basketball_all_seasons… All Seasons… Season-lev… One … All seasons re… Scrap…
#>  9 basketball_all_seasons… All Seasons… Season-lev… One … All seasons re… Scrap…
#> 10 basketball_all_seasons… All Seasons… Season-lev… One … All seasons re… Scrap…
#> 11 basketball_game_high_i… Individual … Highest si… One … All seasons re… Scrap…
#> 12 basketball_all_cat_lea… All Categor… Player-sea… One … All seasons re… Scrap…
#> 13 basketball_player_reco… Removed Pla… Audit tabl… One … Seasons repres… Gener…
#> 14 basketball_player_seas… Unified Pla… Consolidat… Like… All player-sea… Deriv…
#> # ℹ 5 more variables: primary_key_candidate <chr>, foreign_keys_needed <chr>,
#> #   likely_uses <chr>, cleaning_priority <chr>, notes <chr>

We could instead search for other topics:

cuny_find_tables("scoring")
#> # A tibble: 2 × 11
#>   table_name               display_name description grain season_coverage source
#>   <chr>                    <chr>        <chr>       <chr> <chr>           <chr> 
#> 1 basketball_all_seasons_… All Seasons… Season-lev… One … All seasons re… Scrap…
#> 2 basketball_all_seasons_… All Seasons… Season-lev… One … All seasons re… Scrap…
#> # ℹ 5 more variables: primary_key_candidate <chr>, foreign_keys_needed <chr>,
#> #   likely_uses <chr>, cleaning_priority <chr>, notes <chr>
cuny_find_tables("game")
#> # A tibble: 18 × 11
#>    table_name              display_name description grain season_coverage source
#>    <chr>                   <chr>        <chr>       <chr> <chr>           <chr> 
#>  1 dim_opponents           Opponent Di… Lookup tab… One … 2012-13 to pre… Deriv…
#>  2 dim_games               Game Dimens… Lookup tab… One … 2012-13 to pre… Deriv…
#>  3 basketball_all_seasons… All Seasons… Season-lev… One … All seasons re… Scrap…
#>  4 basketball_all_seasons… All Seasons… Season-lev… One … All conference… Scrap…
#>  5 basketball_all_seasons… All Seasons… Season-lev… One … All conference… Scrap…
#>  6 basketball_all_seasons… All Seasons… Season-lev… One … All seasons re… Scrap…
#>  7 basketball_all_seasons… All Seasons… Season-lev… One … All seasons re… Scrap…
#>  8 basketball_all_seasons… All Seasons… Season-lev… One … All seasons re… Scrap…
#>  9 basketball_game_by_gam… Game-by-Gam… Game-level… One … All game-level… Scrap…
#> 10 basketball_game_by_gam… Game-by-Gam… Game-level… One … All game-level… Scrap…
#> 11 basketball_game_by_gam… Game-by-Gam… Season-lev… One … All seasons re… Scrap…
#> 12 basketball_game_by_gam… Game-by-Gam… Game-level… One … All game-level… Scrap…
#> 13 basketball_game_by_gam… Game-by-Gam… Season-lev… One … All seasons re… Scrap…
#> 14 basketball_game_by_gam… Game-by-Gam… Game-level… One … All game-level… Scrap…
#> 15 basketball_game_high_t… Team Game H… Highest si… One … All seasons re… Scrap…
#> 16 basketball_game_high_i… Individual … Highest si… One … All seasons re… Scrap…
#> 17 basketball_all_cat_lea… All Categor… Player-sea… One … All seasons re… Scrap…
#> 18 basketball_game_by_gam… Combined Ga… Unified ga… One … All game-level… Left …
#> # ℹ 5 more variables: primary_key_candidate <chr>, foreign_keys_needed <chr>,
#> #   likely_uses <chr>, cleaning_priority <chr>, notes <chr>

This provides a convenient way to discover relevant tables without memorizing database table names.

Understanding a Table

After identifying a table of interest, the next step is understanding what it contains.

For this vignette, we will use:

basketball_all_seasons_player_overall_final

This table contains season-level overall player statistics for CUNY basketball.

The cuny_table_info() function

The cuny_table_info() function provides table-level documentation.

cuny_table_info(
  "basketball_all_seasons_player_overall_final"
)
#> # A tibble: 1 × 11
#>   table_name               display_name description grain season_coverage source
#>   <chr>                    <chr>        <chr>       <chr> <chr>           <chr> 
#> 1 basketball_all_seasons_… All Seasons… Season-lev… One … All seasons re… Scrap…
#> # ℹ 5 more variables: primary_key_candidate <chr>, foreign_keys_needed <chr>,
#> #   likely_uses <chr>, cleaning_priority <chr>, notes <chr>

The returned information describes characteristics such as the table’s:

This is particularly useful when several tables contain related types of statistics.

The cuny_columns_info() function

Once we understand the purpose of the table, we can inspect its individual variables using cuny_columns_info().

cuny_columns_info(
  "basketball_all_seasons_player_overall_final"
)
#> # A tibble: 42 × 8
#>    table_name  column_name suggested_name data_type description example key_type
#>    <chr>       <chr>       <chr>          <chr>     <chr>       <chr>   <chr>   
#>  1 basketball… jersey_num… jersey_number  character Jersey num… 1.0     Descrip…
#>  2 basketball… player_name player_name    character Human-read… Joe Ch… Descrip…
#>  3 basketball… season_lab… season_label   character Human-read… 2025-26 Descrip…
#>  4 basketball… games_play… games_played   integer   Number of … 26.0    Measure 
#>  5 basketball… games_star… games_started  integer   Number of … 26.0    Measure 
#>  6 basketball… total_minu… total_minutes  numeric   Total minu… 790.0   Measure 
#>  7 basketball… minutes_pe… minutes_per_g… numeric   Average mi… 30.4    Measure 
#>  8 basketball… field_goal… field_goals_m… integer   Total fiel… 122.0   Measure 
#>  9 basketball… field_goal… field_goal_at… integer   Total fiel… 374.0   Measure 
#> 10 basketball… field_goal… field_goal_pe… numeric   Proportion… 0.326   Measure 
#> # ℹ 32 more rows
#> # ℹ 1 more variable: notes <chr>

The column catalog provides information about each documented variable, including:

Together, cuny_table_info() and cuny_columns_info() allow users to understand the structure of the data before beginning an analysis.

Retrieving Data

Once we have identified and documented the table we want, we can retrieve it using cuny_pull_table().

player_stats <- cuny_pull_table(
  "basketball_all_seasons_player_overall_final"
)

player_stats |>
  slice_head(n = 6)
#>   jersey_number             player_name season_label games_played games_started
#> 1            21            Adam Elfassi      2011-12           14             0
#> 2            33           Brian McMahon      2011-12           27             0
#> 3            22 Calvin Jurewicz-Johnson      2011-12           28            19
#> 4            20         Chris Beauchamp      2011-12           26            26
#> 5            11              Fuat Yapar      2011-12           28            15
#> 6            25       Granville Gittens      2011-12           18             2
#>   total_minutes minutes_per_game field_goals_made field_goal_attempts
#> 1            91              6.5                6                   8
#> 2           347             12.9               33                  63
#> 3           771             27.5               84                 207
#> 4           863             33.2              160                 366
#> 5           593             21.2               65                 147
#> 6           251             13.9               40                  83
#>   field_goal_percentage three_pointers_made three_point_attempts
#> 1                 0.750                   0                    0
#> 2                 0.524                   0                    0
#> 3                 0.406                  20                   71
#> 4                 0.437                  57                  148
#> 5                 0.442                  23                   68
#> 6                 0.482                   0                    2
#>   three_point_percentage free_throws_made free_throw_attempts
#> 1                  0.000                5                   8
#> 2                  0.000               11                  28
#> 3                  0.282               57                  82
#> 4                  0.385              101                 122
#> 5                  0.338               23                  33
#> 6                  0.000                9                  17
#>   free_throw_percentage total_points points_per_game offensive_rebounds
#> 1                 0.625           17             1.2                  1
#> 2                 0.393           77             2.9                 25
#> 3                 0.695          245             8.8                 30
#> 4                 0.828          478            18.4                 25
#> 5                 0.697          176             6.3                 44
#> 6                 0.529           89             4.9                 20
#>   defensive_rebounds total_rebounds rebounds_per_game personal_fouls assists
#> 1                  9             10               0.7             25       4
#> 2                 65             90               3.3             44      10
#> 3                 75            105               3.8             57      83
#> 4                105            130               5.0             46      69
#> 5                104            148               5.3             69      42
#> 6                 49             69               3.8             36       7
#>   turnovers steals blocks effective_field_goal_percentage
#> 1        11      6      3                           0.750
#> 2        30      5     21                           0.524
#> 3        88     37      1                           0.454
#> 4        79     30     10                           0.515
#> 5        34     31      3                           0.520
#> 6        23     18     15                           0.482
#>   true_shooting_percentage three_point_attempt_rate free_throw_rate
#> 1                    0.738                    0.000           1.000
#> 2                    0.511                    0.000           0.444
#> 3                    0.504                    0.343           0.396
#> 4                    0.569                    0.404           0.333
#> 5                    0.545                    0.463           0.224
#> 6                    0.492                    0.024           0.205
#>   assist_turnover_ratio offensive_rebound_share points_per_game_alt
#> 1                 0.364                   0.100                 1.2
#> 2                 0.333                   0.278                 2.9
#> 3                 0.943                   0.286                 8.8
#> 4                 0.873                   0.192                18.4
#> 5                 1.235                   0.297                 6.3
#> 6                 0.304                   0.290                 4.9
#>   points_per_40_minutes split_type          school gender source_row_id
#> 1                   7.5    overall baruchathletics      M          1141
#> 2                   8.9    overall baruchathletics      M          1139
#> 3                  12.7    overall baruchathletics      M          1132
#> 4                  22.2    overall baruchathletics      M          1130
#> 5                  11.9    overall baruchathletics      M          1134
#> 6                  14.2    overall baruchathletics      M          1136
#>   season_id school_id player_id year_with_team
#> 1         8         1         3              1
#> 2         8         1        26              1
#> 3         8         1        30              1
#> 4         8         1        34              1
#> 5         8         1        67              1
#> 6         8         1        73              1

The result is returned to R and can be used with familiar tools from packages such as dplyr, tidyr, and ggplot2.

For example, men’s and women’s basketball records are stored together and can be distinguished using the gender column.

To work with women’s basketball:

womens_player_stats <- player_stats |>
  filter(gender == "W")

womens_player_stats |>
  slice_head(n = 6)
#>   jersey_number      player_name season_label games_played games_started
#> 1            15     Alicea Ulmer      2011-12           26             0
#> 2            44   Alyscia Jordan      2011-12           28             9
#> 3            32   Camille Voitot      2011-12            2             0
#> 4            34      Fanta Conde      2011-12           12             0
#> 5            24 Folashade Akinde      2011-12           29            20
#> 6            10   Francess Henry      2011-12           23            10
#>   total_minutes minutes_per_game field_goals_made field_goal_attempts
#> 1           225              8.7               34                  96
#> 2           313             11.2               39                  94
#> 3            29             14.5                1                   6
#> 4            47              3.9                1                  11
#> 5           635             21.9              108                 269
#> 6           455             19.8               70                 210
#>   field_goal_percentage three_pointers_made three_point_attempts
#> 1                 0.354                  15                   52
#> 2                 0.415                   0                    0
#> 3                 0.167                   0                    3
#> 4                 0.091                   0                    0
#> 5                 0.401                   0                    3
#> 6                 0.333                  28                  117
#>   three_point_percentage free_throws_made free_throw_attempts
#> 1                  0.288                8                  14
#> 2                  0.000               10                  16
#> 3                  0.000                1                   2
#> 4                  0.000                1                   4
#> 5                  0.000               46                  73
#> 6                  0.239               27                  40
#>   free_throw_percentage total_points points_per_game offensive_rebounds
#> 1                 0.571           91             3.5                 23
#> 2                 0.625           88             3.1                 43
#> 3                 0.500            3             1.5                  2
#> 4                 0.250            3             0.3                  2
#> 5                 0.630          262             9.0                 66
#> 6                 0.675          195             8.5                 18
#>   defensive_rebounds total_rebounds rebounds_per_game personal_fouls assists
#> 1                 35             58               2.2             27       8
#> 2                 71            114               4.1             26       1
#> 3                  2              4               2.0              3       0
#> 4                  1              3               0.3              6       5
#> 5                122            188               6.5             50      36
#> 6                 52             70               3.0             31      31
#>   turnovers steals blocks effective_field_goal_percentage
#> 1        14      6      4                           0.432
#> 2        24     17     34                           0.415
#> 3         3      3      0                           0.167
#> 4         7      3      0                           0.091
#> 5        68     47     16                           0.401
#> 6        47     33     15                           0.400
#>   true_shooting_percentage three_point_attempt_rate free_throw_rate
#> 1                    0.445                    0.542           0.146
#> 2                    0.435                    0.000           0.170
#> 3                    0.218                    0.500           0.333
#> 4                    0.118                    0.000           0.364
#> 5                    0.435                    0.011           0.271
#> 6                    0.428                    0.557           0.190
#>   assist_turnover_ratio offensive_rebound_share points_per_game_alt
#> 1                 0.571                   0.397                 3.5
#> 2                 0.042                   0.377                 3.1
#> 3                 0.000                   0.500                 1.5
#> 4                 0.714                   0.667                 0.2
#> 5                 0.529                   0.351                 9.0
#> 6                 0.660                   0.257                 8.5
#>   points_per_40_minutes split_type          school gender source_row_id
#> 1                  16.2    overall baruchathletics      W          3127
#> 2                  11.2    overall baruchathletics      W          3128
#> 3                   4.1    overall baruchathletics      W          3130
#> 4                   2.6    overall baruchathletics      W          3131
#> 5                  16.5    overall baruchathletics      W          3119
#> 6                  17.1    overall baruchathletics      W          3120
#>   season_id school_id player_id year_with_team
#> 1         8         1         8              1
#> 2         8         1        13              1
#> 3         8         1        32              1
#> 4         8         1        62              1
#> 5         8         1        63              1
#> 6         8         1        65              1

Similarly, men’s basketball can be selected using:

mens_player_stats <- player_stats |>
  filter(gender == "M")

mens_player_stats |>
  slice_head(n = 6)
#>   jersey_number             player_name season_label games_played games_started
#> 1            21            Adam Elfassi      2011-12           14             0
#> 2            33           Brian McMahon      2011-12           27             0
#> 3            22 Calvin Jurewicz-Johnson      2011-12           28            19
#> 4            20         Chris Beauchamp      2011-12           26            26
#> 5            11              Fuat Yapar      2011-12           28            15
#> 6            25       Granville Gittens      2011-12           18             2
#>   total_minutes minutes_per_game field_goals_made field_goal_attempts
#> 1            91              6.5                6                   8
#> 2           347             12.9               33                  63
#> 3           771             27.5               84                 207
#> 4           863             33.2              160                 366
#> 5           593             21.2               65                 147
#> 6           251             13.9               40                  83
#>   field_goal_percentage three_pointers_made three_point_attempts
#> 1                 0.750                   0                    0
#> 2                 0.524                   0                    0
#> 3                 0.406                  20                   71
#> 4                 0.437                  57                  148
#> 5                 0.442                  23                   68
#> 6                 0.482                   0                    2
#>   three_point_percentage free_throws_made free_throw_attempts
#> 1                  0.000                5                   8
#> 2                  0.000               11                  28
#> 3                  0.282               57                  82
#> 4                  0.385              101                 122
#> 5                  0.338               23                  33
#> 6                  0.000                9                  17
#>   free_throw_percentage total_points points_per_game offensive_rebounds
#> 1                 0.625           17             1.2                  1
#> 2                 0.393           77             2.9                 25
#> 3                 0.695          245             8.8                 30
#> 4                 0.828          478            18.4                 25
#> 5                 0.697          176             6.3                 44
#> 6                 0.529           89             4.9                 20
#>   defensive_rebounds total_rebounds rebounds_per_game personal_fouls assists
#> 1                  9             10               0.7             25       4
#> 2                 65             90               3.3             44      10
#> 3                 75            105               3.8             57      83
#> 4                105            130               5.0             46      69
#> 5                104            148               5.3             69      42
#> 6                 49             69               3.8             36       7
#>   turnovers steals blocks effective_field_goal_percentage
#> 1        11      6      3                           0.750
#> 2        30      5     21                           0.524
#> 3        88     37      1                           0.454
#> 4        79     30     10                           0.515
#> 5        34     31      3                           0.520
#> 6        23     18     15                           0.482
#>   true_shooting_percentage three_point_attempt_rate free_throw_rate
#> 1                    0.738                    0.000           1.000
#> 2                    0.511                    0.000           0.444
#> 3                    0.504                    0.343           0.396
#> 4                    0.569                    0.404           0.333
#> 5                    0.545                    0.463           0.224
#> 6                    0.492                    0.024           0.205
#>   assist_turnover_ratio offensive_rebound_share points_per_game_alt
#> 1                 0.364                   0.100                 1.2
#> 2                 0.333                   0.278                 2.9
#> 3                 0.943                   0.286                 8.8
#> 4                 0.873                   0.192                18.4
#> 5                 1.235                   0.297                 6.3
#> 6                 0.304                   0.290                 4.9
#>   points_per_40_minutes split_type          school gender source_row_id
#> 1                   7.5    overall baruchathletics      M          1141
#> 2                   8.9    overall baruchathletics      M          1139
#> 3                  12.7    overall baruchathletics      M          1132
#> 4                  22.2    overall baruchathletics      M          1130
#> 5                  11.9    overall baruchathletics      M          1134
#> 6                  14.2    overall baruchathletics      M          1136
#>   season_id school_id player_id year_with_team
#> 1         8         1         3              1
#> 2         8         1        26              1
#> 3         8         1        30              1
#> 4         8         1        34              1
#> 5         8         1        67              1
#> 6         8         1        73              1

Putting the Workflow Together

At this point, we have completed the basic CUNYsports workflow.

We:

  1. Discovered available tables using cuny_table_names()
  2. Searched for relevant data using cuny_find_tables()
  3. Learned about a table using cuny_table_info()
  4. Examined its variables using cuny_columns_info()
  5. Retrieved the data using cuny_pull_table()

In practice, the workflow can be written as:

# Discover
cuny_find_tables("player")
#> # A tibble: 14 × 11
#>    table_name              display_name description grain season_coverage source
#>    <chr>                   <chr>        <chr>       <chr> <chr>           <chr> 
#>  1 dim_players             Player Dime… Lookup tab… One … 2012-13 to pre… Deriv…
#>  2 dim_player_year         Player Years Bridge tab… One … All player-sea… Deriv…
#>  3 basketball_all_seasons… All Seasons… Season-lev… One … All seasons re… Scrap…
#>  4 basketball_all_seasons… All Seasons… Season-lev… One … All seasons re… Scrap…
#>  5 basketball_all_seasons… All Seasons… Season-lev… One … All conference… Scrap…
#>  6 basketball_all_seasons… All Seasons… Season-lev… One … All conference… Scrap…
#>  7 basketball_all_seasons… All Seasons… Season-lev… One … All seasons re… Scrap…
#>  8 basketball_all_seasons… All Seasons… Season-lev… One … All seasons re… Scrap…
#>  9 basketball_all_seasons… All Seasons… Season-lev… One … All seasons re… Scrap…
#> 10 basketball_all_seasons… All Seasons… Season-lev… One … All seasons re… Scrap…
#> 11 basketball_game_high_i… Individual … Highest si… One … All seasons re… Scrap…
#> 12 basketball_all_cat_lea… All Categor… Player-sea… One … All seasons re… Scrap…
#> 13 basketball_player_reco… Removed Pla… Audit tabl… One … Seasons repres… Gener…
#> 14 basketball_player_seas… Unified Pla… Consolidat… Like… All player-sea… Deriv…
#> # ℹ 5 more variables: primary_key_candidate <chr>, foreign_keys_needed <chr>,
#> #   likely_uses <chr>, cleaning_priority <chr>, notes <chr>

# Understand
cuny_table_info(
  "basketball_all_seasons_player_overall_final"
)
#> # A tibble: 1 × 11
#>   table_name               display_name description grain season_coverage source
#>   <chr>                    <chr>        <chr>       <chr> <chr>           <chr> 
#> 1 basketball_all_seasons_… All Seasons… Season-lev… One … All seasons re… Scrap…
#> # ℹ 5 more variables: primary_key_candidate <chr>, foreign_keys_needed <chr>,
#> #   likely_uses <chr>, cleaning_priority <chr>, notes <chr>

cuny_columns_info(
  "basketball_all_seasons_player_overall_final"
)
#> # A tibble: 42 × 8
#>    table_name  column_name suggested_name data_type description example key_type
#>    <chr>       <chr>       <chr>          <chr>     <chr>       <chr>   <chr>   
#>  1 basketball… jersey_num… jersey_number  character Jersey num… 1.0     Descrip…
#>  2 basketball… player_name player_name    character Human-read… Joe Ch… Descrip…
#>  3 basketball… season_lab… season_label   character Human-read… 2025-26 Descrip…
#>  4 basketball… games_play… games_played   integer   Number of … 26.0    Measure 
#>  5 basketball… games_star… games_started  integer   Number of … 26.0    Measure 
#>  6 basketball… total_minu… total_minutes  numeric   Total minu… 790.0   Measure 
#>  7 basketball… minutes_pe… minutes_per_g… numeric   Average mi… 30.4    Measure 
#>  8 basketball… field_goal… field_goals_m… integer   Total fiel… 122.0   Measure 
#>  9 basketball… field_goal… field_goal_at… integer   Total fiel… 374.0   Measure 
#> 10 basketball… field_goal… field_goal_pe… numeric   Proportion… 0.326   Measure 
#> # ℹ 32 more rows
#> # ℹ 1 more variable: notes <chr>

# Retrieve
players <- cuny_pull_table(
  "basketball_all_seasons_player_overall_final"
)

From here, the data are ready for analysis.

Real World Example

Suppose we are interested in exploring scoring across CUNY basketball.

Instead of beginning with the underlying athletics websites, scraping the data, and standardizing records across schools and seasons, we can search the existing CUNY Sports database.

First, let’s find tables related to scoring.

cuny_find_tables("scoring")
#> # A tibble: 2 × 11
#>   table_name               display_name description grain season_coverage source
#>   <chr>                    <chr>        <chr>       <chr> <chr>           <chr> 
#> 1 basketball_all_seasons_… All Seasons… Season-lev… One … All seasons re… Scrap…
#> 2 basketball_all_seasons_… All Seasons… Season-lev… One … All seasons re… Scrap…
#> # ℹ 5 more variables: primary_key_candidate <chr>, foreign_keys_needed <chr>,
#> #   likely_uses <chr>, cleaning_priority <chr>, notes <chr>

We can then inspect the player scoring table:

cuny_table_info(
  "basketball_all_seasons_player_scoring_final"
)
#> # A tibble: 1 × 11
#>   table_name               display_name description grain season_coverage source
#>   <chr>                    <chr>        <chr>       <chr> <chr>           <chr> 
#> 1 basketball_all_seasons_… All Seasons… Season-lev… One … All seasons re… Scrap…
#> # ℹ 5 more variables: primary_key_candidate <chr>, foreign_keys_needed <chr>,
#> #   likely_uses <chr>, cleaning_priority <chr>, notes <chr>

And examine its available columns:

cuny_columns_info(
  "basketball_all_seasons_player_scoring_final"
)
#> # A tibble: 29 × 8
#>    table_name  column_name suggested_name data_type description example key_type
#>    <chr>       <chr>       <chr>          <chr>     <chr>       <chr>   <chr>   
#>  1 basketball… jersey_num… jersey_number  character Jersey num… 1.0     Descrip…
#>  2 basketball… player_name player_name    character Human-read… Joe Ch… Descrip…
#>  3 basketball… games_play… games_played   integer   Number of … 26.0    Measure 
#>  4 basketball… games_star… games_started  integer   Number of … 26.0    Measure 
#>  5 basketball… total_minu… total_minutes  numeric   Total minu… 790.0   Measure 
#>  6 basketball… field_goal… field_goals_m… integer   Total fiel… 122.0   Measure 
#>  7 basketball… field_goal… field_goal_at… integer   Total fiel… 374.0   Measure 
#>  8 basketball… field_goal… field_goal_pe… numeric   Proportion… 0.326   Measure 
#>  9 basketball… three_poin… three_pointer… integer   Total thre… 54.0    Measure 
#> 10 basketball… three_poin… three_point_a… integer   Total thre… 147.0   Measure 
#> # ℹ 19 more rows
#> # ℹ 1 more variable: notes <chr>

Now we can retrieve the data:

scoring <- cuny_pull_table(
  "basketball_all_seasons_player_scoring_final"
)

scoring |>
  slice_head(n = 6)
#>   jersey_number             player_name games_played games_started
#> 1            21            Adam Elfassi           14             0
#> 2            33           Brian McMahon           27             0
#> 3            22 Calvin Jurewicz-Johnson           28            19
#> 4            20         Chris Beauchamp           26            26
#> 5            11              Fuat Yapar           28            15
#> 6            25       Granville Gittens           18             2
#>   total_minutes field_goals_made field_goal_attempts field_goal_percentage
#> 1            91                6                   8                 0.750
#> 2           347               33                  63                 0.524
#> 3           771               84                 207                 0.406
#> 4           863              160                 366                 0.437
#> 5           593               65                 147                 0.442
#> 6           251               40                  83                 0.482
#>   three_pointers_made three_point_attempts three_point_percentage
#> 1                   0                    0                  0.000
#> 2                   0                    0                  0.000
#> 3                  20                   71                  0.282
#> 4                  57                  148                  0.385
#> 5                  23                   68                  0.338
#> 6                   0                    2                  0.000
#>   free_throws_made free_throw_attempts free_throw_percentage total_points
#> 1                5                   8                 0.625           17
#> 2               11                  28                 0.393           77
#> 3               57                  82                 0.695          245
#> 4              101                 122                 0.828          478
#> 5               23                  33                 0.697          176
#> 6                9                  17                 0.529           89
#>   points_per_game season_label effective_field_goal_percentage
#> 1             1.2      2011-12                           0.750
#> 2             2.9      2011-12                           0.524
#> 3             8.8      2011-12                           0.454
#> 4            18.4      2011-12                           0.515
#> 5             6.3      2011-12                           0.520
#> 6             4.9      2011-12                           0.482
#>   true_shooting_percentage three_point_attempt_rate free_throw_rate
#> 1                    0.738                    0.000           1.000
#> 2                    0.511                    0.000           0.444
#> 3                    0.504                    0.343           0.396
#> 4                    0.569                    0.404           0.333
#> 5                    0.545                    0.463           0.224
#> 6                    0.492                    0.024           0.205
#>   points_per_40_minutes minutes_per_game split_type          school gender
#> 1                   7.5              6.5    scoring baruchathletics      M
#> 2                   8.9             12.9    scoring baruchathletics      M
#> 3                  12.7             27.5    scoring baruchathletics      M
#> 4                  22.2             33.2    scoring baruchathletics      M
#> 5                  11.9             21.2    scoring baruchathletics      M
#> 6                  14.2             13.9    scoring baruchathletics      M
#>   source_row_id season_id school_id player_id year_with_team
#> 1          1139         8         1         3              1
#> 2          1137         8         1        26              1
#> 3          1131         8         1        30              1
#> 4          1128         8         1        34              1
#> 5          1132         8         1        67              1
#> 6          1134         8         1        73              1

Because the data are now in R, we can use normal dplyr operations to explore them.

For example, we can examine the distribution of points per game:

scoring |>
  filter(!is.na(points_per_game)) |>
  summarize(
    players = n(),
    average_ppg = mean(points_per_game),
    median_ppg = median(points_per_game),
    maximum_ppg = max(points_per_game)
  )
#>   players average_ppg median_ppg maximum_ppg
#> 1    3940    5.586802          4        30.9

We can also compare the distribution of scoring between men’s and women’s basketball.

scoring |>
  filter(!is.na(points_per_game)) |>
  group_by(gender) |>
  summarize(
    players = n(),
    average_ppg = mean(points_per_game),
    median_ppg = median(points_per_game),
    maximum_ppg = max(points_per_game),
    .groups = "drop"
  )
#> # A tibble: 2 × 5
#>   gender players average_ppg median_ppg maximum_ppg
#>   <chr>    <int>       <dbl>      <dbl>       <dbl>
#> 1 M         2203        5.63          4        29.1
#> 2 W         1737        5.53          4        30.9

Mini Analysis

Finally, we can visualize the distribution of player scoring.

scoring |>
  filter(!is.na(points_per_game)) |>
  ggplot(aes(x = points_per_game)) +
  geom_histogram(bins = 30) +
  facet_wrap(~ gender) +
  theme_minimal() +
  labs(
    title = "Player Scoring Across CUNY Basketball",
    subtitle = "Men's and Women's Basketball",
    x = "Points Per Game",
    y = "Number of Player-Seasons"
  )
Histogram showing the distribution of player points per game in CUNY basketball, separated into men's and women's basketball.

Distribution of player points per game in CUNY basketball.

This simple example demonstrates how CUNYsports allows users to move directly from data discovery to analysis using standard R workflows.

Direct Database Access

For most users, cuny_pull_table() provides the easiest way to retrieve data.

Advanced users may instead want to interact directly with the SQLite database.

The cuny_db() function opens a database connection:

con <- cuny_db()

DBI::dbListTables(con)
#>  [1] "basketball_all_cat_leaders_final"                   
#>  [2] "basketball_all_seasons_player_averages_final"       
#>  [3] "basketball_all_seasons_player_conference_final"     
#>  [4] "basketball_all_seasons_player_overall_final"        
#>  [5] "basketball_all_seasons_player_scoring_final"        
#>  [6] "basketball_all_seasons_player_team_averages_final"  
#>  [7] "basketball_all_seasons_player_team_conference_final"
#>  [8] "basketball_all_seasons_player_team_overall_final"   
#>  [9] "basketball_all_seasons_player_team_scoring_final"   
#> [10] "basketball_all_seasons_team_final"                  
#> [11] "basketball_game_by_game_combined"                   
#> [12] "basketball_game_by_game_comparison_final"           
#> [13] "basketball_game_by_game_opponents_final"            
#> [14] "basketball_game_by_game_opponents_overall_final"    
#> [15] "basketball_game_by_game_team_final"                 
#> [16] "basketball_game_by_game_team_overall_final"         
#> [17] "basketball_game_high_individual_final"              
#> [18] "basketball_game_high_team_final"                    
#> [19] "basketball_player_records_removed"                  
#> [20] "basketball_player_season_stats"                     
#> [21] "dim_games"                                          
#> [22] "dim_opponents"                                      
#> [23] "dim_player_year"                                    
#> [24] "dim_players"                                        
#> [25] "dim_schools"                                        
#> [26] "dim_seasons"

DBI::dbDisconnect(con)

This can be particularly useful when users want to perform database queries before loading data into memory.

For example, dplyr can work directly with a database table:

con <- cuny_db()

player_db <- dplyr::tbl(
  con,
  "basketball_all_seasons_player_overall_final"
)

player_db |>
  filter(gender == "W") |>
  select(player_name, season_label, school_id, points_per_game) |>
  head()
#> # Source:   SQL [?? x 4]
#> # Database: sqlite 3.53.3 [/private/var/folders/zy/hmwzxgcn60n62sdmsjzprx840000gn/T/RtmpnJRjyQ/Rinst31ef5db0dca9/CUNYsports/extdata/cuny-sports.db]
#>   player_name      season_label school_id points_per_game
#>   <chr>            <chr>            <int>           <dbl>
#> 1 Alicea Ulmer     2011-12              1             3.5
#> 2 Alyscia Jordan   2011-12              1             3.1
#> 3 Camille Voitot   2011-12              1             1.5
#> 4 Fanta Conde      2011-12              1             0.3
#> 5 Folashade Akinde 2011-12              1             9  
#> 6 Francess Henry   2011-12              1             8.5

DBI::dbDisconnect(con)

This approach allows SQLite to perform operations before the resulting data are collected into R.

Rule of Thumb

The package functions serve different parts of the data workflow.

In general:

For most analyses, the first five functions will provide everything needed to find and begin working with CUNY athletics data.

Summary

CUNYsports provides a centralized interface for accessing and exploring CUNY athletics data in R.

Rather than requiring users to independently scrape and standardize statistics across athletics websites, the package provides cleaned data through an included SQLite database together with tools for understanding its structure.

The basic workflow is:

Discover → Understand → Retrieve → Analyze

As demonstrated in this vignette, users can search for relevant data, inspect table and column documentation, retrieve complete datasets, and immediately begin analyzing them using familiar R tools.

Although the initial release focuses on men’s and women’s basketball, the underlying database and package infrastructure are designed to support additional CUNY sports as the project grows.

How to Cite

If you use CUNYsports for research or educational purposes, please cite it as follows:

Martinez C (2026). CUNYsports: Access and Explore CUNY Athletics Data. R package version 0.1.0.