Reading and Preparing the Data

First read the data from the Excel file. In the notation, we will index Events by i, and Members (or more precisely, Email) by j. The preferences data set has rankings and number of tickets asked, both of which are indexed over both i and j.

Let’s start by defining 3 metrics for each event:

S_i = # seats made available for event i M_i = # members asking for seats Req_ij = number of event i seats requested by member j

Now with this, partition the event list into 3 sets E : easy case, Req_i < S_i G : good case, M_i < S_i H : hard case, M_i > S_i

Assignments

The Easy Ones

For all events in E, assign everyone as many seats as they asked for. For each assignment, increment member score Score_j by (1/r_ij)*Req_ij, where r_ij is rank and Req_ij is number of seats asked.

These assignments are ready to go. Focus on the Email, Event, and Allot columns. The column called “Score_j” is just a way of keeping of the total value of allocations made to member j so far. Score_ij= max(5, 10 - Rank_ij).

Here, since a 2nd seat is assigned only when there’s no other demand for it, we do not alter the score due to that 2nd seat. (It was not a scarce resource)

Test
Event	Email	Allot	Req_ij	Score_j
Kinetech Arts \| 3/09/2022 \| 8:30 PM	mcguzel@ucdavis.edu	2	2	19
Kinetech Arts \| 3/10/2022 \| 7:30 PM	boving@ucdavis.edu	2	2	9
Kinetech Arts \| 3/10/2022 \| 7:30 PM	lcamp@ucdavis.edu	1	1	43
Kinetech Arts \| 3/10/2022 \| 7:30 PM	hemantb@ucdavis.edu	2	2	25
Kinetech Arts \| 3/10/2022 \| 7:30 PM	sgini@ucdavis.edu	1	1	27
Kinetech Arts \| 3/10/2022 \| 8:30 PM	ejowhite@ucdavis.edu	2	2	29
Dresher Davel Invented Instrument Duo \| 3/12/2022 \| 7:30 PM	twpatten@ucdavis.edu	1	1	29
Dresher Davel Invented Instrument Duo \| 3/12/2022 \| 7:30 PM	ejowhite@ucdavis.edu	2	2	29
Academy of St Martin in the Fields with Joshua Bell \| 3/12/2022 \| 7:30 PM	ekaplan@ucdavis.edu	2	2	26
Academy of St Martin in the Fields with Joshua Bell \| 3/12/2022 \| 7:30 PM	mcguzel@ucdavis.edu	2	2	19
Academy of St Martin in the Fields with Joshua Bell \| 3/12/2022 \| 7:30 PM	tdiacova@ucdavis.edu	2	2	42
Matthew Whitaker Quartet \| 3/25/2022 \| 7:30 PM	ekaplan@ucdavis.edu	2	2	26
Matthew Whitaker Quartet \| 3/25/2022 \| 7:30 PM	molmccarthy@ucdavis.edu	1	1	18
Matthew Whitaker Quartet \| 3/25/2022 \| 7:30 PM	Mbrunet@ucdavis.edu	2	2	27
Matthew Whitaker Quartet \| 3/25/2022 \| 7:30 PM	tdiacova@ucdavis.edu	2	2	42
Matthew Whitaker Quartet \| 3/25/2022 \| 7:30 PM	ejowhite@ucdavis.edu	2	2	29
Matthew Whitaker Quartet \| 3/25/2022 \| 7:30 PM	lcamp@ucdavis.edu	1	1	43
Matthew Whitaker Quartet \| 3/25/2022 \| 7:30 PM	sgini@ucdavis.edu	1	1	27
Dr. Ibram X. Kendi \| 3/31/2022 \| 7:30 PM	ejowhite@ucdavis.edu	2	2	29
Dr. Ibram X. Kendi \| 3/31/2022 \| 7:30 PM	tdiacova@ucdavis.edu	2	2	42
Dr. Ibram X. Kendi \| 3/31/2022 \| 7:30 PM	hemantb@ucdavis.edu	2	2	25
Dr. Ibram X. Kendi \| 3/31/2022 \| 7:30 PM	sgini@ucdavis.edu	1	1	27
Dr. Ibram X. Kendi \| 3/31/2022 \| 7:30 PM	Mbrunet@ucdavis.edu	2	2	27
Dr. Ibram X. Kendi \| 3/31/2022 \| 7:30 PM	twpatten@ucdavis.edu	1	1	29
Ashwini Bhatt & Forrest Gander \| 4/15/2022 \| 7:30 PM	jkmlee@ucdavis.edu	2	2	22
Ashwini Bhatt & Forrest Gander \| 4/15/2022 \| 7:30 PM	hemantb@ucdavis.edu	2	2	25
Ashwini Bhatt & Forrest Gander \| 4/15/2022 \| 7:30 PM	twpatten@ucdavis.edu	1	1	29
Ashwini Bhatt & Forrest Gander \| 4/15/2022 \| 7:30 PM	lcamp@ucdavis.edu	1	1	43
Christopher Taylor, Piano \| 4/22/2022 \| 7:30 PM	mcguzel@ucdavis.edu	2	2	19
Christopher Taylor, Piano \| 4/24/2022 \| 2:00 PM	aiberlot@ucdavis.edu	1	1	25
Christopher Taylor, Piano \| 4/24/2022 \| 2:00 PM	emalliende@ucdavis.edu	2	2	9
Christopher Taylor, Piano \| 4/24/2022 \| 2:00 PM	tdiacova@ucdavis.edu	2	2	42
Christopher Taylor, Piano \| 4/24/2022 \| 2:00 PM	lcamp@ucdavis.edu	1	1	43
Ashwini Bhatt & Forrest Gander \| 4/28/2022 \| 7:30 PM	jkmlee@ucdavis.edu	2	2	22
Lara Downes \| 5/06/2022 \| 7:30 PM	aiberlot@ucdavis.edu	1	1	25
Lara Downes \| 5/06/2022 \| 7:30 PM	tdiacova@ucdavis.edu	2	2	42
Lara Downes \| 5/06/2022 \| 7:30 PM	twpatten@ucdavis.edu	1	1	29
Alexander String Quartet 3 \| 5/15/2022 \| 2:00 PM	ladebartolo@ucdavis.edu	2	2	9
Alexander String Quartet 3 \| 5/15/2022 \| 2:00 PM	aiberlot@ucdavis.edu	1	1	25
Alexander String Quartet 3 \| 5/15/2022 \| 2:00 PM	ekaplan@ucdavis.edu	1	1	26
Alexander String Quartet 3 \| 5/15/2022 \| 2:00 PM	Mbrunet@ucdavis.edu	2	2	27
Alexander String Quartet 3 \| 5/15/2022 \| 2:00 PM	jkmlee@ucdavis.edu	2	2	22
Alexander String Quartet 3 \| 5/15/2022 \| 2:00 PM	molmccarthy@ucdavis.edu	1	1	18
Alexander String Quartet 3 \| 5/15/2022 \| 2:00 PM	lcamp@ucdavis.edu	1	1	43

