World Heritage Site

for World Heritage Travellers



| Begin | Reply | Search |             Start | The List | Community | Blog
About this website www.worldheritagesite.org Forum / About this website /  
 

Excel-versions of Inscribed List and Tentative List

 
Author elsslots
Admin
#1 | Posted: 1 Jul 2013 13:52 | Edited by: elsslots 
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

Author Wapo
Partaker
#2 | Posted: 24 Jul 2018 09:59 
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

Author elsslots
Admin
#3 | Posted: 24 Jul 2018 10:48 
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.

Author AJRC
Partaker
#4 | Posted: 24 Jul 2018 10:51 | Edited by: AJRC 
elsslots

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

Author elsslots
Admin
#5 | Posted: 24 Jul 2018 10:58 
AJRC:
The links are missing the /docs/ in the path :)

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

Author AJRC
Partaker
#6 | Posted: 24 Jul 2018 11:03 
elsslots:
You must forget the old ones in post #1, they're from 2013

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

Author elsslots
Admin
#7 | Posted: 24 Jul 2018 11:54 | Edited by: elsslots 
elsslots:
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.

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?

Author Wapo
Partaker
#8 | Posted: 24 Jul 2018 14:45 
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?

Author elsslots
Admin
#9 | Posted: 25 Jul 2018 01:00 | Edited by: elsslots 
Wapo:
is it a simple formula

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?

Author nfmungard
Partaker
#10 | Posted: 25 Jul 2018 04:07 | Edited by: nfmungard 
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.

Author nfmungard
Partaker
#11 | Posted: 25 Jul 2018 09:52 
Side remark: we can add this to the user screen -> export your sites as csv with ratings.

Author Wapo
Partaker
#12 | Posted: 25 Jul 2018 14:10 
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?

Author nfmungard
Partaker
#13 | Posted: 25 Jul 2018 15:29 
Wapo:
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.

Author Wapo
Partaker
#14 | Posted: 26 Jul 2018 08:55 
nfmungard:
Side remark: we can add this to the user screen -> export your sites as csv with ratings.

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

nfmungard:
To get the score, you need ALL user ratings and then run the query that I linked in my post.

That is what I suspected.

nfmungard:
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.

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.

Author Wapo
Partaker
#15 | Posted: 5 Apr 2019 09:57 
elsslots:
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

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

About this website www.worldheritagesite.org Forum / About this website /
 Excel-versions of Inscribed List and Tentative List

Your Reply Click this icon to move up to the quoted message


 ?
Only registered users are allowed to post here. Please, enter your username/password details upon posting a message, or register first.

 
 
www.worldheritagesite.org Forum Powered by Chat Forum Software miniBB ®
 ⇑