<SYSTEM_TASK:>
Given the following text description, write Python code to implement the functionality described below step by step
<END_TASK>
<USER_TASK:>
Description:
 DataPath Example 4
This notebook covers somewhat more advanced examples for using DataPaths. It assumes that you understand 
the concepts presented in the previous example notebooks.
You should also read the ERMrest documentation and the derivapy wiki. There are more advanced concepts in this notebook that are demonstrated but not fully (re)explained here, as the concepts are explained in other documentation.
Exampe Data Model
The examples require that you understand a little bit about the example catalog data model, which in this case manages data for biological experiments.
Key tables
'dataset' 
Step1:  Implicit DataPaths
Proceed with caution
For compactness, Table objects (and TableAlias objects) provide DataPath-like methods. E.g., link(...), filter(...), and entities(...), which will implicitly create DataPaths rooted at the table and return the newly created path. These operations return the new DataPath rather than mutating the Table (or TableAlias) objects.
Step2:  DataPath-like methods
The DataPath-like methods on Tables are essentially "wrapper" functions over the implicitly generated DataPath rooted at the Table instance. The wrappers include, link(...), filter(...), entities(...), attributes(...), aggregates(...), and groupby(...).
Attribute Examples
Example
Step3:  It is important to remember that the attributes(...) method returns a result set based on the entity type of the last elmenent of the path. In this example that means the number of results will be determined by the number of unique rows in the replicate table instance in the path created above, as the last link method used the replicate table.
Example
Step4:  If you want to base the results on a different entity, you can introduce a table instance alias into the end of the path, before calling the attributes function.  In this case, even though we are asking for the same attributes, we are getting the set of datasets, not the set of replicates.  Also, since we are including the attributes from dataset in our query, we know that we will not be seeing any duplicate rows.
Step5:  Filtering Examples
Example
Step6:  Example
Step7:  Example
Step8:  Example
Step9:  Example
Step10:  Example
Step11:  Linking Examples
Example
Step12:  IMPORTANT Not all tables are related by foreign key references. ERMrest does not allow arbitrary relational joins. Tables must be related by a foreign key reference in order to link them in a data path.
Step13:  Example
Step14:  Notice that we cannot use the alias right away in the on clause because it was not bound to the path until after the link(...) operation was performed.
Example
Step15:  We can see above that we have a full set of datasets whether or not they have any experiments with biosamples. For further evidence, we can convert to a DataFrame and look at a slice of its entries. Note that the biosample's 'species' and 'stage' attributes do not exist for some results (i.e., NaN) because those attributes did not exist for the join condition.
Step16:  Faceting Examples
You may have noticed that in the examples above, the 'species' and 'experiment_type' attributes are identifiers ('CURIE's to be precise). We may want to construct filters on our datasets based on these categories. This can be used for "faceted search" modes and can be useful even within the context of programmatic access to data in the catalog.
Example
Step17:  First, let's link samples with species and filter on the term "Mus musculus" (i.e., "mouse").
Step18:  Now the context of the path is the species table instance, but we need to link from the biosample to the age stage table.
To do so, we reference the biosample table instance, in this case using its alias S. Then we link off of that table instance which updates the path itself.
Step19:  Now, the path context is the age stage table instance, but we wanted to get the entities for the biosample table. To do so, again we will reference the biosample table instance by the alias S we used. From there, we will call the entities(...) method to get the samples.
Step20:  Grouping Examples
Now support you would like to aggregate all of the vocabulary terms associated with a Dataset. Here, we examine what happens when you have a model such that dataset &lt;- dataset_VOCAB -&gt; VOCAB where VOCAB is a placeholder for a table that includes a vocabulary term set. These tables typically have a name column for the human-readable preferred label to go along with the formal URI or CURIE of the concept class.
<ASSISTANT_TASK:>
Python Code:
# Import deriva modules and pandas DataFrame (for use in examples only)
from deriva.core import ErmrestCatalog, get_credential
from pandas import DataFrame
# Connect with the deriva catalog
protocol = 'https'
hostname = 'www.facebase.org'
catalog_number = 1
credential = None
# If you need to authenticate, use Deriva Auth agent and get the credential
# credential = get_credential(hostname)
catalog = ErmrestCatalog(protocol, hostname, catalog_number, credential)
# Get the path builder interface for this catalog
pb = catalog.getPathBuilder()
# Get some local variable handles to tables for convenience
dataset = pb.isa.dataset
experiment = pb.isa.experiment
biosample = pb.isa.biosample
replicate = pb.isa.replicate
Explanation: DataPath Example 4
This notebook covers somewhat more advanced examples for using DataPaths. It assumes that you understand 
the concepts presented in the previous example notebooks.
You should also read the ERMrest documentation and the derivapy wiki. There are more advanced concepts in this notebook that are demonstrated but not fully (re)explained here, as the concepts are explained in other documentation.
Exampe Data Model
The examples require that you understand a little bit about the example catalog data model, which in this case manages data for biological experiments.
Key tables
'dataset' : represents a unit of data usually for a study or set of experiments;
'biosample' : a biosample (describes biological details of a specimen);
'replicate' : a replicate (describes both bio- and technical-replicates);
'experiment' : a bioassay (any type of experiment or assay; e.g., imaging, RNA-seq, ChIP-seq, etc.).
Relationships
dataset &lt;- biosample: A dataset may have one to many biosamples. I.e., there is a 
  foreign key reference from biosample to dataset.