The Good Ones: Enough Tickets for Members (but not Guests)

In the next set you can assign 1 ticket to each member who requested as seat, but some of them asked for two, and we’re not ready to assign the 2nd ticket yet. It could still occur, after we examine the remaining shows where there are stronger conflicts (requests exceed avaialble seats).

Test
Event	Email	Allot	Req_ij	Score_j
Ballet Folklorico de Mexico \| 3/18/2022 \| 7:30 PM	heamartin@ucdavis.edu	1	2	18
Ballet Folklorico de Mexico \| 3/18/2022 \| 7:30 PM	molmccarthy@ucdavis.edu	1	1	27
Ballet Folklorico de Mexico \| 3/18/2022 \| 7:30 PM	tdiacova@ucdavis.edu	1	2	36
Ballet Folklorico de Mexico \| 3/18/2022 \| 7:30 PM	twpatten@ucdavis.edu	1	2	27
Ballet Folklorico de Mexico \| 3/18/2022 \| 7:30 PM	hemantb@ucdavis.edu	1	2	27
Ballet Folklorico de Mexico \| 3/18/2022 \| 7:30 PM	sgini@ucdavis.edu	1	2	36
Ballet Folklorico de Mexico \| 3/18/2022 \| 7:30 PM	ejowhite@ucdavis.edu	1	2	36
Heather McGhee \| 4/03/2022 \| 7:30 PM	lasanmartin@ucdavis.edu	1	2	18
Heather McGhee \| 4/03/2022 \| 7:30 PM	ejowhite@ucdavis.edu	1	2	36
Heather McGhee \| 4/03/2022 \| 7:30 PM	ladebartolo@ucdavis.edu	1	2	9
Heather McGhee \| 4/03/2022 \| 7:30 PM	emalliende@ucdavis.edu	1	2	18
Heather McGhee \| 4/03/2022 \| 7:30 PM	twpatten@ucdavis.edu	1	1	27
Heather McGhee \| 4/03/2022 \| 7:30 PM	lcamp@ucdavis.edu	1	2	27
Heather McGhee \| 4/03/2022 \| 7:30 PM	sgini@ucdavis.edu	1	1	36
Heather McGhee \| 4/03/2022 \| 7:30 PM	tdiacova@ucdavis.edu	1	2	36
Las Cafeteras \| 4/28/2022 \| 7:30 PM	hemantb@ucdavis.edu	1	2	27
Las Cafeteras \| 4/28/2022 \| 7:30 PM	sgini@ucdavis.edu	1	2	36
Las Cafeteras \| 4/28/2022 \| 7:30 PM	jkmlee@ucdavis.edu	1	2	9
Las Cafeteras \| 4/28/2022 \| 7:30 PM	Mbrunet@ucdavis.edu	1	2	9
Las Cafeteras \| 4/28/2022 \| 7:30 PM	mcguzel@ucdavis.edu	1	2	9
Las Cafeteras \| 4/28/2022 \| 7:30 PM	boving@ucdavis.edu	1	2	9
Las Cafeteras \| 4/28/2022 \| 7:30 PM	lcamp@ucdavis.edu	1	2	27
Las Cafeteras \| 4/28/2022 \| 7:30 PM	ekaplan@ucdavis.edu	1	1	9
Las Cafeteras \| 4/28/2022 \| 7:30 PM	molmccarthy@ucdavis.edu	1	1	27
Las Cafeteras \| 4/28/2022 \| 7:30 PM	ejowhite@ucdavis.edu	1	2	36
Las Cafeteras \| 4/28/2022 \| 7:30 PM	tdiacova@ucdavis.edu	1	2	36
David Sedaris \| 5/01/2022 \| 7:30 PM	lcamp@ucdavis.edu	1	1	27
David Sedaris \| 5/01/2022 \| 7:30 PM	twpatten@ucdavis.edu	1	2	27
David Sedaris \| 5/01/2022 \| 7:30 PM	lasanmartin@ucdavis.edu	1	2	18
David Sedaris \| 5/01/2022 \| 7:30 PM	heamartin@ucdavis.edu	1	2	18
David Sedaris \| 5/01/2022 \| 7:30 PM	molmccarthy@ucdavis.edu	1	1	27
David Sedaris \| 5/01/2022 \| 7:30 PM	emalliende@ucdavis.edu	1	2	18
David Sedaris \| 5/01/2022 \| 7:30 PM	hemantb@ucdavis.edu	1	2	27
David Sedaris \| 5/01/2022 \| 7:30 PM	sgini@ucdavis.edu	1	1	36
David Sedaris \| 5/01/2022 \| 7:30 PM	ejowhite@ucdavis.edu	1	2	36
David Sedaris \| 5/01/2022 \| 7:30 PM	tdiacova@ucdavis.edu	1	2	36

