Predicting Popularity on Spotify¶

This example uses the publicly-available Spotify from Kaggle to predict the popularity of Polish songs and artists on Spotify. We'll also use a model to group artists together based on how similar their songs are.

You can download the Jupyter notebook of this study here.

The "tracks" dataset (tracks.csv) have the following features:

id represents the Id of the track generated by Spotify

Numerical:

acousticness (range: [0,1])
danceability (range: [0,1])
energy, (range: [0,1])
duration_ms (range: [200000,300000])
instrumentalness (range: [0,1])
valence (range: [0,1])
popularity (range: [0,100])
tempo (range: [50,150])
liveness (range: [0,1])
loudness (range: [-60,0])
speechiness (range: [0,1])

Dummy:

mode (0 = Minor, 1 = Major)
explicit (0 = No explicit content and 1 = Explicit content)

Categorical:

key - keys on an octave encoded as integers in range [0,11] (C = 0, C# = 1, etc.)
timesignature - predicted time signature
artists - list of contributing artists
artists - list of IDs of contributing artists
release_date - date of release (yyyy-mm-dd)
name - track name

The "artists" dataset (artists.csv) has the following features:

id - ID of the artist
name - artist name
followers - how many followers the artist has
popularity - popularity of the artists based on their tracks
genres - list of genres covered by the artist's tracks

Import libraries¶

Start by importing VerticaPy and loading the SQL extension, which allows you to query the Vertica database with SQL.

In [1]:

import verticapy as vp
%load_ext verticapy.sql

This examlpe uses the following version of VerticaPy:

In [2]:

vp.__version__

Out[2]:

'0.9.0'

Connect to Vertica. This example uses an existing connection called "VerticaDSN." For details on how to create a connection, use see the connection tutorial.

In [3]:

vp.connect("VerticaDSN")

Create a new schema, "spotify."

In [4]:

vp.drop("spotify", method = "schema")
vp.create_schema("spotify")

Out[4]:

True

Data Loading¶

Load the datasets into the vDataFrame with read_csv() and then view them with display().

In [5]:

# load datasets as vDataFrame objects
artists = vp.read_csv("data/artists.csv", schema = "spotify", parse_nrows = 100)
tracks  = vp.read_csv("data/tracks.csv" , schema = "spotify", parse_nrows = 100)

The table "spotify"."artists" has been successfully created.
The table "spotify"."tracks" has been successfully created.

In [6]:

display(artists)
display(tracks)

	Abc id Varchar(44)	123 followers Numeric(7,2)	Abc Varchar(36)	Abc name Varchar(114)	123 popularity Int
1	0001K3ON4ACv7hte9Jlc6o	1.0		Chris	0
2	0001ZVMPt41Vwzt1zsmuzp	13667.0		Thyro & Yumi	39
3	0001cekkfdEBoMlwVQvpLg	80.0		Jordan Colle	0
4	0001wHqxbF2YYRQxGdbyER	6329.0		Motion Drive	19
5	0002XY9y3JhjzTZqNCqEcv	1828.0		Katie Price & Peter Andre	0
6	00045gNg7mLEf9UY9yhD0t	829.0		Kubus & BangBang	13
7	0004m70bDmySiyX0mbJMNS	0.0		Carla Sanders	0
8	0004mMWDlIBRhLMZP3p92i	4.0		Yung Danzy	0
9	00052qkj8osxzuMhyZA88Y	1.0		Stephen Olaussen	0
10	00062105bTKjdbdc1K1UpI	0.0		懾魂紫	0
11	0006sHOabJ20USMjG8cWtD	705.0		The Art Company	32
12	000778ICMgFxCapm7fVDAW	1.0		Polyphoniko Sygkrotima	0
13	0007DSGSwy4e3PdpExQESD	47.0		Detect Audio	0
14	0008EBLAehfRBSeStEUQmn	0.0		Hi Tide Wild	0
15	0008dSXw2QjJXEg3jNl6hG	0.0		prottaborton	0
16	00097QzAB3ui6aBPavao2O	0.0		Gastraws Gang	0
17	0009LMJQT62wdZVgnM4vBy	97.0		Bernd Mehler	34
18	0009v6e5hmLdAqSFTLcKry	1417.0		Komissar	0
19	000AK4sjc0Np5MbCQ6JC1U	11.0		Tony Camillo's Bazuka	0
20	000AreMafq1WYcwszMM61y	0.0		Da Brxkxnhxrt Boys	0
21	000BQxIqXh6LNfsVTt703l	0.0		CecilyCecily	0
22	000BblCiHJeKvtiq5aiHOs	1994.0		51 Koodia	34
23	000CkqJliuK4m3uNRl0jd2	0.0		Young Ranz	0
24	000Cz1Pts8aGpDLBvrc4wi	597.0		El Negrito	18
25	000DEqM4VjDJIUIFJ2LNk1	0.0		Han Young Chul	0
26	000DpPOWQC5JxdVapodOTM	0.0		Blu Bill$	0
27	000Dq0VqTZpxOP6jQMscVL	4923.0		Thug Brothers	14
28	000ESzt0wlQI41YbKYGIkf	192.0		Zach Russell	19
29	000EYNgg9zCfoWNtDBemdu	0.0		Coral Anointed vases	0
30	000Eamfs6aR2aDr5svq1XF	26.0		Scarlett Gonzalez	0
31	000FVR595IhUXTTTRwMgyQ	0.0		Mr. Todd	0
32	000G2jFbXDhyuRZGw3jX0l	0.0		Ci	0
33	000GXXsAUTgVJfJzOEMIQz	3.0		Seanine Seven	0
34	000Gam69yUQckclDmjLvlL	1331.0		Tony Victor	0
35	000IpCJToEX5T7oDZtXSeW	0.0		@Harlem Fuego	0
36	000JK7wzCV5X4mg3MbsuGA	17.0		Delmonaco	0
37	000JMMX1ZXl1WQIu9GGxCI	0.0		P.O.P	0
38	000JdbKW4RCTX2eV304DDR	5.0		Yuni Ayunda	0
39	000LGmf577qlcsgUZMWJLL	0.0		Mel Vine	0
40	000MS0paUmy5CYke9uzmAO	0.0		Zues Carter	0
41	000N5stG2Co67GJJeqBJpy	0.0		Hajoso	0
42	000NbZqISnuKaqmw33JxTV	1418.0		Carlos Nakai	9
43	000NkLnzi66UcbuTttHDqX	1.0		R.J. Paradee	0
44	000Nolc0A0ZuWkFzNYVYdU	16.0		Peter Clark	1
45	000OybtozoTUivAiSvHstz	3.0		Mohamad Sparow	0
46	000PIVxMsaaWl1FtigEyDo	0.0		Halo Kitty	0
47	000PWQyVzAHQICDwxkwEvy	0.0		TrulySincere	0
48	000QJOWUPdmFGneapRPzMx	144.0		The Red Carpet	0
49	000R5IwxLTiwZ1O0lsEgMZ	2.0		Verónica Falco	0
50	000SLLJ1xQBlePMwMXtkK5	0.0		SlimStnr	0
51	000Saas96Bv97L2quWFvJl	4.0		3MYR	0
52	000SwGJYBhOXUnpXva6H3v	3.0		Lucrative Burrito	0
53	000SzAei1SOa0HERIjNNqb	0.0		Man Gogh	0
54	000TLrYTTOschyZq06KUyV	0.0		Sadia Naeem	0
55	000TPdyW9vCzS7vAboRLPP	0.0		Florence Mestais	0
56	000TcTgrYANFi4NWE4K0fi	0.0		B Greazy	0
57	000UmiuzgqlUjKHpvIWmAo	3834.0		Lisa Knowles-Smith	27
58	000UxvYLQuybj6iVRRCAw1	91.0		Primera Etica	1
59	000VNWB7V9Xt6HDlPnP1FM	1.0		JVC	0
60	000VtE5U1okZpIrAilYHpJ	0.0		Элиана Бублик	0
61	000WDFTEMnS2Ey1iyugp0x	3.0		Nyell Jeudy	0
62	000WMX8CCUlKyWxaOasSNZ	346.0		John Wang	9
63	000WyXOWRZlAYSCHOl6vbw	5.0		Naveen Hiremath	0
64	000XIsEmxd7gGlxWDFCD43	2.0		YkUno	0
65	000ZSUbEUZFtMiWHIYBv3W	0.0		Cathy Fishburn	0
66	000ZhE5mMcLVkkIMYScxGn	0.0		Torrober	0
67	000ZqKpgzccO8TcuxHK6VW	2.0		Alibastard	0
68	000dicAA3QWT6m118rFXLO	134.0		Wings of the ISANG	0
69	000e7vL2PldGExOPwRFmfF	0.0		Tea K	0
70	000eJgbruxYJJGYpzP33bd	0.0		Alonzo Garton	0
71	000fiSPJm8TYO2T7UPExMS	0.0		Bobbie Johnson	0
72	000h0et7HHi3pmuWZ80LNj	0.0		NT Money	0
73	000isQj1PXwsP5kJF0F1Fi	14.0		YoursTruly	1
74	000j0w6ospeDya5uUncVNQ	0.0		Pradeep Dutto	0
75	000j8IPyHeLwvo4ScFUJMn	0.0		El Bkno	0
76	000kGSv1ge0O6gJDYkOBLZ	11.0		Electra Vega	0
77	000lFylX9iGEN12AIz7qkF	0.0		Mr. Goodbarz	0
78	000lUERE6k2fnBFdDn58yb	6.0		Décio Pulini	0
79	000lc4PE8DuDC8O1n2jsao	0.0		Luh H	0
80	000nr5NQY4yigOdegZaKoV	7.0		Ghostfog	0
81	000ohFb1Uurub7P6UxvkJ3	0.0		Leek Sosa	0
82	000p4jMMhpEHq1h6PFCyO1	335.0		Anne Veski	24
83	000pQJoy8qFwpZwB5T8htu	2.0		RCHILL	0
84	000q9sWX0l76D7fijF5iOl	19.0		Room 404	0
85	000qAzhdvazIILeGHZhfHE	1.0		Deja Amari	0
86	000qxDglux9HycJpOEigNr	0.0		Em. Mâu Huỳnh Điệp	0
87	000rFz8r7IQznjS1qjIsyk	0.0		Soph vs. Hypa	0
88	000rLf9cJqQOstUbHCzl77	5.0		Raffaele Cardone	0
89	000rOFFfUyGGzKmB8XYIuu	132.0		Milton Cross	0
90	000rU47x3QblbKdSE2VRFm	2.0		Ron One	0
91	000s6SnJAGVnsoT33VKcSW	59.0		Poker Pets	8
92	000s7VhvjpDLaDf3L140Zc	0.0		古風な音楽を学ぶ	0
93	000sD0EGScyHwmiRNF0RRP	9.0		Doblado	0
94	000spuc3oKgwYmfg5IE26s	301.0		Parliament Syndicate	8
95	000tKel9gS8530Lh9kd8xb	2.0		Rupantar	1
96	000vMR3cHSTJ88w3PJWHJB	114.0		Jeff Kryka	20
97	000vYbdVeUF5Mx5RLgT2iR	0.0		608 A	0
98	000vgESUHZ8XyaYCCxapde	7.0		Maggie Lee	0
99	000vr8pSUPAUgtGPpJbGEH	0.0		Dro-Delecity	0
100	000vwlhkQBKs6RjdBkjltZ	41.0		Soul Creation feat. Kenny Bobien	0

Rows: 1-100 | Columns: 5

	Abc id Varchar(44)	Abc Varchar(96)	123 popularity Int	123 duration_ms Int	123 explicit Int	Abc Varchar(100)	Abc Varchar(156)	Abc release_date Varchar(20)	123 danceability Numeric(6,4)	123 energy Numeric(8,6)	123 key Int	123 loudness Numeric(8,4)	123 mode Int	123 speechiness Numeric(7,5)	123 acousticness Numeric(6,4)	123 instrumentalness Float	123 liveness Numeric(7,5)	123 valence Numeric(7,5)	123 tempo Numeric(9,4)	123 time_signature Int
1	0004Uy71ku11n3LMpuyf59		34	258907	0			2012-01-01	0.623	0.599	6	-9.255	1	0.0255	0.177	0.00148	0.0748	0.381	140.056	4
2	000CSYu4rvd8cQ7JilfxhZ		43	189107	0			2005-05-03	0.624	0.766	2	-7.86	1	0.0731	0.345	0.0	0.113	0.548	95.128	4
3	000DsoWJKHdaUmhgcnpr8j		16	234533	0			1977-07-11	0.572	0.608	7	-10.224	1	0.555	0.642	0.00022	0.344	0.559	106.453	4
4	000G1xMMuwxNHmwVsBdtj1		32	182347	0			1978-09-23	0.256	0.895	2	-4.86	1	0.0707	0.0131	0.000106	0.0821	0.555	191.307	4
5	000KblXP5csWFFFsD6smOy		35	240013	0			2006-07-08	0.619	0.518	4	-5.392	0	0.0534	0.805	0.0	0.102	0.314	143.757	3
6	000Npgk5e2SgwGaIsN3ztv		0	206972	0			1953-12-31	0.277	0.145	3	-19.898	1	0.0845	0.98	0.879	0.111	0.494	75.644	4
7	000P83HDtOHcNVFZy7Q2Yu		18	242493	0			1980	0.619	0.561	7	-8.377	0	0.0736	0.438	4.17e-05	0.126	0.696	88.94	4
8	000RDCYioLteXcutOjeweY		54	190203	0			2018-02-12	0.679	0.77	0	-3.537	1	0.19	0.0583	0.0	0.0825	0.839	161.721	4
9	000TXa2oEZLYfQGPCiv23U		6	243200	0			1982	0.61	0.91	6	-10.981	1	0.043	0.0606	0.0828	0.148	0.775	129.639	4
10	000TiSS4vK5su0MkoFyQbd		44	159250	0			2017-12-22	0.72	0.646	6	-7.691	0	0.106	0.237	0.0	0.127	0.0958	80.013	4
11	000d0lQMYaRR5ZXS9nTeiN		32	258533	1			1995	0.826	0.827	1	-8.393	1	0.205	0.0106	4.62e-05	0.17	0.49	92.137	4
12	000jBcNljWTnyjB4YO7ojf		0	179747	0			1954-01-01	0.788	0.808	5	-6.59	1	0.0395	0.656	0.0	0.154	0.969	113.046	4
13	000mGrJNc2GAgQdMESdgEc		0	498560	0			1951	0.0753	0.15	2	-16.705	0	0.0371	0.302	0.884	0.121	0.0351	76.558	3
14	000q9YBtesW8yPwlmus12C		3	178515	0			1975-01-01	0.715	0.254	5	-15.459	1	0.0343	0.878	0.0	0.0867	0.559	94.205	4
15	000u1dTg7y1XCDXi80hbBX		58	300600	0			1989-01-01	0.756	0.47	7	-12.615	1	0.0394	0.196	0.000487	0.126	0.43	120.484	4
16	000x2qE0ZI3hodeVrnJK8A		37	200627	0			1975	0.507	0.356	0	-14.24	1	0.0306	0.339	0.0	0.18	0.472	134.248	4
17	000xYdQfIZ4pDmBGzQalKU		53	187119	0			2016-11-04	0.509	0.803	0	-6.743	1	0.04	0.684	0.000539	0.463	0.651	166.018	4
18	000ydDsz4ijCNUsmoIeZcj		13	98667	0			1978-09-22	0.637	0.353	1	-13.978	0	0.039	0.233	8.44e-06	0.102	0.662	139.209	3
19	00105Q1NbnHkf8R5eXXeXm		44	234067	0			2011-08-23	0.777	0.832	6	-3.982	0	0.0403	0.334	1.08e-05	0.0765	0.72	94.964	4
20	00147h65HDYSncB3byziPP		12	241440	0			1961-01-01	0.238	0.253	10	-13.898	1	0.0315	0.877	0.0	0.0748	0.164	90.855	4
21	0018QzCxmMrpa0FubbNdak		31	147933	0			1993-01-01	0.804	0.796	9	-8.616	1	0.032	0.319	0.000127	0.0987	0.965	113.964	4
22	001AGGmHtn4FUzXgAUxz39		13	191504	0			1991-01-01	0.516	0.295	7	-15.558	0	0.0662	0.676	0.0	0.195	0.881	150.542	3
23	001GxQGaFwTjxM7tmKbMF3		30	214493	0			2004	0.547	0.731	11	-6.055	1	0.0737	0.206	0.0	0.0939	0.965	159.801	4
24	001I9iXPNwN0HlHfdQjDIX		21	267840	0			1988-01-01	0.453	0.688	5	-9.075	1	0.0556	0.0267	5.21e-06	0.293	0.42	120.953	4
25	001IcYypSE1ryXKY5KNIin		58	2704053	0			2016-10-08	0.111	0.376	10	-27.786	0	0.0863	0.566	0.716	0.31	1e-05	95.28	5
26	001LvKFwYbfKYPQF2Fiv77		34	190787	0			1954-01-01	0.555	0.533	3	-7.609	1	0.0372	0.802	0.211	0.27	0.882	82.68	4
27	001UI3J6PKAEnBgqrwGGQC		21	163840	0			1977	0.839	0.406	7	-17.732	1	0.1	0.0555	2.39e-05	0.107	0.559	139.367	4
28	001UkMQHw4zXfFNdKpwXAF		69	191053	0			2019-04-05	0.573	0.846	7	-4.866	1	0.0344	0.0037	7.87e-05	0.29	0.562	127.061	4
29	001YQlnDSduXd5LgBd66gT		36	177267	0			1984-02-06	0.554	0.921	2	-4.589	1	0.0758	0.0194	0.0881	0.329	0.7	183.571	1
30	001ZmOPuWEW5czwun7nkha		8	92867	0			1952-04-11	0.16	0.12	5	-15.244	1	0.0367	0.915	0.00024	0.28	0.162	169.722	1
31	001e2JrYMwnTeRnxf3sgIz		12	364280	0			1953-01-01	0.192	0.0763	3	-24.388	1	0.0425	0.951	0.284	0.0702	0.0554	91.097	3
32	001f6XLtM53gwKSauiUcKI		31	379987	0			1977-01-01	0.556	0.315	1	-12.923	0	0.228	0.151	0.0	0.102	0.364	129.998	4
33	001gx41rQo0bKh063TrC1I		22	382030	0			1984-01-01	0.503	0.233	10	-16.827	1	0.0675	0.813	0.0129	0.546	0.153	129.254	4
34	001hyVfKgE2R1a0TBXeLlV		52	219742	1			2019-05-20	0.666	0.554	11	-10.353	1	0.0325	0.67	0.00025	0.0631	0.414	130.052	4
35	001s02k3baVIxp6lIVRu6k		0	205473	0			1937-12-31	0.426	0.0884	3	-10.925	1	0.0639	0.988	0.00113	0.393	0.396	80.21	3
36	00218i6cYENiDszmBKsYYg		55	132657	1			2018-08-23	0.706	0.473	0	-8.085	0	0.0926	0.984	0.0242	0.106	0.59	87.412	4
37	0022sbR4gAWFhpMaYLbtnX		22	216561	0			2009-01-01	0.748	0.865	8	-5.663	0	0.0327	0.18	0.0	0.133	0.879	138.018	4
38	0024tEymsoc9FyKUauQngQ		41	546107	0			1977	0.305	0.234	1	-18.255	1	0.0259	0.752	0.929	0.132	0.078	82.453	3
39	0025JMWRhsWx0GXdlzhHMO		1	197293	0			2016-05-27	0.252	0.877	0	-7.876	0	0.0481	0.0001	0.0807	0.0685	0.332	103.784	4
40	0029TH4cSnQ12KKfHaq11C		38	198960	0			2000-01-01	0.616	0.819	5	-4.109	1	0.0303	0.0025	0.000114	0.215	0.962	126.816	4
41	002Ac7LJjVIcfFYMZ6Irue		3	177613	0			1969-08-23	0.4	0.391	6	-10.487	1	0.0276	0.485	1.18e-05	0.118	0.401	83.459	4
42	002CcxKpBE1tfKOy2CRaWr		0	194154	0			1955-12-31	0.505	0.419	3	-10.511	0	0.316	0.983	0.0512	0.148	0.654	134.658	3
43	002DkDzzQ7lrgaqWBF2o1M		37	205280	0			1985-01-01	0.509	0.715	7	-6.419	1	0.176	0.0251	0.0	0.419	0.833	178.144	4
44	002KIBXwb0pa66mPGuPKMr		33	294249	0			1990-03-01	0.641	0.854	9	-9.109	0	0.0291	0.0017	0.348	0.325	0.871	121.244	4
45	002TGKi4LBwxYodlfWoaN0		32	132093	0			2000-01-01	0.763	0.881	5	-7.409	1	0.0311	0.596	3.11e-05	0.148	0.987	143.164	4
46	002aR3zqP6SvscCnPT44on		0	726436	0			1955-01-01	0.203	0.107	4	-21.175	0	0.0399	0.925	0.832	0.112	0.0375	107.391	4
47	002ak2fuoNB5KW0plsN1jp		26	213533	0			1990-04-10	0.731	0.39	9	-12.023	0	0.0286	0.421	2.55e-06	0.0785	0.696	97.397	4
48	002c2TeuD0GPfL2ahcmWEF		0	178204	0			1945-12-31	0.676	0.245	6	-10.906	0	0.0945	0.995	0.571	0.134	0.748	75.444	4
49	002dEfJAJnpoDU5cUhMmZJ		0	158145	0			1940	0.354	0.353	9	-12.506	1	0.068	0.985	0.278	0.0957	0.649	152.424	4
50	002dh6a4LfxfGGnhPZY4fG		0	309347	0			1951	0.389	0.312	7	-9.839	1	0.04	0.919	4.08e-06	0.425	0.289	119.479	4
51	002jsFzKzBLqDS71IZpnd6		5	211293	0			1971	0.691	0.4	4	-8.828	1	0.0367	0.755	0.000125	0.11	0.716	121.355	4
52	002lZtb0gYsfOJKqIouc0j		14	53600	0			1972-09-11	0.606	0.954	3	-5.939	0	0.934	0.959	0.0	0.915	0.288	76.856	3
53	002sGwDZYna3CKXbYIilHz		0	166949	0			2009-12-16	0.525	0.659	9	-14.273	0	0.0314	0.218	0.865	0.119	0.739	128.002	4
54	002x5YvGv3c0OW9HOoa4lG		10	173035	0			1977-01-01	0.745	0.586	10	-6.647	1	0.0627	0.629	0.000382	0.34	0.95	90.111	4
55	002zOHMdBKYgNGtmmHSE2D		5	250893	0			1959-02-02	0.39	0.699	7	-9.87	1	0.807	0.797	0.0	0.7	0.791	164.58	4
56	00309sp8DDeN07RtAyBKIX		34	251646	1			1992-02-24	0.57	0.881	7	-12.808	1	0.0483	0.0007	0.19	0.334	0.623	128.91	4
57	0033rRpGIViA0RngXc0jtL		0	245957	1			2012-11-01	0.175	0.0682	5	-16.955	1	0.0419	0.937	0.888	0.226	0.0354	118.248	4
58	0034Fs63aj5J6FTxe5U2Ru		11	777667	0			1983-06-10	0.466	0.419	0	-10.342	1	0.0371	0.698	6.09e-05	0.209	0.277	122.637	4
59	0036VchQkckr6Xxz7WYEh0		31	232587	0			2010-03-08	0.707	0.266	1	-12.338	1	0.0307	0.766	3.72e-05	0.105	0.473	92.008	4
60	0038DNjmwf6yFivn7PLwqP		21	336653	0			1983	0.898	0.739	9	-7.487	0	0.0605	0.225	0.000128	0.285	0.883	113.884	4
61	003FTlCpBTM4eSqYSWPv4H		69	233267	0			2002-10-15	0.553	0.717	7	-5.857	1	0.0318	0.0001	0.128	0.0385	0.318	127.947	4
62	003IDC4FUadw6ocSKqWHtq		22	193760	0			1988-01-01	0.574	0.59	7	-7.879	0	0.0241	0.0844	0.0	0.0676	0.346	102.683	4
63	003IdD0Ir5LSZHlrPpLZlm		27	120907	0			1975	0.293	0.186	7	-27.67	1	0.0419	0.989	0.848	0.109	0.796	152.045	4
64	003IvyvD1M7gdSDK3z5Kfd		36	143200	0			1996	0.716	0.33	5	-11.764	0	0.0413	0.57	1.79e-05	0.138	0.554	117.835	4
65	003JzPprzThp8SHUctgXnn		10	182413	0			1952	0.461	0.454	7	-8.499	1	0.0419	0.797	0.00612	0.107	0.298	142.197	4
66	003KPastz3K8HNyHb8dixS		26	303120	0			2000-04-09	0.65	0.83	0	-5.758	0	0.0281	0.106	0.0	0.318	0.632	132.353	4
67	003PmYxl19KBg6m4uxClbR		22	217267	0			1990-01-01	0.47	0.712	5	-5.462	1	0.0317	0.164	0.0	0.225	0.28	146.181	4
68	003ShLCyC9ACAsdUWOcnk0		19	228573	0			1993-03-12	0.773	0.587	7	-12.196	1	0.039	0.829	3.94e-05	0.0719	0.86	130.138	4
69	003WuNd8vTwCW4JyhFQMYT		0	42267	0			1954	0.656	0.518	4	-18.673	1	0.101	0.987	0.132	0.762	0.932	142.216	3
70	003YOvQRIurKekUuUMDtkZ		44	293616	0			1999-03-17	0.801	0.555	6	-7.871	1	0.0321	0.461	0.0	0.236	0.806	142.343	4
71	003Ymyk0DvgqFmqEmA5Vzc		24	249160	0			1978	0.693	0.57	0	-9.949	0	0.0309	0.38	0.0	0.28	0.855	123.193	4
72	003d3VbyJTZiiOYT2W7fnQ		41	290933	0			1973-01-01	0.461	0.32	0	-16.337	1	0.0327	0.266	3.5e-06	0.211	0.487	134.881	3
73	003fzMu2jBvZqXNPMYNYox		32	213107	0			2009-09-29	0.567	0.603	7	-8.223	1	0.044	0.317	3.7e-05	0.13	0.681	159.934	4
74	003gesN9g85fPpsH1Gx5OO		47	196040	0			2001	0.792	0.952	2	-6.749	1	0.055	0.134	0.000263	0.254	0.968	106.009	4
75	003hDp0MCmLiYKYRfzbhMo		18	229227	0			1996	0.621	0.817	4	-3.34	1	0.0526	0.509	0.0	0.156	0.976	155.129	4
76	003noSDLb7rvHSJ2FPYmDX		38	193867	0			1995-01-01	0.566	0.95	2	-9.146	0	0.0376	0.0035	0.00493	0.227	0.759	148.029	4
77	003oRl3zfRZvhttUxM9Gr5		12	238707	0			1986-11-12	0.76	0.746	2	-5.179	1	0.271	0.322	2.04e-06	0.0457	0.892	137.078	4
78	003vvx7Niy0yvhvHt4a68B		82	222973	0			2004	0.352	0.911	1	-5.23	1	0.0747	0.0012	0.0	0.0995	0.236	148.033	4
79	0043vCcTBYYsmUPzCNd1JQ		11	224627	0			1989-01-01	0.749	0.756	9	-6.095	1	0.0374	0.0061	0.0	0.0332	0.861	122.414	4
80	00469l4g2MMTCWwPudV9Qq		1	255547	0			1960-01-01	0.682	0.262	5	-14.67	0	0.0495	0.765	0.0	0.228	0.598	123.795	4
81	0046quUYhSAFccrKIC3Iht		43	284973	0			1985-06-01	0.48	0.435	1	-9.959	1	0.0305	0.207	5.83e-06	0.196	0.328	144.435	4
82	0047CfWoMPpXkZlizPQAb2		27	236213	0			2004-11-23	0.856	0.754	7	-2.728	0	0.0416	0.0294	1.49e-05	0.206	0.722	98.018	4
83	004ADkC8JLeDkT5HGsPDBm		4	239867	0			2005-11-29	0.559	0.713	8	-4.029	0	0.0318	0.123	0.0	0.1	0.734	164.048	4
84	004Me6nm9MknKeGKLnWx79		4	223240	0			1979-04-30	0.705	0.584	9	-9.997	1	0.0296	0.158	5.39e-06	0.234	0.831	98.844	4
85	004NJ3y5ED5zhn5adktc8g		0	184812	0			1926	0.378	0.598	3	-6.381	1	0.0434	0.996	0.607	0.43	0.649	198.492	4
86	004Oj2hkXAaenToKOLbYla		0	107760	0			1956	0.764	0.302	2	-14.698	1	0.037	0.911	0.000446	0.261	0.829	117.191	3
87	004TG0nRHejwSKisvwTcAB		31	204853	0			1963	0.22	0.0447	4	-25.514	1	0.0384	0.995	0.941	0.101	0.197	68.349	4
88	004TP6xsBixiK3zqOfPBEx		2	121060	0			1958	0.708	0.178	10	-17.491	1	0.958	0.608	0.0	0.234	0.561	100.121	3
89	004UHJTfLZH02Xds3ETd7e		0	254640	0			1953-01-01	0.414	0.425	2	-9.299	1	0.0378	0.891	0.0164	0.37	0.533	98.013	4
90	004VesjMxIn4AzXd8c52n1		5	392507	0			1990-01-01	0.568	0.62	11	-11.878	0	0.0274	0.58	0.00539	0.0846	0.418	100.897	3
91	004XgNPueIQSJrK25rzP3D		23	243533	0			2012	0.536	0.506	0	-9.836	1	0.0277	0.0009	0.0	0.0964	0.306	83.462	4
92	004a0SY8lPBwVnLCtO9aOO		36	72223	0			2016-12-25	0.0	0.00125	1	-27.592	1	0.0	0.908	1.0	0.111	0.0	0.0	0
93	004cCP7Csq7U0m67DDzEFs		0	72147	0			1946	0.419	0.0874	3	-27.612	0	0.0419	0.995	0.922	0.12	0.507	64.008	5
94	004dqsaJ3B8BBpqpN0F4YT		31	186333	0			1989	0.665	0.5	7	-9.579	0	0.0345	0.0862	2.33e-06	0.104	0.95	119.969	4
95	004ltC6j7SM8J06Egoa4wN		19	251960	0			1999	0.762	0.824	7	-7.571	1	0.0723	0.155	9.56e-05	0.097	0.756	98.072	4
96	004q4eDxR33ci4f8m4flwl		43	171627	0			2016-08-26	0.838	0.703	1	-6.195	0	0.05	0.768	1.56e-06	0.0861	0.658	104.982	4
97	004sbHg1qMzdrDXdxJpkj1		5	262840	0			2021-04-02	0.568	0.517	6	-7.316	0	0.0894	0.146	0.0	0.343	0.578	104.441	4
98	004tIr2Xy3FJVesPxRBLqm		2	226853	0			2020-09-25	0.683	0.572	7	-7.086	1	0.0447	0.11	1.54e-05	0.0895	0.583	100.035	4
99	004tQ7CGOm6ZKbmhdTgKsM		17	140120	0			1995-06-20	0.683	0.479	1	-11.574	1	0.942	0.367	0.0	0.337	0.707	76.782	1
100	004uWjBm1cOgxJAwQb6X8P		39	260467	0			1993-11-01	0.774	0.634	0	-11.401	0	0.0487	0.2	0.00976	0.22	0.933	119.953	4

Rows: 1-100 | Columns: 20

Data Exploration¶

Our "artists" dataset is too broad for us to use right now; we're only concerned with Polish artists, so let's extract and save them to our Vertica database.

In [7]:

# filter polish artists out of the 'artists' dataset using information in 'genres' column
polish_artists = artists.search("genres ilike '%disco polo%' or genres ilike '%polish%'") 

# save it to the database
polish_artists.to_db('"spotify"."polish_artists"', relation_type = "table")

Out[7]:

	Abc id Varchar(44)	123 followers Numeric(7,2)	Abc Varchar(36)	Abc Varchar(114)	123 popularity Integer
1	004reCzVFOidvBuYrYia9Y	12726.0			27
2	00drc18J6PkIXn24widBC5	3.0			0
3	00ekfPE5ZS3NwF8H8o8GBk	17574.0			47
4	0138Hlfmap3fEvKTqmZhE9	1289.0			5
5	018gIUaP08hROTOiVdiEQ3	584.0			8
6	01BlTZ696EkKe6xr56Gu6G	362.0			3
7	01TgMAgIALWvVXlKjUwpfn	1063.0			9
8	0244q9rIqAIzBFXKNNRN6O	27393.0			40
9	02Cq85QmaYHDi4dW7AxTRZ	749.0			9
10	02ESuuto8Jwyo4PeiJ1Xim	149.0			1
11	02JmHOSFJi2bLjGnO274di	8817.0			31
12	02LrsTMdnHVvKmXxN0epQF	753.0			22
13	02eZEXslMzAjHDkygNJHSX	8010.0			26
14	02keDoJak6YO12KBJFMFNm	1873.0			15
15	02tQ309SzZZ0bYs2yyO60G	11734.0			30
16	0338weYyACbkc5ERuLnFTa	270.0			17
17	033WIygOyXwUjc1vfCGxJ2	126.0			0
18	03Dy3XKBUsC3vJLCuF0T7I	152.0			1
19	03KLzHVK6la8dVop1iVI5x	63817.0			50
20	03ZzgzybQr8UyvWCMSCvRy	1363.0			14
21	03jLJnyfZXs1ssrIALfGRm	2633.0			22
22	03ohDYwWFrXfgp0VEtSTiF	706.0			9
23	03qKjVTzyKc3SyTjHaOpFc	2376.0			22
24	03rREATXGWcD2CfG3OXDZY	10155.0			44
25	03xKZpOUZOQjf7g5WBN4ee	3676.0			30
26	03yP3BHBnpGyvddEoIGnsx	2089.0			16
27	04Lio76CKJCMPbK5hV6J4w	1876.0			5
28	04Loj16dRX1yZodeEQlCOv	308.0			1
29	04WxKoI0kS5JclvQ8rn8qp	13.0			0
30	04bDWf1u7HxKdskC3N2nIk	27127.0			37
31	05AVHcWP9DF6y6LEU845uz	1545.0			15
32	05Fgqq7GfWeNol1TR5H3og	15868.0			35
33	05UsyksBcAUVdfyREMxbDm	295.0			11
34	063D0MKbIbbBjKgtYRGBga	7458.0			36
35	0690wuO0NVERuqxuoi2mTF	319.0			4
36	06O52v4thQuBoLC6jWatGW	21.0			0
37	06UcKJxYJXthEwn0c8XOCt	11024.0			20
38	06wBGqhkbyUAtVNMbbcK1x	607.0			8
39	070tdNOiP3pIsGlqNfVkG3	86130.0			51
40	072HrG3T5BaaBj4YhKIkxv	1166.0			8
41	07ILo13zpakvXxTL3VtqwS	540.0			10
42	098RsUTij7grC7evZUhWwA	720.0			31
43	09MjLGtslj39ILxA1MqUny	556.0			7
44	09ScR35g0VzipHacuPtXZd	440.0			7
45	09Z3SI4GkhYjpCB6884vC8	10395.0			25
46	09j4UTVH7vk7fVfVB71roU	348.0			1
47	09u3N07jllcbrnkvjxNBSl	1117.0			15
48	0AYJ3eg4zKi9ilGrhVaINs	2186.0			18
49	0AZgkXW6n0zfyOhVAnIopA	1109.0			33
50	0At3wjxYzZL9WwqbFR0JL8	24.0			0
51	0BBB9DjvskQV0oReJMxTP1	30889.0			45
52	0BQIhJ61mCyaOrVrMJ7e8k	5.0			0
53	0CEw36eWG0dYKCXOX8eUoO	77804.0			47
54	0CI4rQj50Dcr30HpiD2LF6	165.0			8
55	0CgCy79P84g1meaXcwwFqZ	80.0			0
56	0CsrftI3Zs3nvfSW6MRglc	50.0			0
57	0D5kXlS7UOApMpTyuSrFAW	40370.0			39
58	0D9mwbJP5sUH7XYXg4F7u9	580.0			4
59	0E6TslMisIITlZ1QjjPXeo	110.0			0
60	0EDBV0NVPOftbsEM0fg7WZ	2004.0			18
61	0EMDndPZcpfg9Qqgos0S7G	73.0			0
62	0EPzUAW8kwuPedmmVP6n9S	99964.0			53
63	0EQaqT3oKtxAGR0Y5c1Jme	3572.0			11
64	0EYfWGAHPugeWUKKvoMU79	336.0			4
65	0EalHNt7jDL6Tc7XSunjXu	21.0			0
66	0Emf6MyFoCjKazTqoaUu6T	1107.0			8
67	0EvkY8O19trlgsfrVOTQgg	26661.0			31
68	0F1DvSOKRaSA6XKSwDNs40	10792.0			45
69	0FKOL5wp6sgB8VRNsJaUlz	430.0			5
70	0FbccBQBb69lfv4arbt6kX	9237.0			36
71	0G2VUqbZ4C28aN9y41Wp3G	1186.0			17
72	0G6miz5dLrc3NZWi4ZYdJK	2813.0			35
73	0GF5CJ7nKXsMTiWHK4ZQJN	30925.0			46
74	0GPJYkHJm0Fpbhjovpm1h1	2261.0			41
75	0GPfyyiTlLdG6rQthueRBM	682.0			26
76	0GQZc3zcll9HXIVaUA1XzJ	11211.0			32
77	0Gfk7Ww29CWVyrnkqC4KUt	7.0			0
78	0GnO5BjJfHFwkesoObGU36	61.0			1
79	0GsCeqHAG63k8CRj1NH8e4	164.0			0
80	0GxARImYCmCNz0v04YjPq2	179.0			1
81	0GykMtlKoc68Hj2jwZLXul	79213.0			49
82	0HC5DGqdUzXorIXUudkeWG	1805.0			29
83	0HLMuuBFA7R4boMxVl9QgQ	9967.0			29
84	0HTub0NhKSRgggtmJBP9aR	59.0			0
85	0HZL4dV60t13CHasIHwaLP	385.0			4
86	0HhejlCvg1WCO9nXNZGEkc	144.0			1
87	0Hob9LUr2x0SULSZjuf6li	10701.0			42
88	0Id5ZU9SxHcgE32nfJMTbh	259.0			19
89	0It4rGfBk31UDyK9x6uZvP	3056.0			13
90	0IuXBtCmOjyRjzbfJmfKHa	13.0			0
91	0Jl6TFKAJR7zIv2kvA1RNf	60054.0			40
92	0K0Sa7amVwCmQKz7ZHRRim	3005.0			29
93	0KNOQSBwQim4GXpZHekrvu	1728.0			47
94	0KTn3DOb57GcGjPoA09ABL	4.0			0
95	0KZLEvrZHdqVDKdclXRVK0	7.0			0
96	0KirHnU7pIfeMYWSJ6xm8I	1309.0			12
97	0Ks3WKQ64ZmWa3QkbbeCbj	129.0			0
98	0LcUNEKY8mVqNmYfrgZrxl	5803.0			24
99	0M5UiR76X2ybfo6N9iVNWr	27540.0			36
100	0M82iosyReYC1ij8svj145	49.0			0

Rows: 1-100 | Columns: 5

We can visualize the top 60 most-followed Polish artists with a bar chart.

In [8]:

# make a highchart of the top 60 most-followed Polish artists
polish_artists.hchart(x = "name",
                      y = "followers", 
                      aggregate = False,
                      kind = "bar",
                      max_cardinality = 60,
                      height = 900,
                      width = 1500)

Out[8]:

We can do the same with the most popular tracks. For example, we can graph Monika Brodka's most popular tracks like so:

In [9]:

# find Monika Brodka's songs
brodka_tracks = tracks.search("artists ilike '%brodka%'")

# plot Brodka's tracks ordered by popularity
brodka_tracks.hchart(x = "name",
                     y = "popularity",
                     aggregate = False,
                     kind = "bar",
                     max_cardinality = 25)

Out[9]:

To get an idea of what makes Monika Brodka's songs popular, let's create a boxplot of the numerical feature distribution of her tracks.

In [10]:

## list of the relevant numerical features
numerical_features = ['danceability', 
                      'energy', 
                      'speechiness', 
                      'acousticness', 
                      'instrumentalness', 
                      'valence', 
                      'liveness'] 

# create a boxplot of the above features
brodka_tracks[numerical_features].hchart(kind = "boxplot")

Out[10]:

Timing is a classic factor for success, so let's look at the popularity of Monika's songs over time with a smooth curve.

In [11]:

# extract year from the date
brodka_tracks['release_year'] = "year(release_date::date)"

# smooth the popularity using rolling mean
brodka_tracks.rolling(func = 'mean',
                      columns = 'popularity',
                      window = (-3, 3),
                      order_by = 'release_year',
                      name = 'smoothed_popularity')

# plot the smoothed curve for popularity of her songs
brodka_tracks.plot(ts = 'release_date', columns=['smoothed_popularity'])

Out[11]:

<AxesSubplot:xlabel='"release_date"', ylabel='"smoothed_popularity"'>

Numerical-feature Analysis¶

Bringing it all together, let's try to get an idea of how these numerical features change and correlate with each other in Monika's most popular songs.

In [12]:

# extract year from date
tracks['release_year'] = "year(release_date::date)"

# get the average of numerical features during the year
yearly_aggs = tracks.groupby('release_year', ['AVG(danceability) as danceability',
                                              'AVG(energy) as energy', 
                                              'AVG(speechiness) AS speechiness', 
                                              'AVG(acousticness) AS acousticness', 
                                              'AVG(instrumentalness) AS instrumentalness',
                                              'AVG(valence) AS valence', 
                                              'AVG(liveness) AS liveness',])

# plot the cures for numerical features along the different years
yearly_aggs.plot(ts='release_year', 
                 columns=numerical_features)

Out[12]:

<AxesSubplot:xlabel='"release_year"'>

In [13]:

# correlation of numerical features
tracks[tracks[numerical_features]].corr()

Out[13]:

	"danceability"	"energy"	"speechiness"	"acousticness"	"instrumentalness"	"valence"	"liveness"
"danceability"	1.0	0.225378443312902	0.198422110210451	-0.228879028726738	-0.219560144635743	0.517690962189664	-0.107989997860069
"energy"	0.225378443312902	1.0	-0.0588034003217321	-0.710939277579459	-0.190319675033783	0.360195098649349	0.12611905796149
"speechiness"	0.198422110210451	-0.0588034003217321	1.0	0.0735189869046661	-0.101150709419063	0.0439632547745622	0.208192661333212
"acousticness"	-0.228879028726738	-0.710939277579459	0.0735189869046661	1.0	0.199817518163958	-0.166786499501141	-0.00448565029093398
"instrumentalness"	-0.219560144635743	-0.190319675033783	-0.101150709419063	0.199817518163958	1.0	-0.170613868369725	-0.0372257235123641
"valence"	0.517690962189664	0.360195098649349	0.0439632547745622	-0.166786499501141	-0.170613868369725	1.0	-0.000143636296234245
"liveness"	-0.107989997860069	0.12611905796149	0.208192661333212	-0.00448565029093398	-0.0372257235123641	-0.000143636296234245	1.0

Rows: 1-7 | Columns: 8

Feature Engineering¶

To expand our analysis, let's take into account some descriptive features. Since our goal is to predict popularity, some useful features might be:

number of followers
popularity for the artist of the track
the number of artists per track

Additionally, we manipulate our data a bit to make things easier later on:

converting the duration unit from 'ms' to 'minute'
extracting the year from the date.

In [66]:

%%sql
CREATE TABLE spotify.polish_tracks AS
SELECT * FROM spotify.tracks 
WHERE id_artists IN (SELECT t.id_artists FROM spotify.tracks t JOIN spotify.polish_artists p
                     ON t.id_artists LIKE '%' || p.id || '%');
CREATE TABLE spotify.polish_tracks_clean AS
SELECT 
    x.*, 
    x.duration_ms / 60000 AS duration_minute,
    (CASE 
         WHEN LENGTH(x.release_date) = 4 THEN (release_date::INT) 
         ELSE YEAR(x.release_date::date) 
     END) AS release_year,
    y.followers AS artists_followers,
    y.popularity AS artist_popularity
FROM spotify.polish_tracks AS x LEFT JOIN spotify.artists AS y
ON x.id_artists LIKE '%' || y.id || '%';

CREATE
CREATE

Out[66]:

Execution: 634.41s

In [27]:

polish_tracks = vp.vDataFrame("spotify.polish_tracks_clean")

# count the number of artists per track
polish_tracks.regexp(column = "artists", 
                     pattern = ",", 
                     method = "count",
                     name = "nb_singers")
polish_tracks["nb_singers"].add(1)

Out[27]:

	Abc id Varchar(44)	Abc Varchar(96)	123 popularity Int	123 duration_ms Int	123 explicit Int	Abc Varchar(100)	Abc Varchar(156)	Abc release_date Varchar(20)	123 danceability Numeric(6,4)	123 energy Numeric(8,6)	123 key Int	123 loudness Numeric(8,4)	123 mode Int	123 speechiness Numeric(7,5)	123 acousticness Numeric(6,4)	123 instrumentalness Float	123 liveness Numeric(7,5)	123 valence Numeric(7,5)	123 tempo Numeric(9,4)	123 time_signature Int	123 duration_minute Numeric(36,18)	123 release_year Int	123 artists_followers Numeric(7,2)	123 artist_popularity Int	123 nb_singers Integer
1	0004Uy71ku11n3LMpuyf59		34	258907	0			2012-01-01	0.623	0.599	6	-9.255	1	0.0255	0.177	0.00148	0.0748	0.381	140.056	4	4.3151166666666665	2012	5425.0	36	1
2	008UO76dicJJrUb5g6WID0		42	216813	0			2001	0.677	0.885	9	-4.108	0	0.0449	0.164	0.0	0.0827	0.741	119.981	4	3.61355	2001	47110.0	44	1
3	00D7fT49Q54HNiaaLY32UA		41	196231	0			2017-06-07	0.541	0.672	1	-4.956	1	0.113	0.198	0.0	0.112	0.146	120.061	5	3.2705166666666665	2017	75395.0	59	3
4	00De7REGurYkmfrKAh6CU1		36	400867	0			1992-10-30	0.218	0.104	11	-22.809	0	0.047	0.51	0.692	0.0688	0.0329	114.592	3	6.681116666666667	1992	11683.0	40	1
5	00KCwnrvIXX8GRU3ZMOIBW		55	207821	0			2019-12-06	0.825	0.692	6	-6.084	0	0.0627	0.172	0.000996	0.0861	0.602	123.985	4	3.4636833333333334	2019	23399.0	54	1
6	00g7zzRDf2DOf4fKYzFTUA		46	192857	0			2018-08-24	0.75	0.797	1	-6.516	1	0.332	0.567	0.0	0.0914	0.726	112.343	4	3.2142833333333334	2018	28135.0	43	1
7	00i4Q7OfN0mGnUyuyQcAdZ		2	97924	0			1978-01-01	0.573	0.674	7	-7.531	0	0.0947	0.629	0.0	0.217	0.928	98.842	4	1.6320666666666668	1978	5.0	4	2
8	00i4Q7OfN0mGnUyuyQcAdZ		2	97924	0			1978-01-01	0.573	0.674	7	-7.531	0	0.0947	0.629	0.0	0.217	0.928	98.842	4	1.6320666666666668	1978	5059.0	42	2
9	00l8ILQ8qgsew789dGn1Gh		7	326240	0			1963-03-25	0.568	0.601	2	-7.875	0	0.0294	0.509	9.49e-06	0.156	0.78	102.428	4	5.437333333333333	1963	696.0	9	1
10	00vlVv7WLZjyRS783REKmn		24	243107	0			1998	0.883	0.506	1	-8.137	1	0.372	0.0519	0.0	0.207	0.375	93.05	4	4.051783333333334	1998	41198.0	37	1
11	00wJpDUdKoWQ2ibqTpJh1Y		32	211653	0			1998-08-30	0.802	0.923	6	-7.743	0	0.033	0.314	0.000418	0.225	0.978	130.103	4	3.52755	1998	7987.0	27	1
12	010cqDI9IKD8f3Ji8TnT9h		14	191213	0			1967-04-14	0.512	0.416	10	-8.306	1	0.0381	0.784	0.0	0.0836	0.531	135.15	4	3.1868833333333333	1967	13705.0	35	1
13	0123TtFcZ7pZIss52i7nAK		23	174160	0			1995-02-27	0.634	0.627	0	-13.211	1	0.035	0.0148	0.000643	0.0519	0.745	126.63	4	2.9026666666666667	1995	1132.0	28	1
14	015JvDPIBquLQTX6VZPwB2		1	142211	0			1960-09-07	0.283	0.294	0	-12.078	1	0.0345	0.686	6.71e-06	0.626	0.419	112.214	3	2.3701833333333333	1960	12184.0	34	1
15	01FN979nyz1gmdsfWZv5OL		45	182293	0			1995-08-31	0.557	0.686	2	-6.874	1	0.026	0.0621	5.88e-05	0.214	0.38	112.029	4	3.0382166666666666	1995	16698.0	38	1
16	01LN8YM6L9V2a135AnhZqf		57	168333	0			2020-12-05	0.618	0.459	7	-10.995	0	0.0582	0.117	3.49e-05	0.148	0.268	144.046	4	2.80555	2020	8841.0	46	2
17	01LN8YM6L9V2a135AnhZqf		57	168333	0			2020-12-05	0.618	0.459	7	-10.995	0	0.0582	0.117	3.49e-05	0.148	0.268	144.046	4	2.80555	2020	950.0	63	2
18	01j7wulLc7rJVk0STUPEKu		5	200160	0			1967-03-06	0.412	0.244	4	-11.608	0	0.0316	0.957	0.000506	0.158	0.306	132.1	4	3.336	1967	1645.0	15	1
19	01lYEV7KZT34GOAW2MIgHF		35	458307	0			1969-06-11	0.322	0.549	3	-8.303	1	0.0582	0.533	0.00204	0.216	0.209	140.603	3	7.63845	1969	87156.0	42	1
20	01uK4bh8p175iZj7uSkp5k		45	204893	0			2000	0.612	0.533	2	-8.964	0	0.0287	0.42	2.5e-06	0.108	0.359	96.07	4	3.4148833333333335	2000	77205.0	53	1
21	025zlWO2FRZZrFhRw5kqa9		40	191893	0			2013-11-15	0.666	0.977	5	-3.723	0	0.145	0.0012	3.79e-06	0.106	0.21	110.043	4	3.1982166666666667	2013	29602.0	42	1
22	028DQ7NS44vnLRaoon0mwg		18	232640	0			1987	0.37	0.968	2	-3.217	1	0.16	0.0012	0.000327	0.0697	0.448	119.504	4	3.8773333333333335	1987	23629.0	38	1
23	02GvdetV646M4C1Ip8CKg5		9	209507	0			1978-07-22	0.534	0.237	5	-14.073	0	0.069	0.53	0.000783	0.206	0.319	123.09	4	3.4917833333333332	1978	11879.0	24	1
24	02IpFuItRz1yXbYAtHsXJs		40	218436	0			2000-11-20	0.538	0.665	5	-6.631	1	0.0536	0.226	2.91e-05	0.0891	0.338	112.771	4	3.6406	2000	13324.0	39	1
25	02JLJi7sryFu44hhNWOv1c		30	254000	1			2010-09-24	0.839	0.702	0	-7.401	1	0.4	0.222	0.0	0.0626	0.464	90.017	4	4.233333333333333	2010	10611.0	44	3
26	02RqhFaAYzWScfVzKfTZ7L		53	202784	0			2020-01-07	0.612	0.71	1	-4.689	0	0.186	0.0371	0.0	0.0341	0.749	103.811	4	3.3797333333333333	2020	50057.0	55	2
27	02hewGMWcG5Vp8nwhcmTA0		19	219267	0			1994	0.553	0.795	4	-11.382	1	0.0999	0.0083	0.0	0.208	0.168	93.824	4	3.65445	1994	48108.0	42	1
28	02yg6Eksd8zZpYxsT1Lpsp		29	104213	0			1999-01-01	0.499	0.819	7	-7.063	1	0.0454	0.0065	1.37e-06	0.0584	0.915	184.254	4	1.7368833333333333	1999	93331.0	47	1
29	03UV1fQG0MIm4CPszfttTJ		35	183834	0			2006-06-26	0.661	0.653	4	-4.895	0	0.0509	0.0292	0.0	0.0802	0.505	125.991	4	3.0639	2006	55390.0	44	1
30	04GlMx0s7MvdMr2MEXX8s8		34	255840	0			2008-04-18	0.533	0.871	1	-11.237	1	0.0369	0.0002	0.16	0.0609	0.87	155.975	4	4.264	2008	57656.0	42	1
31	04PxH7CFGAaAvo6j0zZAOr		42	229407	0			2014-11-07	0.704	0.77	1	-4.986	0	0.0666	0.0288	0.0	0.299	0.534	100.031	4	3.82345	2014	15874.0	45	3
32	04PxH7CFGAaAvo6j0zZAOr		42	229407	0			2014-11-07	0.704	0.77	1	-4.986	0	0.0666	0.0288	0.0	0.299	0.534	100.031	4	3.82345	2014	65568.0	50	3
33	04QM4iqGabg7QlphUoWiMB		26	267408	0			1995-05-08	0.592	0.869	1	-5.916	0	0.0659	0.12	6.89e-05	0.315	0.607	91.57	4	4.4568	1995	52393.0	47	1
34	04izaHyfYxsE3AQ68kgB4I		24	145827	0			2000-04-20	0.721	0.769	9	-5.933	0	0.175	0.178	0.0	0.0864	0.625	131.909	4	2.43045	2000	810.0	24	1
35	04kSSveBKXiNeRaOXu2iog		43	229933	0			1999-06-05	0.672	0.437	0	-10.317	0	0.0363	0.298	0.0	0.18	0.22	105.421	4	3.8322166666666666	1999	50057.0	55	2
36	04wTGEh2gJTG72yUoizwFV		10	205693	0			1980	0.475	0.852	10	-6.646	1	0.0549	0.0001	0.00324	0.312	0.503	112.037	4	3.4282166666666667	1980	57656.0	42	1
37	053zfKbxSoqRm2RCdJdQMS		28	201400	0			1997-01-01	0.42	0.957	2	-5.475	1	0.0475	0.001	0.0202	0.11	0.596	166.193	4	3.3566666666666665	1997	13433.0	31	1
38	053zgCcuWJ9x4dJJgaF4VS		10	509704	0			1982	0.184	0.201	0	-16.851	1	0.0379	0.785	0.401	0.208	0.0421	135.568	4	8.495066666666666	1982	9218.0	29	1
39	054VUAvYt59A1yGSrBAgac		22	203213	0			1976-02-12	0.537	0.487	5	-9.373	1	0.0513	0.73	0.0271	0.124	0.814	147.668	4	3.3868833333333335	1976	38031.0	42	1
40	05IAxc7j9T9KcEu0lAgbfB		46	216013	0			2016-01-03	0.78	0.927	11	-2.836	1	0.0958	0.125	0.0	0.2	0.971	133.933	4	3.600216666666667	2016	10701.0	42	1
41	05JJTD8KdxbZsMnOn6imMv		42	230853	0			2000	0.664	0.909	7	-4.196	0	0.139	0.0792	0.000299	0.115	0.835	168.032	4	3.84755	2000	19650.0	42	1
42	05RcrI18Qi65dwc3vegSNP		43	216000	0			2017-10-27	0.638	0.881	0	-4.948	1	0.123	0.0872	0.0	0.306	0.642	79.994	4	3.6	2017	45147.0	47	2
43	05hJSmfNiPXSzwSLeG1Rfb		39	232507	0			2016-10-21	0.44	0.426	6	-6.502	0	0.0339	0.822	0.00399	0.114	0.487	99.842	4	3.8751166666666665	2016	6179.0	36	1
44	05hk8ivnCt0HP3aI1uWJfi		43	295067	0			2015-03-06	0.598	0.762	2	-8.386	1	0.0396	0.537	0.0	0.324	0.686	106.972	4	4.917783333333333	2015	32447.0	40	1
45	05v1D17vjtCNRyalBVGbKU		34	481160	0			2007-06-10	0.432	0.784	11	-10.273	0	0.0604	0.829	0.0498	0.963	0.354	108.142	4	8.019333333333334	2007	44870.0	43	1
46	05yf5sbF36dhwGRfj18sg5		27	225647	0			2007-11-19	0.912	0.69	7	-6.206	1	0.196	0.109	0.0	0.256	0.73	92.971	4	3.7607833333333334	2007	20468.0	35	1
47	062pHiX7lpKRDmFvmJor3o		38	182813	0			2006	0.518	0.992	2	-4.926	1	0.104	0.0012	1.17e-06	0.33	0.412	146.553	4	3.046883333333333	2006	42871.0	43	1
48	063FiQaY2FuZe3a44rZuSX		37	221187	0			2012-08-08	0.772	0.705	0	-5.093	1	0.0442	0.194	1.66e-06	0.175	0.729	95.008	4	3.68645	2012	11211.0	32	1
49	063ZobFSzYA0O7fmfi4oik		42	212689	0			2002-03-12	0.767	0.951	10	-4.36	1	0.0499	0.152	0.0	0.361	0.844	99.986	4	3.5448166666666667	2002	51546.0	49	1
50	06AIE9kwssJ3cmPbMuHobb		30	323133	0			2007-05-11	0.137	0.916	7	-5.258	1	0.0581	0.0023	7.75e-05	0.126	0.399	166.21	4	5.38555	2007	34390.0	40	1
51	06K6svVGbiQILn9BAzZNgo		37	374120	1			2013-12-13	0.614	0.762	0	-9.021	0	0.0697	0.0149	1.42e-06	0.122	0.352	133.992	4	6.235333333333333	2013	3133.0	43	2
52	06LDaZRAWOc0fNqkdOQo9S		18	77853	0			1998	0.762	0.455	10	-11.077	1	0.0361	0.3	0.796	0.0696	0.482	147.901	4	1.29755	1998	70621.0	46	1
53	06SnvuakcuOcj3odFZpIpJ		18	137639	0			1969-09-06	0.646	0.54	2	-9.74	0	0.0311	0.0723	0.0	0.131	0.801	94.858	4	2.2939833333333333	1969	7150.0	29	1
54	06V1fPvTfXdIBMUItnLmQ7		14	119493	0			1967-10-16	0.437	0.333	5	-15.826	0	0.036	0.762	0.000529	0.105	0.344	79.078	3	1.99155	1967	10848.0	35	1
55	06kPUkFJv1RfEVjgEF3oRz		23	250227	0			1997-01-01	0.751	0.735	0	-10.087	1	0.0354	0.0088	0.0219	0.102	0.857	129.879	4	4.17045	1997	31913.0	47	1
56	07Iyx8PziAv997SrJanQgl		11	279013	0			1981	0.361	0.14	10	-20.157	0	0.032	0.354	8.66e-06	0.222	0.0972	97.964	4	4.650216666666667	1981	20934.0	42	1
57	07QfWWYlJsbXiEbbKSvqcd		34	320993	1			2010-04-26	0.517	0.956	10	-5.7	0	0.291	0.0139	7.05e-06	0.367	0.667	84.011	4	5.3498833333333335	2010	10011.0	51	3
58	07QfWWYlJsbXiEbbKSvqcd		34	320993	1			2010-04-26	0.517	0.956	10	-5.7	0	0.291	0.0139	7.05e-06	0.367	0.667	84.011	4	5.3498833333333335	2010	7380.0	39	3
59	07rj0QlIw4hRcvhIJGY2Lf		6	123173	0			1965-10-12	0.554	0.189	0	-15.127	0	0.0485	0.968	8.64e-06	0.131	0.301	67.152	4	2.0528833333333334	1965	3121.0	31	1
60	0804d5chEAcksTIZ7IVh6O		29	200000	0			1967-10-16	0.33	0.439	9	-13.421	0	0.0465	0.875	4.92e-05	0.508	0.356	101.975	4	3.3333333333333335	1967	10848.0	35	1
61	08D5BLpRFI6PT2XYuq7eDg		28	273267	0			1996-01-01	0.696	0.939	5	-6.538	0	0.0303	0.507	5.37e-06	0.251	0.974	137.921	4	4.55445	1996	12829.0	36	1
62	08D6EqghkRLD6iFHKKWJ4Y		55	213120	1			2020-01-27	0.758	0.687	0	-8.813	0	0.0569	0.3	0.0263	0.12	0.475	125.018	4	3.552	2020	40274.0	62	2
63	08ER472xnuMlbtkKSh3PB4		11	166867	0			1975-09-07	0.337	0.29	10	-14.607	1	0.0887	0.93	0.0	0.109	0.416	178.159	4	2.7811166666666667	1975	12184.0	34	1
64	08ULDx4KeBpXe7OHEWjgVP		45	277293	0			2014-04-16	0.687	0.93	3	-4.418	1	0.0393	0.0147	0.00116	0.33	0.96	153.068	4	4.62155	2014	3064.0	37	1
65	08uPk6Ge77W31WGGYNEPHA		2	156369	0			1978-01-01	0.469	0.547	5	-8.842	1	0.315	0.781	1.28e-06	0.103	0.736	92.544	4	2.60615	1978	5059.0	42	2
66	08uPk6Ge77W31WGGYNEPHA		2	156369	0			1978-01-01	0.469	0.547	5	-8.842	1	0.315	0.781	1.28e-06	0.103	0.736	92.544	4	2.60615	1978	1.0	1	2
67	08xY8SSfwHc7SIXWroGW9j		16	167360	0			1994	0.324	0.246	2	-14.308	0	0.0391	0.657	0.0	0.107	0.59	124.354	4	2.7893333333333334	1994	36250.0	54	1
68	095PNli2gmoYwlvgotxxEs		28	269067	0			1995	0.576	0.616	7	-5.144	0	0.028	0.0549	6.02e-06	0.096	0.146	119.804	4	4.48445	1995	77205.0	53	1
69	09FEC2H0Ls5bXMSFj2bLzL		50	198460	0			2018-10-24	0.708	0.807	0	-8.003	1	0.219	0.298	0.000125	0.116	0.917	169.848	4	3.3076666666666665	2018	74908.0	65	3
70	09GKW7x0iMuxzzM7IeQ01g		7	153521	0			1964-02-21	0.521	0.209	0	-12.891	1	0.0339	0.736	0.0	0.187	0.548	82.018	4	2.558683333333333	1964	3058.0	31	1
71	09PwmLzRfWj5hkmIiw1a9X		21	251316	0			1969-09-07	0.323	0.385	0	-9.228	1	0.0313	0.714	7.9e-06	0.378	0.379	119.002	4	4.1886	1969	12184.0	34	1
72	09d4etyrEWf5BguIIUfRW9		12	212523	0			1974-08-16	0.308	0.429	9	-8.703	0	0.032	0.434	0.0	0.138	0.314	83.695	4	3.54205	1974	12962.0	36	1
73	0ADOK8ffxUlSDu51nIu5KU		23	274533	0			1999	0.662	0.299	1	-11.214	0	0.0275	0.41	0.0	0.363	0.553	103.037	4	4.57555	1999	61203.0	45	1
74	0AGuYcLlnbR7AOQSbTGzIz		29	232960	0			2000-12-31	0.447	0.573	2	-8.937	0	0.0303	0.0799	0.000111	0.291	0.562	201.077	4	3.8826666666666667	2000	15007.0	40	1
75	0AHy3nZXS725AAHDC0qjPR		22	202336	0			1966-09-28	0.482	0.363	9	-10.885	1	0.0377	0.878	0.0	0.308	0.369	117.999	4	3.3722666666666665	1966	15347.0	35	1
76	0AJJJnR5VwJfeYGvnyG74v		7	191530	0			1967-03-29	0.632	0.303	7	-14.1	0	0.0272	0.774	0.00217	0.102	0.404	104.859	4	3.1921666666666666	1967	69339.0	46	1
77	0ATVUbPlX65HB5H1WbIugt		54	181356	1			2020-12-04	0.891	0.899	7	-9.836	1	0.288	0.185	0.0	0.0778	0.888	154.912	4	3.0226	2020	27699.0	54	1
78	0AUeyoLQYwYUDsQRJZKvZd		22	248907	0			1980	0.424	0.824	4	-5.895	1	0.0434	0.0005	4.31e-05	0.133	0.423	195.99	4	4.14845	1980	57656.0	42	1
79	0AXtdoJaVk6W8pcYstecgw		33	193227	0			2000	0.595	0.806	2	-7.019	1	0.0442	0.199	8.02e-05	0.0204	0.718	120.172	4	3.22045	2000	44870.0	43	1
80	0AYmWvKRGtgePrpo3NdYXh		48	218530	0			2019-02-01	0.641	0.382	4	-11.055	1	0.0299	0.936	0.437	0.107	0.237	112.996	4	3.6421666666666668	2019	90157.0	61	1
81	0ApseFDjbipbKK0dBD4Viz		35	267013	1			2005-11-15	0.837	0.697	9	-5.899	0	0.294	0.0776	0.0	0.153	0.545	90.011	4	4.450216666666667	2005	80933.0	46	1
82	0Aq0weZZ0YzbrNlsA8UV3P		23	294107	0			1997-01-05	0.57	0.596	7	-8.591	1	0.0271	0.0157	0.000331	0.144	0.211	140.843	4	4.901783333333333	1997	509.0	12	1
83	0Au3WxSl0fm6IvSZHCShjZ		22	157187	0			1998-01-01	0.504	0.923	4	-6.533	0	0.0415	0.0141	0.0175	0.151	0.643	139.08	4	2.6197833333333334	1998	29108.0	40	1
84	0AwC0oZxr25SOcThearmN4		28	300383	0			2005-04-29	0.927	0.705	7	-6.27	1	0.0577	0.0135	0.0	0.0755	0.749	99.024	4	5.006383333333333	2005	20468.0	35	1
85	0B1uuoyiPSkV2f8cAw473e		0	230773	0			1946-01-01	0.267	0.193	6	-17.214	1	0.0781	0.991	0.88	0.19	0.346	79.244	1	3.846216666666667	1946	1805.0	29	5
86	0B1uuoyiPSkV2f8cAw473e		0	230773	0			1946-01-01	0.267	0.193	6	-17.214	1	0.0781	0.991	0.88	0.19	0.346	79.244	1	3.846216666666667	1946	12.0	8	5
87	0B1uuoyiPSkV2f8cAw473e		0	230773	0			1946-01-01	0.267	0.193	6	-17.214	1	0.0781	0.991	0.88	0.19	0.346	79.244	1	3.846216666666667	1946	1.0	5	5
88	0B1uuoyiPSkV2f8cAw473e		0	230773	0			1946-01-01	0.267	0.193	6	-17.214	1	0.0781	0.991	0.88	0.19	0.346	79.244	1	3.846216666666667	1946	2671.0	47	5
89	0B1uuoyiPSkV2f8cAw473e		0	230773	0			1946-01-01	0.267	0.193	6	-17.214	1	0.0781	0.991	0.88	0.19	0.346	79.244	1	3.846216666666667	1946	44235.0	43	5
90	0BHIwbxTN0iSClgDaO36Ft		34	293882	0			2009-02-21	0.68	0.868	9	-2.454	0	0.413	0.348	0.0	0.202	0.819	173.874	4	4.898033333333333	2009	52237.0	50	3
91	0BHIwbxTN0iSClgDaO36Ft		34	293882	0			2009-02-21	0.68	0.868	9	-2.454	0	0.413	0.348	0.0	0.202	0.819	173.874	4	4.898033333333333	2009	22905.0	37	3
92	0BleuvVMt39PmQaukAdEcv		37	228053	0			2010	0.415	0.504	5	-8.658	1	0.0292	0.622	1.76e-05	0.12	0.383	156.074	4	3.800883333333333	2010	38499.0	42	1
93	0Bn854XtnJv15m9f1ZvWbe		44	194013	0			2011-05-31	0.49	0.88	6	-4.642	0	0.0447	0.299	0.0	0.099	0.619	160.015	4	3.23355	2011	36693.0	49	1
94	0BsQm2iFzKTzAemliJtu8x		39	201231	0			2016-11-25	0.56	0.791	4	-6.323	1	0.0303	0.0054	0.0	0.0858	0.605	129.992	4	3.35385	2016	36719.0	45	1
95	0C2VNcmHdRRvQtetkaZVn5		32	311440	0			1990	0.599	0.395	1	-14.307	0	0.0258	0.668	3.77e-05	0.116	0.353	102.03	4	5.190666666666667	1990	77205.0	53	1
96	0C2XO15BhHDglDB724brOi		43	316627	0			1998	0.84	0.541	10	-12.035	0	0.21	0.0951	1.2e-05	0.106	0.681	80.988	4	5.277116666666666	1998	70621.0	46	1
97	0CAinfqSGq1peA63s4gNam		21	187933	0			1988-12-31	0.633	0.298	10	-10.693	1	0.0325	0.889	0.0	0.0864	0.591	116.335	3	3.132216666666667	1988	29541.0	43	1
98	0CBj18X3J7MabwDqKeMxuo		47	239373	0			1999	0.384	0.768	0	-6.101	1	0.121	0.124	0.0	0.0682	0.74	119.973	4	3.98955	1999	35719.0	47	1
99	0CLO6b6Ohrnmp9fDLvYj3U		25	162987	0			2003	0.275	0.34	8	-13.435	0	0.0339	0.982	0.0	0.221	0.272	91.165	4	2.71645	2003	3095.0	39	2
100	0CMxaedU31OgxQvrA1VE1J		54	225867	0			2019-10-25	0.862	0.615	3	-5.019	0	0.228	0.239	0.0	0.0797	0.413	114.959	4	3.76445	2019	35006.0	49	2

Rows: 1-100 | Columns: 25

Define a list of predictors and the response, and then save the normalized version of the final dataset to the database.

In [28]:

# define predictors and response
predictors = ['duration_minute', 
              'release_year', 
              'danceability', 
              'energy', 
              'loudness',
              'speechiness', 
              'acousticness', 
              'instrumentalness', 
              'liveness', 
              'valence', 
              'artists_followers', 
              'artist_popularity', 
              'nb_singers']
response = 'popularity'

# normalize the features
polish_tracks.normalize(method = "minmax",
                        columns = predictors)

# save the final dataset to the database
polish_tracks.to_db('"spotify"."polish_tracks_data_final"', relation_type = "table")

Out[28]:

	Abc id Varchar(44)	Abc Varchar(96)	123 popularity Int	123 duration_ms Int	123 explicit Int	Abc Varchar(100)	Abc Varchar(156)	Abc release_date Varchar(20)	123 danceability Float	123 energy Float	123 key Int	123 loudness Float	123 mode Int	123 speechiness Float	123 acousticness Float	123 instrumentalness Float	123 liveness Float	123 valence Float	123 tempo Numeric(9,4)	123 time_signature Int	123 duration_minute Float	123 release_year Float	123 artists_followers Float	123 artist_popularity Float	123 nb_singers Float
1	0004Uy71ku11n3LMpuyf59		34	258907	0			2012-01-01	0.617324561403509	0.599361388077236	6	0.797675055385458	1	0.002405160161802	0.177889447236181	0.00151484135107472	0.061291989664083	0.365659943567771	140.056	4	0.16825584838614155	0.892857142857143	0.054260076228204	0.5	0.0
2	008UO76dicJJrUb5g6WID0		42	216813	0			2001	0.676535087719298	0.886535931961723	9	0.925795932591542	0	0.023614299770417	0.164824120603015	0.0	0.069457364341085	0.74187480405476	119.981	4	0.13709357417826473	0.761904761904762	0.471264367816092	0.611111111111111	0.0
3	00D7fT49Q54HNiaaLY32UA		41	196231	0			2017-06-07	0.527412280701754	0.672661184243556	1	0.904687227740024	1	0.098064939324369	0.198994974874372	0.0	0.099741602067183	0.120075242972097	120.061	5	0.12185667752442997	0.952380952380952	0.754219061052589	0.819444444444444	0.222222222222222
4	00De7REGurYkmfrKAh6CU1		36	400867	0			1992-10-30	0.173245614035088	0.102328523661777	11	0.460284270529958	0	0.025910134470318	0.512562814070352	0.708290685772774	0.055090439276486	0.001881074302435	114.592	3	0.273349126443589	0.654761904761905	0.116863239398577	0.555555555555556	0.0
5	00KCwnrvIXX8GRU3ZMOIBW		55	207821	0			2019-12-06	0.838815789473684	0.692743320179534	6	0.876608667513006	0	0.04307423198863	0.17286432160804	0.00101944728761515	0.07297157622739	0.596614066255617	123.985	4	0.13043677820550784	0.976190476190476	0.234066604643718	0.75	0.0
6	00g7zzRDf2DOf4fKYzFTUA		46	192857	0			2018-08-24	0.756578947368421	0.79817453384342	1	0.865855176362233	1	0.337487700885536	0.569849246231156	0.0	0.078449612403101	0.726199184867802	112.343	4	0.11935889843055966	0.964285714285714	0.281444134329702	0.597222222222222	0.0
7	00i4Q7OfN0mGnUyuyQcAdZ		2	97924	0			1978-01-01	0.5625	0.674669397837154	7	0.840589450626042	0	0.078058379796655	0.632160804020101	0.0	0.208268733850129	0.937297523252168	98.842	4	0.049079804560260586	0.488095238095238	4.0014805478e-05	0.055555555555556	0.111111111111111
8	00i4Q7OfN0mGnUyuyQcAdZ		2	97924	0			1978-01-01	0.5625	0.674669397837154	7	0.840589450626042	0	0.078058379796655	0.632160804020101	0.0	0.208268733850129	0.937297523252168	98.842	4	0.049079804560260586	0.488095238095238	0.050598721526965	0.583333333333333	0.111111111111111
9	00l8ILQ8qgsew789dGn1Gh		7	326240	0			1963-03-25	0.557017543859649	0.601369601670834	2	0.832026485450427	0	0.006668853175905	0.511557788944724	9.71340839303992e-06	0.145219638242894	0.782631413940851	102.428	4	0.2181026058631922	0.30952380952381	0.006952572451807	0.125	0.0
10	00vlVv7WLZjyRS783REKmn		24	243107	0			1998	0.902412280701754	0.505979455974937	1	0.825504692206208	1	0.381217885645567	0.052160804020101	0.0	0.197932816537468	0.359389695892988	93.05	4	0.15655907610305003	0.726190476190476	0.412122485319568	0.513888888888889	0.0
11	00wJpDUdKoWQ2ibqTpJh1Y		32	211653	0			1998-08-30	0.81359649122807	0.924691990240082	6	0.835312274413163	0	0.010604569804307	0.315577889447236	0.000427840327533265	0.216537467700258	0.989549587208695	130.103	4	0.13327361563517914	0.726190476190476	0.079889559136881	0.375	0.0
12	010cqDI9IKD8f3Ji8TnT9h		14	191213	0			1967-04-14	0.495614035087719	0.415609844263036	10	0.821297886640281	1	0.016180168361211	0.787939698492462	0.0	0.070387596899225	0.52241613543735	135.15	4	0.11814184187148356	0.357142857142857	0.13709072356772	0.486111111111111	0.0
13	0123TtFcZ7pZIss52i7nAK		23	174160	0			1995-02-27	0.629385964912281	0.627476378387605	0	0.69920095586588	1	0.012791079042309	0.014874371859296	0.00065813715455476	0.037622739018088	0.746054969171282	126.63	4	0.10551747112822031	0.69047619047619	0.011314186248912	0.388888888888889	0.0
14	015JvDPIBquLQTX6VZPwB2		1	142211	0			1960-09-07	0.244517543859649	0.293108815053569	0	0.727403977796032	1	0.012244451732809	0.689447236180905	6.86796315250768e-06	0.631007751937984	0.405371512174731	112.214	3	0.08186556114894877	0.273809523809524	0.1218750937847	0.472222222222222	0.0
15	01FN979nyz1gmdsfWZv5OL		45	182293	0			1995-08-31	0.544956140350877	0.686718679398741	2	0.856943718417843	1	0.002951787471302	0.062412060301508	6.01842374616172e-05	0.205167958656331	0.36461490228864	112.029	4	0.1115383476458395	0.69047619047619	0.167031801766654	0.527777777777778	0.0
16	01LN8YM6L9V2a135AnhZqf		57	168333	0			2020-12-05	0.611842105263158	0.458786436525389	7	0.754362382694845	0	0.038154586203127	0.117587939698492	3.57215967246674e-05	0.136950904392765	0.247570279026022	144.046	4	0.1012037311222979	0.988095238095238	0.088432720106439	0.638888888888889	0.111111111111111
17	01LN8YM6L9V2a135AnhZqf		57	168333	0			2020-12-05	0.611842105263158	0.458786436525389	7	0.754362382694845	0	0.038154586203127	0.117587939698492	3.57215967246674e-05	0.136950904392765	0.247570279026022	144.046	4	0.1012037311222979	0.988095238095238	0.009493512599662	0.875	0.111111111111111
18	01j7wulLc7rJVk0STUPEKu		5	200160	0			1967-03-06	0.385964912280702	0.242903475213624	4	0.739103377890623	0	0.009074013337706	0.961809045226131	0.000517911975435005	0.147286821705426	0.287281847632982	132.1	4	0.12476532425229493	0.357142857142857	0.016446085051469	0.208333333333333	0.0
19	01lYEV7KZT34GOAW2MIgHF		35	458307	0			1969-06-11	0.287280701754386	0.549156048237291	3	0.821372563662161	1	0.038154586203127	0.535678391959799	0.00208802456499488	0.207235142118863	0.185912843557321	140.603	3	0.3158720758069292	0.380952380952381	0.871872592859358	0.583333333333333	0.0
20	01uK4bh8p175iZj7uSkp5k		45	204893	0			2000	0.605263157894737	0.533090339488508	2	0.804918726507854	0	0.005903574942604	0.422110552763819	2.55885363357216e-06	0.095607235142119	0.342669035426899	96.07	4	0.12826917382291975	0.75	0.772325760531397	0.736111111111111	0.0
21	025zlWO2FRZZrFhRw5kqa9		40	191893	0			2013-11-15	0.664473684210526	0.978913757267223	5	0.935379483732855	0	0.133049087132393	0.001206030150754	3.87922210849539e-06	0.093540051679587	0.186957884836451	110.043	4	0.11864524726088244	0.904761904761905	0.296119564238768	0.583333333333333	0.0
22	028DQ7NS44vnLRaoon0mwg		18	232640	0			1987	0.339912280701754	0.969876796096033	2	0.947975008090011	1	0.149447906417405	0.001206030150754	0.000334698055271238	0.056020671834625	0.435677709269516	119.504	4	0.14881033461652354	0.595238095238095	0.236367455958705	0.527777777777778	0.0
23	02GvdetV646M4C1Ip8CKg5		9	209507	0			1978-07-22	0.519736842105263	0.235874727636031	5	0.677743758245588	0	0.049961736088335	0.532663316582915	0.0008014329580348	0.196899224806202	0.300867384261678	123.09	4	0.13168492745039975	0.488095238095238	0.118823964867001	0.333333333333333	0.0
24	02IpFuItRz1yXbYAtHsXJs		40	218436	0			2000-11-20	0.524122807017544	0.665632436665964	5	0.862992557190153	1	0.033125614955723	0.22713567839196	2.97850562947799e-05	0.076072351421189	0.320723168565158	112.771	4	0.13829508439443292	0.75	0.133279313345938	0.541666666666667	0.0
25	02JLJi7sryFu44hhNWOv1c		30	254000	1			2010-09-24	0.854166666666667	0.702784388147523	0	0.843825454907525	1	0.411829014977588	0.223115577889447	0.0	0.048682170542636	0.452398369735605	90.017	4	0.1646231862599941	0.869047619047619	0.106139271530466	0.611111111111111	0.222222222222222
26	02RqhFaAYzWScfVzKfTZ7L		53	202784	0			2020-01-07	0.605263157894737	0.710817242521915	1	0.911333482687377	0	0.177872526511425	0.037286432160804	0.0	0.01922480620155	0.750235134287804	103.811	4	0.12670787681374002	0.988095238095238	0.500745275752028	0.763888888888889	0.111111111111111
27	02hewGMWcG5Vp8nwhcmTA0		19	219267	0			1994	0.540570175438596	0.796166320249822	4	0.744729046872277	1	0.083743303815459	0.008341708542714	0.0	0.198966408268734	0.143066151112969	93.824	4	0.1389102753923601	0.678571428571429	0.48124806178286	0.583333333333333	0.0
28	02yg6Eksd8zZpYxsT1Lpsp		29	104213	0			1999-01-01	0.481359649122807	0.820264883372996	7	0.85223906603938	1	0.024160927079917	0.006532663316583	1.40225179119754e-06	0.044341085271318	0.923711986623472	184.254	4	0.053735564110156946	0.738095238095238	0.933645448816062	0.652777777777778	0.0
29	03UV1fQG0MIm4CPszfttTJ		35	183834	0			2006-06-26	0.658991228070175	0.653583155104377	4	0.906205660518259	0	0.030173827484421	0.029346733668342	0.0	0.06687338501292	0.495245062179956	125.991	4	0.11267915309446254	0.821428571428571	0.554095015155608	0.611111111111111	0.0
30	04GlMx0s7MvdMr2MEXX8s8		34	255840	0			2008-04-18	0.518640350877193	0.872478436806539	1	0.748338436263162	1	0.01486826281841	0.000201005025126	0.163766632548618	0.046925064599483	0.876685129062598	155.975	4	0.16598534201954399	0.845238095238095	0.57676340245891	0.583333333333333	0.0
31	04PxH7CFGAaAvo6j0zZAOr		42	229407	0			2014-11-07	0.706140350877193	0.771063650329849	1	0.903940457521221	0	0.047337925002733	0.02894472361809	0.0	0.293023255813953	0.525551259274741	100.031	4	0.14641693811074918	0.916666666666667	0.15878875183818	0.625	0.222222222222222
32	04PxH7CFGAaAvo6j0zZAOr		42	229407	0			2014-11-07	0.706140350877193	0.771063650329849	1	0.903940457521221	0	0.047337925002733	0.02894472361809	0.0	0.293023255813953	0.525551259274741	100.031	4	0.14641693811074918	0.916666666666667	0.655912687694447	0.694444444444444	0.222222222222222
33	04QM4iqGabg7QlphUoWiMB		26	267408	0			1995-05-08	0.583333333333333	0.870470223212941	1	0.880790580738307	0	0.046572646769433	0.120603015075377	7.05220061412487e-05	0.309560723514212	0.60183927265127	91.57	4	0.1745491560556707	0.69047619047619	0.524113922151196	0.652777777777778	0.0
34	04izaHyfYxsE3AQ68kgB4I		24	145827	0			2000-04-20	0.724780701754386	0.77005954353305	9	0.880367410947651	0	0.165846725702416	0.178894472361809	0.0	0.07328165374677	0.620650015675619	131.909	4	0.08454249333728162	0.75	0.008092994407931	0.333333333333333	0.0
35	04kSSveBKXiNeRaOXu2iog		43	229933	0			1999-06-05	0.671052631578947	0.436696086995813	0	0.771239389639808	0	0.01421231004701	0.299497487437186	0.0	0.170025839793282	0.197408297627756	105.421	4	0.1468063369854901	0.738095238095238	0.500745275752028	0.763888888888889	0.111111111111111
36	04wTGEh2gJTG72yUoizwFV		10	205693	0			1980	0.455043859649123	0.85340040766736	10	0.862619172080751	1	0.034546845960424	0.000100502512563	0.00331627430910952	0.306459948320413	0.493154979621695	112.037	4	0.12886141545750665	0.511904761904762	0.57676340245891	0.583333333333333	0.0
37	053zfKbxSoqRm2RCdJdQMS		28	201400	0			1997-01-01	0.394736842105263	0.958831621331245	2	0.891768102954721	1	0.026456761779819	0.001005025125628	0.0206755373592631	0.097674418604651	0.590343818580834	166.193	4	0.12568329878590465	0.714285714285714	0.134369716795214	0.430555555555556	0.0
38	053zgCcuWJ9x4dJJgaF4VS		10	509704	0			1982	0.135964912280702	0.199726882951271	0	0.608592835984368	1	0.015961517437411	0.78894472361809	0.410440122824974	0.198966408268734	0.011495454070436	135.568	4	0.3539213799230086	0.535714285714286	0.092204115522743	0.402777777777778	0.0
39	054VUAvYt59A1yGSrBAgac		22	203213	0			1976-02-12	0.523026315789474	0.486901426835758	5	0.794737759191497	1	0.030611129332021	0.733668341708543	0.0277379733879222	0.112144702842377	0.818162817431289	147.668	4	0.12702546639028722	0.464285714285714	0.38044076308234	0.583333333333333	0.0
40	05IAxc7j9T9KcEu0lAgbfB		46	216013	0			2016-01-03	0.789473684210526	0.928708417427278	11	0.957458989868817	1	0.079260959877555	0.125628140703518	0.0	0.190697674418605	0.982234298254781	133.933	4	0.13650133254367783	0.94047619047619	0.107039604653722	0.583333333333333	0.0
41	05JJTD8KdxbZsMnOn6imMv		42	230853	0			2000	0.662280701754386	0.910634495084897	7	0.923605406616384	0	0.126489559418389	0.079597989949749	0.00030603889457523	0.102842377260982	0.84010868429303	168.032	4	0.14748741486526504	0.75	0.196562728209437	0.583333333333333	0.0
42	05RcrI18Qi65dwc3vegSNP		43	216000	0			2017-10-27	0.633771929824561	0.882519504774528	0	0.904886366465039	1	0.108997485514376	0.087638190954774	0.0	0.300258397932817	0.638415717420838	79.994	4	0.1364917086171158	0.952380952380952	0.45162710202775	0.652777777777778	0.111111111111111
43	05hJSmfNiPXSzwSLeG1Rfb		39	232507	0			2016-10-21	0.416666666666667	0.425650912231025	6	0.866203669131008	0	0.011588498961408	0.826130653266332	0.00408393039918117	0.101808785529716	0.476434319155607	99.842	4	0.14871187444477346	0.94047619047619	0.061802867060813	0.5	0.0
44	05hk8ivnCt0HP3aI1uWJfi		43	295067	0			2015-03-06	0.589912280701754	0.763030795955458	2	0.819306499390138	1	0.017820050289712	0.539698492462312	0.0	0.318863049095607	0.684397533702581	106.972	4	0.19502517026946994	0.928571428571429	0.324580094635015	0.555555555555556	0.0
45	05v1D17vjtCNRyalBVGbKU		34	481160	0			2007-06-10	0.407894736842105	0.785121145485034	11	0.772334652627387	0	0.040559746364928	0.833165829145729	0.0509723643807574	0.979328165374677	0.337443829031247	108.142	4	0.3327901984009476	0.833333333333333	0.448856076748397	0.597222222222222	0.0
46	05yf5sbF36dhwGRfj18sg5		27	225647	0			2007-11-19	0.934210526315789	0.690735106585936	7	0.873571801956538	1	0.188805072701432	0.109547738693467	0.0	0.248578811369509	0.730379349984324	92.971	4	0.1436334024281907	0.833333333333333	0.204745755929694	0.486111111111111	0.0
47	062pHiX7lpKRDmFvmJor3o		38	182813	0			2006	0.50219298245614	0.993975359219207	2	0.905433997958828	1	0.088225647753362	0.001206030150754	1.19754350051177e-06	0.325064599483204	0.398056223220817	146.553	4	0.111923304708321	0.821428571428571	0.428858677710753	0.597222222222222	0.0
48	063FiQaY2FuZe3a44rZuSX		37	221187	0			2012-08-08	0.780701754385965	0.70579670853792	0	0.901276977074154	1	0.022849021537116	0.194974874371859	1.69907881269191e-06	0.164857881136951	0.729334308705194	95.008	4	0.14033165531536868	0.892857142857143	0.11214149235217	0.444444444444444	0.0
49	063ZobFSzYA0O7fmfi4oik		42	212689	0			2002-03-12	0.775219298245614	0.952806980550451	10	0.919523062753591	1	0.02908057286542	0.152763819095477	0.0	0.357105943152455	0.849514055805204	99.986	4	0.1340405685519692	0.773809523809524	0.515640787091224	0.680555555555556	0.0
50	06AIE9kwssJ3cmPbMuHobb		30	323133	0			2007-05-11	0.084429824561404	0.91766324266249	7	0.897169740870734	1	0.038045260741227	0.002311557788945	7.93244626407369e-05	0.11421188630491	0.38447068659212	166.21	4	0.21580248741486527	0.833333333333333	0.344017286395967	0.555555555555556	0.0
51	06K6svVGbiQILn9BAzZNgo		37	374120	1			2013-12-13	0.607456140350877	0.763030795955458	0	0.803499863092127	0	0.050727014321636	0.014974874371859	1.45342886386899e-06	0.110077519379845	0.335353746472986	133.992	4	0.2535482676932188	0.904761904761905	0.031331592689295	0.597222222222222	0.111111111111111
52	06LDaZRAWOc0fNqkdOQo9S		18	77853	0			1998	0.769736842105263	0.454770009338193	10	0.752321210763448	1	0.01399365912321	0.301507537688442	0.814738996929376	0.055917312661499	0.471209112759954	147.901	4	0.03422120225051821	0.726190476190476	0.706461390714564	0.638888888888889	0.0
53	06SnvuakcuOcj3odFZpIpJ		18	137639	0			1969-09-06	0.642543859649123	0.5401190870661	2	0.785602270181465	0	0.008527386028206	0.072663316582915	0.0	0.11937984496124	0.804577280802592	94.858	4	0.07848090020728457	0.380952380952381	0.071516461090604	0.402777777777778	0.0
54	06V1fPvTfXdIBMUItnLmQ7		14	119493	0			1967-10-16	0.413377192982456	0.332268980128726	5	0.634107485126826	0	0.01388433366131	0.765829145728643	0.000541453428863869	0.09250645994832	0.326993416239941	79.078	3	0.06504737933076696	0.357142857142857	0.108510148755039	0.486111111111111	0.0
55	06kPUkFJv1RfEVjgEF3oRz		23	250227	0			1997-01-01	0.757675438596491	0.735919912441887	0	0.776964627983969	1	0.013228380889909	0.008844221105528	0.0224155578300921	0.089405684754522	0.863099592433901	129.879	4	0.16183002665087357	0.714285714285714	0.319238118103698	0.652777777777778	0.0
56	07Iyx8PziAv997SrJanQgl		11	279013	0			1981	0.330043859649123	0.138476368346537	10	0.526298757872203	0	0.009511315185307	0.355778894472362	8.86386898669396e-06	0.21343669250646	0.069077228550528	97.964	4	0.1831403612673971	0.523809523809524	0.209407480767884	0.583333333333333	0.0
57	07QfWWYlJsbXiEbbKSvqcd		34	320993	1			2010-04-26	0.50109649122807	0.957827514534446	10	0.886167326313693	0	0.292664261506505	0.013969849246231	7.21596724667349e-06	0.363307493540052	0.664541749399101	84.011	4	0.21421824104234527	0.869047619047619	0.100137050708762	0.708333333333333	0.222222222222222
58	07QfWWYlJsbXiEbbKSvqcd		34	320993	1			2010-04-26	0.50109649122807	0.957827514534446	10	0.886167326313693	0	0.292664261506505	0.013969849246231	7.21596724667349e-06	0.363307493540052	0.664541749399101	84.011	4	0.21421824104234527	0.869047619047619	0.07381731240559	0.541666666666667	0.222222222222222
59	07rj0QlIw4hRcvhIJGY2Lf		6	123173	0			1965-10-12	0.541666666666667	0.187677601389684	0	0.651507231224952	0	0.027550016398819	0.97286432160804	8.84339815762538e-06	0.11937984496124	0.282056641237329	67.152	4	0.06777169084986674	0.333333333333333	0.031211548272861	0.430555555555556	0.0
60	0804d5chEAcksTIZ7IVh6O		29	200000	0			1967-10-16	0.296052631578947	0.438704300589411	9	0.693973564334254	0	0.025363507160818	0.879396984924623	5.03582395087001e-05	0.509043927648579	0.339533911589508	101.975	4	0.12464687592537756	0.357142857142857	0.108510148755039	0.486111111111111	0.0
61	08D5BLpRFI6PT2XYuq7eDg		28	273267	0			1996-01-01	0.697368421052632	0.940757698988864	5	0.865307544868444	0	0.007652782333005	0.509547738693467	5.496417604913e-06	0.243410852713178	0.985369422092173	137.921	4	0.1788865857269766	0.702380952380952	0.128327481168032	0.5	0.0
62	08D6EqghkRLD6iFHKKWJ4Y		55	213120	1			2020-01-27	0.765350877192982	0.68772278619554	0	0.808677469942499	0	0.036733355198426	0.301507537688442	0.0269191402251791	0.108010335917313	0.46389382380604	125.018	4	0.1343596387326029	0.988095238095238	0.402879065254144	0.861111111111111	0.111111111111111
63	08ER472xnuMlbtkKSh3PB4		11	166867	0			1975-09-07	0.303728070175439	0.289092387866373	10	0.664451248350882	1	0.07149885208265	0.934673366834171	0.0	0.096640826873385	0.402236388337339	178.159	4	0.10011844832691738	0.452380952380952	0.1218750937847	0.472222222222222	0.0
64	08ULDx4KeBpXe7OHEWjgVP		45	277293	0			2014-04-16	0.6875	0.931720737817674	3	0.918079306997237	1	0.017492073904012	0.014773869346734	0.00118730808597748	0.325064599483204	0.970738844184345	153.068	4	0.18186704175303522	0.916666666666667	0.030641337294799	0.513888888888889	0.0
65	08uPk6Ge77W31WGGYNEPHA		2	156369	0			1978-01-01	0.448464912280702	0.547147834643693	5	0.807955592064322	1	0.318902372362523	0.784924623115578	1.31013306038895e-06	0.090439276485788	0.736649597659108	92.544	4	0.09234675747705064	0.488095238095238	0.050598721526965	0.583333333333333	0.111111111111111
66	08uPk6Ge77W31WGGYNEPHA		2	156369	0			1978-01-01	0.448464912280702	0.547147834643693	5	0.807955592064322	1	0.318902372362523	0.784924623115578	1.31013306038895e-06	0.090439276485788	0.736649597659108	92.544	4	0.09234675747705064	0.488095238095238	0.0	0.013888888888889	0.111111111111111
67	08xY8SSfwHc7SIXWroGW9j		16	167360	0			1994	0.289473684210526	0.244911688807222	2	0.671894058198292	0	0.017273422980212	0.660301507537688	0.0	0.094573643410853	0.584073570906051	124.354	4	0.10048341723423157	0.678571428571429	0.362624170943249	0.75	0.0
68	095PNli2gmoYwlvgotxxEs		28	269067	0			1995	0.565789473684211	0.616431203622817	7	0.900007467702188	0	0.005138296709304	0.055175879396985	6.16171954964176e-06	0.083204134366925	0.120075242972097	119.804	4	0.17577731714539532	0.69047619047619	0.772325760531397	0.736111111111111	0.0
69	09FEC2H0Ls5bXMSFj2bLzL		50	198460	0			2018-10-24	0.710526315789474	0.808215601811409	0	0.828840265850198	1	0.21394992893845	0.299497487437186	0.000127942681678608	0.103875968992248	0.925802069181733	169.848	4	0.12350681077879774	0.964285714285714	0.74934725848564	0.902777777777778	0.222222222222222
70	09GKW7x0iMuxzzM7IeQ01g		7	153521	0			1964-02-21	0.505482456140351	0.207759737325662	0	0.707166504866453	1	0.011588498961408	0.739698492462312	0.0	0.177260981912145	0.540181837182569	82.018	4	0.09023837725792123	0.321428571428571	0.030581315086582	0.430555555555556	0.0
71	09PwmLzRfWj5hkmIiw1a9X		21	251316	0			1969-09-07	0.288377192982456	0.38448253356227	0	0.798347148582381	1	0.008746036952006	0.717587939698492	8.08597748208802e-06	0.374677002583979	0.36356986100951	119.002	4	0.16263621557595498	0.380952380952381	0.1218750937847	0.472222222222222	0.0
72	09d4etyrEWf5BguIIUfRW9		12	212523	0			1974-08-16	0.271929824561404	0.428663232621422	9	0.811415627411445	0	0.009511315185307	0.436180904522613	0.0	0.126614987080103	0.295642177866026	83.695	4	0.13391767841279242	0.44047619047619	0.129657973450177	0.5	0.0
73	0ADOK8ffxUlSDu51nIu5KU		23	274533	0			1999	0.660087719298246	0.298129349037564	1	0.748910960097578	0	0.004591669399803	0.412060301507538	0.0	0.359173126614987	0.545407043578221	103.037	4	0.1798238081137104	0.738095238095238	0.61224653121655	0.625	0.0
74	0AGuYcLlnbR7AOQSbTGzIz		29	232960	0			2000-12-31	0.424342105263158	0.573254611360464	2	0.805590819704777	0	0.007652782333005	0.080301507537688	0.000113613101330604	0.284754521963824	0.554812415090396	201.077	4	0.1490472312703583	0.75	0.150115542750818	0.555555555555556	0.0
75	0AHy3nZXS725AAHDC0qjPR		22	202336	0			1966-09-28	0.462719298245614	0.362392184032694	9	0.757100540163792	1	0.015742866513611	0.882412060301508	0.0	0.302325581395349	0.353119448218205	117.999	4	0.12637622149837133	0.345238095238095	0.15351680121645	0.486111111111111	0.0
76	0AJJJnR5VwJfeYGvnyG74v		7	191530	0			1967-03-29	0.62719298245614	0.302145776224759	7	0.677071665048665	0	0.004263693014103	0.777889447236181	0.00222108495394063	0.089405684754522	0.389695892987773	104.859	4	0.11837651761918863	0.357142857142857	0.693636645558857	0.638888888888889	0.0
77	0ATVUbPlX65HB5H1WbIugt		54	181356	1			2020-12-04	0.911184210526316	0.900593427116908	7	0.783212605481293	1	0.289384497649503	0.185929648241206	0.0	0.064392764857881	0.895495872086947	154.912	4	0.11084468463132958	0.988095238095238	0.277082520532597	0.75	0.0
78	0AUeyoLQYwYUDsQRJZKvZd		22	248907	0			1980	0.399122807017544	0.82528541735699	4	0.881313319891469	1	0.021974417841915	0.000502512562814	4.4114636642784e-05	0.121447028423773	0.409551677291253	195.99	4	0.16085282795380515	0.511904761904762	0.57676340245891	0.583333333333333	0.0
79	0AXtdoJaVk6W8pcYstecgw		33	193227	0			2000	0.586622807017544	0.80721149501461	2	0.853334329026958	1	0.022849021537116	0.2	8.20880245649949e-05	0.005064599483204	0.717838854634758	120.172	4	0.11963281018655611	0.75	0.448856076748397	0.597222222222222	0.0
80	0AYmWvKRGtgePrpo3NdYXh		48	218530	0			2019-02-01	0.637061403508772	0.381470213171873	4	0.752868842257237	1	0.007215480485405	0.94070351758794	0.447287615148414	0.094573643410853	0.215173999372975	112.996	4	0.1383646727864969	0.976190476190476	0.901893700669248	0.847222222222222	0.0
81	0ApseFDjbipbKK0dBD4Viz		35	267013	1			2005-11-15	0.851973684210526	0.697763854163529	9	0.881213750528962	0	0.295944025363507	0.077989949748744	0.0	0.142118863049096	0.537046713345177	90.011	4	0.17425673674859343	0.80952380952381	0.809619559236918	0.638888888888889	0.0
82	0Aq0weZZ0YzbrNlsA8UV3P		23	294107	0			1997-01-05	0.559210526315789	0.596349067686839	7	0.814203569561646	1	0.004154367552203	0.015778894472362	0.000338792221084954	0.1328165374677	0.188002926115582	140.843	4	0.19431448030796564	0.714285714285714	0.005081880295709	0.166666666666667	0.0
83	0Au3WxSl0fm6IvSZHCShjZ		22	157187	0			1998-01-01	0.486842105263158	0.924691990240082	4	0.865432006571578	0	0.019897234065814	0.014170854271357	0.0179119754350051	0.140051679586563	0.639460758699969	139.08	4	0.09295232454841575	0.726190476190476	0.291177735762232	0.555555555555556	0.0
84	0AwC0oZxr25SOcThearmN4		28	300383	0			2005-04-29	0.950657894736842	0.70579670853792	7	0.871978692156423	1	0.037607958893626	0.01356783919598	0.0	0.062015503875969	0.750235134287804	99.024	4	0.19896061593129996	0.80952380952381	0.204745755929694	0.486111111111111	0.0
85	0B1uuoyiPSkV2f8cAw473e		0	230773	0			1946-01-01	0.226973684210526	0.191694028576879	6	0.599556916336843	1	0.059910353121242	0.995979899497487	0.9007164790174	0.180361757105943	0.329083498798203	79.244	1	0.14742819070180632	0.107142857142857	0.01804667727059	0.402777777777778	0.444444444444444
86	0B1uuoyiPSkV2f8cAw473e		0	230773	0			1946-01-01	0.226973684210526	0.191694028576879	6	0.599556916336843	1	0.059910353121242	0.995979899497487	0.9007164790174	0.180361757105943	0.329083498798203	79.244	1	0.14742819070180632	0.107142857142857	0.000110040715065	0.111111111111111	0.444444444444444
87	0B1uuoyiPSkV2f8cAw473e		0	230773	0			1946-01-01	0.226973684210526	0.191694028576879	6	0.599556916336843	1	0.059910353121242	0.995979899497487	0.9007164790174	0.180361757105943	0.329083498798203	79.244	1	0.14742819070180632	0.107142857142857	0.0	0.069444444444444	0.444444444444444
88	0B1uuoyiPSkV2f8cAw473e		0	230773	0			1946-01-01	0.226973684210526	0.191694028576879	6	0.599556916336843	1	0.059910353121242	0.995979899497487	0.9007164790174	0.180361757105943	0.329083498798203	79.244	1	0.14742819070180632	0.107142857142857	0.026709882656583	0.652777777777778	0.444444444444444
89	0B1uuoyiPSkV2f8cAw473e		0	230773	0			1946-01-01	0.226973684210526	0.191694028576879	6	0.599556916336843	1	0.059910353121242	0.995979899497487	0.9007164790174	0.180361757105943	0.329083498798203	79.244	1	0.14742819070180632	0.107142857142857	0.44250372637876	0.597222222222222	0.444444444444444
90	0BHIwbxTN0iSClgDaO36Ft		34	293882	0			2009-02-21	0.679824561403509	0.869466116416142	9	0.966967863988251	0	0.426041325024598	0.349748743718593	0.0	0.192764857881137	0.823388023826941	173.874	4	0.19414791234823808	0.857142857142857	0.522553344737553	0.694444444444444	0.222222222222222
91	0BHIwbxTN0iSClgDaO36Ft		34	293882	0			2009-02-21	0.679824561403509	0.869466116416142	9	0.966967863988251	0	0.426041325024598	0.349748743718593	0.0	0.192764857881137	0.823388023826941	173.874	4	0.19414791234823808	0.857142857142857	0.229124776167182	0.513888888888889	0.222222222222222
92	0BleuvVMt39PmQaukAdEcv		37	228053	0			2010	0.389254385964912	0.50397124238134	5	0.812535782739651	1	0.006450202252105	0.625125628140704	1.8014329580348e-05	0.108010335917313	0.367750026126032	156.074	4	0.14541456914421083	0.869047619047619	0.38512249532327	0.583333333333333	0.0
93	0Bn854XtnJv15m9f1ZvWbe		44	194013	0			2011-05-31	0.471491228070175	0.881515397977729	6	0.912503422696836	0	0.023395648846616	0.300502512562814	0.0	0.086304909560724	0.614379768000836	160.015	4	0.12021468759253776	0.880952380952381	0.36705581064994	0.680555555555556	0.0
94	0BsQm2iFzKTzAemliJtu8x		39	201231	0			2016-11-25	0.548245614035088	0.792149893062626	4	0.870659398103204	1	0.007652782333005	0.005427135678392	0.0	0.07266149870801	0.599749190093009	129.992	4	0.12555818774059815	0.94047619047619	0.367315906885548	0.625	0.0
95	0C2VNcmHdRRvQtetkaZVn5		32	311440	0			1990	0.591008771929825	0.394523601530259	1	0.671918950538919	0	0.002733136547502	0.671356783919598	3.85875127942682e-05	0.103875968992248	0.336398787752116	102.03	4	0.2071461356233343	0.630952380952381	0.772325760531397	0.736111111111111	0.0
96	0C2XO15BhHDglDB724brOi		43	316627	0			1998	0.855263157894737	0.541123193862899	10	0.728474348442984	0	0.204110637367443	0.095577889447236	1.22824974411464e-05	0.093540051679587	0.679172327306929	80.988	4	0.2109860823215872	0.726190476190476	0.706461390714564	0.638888888888889	0.0
97	0CAinfqSGq1peA63s4gNam		21	187933	0			1988-12-31	0.628289473684211	0.297125242240765	10	0.761879869564135	1	0.010057942494807	0.893467336683417	0.0	0.07328165374677	0.585118612185181	116.335	3	0.11571365116967723	0.607142857142857	0.295509338455228	0.597222222222222	0.0
98	0CBj18X3J7MabwDqKeMxuo		47	239373	0			1999	0.355263157894737	0.769055436736251	0	0.876185497722351	1	0.106810976276375	0.124623115577889	0.0	0.054470284237726	0.74082976277563	119.973	4	0.15379478827361565	0.738095238095238	0.357312205516041	0.652777777777778	0.0
99	0CLO6b6Ohrnmp9fDLvYj3U		25	162987	0			2003	0.235745614035088	0.339297727706319	8	0.693625071565479	0	0.011588498961408	0.986934673366834	0.0	0.212403100775194	0.251750444142544	91.165	4	0.09724607639917086	0.785714285714286	0.030951452037254	0.541666666666667	0.111111111111111
100	0CMxaedU31OgxQvrA1VE1J		54	225867	0			2019-10-25	0.879385964912281	0.615427096826018	3	0.903119010280537	0	0.223789220509457	0.240201005025126	0.0	0.066356589147287	0.399101264499948	114.959	4	0.1437962688777021	0.976190476190476	0.350179566439583	0.680555555555556	0.111111111111111

Rows: 1-100 | Columns: 25

Machine Learning¶

We can use AutoML to easily get a well-performing model.

In [29]:

# define a random seed so models tested by AutoML produce consistent results
vp.set_option("random_state", 2)

AutoML automatically tests several machine learning models and picks the best performing one.

In [30]:

from verticapy.learn.delphi import AutoML
# define the model
auto_model = AutoML('spotify.automl_spotify_polish',
                    estimator = "native",
                    preprocess_data = False,
                    stepwise = False,
                    cv = 2)

Train the model.

In [31]:

%%time
auto_model.fit('spotify.polish_tracks_data_final', 
               predictors, 
               response)

Starting AutoML

Testing Model - LinearRegression

Model: LinearRegression; Parameters: {'tol': 1e-06, 'max_iter': 100, 'solver': 'newton'}; Test_score: 8.066647640522543; Train_score: 8.118985304784605; Time: 0.20426690578460693;
Model: LinearRegression; Parameters: {'tol': 1e-06, 'max_iter': 100, 'solver': 'bfgs'}; Test_score: 8.06657408402635; Train_score: 8.118986556025963; Time: 0.7867480516433716;

Grid Search Selected Model
LinearRegression; Parameters: {'solver': 'bfgs', 'penalty': 'none', 'max_iter': 100, 'tol': 1e-06}; Test_score: 8.06657408402635; Train_score: 8.118986556025963; Time: 0.7867480516433716;

Testing Model - ElasticNet

Model: ElasticNet; Parameters: {'tol': 1e-06, 'max_iter': 100, 'solver': 'cgd', 'C': 1.0, 'l1_ratio': 0.5}; Test_score: 14.822426878049042; Train_score: 15.035727014744669; Time: 0.27442634105682373;

Grid Search Selected Model
ElasticNet; Parameters: {'solver': 'cgd', 'penalty': 'enet', 'max_iter': 100, 'l1_ratio': 0.5, 'C': 1.0, 'tol': 1e-06}; Test_score: 14.822426878049042; Train_score: 15.035727014744669; Time: 0.27442634105682373;

Testing Model - Ridge

Model: Ridge; Parameters: {'tol': 1e-06, 'max_iter': 100, 'C': 1.0}; Test_score: 8.064233984706952; Train_score: 8.121069572782197; Time: 0.20085954666137695;

Grid Search Selected Model
Ridge; Parameters: {'solver': 'newton', 'penalty': 'l2', 'max_iter': 100, 'C': 1.0, 'tol': 1e-06}; Test_score: 8.064233984706952; Train_score: 8.121069572782197; Time: 0.20085954666137695;

Testing Model - Lasso

Model: Lasso; Parameters: {'tol': 1e-06, 'max_iter': 100, 'solver': 'cgd', 'C': 1.0}; Test_score: 9.76754307664328; Train_score: 10.121086467655486; Time: 0.2544424533843994;

Grid Search Selected Model
Lasso; Parameters: {'solver': 'cgd', 'penalty': 'l1', 'max_iter': 100, 'C': 1.0, 'tol': 1e-06}; Test_score: 9.76754307664328; Train_score: 10.121086467655486; Time: 0.2544424533843994;

Testing Model - LinearSVR

Model: LinearSVR; Parameters: {'tol': 1e-06, 'fit_intercept': True, 'intercept_mode': 'regularized', 'max_iter': 100}; Test_score: 8.06441805263914; Train_score: 8.120720846232265; Time: 2.197774648666382;
Model: LinearSVR; Parameters: {'tol': 1e-06, 'fit_intercept': True, 'intercept_mode': 'unregularized', 'max_iter': 100}; Test_score: 8.065481552212296; Train_score: 8.119616043007014; Time: 2.323397994041443;
Model: LinearSVR; Parameters: {'tol': 1e-06, 'C': 1.0, 'fit_intercept': True, 'intercept_mode': 'regularized', 'max_iter': 100}; Test_score: 8.06441805263914; Train_score: 8.120720846232265; Time: 2.182707905769348;
Model: LinearSVR; Parameters: {'tol': 1e-06, 'C': 1.0, 'fit_intercept': True, 'intercept_mode': 'unregularized', 'max_iter': 100}; Test_score: 8.065481552212296; Train_score: 8.119616043007014; Time: 2.6499600410461426;

Grid Search Selected Model
LinearSVR; Parameters: {'tol': 1e-06, 'C': 1.0, 'max_iter': 100, 'fit_intercept': True, 'intercept_scaling': 1.0, 'intercept_mode': 'regularized', 'acceptable_error_margin': 0.1}; Test_score: 8.06441805263914; Train_score: 8.120720846232265; Time: 2.197774648666382;

Testing Model - RandomForestRegressor

Model: RandomForestRegressor; Parameters: {'max_features': 'max', 'max_leaf_nodes': 128, 'max_depth': 5, 'min_samples_leaf': 1, 'min_info_gain': 0.0, 'nbins': 32}; Test_score: 7.113663323363556; Train_score: 6.39265180458105; Time: 0.42195308208465576;
Model: RandomForestRegressor; Parameters: {'max_features': 'max', 'max_leaf_nodes': 1000, 'max_depth': 5, 'min_samples_leaf': 1, 'min_info_gain': 0.0, 'nbins': 32}; Test_score: 7.113663323363556; Train_score: 6.39265180458105; Time: 0.3885120153427124;
Model: RandomForestRegressor; Parameters: {'max_features': 'max', 'max_leaf_nodes': 128, 'max_depth': 5, 'min_samples_leaf': 2, 'min_info_gain': 0.0, 'nbins': 32}; Test_score: 7.181553675673483; Train_score: 6.5483753528126805; Time: 0.40996646881103516;
Model: RandomForestRegressor; Parameters: {'max_features': 'max', 'max_leaf_nodes': 64, 'max_depth': 4, 'min_samples_leaf': 1, 'min_info_gain': 0.0, 'nbins': 32}; Test_score: 7.266855044387853; Train_score: 6.809449561850816; Time: 0.3967854976654053;
Model: RandomForestRegressor; Parameters: {'max_features': 'max', 'max_leaf_nodes': 64, 'max_depth': 6, 'min_samples_leaf': 2, 'min_info_gain': 0.0, 'nbins': 32}; Test_score: 7.064327407880318; Train_score: 6.210967309985071; Time: 0.4239314794540405;

Grid Search Selected Model
RandomForestRegressor; Parameters: {'n_estimators': 10, 'max_features': 'max', 'max_leaf_nodes': 64, 'sample': 0.632, 'max_depth': 6, 'min_samples_leaf': 2, 'min_info_gain': 0.0, 'nbins': 32}; Test_score: 7.064327407880318; Train_score: 6.210967309985071; Time: 0.4239314794540405;

Final Model

RandomForestRegressor; Best_Parameters: {'n_estimators': 10, 'max_features': 'max', 'max_leaf_nodes': 64, 'sample': 0.632, 'max_depth': 6, 'min_samples_leaf': 2, 'min_info_gain': 0.0, 'nbins': 32}; Best_Test_score: 7.064327407880318; Train_score: 6.210967309985071; Time: 0.4239314794540405;


CPU times: user 1.51 s, sys: 113 ms, total: 1.62 s
Wall time: 33.3 s

Out[31]:

	model_type	avg_score	avg_train_score	avg_time	score_std	score_train_std
1	RandomForestRegressor	7.064327407880318	6.210967309985071	0.4239314794540405	0.021951610316419245	0.05495843581963396
2	RandomForestRegressor	7.113663323363556	6.39265180458105	0.42195308208465576	0.04420745773425167	0.05405953173125159
3	RandomForestRegressor	7.113663323363556	6.39265180458105	0.3885120153427124	0.04420745773425167	0.05405953173125159
4	RandomForestRegressor	7.181553675673483	6.5483753528126805	0.40996646881103516	0.0457345096188477	0.05011685394254436
5	RandomForestRegressor	7.266855044387853	6.809449561850816	0.3967854976654053	0.05954893368677364	0.20818778505776214
6	Ridge	8.064233984706952	8.121069572782197	0.20085954666137695	0.15216905564050348	0.1513846089750132
7	LinearSVR	8.06441805263914	8.120720846232265	2.197774648666382	0.15018542501699342	0.1512890154897334
8	LinearSVR	8.06441805263914	8.120720846232265	2.182707905769348	0.15018542501699342	0.1512890154897334
9	LinearSVR	8.065481552212296	8.119616043007014	2.323397994041443	0.14813750528375574	0.15105738388572493
10	LinearSVR	8.065481552212296	8.119616043007014	2.6499600410461426	0.14813750528375574	0.15105738388572493
11	LinearRegression	8.06657408402635	8.118986556025963	0.7867480516433716	0.1451403695610597	0.15092076870560675
12	LinearRegression	8.066647640522543	8.118985304784605	0.20426690578460693	0.14523553927522023	0.15092178039328827
13	Lasso	9.76754307664328	10.121086467655486	0.2544424533843994	0.1918344551389224	0.22492932355164136
14	ElasticNet	14.822426878049042	15.035727014744669	0.27442634105682373	0.00898900231672321	0.08108192620896942

Rows: 1-14 | Columns: 8

The numbers are hard to grasp on their own, so let's plot the performance and efficiency of each model.

In [32]:

# visualize the performance of different models
auto_model.plot()

Out[32]:

<AxesSubplot:xlabel='time', ylabel='score'>

Extract the best model according to AutoML. From here, we can look at the model type and its hyperparameters.

In [33]:

# extract the model type and hyperparameters
best_model = auto_model.best_model_
bm_type = best_model.type
hyperparams = best_model.get_params()

print(bm_type)
print(hyperparams)

RandomForestRegressor
{'n_estimators': 10, 'max_features': 'max', 'max_leaf_nodes': 64, 'sample': 0.632, 'max_depth': 6, 'min_samples_leaf': 2, 'min_info_gain': 0.0, 'nbins': 32}

Thanks to AutoML, we know best model type and its hyperparameters. Let's create a new model with this information in mind.

In [47]:

from verticapy.learn.ensemble import RandomForestRegressor

# define the model
rf_model = RandomForestRegressor('spotify.randomforest_spotify', **hyperparams)

# train the model
rf_model.fit(polish_tracks, predictors, response) 

# use the model to predict
rf_model.predict(polish_tracks, 
                 name = 'estimated_popularity')

Out[47]:

	Abc id Varchar(44)	Abc Varchar(96)	123 popularity Int	123 duration_ms Int	123 explicit Int	Abc Varchar(100)	Abc Varchar(156)	Abc release_date Varchar(20)	123 danceability Float	123 energy Float	123 key Int	123 loudness Float	123 mode Int	123 speechiness Float	123 acousticness Float	123 instrumentalness Float	123 liveness Float	123 valence Float	123 tempo Numeric(9,4)	123 time_signature Int	123 duration_minute Float	123 release_year Float	123 artists_followers Float	123 artist_popularity Float	123 nb_singers Float	123 estimated_popularity Float
1	0004Uy71ku11n3LMpuyf59		34	258907	0			2012-01-01	0.617324561403509	0.599361388077236	6	0.797675055385458	1	0.002405160161802	0.177889447236181	0.00151484135107472	0.061291989664083	0.365659943567771	140.056	4	0.16825584838614155	0.892857142857143	0.054260076228204	0.5	0.0	40.4301647912923
2	008UO76dicJJrUb5g6WID0		42	216813	0			2001	0.676535087719298	0.886535931961723	9	0.925795932591542	0	0.023614299770417	0.164824120603015	0.0	0.069457364341085	0.74187480405476	119.981	4	0.13709357417826473	0.761904761904762	0.471264367816092	0.611111111111111	0.0	34.869262824777
3	00D7fT49Q54HNiaaLY32UA		41	196231	0			2017-06-07	0.527412280701754	0.672661184243556	1	0.904687227740024	1	0.098064939324369	0.198994974874372	0.0	0.099741602067183	0.120075242972097	120.061	5	0.12185667752442997	0.952380952380952	0.754219061052589	0.819444444444444	0.222222222222222	45.0804559629553
4	00De7REGurYkmfrKAh6CU1		36	400867	0			1992-10-30	0.173245614035088	0.102328523661777	11	0.460284270529958	0	0.025910134470318	0.512562814070352	0.708290685772774	0.055090439276486	0.001881074302435	114.592	3	0.273349126443589	0.654761904761905	0.116863239398577	0.555555555555556	0.0	30.6638002980626
5	00KCwnrvIXX8GRU3ZMOIBW		55	207821	0			2019-12-06	0.838815789473684	0.692743320179534	6	0.876608667513006	0	0.04307423198863	0.17286432160804	0.00101944728761515	0.07297157622739	0.596614066255617	123.985	4	0.13043677820550784	0.976190476190476	0.234066604643718	0.75	0.0	52.0762280321545
6	00g7zzRDf2DOf4fKYzFTUA		46	192857	0			2018-08-24	0.756578947368421	0.79817453384342	1	0.865855176362233	1	0.337487700885536	0.569849246231156	0.0	0.078449612403101	0.726199184867802	112.343	4	0.11935889843055966	0.964285714285714	0.281444134329702	0.597222222222222	0.0	45.0804559629553
7	00i4Q7OfN0mGnUyuyQcAdZ		2	97924	0			1978-01-01	0.5625	0.674669397837154	7	0.840589450626042	0	0.078058379796655	0.632160804020101	0.0	0.208268733850129	0.937297523252168	98.842	4	0.049079804560260586	0.488095238095238	4.0014805478e-05	0.055555555555556	0.111111111111111	2.86773809523809
8	00i4Q7OfN0mGnUyuyQcAdZ		2	97924	0			1978-01-01	0.5625	0.674669397837154	7	0.840589450626042	0	0.078058379796655	0.632160804020101	0.0	0.208268733850129	0.937297523252168	98.842	4	0.049079804560260586	0.488095238095238	0.050598721526965	0.583333333333333	0.111111111111111	10.6410158019808
9	00l8ILQ8qgsew789dGn1Gh		7	326240	0			1963-03-25	0.557017543859649	0.601369601670834	2	0.832026485450427	0	0.006668853175905	0.511557788944724	9.71340839303992e-06	0.145219638242894	0.782631413940851	102.428	4	0.2181026058631922	0.30952380952381	0.006952572451807	0.125	0.0	7.30127856572452
10	00vlVv7WLZjyRS783REKmn		24	243107	0			1998	0.902412280701754	0.505979455974937	1	0.825504692206208	1	0.381217885645567	0.052160804020101	0.0	0.197932816537468	0.359389695892988	93.05	4	0.15655907610305003	0.726190476190476	0.412122485319568	0.513888888888889	0.0	24.9533724588221
11	00wJpDUdKoWQ2ibqTpJh1Y		32	211653	0			1998-08-30	0.81359649122807	0.924691990240082	6	0.835312274413163	0	0.010604569804307	0.315577889447236	0.000427840327533265	0.216537467700258	0.989549587208695	130.103	4	0.13327361563517914	0.726190476190476	0.079889559136881	0.375	0.0	24.9533724588221
12	010cqDI9IKD8f3Ji8TnT9h		14	191213	0			1967-04-14	0.495614035087719	0.415609844263036	10	0.821297886640281	1	0.016180168361211	0.787939698492462	0.0	0.070387596899225	0.52241613543735	135.15	4	0.11814184187148356	0.357142857142857	0.13709072356772	0.486111111111111	0.0	13.4855833789985
13	0123TtFcZ7pZIss52i7nAK		23	174160	0			1995-02-27	0.629385964912281	0.627476378387605	0	0.69920095586588	1	0.012791079042309	0.014874371859296	0.00065813715455476	0.037622739018088	0.746054969171282	126.63	4	0.10551747112822031	0.69047619047619	0.011314186248912	0.388888888888889	0.0	23.1748101052984
14	015JvDPIBquLQTX6VZPwB2		1	142211	0			1960-09-07	0.244517543859649	0.293108815053569	0	0.727403977796032	1	0.012244451732809	0.689447236180905	6.86796315250768e-06	0.631007751937984	0.405371512174731	112.214	3	0.08186556114894877	0.273809523809524	0.1218750937847	0.472222222222222	0.0	3.61489291707544
15	01FN979nyz1gmdsfWZv5OL		45	182293	0			1995-08-31	0.544956140350877	0.686718679398741	2	0.856943718417843	1	0.002951787471302	0.062412060301508	6.01842374616172e-05	0.205167958656331	0.36461490228864	112.029	4	0.1115383476458395	0.69047619047619	0.167031801766654	0.527777777777778	0.0	24.4145764534479
16	01LN8YM6L9V2a135AnhZqf		57	168333	0			2020-12-05	0.611842105263158	0.458786436525389	7	0.754362382694845	0	0.038154586203127	0.117587939698492	3.57215967246674e-05	0.136950904392765	0.247570279026022	144.046	4	0.1012037311222979	0.988095238095238	0.088432720106439	0.638888888888889	0.111111111111111	52.3733282506672
17	01LN8YM6L9V2a135AnhZqf		57	168333	0			2020-12-05	0.611842105263158	0.458786436525389	7	0.754362382694845	0	0.038154586203127	0.117587939698492	3.57215967246674e-05	0.136950904392765	0.247570279026022	144.046	4	0.1012037311222979	0.988095238095238	0.009493512599662	0.875	0.111111111111111	55.4733496073336
18	01j7wulLc7rJVk0STUPEKu		5	200160	0			1967-03-06	0.385964912280702	0.242903475213624	4	0.739103377890623	0	0.009074013337706	0.961809045226131	0.000517911975435005	0.147286821705426	0.287281847632982	132.1	4	0.12476532425229493	0.357142857142857	0.016446085051469	0.208333333333333	0.0	11.5052223490586
19	01lYEV7KZT34GOAW2MIgHF		35	458307	0			1969-06-11	0.287280701754386	0.549156048237291	3	0.821372563662161	1	0.038154586203127	0.535678391959799	0.00208802456499488	0.207235142118863	0.185912843557321	140.603	3	0.3158720758069292	0.380952380952381	0.871872592859358	0.583333333333333	0.0	14.9059819728965
20	01uK4bh8p175iZj7uSkp5k		45	204893	0			2000	0.605263157894737	0.533090339488508	2	0.804918726507854	0	0.005903574942604	0.422110552763819	2.55885363357216e-06	0.095607235142119	0.342669035426899	96.07	4	0.12826917382291975	0.75	0.772325760531397	0.736111111111111	0.0	34.389324210261
21	025zlWO2FRZZrFhRw5kqa9		40	191893	0			2013-11-15	0.664473684210526	0.978913757267223	5	0.935379483732855	0	0.133049087132393	0.001206030150754	3.87922210849539e-06	0.093540051679587	0.186957884836451	110.043	4	0.11864524726088244	0.904761904761905	0.296119564238768	0.583333333333333	0.0	40.4301647912923
22	028DQ7NS44vnLRaoon0mwg		18	232640	0			1987	0.339912280701754	0.969876796096033	2	0.947975008090011	1	0.149447906417405	0.001206030150754	0.000334698055271238	0.056020671834625	0.435677709269516	119.504	4	0.14881033461652354	0.595238095238095	0.236367455958705	0.527777777777778	0.0	18.5862492866963
23	02GvdetV646M4C1Ip8CKg5		9	209507	0			1978-07-22	0.519736842105263	0.235874727636031	5	0.677743758245588	0	0.049961736088335	0.532663316582915	0.0008014329580348	0.196899224806202	0.300867384261678	123.09	4	0.13168492745039975	0.488095238095238	0.118823964867001	0.333333333333333	0.0	11.7498709352586
24	02IpFuItRz1yXbYAtHsXJs		40	218436	0			2000-11-20	0.524122807017544	0.665632436665964	5	0.862992557190153	1	0.033125614955723	0.22713567839196	2.97850562947799e-05	0.076072351421189	0.320723168565158	112.771	4	0.13829508439443292	0.75	0.133279313345938	0.541666666666667	0.0	34.09439364668
25	02JLJi7sryFu44hhNWOv1c		30	254000	1			2010-09-24	0.854166666666667	0.702784388147523	0	0.843825454907525	1	0.411829014977588	0.223115577889447	0.0	0.048682170542636	0.452398369735605	90.017	4	0.1646231862599941	0.869047619047619	0.106139271530466	0.611111111111111	0.222222222222222	35.0806689337132
26	02RqhFaAYzWScfVzKfTZ7L		53	202784	0			2020-01-07	0.605263157894737	0.710817242521915	1	0.911333482687377	0	0.177872526511425	0.037286432160804	0.0	0.01922480620155	0.750235134287804	103.811	4	0.12670787681374002	0.988095238095238	0.500745275752028	0.763888888888889	0.111111111111111	52.7615971869234
27	02hewGMWcG5Vp8nwhcmTA0		19	219267	0			1994	0.540570175438596	0.796166320249822	4	0.744729046872277	1	0.083743303815459	0.008341708542714	0.0	0.198966408268734	0.143066151112969	93.824	4	0.1389102753923601	0.678571428571429	0.48124806178286	0.583333333333333	0.0	21.3180307423213
28	02yg6Eksd8zZpYxsT1Lpsp		29	104213	0			1999-01-01	0.481359649122807	0.820264883372996	7	0.85223906603938	1	0.024160927079917	0.006532663316583	1.40225179119754e-06	0.044341085271318	0.923711986623472	184.254	4	0.053735564110156946	0.738095238095238	0.933645448816062	0.652777777777778	0.0	25.7209218589767
29	03UV1fQG0MIm4CPszfttTJ		35	183834	0			2006-06-26	0.658991228070175	0.653583155104377	4	0.906205660518259	0	0.030173827484421	0.029346733668342	0.0	0.06687338501292	0.495245062179956	125.991	4	0.11267915309446254	0.821428571428571	0.554095015155608	0.611111111111111	0.0	35.0794305188842
30	04GlMx0s7MvdMr2MEXX8s8		34	255840	0			2008-04-18	0.518640350877193	0.872478436806539	1	0.748338436263162	1	0.01486826281841	0.000201005025126	0.163766632548618	0.046925064599483	0.876685129062598	155.975	4	0.16598534201954399	0.845238095238095	0.57676340245891	0.583333333333333	0.0	35.0806689337132
31	04PxH7CFGAaAvo6j0zZAOr		42	229407	0			2014-11-07	0.706140350877193	0.771063650329849	1	0.903940457521221	0	0.047337925002733	0.02894472361809	0.0	0.293023255813953	0.525551259274741	100.031	4	0.14641693811074918	0.916666666666667	0.15878875183818	0.625	0.222222222222222	40.8800274671513
32	04PxH7CFGAaAvo6j0zZAOr		42	229407	0			2014-11-07	0.706140350877193	0.771063650329849	1	0.903940457521221	0	0.047337925002733	0.02894472361809	0.0	0.293023255813953	0.525551259274741	100.031	4	0.14641693811074918	0.916666666666667	0.655912687694447	0.694444444444444	0.222222222222222	40.8800274671513
33	04QM4iqGabg7QlphUoWiMB		26	267408	0			1995-05-08	0.583333333333333	0.870470223212941	1	0.880790580738307	0	0.046572646769433	0.120603015075377	7.05220061412487e-05	0.309560723514212	0.60183927265127	91.57	4	0.1745491560556707	0.69047619047619	0.524113922151196	0.652777777777778	0.0	25.4977094719485
34	04izaHyfYxsE3AQ68kgB4I		24	145827	0			2000-04-20	0.724780701754386	0.77005954353305	9	0.880367410947651	0	0.165846725702416	0.178894472361809	0.0	0.07328165374677	0.620650015675619	131.909	4	0.08454249333728162	0.75	0.008092994407931	0.333333333333333	0.0	34.4777416980164
35	04kSSveBKXiNeRaOXu2iog		43	229933	0			1999-06-05	0.671052631578947	0.436696086995813	0	0.771239389639808	0	0.01421231004701	0.299497487437186	0.0	0.170025839793282	0.197408297627756	105.421	4	0.1468063369854901	0.738095238095238	0.500745275752028	0.763888888888889	0.111111111111111	33.8012231517393
36	04wTGEh2gJTG72yUoizwFV		10	205693	0			1980	0.455043859649123	0.85340040766736	10	0.862619172080751	1	0.034546845960424	0.000100502512563	0.00331627430910952	0.306459948320413	0.493154979621695	112.037	4	0.12886141545750665	0.511904761904762	0.57676340245891	0.583333333333333	0.0	14.610474284499
37	053zfKbxSoqRm2RCdJdQMS		28	201400	0			1997-01-01	0.394736842105263	0.958831621331245	2	0.891768102954721	1	0.026456761779819	0.001005025125628	0.0206755373592631	0.097674418604651	0.590343818580834	166.193	4	0.12568329878590465	0.714285714285714	0.134369716795214	0.430555555555556	0.0	24.4145764534479
38	053zgCcuWJ9x4dJJgaF4VS		10	509704	0			1982	0.135964912280702	0.199726882951271	0	0.608592835984368	1	0.015961517437411	0.78894472361809	0.410440122824974	0.198966408268734	0.011495454070436	135.568	4	0.3539213799230086	0.535714285714286	0.092204115522743	0.402777777777778	0.0	11.773544254012
39	054VUAvYt59A1yGSrBAgac		22	203213	0			1976-02-12	0.523026315789474	0.486901426835758	5	0.794737759191497	1	0.030611129332021	0.733668341708543	0.0277379733879222	0.112144702842377	0.818162817431289	147.668	4	0.12702546639028722	0.464285714285714	0.38044076308234	0.583333333333333	0.0	21.6044790501583
40	05IAxc7j9T9KcEu0lAgbfB		46	216013	0			2016-01-03	0.789473684210526	0.928708417427278	11	0.957458989868817	1	0.079260959877555	0.125628140703518	0.0	0.190697674418605	0.982234298254781	133.933	4	0.13650133254367783	0.94047619047619	0.107039604653722	0.583333333333333	0.0	45.0804559629553
41	05JJTD8KdxbZsMnOn6imMv		42	230853	0			2000	0.662280701754386	0.910634495084897	7	0.923605406616384	0	0.126489559418389	0.079597989949749	0.00030603889457523	0.102842377260982	0.84010868429303	168.032	4	0.14748741486526504	0.75	0.196562728209437	0.583333333333333	0.0	34.869262824777
42	05RcrI18Qi65dwc3vegSNP		43	216000	0			2017-10-27	0.633771929824561	0.882519504774528	0	0.904886366465039	1	0.108997485514376	0.087638190954774	0.0	0.300258397932817	0.638415717420838	79.994	4	0.1364917086171158	0.952380952380952	0.45162710202775	0.652777777777778	0.111111111111111	45.0804559629553
43	05hJSmfNiPXSzwSLeG1Rfb		39	232507	0			2016-10-21	0.416666666666667	0.425650912231025	6	0.866203669131008	0	0.011588498961408	0.826130653266332	0.00408393039918117	0.101808785529716	0.476434319155607	99.842	4	0.14871187444477346	0.94047619047619	0.061802867060813	0.5	0.0	45.0804559629553
44	05hk8ivnCt0HP3aI1uWJfi		43	295067	0			2015-03-06	0.589912280701754	0.763030795955458	2	0.819306499390138	1	0.017820050289712	0.539698492462312	0.0	0.318863049095607	0.684397533702581	106.972	4	0.19502517026946994	0.928571428571429	0.324580094635015	0.555555555555556	0.0	40.8800274671513
45	05v1D17vjtCNRyalBVGbKU		34	481160	0			2007-06-10	0.407894736842105	0.785121145485034	11	0.772334652627387	0	0.040559746364928	0.833165829145729	0.0509723643807574	0.979328165374677	0.337443829031247	108.142	4	0.3327901984009476	0.833333333333333	0.448856076748397	0.597222222222222	0.0	33.6127140914514
46	05yf5sbF36dhwGRfj18sg5		27	225647	0			2007-11-19	0.934210526315789	0.690735106585936	7	0.873571801956538	1	0.188805072701432	0.109547738693467	0.0	0.248578811369509	0.730379349984324	92.971	4	0.1436334024281907	0.833333333333333	0.204745755929694	0.486111111111111	0.0	34.6960824675478
47	062pHiX7lpKRDmFvmJor3o		38	182813	0			2006	0.50219298245614	0.993975359219207	2	0.905433997958828	1	0.088225647753362	0.001206030150754	1.19754350051177e-06	0.325064599483204	0.398056223220817	146.553	4	0.111923304708321	0.821428571428571	0.428858677710753	0.597222222222222	0.0	35.0794305188842
48	063FiQaY2FuZe3a44rZuSX		37	221187	0			2012-08-08	0.780701754385965	0.70579670853792	0	0.901276977074154	1	0.022849021537116	0.194974874371859	1.69907881269191e-06	0.164857881136951	0.729334308705194	95.008	4	0.14033165531536868	0.892857142857143	0.11214149235217	0.444444444444444	0.0	40.4301647912923
49	063ZobFSzYA0O7fmfi4oik		42	212689	0			2002-03-12	0.775219298245614	0.952806980550451	10	0.919523062753591	1	0.02908057286542	0.152763819095477	0.0	0.357105943152455	0.849514055805204	99.986	4	0.1340405685519692	0.773809523809524	0.515640787091224	0.680555555555556	0.0	34.869262824777
50	06AIE9kwssJ3cmPbMuHobb		30	323133	0			2007-05-11	0.084429824561404	0.91766324266249	7	0.897169740870734	1	0.038045260741227	0.002311557788945	7.93244626407369e-05	0.11421188630491	0.38447068659212	166.21	4	0.21580248741486527	0.833333333333333	0.344017286395967	0.555555555555556	0.0	35.0794305188842
51	06K6svVGbiQILn9BAzZNgo		37	374120	1			2013-12-13	0.607456140350877	0.763030795955458	0	0.803499863092127	0	0.050727014321636	0.014974874371859	1.45342886386899e-06	0.110077519379845	0.335353746472986	133.992	4	0.2535482676932188	0.904761904761905	0.031331592689295	0.597222222222222	0.111111111111111	40.4301647912923
52	06LDaZRAWOc0fNqkdOQo9S		18	77853	0			1998	0.769736842105263	0.454770009338193	10	0.752321210763448	1	0.01399365912321	0.301507537688442	0.814738996929376	0.055917312661499	0.471209112759954	147.901	4	0.03422120225051821	0.726190476190476	0.706461390714564	0.638888888888889	0.0	22.8083311858699
53	06SnvuakcuOcj3odFZpIpJ		18	137639	0			1969-09-06	0.642543859649123	0.5401190870661	2	0.785602270181465	0	0.008527386028206	0.072663316582915	0.0	0.11937984496124	0.804577280802592	94.858	4	0.07848090020728457	0.380952380952381	0.071516461090604	0.402777777777778	0.0	12.3261274758088
54	06V1fPvTfXdIBMUItnLmQ7		14	119493	0			1967-10-16	0.413377192982456	0.332268980128726	5	0.634107485126826	0	0.01388433366131	0.765829145728643	0.000541453428863869	0.09250645994832	0.326993416239941	79.078	3	0.06504737933076696	0.357142857142857	0.108510148755039	0.486111111111111	0.0	13.2248451239649
55	06kPUkFJv1RfEVjgEF3oRz		23	250227	0			1997-01-01	0.757675438596491	0.735919912441887	0	0.776964627983969	1	0.013228380889909	0.008844221105528	0.0224155578300921	0.089405684754522	0.863099592433901	129.879	4	0.16183002665087357	0.714285714285714	0.319238118103698	0.652777777777778	0.0	24.7301600717939
56	07Iyx8PziAv997SrJanQgl		11	279013	0			1981	0.330043859649123	0.138476368346537	10	0.526298757872203	0	0.009511315185307	0.355778894472362	8.86386898669396e-06	0.21343669250646	0.069077228550528	97.964	4	0.1831403612673971	0.523809523809524	0.209407480767884	0.583333333333333	0.0	12.780501473184
57	07QfWWYlJsbXiEbbKSvqcd		34	320993	1			2010-04-26	0.50109649122807	0.957827514534446	10	0.886167326313693	0	0.292664261506505	0.013969849246231	7.21596724667349e-06	0.363307493540052	0.664541749399101	84.011	4	0.21421824104234527	0.869047619047619	0.100137050708762	0.708333333333333	0.222222222222222	35.0794305188842
58	07QfWWYlJsbXiEbbKSvqcd		34	320993	1			2010-04-26	0.50109649122807	0.957827514534446	10	0.886167326313693	0	0.292664261506505	0.013969849246231	7.21596724667349e-06	0.363307493540052	0.664541749399101	84.011	4	0.21421824104234527	0.869047619047619	0.07381731240559	0.541666666666667	0.222222222222222	35.0794305188842
59	07rj0QlIw4hRcvhIJGY2Lf		6	123173	0			1965-10-12	0.541666666666667	0.187677601389684	0	0.651507231224952	0	0.027550016398819	0.97286432160804	8.84339815762538e-06	0.11937984496124	0.282056641237329	67.152	4	0.06777169084986674	0.333333333333333	0.031211548272861	0.430555555555556	0.0	8.58681052013858
60	0804d5chEAcksTIZ7IVh6O		29	200000	0			1967-10-16	0.296052631578947	0.438704300589411	9	0.693973564334254	0	0.025363507160818	0.879396984924623	5.03582395087001e-05	0.509043927648579	0.339533911589508	101.975	4	0.12464687592537756	0.357142857142857	0.108510148755039	0.486111111111111	0.0	13.190075690601
61	08D5BLpRFI6PT2XYuq7eDg		28	273267	0			1996-01-01	0.697368421052632	0.940757698988864	5	0.865307544868444	0	0.007652782333005	0.509547738693467	5.496417604913e-06	0.243410852713178	0.985369422092173	137.921	4	0.1788865857269766	0.702380952380952	0.128327481168032	0.5	0.0	24.4145764534479
62	08D6EqghkRLD6iFHKKWJ4Y		55	213120	1			2020-01-27	0.765350877192982	0.68772278619554	0	0.808677469942499	0	0.036733355198426	0.301507537688442	0.0269191402251791	0.108010335917313	0.46389382380604	125.018	4	0.1343596387326029	0.988095238095238	0.402879065254144	0.861111111111111	0.111111111111111	55.9854708194548
63	08ER472xnuMlbtkKSh3PB4		11	166867	0			1975-09-07	0.303728070175439	0.289092387866373	10	0.664451248350882	1	0.07149885208265	0.934673366834171	0.0	0.096640826873385	0.402236388337339	178.159	4	0.10011844832691738	0.452380952380952	0.1218750937847	0.472222222222222	0.0	11.6068359628288
64	08ULDx4KeBpXe7OHEWjgVP		45	277293	0			2014-04-16	0.6875	0.931720737817674	3	0.918079306997237	1	0.017492073904012	0.014773869346734	0.00118730808597748	0.325064599483204	0.970738844184345	153.068	4	0.18186704175303522	0.916666666666667	0.030641337294799	0.513888888888889	0.0	40.8800274671513
65	08uPk6Ge77W31WGGYNEPHA		2	156369	0			1978-01-01	0.448464912280702	0.547147834643693	5	0.807955592064322	1	0.318902372362523	0.784924623115578	1.31013306038895e-06	0.090439276485788	0.736649597659108	92.544	4	0.09234675747705064	0.488095238095238	0.050598721526965	0.583333333333333	0.111111111111111	10.4222738664969
66	08uPk6Ge77W31WGGYNEPHA		2	156369	0			1978-01-01	0.448464912280702	0.547147834643693	5	0.807955592064322	1	0.318902372362523	0.784924623115578	1.31013306038895e-06	0.090439276485788	0.736649597659108	92.544	4	0.09234675747705064	0.488095238095238	0.0	0.013888888888889	0.111111111111111	2.86773809523809
67	08xY8SSfwHc7SIXWroGW9j		16	167360	0			1994	0.289473684210526	0.244911688807222	2	0.671894058198292	0	0.017273422980212	0.660301507537688	0.0	0.094573643410853	0.584073570906051	124.354	4	0.10048341723423157	0.678571428571429	0.362624170943249	0.75	0.0	22.0225455927807
68	095PNli2gmoYwlvgotxxEs		28	269067	0			1995	0.565789473684211	0.616431203622817	7	0.900007467702188	0	0.005138296709304	0.055175879396985	6.16171954964176e-06	0.083204134366925	0.120075242972097	119.804	4	0.17577731714539532	0.69047619047619	0.772325760531397	0.736111111111111	0.0	25.4977094719485
69	09FEC2H0Ls5bXMSFj2bLzL		50	198460	0			2018-10-24	0.710526315789474	0.808215601811409	0	0.828840265850198	1	0.21394992893845	0.299497487437186	0.000127942681678608	0.103875968992248	0.925802069181733	169.848	4	0.12350681077879774	0.964285714285714	0.74934725848564	0.902777777777778	0.222222222222222	45.0804559629553
70	09GKW7x0iMuxzzM7IeQ01g		7	153521	0			1964-02-21	0.505482456140351	0.207759737325662	0	0.707166504866453	1	0.011588498961408	0.739698492462312	0.0	0.177260981912145	0.540181837182569	82.018	4	0.09023837725792123	0.321428571428571	0.030581315086582	0.430555555555556	0.0	5.49629465913261
71	09PwmLzRfWj5hkmIiw1a9X		21	251316	0			1969-09-07	0.288377192982456	0.38448253356227	0	0.798347148582381	1	0.008746036952006	0.717587939698492	8.08597748208802e-06	0.374677002583979	0.36356986100951	119.002	4	0.16263621557595498	0.380952380952381	0.1218750937847	0.472222222222222	0.0	13.190075690601
72	09d4etyrEWf5BguIIUfRW9		12	212523	0			1974-08-16	0.271929824561404	0.428663232621422	9	0.811415627411445	0	0.009511315185307	0.436180904522613	0.0	0.126614987080103	0.295642177866026	83.695	4	0.13391767841279242	0.44047619047619	0.129657973450177	0.5	0.0	13.7043253144824
73	0ADOK8ffxUlSDu51nIu5KU		23	274533	0			1999	0.660087719298246	0.298129349037564	1	0.748910960097578	0	0.004591669399803	0.412060301507538	0.0	0.359173126614987	0.545407043578221	103.037	4	0.1798238081137104	0.738095238095238	0.61224653121655	0.625	0.0	25.7209218589767
74	0AGuYcLlnbR7AOQSbTGzIz		29	232960	0			2000-12-31	0.424342105263158	0.573254611360464	2	0.805590819704777	0	0.007652782333005	0.080301507537688	0.000113613101330604	0.284754521963824	0.554812415090396	201.077	4	0.1490472312703583	0.75	0.150115542750818	0.555555555555556	0.0	34.09439364668
75	0AHy3nZXS725AAHDC0qjPR		22	202336	0			1966-09-28	0.462719298245614	0.362392184032694	9	0.757100540163792	1	0.015742866513611	0.882412060301508	0.0	0.302325581395349	0.353119448218205	117.999	4	0.12637622149837133	0.345238095238095	0.15351680121645	0.486111111111111	0.0	13.190075690601
76	0AJJJnR5VwJfeYGvnyG74v		7	191530	0			1967-03-29	0.62719298245614	0.302145776224759	7	0.677071665048665	0	0.004263693014103	0.777889447236181	0.00222108495394063	0.089405684754522	0.389695892987773	104.859	4	0.11837651761918863	0.357142857142857	0.693636645558857	0.638888888888889	0.0	15.3641314115338
77	0ATVUbPlX65HB5H1WbIugt		54	181356	1			2020-12-04	0.911184210526316	0.900593427116908	7	0.783212605481293	1	0.289384497649503	0.185929648241206	0.0	0.064392764857881	0.895495872086947	154.912	4	0.11084468463132958	0.988095238095238	0.277082520532597	0.75	0.0	51.9031221393765
78	0AUeyoLQYwYUDsQRJZKvZd		22	248907	0			1980	0.399122807017544	0.82528541735699	4	0.881313319891469	1	0.021974417841915	0.000502512562814	4.4114636642784e-05	0.121447028423773	0.409551677291253	195.99	4	0.16085282795380515	0.511904761904762	0.57676340245891	0.583333333333333	0.0	14.9059819728965
79	0AXtdoJaVk6W8pcYstecgw		33	193227	0			2000	0.586622807017544	0.80721149501461	2	0.853334329026958	1	0.022849021537116	0.2	8.20880245649949e-05	0.005064599483204	0.717838854634758	120.172	4	0.11963281018655611	0.75	0.448856076748397	0.597222222222222	0.0	34.09439364668
80	0AYmWvKRGtgePrpo3NdYXh		48	218530	0			2019-02-01	0.637061403508772	0.381470213171873	4	0.752868842257237	1	0.007215480485405	0.94070351758794	0.447287615148414	0.094573643410853	0.215173999372975	112.996	4	0.1383646727864969	0.976190476190476	0.901893700669248	0.847222222222222	0.0	42.9220527846565
81	0ApseFDjbipbKK0dBD4Viz		35	267013	1			2005-11-15	0.851973684210526	0.697763854163529	9	0.881213750528962	0	0.295944025363507	0.077989949748744	0.0	0.142118863049096	0.537046713345177	90.011	4	0.17425673674859343	0.80952380952381	0.809619559236918	0.638888888888889	0.0	34.8690265263156
82	0Aq0weZZ0YzbrNlsA8UV3P		23	294107	0			1997-01-05	0.559210526315789	0.596349067686839	7	0.814203569561646	1	0.004154367552203	0.015778894472362	0.000338792221084954	0.1328165374677	0.188002926115582	140.843	4	0.19431448030796564	0.714285714285714	0.005081880295709	0.166666666666667	0.0	24.4145764534479
83	0Au3WxSl0fm6IvSZHCShjZ		22	157187	0			1998-01-01	0.486842105263158	0.924691990240082	4	0.865432006571578	0	0.019897234065814	0.014170854271357	0.0179119754350051	0.140051679586563	0.639460758699969	139.08	4	0.09295232454841575	0.726190476190476	0.291177735762232	0.555555555555556	0.0	24.637788840476
84	0AwC0oZxr25SOcThearmN4		28	300383	0			2005-04-29	0.950657894736842	0.70579670853792	7	0.871978692156423	1	0.037607958893626	0.01356783919598	0.0	0.062015503875969	0.750235134287804	99.024	4	0.19896061593129996	0.80952380952381	0.204745755929694	0.486111111111111	0.0	34.4856784749792
85	0B1uuoyiPSkV2f8cAw473e		0	230773	0			1946-01-01	0.226973684210526	0.191694028576879	6	0.599556916336843	1	0.059910353121242	0.995979899497487	0.9007164790174	0.180361757105943	0.329083498798203	79.244	1	0.14742819070180632	0.107142857142857	0.01804667727059	0.402777777777778	0.444444444444444	1.87941937277902
86	0B1uuoyiPSkV2f8cAw473e		0	230773	0			1946-01-01	0.226973684210526	0.191694028576879	6	0.599556916336843	1	0.059910353121242	0.995979899497487	0.9007164790174	0.180361757105943	0.329083498798203	79.244	1	0.14742819070180632	0.107142857142857	0.000110040715065	0.111111111111111	0.444444444444444	0.47034210575268
87	0B1uuoyiPSkV2f8cAw473e		0	230773	0			1946-01-01	0.226973684210526	0.191694028576879	6	0.599556916336843	1	0.059910353121242	0.995979899497487	0.9007164790174	0.180361757105943	0.329083498798203	79.244	1	0.14742819070180632	0.107142857142857	0.0	0.069444444444444	0.444444444444444	0.47034210575268
88	0B1uuoyiPSkV2f8cAw473e		0	230773	0			1946-01-01	0.226973684210526	0.191694028576879	6	0.599556916336843	1	0.059910353121242	0.995979899497487	0.9007164790174	0.180361757105943	0.329083498798203	79.244	1	0.14742819070180632	0.107142857142857	0.026709882656583	0.652777777777778	0.444444444444444	1.87941937277902
89	0B1uuoyiPSkV2f8cAw473e		0	230773	0			1946-01-01	0.226973684210526	0.191694028576879	6	0.599556916336843	1	0.059910353121242	0.995979899497487	0.9007164790174	0.180361757105943	0.329083498798203	79.244	1	0.14742819070180632	0.107142857142857	0.44250372637876	0.597222222222222	0.444444444444444	1.3571971505568
90	0BHIwbxTN0iSClgDaO36Ft		34	293882	0			2009-02-21	0.679824561403509	0.869466116416142	9	0.966967863988251	0	0.426041325024598	0.349748743718593	0.0	0.192764857881137	0.823388023826941	173.874	4	0.19414791234823808	0.857142857142857	0.522553344737553	0.694444444444444	0.222222222222222	35.4709516456447
91	0BHIwbxTN0iSClgDaO36Ft		34	293882	0			2009-02-21	0.679824561403509	0.869466116416142	9	0.966967863988251	0	0.426041325024598	0.349748743718593	0.0	0.192764857881137	0.823388023826941	173.874	4	0.19414791234823808	0.857142857142857	0.229124776167182	0.513888888888889	0.222222222222222	35.4709516456447
92	0BleuvVMt39PmQaukAdEcv		37	228053	0			2010	0.389254385964912	0.50397124238134	5	0.812535782739651	1	0.006450202252105	0.625125628140704	1.8014329580348e-05	0.108010335917313	0.367750026126032	156.074	4	0.14541456914421083	0.869047619047619	0.38512249532327	0.583333333333333	0.0	33.9973005576168
93	0Bn854XtnJv15m9f1ZvWbe		44	194013	0			2011-05-31	0.471491228070175	0.881515397977729	6	0.912503422696836	0	0.023395648846616	0.300502512562814	0.0	0.086304909560724	0.614379768000836	160.015	4	0.12021468759253776	0.880952380952381	0.36705581064994	0.680555555555556	0.0	40.4301647912923
94	0BsQm2iFzKTzAemliJtu8x		39	201231	0			2016-11-25	0.548245614035088	0.792149893062626	4	0.870659398103204	1	0.007652782333005	0.005427135678392	0.0	0.07266149870801	0.599749190093009	129.992	4	0.12555818774059815	0.94047619047619	0.367315906885548	0.625	0.0	45.0804559629553
95	0C2VNcmHdRRvQtetkaZVn5		32	311440	0			1990	0.591008771929825	0.394523601530259	1	0.671918950538919	0	0.002733136547502	0.671356783919598	3.85875127942682e-05	0.103875968992248	0.336398787752116	102.03	4	0.2071461356233343	0.630952380952381	0.772325760531397	0.736111111111111	0.0	26.8362368045648
96	0C2XO15BhHDglDB724brOi		43	316627	0			1998	0.855263157894737	0.541123193862899	10	0.728474348442984	0	0.204110637367443	0.095577889447236	1.22824974411464e-05	0.093540051679587	0.679172327306929	80.988	4	0.2109860823215872	0.726190476190476	0.706461390714564	0.638888888888889	0.0	26.0365054773228
97	0CAinfqSGq1peA63s4gNam		21	187933	0			1988-12-31	0.628289473684211	0.297125242240765	10	0.761879869564135	1	0.010057942494807	0.893467336683417	0.0	0.07328165374677	0.585118612185181	116.335	3	0.11571365116967723	0.607142857142857	0.295509338455228	0.597222222222222	0.0	21.0755577778261
98	0CBj18X3J7MabwDqKeMxuo		47	239373	0			1999	0.355263157894737	0.769055436736251	0	0.876185497722351	1	0.106810976276375	0.124623115577889	0.0	0.054470284237726	0.74082976277563	119.973	4	0.15379478827361565	0.738095238095238	0.357312205516041	0.652777777777778	0.0	24.637788840476
99	0CLO6b6Ohrnmp9fDLvYj3U		25	162987	0			2003	0.235745614035088	0.339297727706319	8	0.693625071565479	0	0.011588498961408	0.986934673366834	0.0	0.212403100775194	0.251750444142544	91.165	4	0.09724607639917086	0.785714285714286	0.030951452037254	0.541666666666667	0.111111111111111	31.9652849415299
100	0CMxaedU31OgxQvrA1VE1J		54	225867	0			2019-10-25	0.879385964912281	0.615427096826018	3	0.903119010280537	0	0.223789220509457	0.240201005025126	0.0	0.066356589147287	0.399101264499948	114.959	4	0.1437962688777021	0.976190476190476	0.350179566439583	0.680555555555556	0.111111111111111	52.5464341434452

Rows: 1-100 | Columns: 26

View the regression report and the importance of each feature.

In [48]:

rf_model.regression_report()

Out[48]:

	value
explained_variance	0.856500452337576
max_error	35.5696078431373
median_absolute_error	3.91965797527483
mean_absolute_error	4.76133210498922
mean_squared_error	39.2157935438824
root_mean_squared_error	6.262251475618206
r2	0.856498475694773
r2_adj	0.8560006054476506
aic	13827.520352964559
bic	13914.66239558891

Rows: 1-10 | Columns: 2

In [49]:

rf_model.features_importance()

Out[49]:

	importance	sign
release_year	90.24	1
artist_popularity	5.21	1
instrumentalness	1.98	1
artists_followers	0.54	1
loudness	0.54	1
acousticness	0.31	1
energy	0.3	1
speechiness	0.24	1
duration_minute	0.21	1
nb_singers	0.13	1
valence	0.13	1
danceability	0.1	1
liveness	0.09	1

Rows: 1-13 | Columns: 3

To see how our model performs, let's plot the popularity and estimated popularity of songs by other Polish artists like Brodka, Akcent, and Maanam.

In [51]:

# results for Brodka
polish_tracks.search("LOWER(artists) LIKE '%brodka%'",
                     usecols = ['popularity', 'name', 'estimated_popularity']).plot(
                               ts='name', columns=['popularity', 'estimated_popularity'])

Out[51]:

<AxesSubplot:xlabel='"name"'>

In [52]:

# results for Brodka
polish_tracks.search("LOWER(artists) LIKE '%akcent%'",
                     usecols = ['popularity', 'name', 'estimated_popularity']).plot(
                               ts='name', columns=['popularity', 'estimated_popularity'])

Out[52]:

<AxesSubplot:xlabel='"name"'>

Group Artists using Track Features¶

While our tracks don't have an explicit "genre" feature, we can approximate the effect by grouping artists based on their tracks' numerical features.

Let's start by taking the averages of these numerical features for each artist.

In [55]:

# group by artist
artists_features = polish_tracks.groupby(['id_artists',
                                          'artists'], expr=['AVG(danceability) AS danceability',
                                                            'AVG(energy) AS energy', 
                                                            'AVG(speechiness) AS speechiness', 
                                                            'AVG(acousticness) AS acousticness', 
                                                            'AVG(instrumentalness) AS instrumentalness',
                                                            'AVG(valence) AS valence', 
                                                            'AVG(liveness) AS liveness'])

# save relation to the database as "artists_features"
artists_features.to_db('"spotify"."artists_features"')

Out[55]:

	Abc Varchar(156)	Abc Varchar(100)	123 danceability Float	123 energy Float	123 speechiness Float	123 acousticness Float	123 instrumentalness Float	123 valence Float	123 liveness Float
1			0.667763157894737	0.779096504704241	0.309063080791516	0.111608040201005	0.000926305015353122	0.698505590970843	0.145271317829457
2			0.115131578947368	0.226837766464841	0.0139389963922595	0.978391959798995	0.0	0.0909708433483125	0.157105943152454
3			0.726973684210527	0.584801839523652	0.0137750081994095	0.199899497487437	0.0214651995905834	0.832270874699551	0.048372093023256
4			0.6875	0.661616009478768	0.076199846944353	0.108542713567839	1.97543500511771e-06	0.834883477897377	0.083100775193798
5			0.672149122807018	0.734915805645088	0.011588498961408	0.462311557788945	0.0	0.793081826732156	0.10077519379845
6			0.419557416267943	0.636878469303086	0.0352326147668883	0.465746916400183	0.00257370428956918	0.565262827881701	0.184947145877378
7			0.555921052631579	0.925696097036881	0.113370503990379	0.007336683417085	0.0	0.724109102309541	0.089405684754522
8			0.747807017543859	0.868964063017742	0.0984475784410185	0.164321608040201	0.000573183213920164	0.618559933117358	0.14687338501292
9			0.373903508771929	0.351347009267906	0.031212419372472	0.652763819095478	0.0	0.559515100846483	0.572093023255814
10			0.551535087719298	0.597353174483638	0.2850114791735	0.163819095477387	0.0	0.336398787752116	0.561757105943152
11			0.62390350877193	0.841351126105773	0.003717065704603	0.000402010050251	0.0583418628454452	0.351029365659944	0.67235142118863
12			0.680921052631579	0.881515397977729	0.168033234940418	0.34572864321608	0.0	0.454488452293866	0.160723514211886
13			0.639254385964912	0.594340854093241	0.064283371597245	0.776884422110553	0.0	0.890270665691295	0.1328165374677
14			0.698464912280702	0.546143727846894	0.024379578003717	0.375879396984925	0.0	0.890270665691295	0.236175710594315
15			0.539473684210527	0.472341878282174	0.0135563572756095	0.291557788944724	0.0	0.440902915665169	0.246511627906977
16			0.779605263157895	0.684710465805143	0.277358696840494	0.461306532663317	0.0	0.64259588253736	0.257881136950904
17			0.400219298245614	0.679689931821148	0.529900513829671	0.673366834170854	0.0	0.674992162190407	0.093540051679587
18			0.677631578947368	0.649566727917181	0.173499508035421	0.05678391959799	1.48413510747185e-06	0.430452502873864	0.037416020671835
19			0.836622807017544	0.865449689228946	0.0496337597026347	0.111490787269682	0.00132118389628113	0.948792977322604	0.144289405684755
20			0.701754385964912	0.75198562119067	0.033016289493823	0.026130653266332	0.000231320368474923	0.866234716271293	0.2671834625323
21			0.605263157894737	0.710817242521915	0.177872526511425	0.037286432160804	0.0	0.750235134287804	0.01922480620155
22			0.823464912280702	0.607394242451627	0.15710068875041	0.636180904522613	1.18730808597748e-06	0.360434737172118	0.12764857881137
23			0.199805068226121	0.920898697896619	0.0947730281938218	0.000569514237856111	0.130718038212214	0.29709362408704	0.290152167671548
24			0.916666666666667	0.773071863923447	0.093691920848366	0.219095477386935	0.00174002047082907	0.82129794126868	0.341602067183463
25			0.846491228070175	0.667640650259562	0.039247840822127	0.143718592964824	4.82088024564995e-05	0.48374960810952	0.262015503875969
26			0.814912280701754	0.735719091082528	0.281731715316497	0.243678391959799	0.0	0.719719928937193	0.168124031007752
27			0.49780701754386	0.543131407456497	0.121023286323385	0.118592964824121	0.000601842374616172	0.451353328456474	0.11421188630491
28			0.555921052631579	0.573254611360465	0.0108778834590575	0.725628140703518	0.00205552712384852	0.588776256662139	0.143152454780362
29			0.446271929824561	0.651574941510779	0.036077402427025	0.024221105527638	0.000213920163766633	0.692757863935625	0.297157622739018
30			0.452850877192982	0.399544135514253	0.015414890127911	0.87035175879397	3.13203684749232e-05	0.188002926115582	0.106976744186047
31			0.870614035087719	0.740605744160282	0.315987026711855	0.295477386934673	1.37154554759468e-06	0.817117776152158	0.0642549526270457
32			0.756578947368421	0.79817453384342	0.337487700885536	0.569849246231156	0.0	0.726199184867802	0.078449612403101
33			0.553216374269006	0.524254199676678	0.0587442148609745	0.336603015075377	0.000791675196178779	0.660473055352353	0.151138673557278
34			0.792763157894737	0.674669397837154	0.238001530556467	0.097989949748744	0.0	0.357299613334727	0.209302325581395
35			0.510964912280702	0.783112931891436	0.162566961845414	0.014673366834171	0.0	0.210993834256453	0.099741602067183
36			0.770833333333333	0.731903485254692	0.058598447578441	0.357788944723618	0.0	0.785766537778242	0.139018087855297
37			0.565241228070176	0.636513339558795	0.0780037170657045	0.217537688442211	3.11412487205732e-05	0.26324589821298	0.068888888888889
38			0.928728070175439	0.627476378387605	0.163660216464415	0.011758793969849	0.0	0.486884731946912	0.152454780361757
39			0.672149122807018	0.789137572672229	0.092598666229365	0.029346733668342	0.0	0.595569024976487	0.297157622739018
40			0.452282326185835	0.180262086749621	0.030465362049488	0.830411315838452	0.00141472421244172	0.432256166711178	0.186865728777873
41			0.709429824561404	0.681698145414746	0.035530775117525	0.582914572864322	1.6171954964176e-06	0.796216950569547	0.304392764857881
42			0.797149122807018	0.668644757056361	0.148354651798404	0.304522613065327	0.0	0.201588462744278	0.085478036175711
43			0.472861842105263	0.624213031298009	0.010303924784082	0.334572864321608	0.0737005910951894	0.396828299717839	0.243152454780362
44			0.300438596491228	0.767047223142653	0.05706789111184	0.179899497487437	0.0	0.594523983697356	0.198966408268734
45			0.763157894736842	0.578275145344459	0.034546845960424	0.220100502512563	0.436028659160696	0.157696729020796	0.381912144702842
46			0.721491228070175	0.377453785984677	0.01497758828031	0.528643216080402	1.7093142272262e-06	0.185912843557321	0.155555555555556
47			0.845394736842105	0.719854203693104	0.25713348638898	0.215276381909548	2.11873080859775e-06	0.346849200543421	0.0931782945736435
48			0.655701754385965	0.905613961100903	0.072482781239751	0.125628140703518	0.0	0.346849200543421	0.259948320413437
49			0.816885964912281	0.518028737536524	0.32874166393353	0.281407035175879	0.0	0.532866548228655	0.037519379844961
50			0.679824561403509	0.793656053257825	0.0690936919208485	0.160301507537689	5.88536335721597e-06	0.46389382380604	0.20671834625323
51			0.865131578947368	0.809219708608208	0.180059035749426	0.049748743718593	0.0	0.667676873236493	0.335400516795866
52			0.40009746588694	0.870916492900407	0.118982544367917	0.0333221663874931	7.56795177982486e-05	0.601955388348951	0.180361757105943
53			0.506578947368421	0.333273086925525	0.0958237673554175	0.744723618090452	0.000163249744114637	0.432542585432125	0.101602067183462
54			0.607456140350877	0.885531825164925	0.429321088881601	0.280402010050251	0.0	0.607064479046922	0.07266149870801
55			0.793311403508772	0.465815184102982	0.35716628402755	0.217587939698492	0.0	0.774271083707807	0.068423772609819
56			0.408991228070175	0.488909640429356	0.052476221712037	0.152763819095477	0.0221084953940635	0.26533598077124	0.084134366925065
57			0.460526315789474	0.318546187239141	0.02365074159105	0.651591289782245	0.0229375980893893	0.293900442400808	0.184840654608096
58			0.505847953216374	0.565556459251673	0.0214095696220983	0.347403685092127	0.327533265097236	0.664193402306058	0.132575366063738
59			0.855263157894737	0.810223815405006	0.259866622936482	0.146733668341709	0.0	0.913261573832166	0.395348837209302
60			0.650219298245614	0.85340040766736	0.149447906417405	0.287437185929648	0.000356192425793245	0.588253736022573	0.336434108527132
61			0.565058479532164	0.367858987704155	0.0550757382505499	0.78537130094919	1.47617422950074e-06	0.520674399972132	0.208693654895205
62			0.673245614035088	0.587312106515649	0.007106155023505	0.250251256281407	0.0	0.853694220921726	0.185529715762274
63			0.535087719298246	0.716841883302708	0.406362741882584	0.092562814070352	0.0	0.729334308705194	0.06749354005168
64			0.706140350877193	0.771063650329849	0.047337925002733	0.02894472361809	0.0	0.525551259274741	0.293023255813953
65			0.725877192982456	0.951802873753652	0.035968076965125	0.064221105527638	0.000466734902763562	0.932072316856516	0.034108527131783
66			0.600328947368421	0.774578024118646	0.0233956488466165	0.075778894472362	3.03480040941658e-06	0.430452502873863	0.117829457364341
67			0.837719298245614	0.635509232761997	0.335301191647535	0.260301507537688	0.0	0.82025289998955	0.287855297157623
68			0.504385964912281	0.6324969123716	0.019459932218214	0.405025125628141	0.0	0.395966140662556	0.10594315245478
69			0.492324561403509	0.839342912512175	0.234721766699464	0.048241206030151	9.58034800409417e-06	0.255930609259066	0.706459948320413
70			0.573464912280702	0.699772067757127	0.149447906417405	0.143718592964824	0.0	0.82129794126868	0.171059431524548
71			0.867324561403509	0.677681718227551	0.16730439852775	0.140837520938023	0.0	0.484446302295607	0.291645133505599
72			0.554093567251462	0.478199167930168	0.0127181954010423	0.590519262981574	0.217748208802457	0.478872748806911	0.132540913006029
73			0.700657894736842	0.801688907632215	0.327101782005029	0.411055276381909	0.0	0.788901661615633	0.435658914728682
74			0.672697368421053	0.491252556288553	0.0116978244233082	0.770016750418761	0.238420677243262	0.351551886299509	0.0921274763135228
75			0.577850877192982	0.691739213382735	0.012244451732809	0.523618090452261	6.16171954964176e-06	0.770090918591284	0.254780361757106
76			0.706140350877193	0.681698145414746	0.301410298458511	0.535678391959799	0.0410440122824974	0.82129794126868	0.079896640826873
77			0.617690058479532	0.596125932843106	0.0119164753471083	0.242367392518146	0.00912620948481747	0.714587615099685	0.098409417169107
78			0.822368421052632	0.521041057926921	0.190991581939434	0.354773869346734	0.0	0.461803741247779	0.301291989664083
79			0.776315789473684	0.378457892781476	0.022849021537116	0.682412060301508	0.0386898669396111	0.825478106385202	0.091472868217054
80			0.649122807017544	0.828297737747387	0.007434131409205	0.056884422110553	0.0	0.92057686278608	0.061808785529716
81			0.546491228070175	0.878904720306052	0.0458729638132722	0.035396984924623	2.38894575230297e-06	0.716375796843975	0.179555555555556
82			0.606725146198831	0.950798766956853	0.112787434860246	0.0974204355108877	1.79119754350051e-06	0.61577315637301	0.195176571920758
83			0.633771929824561	0.510999989958932	0.368098830217558	0.252261306532663	0.0	0.52346117671648	0.126614987080103
84			0.533333333333333	0.381871855890593	0.008702306767246	0.701507537688442	1.82599795291709e-05	0.60664646253527	0.145012919896641
85			0.81140350877193	0.582291572531654	0.422761561167596	0.14572864321608	4.28863868986694e-06	0.668721914515623	0.236175710594315
86			0.411105889724311	0.300395761521767	0.0240698225283335	0.783847810480976	0.0098786628162012	0.462873664462127	0.202726713424388
87			0.641447368421053	0.552168368627687	0.244561058270471	0.552763819095477	0.0	0.231894659839064	0.086821705426357
88			0.614035087719298	0.727485415348776	0.0103421886957474	0.315497487437186	0.00243379733879222	0.91576967290208	0.214346253229974
89			0.210526315789474	0.32925665973833	0.024270252541817	0.761809045226131	0.000554759467758444	0.127390531926011	0.10594315245478
90			0.245248538011696	0.023706961472422	0.0203709777340477	0.997654941373534	0.931422722620266	0.042777023025743	0.0833074935400513
91			0.841008771929825	0.952806980550451	0.048649830545534	0.022010050251256	0.0	0.912216532553036	0.011886304909561
92			0.780701754385965	0.70579670853792	0.022849021537116	0.194974874371859	1.69907881269191e-06	0.729334308705194	0.164857881136951
93			0.768640350877193	0.896576999929713	0.136328850989395	0.055979899497487	0.0	0.684397533702581	0.097674418604651
94			0.723684210526316	0.802190961030615	0.194271345796436	0.076884422110553	0.0	0.620650015675619	0.049819121447028
95			0.794956140350877	0.827293630950588	0.321088881600525	0.099798994974874	0.0	0.521371094158219	0.104909560723514
96			0.893092105263158	0.601871655069233	0.37192522138406	0.221105527638191	0.0	0.896018392726513	0.145839793281654
97			0.803728070175439	0.59534496089004	0.207390401224445	0.558793969849246	0.0	0.735604556379977	0.096640826873385
98			0.717379385964912	0.697450070789529	0.272644036296053	0.186438442211055	0.00937530962128966	0.657683666004807	0.218695090439276
99			0.898026315789474	0.545139621050095	0.574723953208702	0.105527638190955	2.80450358239509e-06	0.296687219145156	0.102842377260982
100			0.615131578947368	0.498950708397345	0.016836121132612	0.789949748743719	2.58955987717503e-06	0.842198766851291	0.09250645994832

Rows: 1-100 | Columns: 9

Grouping means clustering, so we use an elbow curve to find a suitable number of clusters.

In [56]:

from verticapy.learn.model_selection import elbow

# define numerical features
preds = ["danceability",
         "energy",
         "speechiness",
         "acousticness",
         "instrumentalness",
         "liveness",
         "valence"]

# elbow curve
elbow = elbow('"spotify"."artists_features"',
              preds,
              n_cluster = (1, 30))

Let's define and use the Vertica k-means algorithm to create a model that can group artists together.

In [57]:

from verticapy.learn.cluster import KMeans

# define k-means
model = KMeans('"spotify"."KMeans_spotify"', 
               n_cluster = 7)

We can train our new model on the "artists_features" relation we saved earlier.

In [58]:

# train the model
model.fit('"spotify"."artists_features"', 
          X = preds)

Out[58]:


=======
centers
=======
danceability| energy |speechiness|acousticness|instrumentalness|liveness|valence 
------------+--------+-----------+------------+----------------+--------+--------
   0.69555  | 0.78801|  0.10285  |   0.12729  |     0.01264    | 0.16900| 0.77472
   0.76995  | 0.64462|  0.27348  |   0.20702  |     0.00206    | 0.14808| 0.51245
   0.47486  | 0.36216|  0.03955  |   0.73322  |     0.01880    | 0.15942| 0.35397
   0.54658  | 0.69307|  0.06738  |   0.13429  |     0.09020    | 0.15264| 0.35517
   0.60919  | 0.75848|  0.17245  |   0.17270  |     0.06267    | 0.71443| 0.54388
   0.64666  | 0.63465|  0.15365  |   0.51753  |     0.01172    | 0.18339| 0.61588
   0.35545  | 0.24647|  0.02482  |   0.85935  |     0.78889    | 0.15544| 0.26981


=======
metrics
=======
Evaluation metrics:
     Total Sum of Squares: 236.02931
     Within-Cluster Sum of Squares: 
         Cluster 0: 19.761467
         Cluster 1: 12.785339
         Cluster 2: 12.796322
         Cluster 3: 23.034665
         Cluster 4: 5.8827394
         Cluster 5: 19.168113
         Cluster 6: 3.8413151
     Total Within-Cluster Sum of Squares: 97.26996
     Between-Cluster Sum of Squares: 138.75935
     Between-Cluster SS / Total SS: 58.79%
 Number of iterations performed: 35
 Converged: True
 Call:
kmeans('"spotify"."KMeans_spotify"', '"spotify"."artists_features"', '"danceability", "energy", "speechiness", "acousticness", "instrumentalness", "liveness", "valence"', 7
USING PARAMETERS max_iterations=300, epsilon=0.0001, init_method='kmeanspp', distance_method='euclidean')

Plot the result of the k-means algoritm:

In [59]:

model.plot()

Out[59]:

<AxesSubplot:xlabel='Dim1 (44.2%)', ylabel='Dim2 (18.6%)'>

In [60]:

# predict the genres
pred_genres = model.predict('"spotify"."artists_features"', 
                            X = ["danceability",
                                 "energy",
                                 "speechiness",
                                 "acousticness",
                                 "instrumentalness",
                                 "liveness",
                                 "valence"], 
                            name="pred_genres")

Let's see how our model groups these artists together:

In [63]:

# observe the results
pred_genres['artists',
            'pred_genres'].sort({'pred_genres':'desc'})

Out[63]:

	Abc Varchar(100)	123 pred_genres Integer
1		6
2		6
3		6
4		6
5		6
6		6
7		6
8		6
9		6
10		6
11		6
12		6
13		6
14		6
15		6
16		6
17		6
18		6
19		6
20		6
21		6
22		6
23		6
24		6
25		5
26		5
27		5
28		5
29		5
30		5
31		5
32		5
33		5
34		5
35		5
36		5
37		5
38		5
39		5
40		5
41		5
42		5
43		5
44		5
45		5
46		5
47		5
48		5
49		5
50		5
51		5
52		5
53		5
54		5
55		5
56		5
57		5
58		5
59		5
60		5
61		5
62		5
63		5
64		5
65		5
66		5
67		5
68		5
69		5
70		5
71		5
72		5
73		5
74		5
75		5
76		5
77		5
78		5
79		5
80		5
81		5
82		5
83		5
84		5
85		5
86		5
87		5
88		5
89		5
90		5
91		5
92		5
93		5
94		5
95		5
96		5
97		5
98		5
99		5
100		5

Rows: 1-100 | Columns: 2

Conclusion¶

We were able to predict the popularity Polish songs with a RandomForestRegressor model suggested by AutoML. We then created a k-means model to group artists into "genres" (clusters) based on the feature-commonalities in their tracks.

VerticaPy

Python API for Vertica Data Science at Scale

About the Author

Jehona Kryeziu
Data Scientist

Jehona is a Junior Data Scientist working for Vertica. She works on data projects identifying use cases and implementing solutions that can deliver value and insights to businesses having in focus to provide machine learning, analytics and architecture solutions. She follows modern trends in data science and is curious in current needs that come as a result of the recent developments of technology and increase of data volume. Her other interests include mathematics and Natural Language Processing.