def action_fact_table_group(df, entities):
if entities['logic']:
return fact_table_logic(df, entities)
if not entities['graph']:
entities['graph'] = ['table']
df = helpers.apply_date_condition(df, entities['date_condition'])
df = helpers.apply_dim_filters(df, entities['dim_filters'])
df = helpers.apply_fact_condition(df, entities['dim'] + entities['adject'],
entities['fact_condition'])
facts = df.columns.tolist()
df = df.reset_index().fillna(0)
if 'index' in df.columns:
df = df.drop('index', axis=1)
data = []
for graph in entities['graph']:
if not entities['select_upto']:
if len(df) == 1 and len(df.columns) == 1 and graph == 'text':
df = "{0}{1}{2} is {3}".format(
df.columns[0],
helpers.get_date_text(entities['date_condition']),
helpers.get_dim_filter_text(entities['dim_filters']),
helpers.format_value_to_language(df.iloc[0, 0]))
table_data = {
'chart': graph,
'chart_title': 'message',
'data': df
}
data.append(table_data)
else:
table_data = {
'chart': graph,
'facts': facts,
'dimensions': entities['dim'] + entities['adject'],
'table': df
}
data.append(table_data)
else:
# Apply Selection
final_df = pd.DataFrame()
for selection in entities['select_upto']:
table_data = helpers.dynamic_selection_upto(
df, entities['dim'] + entities['adject'], facts,
selection['selection'], selection['upto'])
final_df = final_df.append(table_data)
table_data = {
'chart': graph,
'facts': facts,
'dimensions': entities['dim'] + entities['adject'],
'table': final_df
}
data.append(table_data)
return data
def target_achievement(self, df):
fact_condition = self.fact_condition.copy()
if self.dim_filters:
df = helpers.apply_dim_filters(df, dim_filters=self.dim_filters)
if self.date_condition:
df = helpers.apply_date_condition(df, self.date_condition)
# add Sales in Aggregation
if 'SalesAmount' not in fact_condition['aggregation']:
fact_condition['aggregation']['SalesAmount'] = ['sum']
fact_condition['conditions'] += [{
'fact_name': 'SalesAmount sum',
'conditions': np.nan,
'fact_value': np.nan
}]
# add Target Amount in Aggregation
if 'TargetAmount' not in fact_condition['aggregation']:
fact_condition['aggregation']['TargetAmount'] = fact_condition[
'aggregation']['SalesAmount']
fact_condition['conditions'] += [{
'fact_name': 'TargetAmount sum',
'conditions': np.nan,
'fact_value': np.nan
}]
# add sum to sales Amount
if 'sum' not in fact_condition['aggregation']['SalesAmount']:
fact_condition['aggregation']['SalesAmount'] += ['sum']
fact_condition['conditions'] += [{
'fact_name': 'SalesAmount sum',
'conditions': np.nan,
'fact_value': np.nan
}]
# Apply fact condition
df = helpers.apply_fact_condition(df, self.dimensions, fact_condition)
sale_fact_names = [
c['fact_name'] for c in fact_condition['conditions']
if c['fact_name'][0:5] == 'Sales'
]
target_fact_names = [
c['fact_name'] for c in fact_condition['conditions']
if c['fact_name'][0:6] == 'Target'
]
# target facts for 0th name
target_facts = pd.DataFrame(100 * df[sale_fact_names[0]] /
df[target_fact_names[0]])
target_facts.columns = ["% Target Achievement"]
# return pd.concat([df, target_facts], axis=1, sort=False)
return target_facts
def action_fact_kpi(df, entities):
if entities['dim']:
return action_fact_table_group(df, entities)
entities['graph'] = 'text'
df = helpers.apply_date_condition(df, entities['date_condition'])
df = helpers.apply_dim_filters(df, entities['dim_filters'])
date_text = helpers.get_date_text(entities['date_condition'])
dim_filters_text = helpers.get_dim_filter_text(entities['dim_filters'])
data = []
for k, v in entities['fact_condition']['aggregation'].items():
for agg in v:
val = df[k].agg(agg)
val = helpers.format_value_to_language(val)
text = "The {0}{1}{2}{3} is {4}.".format(agg + ' of ', k,
date_text,
dim_filters_text, val)
data.append({
'chart': entities['graph'],
'chart_title': 'Message',
'data': text
})
return data
def contribution(self, df):
# Raise Error if there is no filters
if not self.dim_filters and not self.date_condition and not self.dimensions:
raise NoLeftOperandInPercentage(
"Could not apply percentage without a filter! Either apply filter in dimension or date"
)
all_filtered = df.copy()
less_filtered = df.copy()
contribution = pd.DataFrame(
{'Error': ["Could not applied percentage"]})
if (self.dim_filters or self.dimensions) and self.date_condition:
# Do not filter lowest level dimension
all_filtered = helpers.apply_dim_filters(
all_filtered, dim_filters=self.dim_filters)
all_filtered = helpers.apply_date_condition(
all_filtered, self.date_condition)
less_filtered = helpers.apply_date_condition(
less_filtered, self.date_condition)
all_filtered = helpers.apply_fact_condition(
all_filtered, self.dimensions, self.fact_condition)
less_filtered = helpers.apply_fact_condition(
less_filtered, self.dimensions[1:], self.fact_condition)
contribution = 100 * all_filtered / less_filtered.iloc[0]
contribution = contribution.add_prefix("% ")
elif len(self.dim_filters.keys()) == 1 or self.dimensions:
# Do not filter lowest level dimension
less_dim_filters = {
k: v
for k, v in self.dim_filters.items()
if k != list(self.dim_filters.keys())[0]
}
if not less_dim_filters:
less_dim_filters = self.dim_filters
all_filtered = helpers.apply_dim_filters(
all_filtered, dim_filters=self.dim_filters)
less_filtered = helpers.apply_dim_filters(
less_filtered, dim_filters=less_dim_filters)
all_filtered = helpers.apply_fact_condition(
all_filtered, self.dimensions, self.fact_condition)
less_filtered = helpers.apply_fact_condition(
less_filtered, self.dimensions[1:], self.fact_condition)
contribution = 100 * all_filtered / less_filtered.iloc[0]
contribution = contribution.add_prefix("% ")
elif len(self.dim_filters.keys()) > 1:
# Do not filter lowest level dimension
less_dim_filters = {
k: v
for k, v in self.dim_filters.items()
if k != list(self.dim_filters.keys())[0]
}
all_filtered = helpers.apply_dim_filters(
all_filtered, dim_filters=self.dim_filters)
less_filtered = helpers.apply_dim_filters(
less_filtered, dim_filters=less_dim_filters)
all_filtered = helpers.apply_fact_condition(
all_filtered, self.dimensions, self.fact_condition)
less_filtered = helpers.apply_fact_condition(
less_filtered, self.dimensions[1:], self.fact_condition)
contribution = 100 * all_filtered / less_filtered
contribution = contribution.add_prefix("% ")
elif self.date_condition:
# Do not filter first level date
less_date_filters = self.date_condition[1:]
more_date_filters = self.date_condition[0:1]
if not len(self.date_condition) % 2:
less_date_filters = self.date_condition[2:]
more_date_filters = self.date_condition[0:2]
all_filtered = helpers.apply_date_condition(
all_filtered, more_date_filters)
less_filtered = helpers.apply_date_condition(
less_filtered, less_date_filters)
all_filtered = helpers.apply_fact_condition(
all_filtered, self.dimensions, self.fact_condition)
less_filtered = helpers.apply_fact_condition(
less_filtered, self.dimensions[1:], self.fact_condition)
contribution = 100 * all_filtered / less_filtered
contribution = contribution.add_prefix("% ")
return contribution
def action_po_header(df, entities, *args, **kwargs):
if not entities['graph']:
entities['graph'] = ['table']
if not entities['fact_condition']:
entities['fact_condition'] = {
'aggregation': {
'SubTotal': ['sum']
},
'conditions': [{
'fact_name': 'SubTotal sum',
'conditions': np.nan,
'fact_value': np.nan
}]
}
if {
'ProductName', 'ProductNumber', 'ProductSubcategoryName',
'ProductCategoryName'
}.intersection(entities['dim'] + entities['adject'] +
list(entities['dim_filters'].keys())):
entities['fact_condition'] = {
'aggregation': {
'LineTotal': ['sum']
},
'conditions': [{
'fact_name': 'LineTotal sum',
'conditions': np.nan,
'fact_value': np.nan
}]
}
df = helpers.apply_date_condition(df, entities['date_condition'])
df = helpers.apply_dim_filters(df, entities['dim_filters'])
df = helpers.apply_fact_condition(df, entities['dim'] + entities['adject'],
entities['fact_condition'])
facts = df.columns.tolist()
df = df.reset_index().fillna(0)
if 'index' in df.columns:
df = df.drop('index', axis=1)
data = []
for graph in entities['graph']:
if not entities['select_upto']:
if len(df) == 1 and len(df.columns) == 1 and graph == 'text':
df = "{0}{1}{2} is {3}".format(
df.columns[0],
helpers.get_date_text(entities['date_condition']),
helpers.get_dim_filter_text(entities['dim_filters']),
helpers.format_value_to_language(df.iloc[0, 0]))
table_data = {
'chart': graph,
'chart_title': 'message',
'data': df
}
data.append(table_data)
else:
table_data = {
'chart': graph,
'facts': facts,
'dimensions': entities['dim'] + entities['adject'],
'table': df
}
data.append(table_data)
else:
# Apply Selection
final_df = pd.DataFrame()
for selection in entities['select_upto']:
table_data = helpers.dynamic_selection_upto(
df, entities['dim'] + entities['adject'], facts,
selection['selection'], selection['upto'])
final_df = final_df.append(table_data)
table_data = {
'chart': graph,
'facts': facts,
'dimensions': entities['dim'] + entities['adject'],
'table': final_df
}
data.append(table_data)
return data, 1
def action_product_description(df,
entities,
page_num=0,
length=100,
*args,
**kwargs):
df = helpers.apply_date_condition(df, entities['date_condition'])
df = helpers.apply_dim_filters(df, entities['dim_filters'])
for col in df:
dt = df[col].dtype
if dt == int or dt == float:
df[col].fillna(0, inplace=True)
else:
df[col].fillna("", inplace=True)
product_group_columns = [
'ProductID', 'ProductName', 'ProductNumber', 'ProductSubcategoryName',
'ProductCategoryName'
]
product_agg = {
'OrderDate': 'max',
'OrderQty': 'sum',
'LineTotal': 'sum',
'ReceivedQty': 'sum',
'RejectedQty': 'sum'
}
product_df = df.groupby(product_group_columns).agg(
product_agg).reset_index()
total_pages = len(product_df) // length
product_df = product_df.sort_values(
'ProductID',
ascending=True).iloc[page_num * length:(page_num + 1) * length]
product_df['LastPurchasedOn'] = product_df['OrderDate']
product_df.drop('OrderDate', axis=1, inplace=True)
product_df['ReturnRate'] = (product_df['RejectedQty'] /
product_df['OrderQty']).round(2)
product_df['PendingOrder'] = product_df.apply(lambda x: df[
(df['Status'] == 'Pending') &
(df['ProductID'] == x['ProductID'])]['OrderQty'].sum(),
axis=1)
product_df['UnitPrice'] = product_df.apply(lambda x: df[
(df['ProductID'] == x['ProductID']) &
(df['OrderDate'] == x['LastPurchasedOn'])]['UnitPrice'].mean(),
axis=1)
product_df = product_df.round(2)
data = []
for index, row in product_df.iterrows():
row_product_details_df = df[df['ProductID'] == row['ProductID']].round(
2)
row_product_details_df = row_product_details_df.groupby('Month').agg({
'LineTotal':
'sum'
}).reset_index()
full_month_names = {k: v['FullMonthName'] for k, v in MONTH_NAMES.items()}
row_product_details_df = row_product_details_df.replace({
'Month':
full_month_names
}).round(2)
for col in row_product_details_df:
dt = row_product_details_df[col].dtype
if dt == int or dt == float:
row_product_details_df[col].fillna(0, inplace=True)
else:
row_product_details_df[col].fillna("", inplace=True)
row_product_data = [row_product_details_df.columns.to_list()
] + row_product_details_df.to_numpy().tolist()
row_data = {
'ProductData': {
'type': 'values',
'value': row.to_dict()
},
'ProductTrend': {
'type': 'line',
'value': row_product_data
}
}
data.append(row_data)
if not data:
return [{
'chart': 'text',
'chart_title': 'Message',
'data': 'No data found!'
}], 1
data = [{'chart': 'customProductChart', 'data': data}]
return data, total_pages
def action_po_header_details(df,
entities,
page_num=0,
length=100,
*args,
**kwargs):
df = helpers.apply_date_condition(df, entities['date_condition'])
df = helpers.apply_dim_filters(df, entities['dim_filters'])
total_pages = len(df) // length
df = df.sort_values('PurchaseOrderID',
ascending=False).iloc[page_num *
length:(page_num + 1) * length]
for col in df:
dt = df[col].dtype
if dt == int or dt == float:
df[col].fillna(0, inplace=True)
else:
df[col].fillna("", inplace=True)
order_group_columns = [
'PurchaseOrderID', 'EmployeeName', 'JobTitle', 'DepartmentName',
'VendorAccountNumber', 'VendorName', 'VendorCreditRating', 'Status',
'ShipMethodName', 'OrderDate', 'ShipDate'
]
order_agg = {
'SubTotal': 'mean',
'TaxAmt': 'mean',
'Freight': 'mean',
'TotalDue': 'mean'
}
order_df = df.groupby(order_group_columns).agg(order_agg)
order_df = order_df.reset_index()
order_details_group_columns = [
'PurchaseOrderID', 'ProductName', 'ProductNumber',
'ProductSubcategoryName', 'ProductCategoryName'
]
order_details_agg = [
'OrderQty', 'UnitPrice', 'LineTotal', 'ReceivedQty', 'RejectedQty'
]
order_details_df = df[order_details_group_columns + order_details_agg]
data = []
for index, row in order_df.iterrows():
row_order_details_df = order_details_df[
order_details_df['PurchaseOrderID'] ==
row['PurchaseOrderID']].round(2)
row_order_data = [row_order_details_df.columns.to_list()
] + row_order_details_df.to_numpy().tolist()
row_data = {
'PurchaseOrderHeader': {
'type': 'values',
'value': row.to_dict()
},
'PurchaseOrderDetails': {
'type': 'table',
'value': row_order_data
}
}
data.append(row_data)
if not data:
return [{
'chart': 'text',
'chart_title': 'Message',
'data': 'No data found!'
}], 1
data = [{'chart': 'customPOChart', 'data': data}]
return data, total_pages