Temat: Zarządzanie złożonymi danymi w R

Czołem.

Zaczynam się przymierzać do R. Widzę, że ma ogromne możliwości manipulowania danymi. Bardzo podobają mi się ramki danych. Kolega, który jest programistą i zna R powiedział mi, że to bardzo przypomina tabele i że ramki można ze sobą łączyć funkcją merge tak, że naśladuje to komendę SQLa "join". Tutaj przeczytałem w jednym z wątków o pakiecie sqldf i RODBC. Jedna osoba napisała jak wygodnie wybrac kolumnę SQLem, a druga pokazała coś skomplikowanego w języku R z "NA" w wektorze gdzie jeszcze trzeba liczyć pozycję tej kolumny, brrrrr.

Mam do czynienia często ze złożnymi danymi. Branża nie jest istotna, ale istotny jest fakt, że są to dane z wielu różnych ośrodków raportujących, wiązane identyfikatorami, często bardzo "zabrudzone" to jest niespójne kodowania, dużo błędów, przesunięcia o kolumnę i tak dalej. Kolega programista powiedział, że nie wyobraża sobie pracy z takimi danymi inaczej, jak w SQLu.

Ja nie znam na razie SQL, a te wszystkie samouczki to są dobre do podstaw, a jak mam kilkanaście tabel połączonych relacjami i ten kolega mi pokazuje subquery w subquery z subjoinami, grupowaniami, havingami i selektami w selektach w subselektach i rankingami, pivotami, partycjami, to mi się w oczach troi. Miną lata, zanim dojdę do takiej biegłości jak on. Ale widzę w tym ogromny potencjał. Te możliwości R w manipulowaniu danymi wydają się śmieszne przy możliwości wykorzystania do tego SQLa. Powinienem się go zacząć uczyć. Ale że stracić czas jest najłatwiej, to chciałem zapytać was, czy wykorzystujecie w swojej pracy SQL i jak pracujecie w ogóle na danych? Zatrudnilibyście analityka, który nie zna SQLa, a ma pracować ze złożnymi strukturami danych? Może wystarczyłby Excel i w nim obrabiać dane, a potem zapisać do csv, albo kopiować do schowka i read.table?

Proszę o dyskusję o waszych doświadczeniach.

Temat: Zarządzanie złożonymi danymi w R

Trudno mi sobie wyobrazić profesjonalistę bez bardzo dobrej znajomości SQL. To jest jak alfabet. Większość danych biznesowych jest zapisana w jakiejś strukturze relacyjnej, żeby ją rozumieć, trzeba myśleć językiem kwerend, czyli znać abecadło.

Jeśli chcesz sprawnie pracować w biznesie, to nawet jeśli masz ambicje związane tylko z modelowaniem i analizą statystyczną, inwestycja czasu w SQL nie będzie stracona. Jestem takiego zdania, mimo , że od pewnego czasu 99% zadań przetwarzania danych realizuję tylko w SAS/4GL.

I nie taki diabeł straszny - kwestie zarządzania i administracji bazą możesz pominąć, wystarczą na początek tylko podstawy optymalizacji, aby głupot nie robić. Polecam "SQL. Receptury" (Anthony Molinaro ).

Temat: Zarządzanie złożonymi danymi w R

Nieistotne Telco? ;) :P

Osobiście od kilku ładnych lat nie wychodzę z jakiegoś edytora SQLa i to nie tylko przy statystyce, ale także we wdrożeniach i integracji systemów IT. Można rzec, że "myślę SQLem" i nie przerażają mnie te wszystkie "subselekty", są dla mnie dość naturalne (co "przeraża" z pewnych względów niektórych projektantów oprogramowania w paradygmacie obiektowym, ale to inny temat :) ). Po prostu jak raz "zaskoczysz", to "opiszesz SQLem świat" :) Inna sprawa to optymalizacja zapytań, ale Bogdan ma rację - umiejętności DBA pracującego w banku Ci nie są potrzebne na takim poziomie wykorzystania bazy.

Moja działka to medycyna. Często dostaję od lekarzy dane w najróżniejszych kombinacjach formatów. Wszystko się rozjeżdża - kodowania, daty, jak piszesz - poprzesuwane kolumny, niespójne opisy/etykiety, błędy grube, łączenia danych przez kilka arkuszy po IDkach, różne kodowania braków danych, powielone rekordy, "mieszanie układu wierszowo/kolumnowego", tj. po kilka rekordów na pacjenta, ale jednocześnie inne dane "hierarchiczne" (np. follow-up leczenia) w kolejnych... kolumnach, a w dodatku po kilka wielkości w rekordzie oddzielonych raz przecinkami, raz średnikami. I można tak wymieniać długo. Nie wspomnę, ze czasem do jednego prograu mam dane od różnych osób w różnych formatach i wg różnych koncepcji. DRAMAT. 70% czasu analizy to naprawdę oczyszczanie danych.

Osobiście nie wyobrażam sobie, jak można pracować na złożonych zbiorach danych bez relacyjnej bazy danych (grupowania powiązanych danych w tabele - relacje, łączenie ich zależnościami, itd) i SQLa, bez dokładnego typowania danych. Po to 40 lat temu ktoś to wymyślił, by zgrabnie i prosto opisać powiązane ze sobą w skomplikowany sposób dane, pytać o nie, a także zmniejszyć redundancję ich przechowywania (dziś to już stopniowo marginalizowany argument). Skoro R pozwala pracować z ODBC/JDBC i Oraclem wprost, to naprawdę grzechem jest babrać się w dzierganie po ramkach danych, mając coś tak potężnego, jak SQL, że o PL/SQL czy T-SQL nie wspomnę (RODBC tylko pośredniczy w komunikacji z bazą, więc jeśli mam SQL Server, to mogę pisać zapytania w T-SQL).

Ramki danych jako wynikowy kontener na to, co zwróci SQL. Czasem drobne operacje na indeksach i wybór kolumn. To wszystko.

