Football Data (CSV, JSON) - UPDATED 16/08/17

Post Reply
welshboy06
Posts: 165
Joined: Wed Mar 01, 2017 2:06 pm

doovd wrote:
Sun Aug 13, 2017 12:28 pm
I think the nature of the data lends itself more to json as there are often one to many relationships (e.g. game has many goals). Thanks for this!
Yes, which is exactly why I chose json. Plus theres a neat library for python called jsonpickle, which lets me read and write my python objects directly to a json file. Much smaller and less overhead than a database or even a csv.

I believe the above json file has a slight error on some of the games dates (It points to a python object instead of showing the actual date) I've correct this and will be uploading the fixed version soon, as well as a csv copy of the BPL league.

Then I'll move on to scraping the other leagues.

Cheers,
Adam
spreadbetting
Posts: 3140
Joined: Sun Jan 31, 2010 8:06 pm

Does it convert well to a MySql database?
Tenable
Posts: 20
Joined: Sat Jul 16, 2016 4:04 pm

welshboy06 wrote:
Sun Aug 13, 2017 9:45 am
Hi All,

I've decided to start collecting football data. Mainly because I have an interest in the sport and also because of a certain jonnyg throwing stats around in a very hard to read format.

The data is quite simple and was just scraped from HKJC.

The data is in json format, but you should be able to convert it to csv and import it to Excel. I chose json as it reduces data duplication, is structured and can be read reasonably well by the human eye. It also works really well with Python (Which I'm using to scrape and also analyse the data)
What I’ve noticed from his posts is Jonnyg is years behind the curve when it comes to data and analysis compared to most people and especially the users of this forum,, something that has manually taken him 5 years and 10hrs a day of intensive typing to do you have just done in a day or two,, and in far greater detail,, I’m guessing most was even automated while having your Sunday lunch :lol:

With the amount of readily available data for every sport that can be downloaded, scraped from a number of sources or even collected in real time,, im lost as to why anyone would still be sitting doing this manually and collecting such little in the scheme of things
User avatar
jonnyg
Posts: 691
Joined: Wed Jan 18, 2017 8:11 pm

welshboy06 wrote:
Sun Aug 13, 2017 9:45 am
Hi All,

I've decided to start collecting football data. Mainly because I have an interest in the sport and also because of a certain jonnyg throwing stats around in a very hard to read format.

The data is quite simple and was just scraped from HKJC.

The data is in json format, but you should be able to convert it to csv and import it to Excel. I chose json as it reduces data duplication, is structured and can be read reasonably well by the human eye. It also works really well with Python (Which I'm using to scrape and also analyse the data)
The data contains the following info.
League
Season
Game Date
Home Team
Away Team
And also Goals and Red cards (Player, Time and Team)


At the moment I've only gotten around to scraping the BPL seasons that have all goal data on hkjc (04-05 to 16-17) I may also add in corners, however that would be total for the game, not timings.

More Leagues and seasons will be coming soon, but I'm pretty busy with work etc atm.

The file is a .txt file stored in the below zip (Couldn't upload .txt directly) and is less than 2mb! So should be easy to read and manipulate on anyones setup.

Just to note: The data is all scraped from HKJC, so if there are any errors it would be down to the data they provided.

BPL.zip

Now for one of the main reasons I did this, @jonnyg Asked me a question in another thread...
when you say easy ?

how easy ?





Well the answer to that question is 3.5gls (Total goals / Total Games)

Since the 2011-2012 season, the amount of games where the Home team scored first and the First goal was scored On or Before the 8th minute...
Total Games: 84 Total Goals: 294 Average Goals: 3.5
Min Goals: 1
Max Goals: 9

Cheers,
Adam

the question was rather different


"can you tell me for example what is the average goal production since 2011-2012 in games where the home team in the PL opened the scoring on 8 minutes ?
"
User avatar
jonnyg
Posts: 691
Joined: Wed Jan 18, 2017 8:11 pm

PL 2017-2018 in games where the home team opened the scoring on 8 minutes or before > exactly 8 minutes will be in bold

4-3 3-3

2016-2017

3-1 2-1 4-0 5-0 1-0 4-2 4-0 2-0 3-1 4-2 1-2 3-1 3-4 1-1 6-3 1-0 1-1 1-0 2-2 1-4 1-3 2-2 4-2 1-0 4-0 2-1 3-2 3-1 4-0 6-1 2-0 3-0 1-2 4-2 2-4 3-1 2-0 2-1 1-1

28-5-6 < average goal production = 3.85

2015-2016

4-0 2-2 3-1 1-1 2-1 2-2 4-1 3-1 4-0 3-0 2-0 2-0 1-0 5-1 2-1 2-2 3-0 2-1 3-0 3-1 2-1 2-0 3-1 1-3 1-5 5-1 3-0 3-2 2-0

2014-2015

2013-2014

2012-2013

2011-2012


will double check the average goal production data at the end
Last edited by jonnyg on Sun Aug 13, 2017 3:29 pm, edited 7 times in total.
welshboy06
Posts: 165
Joined: Wed Mar 01, 2017 2:06 pm

spreadbetting wrote:
Sun Aug 13, 2017 1:51 pm
Does it convert well to a MySql database?
I've not tried it myself, I don't see why not.
All the fields are labeled, so should be easy enough to map to tables. You'd probably need to make a quick python script to spit out the relevant sql.
welshboy06
Posts: 165
Joined: Wed Mar 01, 2017 2:06 pm

jonnyg wrote:
Sun Aug 13, 2017 3:00 pm
PL 2017-2018 in games where the home team opened the scoring on 8 minutes or before > exactly 8 minutes will be in bold

4-3 3-3

2016-2017
I dont understand what you're asking?
But I've made the data available to the forum, so I'm sure you could use it to prove your own theories.
spreadbetting
Posts: 3140
Joined: Sun Jan 31, 2010 8:06 pm

Thanks I'll give it a try after racing and thanks again for putting it up.
User avatar
Dallas
Posts: 22674
Joined: Sun Aug 09, 2015 10:57 pm
Location: Working From Home

jonnyg wrote:
Sun Aug 13, 2017 2:49 pm
"can you tell me for example what is the average goal production since 2011-2012 in games where the home team in the PL opened the scoring on 8 minutes ?
Since 2011-2012 = 3.73
11-12 = 3.28
12-13 = 4.0
13-14 = 4.25
14-15 = 4.25
15-16 = 3.66
16-17 = 3.37
User avatar
jonnyg
Posts: 691
Joined: Wed Jan 18, 2017 8:11 pm

there were many more games then 84 in the Pl since 2011-2012 where the home team opened the scoring on or before 8 minutes :!:
User avatar
jonnyg
Posts: 691
Joined: Wed Jan 18, 2017 8:11 pm

Dallas wrote:
Sun Aug 13, 2017 3:16 pm
jonnyg wrote:
Sun Aug 13, 2017 2:49 pm
"can you tell me for example what is the average goal production since 2011-2012 in games where the home team in the PL opened the scoring on 8 minutes ?
3.73
hang on will just check that
User avatar
jonnyg
Posts: 691
Joined: Wed Jan 18, 2017 8:11 pm

Watford
3 - 3
Liverpool View events More info
S. Okaka 8'
1 - 0
1 - 1
29' S. Mané
A. Doucouré 32'
2 - 1
2 - 2
55' Roberto Firmino (PG)
2 - 3
57' Mohamed Salah
M. Britos 90'
3 - 3

Arsenal
3 - 1
Everton View events More info
Bellerín 8'
1 - 0
A. Sánchez 27'
2 - 0
2 - 1
58' R. Lukaku (PG)
A. Ramsey 90'
3 - 1


Middlesbrough
1 - 0
Sunderland View events More info
M. de Roon 8'
1 - 0


Liverpool
3 - 1
Everton View events More info
S. Mané 8'
1 - 0
1 - 1
28' M. Pennington
Philippe Coutinho 31'
2 - 1
D. Origi 60'
3 - 1


Liverpool
1 - 0
Manchester City View events More info
G. Wijnaldum 8'
1 - 0


Stoke City
2 - 0
Sunderland View events More info
J. Allen 8'
1 - 0
J. Allen 45'
2 - 0

Swansea City
1 - 2
Liverpool View events More info
L. Fer 8'
1 - 0
1 - 1
54' Roberto Firmino
1 - 2
84' J. Milner (PG)

West Bromwich …
4 - 2
West Ham United View events More info
N. Chadli (PG) 8'
1 - 0
S. Rondón 37'
2 - 0
J. McClean 44'
3 - 0
N. Chadli 56'
4 - 0
4 - 1
61' M. Antonio
4 - 2
65' M. Lanzini (PG)


Manchester City
2 - 2
Arsenal View events More info
S. Agüero 8'
1 - 0
1 - 1
10' O. Giroud
K. De Bruyne 51'
2 - 1
2 - 2
68' A. Sánchez


Manchester United
1 - 1
Leicester City View events More info
A. Martial 8'
1 - 0
1 - 1
17' W. Morgan

Liverpool
4 - 1
Stoke City View events More info
Alberto Moreno 8'
1 - 0
1 - 1
22' Bojan
D. Sturridge 32'
2 - 1
D. Origi 50'
3 - 1
D. Origi 65'
4 - 1


West Bromwich …
2 - 3
Leicester City View events More info
D. Fletcher 8'
1 - 0
1 - 1
20' D. Nugent
C. Gardner 26'
2 - 1
2 - 2
80' R. Huth
2 - 3
90' J. Vardy

Arsenal
5 - 0
Aston Villa View events More info
O. Giroud 8'
1 - 0
M. Özil 56'
2 - 0
T. Walcott 63'
3 - 0
Santi Cazorla (PG) 75'
4 - 0
Bellerín 90'
5 - 0

Hull City
1 - 2
Tottenham Hotspur View events More info
J. Livermore 8'
1 - 0
1 - 1
61' H. Kane
1 - 2
90' C. Eriksen

West Bromwich …
2 - 2
Manchester United View events More info
S. Sessègnon 8'
1 - 0
1 - 1
48' M. Fellaini
S. Berahino 66'
2 - 1
2 - 2
87' D. Blind


Fulham
2 - 3
Liverpool View events More info
K. Touré (OG) 8'
1 - 0
1 - 1
41' D. Sturridge
K. Richardson 63'
2 - 1
2 - 2
72' Philippe Coutinho
2 - 3
90' S. Gerrard (PG)

Manchester City
3 - 0
Swansea City View events More info
Álvaro Negredo 8'
1 - 0
S. Nasri 58'
2 - 0
S. Nasri 77'
3 - 0

Everton
2 - 1
Hull City View events More info
G. Barry 8'
1 - 0
1 - 1
30' Y. Sagbo
S. Pienaar 57'
2 - 1

Fulham
3 - 2
Queens Park Ra… View events More info
D. Berbatov (PG) 8'
1 - 0
D. Berbatov 22'
2 - 0
C. Hill (OG) 41'
3 - 0
3 - 1
45' A. Taarabt
3 - 2
51' L. Rémy

Swansea City
4 - 1
Queens Park Ra… View events More info
Michu 8'
1 - 0
Àngel Rangel 18'
2 - 0
2 - 1
48' B. Zamora
Pablo Hernández 50'
3 - 1
Michu 67'
4 - 1


Fulham
1 - 1
Southampton View events More info
D. Berbatov 8'
1 - 0
1 - 1
85' R. Lambert (PG)

Liverpool
4 - 0
Fulham View events More info
M. Škrtel 8'
1 - 0
S. Gerrard 36'
2 - 0
S. Downing 52'
3 - 0
L. Suárez 90'
4 - 0

Norwich City
2 - 1
Sunderland View events More info
S. Bassong 8'
1 - 0
A. Pilkington 37'
2 - 0
2 - 1
44' C. Gardner


Reading
3 - 4
Manchester United View events More info
H. Robson-Kanu 8'
1 - 0
1 - 1
13' Anderson
1 - 2
16' W. Rooney (PG)
A. Le Fondre 19'
2 - 2
S. Morrison 23'
3 - 2
3 - 3
30' W. Rooney
3 - 4
34' R. van Persie

Wigan Athletic
2 - 1
West Ham United View events More info
Ramis 8'
1 - 0
J. McArthur 47'
2 - 0
2 - 1
90' J. Tomkins

Norwich City
2 - 0
Aston Villa View events More info
G. Holt 8'
1 - 0
S. Jackson 22'
2 - 0

Arsenal
1 - 1
Wolverhampton … View events More info
Y. Gervinho 8'
1 - 0
1 - 1
38' S. Fletcher


Manchester United
5 - 0
Wigan Athletic View events More info
Ji-Sung Park 8'
1 - 0
D. Berbatov 41'
2 - 0
D. Berbatov 58'
3 - 0
A. Valencia 75'
4 - 0
D. Berbatov (PG) 78'
5 - 0

Tottenham Hotspur
1 - 1
Chelsea View events More info
E. Adebayor 8'
1 - 0
1 - 1
23' D. Sturridge

Sunderland
1 - 2
Wigan Athletic View events More info
S. Larsson 8'
1 - 0
1 - 1
44' Jordi Gómez (PG)
1 - 2
90' F. Di Santo


Stoke City
2 - 3
Queens Park Ra… View events More info
J. Walters 8'
1 - 0
1 - 1
22' H. Helguson
1 - 2
44' L. Young
1 - 3
54' H. Helguson
R. Shawcross 64'
2 - 3

West Bromwich …
2 - 0
Wolverhampton … View events More info
C. Brunt 8'
1 - 0
P. Odemwingie 76'
2 - 0

Manchester United
3 - 1
Chelsea View events More info
C. Smalling 8'
1 - 0
Nani 37'
2 - 0
W. Rooney 45'
3 - 0
3 - 1
46' Fernando Torres
Last edited by jonnyg on Sun Aug 13, 2017 4:28 pm, edited 17 times in total.
LinusP
Posts: 1871
Joined: Mon Jul 02, 2012 10:45 pm

Thanks for sharing!

Jonnyg do you have to fill this thread up as well?
spreadbetting
Posts: 3140
Joined: Sun Jan 31, 2010 8:06 pm

LinusP wrote:
Sun Aug 13, 2017 3:40 pm
Thanks for sharing!

Jonnyg do you have to fill this thread up as well?
+1

Posting lists of scores with the odd winking smily really isn't needed on this thread
User avatar
Dallas
Posts: 22674
Joined: Sun Aug 09, 2015 10:57 pm
Location: Working From Home

Dallas wrote:
Sun Aug 13, 2017 3:16 pm
jonnyg wrote:
Sun Aug 13, 2017 2:49 pm
"can you tell me for example what is the average goal production since 2011-2012 in games where the home team in the PL opened the scoring on 8 minutes ?
Since 2011-2012 = 3.73
11-12 = 3.28
12-13 = 4.0
13-14 = 4.25
14-15 = 4.25
15-16 = 3.66
16-17 = 3.37
In case anyone want the actual data before the thread gets cluttered with it typed out

*Edit* looks like i was to late
You do not have the required permissions to view the files attached to this post.
Post Reply

Return to “Betfair Data”