<SYSTEM_TASK:>
Given the following text description, write Python code to implement the functionality described below step by step
<END_TASK>
<USER_TASK:>
Description:
 Exploring and cleaning the Union of Concerned Scientists database of Earth Satellites
The Union of Concerned Scientists maintains a database of ~1000 Earth satellites
Step5:  Querying the data using SQL
Before querying the implications of a population, it can be useful to look at a sample of the raw data and metadata. This can be done using a combination of ordinary SQL and convenience functions built into bayeslite. We start by finding one of the most well-known satellites, the International Space Station
Step7:  bayeslite includes statistical graphics procedures designed for easy use with data extracted from an SQL database. Consider the problem of visualizing a table with two columns
Step10:  Querying the implications using BQL — What if? questions
Consider the following "what if?" question about satellites
Step12:  Note that everything after the AS is a perfectly valid query. CREATE TEMP TABLE satellite_purpose AS saves the result of the query that follows it into a table called satellite_purpose which we can refer to later. Temporary
tables are destroyed when the session is closed.
The %g specifies the set of generative population models created for this population during the analysis step that we performed for you on this population, and stored in the bdb file that we downloaded in the first step of the notebook. Creating those models is beyond the scope of this segment of the tutorial. The important thing to note is that one has to use %g (or explicitly the name of the set of models, in this case satellites_cc as we can see in the BQL echoed above) whenever we are trying to SIMULATE or ESTIMATE from those models.
To inspect the results, we first create a derived Country-Purpose variable, aggregate over it, and sort the results, all using SQL, and visualize it using bdbcontrib.barplot
Step14:  What if you approached this question by querying the data and not its implications? One approach would be to find existing satellites that are in GEO and have a dry_mass_kg that is close to 500
Step16:  Without understanding the joint distribution of dry_mass_kg, it is difficult to know how wide a net to cast. Broadening the range another 100kg still yields an idiosyncratic list.
Step18:  In general, as the constraints for the hypothetical get narrower, the results of a SELECT based approach grow more unstable.
Querying the implications using BQL — Exploring predictive relationships
For data sources that can be arranged into statistical populations, a key exploratory question is
Which variables probably predict one another?
It is closely related to the key confirmatory analysis question of
How much evidence is there for a predictive relationship between two variables?
BayesDB makes it easy to ask and answer both of these questions by using simple BQL queries executed against baseline models built using Meta-modeling Language (MML).
To quantify the evidence for (or against) a predictive relationship between two pairs of variables, BQL relies on information theory. The notion of dependence between two variables A and B is taken to be mutual information; the amount of evidence for dependence is then the probability that the mutual information between A and B is nonzero. This can be defined in terms of a weighted collection of population models {(G_i, m_i)} as follows
Step20:  This heatmap shows several groups of variables with high probability of mutual interdependence. For example, we see a definite block of geopolitically related variables, such as the country of contractor & operator, the contractor's identity, and the location of the satellite (if it is in geosynchronous orbit). The kinematic variables describing orbits, such as perigee, apogee, period, and orbit class, are also shown as strongly interdependent. A domain expert with sufficiently confident domain knowledge can thus use this overview of the predictive relationships to critically assess the value of the data and the efficacy of MML.
It is also instructive to compare the heatmap of pairwise dependence probabilities with standard alternatives from statistics, such as datatype-appropriate measures of correlation
Step22:  The results from correlation are sufficiently noisy that it would be difficult to trust inferences from techniques that use correlation to select variables. Furthermore, the most causally unambiguous relationships, such as the geometric constraint relating perigee and apogee, are not detected by correlation.
Querying the implications using BQL — Inferring missing values
A key step in data cleaning is choosing a method for handling missing values. BQL provides the INFER primitive to make it straightforward to obtain point estimates and confidence scores for arbitrary cells in the database.
Consider the variable type_of_orbit. It is easy to see that many values are missing
Step25:  The following query produces a table with several variables, along with both a predicted value for type_of_orbit and the confidence associated with that value
Step28:  We can visualize the result both in tabular and graphical form.
Step32:  The plot on the bottom left shows that the confidence depends on the orbit class and on the predicted value for the inferred orbit type. For example, there is typically moderate to high confidence for the orbit type of LEO satellites — and high confidence (but some variability in confidence) for those with Sun-Synchronous orbits. Satellites with Elliptical orbits may be assigned a Sun-Synchronous type with moderate confidence, but for other target labels confidence is generally lower.
Note that many standard techniques for imputation from statistics correspond to INFER ... WITH CONFIDENCE 0, as they have no natural notion of confidence.
Query the implications using BQL — Identify anomalies
Another core data cleaning operation is identifying erroneous values — including (univariate) outliers and other kinds of anomalies. BQL makes it straightforward to assess the predictive probability density of each cell in the data.
Recall earlier that we mentioned that some of the relations are governed by
the laws of physics and are thus nearly deterministic? We can use this
determinism coupled with our notion of anomalousness to search the table for
data-entry errors. A geosynchronous orbit should take 24 hours
(1440 minutes). Let us display the anomalous values for satellites in
geosynchronous orbit.
<ASSISTANT_TASK:>
Python Code:
import os
import subprocess
if not os.path.exists('satellites.bdb'):
    subprocess.check_call(['curl', '-O', 'http://probcomp.csail.mit.edu/bayesdb/downloads/satellites.bdb'])