dataset &lt;- experiment: A dataset may have one to many experiments. I.e., there 
  is a foreign key reference from experiment to dataset.
experiment &lt;- replicate: An experiment may have one to many replicates. I.e., there is a
  foreign key reference from replicate to experiment.
End of explanation
entities = dataset.filter(dataset.released == True).entities()
len(entities)
Explanation: Implicit DataPaths
Proceed with caution
For compactness, Table objects (and TableAlias objects) provide DataPath-like methods. E.g., link(...), filter(...), and entities(...), which will implicitly create DataPaths rooted at the table and return the newly created path. These operations return the new DataPath rather than mutating the Table (or TableAlias) objects.
End of explanation
path = dataset.alias('D').path
path.link(experiment).link(replicate)
results = path.attributes(path.D)
print(len(results))
print(results.uri)
Explanation: DataPath-like methods
The DataPath-like methods on Tables are essentially "wrapper" functions over the implicitly generated DataPath rooted at the Table instance. The wrappers include, link(...), filter(...), entities(...), attributes(...), aggregates(...), and groupby(...).
Attribute Examples
Example: selecting all columns of a table instance
Passing a table (or table instance) object to the attributes(...) method will project all (i.e., *) of its attributes.
End of explanation
results = path.attributes(path.D,
                          path.experiment.experiment_type,
                          path.replicate)
print(len(results))
print(results.uri)
Explanation: It is important to remember that the attributes(...) method returns a result set based on the entity type of the last elmenent of the path. In this example that means the number of results will be determined by the number of unique rows in the replicate table instance in the path created above, as the last link method used the replicate table.
Example: selecting from multiple table instances
More than one table instance may be selected in this manner and it can be mixed and matched with columns from other tables instances.
End of explanation
results = path.D.attributes(path.D,
                            path.experiment.experiment_type,
                            path.replicate)
print(len(results))
print(results.uri)
Explanation: If you want to base the results on a different entity, you can introduce a table instance alias into the end of the path, before calling the attributes function.  In this case, even though we are asking for the same attributes, we are getting the set of datasets, not the set of replicates.  Also, since we are including the attributes from dataset in our query, we know that we will not be seeing any duplicate rows.
End of explanation
path = dataset.link(experiment).filter(experiment.molecule_type == None)
print(path.uri)
print(len(path.entities()))
Explanation: Filtering Examples
Example: filter on null attribute
To test for a null attribute value, do an equality comparison against the None identity.
End of explanation
path = dataset.filter(dataset.description.ciregexp('palate'))
print(path.uri)
print(len(path.entities()))
Explanation: Example: advanced text filters
Deriva supports advanced text filters for regular expressions (regexp), case-instansitive regexp (ciregexp), and text search (ts). You may have to review your text and full-text indexes in your ERMrest catalog before using these features.
End of explanation
path = dataset.filter( ~ (dataset.description.ciregexp('palate')) )
print(path.uri)
print(len(path.entities()))
Explanation: Example: negate a filter
Use the "inverse" ('~') operator to negate a filter. Negation works against simple comparison filters as demonstrated above as well as on logical operators to be discussed next. You must wrap the comparison or logical operators in an extra parens to use the negate operation, e.g., "~ (...)".
End of explanation
path = dataset.link(biosample).filter(
    ((biosample.species == 'NCBITAXON:10090') & (biosample.anatomy == 'UBERON:0002490')))