Co do zatrudnienia... Jesli gość umie wcisnąć moje dane w eRowe ramki i sprawnie tym operować, to OK. Ale jednak wahałbym się zatrudnić analityka danych, który nie zna SQLa. Znajomość Excela? No może aby mi umiał wczytać CSVy z kolejnymi kawałkami danych, przeglądnąć je "autofiltrem" i wygenerować INSERTy przez "złącz.teksty()". Aha i ewentualnie by wygenerował "śliczne sweetaśne biznesowo" wykresy opierając się o MS Query.

Wszelkie oczyszczanie danych - wyłącznie SQLem przez SELECT/GROUP BY i UPDATE. Jedynie do małych porcji danych - excelowy Autofiltr (to jest zmyślne narzędzie).

Ważny "bonus", o którym często przypominam analitykom, których staram się zachęcić do SQL na szkoleniach. Robisz analizę dla klienta i:

1. z woli Twojej bądź klienta masz konsultować wyniki z innym analitykiem. Musisz mu więc przekazać dane i określić warunki ich selekcji.

2. kryteria wyboru danych zmieniają się co chwila, a są złożone. Podaję REALNY przykład:

Pacjent z grupą rozpoznania Roz1 i/lub Roz2, mający w ich ramach odpowiednio 2 lub 3 i IVa na skali S, leczony lekiem L1 i/lub L2 przez pierwsze 2 tygodnie, a potem jednym z leków {L5, L6, L7} przez kolejne 3 tygodnie, któremu w ostatnich 2 latach wykonano zabiegi Z1 i/lub Z2, z których Z2 zakończył się sukcesem zdefiniowanym, jako "stabilny poziom parametru klinicznego PK1 przez 4 okresy od operacji" (stabilny to np. odchylenie góra o X jednostek). Ten sam pacjent pojawia się w bazie 3 krotnie z uwagi na reoperacje, które należy pominąć, chyba, że powód reoperacji był jednym z { R1, R2, R3 }. Można tak długo :)

Weź to teraz komuś opisz "słowno-muzycznie" i miej nadzieję, że się nie pomylił przy selekcji danych. Bo jak się pomylił, to albo podniósł Ci ciśnienie twierdząc, że policzyłeś bzdury, albo sam stracił kupę czasu na analize złej próby. A tak - wysyłam mu bazę danych (np. SQLite, MDFa SQL Serverowego albo "dumpa" z PL/SQLa) i komplet zapytań SQLowych do każdego wyboru próby. Mam zatem gwarancję, że jak podepnie moją baze danych i wykona moje zapytanie, to dostanie identyczną próbę. A ponieważ SQL to język formalny, więc jeśli się go nauczy, może wychwycić moją pomyłkę w definicji próby, np. niewłaściwe zagnieżdżenie, bądź źle dobrane warunku. Albo powtórz złożony tok rozumowania, który doprowadził Cię do konkretnego zapytania. Spamiętasz to? Przy X analizach w N projektach? A tak - do każdej analizy robisz sobie załącznik, gdzie są ponumerowane zapytania, a w treści, jeśli potrzeba, robisz w Wordzie odnośniki do nich. W razie potrzeby kontroli bądź zmiany definicji próby masz wszystko na wyciągnięcie ręki.

Owszem, to samo możesz zrobić w R. Mimo wszystko wolę tu jednak język deklaratywny, niż sekwencyjny.

Przechowywanie danych i ich selekcję zostawmy bazie danych, a obliczenia matematyczne - eRowi.

Temat: Zarządzanie złożonymi danymi w R

Jeżeli liczba rekordów nie przekracza setek tysięcy, to każde z wymienionych narzędzi jest dobre o ile potrafisz je wykorzystać do wykonania zadania. Jeżeli bazy są większe to zdecydowanie SQL.

Te możliwości R w manipulowaniu danymi wydają się śmieszne przy możliwości wykorzystania do tego SQLa.

??

Może wystarczyłby Excel i w nim obrabiać dane, a potem zapisać do csv, albo kopiować do schowka i read.table?

Można bezpośrednio wczytać to R i tu obrabiać (pakiet XLConnect lub xlsx lub RODBC).

Temat: Zarządzanie złożonymi danymi w R

Zgadzam się z kolegami w całej rozciągłości, bez SQL ani rusz. Wręcz należy przerzucać na SQL wszystko, co tylko się da. Pamiętam projekt, w którym fantastycznie sprawdziły się SQLe automatycznie sklejane przez R na bazie modeli budowanych w R i wykonywane po stronie bazy (i fantastycznie znaczyło zdaje się wzrost szybkości pracy chyba sporo ponad 20 razy, od praktycznie niewykonalnego czasu do przebiegu algorytmu w kilkanaście minut). W ten sposób pominięta była konieczność transferu danych + konieczność ciągłego czytania i zapisywania paczek danych w R w różnych strukturach pamięci (zbyt dużo danych dla pamięci operacyjnej). Nawet część operacji matematycznych też była przerzucona na bazę po dopisaniu kilku funkcji (np. przybliżenie odwrotnej dystrybuanty).

Ale nie wyobrażam sobie też analizy danych bez narzędzia, w którym szybko wyliczę kilka, kilkadziesiąt a jak potrzeba i kilka tysięcy różnych wskaźników (które mogą być nawet liczone tylko tymczasowo na potrzeby testu podejścia do danych lub na potrzeby jednej analizy), czy eksplorując dane szybko wyrzucę kilkadziesiąt wykresów do przejrzenia, itp. (akurat w biznesie u mnie częściej to będzie SPSS niż R, w nauce: różnie, chyba zależy to od tego gdzie mam na starcie dane i czy przewiduję, że będę potrzebował R).

W zarządzaniu danymi R ma akurat jedną bardzo ważną zaletę: bardzo łatwe jest adresowanie dowolnej komórki (obszaru, wektora, itd.) danych w dowolnej strukturze danych z dowolnego poziomu. Trochę wyobraźni i można z tym zdziałać cuda, co pokazuje wiele przykładów z tej grupy.

