Jump to content


NEED HELP WITH WOWP API


  • Please log in to reply
45 replies to this topic

GouIdy #21 Posted 31 March 2017 - 03:32 PM

    First Lieutenant

  • Member
  • 6 battles
  • 2,612
  • Member since:
    11-18-2013

View PostMARS_REVENANT, on 30 March 2017 - 11:37 PM, said:

 

Next step will be to figure out how to pull data for and calculate average plane tier. 

 

 

EZ... In the API you go into Account/Player's aircraft

Put in account id's

For fields put in battles, plane_id

 

The plane id is a 4 digit number, first digit is nation second digit is tier (0=tier 10)

 

Then the rest is just math.

 

 


Edited by GouIdy, 31 March 2017 - 03:33 PM.


MARS_REVENANT #22 Posted 31 March 2017 - 03:39 PM

    Colonel

  • Member
  • 809 battles
  • 7,873
  • [SICK] SICK
  • Member since:
    01-26-2012

View PostGouIdy, on 31 March 2017 - 10:32 AM, said:

 

EZ... In the API you go into Account/Player's aircraft

Put in account id's

For fields put in battles, plane_id

 

The plane id is a 4 digit number, first digit is nation second digit is tier (0=tier 10)

 

Then the rest is math.

 

 

 

I was able to get all the data I required.  Just need to figure out how to get the math into the software I am using and link the results to the table I started with the general stats. And how to condense the data down, basically I have 1 line of data for every player, and for every plane id... so 1 player can have over 100 lines of data listing depending how many planes they have flown.

 

You have eliminated 1 step for me though.  I didn't know the plane ID second digit meant tier.  So I went and gathered plane_id and tier from the plane encyclopedia API. and linked that to the account ID and plane ID that you mentioned.  Also needed wins and losses for plane ids to calculate total battles in each plane.

 

Math is fun... 


1.9.x Forum Stats: Colonel; Member; 34638 battles; 7,526 message_img.pngMember since: 01-26-2012

 

I never lose; either I win or I learn.

 


GouIdy #23 Posted 31 March 2017 - 03:55 PM

    First Lieutenant

  • Member
  • 6 battles
  • 2,612
  • Member since:
    11-18-2013

View PostMARS_REVENANT, on 31 March 2017 - 11:39 AM, said:

 

 Also needed wins and losses for plane ids to calculate total battles in each plane.

 

 

The API has a line for total battles in each plane already, no need to calculate.

MARS_REVENANT #24 Posted 31 March 2017 - 03:56 PM

    Colonel

  • Member
  • 809 battles
  • 7,873
  • [SICK] SICK
  • Member since:
    01-26-2012

View PostGouIdy, on 31 March 2017 - 10:55 AM, said:

 

The API has a line for total battles in each plane already, no need to calculate.

 

I thought about that.... but must have missed it... another step eliminated

 


1.9.x Forum Stats: Colonel; Member; 34638 battles; 7,526 message_img.pngMember since: 01-26-2012

 

I never lose; either I win or I learn.

 


Mackunaima #25 Posted 31 March 2017 - 04:32 PM

    Second Lieutenant

  • Closed Beta Tester
  • 0 battles
  • 1,622
  • Member since:
    04-09-2012

View PostMARS_REVENANT, on 31 March 2017 - 12:37 AM, said:

Thanks MAC, Sinister, and everyone else for the advice... Still learning, need to automate the steps.

 

This is what I have so far.  Using Microsoft Power BI was able to pull data for names and id, as well as ID and stats and linked them to create this chart.

 

The formula used in the table right now is (Kills+Assists+(Ground Targets/2)) / Battles + (Victories/Battles)+(Survivals/Battles)

 

Next step will be to figure out how to pull data for and calculate average plane tier.  The formula will then be modifies to add weight to wins and average tier.

 

 

Well, there is a soft way to get this, trying to mount the same query string your profile page uses to get your personal data or use the hard but legal way to get these datas.

 

First of "wall" you will need to use their developer API with "demo" application ID given by them if you don't register your own application.

 

1 - So you need to go to https://developers.wargaming.net/

2 - Click at World of Warples item menu xpanding it

3 - Click list of aircraft

4 - Click "Send request" to get all JSON data o