print(path.uri)
DataFrame(path.entities())
Explanation: Example: filters with logical operators
This example shows how to combine two comparisons with a conjuncting (i.e., and operator). Because Python's logical-and (and) keyword cannot be overloaded, we instead overload the bitwise-and (&amp;) operator. This approach has become customary among many similar data access libraries.
End of explanation
path = dataset.link(biosample).filter(
    ((biosample.species == 'NCBITAXON:10090') & (biosample.anatomy == 'UBERON:0002490')) |
    ((biosample.specimen == 'FACEBASE:1-4GNR') & (biosample.stage == 'FACEBASE:1-4GJA')))
print(path.uri)
DataFrame(path.entities())
Explanation: Example: combine conjunction and disjunctions in filters
Similar to the prior example, the filters allow combining of conjunctive and disjunctive operators. Like the bitwise-and operator, we also overload the bitwise-or (|) operator because the logical-or (or) operatar cannot be overloaded.
End of explanation
path = dataset.filter(dataset.release_date >= '2017-01-01') \
    .link(experiment).filter(experiment.experiment_type == 'OBI:0001271') \
    .link(replicate).filter(replicate.bioreplicate_number == 1)
    
print(path.uri)
DataFrame(path.entities())
Explanation: Example: filtering at different stages of the path
Filtering a path does not have to be done at the end of a path. In fact, the initial intention of the ERMrest URI was to mimick "RESTful" semantics where a RESTful "resource" is identified, then filtered, then a "sub-resource" is identified, and then filtered, and so on.
End of explanation
path = dataset.link(experiment, on=(dataset.RID==experiment.dataset))
print(path.uri)
Explanation: Linking Examples
Example: explicit column links
Up until now, the examples have shown how to link entities via implicit join predicates. That is, we knew there existed a foriegn key reference constraint between foreign keys of one entity and keys of another entity. We needed only to ask ERMrest to link the entities in order to get the linked set.
The problem with implicit links is that it become ambiguous if there are more than one foreign key reference between tables. To support these situations, ERMrest and the DataPath's link(...) method can specify the columns to use for the link condition, explicitly.
The structure of the on clause is:
- an equality comparison operation where
- the left operand is a column of the left table instance which is also the path context before the link method is called, and
- the right operand is a column of the right table instance which is the table to be linked to the path.
End of explanation
DataFrame(path.entities().fetch(limit=3))
Explanation: IMPORTANT Not all tables are related by foreign key references. ERMrest does not allow arbitrary relational joins. Tables must be related by a foreign key reference in order to link them in a data path.
End of explanation
path = dataset.link(biosample.alias('S'), on=(dataset.RID==biosample.dataset))
print(path.uri)
Explanation: Example: explicit column links combined with table aliasing
As usual, table instances are generated automatically unless we provide a table alias.
End of explanation
# Notice in between `link`s that we have to reset the context back to `dataset` so that the
# second join is also left joined from the dataset table instance.
path = dataset.link(experiment.alias('E'), on=dataset.RID==experiment.dataset, join_type='left') \
              .dataset \
              .link(biosample.alias('S'), on=dataset.RID==biosample.dataset, join_type='left') \
# Notice that we have to perform the attribute fetch from the context of the `path.dataset`
# table instance.
results = path.dataset.attributes(path.dataset.RID, 
                                  path.dataset.title, 
                                  path.E.experiment_type, 
                                  path.S.species)