Tak naprawdę ważna jest elastyczność. SQL, PL/SQL, Excel (też się przecież przyda!), 4GL, syntax SPSS-a, R, PMML: im większy repertuar narzędzi, tym więcej możliwości. Chyba najważniejsze jest, żeby się tego wszystkiego nie bać i śmiało sięgać po nowe narzędziami (no, może warto by wyłączyć z tej rekomendacji śmiałe selecty na bazach produkcyjnych...), bo i tak najbardziej wymagająca praca stoi po stronie przełożenia wyników analizy na zastosowanie w praktyce.

Temat: Zarządzanie złożonymi danymi w R

Postanowiłem zrobić mały test, by pokazać, jak bardzo SQL może uprościć życie, o ile tylko się go dobrze zna (ale to dotyczy absolutnie wszystkiego).

Wziąłem sobie wycinek rzeczywistej struktury danych z jednego z moich projektów.
Założyłem nową bazę danych, zaprojektowałem od zera strukturę tabel.
Powrzucałem do nich jedynie tyle atrybutów, by zachować sens, a nie narobić się jak głupi (w projekcie tych atrybutów jest masa).

Tutaj drobna uwaga:
Do dobrych praktyk projektowania baz danych (chociaż nie związanych z normalizacją *NF) należy "słownikowanie" danych. Zwykle (pomijam patologiczne przypadki) zmniejsza ono znacznie redundancję w danych i ułatwia panowanie nad porządkiem w danych (chodzi o literały, np. nazwy chorób). Efektem tej praktyki są u mnie tabele XXX_DICT, czyli słownikowe. Nie są one jednak bezwzględnie potrzebne i cały model można uprościć o te tabele. Trzeba tylko wtedy znajdujące się w nich pola opisowe (wszelkie nazwy) trzymać wprost w innych tabelach, np. z góry na dół pełne nazwy zabiegów/leków/rozpoznań, zamiast jedynie kluczy obcych do odpowiedniej pozycji w tabeli słownikowej.

Tabele wypełniłem skromnie danymi - tak, by w wyniku dostać chociaż dwóch pacjentów.

Mając "środowisko pracy", postanowiłem sprostać zadaniu podobnemu do tego, jakie postawiłem w moim poprzednim poście.

Treść:
1. Pacjent z grupą rozpoznania Nowotwory przewodu pokarmowego i/lub Nowotwory kończyn,
2. mający w ich ramach odpowiednio III lub IV na skali rangowej albo T3 N0 M0 na skali TNM,
3. leczony lekiem LekA i/lub LekC przez pierwsze 2 tygodnie, a potem LekB i/lub LekD przez kolejne 3 tygodnie,
4. a różnica między czasami podania leków to więcej niż 2 tygodnie
5. któremu w ostatnich 2 latach wykonano zabiegi ZabiegA i/lub ZabiegB,
6. które zakończyły się sukcesem zdefiniowanym jako "stabilny poziom parametru klinicznego ParametrB przez 4 okresy od operacji"
7. stabilny to wahania góra o 3 jednostki.
8. dopuszczamy jedynie pacjentów włączonych do leczenia, z jego kontynuacją i reoperacją z powodu "A" (zasugerowaną w oryginale zabawę z "powtórkami" pacjenta łatwo rozwiązać korzystając z agregacji GROUP BY w podzapytaniu w WHERE)

----------------------------------------------------------------

Diagram danych:




Próbka danych:




Zapytanie o powyższe i wyniki. Wiem, że może przerażać, ale jest naprawdę czytelne.

Uwaga - ponieważ GL ma problem ze skalowaniem obrazków w powiększeniu (cóż za nowość), proszę kliknąć w poniższy obraz, a otworzy się w pełnym rozmiarze w nowym oknie przeglądarki, zamiast w "galerii".



I przykład wywołania w R. Do częstego wykonywania kwerend wygodnie jest napisać sobie funkcje opakowujące poniższy kod. Albo po jednej funkcji na kwerendę, albo, jeśli się ma ponazywane krótko pliki z kwerendami, to można tę nazwę podawać jako parametr do jakiejś ogólnej funkcji RunQuery(...)





Dla ćwiczenia można spróbować zapytać o te same dane w R :)
Zainteresowanym mogę podesłać CSV z danymi (LEFT JOINy od poziomu Pacjenta)Adrian Olszewski edytował(a) ten post dnia 16.12.11 o godzinie 05:55

Temat: Zarządzanie złożonymi danymi w R

Adrian Olszewski:
Postanowiłem zrobić mały test, by pokazać, jak bardzo SQL może uprościć życie, o ile tylko się go dobrze zna (ale to dotyczy absolutnie wszystkiego).

Wziąłem sobie wycinek rzeczywistej struktury danych z jednego z moich projektów.
Założyłem nową bazę danych, zaprojektowałem od zera strukturę tabel.
Powrzucałem do nich jedynie tyle atrybutów, by zachować sens, a nie narobić się jak głupi (w projekcie tych atrybutów jest masa).

Adrian to jest chyba dobry przykład na to, aby o strukturę danych dbać na etapie wprowadzania danych. Do tego celu bazy relacyjne są bardzo dobre (o ile nie najlepsze).
Tabele słownikowe mogą się również przydać do sprawdzania danych poprzez sprawdzenie czy wpisy znadujące się w polu znadują się w tabeli słownikowej.

W SQL można to zrobić tak:
SELECT dane.* FROM dane LEFT JOIN słownik ON (dane.pole=słownik.pole) WHERE (słownik.pole Is Null);

W R to samo można zrobić tak:
subset(dane,!pole %in% słownik.pole)

Aby kogoś przekonać do konkretnego narzędzia najlepiej podać kilka przykładów rozwiązań problemów z którymi spotyka się na co dzień.
Generalnie najlepiej jest znać kilka narzędzi, bo niektóre zadania są proste do wykonania przy użyciu jednego narzędzia ale trudne bądź niemożliwe przy użyciu drugiego.
Minimum dla analityka to podstawy Excela, znajomość SQL, R (lub innego pakietu statystycznego) przy czym znajomość pakietu statystycznego lub SQL bardzo dobra (na który lepiej postawić zależy od rodzaju wykonywanej pracy).
Jeżeli głównym zadaniem jest czyszczenie danych można też roważyć PERLA lub PYTHONA.
Jeżeli ilość analizowanych danych jest bardzo duża (pojęcie jest nieco względne, bo to się zmienia szybko w czasie) to pozostaje SQL lub SAS (chociaż R też próbuje na tym polu dogonić konkurencję).

