Jak stworzyć (inteligentne) raporty na żądanie

Dzisiaj przygotujemy(quasi) inteligentnie narzędzie do automatycznego generowania raportów, które będzie odpowiadać na pytania zadane przez email. Za jego pomocą pozbędziemy się upierdliwej pracy. Wraz z aktualizacją pakietu gmailr do wersji 1.0 prezentowany poniżej kod związany z logowaniem do konta może nie działać. Przeczytaj proszę informację o tym, jak zmienić kod, ten na GitHubie powinien być ok.

Łukasz Prokulski. Data analyst / R developer / PMO Manager w PZU. Uwielbia zajmować się liczbami, ich wizualizacją i wyciąganiem wniosków na podstawie ich analizy. Zna doskonale Excela, potrafi przygotować raporty w kilku narzędziach, które wykorzystują dane z różnych źródeł. Jest fanem i popularyzatorem języka R, czego dowody znajdziecie na blogu.

Problem z jakim można się często spotkać w korporacjach to wysyłanie tych samych raportów mailem. Wyobraźmy sobie taki scenariusz:

• są trzy biura sprzedaży, każde jest odpowiedzialne za inny region,
• jesteś specem od generowania raportów ze sprzedaży dla tych biur,
• wszystkie raporty są takie same, różnią się tylko regionem,
• co chwilę ktoś pyta: podeślij mi aktualny raport dla mojego regionu,
• więc generujesz ten raport i odsyłasz mailem w odpowiedzi.

Znasz to? Ja znam. Upierdliwe jak cholera. Można zrobić jakiś dashboard (w Shiny czy innym ustrojstwie), ale ludzie zapominają, gdzie można go znaleźć (jak zrobiłem kilka takich, to teraz dzwonią z pytaniem o link do tych dashboardów…). A gdyby tak samo się robiło? Z aktualnych danych? Dzisiaj coś takiego napiszemy!

Założenie jest takie, że pytanie należy zadać poprzez wysłanie maila o odpowiednim tytule na odpowiednią skrzynkę mailową.

Zaczniemy od algorytmu:

• sprawdź, czy są nowe pytania na skrzynce,
• sprawdź, czego dotyczy pytanie (jaki temat ma mail),
• przygotuj odpowiedni raport,
• odeślij do pytającego.

W pierwszej kolejności trzeba dostać się do skrzynki i sprawdzić czy są nowe maile. Już prawie rok temu o tym pisałem, wykorzystałem wówczas pakiet gmailr. Tym razem będzie tak samo, też wykorzystamy konto na Gmailu.

W przypadku korporacyjnej poczty trzeba użyć innych rozwiązań, ale na pewno się da. Osobiście tego nie badałem, ale na StackOverflow jest kod z użyciem Pythona (osadzony przez rJython – można przepisać na reticulate) oraz jest pakiet imapr, który robi coś ze skrzynkami IMAP.

Wróćmy jednak do Gmaila. We wspomnianym wpisie znajdziecie minimalne informacje jak w konsoli dla developerów wyklikać odpowiednią aplikację pozwalającą na dostęp do skrzynki poprzez Gmail API – potrzebujemy odczytywać, wysyłać i modyfikować (przenosić do kosza) maile. Odpowiednie uprawnienia dostaniemy w pliku JSON (w kodzie niżej jest to plik gmail_api_key.json).

Żeby cała maszyneria działała potrzebujemy:

• dostępu do skrzynki Gmail po API,
• serwera z Linuxem (z takiego założenia wychodzę) albo czegoś, co pozwoli nam uruchomić w zadanych odstępach czasu skrypty R – potrzebny będzie więc cron albo inny task scheduler (może być w Windowsie),
• skonfigurowanego dla R renderera PDFów z plików markdown (przy instalacji RStudio to się chyba samo instaluje w Linuxie, na Windows zdaje się trzeba doinstalować ręcznie pandoc).

Teraz czas na kodowanie. Najpierw autoryzujemy się w Gmailu i pobieramy najnowsze maile (skrypt process_inbox.R):

library(gmailr)
library(tidyverse)
library(lubridate)
 
# autoryzacja w Gmail
use_secret_file("gmail_api_key.json")
 
# czego szukamy? Dzisiejsze maile w "inbox"
# reguły można bardziej skomplikować - np. sprawdzając czy są w temacie odpowiednie słowa
gmail_search_query = paste0("in:inbox after:", Sys.Date())
 
