Skip to content

Instantly share code, notes, and snippets.

@dirkschumacher dirkschumacher/bvg.R
Last active Jul 29, 2016

Embed
What would you like to do?
Query BVG billing data from gmail with R
# Use this code if you use the official BVG Berlin app
# I only tested it with credit card billing
# The BVG sends you an email for every purchase you make.
# The following code tries to find all those mails and analyzes them.
library(gmailr)
library(purrr)
library(stringr)
library(lubridate)
library(ggplot2)
library(dplyr)
library(broom)
# auth gmail
gmail_auth('client_id.json')
# get all bvg messages
all_messages <- gmailr::messages("bvg-onlineshop bestellbestätigung")
bvg_raw_messages <- all_messages[[1]]$messages %>% map(~ gmailr::message(.x$id)) %>%
map(~rawToChar(base64enc::base64decode(.x$payload$body$data)))
# extract price and time
price <- str_match_all(bvg_raw_messages, "Abbuchungsbetrag: € ([0-9]+,[0-9]+)")
time <- str_match_all(bvg_raw_messages, "Bestellung vom ([0-9]{1,2}.[0-9]{1,2}.[0-9]{4}) ([0-9]{1,2}:[0-9]{1,2}) Uhr")
# join data into a data.frame
result <- map2(price, time, function(p, t) {
data.frame(datetime = parse_date_time(paste0(t[, 2], " ", t[, 3]), "d.m.Y H:M"), price = as.numeric(str_replace(p[, 2], ",", ".")))
}) %>% bind_rows %>%
arrange(datetime) %>%
mutate(cumprice = cumsum(price))
# cumulative price over time
ggplot(data = result, aes(x = datetime, y = cumprice)) +
geom_line()
# fill months without records
dt_seq <- seq(min(result$datetime), Sys.time(), by = "1 month")
empty_records <- data.frame(datetime = dt_seq, price = NA_real_)
# data by month
# months with no data filled with 0/NA
data_by_month <- bind_rows(result, empty_records) %>%
mutate(year_month = paste0(year(datetime), "_", str_pad(month(datetime), 2, pad = "0"))) %>%
group_by(year_month) %>%
summarise(price = sum(price, na.rm = TRUE), trips = n() - 1) %>%
mutate(trips = ifelse(trips == 0, NA_integer_, trips))
arrange(year_month)
# plot price by month and the number of trips as dots.
ggplot(data = data_by_month, aes(x = year_month, y = price)) +
geom_bar(stat = "identity") +
geom_point(aes(y = trips), color = "red") +
xlab("Year_Month") +
ylab("Money spent as bars / #trips as dots") +
ggtitle("Berlin BVG spending")
# LICENSE
# This is free and unencumbered software released into the public domain.
#
# Anyone is free to copy, modify, publish, use, compile, sell, or
# distribute this software, either in source code form or as a compiled
# binary, for any purpose, commercial or non-commercial, and by any
# means.
#
# In jurisdictions that recognize copyright laws, the author or authors
# of this software dedicate any and all copyright interest in the
# software to the public domain. We make this dedication for the benefit
# of the public at large and to the detriment of our heirs and
# successors. We intend this dedication to be an overt act of
# relinquishment in perpetuity of all present and future rights to this
# software under copyright law.
#
# THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
# EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
# MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT.
# IN NO EVENT SHALL THE AUTHORS BE LIABLE FOR ANY CLAIM, DAMAGES OR
# OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE,
# ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR
# OTHER DEALINGS IN THE SOFTWARE.
#
# For more information, please refer to <http://unlicense.org>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.