print(results.uri)
len(results)
Explanation: Notice that we cannot use the alias right away in the on clause because it was not bound to the path until after the link(...) operation was performed.
Example: links with "outer join" semantics
Up until now, the examples have shown "links" with inner join semantics. Outer join semantics can be expressed as part of explicit column links, and only when using explicit column links.
The link(...) method accepts a "join_type" parameter, i.e., ".link(... join_type=TYPE)", where TYPE may be 'left', 'right', 'full', and defaults to '' which indicates inner join type.
By 'left' outer joining in the link from 'dataset' to 'experiment' and to 'biosample', and then reseting the context of the path to 'dataset', the following path gives us a reference to 'dataset' entities that whether or not they have any experiments or biosamples.
End of explanation
DataFrame(results)[:10]
Explanation: We can see above that we have a full set of datasets whether or not they have any experiments with biosamples. For further evidence, we can convert to a DataFrame and look at a slice of its entries. Note that the biosample's 'species' and 'stage' attributes do not exist for some results (i.e., NaN) because those attributes did not exist for the join condition.
End of explanation
species = pb.vocab.species
stage = pb.vocab.stage
Explanation: Faceting Examples
You may have noticed that in the examples above, the 'species' and 'experiment_type' attributes are identifiers ('CURIE's to be precise). We may want to construct filters on our datasets based on these categories. This can be used for "faceted search" modes and can be useful even within the context of programmatic access to data in the catalog.
Example: faceting on "related" tables
Let's say we want to find all of the biosamples in our catalog where their species are 'Mus musculus' and their age stage are 'E10.5'.
We need to extend our understanding of the data model with the following tables that are related to 'biosample'.
- isa.biosample.species -&gt; vocab.species: the biosample table has a foreign key reference to the 'species' table.
- isa.biosample.stage -&gt; vocab.stage: the biosample table has a foreign key reference to the 'stage' table.
We may say that species and stage are related to the biosample table in the sense that biosample has a direct foreign key relationship from it to them.
For convenience, we will get local variables for the species and stage tables.
End of explanation
# Here we have to use the container `columns_definitions` because `name` is reserved
path = biosample.alias('S').link(species).filter(species.column_definitions['name'] == 'Mus musculus')
print(path.uri)
Explanation: First, let's link samples with species and filter on the term "Mus musculus" (i.e., "mouse").
End of explanation
path.S.link(stage).filter(stage.column_definitions['name'] == 'E10.5')
print(path.uri)
Explanation: Now the context of the path is the species table instance, but we need to link from the biosample to the age stage table.
To do so, we reference the biosample table instance, in this case using its alias S. Then we link off of that table instance which updates the path itself.
End of explanation
results = path.S.attributes(path.S.RID,
                            path.S.collection_date,
                            path.species.column_definitions['name'].alias('species'),
                            path.species.column_definitions['uri'].alias('species_uri'),
                            path.stage.column_definitions['name'].alias('stage'),
                            path.stage.column_definitions['uri'].alias('stage_uri'))
print(results.uri)
DataFrame(results)
Explanation: Now, the path context is the age stage table instance, but we wanted to get the entities for the biosample table. To do so, again we will reference the biosample table instance by the alias S we used. From there, we will call the entities(...) method to get the samples.
End of explanation
# We need to import the `ArrayD` aggregate function for this example.
from deriva.core.datapath import ArrayD
# For convenience, get python objects for the additional tables.
dataset_organism = pb.isa.dataset_organism
dataset_experiment_type = pb.isa.dataset_experiment_type
species = pb.vocab.species
experiment_type = pb.vocab.experiment_type
# Start by doing a couple left outer joins on the dataset-term association tables, then link
# (i.e., inner join) the associated vocabulary term table, then reset the context back to the
# dataset table.
path = dataset.link(dataset_organism, on=dataset.id==dataset_organism.dataset_id, join_type='left') \
              .link(species) \
              .dataset \
              .link(dataset_experiment_type, on=dataset.id==dataset_experiment_type.dataset_id, join_type='left') \
              .link(experiment_type)
# Again, notice that we reset the context to the `dataset` table alias so that we will retrieve 
# dataset entities based on the groupings to be defined next. For the groupby key we will use the
# dataset.RID, but for this example any primary key would work. Then we will get aggregate arrays
# of the linked vocabulary tables.
results = path.dataset.groupby(dataset.RID).attributes(
    dataset.title,
    ArrayD(path.species.column_definitions['name']).alias('species'),
    ArrayD(path.experiment_type.column_definitions['name']).alias('experiment_type')
)
#results = path.dataset.entities()
print(results.uri)
print(len(results))
DataFrame(results.fetch(limit=20))
Explanation: Grouping Examples
Now support you would like to aggregate all of the vocabulary terms associated with a Dataset. Here, we examine what happens when you have a model such that dataset &lt;- dataset_VOCAB -&gt; VOCAB where VOCAB is a placeholder for a table that includes a vocabulary term set. These tables typically have a name column for the human-readable preferred label to go along with the formal URI or CURIE of the concept class.
End of explanation
<END_TASK>