Created
April 16, 2011 02:33
-
-
Save danielpcox/922798 to your computer and use it in GitHub Desktop.
Generates my ORIE 3800 HW5 homework (database connection code omitted)
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#!/home/danielpcox/.rvm/rubies/ruby-1.9.2-p180/bin/ruby | |
require 'active_record' | |
require './ticket' | |
require './extra' | |
require './show_info' | |
require 'gruff' | |
conn_params = { | |
:adapter => 'mysql', | |
:host => 'localhost', | |
:username => 'root', | |
:password => 'password', | |
:database => 'RMT' | |
} | |
Ticket.establish_connection(conn_params) | |
Extra.establish_connection(conn_params) | |
ShowInfo.establish_connection(conn_params) | |
puts '1)' | |
puts ' a) Total Ticket Revenue for shows in' | |
[2008, 2009, 2010].each do |year| | |
total = Ticket.joins(:show).where(:show_infos => {:Year => year}).map {|t| t.TicketPrice}.sum | |
puts "\t%d:\t $%12.2f" % [year, total] | |
end | |
puts ' b) Total Ticket Revenue by Area:' | |
[2008, 2009, 2010].each do |year| | |
yeartotal = 0 | |
[1, 2, 3].each do |area| | |
total = Ticket.joins(:show).where(:Area => area, :show_infos => {:Year => year}).map {|t| t.TicketPrice}.sum | |
yeartotal += total | |
puts "\t%d-Area%d:\t $%12.2f" % [year, area, total] | |
end | |
#puts "\tYear Total:\t $%12.2f\n\n" % [yeartotal] | |
end | |
SR = false # sort results? | |
puts '2)' | |
puts ' a) (See Attached Histogram)' | |
showtimes2008 = Ticket.joins(:show).select('DISTINCT tickets.Time_ID').where(:Area => 1, :show_infos => {:Year => 2008}).map{|t| t.Time_ID} | |
g = Gruff::Bar.new('1024x768') | |
g.title = 'People in Area 1 Per 2008 Show Time' | |
people_per_showtime = [] | |
showtimes2008.sort.each do |st| | |
numpeople = Ticket.where(:Time_ID => st, :Area => 1).count | |
#g.data("%d" % st, numpeople) | |
people_per_showtime << numpeople | |
#puts "%d: %d" % [st, numpeople] | |
end | |
g.data("Number of People", SR ? people_per_showtime.sort : people_per_showtime) | |
g.sort = false | |
g.y_axis_increment = 10 | |
g.legend_font_size = 6 | |
g.marker_font_size = 10 | |
g.title_font_size = 12 | |
g.hide_legend = true | |
g.write('2a.png') | |
puts ' b) (See Attached Histogram)' | |
showtimes2009 = Ticket.joins(:show).select('DISTINCT tickets.Time_ID').where(:Area => 1, :show_infos => {:Year => 2009}).map{|t| t.Time_ID} | |
showtimes2010 = Ticket.joins(:show).select('DISTINCT tickets.Time_ID').where(:Area => 1, :show_infos => {:Year => 2010}).map{|t| t.Time_ID} | |
g = Gruff::Bar.new('1024x768') | |
g.title = 'People in Area 1 Per 2009 and 2010 Show Times' | |
people_per_showtime_2009 = [] | |
showtimes2009.sort.each do |st| | |
numpeople = Ticket.where(:Time_ID => st, :Area => 1).count | |
#g.data("%d" % st, numpeople) | |
people_per_showtime_2009 << numpeople | |
#puts "%d: %d" % [st, numpeople] | |
end | |
people_per_showtime_2010 = [] | |
showtimes2010.sort.each do |st| | |
numpeople = Ticket.where(:Time_ID => st, :Area => 1).count | |
#g.data("%d" % st, numpeople) | |
people_per_showtime_2010 << numpeople | |
#puts "%d: %d" % [st, numpeople] | |
end | |
g.data("Number of People in Area1 in 2009", SR ? people_per_showtime_2009.sort : people_per_showtime_2009) | |
g.data("Number of People in Area1 in 2010", SR ? people_per_showtime_2010.sort : people_per_showtime_2010) | |
g.sort = false | |
g.y_axis_increment = 10 | |
g.legend_font_size = 6 | |
g.marker_font_size = 10 | |
g.title_font_size = 12 | |
#g.hide_legend = true | |
g.write('2b.png') | |
puts <<PARTC | |
c) First, the number of people in Area 1 in 2009 and 2010 has a much greater range over all show times, | |
from about 47-367, whereas in 2008 it's tighter, about 157-227. | |
There also appears to be a greater variability in general between the two histograms. Almost all of | |
the 2008 data is between 203 and 227, whereas the 2009/2010 data is all over the place, differing | |
frequently by a hundred or more. | |
PARTC | |
puts '3)' | |
# find all uniq Patron_ID's for non-comp tickets | |
nc_Patron_IDs = Ticket.select('DISTINCT Patron_ID').order(:Patron_ID).where('TicketPrice > 0').map{|t| t.Patron_ID} | |
#DEBUG | |
#nc_Patron_IDs = [44114] #nc_Patron_IDs[0..5] # [57118] # [138414] | |
superimposed_wtps_all_years = [] | |
File.delete("allyears.csv") # out with the old and in with the new... | |
[2008, 2009, 2010].each do |year| | |
#[2010].each do |year| # DEBUG | |
step_1_wtp_ary = [] | |
# [Patron_ID, Event_ID, TicketPrice] for all non-comp tickets of a particular patron (943) in 2008 | |
patronsRecord = Ticket.joins(:show).order(:Patron_ID, :Event_ID).where("TicketPrice > 0 AND show_infos.Year = %d" % year).map {|t| [t.Patron_ID, t.Event_ID, t.TicketPrice]} | |
nc_Patron_IDs_count = nc_Patron_IDs.count | |
nc_Patron_IDs.each_with_index do |pID, pIDindex| | |
#puts "%d Percent complete: %4.2f" % [year, (pIDindex / nc_Patron_IDs_count.to_f)] # DEBUG | |
#patron_record = patronsRecord.find_all {|pr| pr[0] == pID} | |
fi = patronsRecord.find_index {|pr| pr[0] != pID} | |
patron_record = fi ? patronsRecord[0...fi] : patronsRecord | |
patronsRecord = patronsRecord[fi...patronsRecord.count] if fi | |
#puts patron_record.to_s # DEBUG | |
# grouped patron record (a list of lists of patron records - inner lists contain PRs with same Event_ID | |
gpr = patron_record.inject([]) do |acc, pr| | |
if !acc.empty? && acc.last[0][0]==pr[0] && acc.last[0][1]==pr[1] | |
acc.last << pr | |
else | |
acc << [pr] | |
end | |
acc | |
end | |
#puts gpr.to_s # DEBUG | |
# averaged patron record - [[Patron_ID, Event_ID, NumTickets, AvgPrice], ...] | |
apr = gpr.inject([]) do |acc, group| | |
acc << [group[0][0], group[0][1], group.count, group.sum {|pr| pr[2]} / group.count.to_f] | |
end | |
### Infer [NumPeople, WTP] from a particular patron's record | |
# sort by NumTickets | |
apr.sort_by! {|pr| pr[2]} | |
#puts apr.to_s # DEBUG | |
# cull | |
while !apr.empty? do | |
low_list = apr.find_all {|i| i[2] == apr[0][2]} | |
step_1_wtp_ary << [apr[0][2], apr.sum {|pr| pr[3]}] | |
apr -= low_list | |
apr.map! {|i| i[2] -= low_list[0][2]; i} | |
end | |
end | |
step_1_wtp_ary.sort_by! {|i| i[1]} | |
# DEBUG | |
# puts "\n\n" | |
#puts step_1_wtp_ary.to_s # DEBUG | |
step_2_wtp_ary_intermediate = step_1_wtp_ary.inject([]) do |acc, i| | |
if !acc.empty? && acc.last[0][1]==i[1] | |
acc.last << i | |
else | |
acc << [i] | |
end | |
acc | |
end | |
#puts step_2_wtp_ary_intermediate.to_s # DEBUG | |
step_2_wtp_ary = step_2_wtp_ary_intermediate.inject([]) do |acc, j| | |
acc << [j.sum {|k| k[0]}, j[0][1]] | |
end | |
#puts step_2_wtp_ary.to_s # DEBUG | |
step_2_wtp_ary.sort_by! {|i| -i[1]} | |
# [quantity, price] | |
step_3_commulative_wtp_ary = step_2_wtp_ary.inject([]) do |acc, i| | |
acc << [i[0] + (!acc.empty? ? acc.last[0] : 0), i[1]] | |
end | |
#puts step_3_commulative_wtp_ary.to_s # DEBUG | |
csv_out = "" | |
step_3_commulative_wtp_ary.each do |i| | |
csv_out += "%d,%f\n" % i | |
end | |
superimposed_wtps_all_years += step_3_commulative_wtp_ary | |
File.open("allyears.csv", 'a') {|f| f.write(csv_out) } | |
end | |
puts <<P3A | |
a) A linear regression on the resulting CSV file (see attached code) yields | |
\t a demand function of p(q) = (-6.409640E-4)q + 70.017456. | |
\t See attached plot of curve with linear regression line. | |
P3A | |
puts <<P3B | |
b) PS = pq = (-6.409640E-4)q^2 + 70.017456q, which is maximum at q ~= 54618. | |
\tThat is, if this truly is the demand curve, we would do best to charge | |
\tp ~= (-6.409640E-4)*(54618) + 70.017456 ~= $35.01, for a total revenue of | |
\tPS = pq ~= (35.01)(54618) ~= $1,912,176.18. | |
P3B | |
net_loss_wtp_above_bundle = superimposed_wtps_all_years.find_all {|wtp| wtp[1] >= 35.01}.sum {|wtp| wtp[0] * (wtp[1] - 35.01)} | |
pre_bundle_profit_estimate = superimposed_wtps_all_years.sum {|wtp| wtp[0] * wtp[1]} | |
# c) #{(numpats_wtp_above_bundle / numpats.to_f) * 100}\% of | |
puts <<P3C | |
c) From the dataset resulting from (a), we can calculate the loss from people who would | |
\thave paid their willingness to pay for the season, but instead bought the pass. | |
\tSubtracting this from the estimate of the area under the demand curve without bundling, | |
\tand we have only #{"%4.2f\%" % ((pre_bundle_profit_estimate - net_loss_wtp_above_bundle) * 100/pre_bundle_profit_estimate)} the original profit. | |
\tOther than the 8.09\% loss, we make the same amount of profit as before, since the people with | |
\tWTP < $35.01 will continue to buy the individual tickets. Thus the net impact is negative. | |
P3C | |
puts <<P3D | |
d) One glaring limitation of the WTP modeling is that the demand curve is clearly not | |
\tlinear, so our linear regression is a poor approximation. | |
\tAlso, the WTP modeling is based on a minimum WTP for each patron, so the curve | |
\talmost certainly underestimates an arbitrary patron's willingness to pay. | |
\tIn the same vein, without any bundling we were getting the whole area under the demand | |
\tcurve, so with the given curve we lose money by bundling regardless of the price chosen. | |
\tThis can only be alleviated by estimating some delta by which our model has | |
\tunderestimated the demand curve. | |
P3D |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment