{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "
  • Get new Clash API: Done
    • \n", "
  • Get Clash war stats: Done
    • \n", "
  • Prep Clash clan war data: Done
    • \n", "
  • Prep Clash member war data: Done
    • \n", "
  • Get Discord Bot API keys: Done
    • \n", "
  • Make Clash player tag and Discord account reference table: Done
    • \n", "
  • Get test code for Discord Bot API and tag a user: Done
    • \n", "
  • Get a list of players still missing attacks: Done
    • \n", "
  • Mention players missing attacks on Discord: Pending
    • " ] }, { "cell_type": "code", "execution_count": 2, "metadata": {}, "outputs": [], "source": [ "import requests\n", "import pandas as pd\n", "from datetime import date, datetime, timezone, timedelta\n", "import numpy as np\n", "from google.cloud import bigquery" ] }, { "cell_type": "code", "execution_count": 3, "metadata": {}, "outputs": [], "source": [ "api_token = 'token'\n", "today = date.today()\n", "client = bigquery.Client.from_service_account_json(r'ServiceAccount.json')\n", "clan_tags = [\"QG9UJ08\",\"Q2JLCVLR\",\"P09YGYU2\",\"2YCCGY8JU\"]" ] }, { "cell_type": "code", "execution_count": 4, "metadata": {}, "outputs": [], "source": [ "def import_to_gbq(df, destination_table):\n", " try:\n", " ## If you want the column and row count then\n", " table_id = destination_table\n", " job_config = bigquery.LoadJobConfig(\n", " schema=[\n", " ],\n", " write_disposition=\"WRITE_TRUNCATE\",\n", " )\n", "\n", " job = client.load_table_from_dataframe(\n", " df, table_id, job_config=job_config\n", " ) # Make an API request.\n", "\n", " job.result() # Wait for the job to complete.\n", " table = client.get_table(table_id) # Make an API request.\n", " print(\"Loaded {} rows and {} columns to {}\".format(table.num_rows, len(table.schema), table_id))\n", " except Exception as e:\n", " print(e)" ] }, { "cell_type": "code", "execution_count": 5, "metadata": {}, "outputs": [], "source": [ "def get_current_war_data(clan_tag):\n", " url = \"https://api.clashofclans.com/v1/clans/%23{clan_tag}/currentwar\".format(clan_tag=clan_tag)\n", " r = requests.get(url, headers={\"Accept\":\"application/json\", \"authorization\":\"Bearer \"+api_token}) #, params = {\"limit\":20})\n", " data = r.json()\n", "\n", " if r.status_code == 200:\n", " if data.get('state') == \"inWar\" or data.get('state') == \"preparation\" or data.get('state') == \"warEnded\":\n", " return data\n", " else:\n", " return None" ] }, { "cell_type": "code", "execution_count": 6, "metadata": {}, "outputs": [], "source": [ "# Define a function to calculate time left in hours and minutes (as a decimal)\n", "def calculate_time_left(end_time, utc_offset_hours=0):\n", " war_end_time = datetime.strptime(end_time, \"%Y%m%dT%H%M%S.%fZ\").replace(tzinfo=timezone.utc)\n", " my_local_time_plus_UTC = datetime.now(timezone.utc) + timedelta(hours=utc_offset_hours)\n", " time_left = war_end_time - my_local_time_plus_UTC\n", " hours_left = time_left.total_seconds() / 3600 # Convert total seconds to hours\n", " return round(hours_left, 1) # Round to 1 decimal place for readability" ] }, { "cell_type": "code", "execution_count": 7, "metadata": {}, "outputs": [], "source": [ "# Define a function to extract the required information and create a dictionary with clan high level info, like total stars, attacks, percent destruction.\n", "def extract_clan_info(clan_info):\n", " return {\n", " 'clanTag': clan_info.get('tag', ''),\n", " 'clanName': clan_info.get('name', ''),\n", " 'clanLevel': clan_info.get('clanLevel', 0),\n", " 'attacks': clan_info.get('attacks', 0),\n", " 'stars': clan_info.get('stars', 0),\n", " 'destructionPercentage': clan_info.get('destructionPercentage', 0.00)\n", " }\n", "\n", "\n", "# Define a function to reformat the clan info for single row representation, for both my clan and enemy clan\n", "def reformat_for_single_row(user_clan, opponent_clan):\n", " combined_data = {}\n", " for key, value in user_clan.items():\n", " combined_data[f'user_{key}'] = value\n", " for key, value in opponent_clan.items():\n", " combined_data[f'opponent_{key}'] = value\n", " return combined_data\n", "\n", "\n" ] }, { "cell_type": "code", "execution_count": 8, "metadata": {}, "outputs": [], "source": [ "# Define a function to process member attack data\n", "def process_member_attacks(member):\n", " # Initialize default values\n", " attack_details = {\n", " 'player_tag': member.get('tag',''),\n", " 'name': member.get('name', ''),\n", " 'total_attacks': 0,\n", " # 'first_attack_stars': None,\n", " # 'second_attack_stars': None,\n", " # 'first_attack_destruction': None,\n", " # 'second_attack_destruction': None\n", " 'first_attack_stars': np.nan,\n", " 'second_attack_stars': np.nan,\n", " 'first_attack_destruction': np.nan,\n", " 'second_attack_destruction': np.nan\n", " }\n", " \n", " # Process each attack\n", " attacks = member.get('attacks', [])\n", " attack_details['total_attacks'] = len(attacks)\n", " for i, attack in enumerate(attacks):\n", " if i == 0: # First attack\n", " attack_details['first_attack_stars'] = attack.get('stars', 0)\n", " attack_details['first_attack_destruction'] = attack.get('destructionPercentage', 0)\n", " elif i == 1: # Second attack\n", " attack_details['second_attack_stars'] = attack.get('stars', 0)\n", " attack_details['second_attack_destruction'] = attack.get('destructionPercentage', 0)\n", " \n", " return attack_details" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Get Aggregated data for each clan and insert in BigQuery for storing" ] }, { "cell_type": "code", "execution_count": 9, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Clan Tag 2YCCGY8JU not found, please review the tag, or perhaps the clan doesn't have war log public.\n" ] } ], "source": [ "single_row_df_list = []\n", "members_attacks_df_list = []\n", "try:\n", " for clan_tag in clan_tags:\n", " data = get_current_war_data(clan_tag)\n", " if not data:\n", " print(\"Clan Tag {clan_tag} not found, please review the tag, or perhaps the clan doesn't have war log public.\".format(clan_tag=clan_tag))\n", " else:\n", " # Extract the necessary data for both the user's clan and the enemy clan\n", " start_time = data.get('startTime', '')\n", " end_time = data.get('endTime', '')\n", " war_status = data.get('state', '')\n", "\n", " \n", " clan_data = data['clan']\n", " opponent_data = data['opponent']\n", "\n", " clan_stats = extract_clan_info(clan_data)\n", " opponent_stats = extract_clan_info(opponent_data)\n", "\n", " # Reformat the clan and opponent data\n", " single_row_data = reformat_for_single_row(clan_stats, opponent_stats)\n", "\n", " # Create a DataFrame from the single row of data\n", " single_row_df = pd.DataFrame([single_row_data])\n", "\n", " single_row_df['startTime'] = start_time\n", " single_row_df['endTime'] = end_time\n", " single_row_df['war_end_ts'] = datetime.strptime(end_time, \"%Y%m%dT%H%M%S.%fZ\")\n", " # Calculate the time left in the war\n", " time_left_in_hours = calculate_time_left(single_row_df['endTime'][0], utc_offset_hours=0)\n", " single_row_df['timeLeft'] = str(time_left_in_hours)\n", " single_row_df['war_status'] = war_status\n", "\n", " single_row_df_list.append(single_row_df)\n", "\n", "\n", " # Get a list of each player clan to see their stats and also add all information into one list/dataframe\n", " members_attack_data = [process_member_attacks(member) for member in data['clan']['members']]\n", "\n", " members_attacks_df = pd.DataFrame(members_attack_data)\n", " members_attacks_df['clan_tag'] = \"#\"+clan_tag\n", " members_attacks_df['war_end_ts'] = datetime.strptime(end_time, \"%Y%m%dT%H%M%S.%fZ\")\n", " members_attacks_df['timeLeft'] = str(time_left_in_hours)\n", " members_attacks_df['war_status'] = war_status\n", " \n", " members_attacks_df_list.append(members_attacks_df)\n", "except Exception as e:\n", " print(e)\n", "\n", "#Add all clans from the list into one Dataframe before inserting to BigQuery\n", "final_aggregated_clan_df = pd.concat(single_row_df_list)\n", "final_members_attacks_df = pd.concat(members_attacks_df_list)\n", "\n", "# clan_war_status = data.get('state')\n", "# final_members_attacks_df['war_status'] = clan_war_status" ] }, { "cell_type": "code", "execution_count": 10, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
    \n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
    user_clanTaguser_clanNameuser_clanLeveluser_attacksuser_starsuser_destructionPercentageopponent_clanTagopponent_clanNameopponent_clanLevelopponent_attacksopponent_starsopponent_destructionPercentagestartTimeendTimewar_end_tstimeLeftwar_status
    0#QG9UJ08RodeThe6002471966.4#98LLC0GJ••Sabotaje••243517.920240117T014836.000Z20240118T014836.000Z2024-01-18 01:48:3620.2inWar
    0#Q2JLCVLROneHive Patreon16000.0#2R0CP90RPTransfer Portal2000.020240117T204640.000Z20240118T204640.000Z2024-01-18 20:46:4039.2preparation
    0#P09YGYU2TheDonkeyShow25000.0#GR9G920RUNIVERSAL24000.020240117T161217.000Z20240118T161217.000Z2024-01-18 16:12:1734.6preparation
    \n", "
    " ], "text/plain": [ " user_clanTag user_clanName user_clanLevel user_attacks user_stars \\\n", "0 #QG9UJ08 RodeThe600 24 7 19 \n", "0 #Q2JLCVLR OneHive Patreon 16 0 0 \n", "0 #P09YGYU2 TheDonkeyShow 25 0 0 \n", "\n", " user_destructionPercentage opponent_clanTag opponent_clanName \\\n", "0 66.4 #98LLC0GJ ••Sabotaje•• \n", "0 0.0 #2R0CP90RP Transfer Portal \n", "0 0.0 #GR9G920R UNIVERSAL \n", "\n", " opponent_clanLevel opponent_attacks opponent_stars \\\n", "0 24 3 5 \n", "0 2 0 0 \n", "0 24 0 0 \n", "\n", " opponent_destructionPercentage startTime endTime \\\n", "0 17.9 20240117T014836.000Z 20240118T014836.000Z \n", "0 0.0 20240117T204640.000Z 20240118T204640.000Z \n", "0 0.0 20240117T161217.000Z 20240118T161217.000Z \n", "\n", " war_end_ts timeLeft war_status \n", "0 2024-01-18 01:48:36 20.2 inWar \n", "0 2024-01-18 20:46:40 39.2 preparation \n", "0 2024-01-18 16:12:17 34.6 preparation " ] }, "execution_count": 10, "metadata": {}, "output_type": "execute_result" } ], "source": [ "final_aggregated_clan_df" ] }, { "cell_type": "code", "execution_count": 11, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
    \n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
    player_tagnametotal_attacksfirst_attack_starssecond_attack_starsfirst_attack_destructionsecond_attack_destructionclan_tagwar_end_tstimeLeftwar_status
    0#9VG9GVLRDEADEYE0NaNNaNNaNNaN#QG9UJ082024-01-18 01:48:3620.2inWar
    1#UJLUULYUWolf23.02.0100.094.0#QG9UJ082024-01-18 01:48:3620.2inWar
    2#JRU8U80PMJGPTDAWG0NaNNaNNaNNaN#QG9UJ082024-01-18 01:48:3620.2inWar
    3#UU8QUPRHarperjs0NaNNaNNaNNaN#QG9UJ082024-01-18 01:48:3620.2inWar
    4#YU00U2P2Luke0NaNNaNNaNNaN#QG9UJ082024-01-18 01:48:3620.2inWar
    5#2JY0LU8LPnigel23.03.0100.0100.0#QG9UJ082024-01-18 01:48:3620.2inWar
    6#L29CPQL8Coro12.0NaN70.0NaN#QG9UJ082024-01-18 01:48:3620.2inWar
    7#GLLPC8V9piggles0NaNNaNNaNNaN#QG9UJ082024-01-18 01:48:3620.2inWar
    8#PL0RUUQPBK13.0NaN100.0NaN#QG9UJ082024-01-18 01:48:3620.2inWar
    9#9P8LRC8JYpt13.0NaN100.0NaN#QG9UJ082024-01-18 01:48:3620.2inWar
    0#Q99JC0VQ0NuttyPuttyDog®0NaNNaNNaNNaN#Q2JLCVLR2024-01-18 20:46:4039.2preparation
    1#2RV8YG8Ucoro0NaNNaNNaNNaN#Q2JLCVLR2024-01-18 20:46:4039.2preparation
    2#8PP0PJ8Qcoro0NaNNaNNaNNaN#Q2JLCVLR2024-01-18 20:46:4039.2preparation
    3#GV9CQQYPsolver0NaNNaNNaNNaN#Q2JLCVLR2024-01-18 20:46:4039.2preparation
    4#8GLL9RC9coro0NaNNaNNaNNaN#Q2JLCVLR2024-01-18 20:46:4039.2preparation
    0#JJ98YLJGrick flaire woo0NaNNaNNaNNaN#P09YGYU22024-01-18 16:12:1734.6preparation
    1#UJUGL8V8goPokesAJ0NaNNaNNaNNaN#P09YGYU22024-01-18 16:12:1734.6preparation
    2#2QJQYY9VRChip0NaNNaNNaNNaN#P09YGYU22024-01-18 16:12:1734.6preparation
    3#2Q28PJYGmax0NaNNaNNaNNaN#P09YGYU22024-01-18 16:12:1734.6preparation
    4#298G2GVJJuanMinMan0NaNNaNNaNNaN#P09YGYU22024-01-18 16:12:1734.6preparation
    5#9RQLQ20UKTM380Rider0NaNNaNNaNNaN#P09YGYU22024-01-18 16:12:1734.6preparation
    6#VUPRCGR8DepravedDonkey0NaNNaNNaNNaN#P09YGYU22024-01-18 16:12:1734.6preparation
    7#2GL8J9CCUThreePuttTeddy0NaNNaNNaNNaN#P09YGYU22024-01-18 16:12:1734.6preparation
    8#URRJQ8YDangerDonkey0NaNNaNNaNNaN#P09YGYU22024-01-18 16:12:1734.6preparation
    9#JCVL2JPLDireDonkey0NaNNaNNaNNaN#P09YGYU22024-01-18 16:12:1734.6preparation
    10#8LVG8RG8DreadfulDonkey0NaNNaNNaNNaN#P09YGYU22024-01-18 16:12:1734.6preparation
    11#2QPC8PPGNotJohnCoro0NaNNaNNaNNaN#P09YGYU22024-01-18 16:12:1734.6preparation
    12#CV220Q8GKTM380Rider20NaNNaNNaNNaN#P09YGYU22024-01-18 16:12:1734.6preparation
    13#2Y2G8YUJVChip20NaNNaNNaNNaN#P09YGYU22024-01-18 16:12:1734.6preparation
    14#QCGQUR8PTwoPuttTeddy0NaNNaNNaNNaN#P09YGYU22024-01-18 16:12:1734.6preparation
    \n", "
    " ], "text/plain": [ " player_tag name total_attacks first_attack_stars \\\n", "0 #9VG9GVLR DEADEYE 0 NaN \n", "1 #UJLUULYU Wolf 2 3.0 \n", "2 #JRU8U80P MJGPTDAWG 0 NaN \n", "3 #UU8QUPR Harperjs 0 NaN \n", "4 #YU00U2P2 Luke 0 NaN \n", "5 #2JY0LU8LP nigel 2 3.0 \n", "6 #L29CPQL8 Coro 1 2.0 \n", "7 #GLLPC8V9 piggles 0 NaN \n", "8 #PL0RUUQP BK 1 3.0 \n", "9 #9P8LRC8JY pt 1 3.0 \n", "0 #Q99JC0VQ0 NuttyPuttyDog® 0 NaN \n", "1 #2RV8YG8U coro 0 NaN \n", "2 #8PP0PJ8Q coro 0 NaN \n", "3 #GV9CQQYP solver 0 NaN \n", "4 #8GLL9RC9 coro 0 NaN \n", "0 #JJ98YLJG rick flaire woo 0 NaN \n", "1 #UJUGL8V8 goPokesAJ 0 NaN \n", "2 #2QJQYY9VR Chip 0 NaN \n", "3 #2Q28PJYG max 0 NaN \n", "4 #298G2GVJ JuanMinMan 0 NaN \n", "5 #9RQLQ20U KTM380Rider 0 NaN \n", "6 #VUPRCGR8 DepravedDonkey 0 NaN \n", "7 #2GL8J9CCU ThreePuttTeddy 0 NaN \n", "8 #URRJQ8Y DangerDonkey 0 NaN \n", "9 #JCVL2JPL DireDonkey 0 NaN \n", "10 #8LVG8RG8 DreadfulDonkey 0 NaN \n", "11 #2QPC8PPG NotJohnCoro 0 NaN \n", "12 #CV220Q8G KTM380Rider2 0 NaN \n", "13 #2Y2G8YUJV Chip2 0 NaN \n", "14 #QCGQUR8P TwoPuttTeddy 0 NaN \n", "\n", " second_attack_stars first_attack_destruction second_attack_destruction \\\n", "0 NaN NaN NaN \n", "1 2.0 100.0 94.0 \n", "2 NaN NaN NaN \n", "3 NaN NaN NaN \n", "4 NaN NaN NaN \n", "5 3.0 100.0 100.0 \n", "6 NaN 70.0 NaN \n", "7 NaN NaN NaN \n", "8 NaN 100.0 NaN \n", "9 NaN 100.0 NaN \n", "0 NaN NaN NaN \n", "1 NaN NaN NaN \n", "2 NaN NaN NaN \n", "3 NaN NaN NaN \n", "4 NaN NaN NaN \n", "0 NaN NaN NaN \n", "1 NaN NaN NaN \n", "2 NaN NaN NaN \n", "3 NaN NaN NaN \n", "4 NaN NaN NaN \n", "5 NaN NaN NaN \n", "6 NaN NaN NaN \n", "7 NaN NaN NaN \n", "8 NaN NaN NaN \n", "9 NaN NaN NaN \n", "10 NaN NaN NaN \n", "11 NaN NaN NaN \n", "12 NaN NaN NaN \n", "13 NaN NaN NaN \n", "14 NaN NaN NaN \n", "\n", " clan_tag war_end_ts timeLeft war_status \n", "0 #QG9UJ08 2024-01-18 01:48:36 20.2 inWar \n", "1 #QG9UJ08 2024-01-18 01:48:36 20.2 inWar \n", "2 #QG9UJ08 2024-01-18 01:48:36 20.2 inWar \n", "3 #QG9UJ08 2024-01-18 01:48:36 20.2 inWar \n", "4 #QG9UJ08 2024-01-18 01:48:36 20.2 inWar \n", "5 #QG9UJ08 2024-01-18 01:48:36 20.2 inWar \n", "6 #QG9UJ08 2024-01-18 01:48:36 20.2 inWar \n", "7 #QG9UJ08 2024-01-18 01:48:36 20.2 inWar \n", "8 #QG9UJ08 2024-01-18 01:48:36 20.2 inWar \n", "9 #QG9UJ08 2024-01-18 01:48:36 20.2 inWar \n", "0 #Q2JLCVLR 2024-01-18 20:46:40 39.2 preparation \n", "1 #Q2JLCVLR 2024-01-18 20:46:40 39.2 preparation \n", "2 #Q2JLCVLR 2024-01-18 20:46:40 39.2 preparation \n", "3 #Q2JLCVLR 2024-01-18 20:46:40 39.2 preparation \n", "4 #Q2JLCVLR 2024-01-18 20:46:40 39.2 preparation \n", "0 #P09YGYU2 2024-01-18 16:12:17 34.6 preparation \n", "1 #P09YGYU2 2024-01-18 16:12:17 34.6 preparation \n", "2 #P09YGYU2 2024-01-18 16:12:17 34.6 preparation \n", "3 #P09YGYU2 2024-01-18 16:12:17 34.6 preparation \n", "4 #P09YGYU2 2024-01-18 16:12:17 34.6 preparation \n", "5 #P09YGYU2 2024-01-18 16:12:17 34.6 preparation \n", "6 #P09YGYU2 2024-01-18 16:12:17 34.6 preparation \n", "7 #P09YGYU2 2024-01-18 16:12:17 34.6 preparation \n", "8 #P09YGYU2 2024-01-18 16:12:17 34.6 preparation \n", "9 #P09YGYU2 2024-01-18 16:12:17 34.6 preparation \n", "10 #P09YGYU2 2024-01-18 16:12:17 34.6 preparation \n", "11 #P09YGYU2 2024-01-18 16:12:17 34.6 preparation \n", "12 #P09YGYU2 2024-01-18 16:12:17 34.6 preparation \n", "13 #P09YGYU2 2024-01-18 16:12:17 34.6 preparation \n", "14 #P09YGYU2 2024-01-18 16:12:17 34.6 preparation " ] }, "execution_count": 11, "metadata": {}, "output_type": "execute_result" } ], "source": [ "final_members_attacks_df" ] }, { "cell_type": "code", "execution_count": 12, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Loaded 3 rows and 17 columns to fleet-parser-330316.luis_stage.stg_coc_clan_war_clan_information\n", "Loaded 30 rows and 11 columns to fleet-parser-330316.luis_stage.stg_coc_clan_war_player_information\n", "Updated fleet-parser-330316.luistest.stg_coc_clan_war_clan_information.\n", "Updated fleet-parser-330316.luistest.stg_coc_clan_war_player_information.\n" ] } ], "source": [ "#Load both dataframes as string to bigquery\n", "import_to_gbq(final_aggregated_clan_df.astype(str), 'fleet-parser-330316.luis_stage.stg_coc_clan_war_clan_information')\n", "import_to_gbq(final_members_attacks_df.astype(str), 'fleet-parser-330316.luis_stage.stg_coc_clan_war_player_information')\n", "try:\n", " query_job = client.query('CALL `fleet-parser-330316.luistest.sp_coc_clan_war_clan_information`();')\n", " results = query_job.result()\n", "\n", " if query_job.state == 'DONE':\n", " print('Updated fleet-parser-330316.luistest.stg_coc_clan_war_clan_information.')\n", "\n", " query_job = client.query('CALL `fleet-parser-330316.luistest.sp_coc_clan_war_player_information`();')\n", " results = query_job.result()\n", "\n", " if query_job.state == 'DONE':\n", " print('Updated fleet-parser-330316.luistest.stg_coc_clan_war_player_information.') \n", "except Exception as e:\n", " print(e)" ] } ], "metadata": { "kernelspec": { "display_name": "Python 3", "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.6" } }, "nbformat": 4, "nbformat_minor": 2 }