Community alerts .ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "cd3a43c2",
   "metadata": {},
   "outputs": [],
   "source": [
    "#standard deviation\n",
    "#t-test\n",
    "#seperate functions to find rolling means and calculating peaks\n",
    "#significant peaks to be excluded from calculating means but should be shown as peaks in the logs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "61ee8b06",
   "metadata": {},
   "outputs": [],
   "source": [
    "import requests\n",
    "import pandas as pd\n",
    "import plotly.graph_objects as go\n",
    "import plotly.express as px\n",
    "import duckdb"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "c6caa2dd",
   "metadata": {},
   "outputs": [],
   "source": [
    "def generate_dataframe(edit_counts):\n",
    "    # Convert data to a pandas DataFrame\n",
    "    df = pd.DataFrame(edit_counts)\n",
    "    df['timestamp'] = pd.to_datetime(df['timestamp'])\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "d59227df",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Function to identify peaks based on the rolling mean of the past 3 years\n",
    "def find_peaks_rolling_3_years(df, threshold_percentage=0.50):\n",
    "    peaks = []\n",
    "    \n",
    "    # Iterate over each timestamp\n",
    "    for i in range(len(df)):  # Start from the first element\n",
    "        # Get data within the last 3 years\n",
    "        past_3_years_data = df[df['timestamp'] <= df['timestamp'][i]]\n",
    "        past_3_years_data = past_3_years_data[past_3_years_data['timestamp'] >= (df['timestamp'][i] - pd.DateOffset(years=3))]\n",
    "\n",
    "        # Calculate the rolling mean of the last 3 years (average of 'edits' in the past 3 years)\n",
    "        rolling_mean = past_3_years_data['edits'].mean()\n",
    "\n",
    "        # Calculate the threshold (mean + 40%)\n",
    "        threshold = rolling_mean * (1 + threshold_percentage)\n",
    "        \n",
    "        #calculate percenrage difference\n",
    "        percentage_difference = (df[\"edits\"][i]-rolling_mean)*100/rolling_mean\n",
    "        \n",
    "        # Check if the current value is above the threshold\n",
    "        if df['edits'][i] >= threshold:\n",
    "            peaks.append((df['timestamp'][i], df['edits'][i], rolling_mean, threshold, percentage_difference))\n",
    "\n",
    "    return peaks"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "7faf2c19",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Log peaks (timestamp, edits, rolling mean, threshold)\n",
    "def log_peaks(peaks):\n",
    "    for peak in peaks:\n",
    "        print(f\"Peak: {peak[0].strftime('%Y-%m-%d')}, Edits: {peak[1]}, Rolling Mean: {peak[2]:.2f}, Threshold: {peak[3]:.2f}, percentage difference : {peak[4]: .2f}\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "839c691a",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Peak: 2020-03-01, Edits: 5272, Rolling Mean: 3354.67, Threshold: 4361.07, percentage difference :  57.15\n",
      "Peak: 2020-04-01, Edits: 5760, Rolling Mean: 3956.00, Threshold: 5142.80, percentage difference :  45.60\n",
      "Peak: 2020-12-01, Edits: 5525, Rolling Mean: 4100.00, Threshold: 5330.00, percentage difference :  34.76\n",
      "Peak: 2021-01-01, Edits: 12253, Rolling Mean: 4727.15, Threshold: 6145.30, percentage difference :  159.20\n",
      "Peak: 2021-07-01, Edits: 8814, Rolling Mean: 4579.53, Threshold: 5953.38, percentage difference :  92.47\n",
      "Peak: 2021-08-01, Edits: 14835, Rolling Mean: 5092.30, Threshold: 6619.99, percentage difference :  191.32\n",
      "Peak: 2021-09-01, Edits: 65605, Rolling Mean: 7973.86, Threshold: 10366.01, percentage difference :  722.75\n",
      "Peak: 2021-10-01, Edits: 59519, Rolling Mean: 10316.82, Threshold: 13411.86, percentage difference :  476.91\n",
      "Peak: 2021-11-01, Edits: 31284, Rolling Mean: 11228.43, Threshold: 14596.97, percentage difference :  178.61\n",
      "Peak: 2021-12-01, Edits: 51113, Rolling Mean: 12890.29, Threshold: 16757.38, percentage difference :  296.52\n",
      "Peak: 2022-01-01, Edits: 27170, Rolling Mean: 13461.48, Threshold: 17499.92, percentage difference :  101.84\n",
      "Peak: 2022-02-01, Edits: 22061, Rolling Mean: 13792.23, Threshold: 17929.90, percentage difference :  59.95\n",
      "Peak: 2022-05-01, Edits: 60142, Rolling Mean: 14882.17, Threshold: 19346.82, percentage difference :  304.12\n",
      "Peak: 2022-06-01, Edits: 59377, Rolling Mean: 16365.33, Threshold: 21274.93, percentage difference :  262.82\n",
      "Peak: 2022-07-01, Edits: 31324, Rolling Mean: 16847.87, Threshold: 21902.23, percentage difference :  85.92\n",
      "Peak: 2022-08-01, Edits: 64422, Rolling Mean: 18334.56, Threshold: 23834.93, percentage difference :  251.37\n",
      "Peak: 2022-09-01, Edits: 78391, Rolling Mean: 20154.45, Threshold: 26200.79, percentage difference :  288.95\n",
      "Peak: 2022-10-01, Edits: 172441, Rolling Mean: 24633.47, Threshold: 32023.51, percentage difference :  600.03\n",
      "Peak: 2022-11-01, Edits: 205361, Rolling Mean: 29797.11, Threshold: 38736.25, percentage difference :  589.20\n",
      "Peak: 2022-12-01, Edits: 54768, Rolling Mean: 30490.75, Threshold: 39637.97, percentage difference :  79.62\n"
     ]
    }
   ],
   "source": [
    "base_url = \"https://wikimedia.org/api/rest_v1/metrics/edits/aggregate\"\n",
    "\n",
    "project = \"uz.wikipedia.org\"      \n",
    "editor_type = \"all-editor-types\"  \n",
    "page_type = \"content\"      \n",
    "granularity = \"monthly\"           \n",
    "start = \"20200101\"                \n",
    "end = \"20240101\"  \n",
    "\n",
    "rolling_window = 3\n",
    "# Set the threshold percentage (30%)\n",
    "threshold_percentage = 0.30\n",
    "\n",
    "\n",
    "url = f\"{base_url}/{project}/{editor_type}/{page_type}/{granularity}/{start}/{end}\"\n",
    "\n",
    "response = requests.get(url)\n",
    "\n",
    "\n",
    "if response.status_code == 200:\n",
    "    data = response.json()\n",
    "    #print(\"Number of edits data:\")\n",
    "    #print(data)\n",
    "else:\n",
    "    print(f\"Error: {response.status_code} - {response.text}\")\n",
    "    \n",
    "    \n",
    "edit_counts=data[\"items\"][0][\"results\"]\n",
    "\n",
    "df = generate_dataframe(edit_counts)\n",
    "peaks = find_peaks_rolling_3_years(df, threshold_percentage)\n",
    "log_peaks(peaks)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "cfea26bb",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.plotly.v1+json": {
       "config": {
        "plotlyServerURL": "https://plot.ly"
       },
       "data": [
        {
         "line": {
          "color": "blue"
         },
         "mode": "lines+markers",
         "name": "Edits",
         "type": "scatter",
         "x": [
          "2020-01-01T00:00:00+00:00",
          "2020-02-01T00:00:00+00:00",
          "2020-03-01T00:00:00+00:00",
          "2020-04-01T00:00:00+00:00",
          "2020-05-01T00:00:00+00:00",
          "2020-06-01T00:00:00+00:00",
          "2020-07-01T00:00:00+00:00",
          "2020-08-01T00:00:00+00:00",
          "2020-09-01T00:00:00+00:00",
          "2020-10-01T00:00:00+00:00",
          "2020-11-01T00:00:00+00:00",
          "2020-12-01T00:00:00+00:00",
          "2021-01-01T00:00:00+00:00",
          "2021-02-01T00:00:00+00:00",
          "2021-03-01T00:00:00+00:00",
          "2021-04-01T00:00:00+00:00",
          "2021-05-01T00:00:00+00:00",
          "2021-06-01T00:00:00+00:00",
          "2021-07-01T00:00:00+00:00",
          "2021-08-01T00:00:00+00:00",
          "2021-09-01T00:00:00+00:00",
          "2021-10-01T00:00:00+00:00",
          "2021-11-01T00:00:00+00:00",
          "2021-12-01T00:00:00+00:00",
          "2022-01-01T00:00:00+00:00",
          "2022-02-01T00:00:00+00:00",
          "2022-03-01T00:00:00+00:00",
          "2022-04-01T00:00:00+00:00",
          "2022-05-01T00:00:00+00:00",
          "2022-06-01T00:00:00+00:00",
          "2022-07-01T00:00:00+00:00",
          "2022-08-01T00:00:00+00:00",
          "2022-09-01T00:00:00+00:00",
          "2022-10-01T00:00:00+00:00",
          "2022-11-01T00:00:00+00:00",
          "2022-12-01T00:00:00+00:00",
          "2023-01-01T00:00:00+00:00",
          "2023-02-01T00:00:00+00:00",
          "2023-03-01T00:00:00+00:00",
          "2023-04-01T00:00:00+00:00",
          "2023-05-01T00:00:00+00:00",
          "2023-06-01T00:00:00+00:00",
          "2023-07-01T00:00:00+00:00",
          "2023-08-01T00:00:00+00:00",
          "2023-09-01T00:00:00+00:00",
          "2023-10-01T00:00:00+00:00",
          "2023-11-01T00:00:00+00:00",
          "2023-12-01T00:00:00+00:00"
         ],
         "y": [
          1689,
          3103,
          5272,
          5760,
          3551,
          3423,
          3237,
          4448,
          4474,
          5131,
          3587,
          5525,
          12253,
          5822,
          3185,
          3542,
          2485,
          1710,
          8814,
          14835,
          65605,
          59519,
          31284,
          51113,
          27170,
          22061,
          5021,
          7822,
          60142,
          59377,
          31324,
          64422,
          78391,
          172441,
          205361,
          54768,
          23169,
          16459,
          26661,
          36088,
          40786,
          34712,
          19467,
          25930,
          29898,
          17902,
          31152,
          15388
         ]
        },
        {
         "marker": {
          "color": "red",
          "size": 10,
          "symbol": "circle"
         },
         "mode": "markers",
         "name": "Peaks Above Threshold",
         "type": "scatter",
         "x": [
          "2020-03-01T00:00:00+00:00",
          "2020-04-01T00:00:00+00:00",
          "2020-12-01T00:00:00+00:00",
          "2021-01-01T00:00:00+00:00",
          "2021-07-01T00:00:00+00:00",
          "2021-08-01T00:00:00+00:00",
          "2021-09-01T00:00:00+00:00",
          "2021-10-01T00:00:00+00:00",
          "2021-11-01T00:00:00+00:00",
          "2021-12-01T00:00:00+00:00",
          "2022-01-01T00:00:00+00:00",
          "2022-02-01T00:00:00+00:00",
          "2022-05-01T00:00:00+00:00",
          "2022-06-01T00:00:00+00:00",
          "2022-07-01T00:00:00+00:00",
          "2022-08-01T00:00:00+00:00",
          "2022-09-01T00:00:00+00:00",
          "2022-10-01T00:00:00+00:00",
          "2022-11-01T00:00:00+00:00",
          "2022-12-01T00:00:00+00:00"
         ],
         "y": [
          5272,
          5760,
          5525,
          12253,
          8814,
          14835,
          65605,
          59519,
          31284,
          51113,
          27170,
          22061,
          60142,
          59377,
          31324,
          64422,
          78391,
          172441,
          205361,
          54768
         ]
        }
       ],
       "layout": {
        "showlegend": true,
        "template": {
         "data": {
          "bar": [
           {
            "error_x": {
             "color": "#2a3f5f"
            },
            "error_y": {
             "color": "#2a3f5f"
            },
            "marker": {
             "line": {
              "color": "#E5ECF6",
              "width": 0.5
             },
             "pattern": {
              "fillmode": "overlay",
              "size": 10,
              "solidity": 0.2
             }
            },
            "type": "bar"
           }
          ],
          "barpolar": [
           {
            "marker": {
             "line": {
              "color": "#E5ECF6",
              "width": 0.5
             },
             "pattern": {
              "fillmode": "overlay",
              "size": 10,
              "solidity": 0.2
             }
            },
            "type": "barpolar"
           }
          ],
          "carpet": [
           {
            "aaxis": {
             "endlinecolor": "#2a3f5f",
             "gridcolor": "white",
             "linecolor": "white",
             "minorgridcolor": "white",
             "startlinecolor": "#2a3f5f"
            },
            "baxis": {
             "endlinecolor": "#2a3f5f",
             "gridcolor": "white",
             "linecolor": "white",
             "minorgridcolor": "white",
             "startlinecolor": "#2a3f5f"
            },
            "type": "carpet"
           }
          ],
          "choropleth": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "type": "choropleth"
           }
          ],
          "contour": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "colorscale": [
             [
              0,
              "#0d0887"
             ],
             [
              0.1111111111111111,
              "#46039f"
             ],
             [
              0.2222222222222222,
              "#7201a8"
             ],
             [
              0.3333333333333333,
              "#9c179e"
             ],
             [
              0.4444444444444444,
              "#bd3786"
             ],
             [
              0.5555555555555556,
              "#d8576b"
             ],
             [
              0.6666666666666666,
              "#ed7953"
             ],
             [
              0.7777777777777778,
              "#fb9f3a"
             ],
             [
              0.8888888888888888,
              "#fdca26"
             ],
             [
              1,
              "#f0f921"
             ]
            ],
            "type": "contour"
           }
          ],
          "contourcarpet": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "type": "contourcarpet"
           }
          ],
          "heatmap": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "colorscale": [
             [
              0,
              "#0d0887"
             ],
             [
              0.1111111111111111,
              "#46039f"
             ],
             [
              0.2222222222222222,
              "#7201a8"
             ],
             [
              0.3333333333333333,
              "#9c179e"
             ],
             [
              0.4444444444444444,
              "#bd3786"
             ],
             [
              0.5555555555555556,
              "#d8576b"
             ],
             [
              0.6666666666666666,
              "#ed7953"
             ],
             [
              0.7777777777777778,
              "#fb9f3a"
             ],
             [
              0.8888888888888888,
              "#fdca26"
             ],
             [
              1,
              "#f0f921"
             ]
            ],
            "type": "heatmap"
           }
          ],
          "heatmapgl": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "colorscale": [
             [
              0,
              "#0d0887"
             ],
             [
              0.1111111111111111,
              "#46039f"
             ],
             [
              0.2222222222222222,
              "#7201a8"
             ],
             [
              0.3333333333333333,
              "#9c179e"
             ],
             [
              0.4444444444444444,
              "#bd3786"
             ],
             [
              0.5555555555555556,
              "#d8576b"
             ],
             [
              0.6666666666666666,
              "#ed7953"
             ],
             [
              0.7777777777777778,
              "#fb9f3a"
             ],
             [
              0.8888888888888888,
              "#fdca26"
             ],
             [
              1,
              "#f0f921"
             ]
            ],
            "type": "heatmapgl"
           }
          ],
          "histogram": [
           {
            "marker": {
             "pattern": {
              "fillmode": "overlay",
              "size": 10,
              "solidity": 0.2
             }
            },
            "type": "histogram"
           }
          ],
          "histogram2d": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "colorscale": [
             [
              0,
              "#0d0887"
             ],
             [
              0.1111111111111111,
              "#46039f"
             ],
             [
              0.2222222222222222,
              "#7201a8"
             ],
             [
              0.3333333333333333,
              "#9c179e"
             ],
             [
              0.4444444444444444,
              "#bd3786"
             ],
             [
              0.5555555555555556,
              "#d8576b"
             ],
             [
              0.6666666666666666,
              "#ed7953"
             ],
             [
              0.7777777777777778,
              "#fb9f3a"
             ],
             [
              0.8888888888888888,
              "#fdca26"
             ],
             [
              1,
              "#f0f921"
             ]
            ],
            "type": "histogram2d"
           }
          ],
          "histogram2dcontour": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "colorscale": [
             [
              0,
              "#0d0887"
             ],
             [
              0.1111111111111111,
              "#46039f"
             ],
             [
              0.2222222222222222,
              "#7201a8"
             ],
             [
              0.3333333333333333,
              "#9c179e"
             ],
             [
              0.4444444444444444,
              "#bd3786"
             ],
             [
              0.5555555555555556,
              "#d8576b"
             ],
             [
              0.6666666666666666,
              "#ed7953"
             ],
             [
              0.7777777777777778,
              "#fb9f3a"
             ],
             [
              0.8888888888888888,
              "#fdca26"
             ],
             [
              1,
              "#f0f921"
             ]
            ],
            "type": "histogram2dcontour"
           }
          ],
          "mesh3d": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "type": "mesh3d"
           }
          ],
          "parcoords": [
           {
            "line": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "parcoords"
           }
          ],
          "pie": [
           {
            "automargin": true,
            "type": "pie"
           }
          ],
          "scatter": [
           {
            "fillpattern": {
             "fillmode": "overlay",
             "size": 10,
             "solidity": 0.2
            },
            "type": "scatter"
           }
          ],
          "scatter3d": [
           {
            "line": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scatter3d"
           }
          ],
          "scattercarpet": [
           {
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scattercarpet"
           }
          ],
          "scattergeo": [
           {
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scattergeo"
           }
          ],
          "scattergl": [
           {
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scattergl"
           }
          ],
          "scattermapbox": [
           {
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scattermapbox"
           }
          ],
          "scatterpolar": [
           {
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scatterpolar"
           }
          ],
          "scatterpolargl": [
           {
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scatterpolargl"
           }
          ],
          "scatterternary": [
           {
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scatterternary"
           }
          ],
          "surface": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "colorscale": [
             [
              0,
              "#0d0887"
             ],
             [
              0.1111111111111111,
              "#46039f"
             ],
             [
              0.2222222222222222,
              "#7201a8"
             ],
             [
              0.3333333333333333,
              "#9c179e"
             ],
             [
              0.4444444444444444,
              "#bd3786"
             ],
             [
              0.5555555555555556,
              "#d8576b"
             ],
             [
              0.6666666666666666,
              "#ed7953"
             ],
             [
              0.7777777777777778,
              "#fb9f3a"
             ],
             [
              0.8888888888888888,
              "#fdca26"
             ],
             [
              1,
              "#f0f921"
             ]
            ],
            "type": "surface"
           }
          ],
          "table": [
           {
            "cells": {
             "fill": {
              "color": "#EBF0F8"
             },
             "line": {
              "color": "white"
             }
            },
            "header": {
             "fill": {
              "color": "#C8D4E3"
             },
             "line": {
              "color": "white"
             }
            },
            "type": "table"
           }
          ]
         },
         "layout": {
          "annotationdefaults": {
           "arrowcolor": "#2a3f5f",
           "arrowhead": 0,
           "arrowwidth": 1
          },
          "autotypenumbers": "strict",
          "coloraxis": {
           "colorbar": {
            "outlinewidth": 0,
            "ticks": ""
           }
          },
          "colorscale": {
           "diverging": [
            [
             0,
             "#8e0152"
            ],
            [
             0.1,
             "#c51b7d"
            ],
            [
             0.2,
             "#de77ae"
            ],
            [
             0.3,
             "#f1b6da"
            ],
            [
             0.4,
             "#fde0ef"
            ],
            [
             0.5,
             "#f7f7f7"
            ],
            [
             0.6,
             "#e6f5d0"
            ],
            [
             0.7,
             "#b8e186"
            ],
            [
             0.8,
             "#7fbc41"
            ],
            [
             0.9,
             "#4d9221"
            ],
            [
             1,
             "#276419"
            ]
           ],
           "sequential": [
            [
             0,
             "#0d0887"
            ],
            [
             0.1111111111111111,
             "#46039f"
            ],
            [
             0.2222222222222222,
             "#7201a8"
            ],
            [
             0.3333333333333333,
             "#9c179e"
            ],
            [
             0.4444444444444444,
             "#bd3786"
            ],
            [
             0.5555555555555556,
             "#d8576b"
            ],
            [
             0.6666666666666666,
             "#ed7953"
            ],
            [
             0.7777777777777778,
             "#fb9f3a"
            ],
            [
             0.8888888888888888,
             "#fdca26"
            ],
            [
             1,
             "#f0f921"
            ]
           ],
           "sequentialminus": [
            [
             0,
             "#0d0887"
            ],
            [
             0.1111111111111111,
             "#46039f"
            ],
            [
             0.2222222222222222,
             "#7201a8"
            ],
            [
             0.3333333333333333,
             "#9c179e"
            ],
            [
             0.4444444444444444,
             "#bd3786"
            ],
            [
             0.5555555555555556,
             "#d8576b"
            ],
            [
             0.6666666666666666,
             "#ed7953"
            ],
            [
             0.7777777777777778,
             "#fb9f3a"
            ],
            [
             0.8888888888888888,
             "#fdca26"
            ],
            [
             1,
             "#f0f921"
            ]
           ]
          },
          "colorway": [
           "#636efa",
           "#EF553B",
           "#00cc96",
           "#ab63fa",
           "#FFA15A",
           "#19d3f3",
           "#FF6692",
           "#B6E880",
           "#FF97FF",
           "#FECB52"
          ],
          "font": {
           "color": "#2a3f5f"
          },
          "geo": {
           "bgcolor": "white",
           "lakecolor": "white",
           "landcolor": "#E5ECF6",
           "showlakes": true,
           "showland": true,
           "subunitcolor": "white"
          },
          "hoverlabel": {
           "align": "left"
          },
          "hovermode": "closest",
          "mapbox": {
           "style": "light"
          },
          "paper_bgcolor": "white",
          "plot_bgcolor": "#E5ECF6",
          "polar": {
           "angularaxis": {
            "gridcolor": "white",
            "linecolor": "white",
            "ticks": ""
           },
           "bgcolor": "#E5ECF6",
           "radialaxis": {
            "gridcolor": "white",
            "linecolor": "white",
            "ticks": ""
           }
          },
          "scene": {
           "xaxis": {
            "backgroundcolor": "#E5ECF6",
            "gridcolor": "white",
            "gridwidth": 2,
            "linecolor": "white",
            "showbackground": true,
            "ticks": "",
            "zerolinecolor": "white"
           },
           "yaxis": {
            "backgroundcolor": "#E5ECF6",
            "gridcolor": "white",
            "gridwidth": 2,
            "linecolor": "white",
            "showbackground": true,
            "ticks": "",
            "zerolinecolor": "white"
           },
           "zaxis": {
            "backgroundcolor": "#E5ECF6",
            "gridcolor": "white",
            "gridwidth": 2,
            "linecolor": "white",
            "showbackground": true,
            "ticks": "",
            "zerolinecolor": "white"
           }
          },
          "shapedefaults": {
           "line": {
            "color": "#2a3f5f"
           }
          },
          "ternary": {
           "aaxis": {
            "gridcolor": "white",
            "linecolor": "white",
            "ticks": ""
           },
           "baxis": {
            "gridcolor": "white",
            "linecolor": "white",
            "ticks": ""
           },
           "bgcolor": "#E5ECF6",
           "caxis": {
            "gridcolor": "white",
            "linecolor": "white",
            "ticks": ""
           }
          },
          "title": {
           "x": 0.05
          },
          "xaxis": {
           "automargin": true,
           "gridcolor": "white",
           "linecolor": "white",
           "ticks": "",
           "title": {
            "standoff": 15
           },
           "zerolinecolor": "white",
           "zerolinewidth": 2
          },
          "yaxis": {
           "automargin": true,
           "gridcolor": "white",
           "linecolor": "white",
           "ticks": "",
           "title": {
            "standoff": 15
           },
           "zerolinecolor": "white",
           "zerolinewidth": 2
          }
         }
        },
        "title": {
         "text": "Edits count over time with peaks over a threshold(30%) with rolling mean of 3 years"
        },
        "xaxis": {
         "tickangle": 45,
         "tickformat": "%Y-%m-%d",
         "title": {
          "text": "Timestamp"
         }
        },
        "yaxis": {
         "title": {
          "text": "Count(Edits)"
         }
        }
       }
      },
      "text/html": [
       "<div>                            <div id=\"03914ca6-5fdd-42ce-a5bb-cac80d161102\" class=\"plotly-graph-div\" style=\"height:525px; width:100%;\"></div>            <script type=\"text/javascript\">                require([\"plotly\"], function(Plotly) {                    window.PLOTLYENV=window.PLOTLYENV || {};                                    if (document.getElementById(\"03914ca6-5fdd-42ce-a5bb-cac80d161102\")) {                    Plotly.newPlot(                        \"03914ca6-5fdd-42ce-a5bb-cac80d161102\",                        [{\"line\":{\"color\":\"blue\"},\"mode\":\"lines+markers\",\"name\":\"Edits\",\"x\":[\"2020-01-01T00:00:00+00:00\",\"2020-02-01T00:00:00+00:00\",\"2020-03-01T00:00:00+00:00\",\"2020-04-01T00:00:00+00:00\",\"2020-05-01T00:00:00+00:00\",\"2020-06-01T00:00:00+00:00\",\"2020-07-01T00:00:00+00:00\",\"2020-08-01T00:00:00+00:00\",\"2020-09-01T00:00:00+00:00\",\"2020-10-01T00:00:00+00:00\",\"2020-11-01T00:00:00+00:00\",\"2020-12-01T00:00:00+00:00\",\"2021-01-01T00:00:00+00:00\",\"2021-02-01T00:00:00+00:00\",\"2021-03-01T00:00:00+00:00\",\"2021-04-01T00:00:00+00:00\",\"2021-05-01T00:00:00+00:00\",\"2021-06-01T00:00:00+00:00\",\"2021-07-01T00:00:00+00:00\",\"2021-08-01T00:00:00+00:00\",\"2021-09-01T00:00:00+00:00\",\"2021-10-01T00:00:00+00:00\",\"2021-11-01T00:00:00+00:00\",\"2021-12-01T00:00:00+00:00\",\"2022-01-01T00:00:00+00:00\",\"2022-02-01T00:00:00+00:00\",\"2022-03-01T00:00:00+00:00\",\"2022-04-01T00:00:00+00:00\",\"2022-05-01T00:00:00+00:00\",\"2022-06-01T00:00:00+00:00\",\"2022-07-01T00:00:00+00:00\",\"2022-08-01T00:00:00+00:00\",\"2022-09-01T00:00:00+00:00\",\"2022-10-01T00:00:00+00:00\",\"2022-11-01T00:00:00+00:00\",\"2022-12-01T00:00:00+00:00\",\"2023-01-01T00:00:00+00:00\",\"2023-02-01T00:00:00+00:00\",\"2023-03-01T00:00:00+00:00\",\"2023-04-01T00:00:00+00:00\",\"2023-05-01T00:00:00+00:00\",\"2023-06-01T00:00:00+00:00\",\"2023-07-01T00:00:00+00:00\",\"2023-08-01T00:00:00+00:00\",\"2023-09-01T00:00:00+00:00\",\"2023-10-01T00:00:00+00:00\",\"2023-11-01T00:00:00+00:00\",\"2023-12-01T00:00:00+00:00\"],\"y\":[1689,3103,5272,5760,3551,3423,3237,4448,4474,5131,3587,5525,12253,5822,3185,3542,2485,1710,8814,14835,65605,59519,31284,51113,27170,22061,5021,7822,60142,59377,31324,64422,78391,172441,205361,54768,23169,16459,26661,36088,40786,34712,19467,25930,29898,17902,31152,15388],\"type\":\"scatter\"},{\"marker\":{\"color\":\"red\",\"size\":10,\"symbol\":\"circle\"},\"mode\":\"markers\",\"name\":\"Peaks Above Threshold\",\"x\":[\"2020-03-01T00:00:00+00:00\",\"2020-04-01T00:00:00+00:00\",\"2020-12-01T00:00:00+00:00\",\"2021-01-01T00:00:00+00:00\",\"2021-07-01T00:00:00+00:00\",\"2021-08-01T00:00:00+00:00\",\"2021-09-01T00:00:00+00:00\",\"2021-10-01T00:00:00+00:00\",\"2021-11-01T00:00:00+00:00\",\"2021-12-01T00:00:00+00:00\",\"2022-01-01T00:00:00+00:00\",\"2022-02-01T00:00:00+00:00\",\"2022-05-01T00:00:00+00:00\",\"2022-06-01T00:00:00+00:00\",\"2022-07-01T00:00:00+00:00\",\"2022-08-01T00:00:00+00:00\",\"2022-09-01T00:00:00+00:00\",\"2022-10-01T00:00:00+00:00\",\"2022-11-01T00:00:00+00:00\",\"2022-12-01T00:00:00+00:00\"],\"y\":[5272,5760,5525,12253,8814,14835,65605,59519,31284,51113,27170,22061,60142,59377,31324,64422,78391,172441,205361,54768],\"type\":\"scatter\"}],                        {\"template\":{\"data\":{\"histogram2dcontour\":[{\"type\":\"histogram2dcontour\",\"colorbar\":{\"outlinewidth\":0,\"ticks\":\"\"},\"colorscale\":[[0.0,\"#0d0887\"],[0.1111111111111111,\"#46039f\"],[0.2222222222222222,\"#7201a8\"],[0.3333333333333333,\"#9c179e\"],[0.4444444444444444,\"#bd3786\"],[0.5555555555555556,\"#d8576b\"],[0.6666666666666666,\"#ed7953\"],[0.7777777777777778,\"#fb9f3a\"],[0.8888888888888888,\"#fdca26\"],[1.0,\"#f0f921\"]]}],\"choropleth\":[{\"type\":\"choropleth\",\"colorbar\":{\"outlinewidth\":0,\"ticks\":\"\"}}],\"histogram2d\":[{\"type\":\"histogram2d\",\"colorbar\":{\"outlinewidth\":0,\"ticks\":\"\"},\"colorscale\":[[0.0,\"#0d0887\"],[0.1111111111111111,\"#46039f\"],[0.2222222222222222,\"#7201a8\"],[0.3333333333333333,\"#9c179e\"],[0.4444444444444444,\"#bd3786\"],[0.5555555555555556,\"#d8576b\"],[0.6666666666666666,\"#ed7953\"],[0.7777777777777778,\"#fb9f3a\"],[0.8888888888888888,\"#fdca26\"],[1.0,\"#f0f921\"]]}],\"heatmap\":[{\"type\":\"heatmap\",\"colorbar\":{\"outlinewidth\":0,\"ticks\":\"\"},\"colorscale\":[[0.0,\"#0d0887\"],[0.1111111111111111,\"#46039f\"],[0.2222222222222222,\"#7201a8\"],[0.3333333333333333,\"#9c179e\"],[0.4444444444444444,\"#bd3786\"],[0.5555555555555556,\"#d8576b\"],[0.6666666666666666,\"#ed7953\"],[0.7777777777777778,\"#fb9f3a\"],[0.8888888888888888,\"#fdca26\"],[1.0,\"#f0f921\"]]}],\"heatmapgl\":[{\"type\":\"heatmapgl\",\"colorbar\":{\"outlinewidth\":0,\"ticks\":\"\"},\"colorscale\":[[0.0,\"#0d0887\"],[0.1111111111111111,\"#46039f\"],[0.2222222222222222,\"#7201a8\"],[0.3333333333333333,\"#9c179e\"],[0.4444444444444444,\"#bd3786\"],[0.5555555555555556,\"#d8576b\"],[0.6666666666666666,\"#ed7953\"],[0.7777777777777778,\"#fb9f3a\"],[0.8888888888888888,\"#fdca26\"],[1.0,\"#f0f921\"]]}],\"contourcarpet\":[{\"type\":\"contourcarpet\",\"colorbar\":{\"outlinewidth\":0,\"ticks\":\"\"}}],\"contour\":[{\"type\":\"contour\",\"colorbar\":{\"outlinewidth\":0,\"ticks\":\"\"},\"colorscale\":[[0.0,\"#0d0887\"],[0.1111111111111111,\"#46039f\"],[0.2222222222222222,\"#7201a8\"],[0.3333333333333333,\"#9c179e\"],[0.4444444444444444,\"#bd3786\"],[0.5555555555555556,\"#d8576b\"],[0.6666666666666666,\"#ed7953\"],[0.7777777777777778,\"#fb9f3a\"],[0.8888888888888888,\"#fdca26\"],[1.0,\"#f0f921\"]]}],\"surface\":[{\"type\":\"surface\",\"colorbar\":{\"outlinewidth\":0,\"ticks\":\"\"},\"colorscale\":[[0.0,\"#0d0887\"],[0.1111111111111111,\"#46039f\"],[0.2222222222222222,\"#7201a8\"],[0.3333333333333333,\"#9c179e\"],[0.4444444444444444,\"#bd3786\"],[0.5555555555555556,\"#d8576b\"],[0.6666666666666666,\"#ed7953\"],[0.7777777777777778,\"#fb9f3a\"],[0.8888888888888888,\"#fdca26\"],[1.0,\"#f0f921\"]]}],\"mesh3d\":[{\"type\":\"mesh3d\",\"colorbar\":{\"outlinewidth\":0,\"ticks\":\"\"}}],\"scatter\":[{\"fillpattern\":{\"fillmode\":\"overlay\",\"size\":10,\"solidity\":0.2},\"type\":\"scatter\"}],\"parcoords\":[{\"type\":\"parcoords\",\"line\":{\"colorbar\":{\"outlinewidth\":0,\"ticks\":\"\"}}}],\"scatterpolargl\":[{\"type\":\"scatterpolargl\",\"marker\":{\"colorbar\":{\"outlinewidth\":0,\"ticks\":\"\"}}}],\"bar\":[{\"error_x\":{\"color\":\"#2a3f5f\"},\"error_y\":{\"color\":\"#2a3f5f\"},\"marker\":{\"line\":{\"color\":\"#E5ECF6\",\"width\":0.5},\"pattern\":{\"fillmode\":\"overlay\",\"size\":10,\"solidity\":0.2}},\"type\":\"bar\"}],\"scattergeo\":[{\"type\":\"scattergeo\",\"marker\":{\"colorbar\":{\"outlinewidth\":0,\"ticks\":\"\"}}}],\"scatterpolar\":[{\"type\":\"scatterpolar\",\"marker\":{\"colorbar\":{\"outlinewidth\":0,\"ticks\":\"\"}}}],\"histogram\":[{\"marker\":{\"pattern\":{\"fillmode\":\"overlay\",\"size\":10,\"solidity\":0.2}},\"type\":\"histogram\"}],\"scattergl\":[{\"type\":\"scattergl\",\"marker\":{\"colorbar\":{\"outlinewidth\":0,\"ticks\":\"\"}}}],\"scatter3d\":[{\"type\":\"scatter3d\",\"line\":{\"colorbar\":{\"outlinewidth\":0,\"ticks\":\"\"}},\"marker\":{\"colorbar\":{\"outlinewidth\":0,\"ticks\":\"\"}}}],\"scattermapbox\":[{\"type\":\"scattermapbox\",\"marker\":{\"colorbar\":{\"outlinewidth\":0,\"ticks\":\"\"}}}],\"scatterternary\":[{\"type\":\"scatterternary\",\"marker\":{\"colorbar\":{\"outlinewidth\":0,\"ticks\":\"\"}}}],\"scattercarpet\":[{\"type\":\"scattercarpet\",\"marker\":{\"colorbar\":{\"outlinewidth\":0,\"ticks\":\"\"}}}],\"carpet\":[{\"aaxis\":{\"endlinecolor\":\"#2a3f5f\",\"gridcolor\":\"white\",\"linecolor\":\"white\",\"minorgridcolor\":\"white\",\"startlinecolor\":\"#2a3f5f\"},\"baxis\":{\"endlinecolor\":\"#2a3f5f\",\"gridcolor\":\"white\",\"linecolor\":\"white\",\"minorgridcolor\":\"white\",\"startlinecolor\":\"#2a3f5f\"},\"type\":\"carpet\"}],\"table\":[{\"cells\":{\"fill\":{\"color\":\"#EBF0F8\"},\"line\":{\"color\":\"white\"}},\"header\":{\"fill\":{\"color\":\"#C8D4E3\"},\"line\":{\"color\":\"white\"}},\"type\":\"table\"}],\"barpolar\":[{\"marker\":{\"line\":{\"color\":\"#E5ECF6\",\"width\":0.5},\"pattern\":{\"fillmode\":\"overlay\",\"size\":10,\"solidity\":0.2}},\"type\":\"barpolar\"}],\"pie\":[{\"automargin\":true,\"type\":\"pie\"}]},\"layout\":{\"autotypenumbers\":\"strict\",\"colorway\":[\"#636efa\",\"#EF553B\",\"#00cc96\",\"#ab63fa\",\"#FFA15A\",\"#19d3f3\",\"#FF6692\",\"#B6E880\",\"#FF97FF\",\"#FECB52\"],\"font\":{\"color\":\"#2a3f5f\"},\"hovermode\":\"closest\",\"hoverlabel\":{\"align\":\"left\"},\"paper_bgcolor\":\"white\",\"plot_bgcolor\":\"#E5ECF6\",\"polar\":{\"bgcolor\":\"#E5ECF6\",\"angularaxis\":{\"gridcolor\":\"white\",\"linecolor\":\"white\",\"ticks\":\"\"},\"radialaxis\":{\"gridcolor\":\"white\",\"linecolor\":\"white\",\"ticks\":\"\"}},\"ternary\":{\"bgcolor\":\"#E5ECF6\",\"aaxis\":{\"gridcolor\":\"white\",\"linecolor\":\"white\",\"ticks\":\"\"},\"baxis\":{\"gridcolor\":\"white\",\"linecolor\":\"white\",\"ticks\":\"\"},\"caxis\":{\"gridcolor\":\"white\",\"linecolor\":\"white\",\"ticks\":\"\"}},\"coloraxis\":{\"colorbar\":{\"outlinewidth\":0,\"ticks\":\"\"}},\"colorscale\":{\"sequential\":[[0.0,\"#0d0887\"],[0.1111111111111111,\"#46039f\"],[0.2222222222222222,\"#7201a8\"],[0.3333333333333333,\"#9c179e\"],[0.4444444444444444,\"#bd3786\"],[0.5555555555555556,\"#d8576b\"],[0.6666666666666666,\"#ed7953\"],[0.7777777777777778,\"#fb9f3a\"],[0.8888888888888888,\"#fdca26\"],[1.0,\"#f0f921\"]],\"sequentialminus\":[[0.0,\"#0d0887\"],[0.1111111111111111,\"#46039f\"],[0.2222222222222222,\"#7201a8\"],[0.3333333333333333,\"#9c179e\"],[0.4444444444444444,\"#bd3786\"],[0.5555555555555556,\"#d8576b\"],[0.6666666666666666,\"#ed7953\"],[0.7777777777777778,\"#fb9f3a\"],[0.8888888888888888,\"#fdca26\"],[1.0,\"#f0f921\"]],\"diverging\":[[0,\"#8e0152\"],[0.1,\"#c51b7d\"],[0.2,\"#de77ae\"],[0.3,\"#f1b6da\"],[0.4,\"#fde0ef\"],[0.5,\"#f7f7f7\"],[0.6,\"#e6f5d0\"],[0.7,\"#b8e186\"],[0.8,\"#7fbc41\"],[0.9,\"#4d9221\"],[1,\"#276419\"]]},\"xaxis\":{\"gridcolor\":\"white\",\"linecolor\":\"white\",\"ticks\":\"\",\"title\":{\"standoff\":15},\"zerolinecolor\":\"white\",\"automargin\":true,\"zerolinewidth\":2},\"yaxis\":{\"gridcolor\":\"white\",\"linecolor\":\"white\",\"ticks\":\"\",\"title\":{\"standoff\":15},\"zerolinecolor\":\"white\",\"automargin\":true,\"zerolinewidth\":2},\"scene\":{\"xaxis\":{\"backgroundcolor\":\"#E5ECF6\",\"gridcolor\":\"white\",\"linecolor\":\"white\",\"showbackground\":true,\"ticks\":\"\",\"zerolinecolor\":\"white\",\"gridwidth\":2},\"yaxis\":{\"backgroundcolor\":\"#E5ECF6\",\"gridcolor\":\"white\",\"linecolor\":\"white\",\"showbackground\":true,\"ticks\":\"\",\"zerolinecolor\":\"white\",\"gridwidth\":2},\"zaxis\":{\"backgroundcolor\":\"#E5ECF6\",\"gridcolor\":\"white\",\"linecolor\":\"white\",\"showbackground\":true,\"ticks\":\"\",\"zerolinecolor\":\"white\",\"gridwidth\":2}},\"shapedefaults\":{\"line\":{\"color\":\"#2a3f5f\"}},\"annotationdefaults\":{\"arrowcolor\":\"#2a3f5f\",\"arrowhead\":0,\"arrowwidth\":1},\"geo\":{\"bgcolor\":\"white\",\"landcolor\":\"#E5ECF6\",\"subunitcolor\":\"white\",\"showland\":true,\"showlakes\":true,\"lakecolor\":\"white\"},\"title\":{\"x\":0.05},\"mapbox\":{\"style\":\"light\"}}},\"xaxis\":{\"title\":{\"text\":\"Timestamp\"},\"tickformat\":\"%Y-%m-%d\",\"tickangle\":45},\"title\":{\"text\":\"Edits count over time with peaks over a threshold(30%) with rolling mean of 3 years\"},\"yaxis\":{\"title\":{\"text\":\"Count(Edits)\"}},\"showlegend\":true},                        {\"responsive\": true}                    ).then(function(){\n",
       "                            \n",
       "var gd = document.getElementById('03914ca6-5fdd-42ce-a5bb-cac80d161102');\n",
       "var x = new MutationObserver(function (mutations, observer) {{\n",
       "        var display = window.getComputedStyle(gd).display;\n",
       "        if (!display || display === 'none') {{\n",
       "            console.log([gd, 'removed!']);\n",
       "            Plotly.purge(gd);\n",
       "            observer.disconnect();\n",
       "        }}\n",
       "}});\n",
       "\n",
       "// Listen for the removal of the full notebook cells\n",
       "var notebookContainer = gd.closest('#notebook-container');\n",
       "if (notebookContainer) {{\n",
       "    x.observe(notebookContainer, {childList: true});\n",
       "}}\n",
       "\n",
       "// Listen for the clearing of the current output cell\n",
       "var outputEl = gd.closest('.output');\n",
       "if (outputEl) {{\n",
       "    x.observe(outputEl, {childList: true});\n",
       "}}\n",
       "\n",
       "                        })                };                });            </script>        </div>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "fig = go.Figure()\n",
    "fig.add_trace(go.Scatter(x=df['timestamp'], y=df['edits'], mode='lines+markers', name='Edits', line=dict(color='blue')))\n",
    "\n",
    "peak_timestamps = [peak[0] for peak in peaks]\n",
    "peak_values = [peak[1] for peak in peaks]\n",
    "fig.add_trace(go.Scatter(x=peak_timestamps, y=peak_values, mode='markers', name='Peaks Above Threshold', \n",
    "                         marker=dict(color='red', size=10, symbol='circle')))\n",
    "\n",
    "fig.update_layout(\n",
    "    title=\"Edits count over time with peaks over a threshold(30%) with rolling mean of 3 years\",\n",
    "    xaxis_title=\"Timestamp\",\n",
    "    yaxis_title=\"Count(Edits)\",\n",
    "    xaxis=dict(tickformat=\"%Y-%m-%d\", tickangle=45),\n",
    "    showlegend=True\n",
    ")\n",
    "\n",
    "# Display the plot\n",
    "fig.show()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "68360c58",
   "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 tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>Timestamp</th>\n",
       "      <th>Edit_count</th>\n",
       "      <th>Rolling_mean</th>\n",
       "      <th>Threshold</th>\n",
       "      <th>Percentage_Difference</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-03-01 00:00:00+00:00</td>\n",
       "      <td>5272</td>\n",
       "      <td>3354.666667</td>\n",
       "      <td>4361.066667</td>\n",
       "      <td>57.154213</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020-04-01 00:00:00+00:00</td>\n",
       "      <td>5760</td>\n",
       "      <td>3956.000000</td>\n",
       "      <td>5142.800000</td>\n",
       "      <td>45.601618</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2020-12-01 00:00:00+00:00</td>\n",
       "      <td>5525</td>\n",
       "      <td>4100.000000</td>\n",
       "      <td>5330.000000</td>\n",
       "      <td>34.756098</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2021-01-01 00:00:00+00:00</td>\n",
       "      <td>12253</td>\n",
       "      <td>4727.153846</td>\n",
       "      <td>6145.300000</td>\n",
       "      <td>159.204595</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2021-07-01 00:00:00+00:00</td>\n",
       "      <td>8814</td>\n",
       "      <td>4579.526316</td>\n",
       "      <td>5953.384211</td>\n",
       "      <td>92.465320</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2021-08-01 00:00:00+00:00</td>\n",
       "      <td>14835</td>\n",
       "      <td>5092.300000</td>\n",
       "      <td>6619.990000</td>\n",
       "      <td>191.322192</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2021-09-01 00:00:00+00:00</td>\n",
       "      <td>65605</td>\n",
       "      <td>7973.857143</td>\n",
       "      <td>10366.014286</td>\n",
       "      <td>722.751133</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2021-10-01 00:00:00+00:00</td>\n",
       "      <td>59519</td>\n",
       "      <td>10316.818182</td>\n",
       "      <td>13411.863636</td>\n",
       "      <td>476.912367</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2021-11-01 00:00:00+00:00</td>\n",
       "      <td>31284</td>\n",
       "      <td>11228.434783</td>\n",
       "      <td>14596.965217</td>\n",
       "      <td>178.614078</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2021-12-01 00:00:00+00:00</td>\n",
       "      <td>51113</td>\n",
       "      <td>12890.291667</td>\n",
       "      <td>16757.379167</td>\n",
       "      <td>296.523223</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>2022-01-01 00:00:00+00:00</td>\n",
       "      <td>27170</td>\n",
       "      <td>13461.480000</td>\n",
       "      <td>17499.924000</td>\n",
       "      <td>101.835162</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>2022-02-01 00:00:00+00:00</td>\n",
       "      <td>22061</td>\n",
       "      <td>13792.230769</td>\n",
       "      <td>17929.900000</td>\n",
       "      <td>59.952370</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>2022-05-01 00:00:00+00:00</td>\n",
       "      <td>60142</td>\n",
       "      <td>14882.172414</td>\n",
       "      <td>19346.824138</td>\n",
       "      <td>304.121108</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>2022-06-01 00:00:00+00:00</td>\n",
       "      <td>59377</td>\n",
       "      <td>16365.333333</td>\n",
       "      <td>21274.933333</td>\n",
       "      <td>262.821818</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>2022-07-01 00:00:00+00:00</td>\n",
       "      <td>31324</td>\n",
       "      <td>16847.870968</td>\n",
       "      <td>21902.232258</td>\n",
       "      <td>85.922601</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>2022-08-01 00:00:00+00:00</td>\n",
       "      <td>64422</td>\n",
       "      <td>18334.562500</td>\n",
       "      <td>23834.931250</td>\n",
       "      <td>251.369170</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>2022-09-01 00:00:00+00:00</td>\n",
       "      <td>78391</td>\n",
       "      <td>20154.454545</td>\n",
       "      <td>26200.790909</td>\n",
       "      <td>288.951236</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>2022-10-01 00:00:00+00:00</td>\n",
       "      <td>172441</td>\n",
       "      <td>24633.470588</td>\n",
       "      <td>32023.511765</td>\n",
       "      <td>600.027223</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>2022-11-01 00:00:00+00:00</td>\n",
       "      <td>205361</td>\n",
       "      <td>29797.114286</td>\n",
       "      <td>38736.248571</td>\n",
       "      <td>589.197612</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>2022-12-01 00:00:00+00:00</td>\n",
       "      <td>54768</td>\n",
       "      <td>30490.750000</td>\n",
       "      <td>39637.975000</td>\n",
       "      <td>79.621689</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   Timestamp Edit_count  Rolling_mean     Threshold  \\\n",
       "0  2020-03-01 00:00:00+00:00       5272   3354.666667   4361.066667   \n",
       "1  2020-04-01 00:00:00+00:00       5760   3956.000000   5142.800000   \n",
       "2  2020-12-01 00:00:00+00:00       5525   4100.000000   5330.000000   \n",
       "3  2021-01-01 00:00:00+00:00      12253   4727.153846   6145.300000   \n",
       "4  2021-07-01 00:00:00+00:00       8814   4579.526316   5953.384211   \n",
       "5  2021-08-01 00:00:00+00:00      14835   5092.300000   6619.990000   \n",
       "6  2021-09-01 00:00:00+00:00      65605   7973.857143  10366.014286   \n",
       "7  2021-10-01 00:00:00+00:00      59519  10316.818182  13411.863636   \n",
       "8  2021-11-01 00:00:00+00:00      31284  11228.434783  14596.965217   \n",
       "9  2021-12-01 00:00:00+00:00      51113  12890.291667  16757.379167   \n",
       "10 2022-01-01 00:00:00+00:00      27170  13461.480000  17499.924000   \n",
       "11 2022-02-01 00:00:00+00:00      22061  13792.230769  17929.900000   \n",
       "12 2022-05-01 00:00:00+00:00      60142  14882.172414  19346.824138   \n",
       "13 2022-06-01 00:00:00+00:00      59377  16365.333333  21274.933333   \n",
       "14 2022-07-01 00:00:00+00:00      31324  16847.870968  21902.232258   \n",
       "15 2022-08-01 00:00:00+00:00      64422  18334.562500  23834.931250   \n",
       "16 2022-09-01 00:00:00+00:00      78391  20154.454545  26200.790909   \n",
       "17 2022-10-01 00:00:00+00:00     172441  24633.470588  32023.511765   \n",
       "18 2022-11-01 00:00:00+00:00     205361  29797.114286  38736.248571   \n",
       "19 2022-12-01 00:00:00+00:00      54768  30490.750000  39637.975000   \n",
       "\n",
       "   Percentage_Difference  \n",
       "0              57.154213  \n",
       "1              45.601618  \n",
       "2              34.756098  \n",
       "3             159.204595  \n",
       "4              92.465320  \n",
       "5             191.322192  \n",
       "6             722.751133  \n",
       "7             476.912367  \n",
       "8             178.614078  \n",
       "9             296.523223  \n",
       "10            101.835162  \n",
       "11             59.952370  \n",
       "12            304.121108  \n",
       "13            262.821818  \n",
       "14             85.922601  \n",
       "15            251.369170  \n",
       "16            288.951236  \n",
       "17            600.027223  \n",
       "18            589.197612  \n",
       "19             79.621689  "
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "my_df=pd.DataFrame(peaks,columns=[[\"Timestamp\",\"Edit_count\",\"Rolling_mean\",\"Threshold\",\"Percentage_Difference\"]])\n",
    "my_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "27d09366",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "duckdb.sql(\"CREATE TABLE community_alert_logs_table AS SELECT * FROM my_df\")\n",
    "\n",
    "duckdb.sql(\"INSERT INTO community_alert_logs_table SELECT * FROM my_df\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f27cf934",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "681c3a7e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "┌───────────────────────────┬─────────────────┬────────────────────┬────────────────────┬────────────────────────────┐\n",
       "│      ('Timestamp',)       │ ('Edit_count',) │ ('Rolling_mean',)  │   ('Threshold',)   │ ('Percentage_Difference',) │\n",
       "│ timestamp with time zone  │      int64      │       double       │       double       │           double           │\n",
       "├───────────────────────────┼─────────────────┼────────────────────┼────────────────────┼────────────────────────────┤\n",
       "│ 2020-03-01 05:30:00+05:30 │            5272 │ 3354.6666666666665 │  4361.066666666667 │         57.154213036565984 │\n",
       "│ 2020-04-01 05:30:00+05:30 │            5760 │             3956.0 │             5142.8 │         45.601617795753285 │\n",
       "│ 2020-12-01 05:30:00+05:30 │            5525 │             4100.0 │             5330.0 │          34.75609756097561 │\n",
       "│ 2021-01-01 05:30:00+05:30 │           12253 │  4727.153846153846 │  6145.299999999999 │         159.20459538183653 │\n",
       "│ 2021-07-01 05:30:00+05:30 │            8814 │  4579.526315789473 │  5953.384210526316 │          92.46532047672136 │\n",
       "│ 2021-08-01 05:30:00+05:30 │           14835 │             5092.3 │  6619.990000000001 │         191.32219232959568 │\n",
       "│ 2021-09-01 05:30:00+05:30 │           65605 │  7973.857142857143 │ 10366.014285714286 │          722.7511331673146 │\n",
       "│ 2021-10-01 05:30:00+05:30 │           59519 │ 10316.818181818182 │ 13411.863636363638 │          476.9123672732079 │\n",
       "│ 2021-11-01 05:30:00+05:30 │           31284 │ 11228.434782608696 │ 14596.965217391305 │         178.61407761351228 │\n",
       "│ 2021-12-01 05:30:00+05:30 │           51113 │ 12890.291666666666 │ 16757.379166666666 │          296.5232232267825 │\n",
       "├───────────────────────────┴─────────────────┴────────────────────┴────────────────────┴────────────────────────────┤\n",
       "│ 10 rows                                                                                                  5 columns │\n",
       "└────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "duckdb.sql(\"SELECT * FROM community_alert_logs_table LIMIT 10\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5bbbdb27",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1ea2d421",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3f7b5d95",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "97032c68",
   "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.11.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}