# pobieramy maile (max 9999 sztuk)
ids <- messages(search = gmail_search_query, num_results = 9999)
ids <- gmailr::id(ids)

Mając identyfikatory maili możemy pobrać interesujące nas informacje – wystarczy nadawca i temat. Odpowiednia funkcja zrobi to dla podanego ID:

get_mail_content <- function(id) {
  # pobierz maila o podanym id ze skrzynki
  mail_message <- message(id, format = 'full')
 
  # czysty nadawca czyli sam adres email
  from_raw = str_match(gmailr::from(mail_message), "<(.*@.*)>")[[2]]
  if(is.na(from_raw)) from_raw = gmailr::from(mail_message)
 
  tibble(from = gmailr::from(mail_message),       # nadawca
         from_raw = from_raw,                     # oczyszczona wersja nadawcy
         subject = gmailr::subject(mail_message)) # tytuł maila
}

Teraz możemy przepuścić wszystkie identyfikatory maili przez tę funkcję:

grabbed_mails <- ids %>%
  map_dfr(get_mail_content) %>%
  mutate(message_id = ids)

Jeśli nie dbamy o inbox:0 to chwilę to może potrwać (chyba, że odpowiednio skonstruowaliśmy filtr)… ale po to był warunek wyszukiwania ograniczający zapytanie do jedynie dzisiejszych maili, co w zupełności wystarczy, bo sprawdzanie skrzynki będzie odbywało się często (co kilka minut).

Zatem mamy listę maili ze skrzynki. W założeniu przyjęliśmy, że pytania zawarte są w tytule maila. Zanim jednak będziemy odpowiadać na maile – przygotujmy odpowiednie odpowiedzi.

Chcę pokazać Wam dwa rozwiązania – jedno odpowiedź w treści maila, a drugie – jako załącznik z pełnym raportem.

Zacznijmy od odpowiedzi w treści. Zakładać można sytuację, w której raport nie musi być wygenerowany online, bo dane są wolno zmienne. Taki wariant przyjmiemy. Odpowiedzią na pytanie daj wykop będzie lista wykopalisk nie starszych niż 12 godzin z głównej strony Wykop.pl. Przygotujmy taką dość statyczną odpowiedź (to nowy, oddzielny skrypt – make_wykop_mail.R) przy okazji poznając odrobinę API Wykopu:

library(tidyverse)
library(httr)
library(glue)
library(jsonlite)
library(lubridate)
 
# tutaj mamy zapisane klucze do API Wykopu
load("wykop_api.rda") # zmienne wykop_api_key oraz wykop_secret_key
 
# pytamy API Wykopu o linki ze strony głównej (dostaniemy 25 sztuk)
response <- GET(paste0("https://a2.wykop.pl/Links/Promoted/appkey/", wykop_api_key))
 
# tłumaczymy JSON na data.frame
json <- rawToChar(response$content)
wykopki <- as_tibble(fromJSON(json)$data)
 
# zostawiamy sobie tylko te z ostatnich 12 godin
wykopki <- wykopki %>%
  filter(date >= now() - hours(12))
 