Temat: Zarządzanie złożonymi danymi w R

Tutaj jest zestawienie które daje pewien obraz realnej sytuacji:

http://blog.revolutionanalytics.com/2011/12/emc-survey...

Temat: Zarządzanie złożonymi danymi w R

Z mojego punktu widzenia, osobnym etapem jest przetwarzanie danych i modelowanie/analiza danych. Takie podejście wynika np. z metodologii SEMMA i zakłada, że punktem wyjścia dla analizy jest płaska tabela analityczna ze wszystkimi dostępnymi predyktorami i charakterystykami obserwacji. W konsekwencji, jednych narzędzia używa się do przygotowania danych (w tym bardzo specjalistycznych - jak np. SAS ABT Designer dla SAS) , a innych do modelowania. A ponieważ od czasu do czasu analityk musi też w jakimś stopniu dotknąć etapu przetwarzania danych, to odpowiedź na pytanie, czy znajomość dedykowanej do tego technologii (np. SQL) jest potrzebna - staje się oczywista.

Natomiast im bardziej środowisko pracy jest podporządkowane modelowaniu, tym mniej powinno być takich okoliczności i znajomość tylko końcówki analitycznej (czy to R, czy SAS Enterprise Miner, czy Clementine lub cokolwiek innego) staje się coraz bardziej wystarczająca. Ale sytuacja, w której nie trzeba nigdy sięgać do "surowych" danych (i tym samym używać dedykowanych narzędzi jak SQL) to chyba raczej utopia

Temat: Zarządzanie złożonymi danymi w R

Wojciech Sobala:
Adrian to jest chyba dobry przykład na to, aby o strukturę danych dbać na etapie wprowadzania danych.

Tak jest :)

I tutaj jak na dłoni widać zalety narzędzi takich, jak eCRF (forma ankiety elektronicznej), które dzięki sztywnym regułom wprowadzania danych (możliwość dowolnie złożonej walidacji) praktycznie gwarantują szeroko pojętą "czystość" danych - merytoryczną i strukturalną. A jeszcze jak dodamy do tego taki R jako serwer obliczeń (do celów walidacji, jak i analiz okresowo przeczesujących i na-żądanie), to już full wypas.

Ale... rzeczywistość jest brutalna. Ten projekt, którego fragment pokazałem (z kilkoma zmianami z oczywistych względów), to dane z Excela i składają się nań pliki dostarczane przez różne osoby.

Zanim dane zostały wprowadzone do bazy, musiałem poczyścić ile się dało i poukładać je w Excelu w postać "rekordową" (zamiast kolumnowej) i porozbijać na osobne tabele (kolejne arkusze), potworzyć kolumny z kluczami... a potem, za pomocą funkcji złącz.teksty() tworzyłem SQLowe INSERTy:

Wspólny tekst (komórka O2):

=ZŁĄCZ.TEKSTY("INSERT INTO ObjawyBadania(IDPAC, XXXX, YYYYY, Przyczyna, ZZZZ, WystepowanieGdzie, WystepowanieKiedy, StopienNasilenia, StopienNasileniaOpis......) VALUES(")

Teksty dla rekordów:

=ZŁĄCZ.TEKSTY(O$2;ZŁĄCZ.TEKSTY(ZŁĄCZ.TEKSTY(A3;",'";C3;"',";D3;",'";E3;"','";F3;"','";G3;"','";H3;"',";I3;",'";J3;"','";K3;"','";L3;"','";M3;"','");N3;"');"))

("złącz.teksty" występuje kilka razy, bo jedna funkcja może przyjąć max 15 parametrów).

W efekcie:

INSERT INTO ObjawyBadania(IDPAC, XXXX, YYYYY, Przyczyna, ZZZZ, WystepowanieGdzie, WystepowanieKiedy, StopienNasilenia, StopienNasileniaOpis......) VALUES(1,'A',24,'Nieznana','N','W każdych warunkach','Stale',2,'Średni','3',.....);

Generalnie najlepiej jest znać kilka narzędzi, bo niektóre zadania są proste do wykonania przy użyciu jednego narzędzia ale trudne bądź niemożliwe przy użyciu drugiego.

O to to :) To jak w statystyce, czasem wystarczy nam łatwa w interpretacji ANOVA + testy post-factum, a czasem męczymy się z interpretacją wyników regresji w zależności od kodowania macierzy eksperymentu w jakimś złożonym hierarchicznym planie eksperymentu.

Jeżeli głównym zadaniem jest czyszczenie danych można też roważyć PERLA lub PYTHONA.

O tak, zwłaszcza Perl i automatycznie powiązane z tym wyrażenia regularne (!). Niestety, Perl dla mnie od zawsze był "nie do przejścia", zniechęciłem się i nie nauczę się już chyba :] Ale widziałem go w akcji i doceniam jego niesamowity potencjał i zwięzłość. Na szczęście "regexy" obsługuje wygodnie sam R.

Jeżeli ilość analizowanych danych jest bardzo duża (pojęcie jest nieco względne, bo to się zmienia szybko w czasie) to pozostaje SQL lub SAS (chociaż R też próbuje na tym polu dogonić konkurencję).

Może też nie tyle "duże" w sensie rozmiaru, ale w sensie skomplikowania struktury i stopnia skomplikowania zapytań. Jeśli warunki nie są zbyt skomplikowane, to można się bawić na ramkach i wybierać sobie krok po kroku dane. Ale jeśli dane mają złożoną strukturę (pacjenci mają zabiegi, przyjmują leki, mają wyniki badań) i niezbalansowaną (różne ilości rekordów w tabelach podrzędnych, np. pacjenci mają różne liczby zabiegów), to nadal ramka do tego pasuje (bo przecież wynikiem zapytania SQL jest właśnie płaska, "zdżoinowana" ramka), ale już operowanie na niej do wyboru danych bez SQL może być naprawdę męczące.

