Skip to content

Instantly share code, notes, and snippets.

@numa08
Created April 30, 2024 13:20
Show Gist options
  • Save numa08/ce1ac33983299f0d580a9a40a62af849 to your computer and use it in GitHub Desktop.
Save numa08/ce1ac33983299f0d580a9a40a62af849 to your computer and use it in GitHub Desktop.
jarl様式変換君
Display the source blob
Display the rendered blob
Raw
{
"nbformat": 4,
"nbformat_minor": 0,
"metadata": {
"colab": {
"provenance": [],
"mount_file_id": "1wM31LfmHehxGa1C1NR3AGAav-kZfyg3M",
"authorship_tag": "ABX9TyMB04zs2wGosv8h/Wf5jCog",
"include_colab_link": true
},
"kernelspec": {
"name": "python3",
"display_name": "Python 3"
},
"language_info": {
"name": "python"
},
"widgets": {
"application/vnd.jupyter.widget-state+json": {
"5ddbd0949d3c4082947c6e8a06d27c93": {
"model_module": "@jupyter-widgets/controls",
"model_name": "TextModel",
"model_module_version": "1.5.0",
"state": {
"_dom_classes": [],
"_model_module": "@jupyter-widgets/controls",
"_model_module_version": "1.5.0",
"_model_name": "TextModel",
"_view_count": null,
"_view_module": "@jupyter-widgets/controls",
"_view_module_version": "1.5.0",
"_view_name": "TextView",
"continuous_update": true,
"description": "DB パス:",
"description_tooltip": null,
"disabled": false,
"layout": "IPY_MODEL_14a5b703c2c44942bfbbb92d03823a7e",
"placeholder": "データベースのファイルパスを入力してください",
"style": "IPY_MODEL_67e52e49dc5f4ac1b74ab4d988b4dff6",
"value": "/content/drive/MyDrive/アマチュア無線/Log4OM/qsolog.SQLite"
}
},
"14a5b703c2c44942bfbbb92d03823a7e": {
"model_module": "@jupyter-widgets/base",
"model_name": "LayoutModel",
"model_module_version": "1.2.0",
"state": {
"_model_module": "@jupyter-widgets/base",
"_model_module_version": "1.2.0",
"_model_name": "LayoutModel",
"_view_count": null,
"_view_module": "@jupyter-widgets/base",
"_view_module_version": "1.2.0",
"_view_name": "LayoutView",
"align_content": null,
"align_items": null,
"align_self": null,
"border": null,
"bottom": null,
"display": null,
"flex": null,
"flex_flow": null,
"grid_area": null,
"grid_auto_columns": null,
"grid_auto_flow": null,
"grid_auto_rows": null,
"grid_column": null,
"grid_gap": null,
"grid_row": null,
"grid_template_areas": null,
"grid_template_columns": null,
"grid_template_rows": null,
"height": null,
"justify_content": null,
"justify_items": null,
"left": null,
"margin": null,
"max_height": null,
"max_width": null,
"min_height": null,
"min_width": null,
"object_fit": null,
"object_position": null,
"order": null,
"overflow": null,
"overflow_x": null,
"overflow_y": null,
"padding": null,
"right": null,
"top": null,
"visibility": null,
"width": "500px"
}
},
"67e52e49dc5f4ac1b74ab4d988b4dff6": {
"model_module": "@jupyter-widgets/controls",
"model_name": "DescriptionStyleModel",
"model_module_version": "1.5.0",
"state": {
"_model_module": "@jupyter-widgets/controls",
"_model_module_version": "1.5.0",
"_model_name": "DescriptionStyleModel",
"_view_count": null,
"_view_module": "@jupyter-widgets/base",
"_view_module_version": "1.2.0",
"_view_name": "StyleView",
"description_width": "initial"
}
},
"7636571c862244b1a532af378aaa702a": {
"model_module": "@jupyter-widgets/controls",
"model_name": "TextModel",
"model_module_version": "1.5.0",
"state": {
"_dom_classes": [],
"_model_module": "@jupyter-widgets/controls",
"_model_module_version": "1.5.0",
"_model_name": "TextModel",
"_view_count": null,
"_view_module": "@jupyter-widgets/controls",
"_view_module_version": "1.5.0",
"_view_name": "TextView",
"continuous_update": true,
"description": "コンテストID:",
"description_tooltip": null,
"disabled": false,
"layout": "IPY_MODEL_4761e0d05d3f4482aa7732c5bcce1136",
"placeholder": "コンテストIDを入力",
"style": "IPY_MODEL_9f9af01bfd194404b4d7c4769bf18dd7",
"value": "JARL-ALLJA"
}
},
"4761e0d05d3f4482aa7732c5bcce1136": {
"model_module": "@jupyter-widgets/base",
"model_name": "LayoutModel",
"model_module_version": "1.2.0",
"state": {
"_model_module": "@jupyter-widgets/base",
"_model_module_version": "1.2.0",
"_model_name": "LayoutModel",
"_view_count": null,
"_view_module": "@jupyter-widgets/base",
"_view_module_version": "1.2.0",
"_view_name": "LayoutView",
"align_content": null,
"align_items": null,
"align_self": null,
"border": null,
"bottom": null,
"display": null,
"flex": null,
"flex_flow": null,
"grid_area": null,
"grid_auto_columns": null,
"grid_auto_flow": null,
"grid_auto_rows": null,
"grid_column": null,
"grid_gap": null,
"grid_row": null,
"grid_template_areas": null,
"grid_template_columns": null,
"grid_template_rows": null,
"height": null,
"justify_content": null,
"justify_items": null,
"left": null,
"margin": null,
"max_height": null,
"max_width": null,
"min_height": null,
"min_width": null,
"object_fit": null,
"object_position": null,
"order": null,
"overflow": null,
"overflow_x": null,
"overflow_y": null,
"padding": null,
"right": null,
"top": null,
"visibility": null,
"width": "300px"
}
},
"9f9af01bfd194404b4d7c4769bf18dd7": {
"model_module": "@jupyter-widgets/controls",
"model_name": "DescriptionStyleModel",
"model_module_version": "1.5.0",
"state": {
"_model_module": "@jupyter-widgets/controls",
"_model_module_version": "1.5.0",
"_model_name": "DescriptionStyleModel",
"_view_count": null,
"_view_module": "@jupyter-widgets/base",
"_view_module_version": "1.2.0",
"_view_name": "StyleView",
"description_width": "initial"
}
},
"8880ba231c51457b973b91a4c82187f0": {
"model_module": "@jupyter-widgets/controls",
"model_name": "DatePickerModel",
"model_module_version": "1.5.0",
"state": {
"_dom_classes": [],
"_model_module": "@jupyter-widgets/controls",
"_model_module_version": "1.5.0",
"_model_name": "DatePickerModel",
"_view_count": null,
"_view_module": "@jupyter-widgets/controls",
"_view_module_version": "1.5.0",
"_view_name": "DatePickerView",
"description": "開始日:",
"description_tooltip": null,
"disabled": false,
"layout": "IPY_MODEL_2775cdf4782c4863ad4574336010a1a4",
"style": "IPY_MODEL_f8640dc91bf64ac4981001f266d4ab85",
"value": {
"year": 2024,
"month": 3,
"date": 27
}
}
},
"2775cdf4782c4863ad4574336010a1a4": {
"model_module": "@jupyter-widgets/base",
"model_name": "LayoutModel",
"model_module_version": "1.2.0",
"state": {
"_model_module": "@jupyter-widgets/base",
"_model_module_version": "1.2.0",
"_model_name": "LayoutModel",
"_view_count": null,
"_view_module": "@jupyter-widgets/base",
"_view_module_version": "1.2.0",
"_view_name": "LayoutView",
"align_content": null,
"align_items": null,
"align_self": null,
"border": null,
"bottom": null,
"display": null,
"flex": null,
"flex_flow": null,
"grid_area": null,
"grid_auto_columns": null,
"grid_auto_flow": null,
"grid_auto_rows": null,
"grid_column": null,
"grid_gap": null,
"grid_row": null,
"grid_template_areas": null,
"grid_template_columns": null,
"grid_template_rows": null,
"height": null,
"justify_content": null,
"justify_items": null,
"left": null,
"margin": null,
"max_height": null,
"max_width": null,
"min_height": null,
"min_width": null,
"object_fit": null,
"object_position": null,
"order": null,
"overflow": null,
"overflow_x": null,
"overflow_y": null,
"padding": null,
"right": null,
"top": null,
"visibility": null,
"width": null
}
},
"f8640dc91bf64ac4981001f266d4ab85": {
"model_module": "@jupyter-widgets/controls",
"model_name": "DescriptionStyleModel",
"model_module_version": "1.5.0",
"state": {
"_model_module": "@jupyter-widgets/controls",
"_model_module_version": "1.5.0",
"_model_name": "DescriptionStyleModel",
"_view_count": null,
"_view_module": "@jupyter-widgets/base",
"_view_module_version": "1.2.0",
"_view_name": "StyleView",
"description_width": "initial"
}
},
"b234039296ce4eb187dbb440dff9afde": {
"model_module": "@jupyter-widgets/controls",
"model_name": "DatePickerModel",
"model_module_version": "1.5.0",
"state": {
"_dom_classes": [],
"_model_module": "@jupyter-widgets/controls",
"_model_module_version": "1.5.0",
"_model_name": "DatePickerModel",
"_view_count": null,
"_view_module": "@jupyter-widgets/controls",
"_view_module_version": "1.5.0",
"_view_name": "DatePickerView",
"description": "終了日:",
"description_tooltip": null,
"disabled": false,
"layout": "IPY_MODEL_ea3f94cc514e4181acaf29247c56b1db",
"style": "IPY_MODEL_f3c7e4b1a88e4428bfaad2c7681858a2",
"value": {
"year": 2024,
"month": 3,
"date": 28
}
}
},
"ea3f94cc514e4181acaf29247c56b1db": {
"model_module": "@jupyter-widgets/base",
"model_name": "LayoutModel",
"model_module_version": "1.2.0",
"state": {
"_model_module": "@jupyter-widgets/base",
"_model_module_version": "1.2.0",
"_model_name": "LayoutModel",
"_view_count": null,
"_view_module": "@jupyter-widgets/base",
"_view_module_version": "1.2.0",
"_view_name": "LayoutView",
"align_content": null,
"align_items": null,
"align_self": null,
"border": null,
"bottom": null,
"display": null,
"flex": null,
"flex_flow": null,
"grid_area": null,
"grid_auto_columns": null,
"grid_auto_flow": null,
"grid_auto_rows": null,
"grid_column": null,
"grid_gap": null,
"grid_row": null,
"grid_template_areas": null,
"grid_template_columns": null,
"grid_template_rows": null,
"height": null,
"justify_content": null,
"justify_items": null,
"left": null,
"margin": null,
"max_height": null,
"max_width": null,
"min_height": null,
"min_width": null,
"object_fit": null,
"object_position": null,
"order": null,
"overflow": null,
"overflow_x": null,
"overflow_y": null,
"padding": null,
"right": null,
"top": null,
"visibility": null,
"width": null
}
},
"f3c7e4b1a88e4428bfaad2c7681858a2": {
"model_module": "@jupyter-widgets/controls",
"model_name": "DescriptionStyleModel",
"model_module_version": "1.5.0",
"state": {
"_model_module": "@jupyter-widgets/controls",
"_model_module_version": "1.5.0",
"_model_name": "DescriptionStyleModel",
"_view_count": null,
"_view_module": "@jupyter-widgets/base",
"_view_module_version": "1.2.0",
"_view_name": "StyleView",
"description_width": "initial"
}
},
"bf09e10d19104648ac9c74d0cc44631f": {
"model_module": "@jupyter-widgets/controls",
"model_name": "ButtonModel",
"model_module_version": "1.5.0",
"state": {
"_dom_classes": [],
"_model_module": "@jupyter-widgets/controls",
"_model_module_version": "1.5.0",
"_model_name": "ButtonModel",
"_view_count": null,
"_view_module": "@jupyter-widgets/controls",
"_view_module_version": "1.5.0",
"_view_name": "ButtonView",
"button_style": "",
"description": "データ抽出と加工",
"disabled": false,
"icon": "",
"layout": "IPY_MODEL_0fd75b05b570477cb6fc2b6fd1b8603a",
"style": "IPY_MODEL_3cb09c6620f0426ab53287f19cac4f61",
"tooltip": ""
}
},
"0fd75b05b570477cb6fc2b6fd1b8603a": {
"model_module": "@jupyter-widgets/base",
"model_name": "LayoutModel",
"model_module_version": "1.2.0",
"state": {
"_model_module": "@jupyter-widgets/base",
"_model_module_version": "1.2.0",
"_model_name": "LayoutModel",
"_view_count": null,
"_view_module": "@jupyter-widgets/base",
"_view_module_version": "1.2.0",
"_view_name": "LayoutView",
"align_content": null,
"align_items": null,
"align_self": null,
"border": null,
"bottom": null,
"display": null,
"flex": null,
"flex_flow": null,
"grid_area": null,
"grid_auto_columns": null,
"grid_auto_flow": null,
"grid_auto_rows": null,
"grid_column": null,
"grid_gap": null,
"grid_row": null,
"grid_template_areas": null,
"grid_template_columns": null,
"grid_template_rows": null,
"height": null,
"justify_content": null,
"justify_items": null,
"left": null,
"margin": null,
"max_height": null,
"max_width": null,
"min_height": null,
"min_width": null,
"object_fit": null,
"object_position": null,
"order": null,
"overflow": null,
"overflow_x": null,
"overflow_y": null,
"padding": null,
"right": null,
"top": null,
"visibility": null,
"width": null
}
},
"3cb09c6620f0426ab53287f19cac4f61": {
"model_module": "@jupyter-widgets/controls",
"model_name": "ButtonStyleModel",
"model_module_version": "1.5.0",
"state": {
"_model_module": "@jupyter-widgets/controls",
"_model_module_version": "1.5.0",
"_model_name": "ButtonStyleModel",
"_view_count": null,
"_view_module": "@jupyter-widgets/base",
"_view_module_version": "1.2.0",
"_view_name": "StyleView",
"button_color": null,
"font_weight": ""
}
}
}
}
},
"cells": [
{
"cell_type": "markdown",
"metadata": {
"id": "view-in-github",
"colab_type": "text"
},
"source": [
"<a href=\"https://colab.research.google.com/gist/numa08/ce1ac33983299f0d580a9a40a62af849/jarl.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
]
},
{
"cell_type": "code",
"execution_count": 1,
"metadata": {
"id": "_KPB_q5HGf-u"
},
"outputs": [],
"source": [
"import sqlite3\n",
"import re\n",
"from datetime import datetime\n",
"import ipywidgets as widgets\n",
"from IPython.display import display"
]
},
{
"cell_type": "code",
"source": [
"db_path_input = widgets.Text(\n",
" value='',\n",
" placeholder='データベースのファイルパスを入力してください',\n",
" description='DB パス:',\n",
" style={'description_width': 'initial'},\n",
" layout=widgets.Layout(width='500px')\n",
")\n",
"\n",
"contest_id_input = widgets.Text(\n",
" value='JARL-ALLJA',\n",
" placeholder='コンテストIDを入力',\n",
" description='コンテストID:',\n",
" style={'description_width': 'initial'},\n",
" layout=widgets.Layout(width='300px')\n",
")\n",
"\n",
"date_picker_start = widgets.DatePicker(\n",
" description='開始日:',\n",
" disabled=False,\n",
" style={'description_width': 'initial'}\n",
")\n",
"\n",
"date_picker_end = widgets.DatePicker(\n",
" description='終了日:',\n",
" disabled=False,\n",
" style={'description_width': 'initial'}\n",
")"
],
"metadata": {
"id": "a4J3-jxyGvWg"
},
"execution_count": 3,
"outputs": []
},
{
"cell_type": "code",
"source": [
"display(db_path_input, contest_id_input, date_picker_start, date_picker_end)"
],
"metadata": {
"colab": {
"base_uri": "https://localhost:8080/",
"height": 139,
"referenced_widgets": [
"5ddbd0949d3c4082947c6e8a06d27c93",
"14a5b703c2c44942bfbbb92d03823a7e",
"67e52e49dc5f4ac1b74ab4d988b4dff6",
"7636571c862244b1a532af378aaa702a",
"4761e0d05d3f4482aa7732c5bcce1136",
"9f9af01bfd194404b4d7c4769bf18dd7",
"8880ba231c51457b973b91a4c82187f0",
"2775cdf4782c4863ad4574336010a1a4",
"f8640dc91bf64ac4981001f266d4ab85",
"b234039296ce4eb187dbb440dff9afde",
"ea3f94cc514e4181acaf29247c56b1db",
"f3c7e4b1a88e4428bfaad2c7681858a2"
]
},
"id": "bhOFOA1sGw73",
"outputId": "b41240a3-080a-4669-8133-f8cb0bdcf604"
},
"execution_count": 4,
"outputs": [
{
"output_type": "display_data",
"data": {
"text/plain": [
"Text(value='', description='DB パス:', layout=Layout(width='500px'), placeholder='データベースのファイルパスを入力してください', style…"
],
"application/vnd.jupyter.widget-view+json": {
"version_major": 2,
"version_minor": 0,
"model_id": "5ddbd0949d3c4082947c6e8a06d27c93"
}
},
"metadata": {}
},
{
"output_type": "display_data",
"data": {
"text/plain": [
"Text(value='JARL-ALLJA', description='コンテストID:', layout=Layout(width='300px'), placeholder='コンテストIDを入力', style…"
],
"application/vnd.jupyter.widget-view+json": {
"version_major": 2,
"version_minor": 0,
"model_id": "7636571c862244b1a532af378aaa702a"
}
},
"metadata": {}
},
{
"output_type": "display_data",
"data": {
"text/plain": [
"DatePicker(value=None, description='開始日:', style=DescriptionStyle(description_width='initial'))"
],
"application/vnd.jupyter.widget-view+json": {
"version_major": 2,
"version_minor": 0,
"model_id": "8880ba231c51457b973b91a4c82187f0"
}
},
"metadata": {}
},
{
"output_type": "display_data",
"data": {
"text/plain": [
"DatePicker(value=None, description='終了日:', style=DescriptionStyle(description_width='initial'))"
],
"application/vnd.jupyter.widget-view+json": {
"version_major": 2,
"version_minor": 0,
"model_id": "b234039296ce4eb187dbb440dff9afde"
}
},
"metadata": {}
}
]
},
{
"cell_type": "code",
"source": [
"def process_data(b):\n",
" db_path = db_path_input.value\n",
" contest_id = contest_id_input.value\n",
" start_date = date_picker_start.value.strftime('%Y-%m-%d') + \" 12:00\"\n",
" end_date = date_picker_end.value.strftime('%Y-%m-%d') + \" 12:00\"\n",
"\n",
" # データベースに接続\n",
" conn = sqlite3.connect(db_path)\n",
" cursor = conn.cursor()\n",
"\n",
" # SQLクエリを実行\n",
" query = f\"\"\"\n",
" SELECT\n",
" strftime('%Y-%m-%d %H:%M', datetime(qsodate, '+9 hours')) AS qsodate_jst,\n",
" CAST(freq / 1000 AS INTEGER) AS freq_khz,\n",
" mode,\n",
" callsign,\n",
" rstsent || ' ' || stxstring AS sent_info,\n",
" rstrcvd || ' ' || srxstring AS received_info,\n",
" srxstring\n",
" FROM\n",
" Log\n",
" WHERE\n",
" contestid = '{contest_id}' AND\n",
" qsodate BETWEEN '{start_date}' AND '{end_date}';\n",
" \"\"\"\n",
"\n",
" cursor.execute(query)\n",
" rows = cursor.fetchall()\n",
"\n",
" # 結果の後処理\n",
" results = []\n",
" seen_numbers = set()\n",
"\n",
" for row in rows:\n",
" qsodate_jst, freq_khz, mode, callsign, sent_info, received_info, srxstring = row\n",
"\n",
" # srxstringから数字のみを抽出し、2桁の0埋めされた数字にする\n",
" numbers = re.findall(r'\\d+', srxstring)\n",
" number_str = f\"{int(numbers[0]):02d}\" if numbers else \"00\"\n",
"\n",
" if number_str in seen_numbers:\n",
" number_str = '-' # 既に見た数字は '-' として扱う\n",
" else:\n",
" seen_numbers.add(number_str)\n",
"\n",
" # 各フィールドを連結\n",
" result_row = [qsodate_jst, freq_khz, mode, callsign, sent_info, received_info, number_str, '1']\n",
" results.append(result_row)\n",
"\n",
" # 結果を表示\n",
" print('DATE (JST) TIME BAND MODE CALLSIGN SENTNo RCVDNo Mlt Pts')\n",
" for result in results:\n",
" formatted_string = \"{:<17} {:<2} {:<4} {:<12} {:<12}{:<12}{:<2} {:<2}\".format(*result)\n",
" print(formatted_string)\n",
"\n",
" # データベース接続を閉じる\n",
" conn.close()"
],
"metadata": {
"id": "YGsbl0X6HHOD"
},
"execution_count": 28,
"outputs": []
},
{
"cell_type": "code",
"source": [
"execute_button = widgets.Button(description=\"データ抽出と加工\")\n",
"display(execute_button)\n",
"execute_button.on_click(process_data)"
],
"metadata": {
"colab": {
"base_uri": "https://localhost:8080/",
"height": 257,
"referenced_widgets": [
"bf09e10d19104648ac9c74d0cc44631f",
"0fd75b05b570477cb6fc2b6fd1b8603a",
"3cb09c6620f0426ab53287f19cac4f61"
]
},
"id": "uuRlaj5MHKeP",
"outputId": "56ac9d2f-2664-403c-83b2-e88d37c50ce2"
},
"execution_count": 29,
"outputs": [
{
"output_type": "display_data",
"data": {
"text/plain": [
"Button(description='データ抽出と加工', style=ButtonStyle())"
],
"application/vnd.jupyter.widget-view+json": {
"version_major": 2,
"version_minor": 0,
"model_id": "bf09e10d19104648ac9c74d0cc44631f"
}
},
"metadata": {}
},
{
"output_type": "stream",
"name": "stdout",
"text": [
"DATE (JST) TIME BAND MODE CALLSIGN SENTNo RCVDNo Mlt Pts\n",
"2024-04-27 21:28 7 CW JG3QBJ 599 17M 599 22M 22 1 \n",
"2024-04-27 22:41 7 CW JM1TUY 599 17M 599 11H 11 1 \n",
"2024-04-27 22:44 7 CW JK1CWR 599 17M 599 11M - 1 \n",
"2024-04-27 22:52 7 CW JJ1YAF 599 17M 599 14M 14 1 \n",
"2024-04-27 23:01 7 CW JA5DVK 599 17M 599 39M 39 1 \n",
"2024-04-27 23:06 7 CW JK1JHU 599 17M 599 14M - 1 \n",
"2024-04-27 23:09 7 CW JE1SCJ 599 17M 599 10M 10 1 \n",
"2024-04-27 23:13 7 CW JA7ODY 599 17M 599 06H 06 1 \n",
"2024-04-27 23:18 7 CW JQ1YUF 599 17M 599 10H - 1 \n",
"2024-04-27 23:20 7 CW JA1ZLO 599 17M 599 10M - 1 \n",
"2024-04-27 23:26 7 CW JK1YMM 599 17M 599 14M - 1 \n"
]
}
]
},
{
"cell_type": "code",
"source": [],
"metadata": {
"id": "N2-hkQ2sHN7N"
},
"execution_count": null,
"outputs": []
}
]
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment