def getBarChartRaceBySentimentAndDate(self):
cube = self.workspace.cube("tweet")
cube.browser = self.browserTweet
cut = [PointCut("concept", [self.concept])]
cell = Cell(cube, cut)
result = self.browserTweet.aggregate(cell, drilldown=["time:day", "sentiment"],
aggregates=["numberOfTweets_sum"])
output = []
for row in result.table_rows("time"):
output.append(row.record)
data = defaultdict(lambda: defaultdict(lambda: defaultdict()))
for row in output:
date = row['time.day'] + "/" + row['time.month'] + "/" + row['time.year']
sentiment = row['sentiment.sentimentLabel']
data[date][sentiment]['numberOfTweets'] = row['numberOfTweets_sum']
dataList = []
element = {'date': '', 'sentimentsList': []}
for date in data:
element['date'] = date
sentimentElement = {'sentiment': '', 'numberOfTweets': 0}
mySentimentsList = []
for sentiment in data[date]:
sentimentElement['sentiment'] = sentiment
sentimentElement['numberOfTweets'] = data[date][sentiment]['numberOfTweets']
mySentimentsList.append(sentimentElement)
sentimentElement = {'sentiment': '', 'numberOfTweets': 0}
element['sentimentsList'] = mySentimentsList
dataList.append(element)
element = {'date': '', 'sentimentsList': []}
return dataList
def compare_kill_distances(browser, cube):
first_place_distances = np.array([])
all_distances = np.array([])
cuts = [PointCut("match_date_dimension", [2017])]
cell = Cell(cube, cuts)
result = browser.aggregate(
drilldown=["killer_dimension", "victim_dimension"])
# print(result.summary["record_count"])
for record in result:
distance = 0
x1 = record["killer_dimension.killer_pos_x"]
x2 = record["victim_dimension.victim_pos_x"]
y1 = record["killer_dimension.killer_pos_y"]
y2 = record["victim_dimension.victim_pos_y"]
if (y1 != None and y2 != None and x1 != None and x2 != None):
distance = get_distance(x1, y1, x2, y2)
if (record["killer_dimension.killer_placement"] == 1):
first_place_distances = np.append(first_place_distances,
distance)
all_distances = np.append(all_distances, distance)
first_place_average_kill_distance = np.mean(first_place_distances)
average_kill_distance = np.mean(all_distances)
print("First place averages a kill distance of: {}".format(
first_place_average_kill_distance))
print("Average kill distance in comparison: {}".format(
average_kill_distance))
return first_place_average_kill_distance - average_kill_distance
def cslice(self, dimension, values):
print("Slicing by %s" % dimension)
cut = PointCut("time", [5])
cell = Cell(self.browser.cube, [cut])
result = self.browser.aggregate(cell, drilldown=["item"])
print("result")
print(result.summary)
print(result.to_dict())
for record in result:
print(record)
print("result cells")
print(result.cell)
print(self.browser.facts(cell))
#for cell in self.browser.facts(cell):
# print(cell)
ratings = [rating for rating in self.browser.facts(cell)]
result = []
for rating in self.browser.facts(cell):
print("Before")
print(rating)
rating["rating"] = float(rating["rating"])
print("After")
print(rating)
result.append(rating)
print(result)
return result
def getPieChartSource(self):
cube = self.workspace.cube("tweet")
cube.browser = self.browserTweet
cut = [PointCut("concept", [self.concept])]
cell = Cell(cube, cut)
result = self.browserTweet.aggregate(cell, drilldown=["location","source"],aggregates=["numberOfTweets_sum"])
output = defaultdict(lambda: defaultdict())
for row in result.table_rows("location"):
continent = row.record['location.continentName']
source = row.record['source.sourceName']
output[continent][source] = row.record['numberOfTweets_sum']
temp = {'continentName': '',
'sources': [{'source': '', 'numberOfTweets': ''}, {'source': '', 'numberOfTweets': ''},
{'source': '', 'numberOfTweets': ''}, {'source': '', 'numberOfTweets': ''}]}
print("output ",output)
i = 0
data = []
continentsList = ['Asia','Africa','Australia','Europe','North America','South America']
for continent in continentsList:
temp['continentName'] = continent
if output[continent]:
temp['sources'][i]['source'] = "iPhone"
temp['sources'][i]['numberOfTweets'] = output[continent].get('iPhone', 0)
i += 1
temp['sources'][i]['source'] = "Android"
temp['sources'][i]['numberOfTweets'] = output[continent].get('Android', 0)
i += 1
temp['sources'][i]['source'] = "Web"
temp['sources'][i]['numberOfTweets'] = output[continent].get('Web', 0)
i += 1
temp['sources'][i]['source'] = "Unknown"
temp['sources'][i]['numberOfTweets'] = output[continent].get('Unknown', 0)
else:
temp['sources'][i]['source'] = "iPhone"
temp['sources'][i]['numberOfTweets'] = 0
i += 1
temp['sources'][i]['source'] = "Android"
temp['sources'][i]['numberOfTweets'] = 0
i += 1
temp['sources'][i]['source'] = "Web"
temp['sources'][i]['numberOfTweets'] = 0
i += 1
temp['sources'][i]['source'] = "Unknown"
temp['sources'][i]['numberOfTweets'] = 0
i = 0
data.append(temp)
temp = {'continentName': '',
'sources': [{'source': '', 'numberOfTweets': ''}, {'source': '', 'numberOfTweets': ''},
{'source': '', 'numberOfTweets': ''}, {'source': '', 'numberOfTweets': ''}]}
return data
def item_context_agg(browser, item):
print("Slicing by item")
cut = PointCut("item", [item])
cell = Cell(browser.cube, [cut])
result = browser.aggregate(cell, drilldown=["time"])
print("result")
print(result.summary)
print(result.to_dict())
for record in result:
print(record)
print("result cells")
for cell in browser.facts(cell):
print(cell)
def cslice(browser):
print("Slicing by time")
cut = PointCut("time", [5])
cell = Cell(browser.cube, [cut])
print("dir cell")
print(dir(cell))
print(cell)
result = browser.aggregate(cell, drilldown=["item"])
print("result")
print(result.summary)
print(result.to_dict())
for record in result:
print(record)
print("result cells")
print(result.cell)
print(browser.facts(cell))
for cell in browser.facts(cell):
print(cell)
def item_context_agg(self, item):
result = []
print("Slicing by item")
cut = PointCut("item", [item])
cell = Cell(self.browser.cube, [cut])
agg = self.browser.aggregate(cell, drilldown=["time"])
print("agg")
print(agg.summary)
print(agg.to_dict())
for record in agg:
print("Record in agg")
print(record)
record["sum"] = float(record["sum"])
record["average"] = float(record["average"])
result.append(record)
print("result cells")
for cell in self.browser.facts(cell):
print(cell)
return result
def getBarChartRaceByLanguageAndDate(self):
cube = self.workspace.cube("tweet")
cube.browser = self.browserTweet
cut = [PointCut("concept", [self.concept])]
cell = Cell(cube, cut)
result = self.browserTweet.aggregate(cell, drilldown=["time:day", "language"],
aggregates=["numberOfTweets_sum"])
output = []
for row in result.table_rows("time"):
output.append(row.record)
data = defaultdict(lambda: defaultdict(lambda: defaultdict()))
languagesList = []
for row in output:
date = row['time.day'] + "/" + row['time.month'] + "/" + row['time.year']
language = row['language.languageName']
languagesList.append(language)
# creating data structure containing all languages
data[date][language]['numberOfTweets'] = row['numberOfTweets_sum']
#GET LIST OF LANGUAGES FROM FILE
import pickle
with open('../Docs/languagesStructure.pickle', 'rb') as file:
languagesList = pickle.load(file)
print(len(languagesList))
element = {'date': '', 'languagesList': []}
dataList = []
for date in data:
element['date'] = date
element['languagesList'] = []
print(len(languagesList))
for language in languagesList:
if language in data[date]:
element['languagesList'].append({'language':language,'numberOfTweets':data[date][language]['numberOfTweets']})
else:
element['languagesList'].append({'language':language,'numberOfTweets':0})
dataList.append(element)
return dataList
# Drill down on service
print('\n\n...drilling down on services...')
result = browser.aggregate(drilldown=["dm_service:service_name"])
for record in result:
print(record)
# print(record["dm_service.crown"], "\t", record['amount_sum'])
# Cut
print('\n\n...Cut services for Crown Service Only...')
dimension = cube.dimension("dm_service")
hierarchy = dimension.hierarchy()
cell = Cell(cube, cuts_from_string(cube, "dm_service:True"))
cut = cell.cut_for_dimension(dimension)
if cut:
path = cut.path
else:
path = []
#
# Do the work, do the aggregation.
#
result = browser.aggregate(cell, drilldown=["dm_datetime"])
for record in result:
print(record)
workspace = Workspace()
workspace.register_default_store("sql", url="sqlite:///restaurant.sqlite")
workspace.import_model("model.json")
# 2. Getting a browser
cube = workspace.cube("restaurant_details")
browser = workspace.browser(cube)
dimension = cube.dimension("location")
# Rolling up to State
print("\n"
"Roll up to state\n"
"================")
cell = Cell(browser.cube)
rollup(cell, "location")
# Drilling down into the cities of each state
print("\n"
"Drill down by state\n"
"===================")
drilldown(cell, "location", 3)
# Slicing by a particular state
print("\n"
"Slice by State\n"
"==============")
cell = cell.slice(PointCut("location", ["CA"]))
from __future__ import print_function
from cubes import Workspace, Cell, PointCut
workspace = Workspace()
workspace.register_default_store("sql", url="sqlite:///data_sqlite/f1.sqlite")
workspace.import_model("models/model.json")
browser = workspace.browser("qualifying")
cut1 = PointCut("drivers", [])
cut2 = PointCut("races", [])
cell = Cell(browser.cube, cuts=[cut1, cut2])
result = browser.aggregate(cell, drilldown=["drivers", "races"])
list_res = [row for row in result]
def filter_racer(data, name, year):
temp = list(
filter(
lambda x: x['drivers.surname'] == name and x['races.year'] == year,
data))
return sorted(temp, key=lambda x: x['position_min'])
for line in filter_racer(list_res, 'Hamilton', 2009):
print(line)
def process(request):
agg = [
{
"name": "occurrence_sum",
"label": "Nombre d'occurrences observées",
"function": "sum",
"measure": "occurrence_count",
},
{
"name": "richness",
"label": "Nombre d'espèces observées",
"measure": "taxon_dimension_id",
"function": "count_distinct",
},
]
dm = get_dimensional_model(
'taxon_observed_occurrences',
agg,
)
workspace = dm.get_cubes_workspace()
cube = workspace.cube('taxon_observed_occurrences')
browser = workspace.browser(cube)
selected_entity = json.loads(
request.POST.get('selected_entity', None)
)
cuts = []
invert_location_cuts = []
invert_env_cuts = []
invert_env = False
if selected_entity['type'] == 'draw':
location_cuts = get_occurrence_location_cuts(selected_entity)
cuts += location_cuts
invert_env_cuts += location_cuts
invert_location_cuts += get_occurrence_location_cuts(
selected_entity,
invert=True
)
area = 0
else:
entity_cut = PointCut(
selected_entity['type'],
[selected_entity['value']]
)
cuts += [entity_cut]
invert_env_cuts += [entity_cut]
invert_location_cuts += [
PointCut(
selected_entity['type'],
[selected_entity['value']],
invert=True
),
]
dim = get_dimension(selected_entity['type'])
area = dim.get_value(selected_entity['value'], ["area"])[0]
# Update cuts with rainfall filter
rainfall_filter = request.POST.get('rainfall_filter', None)
if rainfall_filter is not None and rainfall_filter != '':
cuts += [
PointCut('rainfall', [rainfall_filter])
]
invert_env_cuts += [
PointCut('rainfall', [rainfall_filter], invert=True)
]
invert_env = True
# Update cuts with elevation filter
elevation_filter = request.POST.get('elevation_filter', None)
if elevation_filter is not None and elevation_filter != '':
cuts += [
PointCut('elevation', [elevation_filter])
]
invert_env_cuts += [
PointCut('elevation', [elevation_filter], invert=True)
]
invert_env = True
df = pd.DataFrame(list(browser.facts(cell=Cell(cube, cuts))))
summary = {'occurrence_sum': 0, 'richness': 0}
records = []
taxa_ids = pd.Index([])
if len(df) > 0:
# Init summary with occurrence_sum
summary['occurrence_sum'] = df['occurrence_count'].sum()
# Filter occurrences identified at species level for richness
df_species = df[df['taxon_dimension.species'] != 'NS']
# Init records with occurrence sum
records = pd.DataFrame(
df_species.groupby(
['taxon_dimension.familia', 'taxon_dimension.genus',
'taxon_dimension.species']
)['occurrence_count'].sum(),
columns=['occurrence_count']
).rename(
columns={'occurrence_count': 'occurrence_sum'},
)
records['richness'] = 1
if len(df_species) > 0:
taxa_ids = pd.Index(df_species['taxon_dimension_id'].unique())
# Update summary with richness
summary['richness'] = df_species['taxon_dimension_id'].nunique()
# Records to dict
records = records.reset_index().to_dict(orient='index').values()
# Compute unique taxa in selected location indicator
invert_loc_cell = Cell(cube, invert_location_cuts)
invert_loc_df = pd.DataFrame(list(browser.facts(cell=invert_loc_cell)))
invert_loc_taxa_ids = pd.Index([])
if len(invert_loc_df) > 0:
invert_loc_taxa_ids = pd.Index(
invert_loc_df['taxon_dimension_id'].unique()
)
diff = taxa_ids.difference(invert_loc_taxa_ids)
if invert_env > 0:
invert_env_cell = Cell(cube, invert_env_cuts)
list(browser.facts(cell=invert_env_cell))
invert_env_df = pd.DataFrame(list(browser.facts(cell=invert_env_cell)))
if len(invert_env_df) > 0:
invert_env_taxa_ids = pd.Index(
invert_env_df['taxon_dimension_id'].unique()
)
diff = diff.difference(invert_env_taxa_ids)
summary['unique_taxa_in_entity'] = len(diff)
# Extract table attributes
attributes = [
'taxon_dimension.familia',
'taxon_dimension.genus',
'taxon_dimension.species',
]
attributes_names = []
for i in attributes:
attributes_names.append((i, cube.attribute(i).label))
aggregates_names = [(i.name, i.label) for i in cube.aggregates]
return Response({
'summary': summary,
'records': records,
'columns': attributes_names + aggregates_names,
'area': area,
})
workspace = Workspace()
workspace.register_default_store(
"sql", url="postgresql://*****:*****@localhost/willowood")
workspace.import_model("SalesTable.json")
browser = workspace.browser("salestable")
result = browser.aggregate()
print(result.summary["record_count"])
print(result.summary["Qty"])
print(result.summary["Value"])
cube = browser.cube
# result = browser.aggregate(drilldown=["billing_date"])
#
# for record in result:
# print(' record: ', record)
# TryingOut Yesterday, MTD , YTD, LYTD
cuts = [
PointCut("bh_master_code", [45000002]),
# PointCut("billing_date", ["2018-08-17"]),
]
cell = Cell(cube, cuts)
result1 = browser.aggregate(cell)
print(' Cut Portion: Qty:- ', result1.summary["Qty"])
print(' Cut Portion: Value:- ', result1.summary["Value"])
"==================================================")
#
result = browser.aggregate(drilldown=["item"])
#
print(("%-20s%10s%10s%10s\n"+"-"*50) % ("Category", "Count", "Total", "Double"))
#
for row in result.table_rows("item"):
print("%-20s%10d%10d%10d" % ( row.label,
row.record["record_count"],
row.record["amount_sum"],
row.record["double_amount_sum"])
)
print("\n"
"Slice where Category = Equity\n"
"==================================================")
cut = PointCut("item", ["e"])
cell = Cell(browser.cube, cuts = [cut])
result = browser.aggregate(cell, drilldown=["item"])
print(("%-20s%10s%10s%10s\n"+"-"*50) % ("Sub-category", "Count", "Total", "Double"))
for row in result.table_rows("item"):
print("%-20s%10d%10d%10d" % ( row.label,
row.record["record_count"],
row.record["amount_sum"],
row.record["double_amount_sum"],
))
def report(dim_name=None):
browser = workspace.browser(CUBE_NAME)
cube = browser.cube
if not dim_name:
return render_template('report.html', dimensions=cube.dimensions)
# First we need to get the hierarchy to know the order of levels. Cubes
# supports multiple hierarchies internally.
dimension = cube.dimension(dim_name)
hierarchy = dimension.hierarchy()
# Parse the`cut` request parameter and convert it to a list of
# actual cube cuts. Think of this as of multi-dimensional path, even that
# for this simple example, we are goint to use only one dimension for
# browsing.
cutstr = request.args.get("cut")
cell = Cell(cube, cuts_from_string(cube, cutstr))
# Get the cut of actually browsed dimension, so we know "where we are" -
# the current dimension path
cut = cell.cut_for_dimension(dimension)
if cut:
path = cut.path
else:
path = []
#
# Do the work, do the aggregation.
#
result = browser.aggregate(cell, drilldown=[dim_name])
# If we have no path, then there is no cut for the dimension, # therefore
# there is no corresponding detail.
if path:
details = browser.cell_details(cell, dimension)[0]
else:
details = []
# Find what level we are on and what is going to be the drill-down level
# in the hierarchy
levels = hierarchy.levels_for_path(path)
if levels:
next_level = hierarchy.next_level(levels[-1])
else:
next_level = hierarchy.next_level(None)
# Are we at the very detailed level?
is_last = hierarchy.is_last(next_level)
# Finally, we render it
return render_template('report.html',
dimensions=cube.dimensions,
dimension=dimension,
levels=levels,
next_level=next_level,
result=result,
cell=cell,
is_last=is_last,
details=details)
def report(dim_name=None):
browser = workspace.browser(CUBE_NAME)
cube = browser.cube
if not dim_name:
return render_template('report.html', dimensions=cube.dimensions)
# First we need to get the hierarchy to know the order of levels. Cubes
# supports multiple hierarchies internally.
dimension = cube.dimension(dim_name)
hierarchy = dimension.hierarchy()
# Parse the`cut` request parameter and convert it to a list of
# actual cube cuts. Think of this as of multi-dimensional path, even that
# for this simple example, we are goint to use only one dimension for
# browsing.
cutstr = request.args.get("cut")
cell = Cell(cube, cuts_from_string(cube, cutstr))
# Get the cut of actually browsed dimension, so we know "where we are" -
# the current dimension path
cut = cell.cut_for_dimension(dimension)
if cut:
path = cut.path
else:
path = []
#
# Do the work, do the aggregation.
#
result = browser.aggregate(cell, drilldown=[dim_name])
# If we have no path, then there is no cut for the dimension, # therefore
# there is no corresponding detail.
if path:
details = browser.cell_details(cell, dimension)[0]
else:
details = []
# Find what level we are on and what is going to be the drill-down level
# in the hierarchy
levels = hierarchy.levels_for_path(path)
if levels:
next_level = hierarchy.next_level(levels[-1])
else:
next_level = hierarchy.next_level(None)
# Are we at the very detailed level?
is_last = hierarchy.is_last(next_level)
# Finally, we render it
return render_template('report.html',
dimensions=cube.dimensions,
dimension=dimension,
levels=levels,
next_level=next_level,
result=result,
cell=cell,
is_last=is_last,
details=details)
This function is like recursively traversing directories on a file system
and aggregating the file sizes, for example.
* `cell` - cube cell to drill-down
* `dimension` - dimension to be traversed through all levels
"""
if cell.is_base(dimension):
return
result = browser.aggregate(aggregates=["contract_amount_sum"], cell=cell, drilldown=[dimension])
# for row in cubes.drilldown_rows(cell, result, dimension):
for row in result.table_rows(dimension):
indent = " " * (len(row.path) - 1)
print "%s%s: %d" % (indent, row.label, row.record["contract_amount_sum"])
new_cell = cell.drilldown(dimension, row.key)
drilldown(new_cell, dimension)
# Get whole cube
cell = Cell(cube)
print "Drill down through date hierarchy:"
drilldown(cell, cube.dimension("date"))
# print "Drill down through CPV hierarchy in 2011:"
# cell = cell.slice(cubes.PointCut("date", [2010,5]))
# drilldown(cell, cube.dimension("cpv"))