# Load the bayeslite client library
import bayeslite
import bdbcontrib
from bdbcontrib import Population
# Load the satellites snapshot into a local instance of bayeslite
satellites = Population(name='satellites', bdb_path='satellites.bdb')
Explanation: Exploring and cleaning the Union of Concerned Scientists database of Earth Satellites
The Union of Concerned Scientists maintains a database of ~1000 Earth satellites:
http://www.ucsusa.org/nuclear-weapons/space-weapons/satellite-database.html
For the majority of satellites, it includes kinematic, material, electrical, political, functional, and economic characteristics, such as dry mass, launch date, orbit type, country of operator, and purpose. The data appears to have been mirrored on other satellite search websites, e.g. http://satellites.findthedata.com/ .
This iPython notebook describes a sequence of interactions with a snapshot of this database using the bayeslite implementation of BayesDB, using the Python bayeslite client library. The snapshot includes a population of satellites defined using the UCS data as well as a constellation of generative probabilistic models for this population.
We have already analyzed this population of satellite data. We first download the results of that analysis:
End of explanation
satellites.q(
SELECT * FROM satellites 
WHERE Name LIKE 'International Space Station%'
).transpose()
satellites.q(SELECT COUNT(*) FROM satellites;)
satellites.q(SELECT * FROM satellites WHERE Name LIKE '%GPS%').transpose()
satellites.q(
SELECT name, dry_mass_kg, period_minutes, class_of_orbit FROM satellites 
ORDER BY period_minutes LIMIT 10;
)
Explanation: Querying the data using SQL
Before querying the implications of a population, it can be useful to look at a sample of the raw data and metadata. This can be done using a combination of ordinary SQL and convenience functions built into bayeslite. We start by finding one of the most well-known satellites, the International Space Station:
End of explanation
import matplotlib.pyplot as plt
%matplotlib inline
satellites.histogram(SELECT dry_mass_kg, class_of_orbit FROM satellites, 
                     nbins=35, normed=True);