Events and Members that Still Need to be Resolved

Before moving on, let’s also generate the “residual demand” from the “Good” group of events. This is the list of requests for two seats whereas we only assigned one.

Test
Event	Email	S_i	Req_ij	Rank_ij	Req_i	M_i
Ballet Folklorico de Mexico \| 3/18/2022 \| 7:30 PM	heamartin@ucdavis.edu	5	1	1	6	6
Ballet Folklorico de Mexico \| 3/18/2022 \| 7:30 PM	tdiacova@ucdavis.edu	5	1	2	6	6
Ballet Folklorico de Mexico \| 3/18/2022 \| 7:30 PM	twpatten@ucdavis.edu	5	1	2	6	6
Ballet Folklorico de Mexico \| 3/18/2022 \| 7:30 PM	hemantb@ucdavis.edu	5	1	3	6	6
Ballet Folklorico de Mexico \| 3/18/2022 \| 7:30 PM	sgini@ucdavis.edu	5	1	3	6	6
Ballet Folklorico de Mexico \| 3/18/2022 \| 7:30 PM	ejowhite@ucdavis.edu	5	1	7	6	6
Heather McGhee \| 4/03/2022 \| 7:30 PM	lasanmartin@ucdavis.edu	4	1	1	6	6
Heather McGhee \| 4/03/2022 \| 7:30 PM	ejowhite@ucdavis.edu	4	1	2	6	6
Heather McGhee \| 4/03/2022 \| 7:30 PM	ladebartolo@ucdavis.edu	4	1	2	6	6
Heather McGhee \| 4/03/2022 \| 7:30 PM	emalliende@ucdavis.edu	4	1	2	6	6
Heather McGhee \| 4/03/2022 \| 7:30 PM	lcamp@ucdavis.edu	4	1	5	6	6
Heather McGhee \| 4/03/2022 \| 7:30 PM	tdiacova@ucdavis.edu	4	1	6	6	6
David Sedaris \| 5/01/2022 \| 7:30 PM	twpatten@ucdavis.edu	2	1	1	7	7
David Sedaris \| 5/01/2022 \| 7:30 PM	lasanmartin@ucdavis.edu	2	1	2	7	7
David Sedaris \| 5/01/2022 \| 7:30 PM	heamartin@ucdavis.edu	2	1	2	7	7
David Sedaris \| 5/01/2022 \| 7:30 PM	emalliende@ucdavis.edu	2	1	3	7	7
David Sedaris \| 5/01/2022 \| 7:30 PM	hemantb@ucdavis.edu	2	1	5	7	7
David Sedaris \| 5/01/2022 \| 7:30 PM	ejowhite@ucdavis.edu	2	1	8	7	7
David Sedaris \| 5/01/2022 \| 7:30 PM	tdiacova@ucdavis.edu	2	1	8	7	7
Las Cafeteras \| 4/28/2022 \| 7:30 PM	hemantb@ucdavis.edu	1	1	1	9	9
Las Cafeteras \| 4/28/2022 \| 7:30 PM	sgini@ucdavis.edu	1	1	1	9	9
Las Cafeteras \| 4/28/2022 \| 7:30 PM	jkmlee@ucdavis.edu	1	1	1	9	9
Las Cafeteras \| 4/28/2022 \| 7:30 PM	Mbrunet@ucdavis.edu	1	1	1	9	9
Las Cafeteras \| 4/28/2022 \| 7:30 PM	mcguzel@ucdavis.edu	1	1	1	9	9
Las Cafeteras \| 4/28/2022 \| 7:30 PM	boving@ucdavis.edu	1	1	2	9	9
Las Cafeteras \| 4/28/2022 \| 7:30 PM	lcamp@ucdavis.edu	1	1	2	9	9
Las Cafeteras \| 4/28/2022 \| 7:30 PM	ejowhite@ucdavis.edu	1	1	4	9	9
Las Cafeteras \| 4/28/2022 \| 7:30 PM	tdiacova@ucdavis.edu	1	1	9	9	9

