A na u na le mesebetsi e iphetang letsatsi le letsatsi, beke le beke? Ka mohlala, ho ngola litlaleho. U kopa lintlha, u li hlahlobisise, u li bone (etsa li-graph, lichate), ebe u li romela ho mookameli oa hau. Empa ho thoe'ng haeba sena sohle se ne se iketselitse?
Thutong ena re tla theha bot bakeng sa Telegraph e tla thusa ho tlaleha ka boits'oaro. 'Me ntho e pholileng ka ho fetisisa ke hore lenaneo lohle le tla ba le mela e 50 feela ea khoutu! Haeba u etsa bot bakeng sa Telegraph ka lekhetlo la pele, u lokela ho bala le ena .
Skillbox e khothaletsa: Tsela e sebetsang .
Re hopotsa: bakeng sa babali bohle ba "Habr" - theolelo ea li-ruble tse 10 ha u ngolisa thupelong efe kapa efe ea Skillbox u sebelisa khoutu ea papatso ea "Habr".
Ha re qaleng
Ho kenya lilaeborari
Re tla sebelisa ho fumana lintlha ho tsoa ho Google BigQuery. , и e tla u thusa ho bona data ea hau ka mahlo. e tla romella data e felileng ho Telegraph.
pip3 kenya google-cloud-bigquery matplotlib numpy pandas python-telegram-bot
E hokela Google BigQuery API
Haeba re batla ho sebelisa ts'ebeletso, re hloka ho hokela Google BigQuery API. Ho etsa sena re ea ho ebe u theha projeke e ncha (kapa khetha e seng e ntse e le teng).
Ka har'a phanele ea taolo, khetha NEBELLA APIS LE SERVICES 'me u batle BigQuery API.
Khetha Numella ho hokela API.
Etsa senotlolo sa akhaonto
Ha re ee ho hape , khetha "Credentials" tab ya, Etsa bopaki le senotlolo sa akhaonto ya Tshebeletso.
Ebe - Ak'haonte e ncha ea tšebeletso, 'me u kenye lebitso sebakeng sa lebitso la akhaonto ea Tšebeletso.
Ho tsoa lenaneng le theohang la Karolo, khetha Morero > Mong'a, ebe o Etsa.
Faele e tla jarolleloa ka bo eona e bitsoa creds.json.
Beha GOOGLE_APPLICATION_CREDENTIALS, u hlakise tsela e lebang creds.json ho theminale.
romella kantle ho naha GOOGLE_APPLICATION_CREDENTIALS='[PATH_TO_CREDS.JSON]'
Haeba tsohle li tsamaile hantle, ke nako ea ho qala ho ngola lenaneo.
Ho etsa kopo
Bakeng sa thupelo re tla sebelisa data ho tloha bigquery-public-data.stackoverflow, bakeng sa tlaleho ea rona re tla khetha palo ea likhatiso tsa letsatsi le letsatsi.
Ntho e 'ngoe le e 'ngoe e bonolo.
Botsa tafole -> Bona data ka mahlo -> Boloka pono -> Romella setšoantšo
Ha re theheng mosebetsi o le mong ho hlalosa khoele ka 'ngoe.
Potso ho BigQuery
Pele re kenya laeborari.
ho tsoa ho google.cloud import bigquery
Re theha tšebetso e bitsoang query_to_bigquery, moo paramethara e botsoang.
def query_to_bigquery(query):
client = bigquery.Client()
query_job = client.query(query)
result = query_job.result()
dataframe = result.to_dataframe()
return dataframeTs'ebetso ena e tla khutlisa kopo joalo ka foreimi ea data.
Ho bona data ka mahlo
Ho rarolla bothata bona, khetha matplotlib.
kenya matplotlib.pyplot joalo ka plt
Re hloka liparamente tse hlano, moo x e leng data ea x-axis, x_label ke sehlooho sa axis, y ke data ea y-axis, y_label ke sehlooho sa axis, 'me sehlooho ke sehlooho sa pono eohle.
def visualize_bar_chart(x, x_label, y, y_label, title):
plt.title(title)
plt.xlabel(x_label)
plt.ylabel(y_label)
index = np.arange(len(x))
plt.xticks(index, x, fontsize=5, rotation=30)
plt.bar(index, y)
return pltBoloka setšoantšo
Joale ha re sebeliseng mesebetsi e 'meli ho theha pono le ho e boloka.
Re tla romella palo ea lipapatso tse hatisitsoeng letsatsi le letsatsi. Pele re ngola kopo.
query = """
SELECT DATE(creation_date) date, COUNT(*) total_posts
FROM `bigquery-public-data.stackoverflow.post_history`
GROUP BY 1
HAVING date > DATE_SUB('2018-12-02', INTERVAL 14 DAY)
ORDER BY 1
"""Potso e thusa ho bokella lintlha tsa libeke tse peli ho qala ka la 2 Tšitoe 2018.
Re sebelisa letsatsi lena hobane 2018-12-02 ke lintlha tsa morao-rao tse tlalehiloeng ho bigquery-public-data.stackoverflow.post_history, maemong a mang u ka sebelisa CURRENT_DATE() ho fumana lintlha tse ncha.
Letsetsa query_to_bigquery ho fumana lintlha.
dataframe = query_to_bigquery(potso)
Ebe re sebelisa kholomo ea data ea letsatsi bakeng sa axis ea x, le kholomo ea kakaretso_ea poso bakeng sa axis ea y.
x = dataframe['date'].tolist()
y = dataframe['total_posts'].tolist()
Re e bona ka mahlo a kelello re sebelisa ts'ebetso ea visualize_bar_chart ebe re e boloka e le setšoantšo.
plt = visualize_bar_chart(x=x, x_label='Letsatsi', y=y, y_label='Kakaretso ya Diphouseta', title='Melaetsa ya Letsatsi le Letsatsi')
plt.savefig('viz.png')
Re thatela khoutu ena ka tšebetso e bitsoang get_and_save_image.
def get_and_save_image():
query = """
SELECT DATE(creation_date) date, COUNT(*) total_posts
FROM `bigquery-public-data.stackoverflow.post_history`
GROUP BY 1
HAVING date > DATE_SUB('2018-12-02', INTERVAL 14 DAY)
ORDER BY 1
"""
dataframe = query_to_bigquery(query)
x = dataframe['date'].tolist()
y = dataframe['total_posts'].tolist()
plt = visualize_bar_chart(x=x, x_label='Date', y=y, y_label='Total Posts', title='Daily Posts')
plt.savefig('viz.png')Romela setšoantšo
E le hore u romele tlaleho ho moamoheli, u lokela ho tseba parameter chat_id.
Re sebelisa ebe o thaepa /start. Bot e araba ka tlhaiso-leseling e hlokahalang, chat_id e teng tšimong ea id.
Joale ha re theheng mosebetsi oa send_image. E tla sebelisa mosebetsi oa get_and_save_image ho fumana le ho boloka setšoantšo. 'Me joale re romela ntho e' ngoe le e 'ngoe ho lebitso le nepahetseng.
def send_image(bot, update):
get_and_save_image()
chat_id = 'CHAT_ID_RECEIVER'
bot.send_photo(chat_id=chat_id, photo=open('viz.png','rb'))Lenaneo le ka sehloohong
Qetellong, re theha ts'ebetso e 'ngoe, e ka sehloohong, ho qala kopo. Se ke oa lebala ho fetola YOUR_TOKEN bakeng sa bot.
Hopola: lenaneo lena le tla romella setšoantšo ka bohona ka nako eo u e boletseng. Ka mohlala, re tla romela tlaleho ka hora ea borobong hoseng letsatsi le leng le le leng.
def main():
updater = Updater('YOUR_TOKEN')
updater.job_queue.run_daily(send_image, time=datetime.datetime.strptime('9:00AM', '%I:%M%p').time(), days=(0,1,2,3,4,5,6))
updater.start_polling()
updater.idle()
if __name__ == '__main__':
main()Ka lebaka leo, kopo ea rona e tla shebahala tjena:
from google.cloud import bigquery
from telegram.ext import Updater
import matplotlib.pyplot as plt
import numpy as np
import datetime
def query_to_bigquery(query):
client = bigquery.Client()
query_job = client.query(query)
result = query_job.result()
dataframe = result.to_dataframe()
return dataframe
def visualize_bar_chart(x, x_label, y, y_label, title):
plt.title(title)
plt.xlabel(x_label)
plt.ylabel(y_label)
index = np.arange(len(x))
plt.xticks(index, x, fontsize=5, rotation=30)
plt.bar(index, y)
return plt
def get_and_save_image():
query = """
SELECT DATE(creation_date) date, COUNT(*) total_posts
FROM `bigquery-public-data.stackoverflow.post_history`
GROUP BY 1
HAVING date > DATE_SUB('2018-12-02', INTERVAL 14 DAY)
ORDER BY 1
"""
dataframe = query_to_bigquery(query)
x = dataframe['date'].tolist()
y = dataframe['total_posts'].tolist()
plt = visualize_bar_chart(x=x, x_label='Date', y=y, y_label='Total Posts', title='Daily Posts')
plt.savefig('viz.png')
def send_image(bot, update):
get_and_save_image()
chat_id = 'CHAT_ID_RECEIVER'
bot.send_photo(chat_id=chat_id, photo=open('viz.png', 'rb'))
def main():
updater = Updater('YOUR_TOKEN')
updater.job_queue.run_daily(send_image, time=datetime.datetime.strptime('9:00AM', '%I:%M%p').time(), days=(0,1,2,3,4,5,6))
updater.start_polling()
updater.idle()
if __name__ == '__main__':
main()Boloka faele 'me u e bitse main.py.
Re qala ts'ebeliso ka ho kenya taelo ho terminal:
python3 main.py
Tsohle di lokile. Hona joale re na le roboto e nang le mela e 50 ea khoutu e hlahisang litlaleho ntle le ho kenella ha rona.
Ha re hlahlobe bot ka ho khetha taelo ea /send.
U ka fumana khoutu e felileng ho .
Skillbox e khothaletsa:
- Thuto ea lilemo tse peli e sebetsang .
- Tsela ea inthanete .
- Thuto ea selemo e sebetsang .
Source: www.habr.com