"""
This one is dedicated to Murdo who wondered whether he could just pull it
all into an excel spreadsheet instead messing around the console.
Yes we can!
"""
from eighty_tools import database, ga_query
# Get all hotels from database
all_hotels = database.Get("Hotels").get_all_in_benchmarking()
# Set up the query for one of the hotels
q = ga_query.Get(hotel_data=all_hotels[0])
# Get data in 'pandas' (excel-like format)
results = q.get_handled_data(handler="pandas")
# Notice that the data is returned in a dictionary, so you'll
# need to access the pandas part using the '0,0,0' key
print(results)
# Use .to_excel to create an excel file
results['0,0,0'].to_excel('results.xlsx')
us. For example, when printed, it looks like an excel table, and it can be
easily downloaded to excel using the .to_excel() method. It can also
quickly sum or average the data.
The above snippet shows how to get the data in the pandas dataframe
structure, and some very basic common operations you can do with it.
However, pandas is a very powerful and large library, and hence I suggest
you have a look at one of the tutorials to get to know this library better.
I quite like this one:
https://www.tutorialspoint.com/python_pandas/python_pandas_introduction_to_data_structures.htm
"""
#### GET DATA FROM GA AS A PANDAS DATAFRAME ####
from eighty_tools import database, ga_query, ga_tools
# Get hotel settings from the database
hotel = database.Get("Hotels").get_one_by_id(7)
# Generate customised hotel segments since hotel from the database
hotel_seg = ga_tools.get_segments_for_hotel(hotel)
# Create Google Analytics Query
q = ga_query.Get(hotel_data=hotel,
segments=hotel_seg['basic'], # Filters group account if necessary, get's rid of voucher traffic
metrics="ga:sessions, ga:users, ga:transactionRevenue",
dimensions="ga:deviceCategory")
# Get results as a Pandas dataframe
r = q.get_handled_data(handler='pandas')
df = r['0,0,0'] # Get the first response (see https://80days.github.io/benchmark-tools/ga_query#request-ids)
#### PANDAS DATAFRAME MANIPULATION ####
# http://pandas.pydata.org/pandas-docs/stable/
"""
Say you wanted to get a list of all the hotels in the London 5 Star set,
so you could compare how many rooms do hotels in this set have on average.
Doing this manually would take too long, because these reports can have
hundreds of hotels. However, all of these sets are saved in the database,
so all you need to do is to select the "BM_reports" table, get your report,
get the hotel_ids associated with this report, and finally retrieve the
hotels from the database using their hotel ids.
"""
>>> from eighty_tools import database
# Find the id of your report from the BM website, or use .get_all() instead to get the list of all the reports
>>> my_report = database.Get("BM_reports").get_one_by_id(7)
>>> print(my_report)
{
'id': 7,
'name': 'London - 5 Star',
'frequency': 1,
'currency': 'GBP',
'type': 'Report,AdWords,Sessions_Index,Conver_Index,Ecom_Index,Speed,List,Glossary',
'hotel_list': '26,14,323,16,27,19,25,377,101,15,18,105,20,23,112,17,21,24,106,22,399'
}
# Each report dict has a hotel_list key, with a string value.
>>> print(type(my_report['hotel_list']))
<class 'str'>
# This means we can use the .split method to split the string into a list, using commas as delimiters.
>>> hotel_ids = my_report['hotel_list'].split(',')
>>> print(hotel_ids)
['26', '14', '323', '16', '27', '19', '25', '377', '101', '15', '18', '105', '20', '23', '112', '17', '21', '24', '106', '22', '399']
# This is almost ready, but to get the hotel information from the databse, the ID needs to be an integer, not a string.
integer_hotel_ids = []
# You don't need to go through all hotels to find the one you need
# Go to the database website, find the ID of the hotel
# and use the database module to retreive that ID only.
# https://80days.github.io/benchmark-tools/database
from eighty_tools import database
chosen_id = int(14)
chosen_hotel = database.Get("Hotels").get_one_by_id(chosen_id)
print(chosen_hotel['full_name'])
# You can also get a list of hotels by list of ids
chosen_ids = [14, 81, 79]
hotels = database.Get("Hotels").get_more_by_ids(chosen_ids)
for hotel in hotels:
print(hotel['full_name'])