For those of you that like to keep track of "your score" in an Excel-sheet, I've put two sheets online that include all entries:


http://www.worldheritagesite.org/sites.xlsx

http://www.worldheritagesite.org/tentative.xlsx

elsslots is it possible to have an export function to csv or excel similar to this one? Ideally it would have

1. Site unique id matching Unesco's id
2. Visited status (optional)
3. The results of all of the possible ratings (value, popular votes from 2015, popular ratings stats)

The main thing is I would like to combine all ratings into a custom rating and I normally do some tracking in an excel sheet as well to do some statistics. The unique id is very important since the name of the sites contains sometimes special characters which excel does not like for the vlookup function

I've made a first version here, including 1 and 2: https://www.worldheritagesite.org/docs/sitestoplists.csv
Will try to add the others as well and make it repeatable.

elsslots

The links are missing the /docs/ in the path :)

You must forget the old ones in post #1, they're from 2013

Oops, I didn't see the date!!! :-D

value ratings & most popular 2015 added as well, in separate worksheets (can be combined via site_id when you're handy with excel):
https://www.worldheritagesite.org/docs/sitestoplists.xls

P.S. plus the current site ratings (most popular community) as well; can you work with this, Wapo?

elsslots just wow :o

I cannot believe how fast you were able to generate this. This works great.

The only question I had is that I wanted to use your ratings "score" based on the Wilson score. Is there a way that is the value we see in the ratings tab, if not, is it a simple formula that uses the positive/negative values and the average rating to get the score?

It is done like this:

sum((5 - sr.rating)/5) negative, sum((sr.rating)/5) positive

@Nan - can you clarify it a bit more how the WIlson score works that you implemented?

The Wilson score calculates based on a binomial distribution a lower and upper bound for ratings. I implemented the formula found here. Not sure if this is methodologically sound, but I think it does the trick.

Now we don't use the lower Wilson score directly. In case of the lower wilson score plenty of sites would show a zero as there simply are too few ratings to derive any confidence greater than zero. What we do:

* We have a config weighing parameter. I think it's 25%. Let's call it wf for weighing factor.
* (1-wf) * Avg Rating + wf * Lower Wilson Score.

You are obviously free to increase / decrease wf. I found 25% mostly did the trick and sorted the list in a sensible fashion.

Side remark: we can add this to the user screen -> export your sites as csv with ratings.

Hello elsslots,

I am not sure i get the formula, using the example of the pyramids I have:

site_id site_rating site_rating_votes negative positive
86 490,625 16 0.30000 1,570,000

do you mean ((5-4.9)/5)*negative+(4.9/5)*positive? what does sr.rating mean? I am not sure I can get the score result value unless I have every single vote.

At this point, would it be simpler then to add the calculated score to the csv instead?

To get the score, you need ALL user ratings and then run the query that I linked in my post. It would be easier to simply wait for the export functionality to be implemented. This will take a while though as I have limited time and am working on bootstrap 4.

I agree, however, I think it would be better to export all sites and have a "Visited" column to filter them instead.


That is what I suspected.


There is no rush. I am actually very impressed I even got a forum reply so quickly. I only asked because I was not even sure an "export" function was going to be implemented so I was trying to see what I could do with the numbers I had in the previous export link.

Hello Els,

It seems like this link its still pointing to the ranking votes from back then.

Is this available somewhere else on the site? Since there are more reviews now I would like to update my combined WHS Score excel sheet