Subpage under development, new version coming soon!
Subject: »[NT][U21] Players database
Mikoos to
All
There are two different databases, with their own topics:
1. this one, Players database - contains info about all the players from sokker, mostly useful for scouting players for national teams or "spying" on your opponent's players' skills (from their last transfers).
2. database from the other topic, Transfers database - contains info about all transfers made after 2021-12-27, useful for estimating player's market value.
---
Juytt has created a site where you can browse some data from my database - check it!
---
A few Polish users asked me to create a database with all the players from sokker, so I made one. :) This database contains generally available data, such as a player value, estimated value in superdivine form, match ratings and formations in which the players played, exact list below. It also contains player's skills from the day of their last transfer if that transfer happened after 2021-12-27.
To open the database you need two things: the program to browse the database and the database file itself.
Link to the program DB Browser for SQLite for browsing transfers database
Link to the players database file
1. Download the installer of the database browser program from the first link, select "DB Browser for SQLite - Standard installer for 64-bit Windows".
2. Install the program
3. Download the database file from the second link in this thread (players.db) by right-clicking on the file and selecting "Download..." -> "Standard Download"
4. Launch the DB Browser for SQLite program
5. Click the "Open Database" button.
6. Select the downloaded players.db file (depending on your browser settings, it will usually be located either in the "Downloads" directory or on desktop)
7. Disable the panels on the right side of the program, they are not needed
8. Go to the "Browse Data" tab
First table, players, has fields (columns):
- id - row number in the table
- pid - id of the player in sokker, by appending this id to the end of https://sokker.org/player/PID/ you can get the player's page in sokker
- team_id - player's team id
- name - player's full name
- country - id of the player's country, the id of the country you are interested in can be obtained from the World page: https://sokker.org/world, it is the number to the left of the country flag in the table at the bottom
- age - player's age
- height - player's height
- weight - player's weight, multiplied by 10 to avoid decimals
- value - player's value (in dollars/euro)
- value_adjusted - value the player would have, if he was in superdivine form (accurate to ±2%)
- wage - player's wage
- form - player's form (numerically 0 - tragic, 18 - superdivine)
- goals, assists, matches - player's goals, assists and number of matches played
- nt_goals, nt_assists, nt_matches - player's national team goals, assists and number of national games played
- nt_status - information whether the player is currently in the national team; 0 - he is not, 1 - he is in the adult national team, 2 - he is in the youth national team
- season - season when player was on transfer list (NULL means no data)
- week - week of the season when player was on transfer list (NULL means no data)
- weeks_ago - how many weeks ago player was on transfer list (NULL means no data)
- stam - stamina at the moment of the last player's transfer (NULL means no data, same for all other skills)
- pace - pace at the moment of the last player's transfer
- tech - technique at the moment of the last player's transfer
- pass - passing at the moment of the last player's transfer
- gk - keeper at the moment of the last player's transfer
- def - defender at the moment of the last player's transfer
- play - playmaker at the moment of the last player's transfer
- str - striker at the moment of the last player's transfer
- exp, tw, disc - experience, teamwork, tactical discipline of the player
- best_note - the player's best rating out of the recently played matches by his club (NULL means he did not play in any of those matches)
- main_order - the formation the player played the most in the recently played matches
- best_note_order - the formation in which the player received the highest rating
- avg_note - the average rating obtained in the recently played matches
- all_notes - all ratings of the recently played matches (in order from the most recent)
- all_orders - all formations of the recently played matches
- match_ids - ids of the recently played matches
Players table became very wide after adding skills there, so I also added table players_compact, which contains only the most useful informations, should be more enjoyable to browse:
- pid - id of the player in sokker, by appending this id to the end of https://sokker.org/player/PID/ you can get the player's page in sokker
- name - player's full name
- team_id - player's team id
- ctry - id of the player's country, the id of the country you are interested in can be obtained from the World page: https://sokker.org/world, it is the number to the left of the country flag in the table at the bottom
- age - player's age
- nt_status - information whether the player is currently in the national team; 0 - he is not, 1 - he is in the adult national team, 2 - he is in the youth national team
- value - player's value (in dollars/euro)
- value_ad - value the player would have, if he was in superdivine form (accurate to ±2%)
- wage - player's wage
- form - player's form (numerically 0 - tragic, 18 - superdivine)
- weeks_ago - how many weeks ago player was on transfer list (NULL means no data)
- stam - stamina at the moment of the last player's transfer (NULL means no data, same for all other skills)
- pace - pace at the moment of the last player's transfer
- tech - technique at the moment of the last player's transfer
- pass - passing at the moment of the last player's transfer
- gk - keeper at the moment of the last player's transfer
- def - defender at the moment of the last player's transfer
- play - playmaker at the moment of the last player's transfer
- disc - tactical discipline of the player
- avg - the average rating obtained in the recently played matches
- best - the player's best rating out of the recently played matches by his club (NULL means he did not play in any of those matches)
- main_order - the formation the player played the most in the recently played matches
- best_order - the formation in which the player received the highest rating
5. to filter the data, type the appropriate text in the Filter field under the name of the relevant column:
- if we want to get results with the exact value we should precede this value with = sign, for example if we want to get only players from Poland we should in the filter in the country column write the text: =1
- if we want to get only youth players, we should write in the age column <=21 (or <22), similarly if we want only older players, we should write there >=22 (or >21), the same way we filter everywhere else the values: smaller-equal / smaller / bigger-equal / bigger
- if you don't want to have goalkeepers in the results, in the main_order and best_note_order fields you should enter the text: /^((?!GK).)*$/
- if you want to have results only in a certain range, for example age from 25 to 30 years write following text in age filter: 25~30
6. To sort the results by a given column click on the name of the column, one click will sort from the lowest value, and the second click from the highest value
7. To clear the filters/sorting click the buttons above the table: "Clear all filters", "Restore row order to default"
8. Columns can be hidden (right-click on column -> Hide column(s)), but after entering any new filter the hidden columns reappear, so I recommend hiding columns once you have entered all filters and sorting
Example search for U21 players from Poland, non-goalkeepers, sorted by average match note, with some columns hidden:
9. If anyone has any questions please write them here, I will not write back to private messages about this database
- I intend to update database file about once a week, on Mondays. After I update it, you have to download it again to have it updated on your computer.
(edited)
1. this one, Players database - contains info about all the players from sokker, mostly useful for scouting players for national teams or "spying" on your opponent's players' skills (from their last transfers).
2. database from the other topic, Transfers database - contains info about all transfers made after 2021-12-27, useful for estimating player's market value.
---
Juytt has created a site where you can browse some data from my database - check it!
---
A few Polish users asked me to create a database with all the players from sokker, so I made one. :) This database contains generally available data, such as a player value, estimated value in superdivine form, match ratings and formations in which the players played, exact list below. It also contains player's skills from the day of their last transfer if that transfer happened after 2021-12-27.
To open the database you need two things: the program to browse the database and the database file itself.
Link to the program DB Browser for SQLite for browsing transfers database
Link to the players database file
1. Download the installer of the database browser program from the first link, select "DB Browser for SQLite - Standard installer for 64-bit Windows".
2. Install the program
3. Download the database file from the second link in this thread (players.db) by right-clicking on the file and selecting "Download..." -> "Standard Download"
4. Launch the DB Browser for SQLite program
5. Click the "Open Database" button.
6. Select the downloaded players.db file (depending on your browser settings, it will usually be located either in the "Downloads" directory or on desktop)
7. Disable the panels on the right side of the program, they are not needed
8. Go to the "Browse Data" tab
First table, players, has fields (columns):
- id - row number in the table
- pid - id of the player in sokker, by appending this id to the end of https://sokker.org/player/PID/ you can get the player's page in sokker
- team_id - player's team id
- name - player's full name
- country - id of the player's country, the id of the country you are interested in can be obtained from the World page: https://sokker.org/world, it is the number to the left of the country flag in the table at the bottom
- age - player's age
- height - player's height
- weight - player's weight, multiplied by 10 to avoid decimals
- value - player's value (in dollars/euro)
- value_adjusted - value the player would have, if he was in superdivine form (accurate to ±2%)
- wage - player's wage
- form - player's form (numerically 0 - tragic, 18 - superdivine)
- goals, assists, matches - player's goals, assists and number of matches played
- nt_goals, nt_assists, nt_matches - player's national team goals, assists and number of national games played
- nt_status - information whether the player is currently in the national team; 0 - he is not, 1 - he is in the adult national team, 2 - he is in the youth national team
- season - season when player was on transfer list (NULL means no data)
- week - week of the season when player was on transfer list (NULL means no data)
- weeks_ago - how many weeks ago player was on transfer list (NULL means no data)
- stam - stamina at the moment of the last player's transfer (NULL means no data, same for all other skills)
- pace - pace at the moment of the last player's transfer
- tech - technique at the moment of the last player's transfer
- pass - passing at the moment of the last player's transfer
- gk - keeper at the moment of the last player's transfer
- def - defender at the moment of the last player's transfer
- play - playmaker at the moment of the last player's transfer
- str - striker at the moment of the last player's transfer
- exp, tw, disc - experience, teamwork, tactical discipline of the player
- best_note - the player's best rating out of the recently played matches by his club (NULL means he did not play in any of those matches)
- main_order - the formation the player played the most in the recently played matches
- best_note_order - the formation in which the player received the highest rating
- avg_note - the average rating obtained in the recently played matches
- all_notes - all ratings of the recently played matches (in order from the most recent)
- all_orders - all formations of the recently played matches
- match_ids - ids of the recently played matches
Players table became very wide after adding skills there, so I also added table players_compact, which contains only the most useful informations, should be more enjoyable to browse:
- pid - id of the player in sokker, by appending this id to the end of https://sokker.org/player/PID/ you can get the player's page in sokker
- name - player's full name
- team_id - player's team id
- ctry - id of the player's country, the id of the country you are interested in can be obtained from the World page: https://sokker.org/world, it is the number to the left of the country flag in the table at the bottom
- age - player's age
- nt_status - information whether the player is currently in the national team; 0 - he is not, 1 - he is in the adult national team, 2 - he is in the youth national team
- value - player's value (in dollars/euro)
- value_ad - value the player would have, if he was in superdivine form (accurate to ±2%)
- wage - player's wage
- form - player's form (numerically 0 - tragic, 18 - superdivine)
- weeks_ago - how many weeks ago player was on transfer list (NULL means no data)
- stam - stamina at the moment of the last player's transfer (NULL means no data, same for all other skills)
- pace - pace at the moment of the last player's transfer
- tech - technique at the moment of the last player's transfer
- pass - passing at the moment of the last player's transfer
- gk - keeper at the moment of the last player's transfer
- def - defender at the moment of the last player's transfer
- play - playmaker at the moment of the last player's transfer
- disc - tactical discipline of the player
- avg - the average rating obtained in the recently played matches
- best - the player's best rating out of the recently played matches by his club (NULL means he did not play in any of those matches)
- main_order - the formation the player played the most in the recently played matches
- best_order - the formation in which the player received the highest rating
5. to filter the data, type the appropriate text in the Filter field under the name of the relevant column:
- if we want to get results with the exact value we should precede this value with = sign, for example if we want to get only players from Poland we should in the filter in the country column write the text: =1
- if we want to get only youth players, we should write in the age column <=21 (or <22), similarly if we want only older players, we should write there >=22 (or >21), the same way we filter everywhere else the values: smaller-equal / smaller / bigger-equal / bigger
- if you don't want to have goalkeepers in the results, in the main_order and best_note_order fields you should enter the text: /^((?!GK).)*$/
- if you want to have results only in a certain range, for example age from 25 to 30 years write following text in age filter: 25~30
6. To sort the results by a given column click on the name of the column, one click will sort from the lowest value, and the second click from the highest value
7. To clear the filters/sorting click the buttons above the table: "Clear all filters", "Restore row order to default"
8. Columns can be hidden (right-click on column -> Hide column(s)), but after entering any new filter the hidden columns reappear, so I recommend hiding columns once you have entered all filters and sorting
Example search for U21 players from Poland, non-goalkeepers, sorted by average match note, with some columns hidden:
9. If anyone has any questions please write them here, I will not write back to private messages about this database
- I intend to update database file about once a week, on Mondays. After I update it, you have to download it again to have it updated on your computer.
(edited)
I have tested it. I have 0 knowledge about such things and yet it all worked wonderfully, also thanks to your explanations!
Thanks for that, Mikoos!
Thanks for that, Mikoos!
Hi Mikoos
Your database contains some interesting colums :-). May I ask you the following questions?:
- How do you calculate "value_adjusted"? Can you tell me the formula you use? Or if it is a regression: Which factors (independent variables) did you include?
- Where do you get "all_notes" and "all_orders" from? I think they are not available through xml? Is there a "hidden" json-endpoint or is the data from scanning the html?
.
(edited)
Your database contains some interesting colums :-). May I ask you the following questions?:
- How do you calculate "value_adjusted"? Can you tell me the formula you use? Or if it is a regression: Which factors (independent variables) did you include?
- Where do you get "all_notes" and "all_orders" from? I think they are not available through xml? Is there a "hidden" json-endpoint or is the data from scanning the html?
.
(edited)
Hi ramonymous!
- value_adjusted formula I used is very simple:
value_adjusted = value / (0,5625 * + 0,025 * form)
unless form is at 18 then
value_adjusted = value
The "proper" formula is value / (0,55 + 0,025 * form), but it assumes we know the exact float value of form, I assume the level is in the middle and add 0,0125 to that 0,55 (that's why 18 case is calculated separately, it would calculate it with 18,5 form, but 18.00 is max)
I saw that formula in Terrion's juniors calculator, few months ago I checked if it's correct - on Monday update I checked thousands of young enough players and checked if their new form and value makes sense with that formula (is in the correct interval) and it worked perfectly.
- Everything is in xmls :)
first I download the list of all teams in sokker using league-[country ID]-[division]-[number].xml and league-[league ID].xml
then I download all the basic info about players from all teams from players-[team ID].xml
then I download list of matches played from matches-team-[team ID].xml (important part here is removing the duplicates, almost every match will be collected twice as it's played by two teams :P)
then I download matches info from match-[match ID].xml, there are players ratings and orders there, I add that info to players information, match after match, player after player appending the lists :)
I miss their NT matches, but I don't think it matters much.
- value_adjusted formula I used is very simple:
value_adjusted = value / (0,5625 * + 0,025 * form)
unless form is at 18 then
value_adjusted = value
The "proper" formula is value / (0,55 + 0,025 * form), but it assumes we know the exact float value of form, I assume the level is in the middle and add 0,0125 to that 0,55 (that's why 18 case is calculated separately, it would calculate it with 18,5 form, but 18.00 is max)
I saw that formula in Terrion's juniors calculator, few months ago I checked if it's correct - on Monday update I checked thousands of young enough players and checked if their new form and value makes sense with that formula (is in the correct interval) and it worked perfectly.
- Everything is in xmls :)
first I download the list of all teams in sokker using league-[country ID]-[division]-[number].xml and league-[league ID].xml
then I download all the basic info about players from all teams from players-[team ID].xml
then I download list of matches played from matches-team-[team ID].xml (important part here is removing the duplicates, almost every match will be collected twice as it's played by two teams :P)
then I download matches info from match-[match ID].xml, there are players ratings and orders there, I add that info to players information, match after match, player after player appending the lists :)
I miss their NT matches, but I don't think it matters much.
Nice to hear it worked for you! You're welcome :)
I tried to make it as simple as I can with the time and knowledge I have, I don't have enough programming skills to create a nice user-friendly website with browsing that database like ramonymous does with his :)
(edited)
I tried to make it as simple as I can with the time and knowledge I have, I don't have enough programming skills to create a nice user-friendly website with browsing that database like ramonymous does with his :)
(edited)
Thanks a lot for your detailed explanation. Thanks to you I just discovered that the league-[country ID]-[division]-[number].xml is indeed very useful ;-). Also your input about value_adjusted is very interesting since it was completely new to me :-).
Maybe one day we should do a collaboration... if only the sokker-devs would guarantee to not block my website's IP anymore ^^.
Maybe one day we should do a collaboration... if only the sokker-devs would guarantee to not block my website's IP anymore ^^.
Hello Mikoos,I tried everything what did you wrote above but when I click "Open Database..." from the "File" menu, and select the players.db its show me a message file already exist.
@ramonymous - Yor web site was amaizing.I dont know why they blocking IP of yor web but they need to be shamed.Is there any options to find some solution with devs so we can use your web again?
@ramonymous - Yor web site was amaizing.I dont know why they blocking IP of yor web but they need to be shamed.Is there any options to find some solution with devs so we can use your web again?
Are you sure you click "Open database..."? This message sounds like "New database..." option.
If you can work more in this and make it easy for the rest to ''work'' in it,it would be awsome!
So far i have install and uninstall that because i can't find anything.
So far i have install and uninstall that because i can't find anything.
I don't understand your problem.
Did something not work? If so then you need to give me more details.
Or everything works, but you didn't find any new useful players?
This is the search I would do for Greece for non-gk players that are not currently in NT with the highest average ratings:
Did something not work? If so then you need to give me more details.
Or everything works, but you didn't find any new useful players?
This is the search I would do for Greece for non-gk players that are not currently in NT with the highest average ratings:
I downloada the data
i install the browser,load the data to the browser but nothing happen
i see so many options so i dissapointed and uninstall the browser,
i just have no time to search it fearther..
thats why i say if it was more simple it would be better ..i dont know..
But every work from the users for the game deserve a respect.
I just cant work with it.
i install the browser,load the data to the browser but nothing happen
i see so many options so i dissapointed and uninstall the browser,
i just have no time to search it fearther..
thats why i say if it was more simple it would be better ..i dont know..
But every work from the users for the game deserve a respect.
I just cant work with it.
