Map location data with Axiom and Hex

curl -X 'POST' 'https://AXIOM_DOMAIN/v1/ingest/DATASET_NAME' \
-H 'Authorization: Bearer API_TOKEN' \
-H 'Content-Type: application/json' \
-d '[
    {
    "data": {
        "robot_id": "robot-001",
        "latitude": 37.7749,
        "longitude": -122.4194,
        "num_satellites": 8,
        "status": "active"
    }
    }
]'

import requests
import pandas as pd
from datetime import datetime, timedelta
import os

# Retrieve the API token from Hex secrets
axiom_token = os.environ.get("AXIOM_TOKEN")

# Define Axiom API endpoint and headers
base_url = "https://AXIOM_DOMAIN/v1/query/_apl"
headers = {
    'Authorization': f'Bearer {axiom_token}',
    'Content-Type': 'application/json',
    'Accept': 'application/json',
    'Accept-Encoding': 'gzip'
}

# Define the time range for your query
end_time = datetime.utcnow()
start_time = end_time - timedelta(days=3)  # Get data from the last 3 days

# Construct the APL query
query = {
    "apl": """DATASET_NAME
| project ['data.latitude'], ['data.longitude'], ['data.num_satellites'], ['data.robot_id'], ['data.status']""",
    "startTime": start_time.strftime("%Y-%m-%dT%H:%M:%SZ"),
    "endTime": end_time.strftime("%Y-%m-%dT%H:%M:%SZ")
}

try:
    # Send the request to Axiom API
    response = requests.post(
        f"{base_url}?format=tabular",
        headers=headers,
        json=query,
        timeout=10
    )
    
    # Print request details for debugging
    print("Request Details:")
    print(f"URL: {base_url}?format=tabular")
    print(f"Query: {query['apl']}")
    print(f"Response Status: {response.status_code}")
    
    if response.status_code == 200:
        data = response.json()
        if 'tables' in data:
            table = data['tables'][0]
            if table.get('columns') and len(table['columns']) > 0:
                columns = [field['name'] for field in table['fields']]
                rows = table['columns']
                
                # Create DataFrame with proper column orientation
                df = pd.DataFrame(list(zip(*rows)), columns=columns)
                
                # Ensure data types are appropriate for mapping
                df['data.latitude'] = pd.to_numeric(df['data.latitude'])
                df['data.longitude'] = pd.to_numeric(df['data.longitude'])
                df['data.num_satellites'] = pd.to_numeric(df['data.num_satellites'])
                
                # Display the first few rows to verify our data
                print("\nDataFrame Preview:")
                display(df.head())
                
                # Store the DataFrame for visualization
                robot_locations = df
            else:
                print("\nNo data found in the specified time range.")
        else:
            print("\nNo tables found in response")
            print("Response structure:", data.keys())
            
except Exception as e:
    print(f"\nError: {str(e)}")