Eine gute Abfrage kann Ihnen zum Erfolg verhelfen
Niveau: Anfänger
Brian Corcoran, JMP-Direktor für Forschung und Entwicklung, SAS
JMP bietet vielfältige Möglichkeiten, eine breite Palette an Datenformaten in das Produkt zu integrieren. Der Grund liegt auf der Hand: Im Laufe der Jahre wurden zahlreiche Tools zum Erfassen und Formatieren von Daten entwickelt. In den meisten Fällen unterstützen diese Tools die Konvertierung Ihrer Daten in JMP-Datentabellen nicht. Sie müssen diese Daten zur Analyse in JMP importieren. In anderen Fällen befinden sich die Daten möglicherweise bereits in einer Datenbank, die Sie vor der Analyse extrahieren müssen. Das Importieren der Daten kann wertvolle Zeit kosten. JMP hat sich im Laufe der Jahre weiterentwickelt und bietet nun leistungsstarke Datenerfassungsfunktionen, die Sie bei dieser Aufgabe unterstützen.
Dieses Dokument bietet einen kleinen Einblick in die Funktionen des Abfrage-Generators in JMP und zeigt anhand eines einfachen Beispiels die Verwendung des Excel-Assistenten. Es zeigt hoffentlich auch, dass der Weg auch das Ziel sein kann, da durch die Erstellung der Abfrage selbst Einblicke in die Daten gewonnen werden können. Während für die Desktop-Screenshots die Windows-Version verwendet wird, sind alle hier beschriebenen Funktionen auch in der Mac-Version von JMP verfügbar. JMP 14 wurde verwendet, aber JMP 13 kann alle besprochenen Funktionen ebenfalls.
Dieses Dokument stellt einen hypothetischen Anwendungsfall vor. Stellen Sie sich vor, Sie untersuchen globale Trends und die Datenbank der Weltbank liefert häufig wertvolle Informationen. Ein Kollege teilt Ihnen mit, dass die Daten des Weltentwicklungsindikators 2017 kürzlich verfügbar geworden sind und in die SQL Server-Datenbank des Unternehmens übertragen wurden. Es handelt sich um eine umfangreiche Tabelle mit über 400.000 Beobachtungen und 62 Spalten. [1] Sie öffnen JMP und wählen Datei -> Datenbank -> Abfrage-Generator, öffnen Ihre Datenbankverbindung und zeigen auf die Tabelle. Im ersten Fenster des Abfrage-Generators wählen Sie die Tabelle „world_bank“ als primäre Tabelle und verwenden „Tabellen-Snapshot“, um einen Blick darauf zu werfen:
Query Builder Table SnapshotTabellen-Snapshot des Abfrage-GeneratorsTabellen-Snapshot des Abfrage-Generators
Das Format ist etwas merkwürdig, zumindest für den typischen JMP-Benutzer. Es gibt vier Variablen oder Spalten für Länderinformationen und dann eine Spalte für jedes Jahr, in dem Daten vorhanden sind. Die Zeilen enthalten scheinbar eine Region und dann die gemessenen Daten. In mancher Hinsicht sieht es ganz anders aus als man von einer typischen JMP-Tabelle erwartet. Der Ländername „ Arabische Welt “ scheint eine Region darzustellen, was ebenfalls verwirrend ist. Wenn Sie auf die Schaltfläche „ Jetzt importieren “ klicken, wird die Tabelle angezeigt und die Dinge werden etwas übersichtlicher. Wenn Sie zur Mitte scrollen, können Sie sehen, dass die Zeilen mit dem betreffenden Land beginnen und dann den Daten zu einem bestimmten Thema, wie „ Ländliche Bevölkerung “, folgen. Dies ist alles nach Ländern sortiert, was nicht intuitiv erscheint.
Immediate Import Table AppearanceDarstellung der Tabelle beim sofortigen ImportDarstellung der Tabelle beim sofortigen Import
Fangen wir noch einmal von vorne an. Dieses Mal können wir „ Abfrage erstellen “ statt „ Jetzt importieren “ auswählen. Wir wissen, dass wir die ersten vier Variablen mit Länderinformationen benötigen. Daher wählen wir diese aus und klicken im Query Builder im Reiter „ Eingeschlossene Spalten “ im Spaltenauswahlfeld auf „ Hinzufügen “. Da es hier viele Daten gibt, bearbeiten wir nur die Variablen für 2010–2017. Der obere Teil des Dialogs sieht nun so aus:
Initial Column SelectionAnfängliche SpaltenauswahlAnfängliche Spaltenauswahl Wir haben unsere vorherige Situation noch nicht wirklich verbessert. Nun können wir die Variable „ Indikatorcode “ in den Bereich „Sortieren nach “ des Abfrage-Generators verschieben. Dadurch werden die Daten nach dem untersuchten Thema und nicht nach der jeweiligen Region oder dem Land sortiert. Dies erscheint für die Analyse intuitiver.
Ordered by Indicator CodeSortiert nach IndikatorcodeSortiert nach Indikatorcode
Es gibt weiterhin Probleme mit den Daten. Erstens enthalten sie regionale Gruppierungen, die derzeit nicht von Interesse sind. Wie lassen sich diese aggregierten Einträge ausschließen? Und wie lässt sich ein bestimmtes Element, beispielsweise „Düngemittelverbrauch“ , am besten abfragen?
Es wäre sicherlich möglich, alle Daten in JMP zu importieren und dann die regionalen Gruppierungen zu löschen, wenn wir nicht nach „Indikatorcode“ sortieren. Dies stellt jedoch eine Belastung für die Person dar, die die Daten analysiert. Wenn es sich dabei um jemanden handelt, der die Abfrage nicht erstellt, kann das verwirrend sein. Innerhalb der Abfrage wäre es möglich, nur die relevanten Länder auszuwählen. Was ist, wenn wir alle Länder der Europäischen Union benötigen? (Ich wollte Nordamerika verwenden, aber die Definition der Weltbank enthält nur die USA, Bermuda und Kanada!) Wir könnten sie manuell auswählen, aber es gibt einen besseren Weg. Dazu rufen wir schnell ein weiteres Tool in JMP auf, den Excel-Assistenten.
Nehmen wir in diesem Szenario an, dass wir häufig mit Daten der Weltbank arbeiten. Die Ländercodes sind wichtig, aber nicht intuitiv. Tabellen verwenden die Codes oft ohne Angabe des vollständigen Landesnamens, wie Sie auf der Website sehen können. Es kann praktisch sein, diese Codes in einer separaten Tabelle zu haben. Eine solche Tabelle ist vorhanden und lässt sich einfach als Excel-Datei von der Website importieren. Öffnen Sie in diesem Fall den Excel-Assistenten für die Tabelle mit den Ländern und Ländercodes. Die Website der Weltbank gruppiert diese in einer unscheinbaren Tabelle namens CLASS.xls, die Sie jedoch umbenennen können. Die Datei enthält zwei Tabellen: „Liste der Volkswirtschaften“ und „Gruppen“. Sehen wir uns „Gruppen“ an:
Preview of Groups Table in Excel FileVorschau der Gruppentabelle in der Excel-DateiVorschau der Gruppentabelle in der Excel-Datei
Es gibt die Gruppierung für die Arabische Welt, die wir in den Daten des Abfrage-Generators gesehen haben. Das sieht vielversprechend aus, aber beim Scrollen nach unten wird Europa noch nicht angezeigt, da die Vorschau bei 100 Zeilen endet. Wählen Sie „ Alle Zeilen anzeigen “, und wir sehen alles, einschließlich „ Europa & Zentralasien “, „ Europa-Gebiet “ und „ Europäische Union “. Die Gruppe „ Europäische Union “ enthält die Ländercodes für alle Länder, die derzeit der Union angehören. Können wir diese verwenden, um die Länder im Abfrage-Generator auszuwählen? Die Antwort lautet ja. Extrahieren wir also nur die Gruppe, die wir benötigen. Wählen Sie dazu die erste Zeile in der Vorschau mit „ Europäische Union “ aus und drücken Sie dann das grüne Plus neben „ Daten beginnen in Zeile“.
Select the First Row in “European Union”Wählen Sie die erste Zeile in „Europäische Union“Wählen Sie die erste Zeile in „Europäische Union“
Nun können wir im Dialog auf „Weiter“ klicken, die letzte Zeile mit „ Europäische Union “ auswählen und auf das grüne Plus neben „ Daten enden mit Zeile “ klicken. Dadurch werden die Daten auf die Länder reduziert, die uns interessieren.
Nun können wir „ Importieren “ auswählen und erhalten eine Zeile mit 28 Einträgen für die Länder der Europäischen Union. Die resultierende JMP-Tabelle speichern wir als „EU.jmp“.
Jetzt können wir zum Abfrage-Generator zurückkehren. Wir hätten die Länder, die uns interessieren, in einem mühsamen Auswahlprozess in einer Liste nach „Ländername“ oder „Ländercode“ auswählen können. Der Abfrage-Generator verfügt jedoch über einen leistungsstarken Filtertyp namens „Spaltenwerte abgleichen“. Um diesen zu verwenden, ziehen wir zunächst „ Ländercode“ in den Bereich „Filter“ . Dadurch wird eine lange Liste mit allen Codes erstellt. Jetzt können wir das rote Dreieckmenü bei „ t1.Ländercode “ für Filtertyp auswählen und „ Spaltenwerte abgleichen “ auswählen. Ein Feld mit den Namen der geöffneten JMP-Tabellen wird angezeigt. Da „EU.jmp“ noch geöffnet ist, wird es in der Liste angezeigt. Wir wählen es und die Variable ( Ländercode ) aus, die wir abgleichen möchten. Es ist auch wichtig sicherzustellen, dass „ Alle Zeilen “ und nicht „ Ausgewählte Zeilen “ aus der Dropdown-Liste ausgewählt ist, da wir in diesem Fall alle Länder möchten.
Selecting the Match Column Values filterAuswählen des Filters „Spaltenwerte abgleichen“Auswählen des Filters „Spaltenwerte abgleichen“
Dadurch werden unsere Ergebnisse auf die Beobachtungen reduziert, bei denen ein EU-Land beteiligt ist. Hier gibt es wirklich keine Zauberei. Wir erstellen eine WHERE/IN-Klausel, um die Ländercodes zu ersetzen. Im SQL- Bereich des Abfrage-Generators sehen Sie das von JMP generierte SQL:
Generated SQLGeneriertes SQLGeneriertes SQL
Wir hoffen jedoch, dass wir den Prozess deutlich vereinfacht haben. Nachdem wir die Daten auf das Wesentliche eingegrenzt haben, wäre es wünschenswert, die Auswahl der zu analysierenden Informationen zu vereinfachen. Dazu ziehen wir „ Indikatorname“ in den Filterbereich . Wir könnten zwar auf das gewünschte Element klicken und die Abfrage ausführen, aber eine interaktivere Benutzeroberfläche wäre wünschenswert. Wir drücken das rote Dreieck neben „ t1.Indikatorname“ und wählen „ Beim Ausführen Eingabeaufforderung “. Daraufhin wird ein Dialogfeld angezeigt, in dem wir den Titel für die Eingabeaufforderung festlegen können.
Prompt on RunEingabeaufforderung beim AusführenEingabeaufforderung beim Ausführen
Wenn wir nun auf „ Abfrage ausführen“ klicken, erhalten wir eine Liste mit den Elementen und können das gewünschte auswählen. Beispielsweise wählen wir „BIP-Wachstum (jährlich %)“ aus und erhalten eine Tabelle mit 28 Zeilen. Diese können wir dann in den Graph Builder einfügen. Ziehen wir den „Ländernamen“ in den Ablagebereich für Kartenformen , wird eine Europakarte erstellt. Anschließend können wir das gewünschte Jahr einfügen. Ab 2014 werden die Daten spärlicher, daher ergibt dieses Jahr:
JMP sucht nach „Slowakei“ statt nach „Slowakische Republik“, daher bietet sich hier die Möglichkeit, Recode zu verwenden. Bei der Betrachtung dieser Daten scheint es, als würden wir immer wieder eine ähnliche Operation durchführen. Wir wählen den Ländernamen für die Kartenform und dann das Jahr, das uns interessiert, um die Ergebnisse zu untersuchen. Wenn wir ein bestimmtes Jahr wiederholt betrachten, ist es sinnvoll, das Skript zu speichern und es als Post-Query-Skript in den Abfrage-Generator einzufügen. Hierfür gibt es im Vorschaufenster eine Registerkarte. Wenn wir zu unserem vorherigen Diagramm gehen und über das rote Dreieckmenü „Skript speichern -> Im Skriptfenster“ auswählen, erhalten wir ein Skript, das so aussieht:
Graph Builder(
Size( 534, 454 ),
Show Control Panel( 0 ),
Variables( Color( :Name( "2014" ) ), Shape( :Country Name ) ),
Elements( Map Shapes( Legend( 7 ) ) )
);
Wenn wir dies in die Registerkarte „Post-Query-Skript“ einfügen und die zu untersuchende Beobachtung auswählen, wird nach der Datenextraktion und Erstellung der JMP-Tabelle immer der gleiche Graph Builder-Bericht ausgeführt. Dies kann eine sich wiederholende Aufgabe erheblich vereinfachen.
Post-Query ScriptPost-Query-SkriptPost-Query-Skript
Das Beste daran: Beim Speichern der Abfrage können wir festlegen, dass der Endbenutzer die Dialoge des Abfrage-Generators gar nicht sieht. Durch Auswahl von „ Beim Öffnen ausführen “ im Dropdown-Menü neben dem Feld „Abfragename“ und anschließendes Speichern der Abfrage sieht der Endbenutzer nur die Eingabeaufforderung sowie die resultierende Tabelle und Grafik.
Run on OpenBeim Öffnen ausführenBeim Öffnen ausführen
Query with Run on Open started from Recent Files Abfrage mit „Ausführen beim Öffnen“ gestartet aus „Letzte Dateien“ Abfrage mit „Ausführen beim Öffnen“ gestartet aus „Letzte Dateien“ Eine Einschränkung bei der Verwendung des Filters „Übereinstimmende Spalten“ besteht darin, dass die Tabelle mit den abzugleichenden Werten auf dem Computer vorhanden sein muss, auf dem wir die Abfrage ausführen. Falls dies ein Problem darstellt, gibt es eine Möglichkeit, dies zu umgehen. Nachdem Sie den Filter mit den übereinstimmenden Spalten erstellt haben, gehen Sie zum roten Dreieckmenü für den Filter. In diesem Beispiel wäre das für „ t1.Ländercode“. Wählen Sie Filtertyp -> Manuelle Liste. Dadurch werden die vom Filter „Übereinstimmende Spalten“ erstellten Werte in eine Textliste importiert, die für SQL verwendet wird. Diese wird dann beim nächsten Speichern in der Abfrage gespeichert und Sie benötigen die Datentabelle nicht mehr zum Abgleichen. Der Nachteil dieses Ansatzes besteht darin, dass die Möglichkeit verloren geht, die ausgewählten übereinstimmenden Werte dynamisch zu ändern und die Abfrage zu bearbeiten.
An diesem Punkt haben wir eine große Menge verwirrender Daten gefiltert und eine wiederholbare Abfrage und Analyse erstellt, die wir an einen Endbenutzer senden können. Der Benutzer der Abfrage benötigt keine wirklichen Kenntnisse der Abfrage oder von JMP.
Verwendete Konventionen:
Die Fensterbereiche des Abfrage-Generators sind fett gedruckt
Schaltflächen/Dialogsteuerelemente sind „ in Anführungszeichen gesetzt und fett gedruckt“.
Variablen-/Spaltennamen werden „ in Anführungszeichen gesetzt und kursiv gedruckt “
[1] Weltbankgruppe. Weltentwicklungsindikatoren , Weltbankgruppe, und Website. 25. Januar 2018. https://datacatalog.worldbank.org/dataset/world-development-indicators . Erhalten unter der Create Commons CC BY 4.0-Lizenz.
