Skip to content

Instantly share code, notes, and snippets.

@danielpcox
Created April 16, 2011 02:33
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save danielpcox/922798 to your computer and use it in GitHub Desktop.
Save danielpcox/922798 to your computer and use it in GitHub Desktop.
Generates my ORIE 3800 HW5 homework (database connection code omitted)
#!/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