Ma ktoś może jakąś propozycję w miarę strukturalnego (łatwego do ogarnięcia i modyfikacji) kodu R, który dokona mi takiej selekcji jak podany przeze mnie SQL?

Bogdan Taranta:
Z mojego punktu widzenia, osobnym etapem jest przetwarzanie danych i modelowanie/analiza danych.

Dokładnie, w każdej chyba metodyce analitycznej, w tym np. CRISP :)

Jednakże wiemy, że w szarej rzeczywistości, w przypadku np. analiz badań w medycznych programach naukowych, gdzie często nie było szczegółowego planu "co i jak będziemy dokładnie badać" (czyli dominują analizy post-factum), ciągła modyfikacja próby jest podstawą procesu analitycznego. Mnóstwo włączeń i wyłączeń warunków, ich modyfikacje, zagnieżdżanie, ujęcie następstwa zdarzeń. Pokazałem prosty przypadek, gdzie w warunku mamy następstwo zdarzeń "najpierw leczony 2 tygodnie lekiem A, potem 3 tygodnie lekiem B, 2 tygodnie przerwy między terapiami).

Poniżej ilustracja, jak może wyglądać prosta analiza w SQL z wykorzystaniem go do definiowania prób. Swego czasu implementowałem kilka podstawowych procedur statystycznych w SQL; próby były mało liczne, ale ich wybór czasem kosmiczny. Z pewnych przyczyn wolałem robić wszystko w TSQLu. Dziś skorzystałbym oczywiście z połączenia R + SQL:






Adrian Olszewski edytował(a) ten post dnia 16.12.11 o godzinie 12:36

Temat: Zarządzanie złożonymi danymi w R

Podzielę się rzeczywistym przykładem, choć może nie tak złożonym, jak w/w.





Inny przykład:



Jak wyglądała praca z tymi skryptami? Komentowanie, odkomentowanie i "F5", "copy with headers", Excel -> analizy. Dzięki SQLowi trwało to ledwie kilka długi nocy w asyście kardiologa, z którym "stroiliśmy" warunki. Takich analiz do jednego projektu mam tuziny.

Brakuje mi tylko jednego - środowiska, które łączyłoby mi np. MS SQL Management Studio i R :) Coś do wygodnego klepania skryptów SQL ze wszystkimi MSowymi "ficzerami" i wywoływanie funkcji R.

Zastanawiam się, czy nie spróbować "gadać" z R przez OLE-COM z poziomu T-SQL? Skoro można sprawdzać błędy ortograficzne w TSQL wywołując moduł sprawdzania pisowni Worda...... Hmmm, pomyślę nad tym. Ale bym miał wypas :) :)

-----------------------------

Przykłady zastosowania SQL w statystyce. Niedokończona od 5 lat prezentacja :)
http://www.scribd.com/doc/11456163/Szkolenie-z-TSQL-dl...Adrian Olszewski edytował(a) ten post dnia 16.12.11 o godzinie 14:27

Temat: Zarządzanie złożonymi danymi w R

Zadanie odwrócenia z układu wiersze na układ kolumny w R:

library(XLConnect)

dane_xls <- readWorksheetFromFile("dane.xls",sheet=1,startRow=1,  endRow=230, startCol=1, endCol=11, header=FALSE)

idx_start <- with(dane_xls,c(1,which(is.na(Col1))+1))

idx_end <- c(with(dane_xls, which(is.na(Col1))-1),nrow(dane))

dane_row <- lapply(seq_along(idx_start),function(x) dane[seq(idx_start[x], idx_end[x]),])

dane_col <- as.data.frame(do.call(rbind, lapply(dane_col,function(x) cbind(t(x[1,-1]), t(x[3:10,-1])))), stringsAsFactors=FALSE)

Temat: Zarządzanie złożonymi danymi w R

Tak przy okazji - niezły hardcore, ale mnie się to zaczyna podobać :)

Obliczenia R z poziomu TSQL.

Jak to wygląda krok po kroku:
1. Tworzę super-skomplikowaną-kwerendę i zapisuję jej wynki do tabeli tymczasowej
2. Eksportuję tabelę tymczasową do pliku CSV na dysku. Skorzystałem z zewnętrznego narzędzia - dobrzy ludzie napisali za mnie taką procedurę :)
3. Wywołuję słynnego StatConnectora i polecam eRowi wczytać plik CSV, a następnie wykonuję prostą analizę, której wyniki obrabiam i wyświetlam w SQL Server Management Studio. Nie umiem sobie poradzić na razie z nagłówkami danych - procedura eksportująca dane do CSV nie uwzględnia mi tej flagi. Potem spróbuję to poprawić, mając dostępny kod SQL procedury.

Tak wygląda zapytanie (po kliknięciu -> nowe okno z obrazkiem):




A taki jest efekt:





Wnioski - trzeba się trochę namęczyć. Ale jeśli wiem, jaką analizę chcę przeprowadzić, a jedynie dużo eksperymentuję z warunkami selekcji - to jest to dla mnie interesujące rozwiązanie. Mam za jednym zamachem świetny edytor SQL (SSMS), świetny pakiet obliczeń (R).

PS1: w kolejnym poście uprościłem powyższy kod, przenosząc większość wywołań R do funkcji eRowej, zapisanej w rprofile.site i wywołując jedynie nią.

PS2: zapewne istnieje też możliwość przeglądania wykresów - zapisujemy go w R do pliku przez png()/cairoPNG(), a następnie odpalamy jakąś przeglądarkę przez COM z plikiem obrazu jako parametrem.Adrian Olszewski edytował(a) ten post dnia 18.12.11 o godzinie 04:46

Temat: Zarządzanie złożonymi danymi w R