# budujemy HTMLa będącego treścią maila
wykstr <- wykopki %>%
  # formatujemy datę wykopaliska
  mutate(date = ymd_hms(date) %>% format("%e/%m @ %H:%M")) %>%
  # z kolejnych wierszy data.frame składamy tabelkę, żeby ładnie wyglądało w mailu ale też w jego kodzie źródłowym
  mutate(prev = glue('<tr>
                      t<td colspan="2"><a href="{source_url}"><h3>{title}</h3></a></td>
                      </tr>
                      <tr>
                      t<td rowspan="2"><a href="{source_url}"><img src="{preview}" /></a></td>
                      t<td><strong>{description}</strong></td>
                      </tr>
                      <tr>
                      t<td>{date} | <a href="https://www.wykop.pl/link/{id}">Wykopy: {vote_count} | Komentarze: {comments_count}</a></td>
                      </tr>')) %>%
  # przygotowane wiersze sklejamy w jeden długi tekst
  pull(prev) %>%
  paste(., collapse = "n") %>%
  # dodajemy nagłówek i stopkę do wierszy tabeli
  paste0('<center><h3>Strona główna Wykop.pl</h3>n',
         '<p>znaleziska dodane w ostatnich 12 godzinach<br/>',
         '<em>(aktualizacja: ', format(now(), "%e/%m %H:%M"), ')</em></p>n',
         'n<table width="80%" cellspacing="5px">n', ., 'n</table>n</center>n')
 
# gotowy HTML zapisujemy do pliku na dysku
write_lines(wykstr, "wykop.html")

Uruchomienie powyższego skryptu (w założeniu co jakiś czas) przygotuje statyczny plik z HTMLem gotowym do wklejenia w treść maila.

Drugie zadanie to raport z danych online (tutaj też trochę wolno zmiennych, ale nie ma to znaczenia). Tym razem ma to być załącznik w PDFie, więc przygotujemy odpowiedni dokument RMarkdown, który po skompilowaniu da PDFa.

Jak pewnie wiecie RMarkdown pozwala na mieszanie treści z kodem (w R, ale też np. w Pythonie przy pomocy pakietu reticulate), co więcej – wynik działania kodu pojawia się w treści dokumentu. Cały dokument bitcoin.Rmd poniżej:

---
title: "Notowania Bitcoin"
output: 
  pdf_document: 
    latex_engine: xelatex
params:
  odbiorca_raportu: "nieznany"
---
```{r, include=FALSE}
setwd("~/RProjects/automatic_reports")
library(tidyverse)
library(lubridate)
library(httr)
library(jsonlite)
library(knitr)
 
# opcje chunków
opts_chunk$set(echo = FALSE, message = FALSE, error = FALSE, warning = FALSE)
options(knitr.table.format = "latex")
 
# klucz API zapisany w pliku (jako string w zmiennej api_key)
load("nomics_apikey.rda")
```
```{r grab_data_30d}
# pobieramy historię z ostatnich 30 dni - to będą dane dzienne
start_date <- today()-days(5)
 
query <- paste0("https://api.nomics.com/v1/currencies/sparkline?key=", api_key, "&start=", start_date ,"T00%3A00%3A00Z")
 
res <- GET(query)
 
temp_df <- fromJSON(rawToChar(res$content), flatten = TRUE)
 
# tylko interesujące nas walory
df_30d <- temp_df %>%
  filter(currency %in% c("BTC", "BTCBEP2", "RBTC", "WBTC")) %>%
  # "rozpakowanie" zagnieżdżonych tabelek
  unnest(cols = c(timestamps, prices)) %>%
  # poprawa typów kolumn
  mutate(timestamps = ymd_hms(timestamps, tz = "UTC") %>% with_tz("Europe/Warsaw"),
         prices = as.numeric(prices))
```
```{r grab_data_1d}
# pobieramy historię z dzisiaj - to będą dane godzinowe
start_date <- today()
 
query <- paste0("https://api.nomics.com/v1/currencies/sparkline?key=", api_key, "&start=", start_date ,"T00%3A00%3A00Z")
 
res <- GET(query)
 
temp_df <- fromJSON(rawToChar(res$content), flatten = TRUE)
 
df_1d <- temp_df %>%
  filter(currency %in% c("BTC", "BTCBEP2", "RBTC", "WBTC")) %>%
  unnest(cols = c(timestamps, prices)) %>%
  mutate(timestamps = ymd_hms(timestamps, tz = "UTC") %>% with_tz("Europe/Warsaw"),
         prices = as.numeric(prices))
```
```{r join_data}
# łączymy dane dzienne i godzinowe
df_full <- bind_rows(df_1d, df_30d) %>%
  # dzisiejsza godzina 00:00 się powtarza - zostawiamy tylko jedną
  distinct(currency, timestamps, .keep_all = TRUE)
```
 
Na podstawie danych z nomics.com. Aktualizacja: `r now()`.
 
Przygotowane dla: *`r params$odbiorca_raportu`*.
 
 
# WYKRES NOTOWAŃ
 
```{r plot_data}
# rysujemy wykres z notowaniami poszczególnych papierów
df_full %>%
  ggplot() +
  geom_line(aes(timestamps, prices, color = currency), size = 2, show.legend = FALSE) +
  theme_minimal() +
  theme(legend.position = "bottom") +
  labs(x = "", y = "Cena [USD]") +
  facet_wrap(~currency, ncol = 2)
```
 
# TABELA Z NOTOWANIAMI
 
```{r print_table}
# przygotowujemy tabelkę z notowaniami
df_full %>%
  mutate(prices = sprintf("%.3f", prices)) %>%
  # każdy papier w swojej kolumnie
  spread(currency, prices, fill = "--") %>%
  arrange(timestamps) %>%
  mutate(timestamps = format(timestamps, "%Y-%m-%d %H:%M")) %>%
  rename(`Data notowania` = timestamps) %>%
  kable()
```

Mam nadzieję, że kod jest wystarczająco skomentowany i poradzicie sobie ze zrozumieniem. Proszę zwrócić uwagę na początek:

params:
  odbiorca_raportu: "nieznany"

i gdzieś dalej r params$odbiorca_raportu. To zapewni nam, że każdy raport będzie generowany inaczej, w zależności od podanego parametru odbiorca_raportu. Oczywiście parametry mogą być różne: może to być region, może to być zakres dat albo właśnie zamawiający (i na tej podstawie określamy region czy jakiś inny poziom dostępu do danych i uprawnień). Parametrów tych będziemy używać za chwilę przy renderowaniu raportu.

Nasz przykładowy raport pobiera dane z API serwisu nomics.com dotyczący notować bitcoinów (przed sprawdzeniem czy to działa potrzebujecie pliku nomics_apikey.rda z zapisaną zmienną zawierającą klucz API). Przy każdym renderowaniu dane są pobierane, nieco przetwarzane i rysowane. Wynik trafia do PDFa. O tym, że raport jest wygenerowany online w tym przypadku świadczy informacja w treści samego raportu (czas aktualizacji). Po zapisaniu tego dokumentu na dysku możecie go knit-nąć – powinien powstać gotowy dokument.

Oczywiście mogą to być inne dane – z bazy SQL, z jakiegoś Hadoopa czy Kafki. Może to być wynik działania jakiegoś modelu na zebranych danych – do wyboru, do koloru.

Krótkie podsumowanie tego co mamy w tym momencie:

• pobieramy dzisiejsze maile z inboxa skrzynki na Gmailu,
• generujemy statyczne kawałki HTMLa z informacjami z Wykopu,
• mamy pełny raport (do wyrenderowania) z danych online generowany w locie do PDF.

Jesteśmy w więcej niż połowie drogi. Pozostało sprawdzenie co wysłać (na podstawie tematu otrzymanego maila), przygotowanie odpowiedzi i wysyłka. Zacznijmy od dwóch funkcji, które przygotują i wyślą nam odpowiednią treść. Piszemy więc ciąg dalszy skryptu process_inbox.R.

Najpierw to co prostsze – Wykop:

send_wykop <- function(reciver) {
  # funkcja przygotowuje maila z informacjami z Wykopu i go wysyła do "reciver"
 
  # wczytujemy odpowiedź ze statycznego HTMLa
  wykop_str <- read_lines("wykop.html") %>%
    paste0(., collapse = "")
 
  # budujemy maila
  email <- mime() %>%
    to(reciver) %>%
    subject("Hity dnia z Wykopu") %>%
    html_body(wykop_str)
  
  # wysyłamy
  ret_val <- send_message(email)
}

Druga wersja wymaga wygenerowania dedykowanego (pamiętajcie o parametrach!) PDFa:

send_bitcoin <- function(reciver) {
  # funkcja przygotowuje dedykowanego PDFa i wysyła go jako załącznik do "reciver"
 
  # potrzebujemy pliku tymczasowego na raport w PDFie
  temp_raport_file = tempfile(fileext = ".pdf")
 
  # renderujemy PDFa do pliku tymczasowego 
  rmarkdown::render("bitcoin.Rmd",
                    output_file = temp_raport_file,
                    # parametry dla raportu
                    params = list(odbiorca_raportu = reciver),
                    quiet = TRUE)
 
  # gmailr ma problem z załącznikiem i treścią jednocześnie, więc:
  # hack via https://github.com/r-lib/gmailr/issues/60
  body_txt <- "Raport znajdziesz w załączniku"
  email <- mime() %>%
    to(reciver) %>%
    subject("Notowania BTC") %>%
    html_body(body_txt) %>%
    attach_part(body_txt) %>%
    attach_file(temp_raport_file, type = "application/pdf")
 
  # wysyłamy maila
  ret_val <- send_message(email)
 
  # kasujemy plik tymczasowy
  unlink(temp_raport_file)
}

Wszystko gotowe, tylko nie działa. Dlaczego? Ano dlatego, że jeszcze nie sprawdzamy czy mamy odpowiednie pytanie w tytule maila oraz nie reagujemy na nie. Prosta pętla, która przejdzie przez wszystkie maile na koniec skryptu process_inbox.R:

for(i in seq_len(nrow(grabbed_mails))) {
 
  # od kogo mail?
  reciver <- as.character(grabbed_mails[i, 'from_raw'])
 
  # czy "daj wykop"?
  if(str_to_lower(grabbed_mails[i, "subject"]) == "daj wykop") {
    # przygotuj i wyślij odpowiedź (wykop)
    send_wykop(reciver)
    # przenosimy maila z pytaniem do kosza - żeby nie mieć go w przyszłości w inboxie
    trash_message(as.character(grabbed_mails[i, "message_id"]))
  }
 
  # czy "daj bitcoin"?
  if(str_to_lower(grabbed_mails[i, "subject"]) == "daj bitcoin") {
    # przygotuj i wyślij odpowiedź (raport)
    send_bitcoin(reciver)
    # przenosimy maila z pytaniem do kosza
    trash_message(as.character(grabbed_mails[i, "message_id"]))
  }
}

Już za chwileczkę, już za momencik zacznie się kręcić! Właściwie już działa, o ile poczynimy ręcznie następujące kroki (w tej właśnie kolejności):

• uruchomimy make_wykop_mail.R żeby przygotować treść maila dotyczącego Wykopu,
• wyślemy sobie (na skrzynkę do której mamy dostęp przez API Gmaila) wiadomość z tytułem “daj wykop”,
• wyślemy sobie maila z tytułem “daj bitcoin”,
• uruchomimy process_inbox.R.

W odpowiedzi powinniśmy dostać dwa maile – z newsletterem Wykopu oraz drugi z raportem w PDFie o cenie bitcoinów. No chyba, że coś nie będzie działało.

Ale przecież nie po to robi się automaty, żeby je ręcznie uruchamiać! Dodajemy więc do crona (albo innego task schedulera) dwa zadania: jedno to uruchomienie make_wykop_mail.R na przykład co 15 minut, drugie – uruchomienie process_inbox.R na przykład co 3 minuty (to określi jak często będziemy sprawdzać inbox i odpowiadać). U mnie te wpisy wyglądają tak:

# automatyczne odpowiedzi na maile
*/15 * * * * Rscript /home/lemur/RProjects/automatic_reports/make_wykop_mail.R
*/3 * * * * Rscript /home/lemur/RProjects/automatic_reports/process_inbox.R

Cała maszyna działa na moim serwerze (na przykład tu możesz sobie taki kupić i tak skonfigurować) i odpowiada na maile wysłane na adres prokulski@gmail.com z tytułem (bez dodatkowych znaków, spacji na końcu itd.) daj wykop lub daj bitcoin – możecie sprawdzić. Uwaga – nie wiem jak długo w cronie to będzie wisiało, pewnie kiedyś wyłączę.

Gotowe pliki znajdziecie w repozytorium automatic_reports na moim GitHubie. Jeśli budujecie to rozwiązanie u siebie to warto w odpowiednich skryptach zadbać o to, aby pliki zapisywały i pobierały się z odpowiednich folderów (zwykłe i niezbyt koszerne setwd() na początek skryptów wystarczy). Oczywiście trzeba dostosować odpowiednio też wpisy w cronie.

Można przygotować kolejne rozwiązania:

• sprawdzać treść maila, a nie tylko jego tytuł. A w treści, w kolejnych liniach mogą być na przykład określone parametry,
• można pobierać dane z załączników przychodzących maili i przetwarzać te załączniki: wyobrażam sobie, że wysyłamy plik Excela, w treści maila piszemy, które kolumny nas interesują, a w odpowiedzi dostajemy stosowny wykres z danych zawartych w Excelu,
• można zaprząc sztuczną inteligencję czy jakieś metody machine learningowe do analizowania treści maila i na podstawie tej analizy odpowiadać – automatyczne, inteligentne odpowiedzi w biurach obsługi klienta to byłoby coś!

Inspiracją do tego wpisu był tekst Automated Report Generation with Papermill oraz jego druga część, gdzie podobny problem rozwiązany jest inaczej (według mnie nieco gorzej).

Jeśli Ci się podobało albo przyda się do czegoś to podziel się wpisem ze światem (odpowiednie guziczki poniżej). Wpadnij też na Dane i Analizy na Facebooku – tam więcej takich smaczków (szczególnie dla praktyków). Nieco więcej smaczków znajdziesz też w nie-tak-bardzo cyklicznym newsletterze, którego archiwum tutaj.

Możesz też rzucić piniądz autorowi, czy tam postawić witrualną kawę – czy Wykop daje Ci newsletter? No właśnie. A serwerki same się nie opłacą…

Artykuł został pierwotnie opublikowany na blog.prokulski.science. Zdjęcie główne artykułu pochodzi z unsplash.com.

Oferty pracy z