>>> budget_dataframe = pd.DataFrame(
... {
... "Position": [
... "Sales manager 1",
... "Sales person 1",
... "Sales person 2",
... "Sales manager 2",
... "Sales person 3",
... ],
... "Budget": [20000, 15000, 10000, 40000, 12000],
... }
... )
>>> organization_dataframe = pd.DataFrame(
... {
... "Supervisors 1": [
... "Sales manager 1",
... "Sales manager 2",
... ],
... "Supervisors 2": ["Sales person 1", "Sales person 3"],
... "Supervisors 3": ["Sales person 2", ""],
... }
... )
>>> budget_table = session.read_pandas(
... budget_dataframe, keys={"Position"}, table_name="Budget"
... )
>>> organization_table = session.read_pandas(
... organization_dataframe,
... keys={"Supervisors 1"},
... table_name="Company organization",
... )
>>> cube = session.create_cube(organization_table, mode="manual")
>>> h, l, m = cube.hierarchies, cube.levels, cube.measures
>>> h["Organization level"] = [
... organization_table["Supervisors 1"],
... organization_table["Supervisors 2"],
... organization_table["Supervisors 3"],
... ]
>>> m["Position"] = tt.where(
... (~l["Supervisors 1"].isnull()) & (l["Supervisors 2"].isnull()),
... l["Supervisors 1"],
... tt.where(
... (~l["Supervisors 2"].isnull()) & (l["Supervisors 3"].isnull()),
... l["Supervisors 2"],
... l["Supervisors 3"],
... ),
... )
>>> cube.query(m["Position"], levels=[l["Supervisors 3"]], include_totals=True)
Position
Supervisors 1 Supervisors 2 Supervisors 3
Sales manager 1 Sales manager 1
Sales person 1 Sales person 1
Sales person 2 Sales person 2
Sales manager 2 Sales manager 2
Sales person 3 Sales person 3
N/A N/A
>>> m["Position budget"] = tt.lookup(
... budget_table["Budget"], budget_table["Position"] == m["Position"]
... )
>>> cube.query(
... m["Position budget"], levels=[l["Supervisors 3"]], include_totals=True
... )
Position budget
Supervisors 1 Supervisors 2 Supervisors 3
Sales manager 1 20,000
Sales person 1 15,000
Sales person 2 10,000
Sales manager 2 40,000
Sales person 3 12,000