Adrian Olszewski:
Tak przy okazji - niezły hardcore, ale mnie się to zaczyna podobać :)

Obliczenia R z poziomu TSQL.

Fajne, ale dlaczego nie wykorzystałeś RODBC? (poza tym że być może nie widzi tabel tymczasowych)

Temat: Zarządzanie złożonymi danymi w R

W 3. poście zamieściłem zrzut ekranu właśnie z RODBC :) Dla mnie RODBC i SQLDF to podstawowe "woły robocze".

W przedostatnim poście zastanowiła mnie jedna rzecz.

R jest świetny do obliczeń, ale edytor czegokolwiek ma, delikatnie mówiąc, "uproszczony". Nie wyobrażam sobie pisania w tym kwerend takich, jak na zamieszczonych przeze mnie obrazkach. Poza tym zwrócona ramka danych nie zawsze jest sformatowana czytelnie, kolumny się rozjeżdżają. Ciężko się w tym przegląda dane... Lubię mieć "ogólny rzut oka na komplet danych" - ogarniam je wzrokiem na raz wszystkie (albo dużymi porcjami) i przyglądam im się. W R szybko braknie miejsca w konsoli, a wyświetlanie wiersza od-do, albo head/tail to jednak nie to samo...

Z drugiej strony Management Studio do SQL Servera ma genialne wsparcie dla pisania kodu, intellisense (podpowiedzi), ba, ma nawet debugger krok-po-kroku, że o diagramach czy planach wykonania nie wspomnę. W dodatku wygodnie się w nim przegląda dane i równie łatwo, dwoma kliknięciami eksportuje do Excela i wraz z nagłówkami kolumn.

Praca więc wygląda tak, że najpierw w SSMS trzeba sobie napisać kwerendę i ja przetestować, a potem zapisać ją do pliku. Po stronie R trzeba tę kwerendę wczytać z pliku, jak na wspomnianym zrzucie ekranu, do zmiennej i wykonać w RODBC.

Podczas analizy statystycznej mamy zazwyczaj dwie sytuacje:

1. mamy raczej prostą selekcją danych (wczytujemy jakiegoś CSV/XLSa), natomiast tak średnio wiemy, co będziemy badać i ile tego będzie (bo np. założenia niespełnione, bo metoda krokowa, etc.), eksperymentujemy i uruchamiamy wiele różnych analiz.

Tutaj wystarczy sam R.

2. wiemy dokładnie, co chcemy w danej chwili badać, np. regresję liniowa, tablice kontyngencji, ale wybór danych jest bardzo złożony, co chwila eksperymentujemy z warunkami (np. siedzi z nami lekarz specjalista i na bieżąco formułuje różne żądania co do wyboru danych).

Tutaj z kolei ważna jest edycja kodu SQL i sprawdzanie, co kwerenda zwróciła, czy wyniki są zgodne z oczekiwaniami (zwłaszcza, jak warunki są złożone, pozagnieżdżane, powiązane operatorami logicznymi). Sama analiza w R jest tutaj prosta, schematyczna (może to robić np. jakaś funkcja użytkownika, która od razu formatuje wyniki), kluczem jest selekcja danych.

Przypadek #1 może składać się z wielu przypadków #2.

Dlatego pomyślałem, że fajnie byłoby mieć coś, co realizuje ten drugi krok :) Czyli wygodny edytor SQL z wykonaniem obliczeń w R - ale bez dodatkowych narzędzi już. Tylko edytor SSMS.

I powyższy kod to było właśnie moje rozwiązanie tego problemu. Jednak było to bardzo męczące. Każde jedno pierdółkowate wywołanie R, choć pozwalało to dokładnie kontrolować, co się dzieje, to osobny blok z funkcją Evaluate/Ev.NoReturn i sprawdzenie kodu błędu. To, co miało się wyświetlić, musiało być objęte nawiasami (). No i ciągłe pamiętanie o transpozycjach.

A poniższy kod to uproszczenie całej sprawy.

1. Najpierw w R tworzymy funkcję, która przyjmie jako parametr ścieżkę do CSV, policzy nam co trzeba i sformatuje wyniki. Sprawdzamy jej działanie. Zapisujemy jej treść do /etc/rprofile.site. To bardzo ważne, by ona się tam znalazła, bo podczas tworzenia obiektu StatConnector tworzona jest zupełnie nowa instancja R. Jeśli definicja funkcji nie znajdzie się w tym pliku startowym, nie będzie widoczna w S.Conn.

Trzeba pamiętać, że dla TSQLa wszystko musi być transponowane. rbind -> cbind i v-versa, macierz -> t(macierz) i tak dalej. Inaczej wszystko wyjdzie odwrócone.

MojaFunkcja <- function(plik) {

dane <- read.csv(plik, header=TRUE, sep=",");

names(dane) <- c("Wiek", "Parametr");

s<-summary(lm(Parametr~Wiek, data=dane));

cbind(c(format(s$call$formula), sprintf("Adj. R2=%.2f", s$adj.r.squared), " ", " ", " "), rbind(c(" ", rownames(s$coeff)), (cbind(colnames(s$coeff), t(s$coeff)))))

}

2. Upraszczam kod SQL wywołując jedynie moją eRową funkcję.
Kod i wynik (klik):




PS: Przy okazji - w poprzednim poście uaktualniłem zrzuty ekranu i pokazuję, jak można wczytać wynik tabelaryczny (np. regresji) do tabeli (np. tymczasowej) przez INSERT INTO EXEC. Dzięki temu można ten wynik dalej przetwarzać, albo łączyć z innymi wynikami.Adrian Olszewski edytował(a) ten post dnia 18.12.11 o godzinie 04:46

Temat: Zarządzanie złożonymi danymi w R

Bogdan Taranta:
Trudno mi sobie wyobrazić profesjonalistę bez bardzo dobrej znajomości SQL. To jest jak alfabet.

>Jestem takiego zdania, mimo , że od pewnego czasu 99% zadań przetwarzania

danych realizuję tylko w SAS/4GL.