5 - Copy the mounte url "URL for request" to use with your power BI (https://api.worldofw...ication_id=demo)

 

This will give you a list of all aircrafts giving you something like this
 

Spoiler

 

 

So after this you need to follow:

1 - Click "Players Aicraft"

2 - Insert his ID (1002206216, your own id MARS_)

3 - Send request again to mount your new qury URL ()

 

This will give you:

 

Spoiler

 

Now, how you are going to query dinamycally:

In power BI you need to ount your query URL using the players ID's, creating a macroto fill the player's datatable. Now you will need some programming skill.

 

Or just create a new query URL by your self every time when a new player join your clan.

 

After all of this you need to sum all battles by tier to get average tier. At this moment I think you can input some constant to increment the final result accordly your interests. Like, Eg (tier * number of battles * (1 - ( -1 * (6 - tier) / 10)) <- this is huge deviance just to show purpose, and 6 the tier I want to be considered as average tier

 

Case 1, same number of battle over 2 tiers:

( 2 * 30 * (1 + (( -1 * (6 - 2)) / 10) ) = 36 points of relevance

 

(8 * 30 * (1 + (( -1 * (6 - 8)) / 10) ) = 288 of relevance

 

Case 2, high tier player

( 2 * 30 * (1 + (( -1 * (6 - 2)) / 10) ) = 36 points of relevance

 

(8 * 100 * (1 + (( -1 * (6 - 8)) / 10) ) = 960 of relevance

 

Case 3, low tier player

( 2 * 100 * (1 + (( -1 * (6 - 2)) / 10) ) = 120 points of relevance

 

(8 * 30 * (1 + (( -1 * (6 - 8)) / 10) ) = 288 of relevance

 

So, after this you need to sum relevance points and divide them by total number of battles:

Case 1:

(36+288) / 60 = 5,4 total relevance

 

Case 2:

(36+960) / 130 = 7,66  total relevance

 

Case 3:

(120+288) / 130 = 3,13 total relevance

 

As you can see you are applying penalties for low tier players

 

Considering this total relevance result as a weight for your final formula, you can turn high tier players more valuable for you, or low tier but just need to invert the formula.

 

You could consider plane type if you want to. but as vet player I know a GA in good hands can be dangerous as an HF. I would not apply this for public matches, just for CW if this will come over here someday.

 

Regards,

Mack


"Certainty of death. Small chance of success. What are we waiting for?" - Gimli

 


MARS_REVENANT #26 Posted 31 March 2017 - 04:46 PM

    Colonel

  • Member
  • 809 battles
  • 7,873
  • [SICK] SICK
  • Member since:
    01-26-2012

View PostMackunaima, on 31 March 2017 - 11:32 AM, said:

 

Well, there is a soft way to get this, trying to mount the same query string your profile page uses to get your personal data or use the hard but legal way to get these datas.

 

First of "wall" you will need to use their developer API with "demo" application ID given by them if you don't register your own application.

 

1 - So you need to go to https://developers.wargaming.net/

2 - Click at World of Warples item menu xpanding it

3 - Click list of aircraft

4 - Click "Send request" to get all JSON data o

5 - Copy the mounte url "URL for request" to use with your power BI (https://api.worldofw...ication_id=demo)

 

This will give you a list of all aircrafts giving you something like this
 

Spoiler

 

 

So after this you need to follow:

1 - Click "Players Aicraft"

2 - Insert his ID (1002206216, your own id MARS_)

3 - Send request again to mount your new qury URL ()

 

This will give you:

 

Spoiler

 

Now, how you are going to query dinamycally:

In power BI you need to ount your query URL using the players ID's, creating a macroto fill the player's datatable. Now you will need some programming skill.

 

Or just create a new query URL by your self every time when a new player join your clan.

 

After all of this you need to sum all battles by tier to get average tier. At this moment I think you can input some constant to increment the final result accordly your interests. Like, Eg (tier * number of battles * (1 - ( -1 * (6 - tier) / 10)) <- this is huge deviance just to show purpose, and 6 the tier I want to be considered as average tier

 

Case 1, same number of battle over 2 tiers:

( 2 * 30 * (1 + (( -1 * (6 - 2)) / 10) ) = 36 points of relevance

 

(8 * 30 * (1 + (( -1 * (6 - 8)) / 10) ) = 288 of relevance

 

Case 2, high tier player

( 2 * 30 * (1 + (( -1 * (6 - 2)) / 10) ) = 36 points of relevance

 

(8 * 100 * (1 + (( -1 * (6 - 8)) / 10) ) = 960 of relevance

 

Case 3, low tier player

( 2 * 100 * (1 + (( -1 * (6 - 2)) / 10) ) = 120 points of relevance

 

(8 * 30 * (1 + (( -1 * (6 - 8)) / 10) ) = 288 of relevance

 

So, after this you need to sum relevance points and divide them by total number of battles:

Case 1:

(36+288) / 60 = 5,4 total relevance

 

Case 2:

(36+960) / 130 = 7,66  total relevance

 

Case 3:

(120+288) / 130 = 3,13 total relevance

 

As you can see you are applying penalties for low tier players

 

Considering this total relevance result as a weight for your final formula, you can turn high tier players more valuable for you, or low tier but just need to invert the formula.

 

You could consider plane type if you want to. but as vet player I know a GA in good hands can be dangerous as an HF. I would not apply this for public matches, just for CW if this will come over here someday.

 

Regards,

Mack

 

Thanks Mack... I'll go through this tonight... that's right I'm using my Friday night to go through programming data for stats on in a Clan for WOWP that has only 500 players on each night....

1.9.x Forum Stats: Colonel; Member; 34638 battles; 7,526 message_img.pngMember since: 01-26-2012

 

I never lose; either I win or I learn.

 


Mackunaima #27 Posted 31 March 2017 - 05:10 PM

    Second Lieutenant

  • Closed Beta Tester
  • 0 battles
  • 1,622
  • Member since:
    04-09-2012

View PostMARS_REVENANT, on 31 March 2017 - 01:46 PM, said:

 

Thanks Mack... I'll go through this tonight... that's right I'm using my Friday night to go through programming data for stats on in a Clan for WOWP that has only 500 players on each night....

 

Friday night programming?

 

Man, my company would like to hire u..

 

How about banon, beer, dirty jokes and shot people down at friday night?


"Certainty of death. Small chance of success. What are we waiting for?" - Gimli

 


MARS_REVENANT #28 Posted 31 March 2017 - 05:18 PM

    Colonel

  • Member
  • 809 battles
  • 7,873
  • [SICK] SICK
  • Member since:
    01-26-2012

View PostMackunaima, on 31 March 2017 - 12:10 PM, said:

 

Friday night programming?

 

Man, my company would like to hire u..

 

How about banon, beer, dirty jokes and shot people down at friday night?

 

Get on TS and you will realize I am already doing most of that... need something to do during queue and 30 countdown.

1.9.x Forum Stats: Colonel; Member; 34638 battles; 7,526 message_img.pngMember since: 01-26-2012

 

I never lose; either I win or I learn.

 


pigeon_kicker #29 Posted 01 April 2017 - 12:23 AM

    Second Lieutenant

  • Open Beta Tester
  • 0 battles
  • 1,421
  • [SF] SF
  • Member since:
    08-04-2013

This can very easily be done as a script.

 

  • You can set a hot-key lets say
  • Your scripting looks for your info and puts it someplace, a text file is my example
  • You have a text file with all of the user id #'s, 1 per line, as many as you want
  • get output

 

You can call for multiple items to be loaded, like tier, type, battles flown, etc....

 

  • Send it to a spreadsheet
  • have the spreadsheet do it's thing
  • get your numbers

 

Google Apps Scripts has lots for this type of thing.

And A Bunch of Pre-Made Libraries

 

PK


Edited by pigeon_kicker, 01 April 2017 - 12:25 AM.

If you want to make god laugh, tell him about your plans for survival..

Lead developer for the NACCW web site. Author of the Pigeon Pak Mod Installer

www.team-sf.com

 

 


pigeon_kicker #30 Posted 01 April 2017 - 12:35 AM

    Second Lieutenant

  • Open Beta Tester
  • 0 battles
  • 1,421
  • [SF] SF
  • Member since:
    08-04-2013

PS

 

Here is the API Developers Console for Google Apps


If you want to make god laugh, tell him about your plans for survival..

Lead developer for the NACCW web site. Author of the Pigeon Pak Mod Installer

www.team-sf.com

 

 


MARS_REVENANT #31 Posted 01 April 2017 - 04:14 PM

    Colonel

  • Member
  • 809 battles
  • 7,873
  • [SICK] SICK
  • Member since:
    01-26-2012

I hit a stumbling block in Power BI

 

The first 4 columns are filled in automatically by pulling data from WG.  I used 2 different queries, and built the table by linking the account ID in each query.

 

The last column is a calculation that multiplies the number of battles in a plane by the planes tier.  WHat I need to do now is total the 'Battles Per Plane x Tier' column, but create separate totals for each unique Account ID.  From there I can use that number and divide it by the total battles(from a different querry) number and get the average tier.  I can probably do this manually in Excel... but it would be a lot easier and less time consuming to get a function to do this in Power BI.

 

Any help is appreciated, Thanks!

 


Edited by MARS_REVENANT, 01 April 2017 - 04:15 PM.

1.9.x Forum Stats: Colonel; Member; 34638 battles; 7,526 message_img.pngMember since: 01-26-2012

 

I never lose; either I win or I learn.

 


Mackunaima #32 Posted 01 April 2017 - 04:50 PM

    Second Lieutenant

  • Closed Beta Tester
  • 0 battles
  • 1,622
  • Member since:
    04-09-2012

View PostMARS_REVENANT, on 01 April 2017 - 01:14 PM, said:

I hit a stumbling block in Power BI

 

The first 4 columns are filled in automatically by pulling data from WG.  I used 2 different queries, and built the table by linking the account ID in each query.

 

The last column is a calculation that multiplies the number of battles in a plane by the planes tier.  WHat I need to do now is total the 'Battles Per Plane x Tier' column, but create separate totals for each unique Account ID.  From there I can use that number and divide it by the total battles(from a different querry) number and get the average tier.  I can probably do this manually in Excel... but it would be a lot easier and less time consuming to get a function to do this in Power BI.

 

Any help is appreciated, Thanks!

 

 

 

You need to look up for each account ID and sum it all, using aggregation.

 

Take a look on this:

 

https://community.po...r-BI/ba-p/35912

https://community.po...xcel/td-p/40781

 

This is called "sum with vertical look up"

https://community.po...n-BI/td-p/61371

 

There is many way to it.

 

Regards,


"Certainty of death. Small chance of success. What are we waiting for?" - Gimli

 


MARS_REVENANT #33 Posted 01 April 2017 - 07:47 PM

    Colonel

  • Member
  • 809 battles
  • 7,873
  • [SICK] SICK
  • Member since:
    01-26-2012

OK... through some trial and error, I was able to remove some fields and Power BI automatically condensed all the data I needed (add up all (plane battles)*(plane tier)) and display it in 1 row associated to the player ID.  From there it was pretty easy to finish the rest.  Here is my table so far.

 

I think the SICK RATINGtm formula needs a bit of work.

 

Current formula:

SICK RATING =

 

(Assists+Kills+(Ground Targets/2)) * Average Tier

---------------------------------------------------------------

Battles+(Wins/Battles*100)+(Survived/Battles*100)

 

Using my own stats as an example:

 

(16,664+51,791+16,381)*7.19                        609,970.84

---------------------------------------           =        -----------------      =   19.82

         30,664+63.22+50.91                               30,778.13

 

The formula was developed by using what I understood to be the base calculations for the Hryunometr...  However looking at it, it doesn't really do a lot for the win rate and survival rate to be on the bottom.  And the win rate really isnt causing a lot of change in the overall rating.  For example if I lost every battle, and was killed every battle, the formula would still be pretty close as it is really dependent on performance per battle (kills, assists, ground targets) divided by total battles.

 

I think it woual be better to pull the W/R and S/R out and use them as multipliers after the calculation.  Something like this:

 

[(Assists+Kills+(Ground Targets/2)) / Battles] * W/R * S/R * Average Tier.

 

Given the data that is able to be pulled down from WG, and that SICK is focussed on Winning, how would you apply extra weight to winning in this formula?

 

 


1.9.x Forum Stats: Colonel; Member; 34638 battles; 7,526 message_img.pngMember since: 01-26-2012

 

I never lose; either I win or I learn.

 


MALICE_AT_BIRTH #34 Posted 01 April 2017 - 08:24 PM

    Second Lieutenant

  • Member
  • 0 battles
  • 1,624
  • [SICK] SICK
  • Member since:
    01-26-2012

 

I need to buy this man A Gift Card - Nice job Mars!! SICK is not playing around all, We are caring for our members, If Wargaming won't make it exciting, we will at least do it for our members, I just had a raffle for a tier 4 and 5 Aircraft, in June I will raffle off a Thrustmaster T-Flight Joystick! Heck, I might even design a Coffee Mug, With Mr. Spaulding on it!

 

 

Stay tuned and please join!!!

 

https://na.wargaming...owp/1000043592/

 

 


Edited by SOJO, 06 May 2017 - 02:16 AM.

MALICE_AT_BIRTH.png

 

 

                                                    


Mackunaima #35 Posted 01 April 2017 - 10:41 PM

    Second Lieutenant

  • Closed Beta Tester
  • 0 battles
  • 1,622
  • Member since:
    04-09-2012

Well, I think you are doing well.. As I said, you just need to chose carefully what stats do you want to apply penalties and what do you want to increase.

 

After this, is easy to turn them into deviance constant for up or down.I gave you some example, but you can turn this more fancy.

 

Regards,


"Certainty of death. Small chance of success. What are we waiting for?" - Gimli

 


MALICE_AT_BIRTH #36 Posted 01 April 2017 - 11:18 PM

    Second Lieutenant

  • Member
  • 0 battles
  • 1,624
  • [SICK] SICK
  • Member since:
    01-26-2012

Thanks Mack for help us out. SICK, Especially on behalf of MARS


MALICE_AT_BIRTH.png

 

 

                                                    


Mackunaima #37 Posted 02 April 2017 - 12:39 PM

    Second Lieutenant

  • Closed Beta Tester
  • 0 battles
  • 1,622
  • Member since:
    04-09-2012

View PostSOJO, on 01 April 2017 - 08:18 PM, said:

Thanks Mack for help us out. SICK, Especially on behalf of MARS

 

Oh man.. don't need to..

 

Just vets helping vets to achieve better things in this game, and about friendship. :great:


"Certainty of death. Small chance of success. What are we waiting for?" - Gimli

 


MARS_REVENANT #38 Posted 02 April 2017 - 02:51 PM

    Colonel

  • Member
  • 809 battles
  • 7,873
  • [SICK] SICK
  • Member since:
    01-26-2012

View PostMackunaima, on 02 April 2017 - 07:39 AM, said:

 

Oh man.. don't need to..

 

Just vets helping vets to achieve better things in this game, and about friendship. :great:

 

Well Mack said it.... (holds back tears) I'm a vet...and (lets tears flow) a friend....

1.9.x Forum Stats: Colonel; Member; 34638 battles; 7,526 message_img.pngMember since: 01-26-2012

 

I never lose; either I win or I learn.

 


MARS_REVENANT #39 Posted 02 April 2017 - 03:00 PM

    Colonel

  • Member
  • 809 battles
  • 7,873
  • [SICK] SICK
  • Member since:
    01-26-2012

Here is the final calculation and current SICK RATINGtm chart.

 

This is the calculation I used.  It is a combination of what was used in Hryonometr, as well we wanted to give average tier and W/R some weight in the calculation , as SICK has always been about winning.

 

SICK RATING = (((Assists*1)+(Kills*1)+(Ground Targets/2))/Battles)*((Average Tier*1))*(Wins/Battles*1.11)*(Survived/Battles*1.05)

 

There are a bunch of *1 in there that don't make sense right now, but those are place holders if we want to give weight to other aspects of the calculation.

 


Edited by MARS_REVENANT, 02 April 2017 - 04:27 PM.

1.9.x Forum Stats: Colonel; Member; 34638 battles; 7,526 message_img.pngMember since: 01-26-2012

 

I never lose; either I win or I learn.

 


pigeon_kicker #40 Posted 02 April 2017 - 03:44 PM

    Second Lieutenant

  • Open Beta Tester
  • 0 battles
  • 1,421
  • [SF] SF
  • Member since:
    08-04-2013

View PostMARS_REVENANT, on 02 April 2017 - 10:00 AM, said:

 

SICK RATING = (((Assists*1)+(Kills*1)+(Ground Targets/2))/Battles)*((Average Tier*1))*(Wins/Battles*1.11)*(Survived]/Battles*1.05)

 

Block Quote

SICK RATING = (((Assists*1)+(Kills*1)+(Ground Targets/2))/Battles)*((Average Tier*1))*(Wins/Battles*1.11)*(Survived)/Battles*1.05)

 

You have a square endian after survived

 


If you want to make god laugh, tell him about your plans for survival..

Lead developer for the NACCW web site. Author of the Pigeon Pak Mod Installer

www.team-sf.com

 

 





1 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users