supporting-blog-content/Boston-Celtics-Demo/celtics-esql-demo.ipynb (656 lines of code) (raw):
{
"cells": [
{
"cell_type": "markdown",
"id": "d8f76480-b620-4cfa-bcaa-236e667ae45b",
"metadata": {},
"source": [
"The following example is adapted from our Search Labs post on using [ES|QL with the Python language client](https://www.elastic.co/search-labs/blog/esql-pandas-dataframes-python). You can load this data using a method described in this [blog post](https://www.elastic.co/search-labs/blog/analyzing-data-using-python-elasticsearch-and-kibana)."
]
},
{
"cell_type": "code",
"execution_count": 1,
"id": "800448a5-57e9-4392-bdbe-09b889a87848",
"metadata": {},
"outputs": [],
"source": [
"# Import required libraries\n",
"from io import StringIO # Used to convert strings to file-like objects\n",
"from getpass import getpass # Securely request password or sensitive input\n",
"from elasticsearch import (\n",
" Elasticsearch,\n",
") # Elasticsearch client from the official library\n",
"import pandas as pd # Pandas library for data manipulation"
]
},
{
"cell_type": "code",
"execution_count": 2,
"id": "2dd890b9-828a-49ee-bbdf-c3de1a5576d9",
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Host: ········\n",
"API Key: ········\n"
]
}
],
"source": [
"# Create an Elasticsearch client instance\n",
"client = Elasticsearch(\n",
" getpass(\"Host: \"), # Securely get the host for the Elasticsearch cluster\n",
" api_key=getpass(\"API Key: \"), # Securely get the API key for authentication\n",
")"
]
},
{
"cell_type": "code",
"execution_count": 3,
"id": "ed7a849a-b23e-4a18-9377-04a790df60e3",
"metadata": {},
"outputs": [],
"source": [
"# Perform a query to retrieve the first 500 entries\n",
"response = client.esql.query(\n",
" query=\"FROM esql | LIMIT 500\",\n",
" format=\"csv\", # Specify the output format as CSV\n",
")"
]
},
{
"cell_type": "code",
"execution_count": 4,
"id": "4e152565-84d8-4632-86ff-9c8244aeef71",
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<style scoped>\n",
" .dataframe tbody tr th:only-of-type {\n",
" vertical-align: middle;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: right;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>AST</th>\n",
" <th>BLK</th>\n",
" <th>DREB</th>\n",
" <th>FG3A</th>\n",
" <th>FG3M</th>\n",
" <th>FG3_PCT</th>\n",
" <th>FGA</th>\n",
" <th>FGM</th>\n",
" <th>FG_PCT</th>\n",
" <th>FTA</th>\n",
" <th>...</th>\n",
" <th>SEASON_ID.keyword</th>\n",
" <th>STL</th>\n",
" <th>TEAM_ABBREVIATION</th>\n",
" <th>TEAM_ABBREVIATION.keyword</th>\n",
" <th>TEAM_ID</th>\n",
" <th>TEAM_NAME</th>\n",
" <th>TEAM_NAME.keyword</th>\n",
" <th>TOV</th>\n",
" <th>WL</th>\n",
" <th>WL.keyword</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>21</td>\n",
" <td>3</td>\n",
" <td>35</td>\n",
" <td>37</td>\n",
" <td>14</td>\n",
" <td>0.378</td>\n",
" <td>86</td>\n",
" <td>36</td>\n",
" <td>0.419</td>\n",
" <td>18</td>\n",
" <td>...</td>\n",
" <td>42023</td>\n",
" <td>5</td>\n",
" <td>BOS</td>\n",
" <td>BOS</td>\n",
" <td>1610612738</td>\n",
" <td>Boston Celtics</td>\n",
" <td>Boston Celtics</td>\n",
" <td>10</td>\n",
" <td>W</td>\n",
" <td>W</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>22</td>\n",
" <td>6</td>\n",
" <td>33</td>\n",
" <td>37</td>\n",
" <td>11</td>\n",
" <td>0.297</td>\n",
" <td>82</td>\n",
" <td>39</td>\n",
" <td>0.476</td>\n",
" <td>21</td>\n",
" <td>...</td>\n",
" <td>42023</td>\n",
" <td>4</td>\n",
" <td>BOS</td>\n",
" <td>BOS</td>\n",
" <td>1610612738</td>\n",
" <td>Boston Celtics</td>\n",
" <td>Boston Celtics</td>\n",
" <td>5</td>\n",
" <td>W</td>\n",
" <td>W</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>21</td>\n",
" <td>8</td>\n",
" <td>33</td>\n",
" <td>32</td>\n",
" <td>12</td>\n",
" <td>0.375</td>\n",
" <td>80</td>\n",
" <td>37</td>\n",
" <td>0.463</td>\n",
" <td>21</td>\n",
" <td>...</td>\n",
" <td>42023</td>\n",
" <td>9</td>\n",
" <td>BOS</td>\n",
" <td>BOS</td>\n",
" <td>1610612738</td>\n",
" <td>Boston Celtics</td>\n",
" <td>Boston Celtics</td>\n",
" <td>12</td>\n",
" <td>L</td>\n",
" <td>L</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>27</td>\n",
" <td>6</td>\n",
" <td>34</td>\n",
" <td>49</td>\n",
" <td>22</td>\n",
" <td>0.449</td>\n",
" <td>82</td>\n",
" <td>39</td>\n",
" <td>0.476</td>\n",
" <td>16</td>\n",
" <td>...</td>\n",
" <td>42023</td>\n",
" <td>6</td>\n",
" <td>BOS</td>\n",
" <td>BOS</td>\n",
" <td>1610612738</td>\n",
" <td>Boston Celtics</td>\n",
" <td>Boston Celtics</td>\n",
" <td>10</td>\n",
" <td>W</td>\n",
" <td>W</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>29</td>\n",
" <td>15</td>\n",
" <td>38</td>\n",
" <td>32</td>\n",
" <td>16</td>\n",
" <td>0.500</td>\n",
" <td>89</td>\n",
" <td>51</td>\n",
" <td>0.573</td>\n",
" <td>21</td>\n",
" <td>...</td>\n",
" <td>22023</td>\n",
" <td>10</td>\n",
" <td>BOS</td>\n",
" <td>BOS</td>\n",
" <td>1610612738</td>\n",
" <td>Boston Celtics</td>\n",
" <td>Boston Celtics</td>\n",
" <td>14</td>\n",
" <td>W</td>\n",
" <td>W</td>\n",
" </tr>\n",
" <tr>\n",
" <th>...</th>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" </tr>\n",
" <tr>\n",
" <th>80</th>\n",
" <td>22</td>\n",
" <td>6</td>\n",
" <td>40</td>\n",
" <td>45</td>\n",
" <td>15</td>\n",
" <td>0.333</td>\n",
" <td>90</td>\n",
" <td>43</td>\n",
" <td>0.478</td>\n",
" <td>27</td>\n",
" <td>...</td>\n",
" <td>22023</td>\n",
" <td>4</td>\n",
" <td>BOS</td>\n",
" <td>BOS</td>\n",
" <td>1610612738</td>\n",
" <td>Boston Celtics</td>\n",
" <td>Boston Celtics</td>\n",
" <td>11</td>\n",
" <td>W</td>\n",
" <td>W</td>\n",
" </tr>\n",
" <tr>\n",
" <th>81</th>\n",
" <td>27</td>\n",
" <td>2</td>\n",
" <td>46</td>\n",
" <td>35</td>\n",
" <td>20</td>\n",
" <td>0.571</td>\n",
" <td>95</td>\n",
" <td>54</td>\n",
" <td>0.568</td>\n",
" <td>28</td>\n",
" <td>...</td>\n",
" <td>22023</td>\n",
" <td>5</td>\n",
" <td>BOS</td>\n",
" <td>BOS</td>\n",
" <td>1610612738</td>\n",
" <td>Boston Celtics</td>\n",
" <td>Boston Celtics</td>\n",
" <td>11</td>\n",
" <td>W</td>\n",
" <td>W</td>\n",
" </tr>\n",
" <tr>\n",
" <th>82</th>\n",
" <td>31</td>\n",
" <td>6</td>\n",
" <td>36</td>\n",
" <td>53</td>\n",
" <td>19</td>\n",
" <td>0.358</td>\n",
" <td>102</td>\n",
" <td>51</td>\n",
" <td>0.500</td>\n",
" <td>7</td>\n",
" <td>...</td>\n",
" <td>22023</td>\n",
" <td>11</td>\n",
" <td>BOS</td>\n",
" <td>BOS</td>\n",
" <td>1610612738</td>\n",
" <td>Boston Celtics</td>\n",
" <td>Boston Celtics</td>\n",
" <td>17</td>\n",
" <td>W</td>\n",
" <td>W</td>\n",
" </tr>\n",
" <tr>\n",
" <th>83</th>\n",
" <td>20</td>\n",
" <td>6</td>\n",
" <td>39</td>\n",
" <td>39</td>\n",
" <td>16</td>\n",
" <td>0.410</td>\n",
" <td>95</td>\n",
" <td>45</td>\n",
" <td>0.474</td>\n",
" <td>19</td>\n",
" <td>...</td>\n",
" <td>22023</td>\n",
" <td>7</td>\n",
" <td>BOS</td>\n",
" <td>BOS</td>\n",
" <td>1610612738</td>\n",
" <td>Boston Celtics</td>\n",
" <td>Boston Celtics</td>\n",
" <td>15</td>\n",
" <td>W</td>\n",
" <td>W</td>\n",
" </tr>\n",
" <tr>\n",
" <th>84</th>\n",
" <td>18</td>\n",
" <td>11</td>\n",
" <td>39</td>\n",
" <td>39</td>\n",
" <td>12</td>\n",
" <td>0.308</td>\n",
" <td>77</td>\n",
" <td>37</td>\n",
" <td>0.481</td>\n",
" <td>26</td>\n",
" <td>...</td>\n",
" <td>22023</td>\n",
" <td>6</td>\n",
" <td>BOS</td>\n",
" <td>BOS</td>\n",
" <td>1610612738</td>\n",
" <td>Boston Celtics</td>\n",
" <td>Boston Celtics</td>\n",
" <td>13</td>\n",
" <td>W</td>\n",
" <td>W</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"<p>85 rows × 34 columns</p>\n",
"</div>"
],
"text/plain": [
" AST BLK DREB FG3A FG3M FG3_PCT FGA FGM FG_PCT FTA ... \\\n",
"0 21 3 35 37 14 0.378 86 36 0.419 18 ... \n",
"1 22 6 33 37 11 0.297 82 39 0.476 21 ... \n",
"2 21 8 33 32 12 0.375 80 37 0.463 21 ... \n",
"3 27 6 34 49 22 0.449 82 39 0.476 16 ... \n",
"4 29 15 38 32 16 0.500 89 51 0.573 21 ... \n",
".. ... ... ... ... ... ... ... ... ... ... ... \n",
"80 22 6 40 45 15 0.333 90 43 0.478 27 ... \n",
"81 27 2 46 35 20 0.571 95 54 0.568 28 ... \n",
"82 31 6 36 53 19 0.358 102 51 0.500 7 ... \n",
"83 20 6 39 39 16 0.410 95 45 0.474 19 ... \n",
"84 18 11 39 39 12 0.308 77 37 0.481 26 ... \n",
"\n",
" SEASON_ID.keyword STL TEAM_ABBREVIATION TEAM_ABBREVIATION.keyword \\\n",
"0 42023 5 BOS BOS \n",
"1 42023 4 BOS BOS \n",
"2 42023 9 BOS BOS \n",
"3 42023 6 BOS BOS \n",
"4 22023 10 BOS BOS \n",
".. ... ... ... ... \n",
"80 22023 4 BOS BOS \n",
"81 22023 5 BOS BOS \n",
"82 22023 11 BOS BOS \n",
"83 22023 7 BOS BOS \n",
"84 22023 6 BOS BOS \n",
"\n",
" TEAM_ID TEAM_NAME TEAM_NAME.keyword TOV WL WL.keyword \n",
"0 1610612738 Boston Celtics Boston Celtics 10 W W \n",
"1 1610612738 Boston Celtics Boston Celtics 5 W W \n",
"2 1610612738 Boston Celtics Boston Celtics 12 L L \n",
"3 1610612738 Boston Celtics Boston Celtics 10 W W \n",
"4 1610612738 Boston Celtics Boston Celtics 14 W W \n",
".. ... ... ... ... .. ... \n",
"80 1610612738 Boston Celtics Boston Celtics 11 W W \n",
"81 1610612738 Boston Celtics Boston Celtics 11 W W \n",
"82 1610612738 Boston Celtics Boston Celtics 17 W W \n",
"83 1610612738 Boston Celtics Boston Celtics 15 W W \n",
"84 1610612738 Boston Celtics Boston Celtics 13 W W \n",
"\n",
"[85 rows x 34 columns]"
]
},
"execution_count": 4,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"# Convert the CSV response into a pandas DataFrame\n",
"df = pd.read_csv(StringIO(response.body))\n",
"df # Display the DataFrame"
]
},
{
"cell_type": "code",
"execution_count": 5,
"id": "ae173c10-94f5-4608-ba63-9bd37ca84378",
"metadata": {},
"outputs": [],
"source": [
"# Query to fetch game dates and win/loss information, limiting to 10 entries\n",
"wl_date = client.esql.query(\n",
" query=\"FROM esql | KEEP GAME_DATE, WL | LIMIT 10\",\n",
" format=\"csv\",\n",
")"
]
},
{
"cell_type": "code",
"execution_count": 6,
"id": "a0cfd315-8d25-44aa-bc70-19efa32bf5b3",
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<style scoped>\n",
" .dataframe tbody tr th:only-of-type {\n",
" vertical-align: middle;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: right;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>GAME_DATE</th>\n",
" <th>WL</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>2024-04-29T00:00:00.000Z</td>\n",
" <td>W</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>2024-04-27T00:00:00.000Z</td>\n",
" <td>W</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>2024-04-24T00:00:00.000Z</td>\n",
" <td>L</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>2024-04-21T00:00:00.000Z</td>\n",
" <td>W</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>2024-04-14T00:00:00.000Z</td>\n",
" <td>W</td>\n",
" </tr>\n",
" <tr>\n",
" <th>5</th>\n",
" <td>2024-04-12T00:00:00.000Z</td>\n",
" <td>W</td>\n",
" </tr>\n",
" <tr>\n",
" <th>6</th>\n",
" <td>2024-04-11T00:00:00.000Z</td>\n",
" <td>L</td>\n",
" </tr>\n",
" <tr>\n",
" <th>7</th>\n",
" <td>2024-04-07T00:00:00.000Z</td>\n",
" <td>W</td>\n",
" </tr>\n",
" <tr>\n",
" <th>8</th>\n",
" <td>2024-04-05T00:00:00.000Z</td>\n",
" <td>W</td>\n",
" </tr>\n",
" <tr>\n",
" <th>9</th>\n",
" <td>2024-04-03T00:00:00.000Z</td>\n",
" <td>W</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" GAME_DATE WL\n",
"0 2024-04-29T00:00:00.000Z W\n",
"1 2024-04-27T00:00:00.000Z W\n",
"2 2024-04-24T00:00:00.000Z L\n",
"3 2024-04-21T00:00:00.000Z W\n",
"4 2024-04-14T00:00:00.000Z W\n",
"5 2024-04-12T00:00:00.000Z W\n",
"6 2024-04-11T00:00:00.000Z L\n",
"7 2024-04-07T00:00:00.000Z W\n",
"8 2024-04-05T00:00:00.000Z W\n",
"9 2024-04-03T00:00:00.000Z W"
]
},
"execution_count": 6,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"# Convert the query response into a DataFrame\n",
"wl_date_df = pd.read_csv(StringIO(wl_date.body))\n",
"wl_date_df # Display the DataFrame"
]
},
{
"cell_type": "code",
"execution_count": 7,
"id": "a422a04a-a314-4a45-8c09-73aadfb958bc",
"metadata": {},
"outputs": [],
"source": [
"# Query to calculate average field goal percentage and average three-point percentage\n",
"stats = client.esql.query(\n",
" query=\"FROM esql | STATS AVG(FG_PCT), AVG(FG3_PCT) | LIMIT 1\",\n",
" format=\"csv\",\n",
")"
]
},
{
"cell_type": "code",
"execution_count": 8,
"id": "1468ab81-5317-4049-b9e9-5ee30519f194",
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<style scoped>\n",
" .dataframe tbody tr th:only-of-type {\n",
" vertical-align: middle;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: right;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>AVG(FG_PCT)</th>\n",
" <th>AVG(FG3_PCT)</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>0.487341</td>\n",
" <td>0.387706</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" AVG(FG_PCT) AVG(FG3_PCT)\n",
"0 0.487341 0.387706"
]
},
"execution_count": 8,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"# Convert the stats query response into a DataFrame\n",
"stats_df = pd.read_csv(StringIO(stats.body))\n",
"stats_df # Display the DataFrame"
]
}
],
"metadata": {
"kernelspec": {
"display_name": "Python 3 (ipykernel)",
"language": "python",
"name": "python3"
},
"language_info": {
"codemirror_mode": {
"name": "ipython",
"version": 3
},
"file_extension": ".py",
"mimetype": "text/x-python",
"name": "python",
"nbconvert_exporter": "python",
"pygments_lexer": "ipython3",
"version": "3.12.1"
}
},
"nbformat": 4,
"nbformat_minor": 5
}