Explanation: bayeslite includes statistical graphics procedures designed for easy use with data extracted from an SQL database. Consider the problem of visualizing a table with two columns: dry_mass_kg, a NUMERICAL column, and class_of_orbit, a CATEGORICAL column. The bdbcontrib.histogram procedure renders this data by producing overlaid histograms, one per distinct value of class_of_orbit.
End of explanation
satellites.q(DROP TABLE IF EXISTS "satellite_purpose";);
satellites.q(
CREATE TEMP TABLE IF NOT EXISTS satellite_purpose AS
    SIMULATE country_of_operator, purpose FROM %g
    GIVEN Class_of_orbit = 'GEO', Dry_mass_kg = 500 
    LIMIT 1000;
);
Explanation: Querying the implications using BQL — What if? questions
Consider the following "what if?" question about satellites:
Suppose there is a satellite in geosynchronous orbit that we know has a dry mass of 500 kilograms. What is its probable purpose? and what countries might be operating it?
In some applications, these "what if?" scenarios may be of intrinsic interest. They also provide a way for domain experts to scrutinize the models that come with this snapshot, by qualitatively checking their implications via simulated examples.
It is straightforward to pose this question using BQL:
End of explanation
satellites.barplot(
SELECT country_of_operator || "--" || purpose AS "Country-Purpose", 
    COUNT("Country-Purpose") AS frequency
FROM satellite_purpose
    GROUP BY "Country-Purpose"
    ORDER BY frequency DESC
LIMIT 20;
);
Explanation: Note that everything after the AS is a perfectly valid query. CREATE TEMP TABLE satellite_purpose AS saves the result of the query that follows it into a table called satellite_purpose which we can refer to later. Temporary
tables are destroyed when the session is closed.
The %g specifies the set of generative population models created for this population during the analysis step that we performed for you on this population, and stored in the bdb file that we downloaded in the first step of the notebook. Creating those models is beyond the scope of this segment of the tutorial. The important thing to note is that one has to use %g (or explicitly the name of the set of models, in this case satellites_cc as we can see in the BQL echoed above) whenever we are trying to SIMULATE or ESTIMATE from those models.
To inspect the results, we first create a derived Country-Purpose variable, aggregate over it, and sort the results, all using SQL, and visualize it using bdbcontrib.barplot:
End of explanation
satellites.q(
SELECT country_of_operator, purpose, Class_of_orbit, Dry_mass_kg
    FROM satellites
        WHERE Class_of_orbit = 'GEO'
            AND Dry_Mass_kg BETWEEN 400 AND 600)
Explanation: What if you approached this question by querying the data and not its implications? One approach would be to find existing satellites that are in GEO and have a dry_mass_kg that is close to 500:
End of explanation
satellites.q(
SELECT country_of_operator, purpose, Class_of_orbit, Dry_mass_kg
    FROM satellites
        WHERE Class_of_orbit = 'GEO'
            AND Dry_Mass_kg BETWEEN 300 AND 700)