A i w 4GL to PROC SQL to większość standardowych przetwarzań.

Temat: Zarządzanie złożonymi danymi w R

Adrian Olszewski:
W 3. poście zamieściłem zrzut ekranu właśnie z RODBC :) Dla mnie RODBC i SQLDF to podstawowe "woły robocze".

Chodziło mi wyłącznie o to aby nie zrzucać danych do csv, tylko użyć sqlQuery z poziomu R. Potem od razu tym samym połączeniem zwrócić dane przez sqlSave.

Pokazałeś pewne zastosowanie offline, ale nawet ciekawsze byłoby zastosowanie tego na środowisku produkcyjnym, np. można by na bieżąco uaktualniać modele zachowań klientów, modele finansowe, itp. wraz z pojawiającymi się nowymi danymi w bazie.

Temat: Zarządzanie złożonymi danymi w R

Krzysztof Hrycko:

A i w 4GL to PROC SQL to większość standardowych przetwarzań.

No to już kwestia - w moim odczuciu złych - przyzwyczajeń. Zwłaszcza na etapach transpozycji czy agregowania danych. PROC SQL jest usprawiedliwiony natomiast w trybie PASS-THROUGH, jeśli ma to uzasadnienie w wydajności.

Temat: Zarządzanie złożonymi danymi w R

Bogdan Taranta:
No to już kwestia - w moim odczuciu złych - przyzwyczajeń. Zwłaszcza na etapach transpozycji czy agregowania danych.

Co do transpozycji to nawet nigdy się nie zastanawiałem jak to zrobić w SQL, ale agregacja?
Nie wszystko się zmieści do hasha (chyba że ktoś ma 8-32 GB), a i proc means/freq czasami mi twierdzi że za duży zbiór (The SAS System stopped processing this step because of insufficient memory). Ja oczywiście mówię o środowisku na którym pracuję, nie wiem czy jest zestawione optymalnie, nie ja za to odpowiadam.
Po za tym macro+proc sql wg mnie to dobre rozwiązanie.

Ale, ale czy to wątek o R czy o SAS :)Krzysztof Hrycko edytował(a) ten post dnia 18.12.11 o godzinie 14:42

Temat: Zarządzanie złożonymi danymi w R

Miron Kołodziejczyk:
Chodziło mi wyłącznie o to aby nie zrzucać danych do csv,

Ależ właśnie we wspomnianym poście na początku wątku, na zrzucie pokazałem zastosowanie RODBC z wykorzystaniem sqlQuery:

 > library(RODBC)

 > con <- file("d:\\zapytanie.sql", open="r");

 > pacjenci.sql <- paste(readLines(con), collapse="\n");

 > close(con);

 > con <- odbcConnect("GLTest");

 > pacjenci.df <- sqlQuery(con, pacjenci.sql);

 > odbcCloseAll();

Pokazałeś pewne zastosowanie offline, ale nawet ciekawsze byłoby zastosowanie tego na środowisku produkcyjnym, np. można by na bieżąco uaktualniać modele zachowań klientów, modele finansowe, itp. wraz z pojawiającymi się nowymi danymi w bazie.

I dokładnie tak to wykorzystuję od 2007 - w szeroko rozumianych systemach analiz danych:

1. do złożonej walidacji danych. Wiadomo, że do walidacji typu "min/max", "należy do zbioru", "jest liczbą" nie potrzebuję nic więcej, niż standardowe walidatory ASP.NET, czy ew. jakaś prosta konstrukcja programistyczna. Ale zdarza się, że potrzebny jest "walidator kontekstowy", np. "delta check" w diagnostyce laboratoryjnej, czy jakiś zakres referencyjny, zależny od wyników historycznych i trzeba to określić jakimś modelem. Oczywiście mogę to zrobić w C#, ale jeśli mam wejść głębiej w statystykę, mogę wykorzystać do tego R. Byle walidacja nie dotyczyła każdego pola, bo "zajadę" serwer.

2. do analiz przeczesujących. Analizy takie uruchamiane są w określonych sytuacjach,"podczas wejścia do modułu", albo co zadany interwał. Przeczesują zbiór danych w poszukiwaniu alarmujących kombinacji wartości parametrów klinicznych i alarmują operatora, wyświetlając listę takich pacjentów.

3. do analiz na żądanie. Badacz otrzymuje listę analiz, jakie może wykonać na danych, a także panel definicji próby, gdzie może określić warunki za pomocą "wyklikania" odpowiednich ustawień, bądź specjalnego, uproszczonego metajęzyka (albo SQL bądź samej tylko klauzuli WHERE, jeśli go zna; znam badaczy, którzy uczyli się podstaw SQL właśnie do takich celów)

Schemat pracy to aplikacja eCRF (ankieta, formularz elektroniczne) napisana w ASP.NET albo .NET WinForms (oczywiście może być w dowolnym narzędziu - PHP, Java, CGI), SQL Server (lub inne bazy), Rscript.exe (o tym za chwilę) i wymiana informacji przez XML. Niestety, wymaga to dedykowanego serwera (Windows/Linux) z możliwością uruchamiania plików wykonywalnych. Jeśli jest to jakiś serwer stojący sobie u klienta, do którego jest pełen dostęp, to oczywiście nie ma z tym żadnego problemu. Gorzej, jak klient sam dzierżawi serwery i np. nie ma takiej akurat możliwości.

Poniżej zamieszczam nieco istotnych informacji prawnych na wypadek, gdyby ktoś zapragnął wykorzystywać R do takich celów komercyjnie i sprzedawać gotowy produkt analityczny lub oferować jego napisanie. Oczywiście w przypadku softu pisanego na własne potrzeby do własnej firmy nie ma problemu - GPL pozwala na takie komercyjne wykorzystanie "by nature". W biznesie zawsze warto mieć przemyślane kwestie licencyjne oprogramowania, by potem nie musieć się tłumaczyć po fakcie. Ignorantia iuris nocet.

