{ "cells": [ { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "import pandas as pd\n", "import numpy as np\n", "import matplotlib.pyplot as plt\n", "import seaborn as sns\n", "from pathlib import Path\n", "\n", "# Set up plotting style\n", "plt.style.use('default')\n", "sns.set_palette(\"husl\")\n", "plt.rcParams['figure.figsize'] = (12, 8)\n", "plt.rcParams['font.size'] = 10\n", "\n", "# Check if file exists\n", "file_path = \"../input/tidbcloud_dedicated_pricing_dataset.csv\"\n", "if Path(file_path).exists():\n", " print(f\"āœ“ Found file: {file_path}\")\n", "else:\n", " print(f\"āœ— File not found: {file_path}\")\n", " print(\"Please make sure the file is in the current directory\")\n", "\n", "df = pd.read_csv(file_path)\n", "df\n" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "df['Dedicated Price Compute Only WO TiFlash'] = df['Dedicated Price TiDB Compute Only'] + df['Dedicated Price TiKV Compute Only']\n", "df['Dedicated Price Compute Only WO TiFlash']\n", "df['target'] = df['Dedicated Price Compute Only WO TiFlash'].astype(float)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# Data exploration and format identification\n", "if df is not None:\n", " print(\"=\"*60)\n", " print(\"DATA EXPLORATION & FORMAT ANALYSIS\")\n", " print(\"=\"*60)\n", " \n", " # Basic information\n", " print(\"\\n1. BASIC INFORMATION:\")\n", " print(f\" Shape: {df.shape}\")\n", " print(f\" Columns: {list(df.columns)}\")\n", " \n", " # Data types\n", " print(\"\\n2. DATA TYPES (Before Conversion):\")\n", " print(df.dtypes)\n", " \n", " # Check for missing values and fill with zero\n", " print(\"\\n3. MISSING VALUES:\")\n", " missing_values = df.isnull().sum()\n", " if missing_values.sum() > 0:\n", " print(\" Missing values found:\")\n", " print(missing_values[missing_values > 0])\n", " print(\"\\n Filling NA values with zero...\")\n", " df = df.fillna(0)\n", " print(\" āœ“ All NA values have been filled with zero\")\n", " else:\n", " print(\" No missing values found\")\n", " \n", " # Clean QPS data first\n", " print(\"\\n4. QPS DATA CLEANING:\")\n", " print(\" Original QPS formats found:\")\n", " qps_samples = df['QPS'].value_counts().head(10)\n", " for qps, count in qps_samples.items():\n", " print(f\" '{qps}': {count} clusters\")\n", " \n", " def parse_qps_range(qps_str):\n", " \"\"\"Parse QPS values into min and max: handles formats like '0-60', '400', '3K-16K'\"\"\"\n", " if pd.isna(qps_str):\n", " return np.nan, np.nan\n", " \n", " qps_str = str(qps_str).strip()\n", " \n", " # Try direct numeric conversion first (single value)\n", " try:\n", " value = float(qps_str)\n", " return value, value # min and max are the same\n", " except ValueError:\n", " pass\n", " \n", " # Handle ranges with K suffix (e.g., \"3K-16K\", \"50K-300K\")\n", " if '-' in qps_str and 'K' in qps_str:\n", " try:\n", " parts = qps_str.replace('K', '').split('-')\n", " if len(parts) == 2:\n", " low, high = float(parts[0]) * 1000, float(parts[1]) * 1000\n", " return low, high\n", " except ValueError:\n", " pass\n", " \n", " # Handle regular ranges (e.g., \"0-60\", \"50-170\")\n", " elif '-' in qps_str:\n", " try:\n", " parts = qps_str.split('-')\n", " if len(parts) == 2:\n", " low, high = float(parts[0]), float(parts[1])\n", " return low, high\n", " except ValueError:\n", " pass\n", " \n", " # Handle single K values (e.g., \"3K\")\n", " elif 'K' in qps_str:\n", " try:\n", " value = float(qps_str.replace('K', '')) * 1000\n", " return value, value\n", " except ValueError:\n", " pass\n", " \n", " return np.nan, np.nan\n", " \n", " # Apply QPS cleaning to create two columns\n", " qps_ranges = df['QPS'].apply(parse_qps_range)\n", " df['QPS_min'] = [x[0] for x in qps_ranges]\n", " df['QPS_max'] = [x[1] for x in qps_ranges]\n", " \n", " print(f\"\\n Cleaning results:\")\n", " print(f\" Successfully parsed: {df['QPS_min'].notna().sum()}/{len(df)} values\")\n", " print(f\" Failed to parse: {df['QPS_min'].isna().sum()} values\")\n", " \n", " if df['QPS_min'].notna().sum() > 0:\n", " print(f\" QPS range: {df['QPS_min'].min():.0f} - {df['QPS_max'].max():.0f}\")\n", " \n", " print(f\"\\n New columns created:\")\n", " print(f\" QPS_min: minimum QPS value\")\n", " print(f\" QPS_max: maximum QPS value\") \n", "\n", " # drop the column which type is not float64\n", " df = df.select_dtypes(include=['float64'])\n", "\n", "df\n" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "fig, axes = plt.subplots(1, 2, figsize=(18, 7)) \n", "fig.suptitle('TiDB Cloud Pricing: Price Compute Only Analysis', fontsize=16, fontweight='bold')\n", "\n", "# 1. Histogram\n", "axes[0].hist(df['Dedicated Price Compute Only WO TiFlash'], bins=30, alpha=0.7, color='skyblue', edgecolor='black')\n", "axes[0].set_title('Distribution of Dedicated Price Compute Only WO TiFlash')\n", "axes[0].set_xlabel('Dedicated Price Compute Only WO TiFlash')\n", "axes[0].set_ylabel('Frequency')\n", "axes[0].axvline(df['Dedicated Price Compute Only WO TiFlash'].mean(), color='red', linestyle='--', label=f'Mean: {df[\"Dedicated Price Compute Only WO TiFlash\"].mean():.2f}')\n", "axes[0].axvline(df['Dedicated Price Compute Only WO TiFlash'].median(), color='green', linestyle='--', label=f'Median: {df[\"Dedicated Price Compute Only WO TiFlash\"].median():.2f}')\n", "axes[0].legend()\n", "axes[0].grid(True, alpha=0.3)\n", "\n", "# 2. Scatter plot: Avg Total CPU Util vs Price Compute Only\n", "scatter = axes[1].scatter(df['Dedicated Price Compute Only WO TiFlash'], df['Avg Total CPU Util'], alpha=0.6, c=df['Avg Total CPU Util'], cmap='RdYlBu_r', s=50)\n", "axes[1].set_title('Avg Total CPU Util vs Price Compute Only')\n", "axes[1].set_xlabel('Price Compute Only ($)')\n", "axes[1].set_ylabel('Avg Total CPU Util')\n", "axes[1].grid(True, alpha=0.3)\n", "\n", "# Focus x-axis on data concentration area (0-100K)\n", "axes[1].set_xlim(0, 100000)\n", "# Set custom ticks for better readability\n", "axes[1].set_xticks([0, 20000, 40000, 60000, 80000, 100000])\n", "axes[1].set_xticklabels(['0', '20K', '40K', '60K', '80K', '100K'])\n", "axes[1].tick_params(axis='x', rotation=45, labelsize=9)\n", "\n", "fig.colorbar(scatter, ax=axes[1], label='Avg Total CPU Util')\n", "\n", "plt.tight_layout()\n", "plt.show()\n", "\n", "# Basic statistics\n", "print(f\"\\nšŸ“Š BASIC STATISTICS:\")\n", "print(f\" Count: {df['Dedicated Price Compute Only WO TiFlash'].count()}\")\n", "print(f\" Mean: {df['Dedicated Price Compute Only WO TiFlash'].mean():.4f}\")\n", "print(f\" Median: {df['Dedicated Price Compute Only WO TiFlash'].median():.4f}\")\n", "print(f\" Std: {df['Dedicated Price Compute Only WO TiFlash'].std():.4f}\")\n", "print(f\" Min: {df['Dedicated Price Compute Only WO TiFlash'].min():.4f}\")\n", "print(f\" Max: {df['Dedicated Price Compute Only WO TiFlash'].max():.4f}\")" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "df.to_pickle(\"../input/tidbcloud_dedicated_pricing_dataset.pkl\")" ] } ], "metadata": { "kernelspec": { "display_name": "corpus", "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.11.11" } }, "nbformat": 4, "nbformat_minor": 2 }