Explanation: Without understanding the joint distribution of dry_mass_kg, it is difficult to know how wide a net to cast. Broadening the range another 100kg still yields an idiosyncratic list.
End of explanation
satellites.heatmap(ESTIMATE DEPENDENCE PROBABILITY FROM PAIRWISE COLUMNS OF %g;);
Explanation: In general, as the constraints for the hypothetical get narrower, the results of a SELECT based approach grow more unstable.
Querying the implications using BQL — Exploring predictive relationships
For data sources that can be arranged into statistical populations, a key exploratory question is
Which variables probably predict one another?
It is closely related to the key confirmatory analysis question of
How much evidence is there for a predictive relationship between two variables?
BayesDB makes it easy to ask and answer both of these questions by using simple BQL queries executed against baseline models built using Meta-modeling Language (MML).
To quantify the evidence for (or against) a predictive relationship between two pairs of variables, BQL relies on information theory. The notion of dependence between two variables A and B is taken to be mutual information; the amount of evidence for dependence is then the probability that the mutual information between A and B is nonzero. This can be defined in terms of a weighted collection of population models {(G_i, m_i)} as follows: 
Pr[ A dep B ] = Pr[ I( A ; B ) > 0 ] = \sum_i G_i w_i Pr[ I( A ; B ) > 0 | G_i ]
If the population models are obtained by posterior inference in a meta-model — as is the case with MML — then this probability approximates the posterior probability (or strength of evidence) that the mutual information is nonzero.
The Python client for bayeslite makes it straightforward to examine the overall matrix of pairwise dependence probabilities. Cell (i,j) in this matrix records Pr[ variable i is dependent on variable j ]. The matrix is reordered using a clustering algorithm to make higher-order predictive relationships — cases where some group of variables are probably all mutually independent — more visually apparent.
End of explanation
# WARNING: This may take a couple minutes.
satellites.heatmap(ESTIMATE CORRELATION FROM PAIRWISE COLUMNS OF %g;);
Explanation: This heatmap shows several groups of variables with high probability of mutual interdependence. For example, we see a definite block of geopolitically related variables, such as the country of contractor & operator, the contractor's identity, and the location of the satellite (if it is in geosynchronous orbit). The kinematic variables describing orbits, such as perigee, apogee, period, and orbit class, are also shown as strongly interdependent. A domain expert with sufficiently confident domain knowledge can thus use this overview of the predictive relationships to critically assess the value of the data and the efficacy of MML.
It is also instructive to compare the heatmap of pairwise dependence probabilities with standard alternatives from statistics, such as datatype-appropriate measures of correlation:
End of explanation
satellites.q(SELECT COUNT(*) FROM satellites WHERE type_of_orbit IS NULL;)
Explanation: The results from correlation are sufficiently noisy that it would be difficult to trust inferences from techniques that use correlation to select variables. Furthermore, the most causally unambiguous relationships, such as the geometric constraint relating perigee and apogee, are not detected by correlation.
Querying the implications using BQL — Inferring missing values
A key step in data cleaning is choosing a method for handling missing values. BQL provides the INFER primitive to make it straightforward to obtain point estimates and confidence scores for arbitrary cells in the database.
Consider the variable type_of_orbit. It is easy to see that many values are missing:
End of explanation
satellites.q(DROP TABLE IF EXISTS "inferred_orbit";)
satellites.q(
CREATE TEMP TABLE IF NOT EXISTS inferred_orbit AS
    INFER EXPLICIT anticipated_lifetime, perigee_km,
        period_minutes, class_of_orbit,
        PREDICT type_of_orbit AS inferred_orbit_type
            CONFIDENCE inferred_orbit_type_conf
    FROM %g
        WHERE type_of_orbit IS NULL;
)
Explanation: The following query produces a table with several variables, along with both a predicted value for type_of_orbit and the confidence associated with that value:
End of explanation
satellites.q(SELECT * FROM inferred_orbit LIMIT 30;)
satellites.pairplot(
SELECT inferred_orbit_type, inferred_orbit_type_conf, class_of_orbit
FROM inferred_orbit;
, 
                    colorby='class_of_orbit');
Explanation: We can visualize the result both in tabular and graphical form.
End of explanation
satellites.q(DROP TABLE IF EXISTS "unlikely_periods";)
satellites.q(
CREATE TEMP TABLE IF NOT EXISTS unlikely_periods AS
    ESTIMATE name, class_of_orbit, period_minutes,
        PREDICTIVE PROBABILITY OF period_minutes
            AS "Relative Probability of Period"
    FROM %g;
)
satellites.q(
SELECT * FROM unlikely_periods
    WHERE class_of_orbit = 'GEO'
        AND period_minutes IS NOT NULL
    ORDER BY "Relative Probability of Period" ASC LIMIT 10;
)
Explanation: The plot on the bottom left shows that the confidence depends on the orbit class and on the predicted value for the inferred orbit type. For example, there is typically moderate to high confidence for the orbit type of LEO satellites — and high confidence (but some variability in confidence) for those with Sun-Synchronous orbits. Satellites with Elliptical orbits may be assigned a Sun-Synchronous type with moderate confidence, but for other target labels confidence is generally lower.
Note that many standard techniques for imputation from statistics correspond to INFER ... WITH CONFIDENCE 0, as they have no natural notion of confidence.
Query the implications using BQL — Identify anomalies
Another core data cleaning operation is identifying erroneous values — including (univariate) outliers and other kinds of anomalies. BQL makes it straightforward to assess the predictive probability density of each cell in the data.
Recall earlier that we mentioned that some of the relations are governed by
the laws of physics and are thus nearly deterministic? We can use this
determinism coupled with our notion of anomalousness to search the table for
data-entry errors. A geosynchronous orbit should take 24 hours
(1440 minutes). Let us display the anomalous values for satellites in
geosynchronous orbit.
End of explanation
<END_TASK>