------------------------------------------------------------
W zastosowaniach komercyjnych sprzedawanych innym podmiotom odradzam korzystanie ze StatConnectora oraz z R.NET ze względów licencyjnych. Jest to dość złożona sprawa. Otóż sam R jest na licencji GPL 2. Dziedziczy to po R.DLL, która jest na tej licencji. Oznacza to, że wszystko, co "linkuje" do R.exe/R.dll musi być również na tej licencji, a zatem jeśli dystrybuuję rozwiązanie programistyczne, muszę jednocześnie udostępnić źródła. StatConnector i R.NET, chociaż same są na licencji LGPL, to jednak linkują do R.DLL, "deklasując" się do GPL. Ja z kolei linkuję do nich. Zresztą - o czym dalej - gdyby nawet R nie było na GPL, to i tak miałbym problem, gdyby wykorzystał jakąkolwiek bibliotekę (np. car), która jest na licencji GPL, bo jest ona wykonywana w przestrzeni adresowej R.

Aby uniknąć problemów prawnych, muszę wykorzystać R tak, jak to przewiduje oficjalne FAQ do licencji GPL2, a więc komunikować się z R "na długość ręki", wywołując binarkę z parametrami i nie wymieniać z nią "dostatecznie złożonych struktur danych, by sugerowało to monolityczny produkt", sprawić, by mój produkt uruchamiał i działał bez zainstalowanego R i wreszcie zapewnić, by to klient zainstalował samodzielnie R (nie dystrybuować go). Mogę sobie natomiast wystawić taką usługę wdrożeniową, jak "przygotowanie środowiska pracy oprogramowania" i dodatkowo wycenić ;)

Stąd pomysł odpalania Rscript.exe --vanilla wraz ze skryptem, który przygotowuje w pewien sposób (uwaga, kolejna pułapka!) moja aplikacja, a następnie odbierać wyniki w postaci XMLa i obrazków (wykresy), a potem analizować XML i wyświetlać, albo wprost przetransformować go do postaci HTMLA szablonem XSLT.

Od razu tę samą metodę mogę zastosować pod Windowsem i Linuksem - tam też jest rscript. Tylko zamiast RODBC stosuję JDBC ze sterownikiem np. Oracle'a (Oracle można zainstalować na Linuksie).

Aby uniknąć wymiany złożonych struktur danych (niestety, nie istnieje definicja, jak należy rozumieć i czym mierzyć złożoność wymienianych struktur danych :/), przekazuję do R jedynie skrypt SQL, ewentualnie z jakimiś metadanymi, albo nawet.... nazwę pliku skryptu SQL, który R sobie wczyta. Podobnie w drugą stronę odbieram XMLa z wynikami.

Co do generowania skryptów dla R, to jest tutaj jeszcze jeden problem - otóż ja te skrypty musiałbym (przy dystrybucji softu) udostępniać. I to pomimo faktu, że skrypty wykonane za pomocą aplikacji GPL nie są przez tę aplikację "infekowane licencją". A to dlatego, że "skrypty odnoszące się do komponentów GPL również stają się GPL. A kiedy ma to miejsce? Kiedy użyjemy jednego z dziesiątek, jak nie setek pakietów R, który jest na licencji GPL :) Musiałbym kontrolować dosłownie każdą funkcję i strukturę (nawet być może data.frame) z każdego pakietu, z którego korzystam, bądź który jest ładowany automatycznie z pakietem, który akurat NIE jest GPL. Widać więc, że to się staje "niedeterministyczne". Dlatego skrypty trzeba generować z "kawałków kodu" i wg pewnego algorytmu, albo....... zgodzić się na ich udostępnienie. Pozostaje jeszcze kwestia taka, czy udostępnić je jedynie klientowi (pisane na zamówienie) i wtedy może nie być problemu, czy wszystkim, kto zechce, gdy ogłaszam się na stronie i oferuję wersję "demo".

Są także inne metody - np. napisanie adaptera, który komunikuje się z R.DLL przez R.NET czy StatConnector, a na zewnątrz wystawia webserwis lub w naszym własnym formacie po TCP/IP. Wtedy nie musimy mieć praw do uruchamiania exeków, wystarczy R.DLL + nasz webserwis. A z kolei obok stoi nasza aplikacja, która łączy się do tego serwera. Kluczem jest tutaj format komunikacji - gniazda, webserwisy, system plików (np. FTP).

Wszystkie te problemy znikają (czyli możemy spokojnie linkować do R.DLL), gdybyśmy nasz soft oferowali w modelu SAAS (software as a service), czyli wszystko dzieje się na naszych serwerach, a dane przesyłane są od klienta przez webservice, albo przez upload plików z danymi. Niestety - to też pociąga za sobą konsekwencje prawne: personalia pacjentów (musimy wymusić ich depersonalizację, co nie jest trywialne; pacjenta może definiować nawet unikalny obraz kliniczny! albo zgłosić bazę do GIODO), zabezpieczenie danych przez kradzieżą, uszkodzeniem (awaria serwera, bazy danych). Jest także kwestia, że gdy klient zechce, by usunąć wszystkie dane, trzeba tez usunąć je ze wszystkich backupów. Najlepiej kupić w takim wypadku miejsce w jakiś komercyjnym Data Center, co nie będzie tanie, ale daje nam to nie tylko święty spokój z licencjami lecz także obsługą informatyczną.

W tym przypadku nie następuje dystrybucja oprogramowania, tylko jego funkcjonalności. I dobrze, że R nie jest na licencji np. Affero, bo wtedy nawet dystrybucja funkcjonalności oprogramowania byłaby "zGiePeeLizowana".

No i wszystko do czasu, aż R nie zostanie objęty inną licencją. Owszem, można bazować na wersji np. 2.13, ale z biegiem czasu nowsze pakiety już się pod nią nie uruchomią.

Jest naprawdę dużo niuansów. Polecam dyskusję poświęconą OpenSource i GPL w tym wątku (trzeba dołączyć do grupy programistów .NET)

Oczywiście zawsze można skorzystać z komercyjnego S+ :]Adrian Olszewski edytował(a) ten post dnia 18.12.11 o godzinie 17:14