Query and Visualize

1
!pip install -U athena-intelligence

1
import json
2
import pandas as pd
3
import os
4
5
ATHENA_API_KEY = os.environ["ATHENA_API_KEY"]
6
7
from athena import Model, Tools
8
from athena.client import Athena
9
10
athena = Athena(
11
    api_key=ATHENA_API_KEY,
12
)

1
# default database id = 9
2
default_database_id = 9

1
query = """
2
WITH RankedCustomers AS (
3
  SELECT
4
    r.route_id,
5
    r.src AS route_source,
6
    r.dst AS route_destination,
7
    ts.passenger_id,
8
    c.name AS customer_name,
9
    SUM(ts.num_tickets) AS total_tickets,
10
    RANK() OVER (PARTITION BY r.route_id ORDER BY SUM(ts.num_tickets) DESC) AS rank
11
  FROM
12
    sample_schema.train_routes r
13
  JOIN sample_schema.train_trips t ON r.route_id = t.route_id
14
  JOIN sample_schema.train_ticketsales ts ON t.trip_id = ts.trip_id
15
  JOIN sample_schema.train_customers c ON ts.passenger_id = c.passenger_id
16
  GROUP BY
17
    r.route_id,
18
    r.src,
19
    r.dst,
20
    ts.passenger_id,
21
    c.name
22
)
23
SELECT
24
  route_id,
25
  route_source,
26
  route_destination,
27
  passenger_id,
28
  customer_name,
29
  total_tickets
30
FROM
31
  RankedCustomers
32
WHERE
33
  rank <= 3
34
ORDER BY
35
  route_id,
36
  rank;
37
"""

1
query_result = athena.query.execute(
2
    sql_command = query,
3
    database_id = default_database_id
4
    )
5
6
formatted_query_result = json.loads(query_result.json())
7
data_output = formatted_query_result['result']['data']
8
9
# Convert the list of dictionaries to a DataFrame
10
df = pd.DataFrame(data_output)
11
12
# Display the DataFrame to verify the contents
13
df

1
import pandas as pd
2
import matplotlib.pyplot as plt
3
import numpy as np
4
5
# Assuming 'df' is your DataFrame
6
# Ensure your DataFrame is named 'df' or adjust the variable name accordingly
7
8
# Visualization 1: Total Tickets Sold by Route
9
route_tickets_sum = df.groupby('route_id')['total_tickets'].sum().reset_index()
10
plt.figure(figsize=(10, 6))
11
plt.bar(route_tickets_sum['route_id'], route_tickets_sum['total_tickets'], color='deepskyblue')  # Changed color to 'deepskyblue'
12
plt.title('Total Tickets Sold by Route')
13
plt.xlabel('Route ID')
14
plt.ylabel('Total Tickets Sold')
15
plt.xticks(rotation=45)
16
plt.tight_layout()
17
plt.show()