And, finally, here is the “Hard” group, which we still need to allocate.

Test
Event	Email	S_i	Req_ij	Rank_ij	M_i	Req_i
Grace Leslie \| 5/19/2022 \| 7:30 PM	mcguzel@ucdavis.edu	4	2	2	5	8
Grace Leslie \| 5/19/2022 \| 7:30 PM	emalliende@ucdavis.edu	4	2	4	5	8
Grace Leslie \| 5/19/2022 \| 7:30 PM	Mbrunet@ucdavis.edu	4	2	5	5	8
Grace Leslie \| 5/19/2022 \| 7:30 PM	lcamp@ucdavis.edu	4	1	7	5	8
Grace Leslie \| 5/19/2022 \| 7:30 PM	twpatten@ucdavis.edu	4	1	8	5	8
SAYS Showcase: Hip Hope, Spoken Word & Poetry \| 6/02/2022 \| 7:30 PM	jkmlee@ucdavis.edu	4	2	2	6	12
SAYS Showcase: Hip Hope, Spoken Word & Poetry \| 6/02/2022 \| 7:30 PM	ejowhite@ucdavis.edu	4	2	3	6	12
SAYS Showcase: Hip Hope, Spoken Word & Poetry \| 6/02/2022 \| 7:30 PM	boving@ucdavis.edu	4	2	3	6	12
SAYS Showcase: Hip Hope, Spoken Word & Poetry \| 6/02/2022 \| 7:30 PM	lcamp@ucdavis.edu	4	2	3	6	12
SAYS Showcase: Hip Hope, Spoken Word & Poetry \| 6/02/2022 \| 7:30 PM	sgini@ucdavis.edu	4	2	4	6	12
SAYS Showcase: Hip Hope, Spoken Word & Poetry \| 6/02/2022 \| 7:30 PM	hemantb@ucdavis.edu	4	2	7	6	12

Visualization and Some Reports

What we have done so far is to assign the relatively easy events. Across all members, the awards or scores they’ve received so far are in the table below. When assigning the “harder” events that require resolving disputes, we will give higher priority to those who have lower scores.

#> `mutate_if()` ignored the following grouping variables:
#> • Column `Email`