Analysis.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "f616d206-5cfd-4ff1-b40d-b5ee2947e447",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from sqlalchemy import create_engine\n",
    "\n",
    "# Define the PostgreSQL URL\n",
    "postgresql_url = 'postgresql://airflow:airflow@173.212.221.185:5432/airflow'\n",
    "\n",
    "# Create an engine\n",
    "engine = create_engine(postgresql_url)\n",
    "\n",
    "# Establish a connection\n",
    "connection = engine.connect()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "45af3f38-4a28-46cc-8004-54e8ed7a3610",
   "metadata": {},
   "outputs": [],
   "source": [
    "price_data = pd.read_sql('Select * from crypto_price_data where processed = False;',connection)\n",
    "depth_data = pd.read_sql('Select * from crypto_depth where processed = False;',connection)\n",
    "ticker_data = pd.read_sql('Select * from crypto_ticker where processed = False;',connection)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "77331a17-791e-4bd3-95e3-4ce0b43a99a1",
   "metadata": {},
   "outputs": [],
   "source": [
    "df1 = price_data.drop(columns=['id','symbol','processed'])\n",
    "df2 = depth_data.drop(columns=['id','processed'])\n",
    "df3 = ticker_data.drop(columns=['id','symbol','processed'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "b15b5fe4-1e12-4d3e-9b95-46332008a217",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Convert 'timestamp' column to datetime\n",
    "df1['timestamp'] = pd.to_datetime(df1['timestamp'])\n",
    "df2['timestamp'] = pd.to_datetime(df2['timestamp'])\n",
    "df3['timestamp'] = pd.to_datetime(df3['timestamp'])\n",
    "\n",
    "# Sort DataFrames by 'timestamp'\n",
    "df1.sort_values('timestamp', inplace=True)\n",
    "df2.sort_values('timestamp', inplace=True)\n",
    "df3.sort_values('timestamp', inplace=True)\n",
    "\n",
    "# Merge with tolerance\n",
    "tolerance = pd.Timedelta('4min')\n",
    "\n",
    "merged_df = pd.merge_asof(df1, df2, on='timestamp', tolerance=tolerance, direction='nearest')\n",
    "merged_df = pd.merge_asof(merged_df, df3, on='timestamp', tolerance=tolerance, direction='nearest')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "fd12112e-48ba-4fc6-9aae-7d910c688a66",
   "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>price</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>lastupdateid</th>\n",
       "      <th>bids</th>\n",
       "      <th>asks</th>\n",
       "      <th>pricechange</th>\n",
       "      <th>pricechangepercent</th>\n",
       "      <th>weightedavgprice</th>\n",
       "      <th>prevcloseprice</th>\n",
       "      <th>lastprice</th>\n",
       "      <th>lastqty</th>\n",
       "      <th>bidprice</th>\n",
       "      <th>bidqty</th>\n",
       "      <th>askprice</th>\n",
       "      <th>askqty</th>\n",
       "      <th>openprice</th>\n",
       "      <th>highprice</th>\n",
       "      <th>lowprice</th>\n",
       "      <th>volume</th>\n",
       "      <th>quotevolume</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>63173.99</td>\n",
       "      <td>2024-08-02 18:25:09.536314</td>\n",
       "      <td>49609604203</td>\n",
       "      <td>{{63172.00000000,6.37552000},{63171.99000000,0...</td>\n",
       "      <td>{{63172.01000000,0.46704000},{63172.11000000,0...</td>\n",
       "      <td>51.99</td>\n",
       "      <td>0.082</td>\n",
       "      <td>64177.913005</td>\n",
       "      <td>63122.01</td>\n",
       "      <td>63173.99</td>\n",
       "      <td>0.00045</td>\n",
       "      <td>63173.99</td>\n",
       "      <td>9.11026</td>\n",
       "      <td>63174.00</td>\n",
       "      <td>1.73773</td>\n",
       "      <td>63122.00</td>\n",
       "      <td>65659.78</td>\n",
       "      <td>62432.9</td>\n",
       "      <td>37206.07340</td>\n",
       "      <td>2.387808e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>63068.0</td>\n",
       "      <td>2024-08-02 18:30:10.501358</td>\n",
       "      <td>49609925676</td>\n",
       "      <td>{{63068.00000000,0.63882000},{63067.97000000,0...</td>\n",
       "      <td>{{63068.01000000,9.70944000},{63068.41000000,0...</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.000</td>\n",
       "      <td>64179.034035</td>\n",
       "      <td>63067.99</td>\n",
       "      <td>63068.00</td>\n",
       "      <td>0.01200</td>\n",
       "      <td>63068.00</td>\n",
       "      <td>1.13472</td>\n",
       "      <td>63068.01</td>\n",
       "      <td>9.70944</td>\n",
       "      <td>63068.00</td>\n",
       "      <td>65659.78</td>\n",
       "      <td>62432.9</td>\n",
       "      <td>37171.37937</td>\n",
       "      <td>2.385623e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>63167.48</td>\n",
       "      <td>2024-08-02 18:35:10.901813</td>\n",
       "      <td>49610238836</td>\n",
       "      <td>{{63170.07000000,0.85979000},{63170.01000000,0...</td>\n",
       "      <td>{{63170.08000000,7.02801000},{63171.50000000,0...</td>\n",
       "      <td>159.49</td>\n",
       "      <td>0.253</td>\n",
       "      <td>64181.254083</td>\n",
       "      <td>63008.00</td>\n",
       "      <td>63167.48</td>\n",
       "      <td>0.02266</td>\n",
       "      <td>63167.48</td>\n",
       "      <td>0.09009</td>\n",
       "      <td>63167.49</td>\n",
       "      <td>6.36451</td>\n",
       "      <td>63007.99</td>\n",
       "      <td>65659.78</td>\n",
       "      <td>62432.9</td>\n",
       "      <td>37110.33633</td>\n",
       "      <td>2.381788e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>62966.01</td>\n",
       "      <td>2024-08-02 18:40:10.304483</td>\n",
       "      <td>49610631825</td>\n",
       "      <td>{{62966.00000000,5.52480000},{62965.99000000,0...</td>\n",
       "      <td>{{62966.01000000,0.01027000},{62967.99000000,0...</td>\n",
       "      <td>30.01</td>\n",
       "      <td>0.048</td>\n",
       "      <td>64183.414380</td>\n",
       "      <td>62936.00</td>\n",
       "      <td>62966.01</td>\n",
       "      <td>0.00177</td>\n",
       "      <td>62966.00</td>\n",
       "      <td>5.50307</td>\n",
       "      <td>62966.01</td>\n",
       "      <td>0.11847</td>\n",
       "      <td>62936.00</td>\n",
       "      <td>65659.78</td>\n",
       "      <td>62432.9</td>\n",
       "      <td>37047.03811</td>\n",
       "      <td>2.377805e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>62966.0</td>\n",
       "      <td>2024-08-02 18:45:09.519664</td>\n",
       "      <td>49610954922</td>\n",
       "      <td>{{62964.00000000,0.01021000},{62963.99000000,0...</td>\n",
       "      <td>{{62964.01000000,1.27229000},{62964.02000000,0...</td>\n",
       "      <td>136.00</td>\n",
       "      <td>0.216</td>\n",
       "      <td>64190.577131</td>\n",
       "      <td>62828.00</td>\n",
       "      <td>62964.00</td>\n",
       "      <td>0.01066</td>\n",
       "      <td>62963.99</td>\n",
       "      <td>0.05694</td>\n",
       "      <td>62964.00</td>\n",
       "      <td>7.90221</td>\n",
       "      <td>62828.00</td>\n",
       "      <td>65659.78</td>\n",
       "      <td>62432.9</td>\n",
       "      <td>36841.58210</td>\n",
       "      <td>2.364882e+09</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      price                  timestamp  lastupdateid  \\\n",
       "0  63173.99 2024-08-02 18:25:09.536314   49609604203   \n",
       "1   63068.0 2024-08-02 18:30:10.501358   49609925676   \n",
       "2  63167.48 2024-08-02 18:35:10.901813   49610238836   \n",
       "3  62966.01 2024-08-02 18:40:10.304483   49610631825   \n",
       "4   62966.0 2024-08-02 18:45:09.519664   49610954922   \n",
       "\n",
       "                                                bids  \\\n",
       "0  {{63172.00000000,6.37552000},{63171.99000000,0...   \n",
       "1  {{63068.00000000,0.63882000},{63067.97000000,0...   \n",
       "2  {{63170.07000000,0.85979000},{63170.01000000,0...   \n",
       "3  {{62966.00000000,5.52480000},{62965.99000000,0...   \n",
       "4  {{62964.00000000,0.01021000},{62963.99000000,0...   \n",
       "\n",
       "                                                asks  pricechange  \\\n",
       "0  {{63172.01000000,0.46704000},{63172.11000000,0...        51.99   \n",
       "1  {{63068.01000000,9.70944000},{63068.41000000,0...         0.00   \n",
       "2  {{63170.08000000,7.02801000},{63171.50000000,0...       159.49   \n",
       "3  {{62966.01000000,0.01027000},{62967.99000000,0...        30.01   \n",
       "4  {{62964.01000000,1.27229000},{62964.02000000,0...       136.00   \n",
       "\n",
       "   pricechangepercent  weightedavgprice  prevcloseprice  lastprice  lastqty  \\\n",
       "0               0.082      64177.913005        63122.01   63173.99  0.00045   \n",
       "1               0.000      64179.034035        63067.99   63068.00  0.01200   \n",
       "2               0.253      64181.254083        63008.00   63167.48  0.02266   \n",
       "3               0.048      64183.414380        62936.00   62966.01  0.00177   \n",
       "4               0.216      64190.577131        62828.00   62964.00  0.01066   \n",
       "\n",
       "   bidprice   bidqty  askprice   askqty  openprice  highprice  lowprice  \\\n",
       "0  63173.99  9.11026  63174.00  1.73773   63122.00   65659.78   62432.9   \n",
       "1  63068.00  1.13472  63068.01  9.70944   63068.00   65659.78   62432.9   \n",
       "2  63167.48  0.09009  63167.49  6.36451   63007.99   65659.78   62432.9   \n",
       "3  62966.00  5.50307  62966.01  0.11847   62936.00   65659.78   62432.9   \n",
       "4  62963.99  0.05694  62964.00  7.90221   62828.00   65659.78   62432.9   \n",
       "\n",
       "        volume   quotevolume  \n",
       "0  37206.07340  2.387808e+09  \n",
       "1  37171.37937  2.385623e+09  \n",
       "2  37110.33633  2.381788e+09  \n",
       "3  37047.03811  2.377805e+09  \n",
       "4  36841.58210  2.364882e+09  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged_df.drop(columns=['opentime','closetime','firstid','lastid','count'], inplace=True)\n",
    "merged_df.head()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "e7626de4-5c99-43e9-b08c-45f85451ff30",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0          1\n",
       "1          2\n",
       "2          3\n",
       "3          4\n",
       "4          5\n",
       "        ... \n",
       "1503    1504\n",
       "1504    1505\n",
       "1505    1506\n",
       "1506    1507\n",
       "1507    1508\n",
       "Name: id, Length: 1508, dtype: int64"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "price_data['id']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "5fd2b1c5-390c-4d62-b079-002a05243095",
   "metadata": {
    "scrolled": true
   },
   "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>price</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>lastupdateid</th>\n",
       "      <th>bids</th>\n",
       "      <th>asks</th>\n",
       "      <th>pricechange</th>\n",
       "      <th>pricechangepercent</th>\n",
       "      <th>weightedavgprice</th>\n",
       "      <th>prevcloseprice</th>\n",
       "      <th>lastprice</th>\n",
       "      <th>...</th>\n",
       "      <th>openprice</th>\n",
       "      <th>highprice</th>\n",
       "      <th>lowprice</th>\n",
       "      <th>volume</th>\n",
       "      <th>quotevolume</th>\n",
       "      <th>opentime</th>\n",
       "      <th>closetime</th>\n",
       "      <th>firstid</th>\n",
       "      <th>lastid</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>63173.99</td>\n",
       "      <td>2024-08-02 18:25:09.536314</td>\n",
       "      <td>49609604203</td>\n",
       "      <td>{{63172.00000000,6.37552000},{63171.99000000,0...</td>\n",
       "      <td>{{63172.01000000,0.46704000},{63172.11000000,0...</td>\n",
       "      <td>51.99</td>\n",
       "      <td>0.082</td>\n",
       "      <td>64177.913005</td>\n",
       "      <td>63122.01</td>\n",
       "      <td>63173.99</td>\n",
       "      <td>...</td>\n",
       "      <td>63122.00</td>\n",
       "      <td>65659.78</td>\n",
       "      <td>62432.9</td>\n",
       "      <td>37206.07340</td>\n",
       "      <td>2.387808e+09</td>\n",
       "      <td>1722536704040</td>\n",
       "      <td>1722623104040</td>\n",
       "      <td>3713093603</td>\n",
       "      <td>3715520420</td>\n",
       "      <td>2426818</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>63068.0</td>\n",
       "      <td>2024-08-02 18:30:10.501358</td>\n",
       "      <td>49609925676</td>\n",
       "      <td>{{63068.00000000,0.63882000},{63067.97000000,0...</td>\n",
       "      <td>{{63068.01000000,9.70944000},{63068.41000000,0...</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.000</td>\n",
       "      <td>64179.034035</td>\n",
       "      <td>63067.99</td>\n",
       "      <td>63068.00</td>\n",
       "      <td>...</td>\n",
       "      <td>63068.00</td>\n",
       "      <td>65659.78</td>\n",
       "      <td>62432.9</td>\n",
       "      <td>37171.37937</td>\n",
       "      <td>2.385623e+09</td>\n",
       "      <td>1722537004310</td>\n",
       "      <td>1722623404310</td>\n",
       "      <td>3713102305</td>\n",
       "      <td>3715527226</td>\n",
       "      <td>2424922</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>63167.48</td>\n",
       "      <td>2024-08-02 18:35:10.901813</td>\n",
       "      <td>49610238836</td>\n",
       "      <td>{{63170.07000000,0.85979000},{63170.01000000,0...</td>\n",
       "      <td>{{63170.08000000,7.02801000},{63171.50000000,0...</td>\n",
       "      <td>159.49</td>\n",
       "      <td>0.253</td>\n",
       "      <td>64181.254083</td>\n",
       "      <td>63008.00</td>\n",
       "      <td>63167.48</td>\n",
       "      <td>...</td>\n",
       "      <td>63007.99</td>\n",
       "      <td>65659.78</td>\n",
       "      <td>62432.9</td>\n",
       "      <td>37110.33633</td>\n",
       "      <td>2.381788e+09</td>\n",
       "      <td>1722537305023</td>\n",
       "      <td>1722623705023</td>\n",
       "      <td>3713111709</td>\n",
       "      <td>3715534057</td>\n",
       "      <td>2422349</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>62966.01</td>\n",
       "      <td>2024-08-02 18:40:10.304483</td>\n",
       "      <td>49610631825</td>\n",
       "      <td>{{62966.00000000,5.52480000},{62965.99000000,0...</td>\n",
       "      <td>{{62966.01000000,0.01027000},{62967.99000000,0...</td>\n",
       "      <td>30.01</td>\n",
       "      <td>0.048</td>\n",
       "      <td>64183.414380</td>\n",
       "      <td>62936.00</td>\n",
       "      <td>62966.01</td>\n",
       "      <td>...</td>\n",
       "      <td>62936.00</td>\n",
       "      <td>65659.78</td>\n",
       "      <td>62432.9</td>\n",
       "      <td>37047.03811</td>\n",
       "      <td>2.377805e+09</td>\n",
       "      <td>1722537604538</td>\n",
       "      <td>1722624004538</td>\n",
       "      <td>3713121147</td>\n",
       "      <td>3715542314</td>\n",
       "      <td>2421168</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>62966.0</td>\n",
       "      <td>2024-08-02 18:45:09.519664</td>\n",
       "      <td>49610954922</td>\n",
       "      <td>{{62964.00000000,0.01021000},{62963.99000000,0...</td>\n",
       "      <td>{{62964.01000000,1.27229000},{62964.02000000,0...</td>\n",
       "      <td>136.00</td>\n",
       "      <td>0.216</td>\n",
       "      <td>64190.577131</td>\n",
       "      <td>62828.00</td>\n",
       "      <td>62964.00</td>\n",
       "      <td>...</td>\n",
       "      <td>62828.00</td>\n",
       "      <td>65659.78</td>\n",
       "      <td>62432.9</td>\n",
       "      <td>36841.58210</td>\n",
       "      <td>2.364882e+09</td>\n",
       "      <td>1722537903951</td>\n",
       "      <td>1722624303951</td>\n",
       "      <td>3713134699</td>\n",
       "      <td>3715548938</td>\n",
       "      <td>2414240</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>62934.0</td>\n",
       "      <td>2024-08-02 18:50:11.269585</td>\n",
       "      <td>49611273338</td>\n",
       "      <td>{{62934.00000000,0.94725000},{62933.92000000,0...</td>\n",
       "      <td>{{62934.01000000,5.19318000},{62934.98000000,0...</td>\n",
       "      <td>78.01</td>\n",
       "      <td>0.124</td>\n",
       "      <td>64193.411499</td>\n",
       "      <td>62856.00</td>\n",
       "      <td>62934.00</td>\n",
       "      <td>...</td>\n",
       "      <td>62855.99</td>\n",
       "      <td>65659.78</td>\n",
       "      <td>62432.9</td>\n",
       "      <td>36770.09872</td>\n",
       "      <td>2.360398e+09</td>\n",
       "      <td>1722538205108</td>\n",
       "      <td>1722624605108</td>\n",
       "      <td>3713144919</td>\n",
       "      <td>3715555962</td>\n",
       "      <td>2411044</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>62938.0</td>\n",
       "      <td>2024-08-02 18:55:09.117454</td>\n",
       "      <td>49611635353</td>\n",
       "      <td>{{62938.00000000,4.47912000},{62937.99000000,0...</td>\n",
       "      <td>{{62938.01000000,6.12227000},{62938.02000000,0...</td>\n",
       "      <td>80.00</td>\n",
       "      <td>0.127</td>\n",
       "      <td>64193.336770</td>\n",
       "      <td>62858.00</td>\n",
       "      <td>62938.00</td>\n",
       "      <td>...</td>\n",
       "      <td>62858.00</td>\n",
       "      <td>65659.78</td>\n",
       "      <td>62432.9</td>\n",
       "      <td>36770.56930</td>\n",
       "      <td>2.360426e+09</td>\n",
       "      <td>1722538503736</td>\n",
       "      <td>1722624903736</td>\n",
       "      <td>3713152478</td>\n",
       "      <td>3715566254</td>\n",
       "      <td>2413777</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>62892.0</td>\n",
       "      <td>2024-08-02 19:00:14.019759</td>\n",
       "      <td>49611943991</td>\n",
       "      <td>{{62902.01000000,3.56695000},{62902.00000000,0...</td>\n",
       "      <td>{{62902.02000000,3.44814000},{62902.03000000,0...</td>\n",
       "      <td>-49.72</td>\n",
       "      <td>-0.079</td>\n",
       "      <td>64193.896449</td>\n",
       "      <td>62951.74</td>\n",
       "      <td>62902.02</td>\n",
       "      <td>...</td>\n",
       "      <td>62951.74</td>\n",
       "      <td>65659.78</td>\n",
       "      <td>62432.9</td>\n",
       "      <td>36755.67029</td>\n",
       "      <td>2.359490e+09</td>\n",
       "      <td>1722538805914</td>\n",
       "      <td>1722625205914</td>\n",
       "      <td>3713158366</td>\n",
       "      <td>3715572870</td>\n",
       "      <td>2414505</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>62777.0</td>\n",
       "      <td>2024-08-02 19:05:10.132720</td>\n",
       "      <td>49612346710</td>\n",
       "      <td>{{62775.99000000,3.53739000},{62775.65000000,0...</td>\n",
       "      <td>{{62776.00000000,0.08025000},{62776.01000000,0...</td>\n",
       "      <td>-416.99</td>\n",
       "      <td>-0.660</td>\n",
       "      <td>64194.037031</td>\n",
       "      <td>63193.99</td>\n",
       "      <td>62777.00</td>\n",
       "      <td>...</td>\n",
       "      <td>63193.99</td>\n",
       "      <td>65659.78</td>\n",
       "      <td>62432.9</td>\n",
       "      <td>36729.77152</td>\n",
       "      <td>2.357832e+09</td>\n",
       "      <td>1722539104979</td>\n",
       "      <td>1722625504979</td>\n",
       "      <td>3713168786</td>\n",
       "      <td>3715582005</td>\n",
       "      <td>2413220</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>62732.67</td>\n",
       "      <td>2024-08-02 19:10:11.109184</td>\n",
       "      <td>49612723666</td>\n",
       "      <td>{{62732.66000000,0.80254000},{62732.65000000,0...</td>\n",
       "      <td>{{62732.67000000,10.99952000},{62732.68000000,...</td>\n",
       "      <td>-457.33</td>\n",
       "      <td>-0.724</td>\n",
       "      <td>64191.298075</td>\n",
       "      <td>63190.00</td>\n",
       "      <td>62732.67</td>\n",
       "      <td>...</td>\n",
       "      <td>63190.00</td>\n",
       "      <td>65659.78</td>\n",
       "      <td>62432.9</td>\n",
       "      <td>36753.49466</td>\n",
       "      <td>2.359255e+09</td>\n",
       "      <td>1722539403520</td>\n",
       "      <td>1722625803520</td>\n",
       "      <td>3713176108</td>\n",
       "      <td>3715592925</td>\n",
       "      <td>2416818</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>10 rows × 25 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      price                  timestamp  lastupdateid  \\\n",
       "0  63173.99 2024-08-02 18:25:09.536314   49609604203   \n",
       "1   63068.0 2024-08-02 18:30:10.501358   49609925676   \n",
       "2  63167.48 2024-08-02 18:35:10.901813   49610238836   \n",
       "3  62966.01 2024-08-02 18:40:10.304483   49610631825   \n",
       "4   62966.0 2024-08-02 18:45:09.519664   49610954922   \n",
       "5   62934.0 2024-08-02 18:50:11.269585   49611273338   \n",
       "6   62938.0 2024-08-02 18:55:09.117454   49611635353   \n",
       "7   62892.0 2024-08-02 19:00:14.019759   49611943991   \n",
       "8   62777.0 2024-08-02 19:05:10.132720   49612346710   \n",
       "9  62732.67 2024-08-02 19:10:11.109184   49612723666   \n",
       "\n",
       "                                                bids  \\\n",
       "0  {{63172.00000000,6.37552000},{63171.99000000,0...   \n",
       "1  {{63068.00000000,0.63882000},{63067.97000000,0...   \n",
       "2  {{63170.07000000,0.85979000},{63170.01000000,0...   \n",
       "3  {{62966.00000000,5.52480000},{62965.99000000,0...   \n",
       "4  {{62964.00000000,0.01021000},{62963.99000000,0...   \n",
       "5  {{62934.00000000,0.94725000},{62933.92000000,0...   \n",
       "6  {{62938.00000000,4.47912000},{62937.99000000,0...   \n",
       "7  {{62902.01000000,3.56695000},{62902.00000000,0...   \n",
       "8  {{62775.99000000,3.53739000},{62775.65000000,0...   \n",
       "9  {{62732.66000000,0.80254000},{62732.65000000,0...   \n",
       "\n",
       "                                                asks  pricechange  \\\n",
       "0  {{63172.01000000,0.46704000},{63172.11000000,0...        51.99   \n",
       "1  {{63068.01000000,9.70944000},{63068.41000000,0...         0.00   \n",
       "2  {{63170.08000000,7.02801000},{63171.50000000,0...       159.49   \n",
       "3  {{62966.01000000,0.01027000},{62967.99000000,0...        30.01   \n",
       "4  {{62964.01000000,1.27229000},{62964.02000000,0...       136.00   \n",
       "5  {{62934.01000000,5.19318000},{62934.98000000,0...        78.01   \n",
       "6  {{62938.01000000,6.12227000},{62938.02000000,0...        80.00   \n",
       "7  {{62902.02000000,3.44814000},{62902.03000000,0...       -49.72   \n",
       "8  {{62776.00000000,0.08025000},{62776.01000000,0...      -416.99   \n",
       "9  {{62732.67000000,10.99952000},{62732.68000000,...      -457.33   \n",
       "\n",
       "   pricechangepercent  weightedavgprice  prevcloseprice  lastprice  ...  \\\n",
       "0               0.082      64177.913005        63122.01   63173.99  ...   \n",
       "1               0.000      64179.034035        63067.99   63068.00  ...   \n",
       "2               0.253      64181.254083        63008.00   63167.48  ...   \n",
       "3               0.048      64183.414380        62936.00   62966.01  ...   \n",
       "4               0.216      64190.577131        62828.00   62964.00  ...   \n",
       "5               0.124      64193.411499        62856.00   62934.00  ...   \n",
       "6               0.127      64193.336770        62858.00   62938.00  ...   \n",
       "7              -0.079      64193.896449        62951.74   62902.02  ...   \n",
       "8              -0.660      64194.037031        63193.99   62777.00  ...   \n",
       "9              -0.724      64191.298075        63190.00   62732.67  ...   \n",
       "\n",
       "   openprice  highprice  lowprice       volume   quotevolume       opentime  \\\n",
       "0   63122.00   65659.78   62432.9  37206.07340  2.387808e+09  1722536704040   \n",
       "1   63068.00   65659.78   62432.9  37171.37937  2.385623e+09  1722537004310   \n",
       "2   63007.99   65659.78   62432.9  37110.33633  2.381788e+09  1722537305023   \n",
       "3   62936.00   65659.78   62432.9  37047.03811  2.377805e+09  1722537604538   \n",
       "4   62828.00   65659.78   62432.9  36841.58210  2.364882e+09  1722537903951   \n",
       "5   62855.99   65659.78   62432.9  36770.09872  2.360398e+09  1722538205108   \n",
       "6   62858.00   65659.78   62432.9  36770.56930  2.360426e+09  1722538503736   \n",
       "7   62951.74   65659.78   62432.9  36755.67029  2.359490e+09  1722538805914   \n",
       "8   63193.99   65659.78   62432.9  36729.77152  2.357832e+09  1722539104979   \n",
       "9   63190.00   65659.78   62432.9  36753.49466  2.359255e+09  1722539403520   \n",
       "\n",
       "       closetime     firstid      lastid    count  \n",
       "0  1722623104040  3713093603  3715520420  2426818  \n",
       "1  1722623404310  3713102305  3715527226  2424922  \n",
       "2  1722623705023  3713111709  3715534057  2422349  \n",
       "3  1722624004538  3713121147  3715542314  2421168  \n",
       "4  1722624303951  3713134699  3715548938  2414240  \n",
       "5  1722624605108  3713144919  3715555962  2411044  \n",
       "6  1722624903736  3713152478  3715566254  2413777  \n",
       "7  1722625205914  3713158366  3715572870  2414505  \n",
       "8  1722625504979  3713168786  3715582005  2413220  \n",
       "9  1722625803520  3713176108  3715592925  2416818  \n",
       "\n",
       "[10 rows x 25 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged_df[:10]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2b5bd765-39a1-4ca0-95e8-c9206a03ba95",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.10.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}