R data mining for beginners with Rattle - new version

Support for Microsoft version of R, new stuff worth checking, Data Mining GUI for those intimidated by R code: http://togaware.com/rattle-5-0-0-alpha-released/
Install: install.packages("rattle", repos="http://rattle.togaware.com")

Excel: Rank if with sumproduct (and VBA)

Look here: http://www.mrexcel.com/forum/excel-questions/82479-rank-if-2.html
 eg.
=SUMPRODUCT(--(A$3:A$11=A3)*(B3<B$3:B$11))+1

Big thanks. Helped me a lot today.

Music from my dream...

Ives: The Unanswered Question / Premil Petrovic / No Borders Orchestra Similar:

Marketing with R, very interesting articles.

My search started with the question, how to reduce the huge amount of information about the history of purchases, contacts of hundred thousands of customers to manageable few variables.
I came across  Recency, Frequency, Monetary value (RFM) analysis.

Review articles of these users on R bloggers.

https://www.r-bloggers.com/author/jack-han/
https://www.r-bloggers.com/author/analyze-core-r-language/

Cleaning data with Regex in Excel (then in R)...

I have a dirty column that should contain duration in months as integer, but it contains variants of strings: due to end on dd-mm-yy, duration x days, from dd.mm.yyyy through/to dd.mm.yyyy y.
Headache.

This is the day I started playing with regex.

First, how to find a date after a specific word:
http://stackoverflow.com/questions/19193251/regex-to-get-the-words-after-matching-string

After some reading (eg. http://stackoverflow.com/questions/18139493/finding-first-word-after-regex-match, http://stackoverflow.com/questions/546220/how-to-match-the-first-word-after-an-expression-with-regex


modifications and experimentations I reached the following compact solution:

[\n\r].*[skończy się:|until:|do:|zakończenia:]\s*(\w+.\w+.\w+)

Tested with: "The contract will last until: dd.mm.yyy or to the [...]" it will return just the date "dd.mm.yyy

Cute!

Macros, that work with Excel:

http://analystcave.com/excel-regex-tutorial/ (I used RegexExecute macro with success)
http://www.vbaexpress.com/kb/getarticle.php?kb_id=841
http://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops
http://www.vbaexpress.com/kb/getarticle.php?kb_id=841
http://superuser.com/questions/978532/how-do-i-use-regex-using-instr-in-vba
http://stackoverflow.com/questions/19481175/general-purpose-udfs-for-using-regular-expressions-in-excel (set of very useful tools)



R code to test:

http://stackoverflow.com/questions/34804708/matching-a-word-after-another-word-in-r-regex
https://renkun.me/blog/2014/02/20/extract-information-from-texts-with-regular-expressions-in-r.html

In R the following worked with me:

library(stringr)

str_extract(string=df$duration, pattern= "(?<=(dnia:|zakończenia:|until:)\\s)(\\w+.\\w+.\\w+)" )

ReadingList: Data Bases in R

1. Data Table by Matt Dowle.
Cheatsheet: https://s3.amazonaws.com/assets.datacamp.com/img/blog/data+table+cheat+sheet.pdf
https://www.analyticsvidhya.com/blog/2016/05/data-table-data-frame-work-large-data-sets/

Basics: https://www.dezyre.com/data-science-in-r-programming-tutorial/r-data-table-tutorial
https://www.r-bloggers.com/a-data-table-r-tutorial-by-datacamp-intro-to-dti-j-by/
https://www.r-bloggers.com/elegant-fast-data-manipulation-with-data-table/

Adv: https://www.r-bloggers.com/advanced-tips-and-tricks-with-data-table/
https://www.r-bloggers.com/efficient-aggregation-and-more-using-data-table/
Quick union: https://www.r-bloggers.com/r-quick-tip-collapse-a-lists-of-data-frames-with-data-table/
Aggregation: https://www.r-bloggers.com/two-of-my-favorite-data-table-features/
Binning: https://www.r-bloggers.com/using-data-table-for-binning/

Joining: http://www.magesblog.com/2014/06/the-joy-of-joining-datatables.html#more
Manipulation the fast way: http://www.magesblog.com/2012/11/datatable-rocks-data-manipulation-fast.html#more

Solving common problems: https://www.r-bloggers.com/solve-common-r-problems-efficiently-with-data-table/

Matt Dowle presentations:
http://datascience.la/matt-dowles-data-table-talk-from-user-2014/

Roll:
https://www.r-bloggers.com/understanding-data-table-rolling-joins/

https://www.r-bloggers.com/advanced-tips-and-tricks-with-data-table/

All articles:
https://github.com/Rdatatable/data.table/wiki/Articles

H2O data table?
http://t.co/TYGG6KcMpx
https://www.analyticsvidhya.com/blog/2016/05/h2o-data-table-build-models-large-data-sets/


2. Dplyr by Hadley Wickham... plenty of stuff everywhere (to be elaborated on)

Free books from Gitbook... R stuff.

General: 

Przewodnik po pakiecie, Przemysław Biecek https://pbiecek.gitbooks.io/przewodnik/content/

R for Data Science, Garrett Grolemund Hadley Wickham http://r4ds.had.co.nz/

Programming:
Efficient R programming, Colin Gillespie, Robin Lovelace https://csgillespie.github.io/efficientR/

Advanced R by Hadley Wickham http://adv-r.had.co.nz/

Time series:Analiza i prognozowanie szeregów czasowych – case studies Część I , Adam Zagdański, Artur Suchwałko http://quantup.pl/szeregi-czasowe-case-studies/

Visual analysis:

Geostatystyka w R, Jakub Nowosad (see also presentations pazuR https://nowosad.github.io/presentations/)https://bookdown.org/nowosad/Geostatystyka/eksploracyjna-analiza-danych-przestrzennych.html
Not compiled files for Ggplot2 book by Hadley Wickham: https://github.com/hadley/ggplot2-book

R for Excel users
http://quantup.pl/operacje-w-R-znane-z-Excela/
http://quantup.pl/wykresy-w-R-znane-z-Excela/

Report writing:

Authoring Books with R Markdown, Yihui Xiehttps://bookdown.org/yihui/bookdown/

Data mining:
The caret Package, Max Kuhn  http://topepo.github.io/caret/index.html

Python

PythonPython Data Science Handbook

Jake VanderPlas Soon to be published: https://github.com/jakevdp/PythonDataScienceHandbook/blob/master/README.md

Extract data from many word documents to table.

Best:
http://stackoverflow.com/questions/35850705/extract-select-data-from-multiple-word-files-to-excel

Find specific word, extract data
http://stackoverflow.com/questions/3567441/extract-data-from-word-document-to-an-excel-spreadsheet

http://superuser.com/questions/774734/copy-data-from-multiple-ms-word-files-to-excel-using-vba?noredirect=1&lq=1

Perhaps the data is in form fields?
http://www.techrepublic.com/blog/10-things/10-steps-to-transferring-word-form-data-to-an-excel-sheet/


Not exactly that:
http://stackoverflow.com/questions/7524064/excel-vba-question-need-to-access-data-from-all-excel-files-in-a-directory-wi
http://stackoverflow.com/questions/7531526/need-to-pull-data-from-all-files-in-a-directory
http://stackoverflow.com/questions/22017966/extract-data-from-text-file-and-put-in-excel-file-using-vba
http://www.excelforum.com/l/777970-asdf.html
http://www.dslreports.com/forum/r24300164-Word-50-word-docs-extracted-to-excel-tables

What to do with old smartphone.

1. Surveillance:
Salient Eye (will work with old Androids, good job Israel!)
http://www.salient-eye.com/yey-we-got-some-new-old-smartphones/
http://www.makeuseof.com/tag/use-old-smartphone-tablet-security-camera/
http://www.usatoday.com/story/tech/columnist/komando/2015/01/30/old-phone-camera/22532911/
http://www.haaretz.com/israel-news/business/.premium-1.577834
2. Road cam
http://www.benchmark.pl/testy_i_recenzje/polecane-rejestratory-jazdy-na-androida.html/
3. Internet radio
(can also be done with old Nokias with Symbian and WiFi)

R: data exploration nomad's top reading this month, mlR

Alternative to caret: mlR
To practice: https://www.analyticsvidhya.com/blog/2016/08/practicing-machine-learning-techniques-in-r-with-mlr-package/
Official tutorial: http://mlr-org.github.io/mlr-tutorial/devel/html/


Other resolutions:
Learn R control structures: http://www.statmethods.net/management/controlstructures.html
Learn xgboost bare without the convenience of caret (prepare sparse/dense matrix data, finetune): https://github.com/dmlc/xgboost/blob/master/R-package/vignettes/xgboostPresentation.Rmd
http://xgboost.readthedocs.io/en/latest/R-package/xgboostPresentation.html
Use my own model in train in caret (or learn to put in the grid the parameters not originally supported in caret)
https://github.com/dmlc/xgboost/blob/master/R-package/vignettes/xgboostPresentation.Rmd
Cleaning data, operations on text, imputation of NA's etc !!!

Other:
Time series wanderings:
http://swmprats.net/forum/potm-single-variable-exploration/35-august-potm-time-series-decomposition-with-swmpr


Excel trivia: learn how to find multiple matches:
 https://fiveminutelessons.com/learn-microsoft-excel/use-index-lookup-multiple-values-list
http://stackoverflow.com/questions/26424226/excel-return-multiple-matching-values-from-a-column-horizontally-in-one-row
http://eimagine.com/how-to-return-multiple-match-values-in-excel-using-index-match-or-vlookup/
 

Top reading and exploring on my list. xgboost, ensembles, preprocessing.

1. xgboost - one of Kaggle winner packages, efficient code for home users.
http://www.r-bloggers.com/an-introduction-to-xgboost-r-package/
https://github.com/rachar1/DataAnalysis/blob/master/xgboost_Classification.R
Awesome XGBoost: https://github.com/dmlc/xgboost/blob/master/demo/README.md#features-walkthrough
https://github.com/dmlc/xgboost/blob/master/R-package/vignettes/xgboostPresentation.Rmd
http://wiselily.com/2015/07/12/xgboost-data-mining-example-1/


apply Xgboost in Kaggle with us https://m.youtube.com/watch?v=zwKFyMkvNXE

Tuning parameters:
https://rpubs.com/flyingdisc/practical-machine-learning-xgboost
http://stackoverflow.com/questions/33949735/tuning-xgboost-parameters-in-r
http://www.r-bloggers.com/r-setup-a-grid-search-for-xgboost/
https://www.kaggle.com/c/otto-group-product-classification-challenge/forums/t/14009/script-understanding-xgboost-model-on-otto-dataset
https://www.kaggle.com/tqchen/otto-group-product-classification-challenge/understanding-xgboost-model-on-otto-data/code
http://stats.stackexchange.com/questions/171043/how-to-tune-hyperparameters-of-xgboost-trees
https://www.kaggle.com/c/bnp-paribas-cardif-claims-management/forums/t/19922/how-to-tune-xgboost-using-r
https://www.kaggle.com/forums/f/15/kaggle-forum/t/17120/how-to-tuning-xgboost-in-an-efficient-way
https://www.analyticsvidhya.com/blog/2016/01/xgboost-algorithm-easy-steps/
https://github.com/topepo/caret/issues/336

Plot:
http://rpackages.ianhowson.com/cran/xgboost/man/xgb.plot.tree.html
(off topic, rearange corr) https://drsimonj.svbtle.com/rearrange-your-correlations-with-corrr

 http://www.analyticsvidhya.com/blog/2016/01/xgboost-algorithm-easy-steps/
(also RF: http://www.analyticsvidhya.com/blog/2015/09/random-forest-algorithm-multiple-challenges/)
https://cran.r-project.org/web/packages/xgboost/vignettes/xgboostPresentation.html
https://www.kaggle.com/rajivranjansingh/liberty-mutual-group-property-inspection-prediction/xgboost-in-caret/run/44015/code
https://cran.r-project.org/web/packages/xgboost/vignettes/discoverYourData.html
https://cran.r-project.org/web/packages/xgboost/vignettes/xgboostPresentation.html

2. ensemble learning
http://www.vikparuchuri.com/blog/intro-to-ensemble-learning-in-r/

3. preprocessing
http://topepo.github.io/caret/preprocess.html

4. Another free course:
https://lagunita.stanford.edu/courses/HumanitiesScience/StatLearning/Winter2014/about

5. Deep learning in R resources: https://discuss.analyticsvidhya.com/t/deep-learning-in-r/10349

 Do not read this, My first attempts:

library(xgboost)

mydata<-read.csv(file="clipboard", sep="\t", header=T)

mydata<-sapply(mydata, as.numeric)

#all columns as numeric
mydata<-as.data.frame(lapply(mydata,as.numeric)

#or
mydata<-as.data.frame(sapply(mydata[,1:3], as.numeric))

#label must be 0 or 1 in logistic regression
mydata$Gatunek<-mydata$Gatunek-1


model <- xgboost(data = as.matrix(mydata[,1:2]), label = mydata$Gatunek,
                 nrounds = 2, objective = "binary:logistic")

> install.packages("DiagrammeR")
xgb.plot.tree(model = model)

My NO to Warsaw Uprising 1944 use in current politics.

Am i entitled to take a stand on behalf of AK too? Can I wear their symbols? An armband?

My granddad was in Home Army (Armia Krajowa) in Czortków and then in 2nd People's Army (2 Armia LWP). In Czortków he witnessed Polish scouts, priests, railwaymen, elites murdered by NKVD, with bags over their heads, driven over by a tracked tractor. When the Russians pushed the Germans back, he joined the soviet controlled Polish Army to fight Germans and (this was an order of his superior) avoid arrest from hands of communist Smiersh. I was very proud of his bravery. After the war his children had a pretty hard time from the socialist "democrats". Chances were lost. Interrogated, beaten, he did not reveal his contacts. He was protected against major persecution and death thanks for his brave rescuing of high Soviet Artillery officer in battle. His colleagues were not so lucky...

After the war he could not buy farming equipment without begging commie bureauucrats, writing letters, showing his war medals. Then his children had various small and large problems in education and finding adequate employment.  My childhood in a gray communist world can humbly be added; where would I be now were it not for limitations of "socialist/communist" heaven?

My grandma's father had a big farm, earned with the hard work in Argentine. It was nationalised by commies, a "friendly neighbor" with a red band warned them they would be arrested before dawn and deported to Siberia. "I am preparing these lists, I removed you once, but I cannot do it again. Please go."  All the Poles were to be deported - this was the main criterion.  They loaded a handful of necessities on a cart and fled to the forest. After Germany attacked the Soviets, she ended up in German Concentration Camp near Koenigsberg. She told me she witnessed a girl inmate publically executed for stealing potato peals. They were just teens forced to work for German war effort, work in two shifts. Initially these camps had a regulation for food rationing calculated to exploit a worker and put him or her to death within 3 months but it was inefficient and had to be relaxed. This is the story I learned from her.

I am also entitled to take a stand because I served my free country and I have a mobilization card. If my country is threatened I will not run and say "Sorry Poland" or even "Sorry NATO countries". I will not be happy to take anyone's lives and I am not prejudiced against any nation but I will defend my country.



Personally I seem to have socialist views: poor people should be helped out and not fought against, employment shall not exploit, education and healthcare for all, fair salaries, inequality shall be moderated by giving people chances. But after the XX totalitarian experiment how can I trust anything that has "socialist" in the name or refers to Marxism.


I am not sure I can properly express my anger at current plays of politicians around Warsaw Uprising and AK heroes. And of the media teaching the young that they should not perhaps wear AK/WP armbands or that they are not entitled to commemorate the heroes of Warsaw Uprising. Or perhaps AK was antisemitic. Or it was responsible for suffering of Warsaw civilians. Responsibility is debatable, but is there a sincere debate without arguments copied verbose from  propagandists serving Stalinist or Nazi regimes?
I am sure of what my grandma would say but it would be an extremely explicit language, I will use Orwell's precise words then:

First of all, a message to [...] left-wing journalists and intellectuals generally: ‘Do remember that dishonesty and cowardice always have to be paid for. Don’t imagine that for years on end you can make yourself the boot-licking propagandist of the Soviet régime, or any other régime, and then suddenly return to mental decency. Once a whore, always a whore.’ 

George Orwell "As I Please", 1 Sep. 1944

http://www.telelib.com/authors/O/OrwellGeorge/essay/tribune/AsIPlease19440901.html

Sorry for the language, this is one of the days I should quote it.

Open Intro - free introductory statistics handbook.

Beautiful project, free books that can be used with free R and R studio software, suitable for highschools and colleges: https://www.openintro.org/stat/textbook.php?stat_book=os

Book for holidays for Data Miner?

The story of Bayesian theory and its applications? Sharon Bertsch McGrayne: "The Theory That Would Not Die" | Talks at Google .

Villard noise cancelling shortwave antenna loop demo - Don't quit SWL ov...

Not only I tried it :-) My attempt: https://www.youtube.com/watch?v=qGipnTk-fyk&feature=youtu.be&a=



 I wonder if it can also transmit. I know about high voltage between the capacitor created by overlapping foil. Would it withstand 3W of power from a CW homemade QRP transceiver on 7MHz?

Here a transmitting loop made of copper tube. https://www.youtube.com/watch?v=Cv_RnLpZ9gw
Note the use of coax to form a capacitor and tune the antenna. In fact the entire antenna can be made just from the coax.

Another solution: http://www.rfa.org/english/about-old/help/Anti-Jamming.html?searchterm:utf8:ustring=antenna

R: Braindump for this week: classification, ETL, etc.

1. I am pondering about the use of clustering to classify unknown data.

• first prepare data (what to do with categorical, ordinal?, mutate - create new variables?)
• for this i found some interesting tutorials:
  http://www.r-bloggers.com/clustering-mixed-data-types-in-r-2/
  http://www.sthda.com/english/wiki/partitioning-cluster-analysis-quick-start-guide-unsupervised-machine-learning
• I also found an algorhitm that proved to be very precise:
• then run through a clustering algorytm (k-means, local density, other?)
• first I chose to play with various algorythms to see how they detect known clusters
  eg. the three species of famous iris database, I played with "cclust", "cluster", and "densityclust" and discovered "factoextra"for quick graphs/diagnostics
• finally throw out a decision tree (or other non-black box tool to show rules and dependencies).

2. Getting out tidy data: broom (in .r documents), pander (in .Rmd documents), memisc:mtable + pander to compare linear models (http://stackoverflow.com/questions/24342162/regression-tables-in-markdown-format-for-flexible-use-in-r-markdown-v2)

3. ETL concept:
https://cran.r-project.org/web/packages/dplyr/vignettes/databases.html
https://github.com/beanumber/etl
http://www.r-bloggers.com/r-and-sqlite-part-1/

Free books: Exploring Data Science by John Mount and Nina Zumel

https://dzone.com/articles/practical-data-science-with-r

TED: Peter donnelly shows how stats fool juries

Especially starting from 11th minute... reminds me about the need to review the measures of the model: specificity, sensitivity... more here: https://en.wikipedia.org/wiki/Sensitivity_and_specificity

xgboost, caret, cleaning, time series - a reading list for next weeks.

Go through this to learn xgboost:
https://github.com/dmlc/xgboost/blob/master/R-package/vignettes/discoverYourData.Rmd
https://cran.r-project.org/web/packages/xgboost/vignettes/discoverYourData.html
https://github.com/dmlc/xgboost/blob/master/R-package/vignettes/xgboostPresentation.Rmd

https://github.com/dmlc/xgboost/tree/master/R-package/demo
https://rpubs.com/flyingdisc/practical-machine-learning-xgboost

http://courseprojects.souravsengupta.com/tag/xgboost/
http://xgboost.readthedocs.io/en/latest/R-package/xgboostPresentation.html
https://www.kaggle.com/jkapila/caterpillar-tube-pricing/0-24-with-xgboost-in-r/run/29813
http://www.analyticsvidhya.com/blog/2016/01/xgboost-algorithm-easy-steps/

Material from recent meetup in Warsaw:
https://github.com/mi2-warsaw/SER/blob/master/SER_XIX/xgboost.R


Cleaning and  some interesting packages (editrules, deducorrect):
https://cran.r-project.org/doc/contrib/de_Jonge+van_der_Loo-Introduction_to_data_cleaning_with_R.pdf
http://www.r-bloggers.com/three-quick-and-simple-data-cleaning-helper-functions-december-2013/
http://www.meetup.com/amst-R-dam/events/57161682/ and especially:
http://cran.r-project.org/web/packages/editrules/index.html
http://cran.r-project.org/web/packages/deducorrect/index.html
Very good examples: https://github.com/data-cleaning
 http://www.r-bloggers.com/deductive-imputation-with-the-deducorrect-package/

Time Series:
http://www.r-bloggers.com/time-series-analysis-and-mining-with-r/

Ensemble:
https://cran.r-project.org/web/packages/caretEnsemble/vignettes/caretEnsemble-intro.html

Caret: (very good stuff): https://github.com/pbiecek/DataMining/tree/master/MINI_2015


Other:  
http://www.datasciencecentral.com/m/blogpost?id=6448529%3ABlogPost%3A352821

Github new tools (also offline):
Write a book:  
https://www.gitbook.com/
Collect and share snippets of code:
https://gist.github.com/


 

Prośba o głos na projekty dot. szkoły podstawowej SP312

 Rodzice i dzieci SP312 (Warszawa Gocław)  proszą o głosy. Można głosować na kilka projektów!Najłatwiej przez internet: https://app.twojbudzet.um.warszawa.pl/2017/voting/voter

DZIELNICA: PRAGA POŁUDNIE OBSZAR: OGÓLNODZIELNICOWE
NR 22 BEZPIECZEŃSTWO DZIECI W DRODZE DO SZKOŁY SP312, W JEJ OTOCZENIU
I NA PRZYSZKOLNYCH BOISKACH. (Projektodawca: Jacek Kotowski)-185.400zł
Modernizacja monitoringu szkoły i doświetlenie jej otoczenia i
podłączenie do systemu miejskiego (Straż Miejska/Policja).
To jest projekt ogólnodzielnicowy, głos nie wpływa na projekty dot. dzielnicy Praga Południe.
===================
DZIELNICA: PRAGA POŁUDNIE OBSZAR: GOCŁAW
NR 4 NAUKA I ZABAWA - PRACOWNIE MULTIMEDIALNE W SP NR 312 I WARSZTATY
UMIEJĘTNOŚCI RODZICIELSKICH - 42.700ZŁ
Projekt polega na wyposażeniu trzech sal lekcyjnych Szkoły Podstawowej
nr 312 w zestawy multimedialne. Tablica interaktywna, projektor, laptop.
Zestawy multimedialne będą także wykorzystywane podczas spotkań z
rodzicami i prowadzonych na terenie SP nr 312 warsztatów umiejętności
rodzicielskich otwartych dla wszystkich rodziców z Gocławia. Będzie
to cykl 10 spotkań warsztatowych.
NR 13 REMONT PLACU ZABAW PRZY UL. UMIŃSKIEGO 12, PRZY SP NR 312,
OGÓLNODOSTĘPNEGO POPOŁUDNIAMI I W WEEKENDY. - 192.500ZŁ
Projekt polega na wymianie obecnych urządzeń na placu zabaw i
dokupieniu nowych. Projekt obejmuje również wymianę nawierzchni placu
zabaw z piaskowej na poliuretanową.
NR 16 BEZPŁATNE ZAJĘCIA JĘZYKA ANGIELSKIEGO DLA DZIECI Z GOCŁAWIA
WRAZ Z ODNOWIENIEM I DOPOSAŻENIEM SAL SP 312 - 76.220ZŁ
Projekt obejmuje przeprowadzenie kursu języka angielskiego dla 8 grup
12 osobowych w wymiarze 64 godzin w ciągu roku, doposażenie 4 sal SP
312 w sprzęt multimedialny (rzutnik, ekran, laptop) oraz remont 4 sal
(cyklinowanie podłóg).
NR 5 ŚCIEŻKA PRZYRODNICZA WZDŁUŻ KANAŁKU GOCŁAWSKIEGO NA ODCINKU
UL. FIELDORFA - UL. RECHNIEWSKIEGO. - 187.200ZŁ
Projekt polega na ustawieniu drewnianych tablic edukacyjnych i
światowidów interaktywnych wzdłuż północnej strony kanałku na
odcinku Ul. Fieldorfa - ul. Rechniewskiego. Dodatkowo projekt obejmuje
nasadzenie ozdobnej roślinności wodnolubnej wzdłuż kanałku na w/w
odcinku oraz remont chodnika.

JAK ZAGŁOSOWAĆ?
KROK 1 Wybierz dzielnicę głosowania - Praga Południe. Można
głosować tylko w jednej dowolnej dzielnicy, nie koniecznie tam gdzie
się mieszka. Głosować mogą tylko osoby mieszkające w Warszawie (nie
trzeba być jednak zameldowanym, nie trzeba być mieszkańcem Pragi
Południe). W głosowaniu może wziąć udział każdy mieszkaniec
Warszawy bez względu na wiek. Również dzieci! opiekun prawny
nieletnich musi wypełnić formularz zgody.
KROK 2 Wybierz obszar - Gocław. Dodatkowo, niezależnie od wybranego
(jednego!) obszaru każdy głosujący na Pradze Południe będzie mógł
również zagłosować na projekty ogólnodzielnicowe - można
zagłosować na obu poziomach i na projekty lokalne i na projekty
ogólnodzielnicowe.
KROK 3 Wybierz projekty. Głosując, można wybrać DOWOLNĄ LICZBĘ
PROJEKTÓW, ale trzeba pamiętać, że łączny koszt realizacji
wszystkich wybranych przez Ciebie projektów nie może być większy
niż kwota dostępna w wybranym obszarze i na poziomie
ogólnodzielnicowym (w przeciwnym wypadku Twój głos zostanie policzony
jako nieważny). Kwota na projekty ogólnodzielnicowe na Pradze
Południe to 446 829 zł , kwota na obszar Gocław to 969 245 zł W
NASZYM PRZYPADKU MOŻNA WIĘC GŁOSOWAĆ NA WSZYSTKIE SZKOLNE PROJEKTY.
KROK 4 ZAGŁOSUJ od 14 czerwca do 24 czerwca 2016 r. możesz to zrobić
na kilka sposobów:
1. Najłatwiej jest zagłosować przez Internet poprzez stronę
www.twojbudzet.um.warszawa.pl Należy posiadać adres e-mailowy. Należy
potwierdzić głosowanie w otrzymanym e-mailu potwierdzającym
zagłosowanie przez internet. Inaczej głos będzie nie ważny.
2. Zagłosować można również papierowo przychodząc do wybranego
urzędu dzielnicy (w godzinach jego pracy, pomiędzy 14 a 24 czerwca) i
wrzucając do urny wypełnioną kartę do głosowania.
KROK 5 Namów znajomych i sąsiadów aby również zagłosowali na
szkolne projekty SP 312. Wydrukuj plakat (załącznik i powieś u siebie
w bloku)

Weekend... Warsaw dream.

and and

LWYT - I saw you Poland

2 minutes and all that should be said about Poland seen by a turist.
Madrecita Filmes from Brasil!

Marriage of Data Science (R, Python) with ham radio.

How about building a statistical machine learning model in R on in Python, that decodes Morse code.
If it is possible to teach machines identify whales based on very poor images of patterns on their backs with commonly available library in Python, why not try decode CW transmission from noisy, distorted samples.
It has been tried already in two Kaggle competitions:

Google: morse machine learning kaggle  for more :-)

It may be a good project for high-school intro to signal processing and data mining!

Excel interfacing R via RScript. (draft)

I played a bit, building a trifle application in Excel using R to explore weather data. My purpose was to make the code portable, with relative links - all files in one folder.

Here is my excel VBA code for exchanging data with R. It starts an R script and then reads back a txt file with numerical results and three plots as svg images.

Sub RunRscript2()
Dim shell As Object: Set shell = VBA.CreateObject("WScript.Shell")
Dim waitOnReturn As Boolean: waitOnReturn = True
Dim windowStyle As Integer: windowStyle = 1
Dim errorCode As Long
Dim sFile As String


'Cleanup
ThisWorkbook.Save
Worksheets("Sheet1").Activate
On Error Resume Next
Worksheets("Sheet1").QueryTables("output1").Delete
On Error GoTo 0
Worksheets("Sheet1").Pictures.Delete
'=========

sFile = ThisWorkbook.Path & "\test_xls_connectivity.R"

errorCode = shell.Run("C:\R\R-3.3.0\bin\x64\Rscript.exe " & sFile & "", windowStyle, waitOnReturn)
'errorCode = shell.Run("Rscript.exe " & sFile & "", windowStyle, waitOnReturn)

Worksheets("Sheet1").Range("$F$3").Select
sFile = ThisWorkbook.Path & "\plots.png"

ActiveSheet.Pictures.Insert(sFile).Select

    With Selection.ShapeRange.Line
        .Visible = msoTrue
        .ForeColor.ObjectThemeColor = msoThemeColorText1
        .ForeColor.TintAndShade = 0
        .ForeColor.Brightness = 0
        .Transparency = 0
    End With
Worksheets("Sheet1").Range("$F$30").Select
sFile2 = ThisWorkbook.Path & "\plots2.png"

ActiveSheet.Pictures.Insert(sFile2).Select

    With Selection.ShapeRange.Line
        .Visible = msoTrue
        .ForeColor.ObjectThemeColor = msoThemeColorText1
        .ForeColor.TintAndShade = 0
        .ForeColor.Brightness = 0
        .Transparency = 0
    End With

Worksheets("Sheet1").Range("$N$3").Select

sFile3 = ThisWorkbook.Path & "\plots3.png"


ActiveSheet.Pictures.Insert(sFile3).Select

    With Selection.ShapeRange.Line
        .Visible = msoTrue
        .ForeColor.ObjectThemeColor = msoThemeColorText1
        .ForeColor.TintAndShade = 0
        .ForeColor.Brightness = 0
        .Transparency = 0
    End With

'''''''''''''''''''''''''
strFile = ThisWorkbook.Path & "\output.csv"

    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;" & strFile & "", Destination:=Range("$N$30"))
        .Name = "output1"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlOverwriteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 1252
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = True
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With


End Sub

And here is my R file which reads data from excel and then returns plots as images and reports in text format that VBA will paste in an Excel sheet. The commented install.packages need to be run once in R (maybe it needs to be corrected so that if the packages are not installed R should install them). Note the args... and then scriptPath... piece of the code which reads the path to the current location of the script. Not my solution - I will attach the source soon, sorry and thanks to the original author.

# install.packages("xlsx")
# install.packages("rattle")
# install.packages("RColorBrewer")
# install.packages("rpart.plot")
# install.packages("scatterplot3d")
require(xlsx)
require(rpart)
# require(rattle) 
require(rpart.plot)
require(ggplot2)
require(scatterplot3d)

rm(list=ls())


args <- commandArgs(trailingOnly = F)  
scriptPath <- normalizePath(dirname(sub("^--file=", "", args[grep("^--file=", args)])))
setwd(scriptPath)
# setwd("C:\\Users\\jkotows2\\Desktop\\testR_xlsm")


#read from Sheet1
DaneBadawcze = read.xlsx("test_R.xlsm",1, encoding = "UTF-8", stringsAsFactors=T)

attach(DaneBadawcze)

fit <- rpart(Padało ~ Temperatura + Ciśnienie + Wilgotność,
             ,data=DaneBadawcze, method= "class")
#cluster<-cutree(hclust(dist(DaneBadawcze[,names(DaneBadawcze)!="Padało"]) ),k = 4)

cluster<-cutree(hclust(dist(DaneBadawcze) ),k = 4)

DaneBadawcze$Klaster<-cluster


#print a plot to a file


fn <- "plots.png"
if (file.exists(fn)) file.remove(fn)

png(fn)
#fancyRpartPlot(fit)
prp(fit,type = 2, fallen.leaves = T, extra = 3, compress = T)
dev.off()

#print another plot to a file
fn <- "plots2.png"
if (file.exists(fn)) file.remove(fn)
png(fn)
scatterplot3d(type="h", x=Temperatura, y=Ciśnienie, z=Wilgotność,color=c("green","blue") [DaneBadawcze$Padało] )
dev.off()

#print another plot to a file
fn <- "plots3.png"
if (file.exists(fn)) file.remove(fn)

png(fn)
scatterplot3d(type="h", x=Temperatura, y=Ciśnienie, z=Wilgotność,color=c("green","blue","red","brown","orange") [DaneBadawcze$Klaster] )
dev.off()


write.csv2(fit$variable.importance, file="output.csv", row.names = T)

#print a report to a file
sink(file="report.txt",  type="output")
  fit$variable.importance

  unlink("report.txt")
sink()

To be continued.

5graders learning R / R w 5tej klasie.

Read this: https://benaracreations2015.shinyapps.io/gr5project/
Implement the program in Polish schools!!!
(Found on Twitter @StatGarrett and  @hadleywickham)

RStudio Addins

I am not so fast at memorising/coding... I need parallel code/(some)wysiwyg development. And my memory fails me, I need snippets management... Maybe addins will help soon. Newest RStudio offers addins functionality. Here are some:

https://github.com/daattali/addinslist#addinslist-table

Coffee break: my 3 absolute top apps for work (Windows) in 30 seconds.

Absolute top productivity tools:

R - and R studio. You can do data exploration with it (compare it to SAS, SPSS, Statistica)
Cmap Tools - brainstorm, draw concept maps and then generate an orderly txt outline
Wikidpad - collect your notes, press snippets, cross-link your knowledge.

Syria: zmuszeni do ucieczki/ forced to flee | PAH |

If we are afraid refugees militants already in EU, why not bring in families direct from War zone. Casualties, those in need. Be the first in EU to do it right.
If we are afraid of Sunnite radical Muslims, why not bring in Christians first who are in most danger from IS radicals. If we are afraid of radical Islam, Wahabites, why not forbid their practice altogether in Poland. Sorry we are not that tolerant that we should risk losing our heads.
If we are afraid of ghettos, why not evenly distribute refugees...
If we do not have people for security screening, why not involve Syrians living in Poland in our sec forces?

Solve problems. Inform the public. Talk. Act.


Refugees (for your coffee break). Scene from Baraka. Music by Dead Can Dance.

Coffee break: Plotly in Excel or Powerpoint? H2O on local cloud?

Can interactive R/ggplot2 plots be used in PowerPoint via plotly? To be tested at home with this Office2013 plugin: https://store.office.com/plotly-d3-js-charts-for-powerpoint-and-excel-WA104379485.aspx?assetid=WA104379485

I need some H2O to survive. (multicomputer data mining at home?, try deep learning?)
http://docs.h2o.ai/h2oclassic/deployment/multinode.html
http://www.kdnuggets.com/2015/01/interview-arno-candel-h20-deep-learning.html
https://tagteam.harvard.edu/hub_feeds/1981/feed_items/1369576


R in robotics? http://www.bnosac.be/index.php/blog/39-using-r-in-robotics-applications-with-ros http://wiki.ros.org/Robots

Coffee Break:Ensembles

Ensembles:
http://machinelearningmastery.com/machine-learning-ensembles-with-r/
http://amunategui.github.io/blending-models/
http://blog.revolutionanalytics.com/2014/04/ensemble-packages-in-r.html
http://www.overkillanalytics.net/more-is-always-better-the-power-of-simple-ensembles/
http://www.r-bloggers.com/caretensemble-classification-example/

http://www.vikparuchuri.com/blog/intro-to-ensemble-learning-in-r/

https://inclass.kaggle.com/c/15-071x-the-analytics-edge-summer-2015/forums/t/15386/has-anyone-tried-model-stacking/86164#post86164

https://github.com/ujjwalkarn/Machine-Learning-Tutorials/blob/master/README.md

For beginners:
http://www.r-bloggers.com/user2013-the-caret-tutorial/
http://www.sharpsightlabs.com/quick-introduction-machine-learning-r-caret/
 http://www.sharpsightlabs.com/dplyr-intro-data-manipulation-with-r/
http://www.sharpsightlabs.com/data-analysis-example-r-supercars-part2/

http://www.computerworld.com/article/2486425/business-intelligence/business-intelligence-4-data-wrangling-tasks-in-r-for-advanced-beginners.html
http://www.computerworld.com/article/2884322/app-development/learn-r-programming-basics-with-our-pdf.html#tk.ctw-eos

Forecasting:
https://www.otexts.org/fpp/using-r

Geospatial
http://www.maths.lancs.ac.uk/~rowlings/Teaching/UseR2012/cheatsheet.html
https://github.com/Robinlovelace/Creating-maps-in-R

GGplot snippets:
http://www.computerworld.com/article/2936729/data-analytics/free-download-save-r-data-visualization-time-with-these-ggplot2-code-snippets.html

Best packages/commands:
http://www.computerworld.com/article/2921176/business-intelligence/great-r-packages-for-data-import-wrangling-visualization.html
http://www.personality-project.org/r/r.commands.html

2 minutes tutorials:
http://www.twotorials.com/
http://courses.had.co.nz/

R-Meetups. New superpower in the making.

In the world: http://blog.revolutionanalytics.com/2015/06/r-user-groups-are-everywhere.html
In Poland, I attended one yesterday in Warsaw. Eye opener:  deep learning (meeting with winners of Kaggle Competition), H2O and MS Visual Studio R addin. http://www.meetup.com/Spotkania-Entuzjastow-R-Warsaw-R-Users-Group-Meetup/events/228608144/

My R coffee break...R in Visual Studio, updateR, free R stuff.

Microsoft mimics RStudio in Visual Studio?
https://www.visualstudio.com/en-us/features/rtvs-vs.aspx


Easy update R to new version with installr: may help automate lab IT work:
http://www.r-statistics.com/2013/03/updating-r-from-r-on-windows-using-the-installr-package/

Interesting resource: Online Open Access Textbooks, here on forecasting: https://www.otexts.org/fpp/data

Wow: Hadley Wickham: R for data science book in the making. http://r4ds.had.co.nz/

SQL with VBS when data too big for Excel

I will use when data is too big for Excel, to quickly filter, aggregate or leftjoin data. Excel is not needed. It may be a poor-man, MSWindows (>=7.0v, it may work with older v. and an older Access runtime) solution for data too big for Excel.

1. Install Microsoft Access Runtime, the newest is 2013 https://www.microsoft.com/nl-nl/download/details.aspx?id=39358

2. Put in the same foldar: your initial data in a text file (csv) and an empty output file (csv) with one row reflecting all column names that should be in a result of a query.

3. Prepare a Schema.ini file: describe the two csv files in the following format (an example):

[PhoneList.csv]
ColNameHeader=True
Format=Delimited(;)
DateTimeFormat=yyyy-mm-dd
MaxScanRows=25
CharacterSet=ANSI
Col1=Surname Char Width 10
Col2=Name Char Width 10
Col3=No Long Width 10


[exp.csv]
ColNameHeader=True
Format=Delimited(;)
Col1=Surname Char Width 10
Col2=Name Char Width 10
Col3=No Long Width 10

4. Prepare a vbs file: it should contain the following code (an example):

Dim db: db = CreateObject("Scripting.FileSystemObject").GetParentFolderName(WScript.ScriptFullName)

Dim cn: Set cn = CreateObject("ADODB.Connection")

cn.Open _
    "Provider=Microsoft.ACE.OLEDB.15.0;" & _
          "Data Source=" & db & ";" & _
          "Extended Properties=""text;HDR=YES;FMT=Delimited(;)"""


cn.Execute "INSERT INTO [exp.csv] SELECT [Surname],[Name],[No] FROM [PhoneList.csv] WHERE [No] > -20"

cn.Close

5. The environment should be 32 or 64 bit, not a mix. Unfortunately I have 64 bit system i and office 32 bit therefore I am using a workaround tostart a script in 32 bit mode:

c:\windows\syswow64\cscript.exe moj_skrypt.vbs

Result: I tried the script on a 1 mln records csv file and it took 2 s to return the filtered data in the output csv.

Note to myself and others: Here you will find an alternative solution with R and the test data 2,6 mln records - R and ff package. For advanced jobs there is a superb idea of using ff with dplyr (ffbase2). I need to test it. http://www.r-bloggers.com/if-you-are-into-large-data-and-work-a-lot-with-package-ff/

BIQdata - a public data exploration portal by Gazeta Wyborcza.

I am not a subscriber, therefore I cannot see how well this idea is accomplished. I think that not providing a trial period or some free content is a mistake. But I hope the service will motivate all other information providers to copy the idea and  build data exploration sections or have at least one data exploration guy in the newsroom.
Data exploration tool can be dangerous in the hands of an investigative journalist. It can show in an objective way, which public tenders are suspicious or where government action against crime and low quality of life is insufficient.

If your budgets are tight, invest in an R guy and use markdown. I will be happy to see journalists using data mining tools to explore public databases and present (and validate) results of research in an informative way.

Open Intro: free introductory statistics handbook, videos plus R lab files.

https://www.openintro.org/ - my recommendation for today.

caret::preprocess, how to see the results.

Being a notorious beginner I wondered how to see the results of preprocessing, (normal-izing, scaling, centering) my data with caret package in R. I typed some data in Excel and played with it in R Studio.I could not figure out at first where the results of preProcess function are. It appears they must be processed with "predict".

library(caret)
x<-read.table(file="clipboard",dec=",",header=T)
x
#preProcValues<-scale(x,center = T, scale=T) - just a note on a built in function in base R

#here i build a "model", a preprocessing object or model

preProcObject<-preProcess(x, method = c("center", "scale","YeoJohnson"))

#to see the results I must apply it
Data_PreProc <- predict(preProcObject, x)



#I build a table to see source data and processed data side by side.
Output<-cbind("input"=x$data,"output"=Data_PreProc$data)

Output
plot(Output)

List do Prezydent Wwy w sprawie przejścia przy SP312

 (wcześniej wysłane e-mailem bez potw. dziś wysyłam poleconym)

Pani Prezydent m.st. Warszawy
Hanna Gronkiewicz Waltz


Prośba o interwencję i osobisty nadzór Pani Prezydent.


Dotyczy: Niebezpiecznych przejść dla pieszych przy ul. Umińskiego między SP 312 a Gimnazjum i osiedlem Wilga Iskra oraz bezpieczeństwa dzieci wokół szkoły SP 312 i na terenie boisk dewastowanych w nocy.



Zwracam się do Pani z ogromną prośbą o osobistą interwencję i nadzór nad zapewnieniem bezpieczeństwa dzieci w otoczeniu Szkoły Podstawowej SP312 (ponad 1300 dzieci) i prowadzących do niej przejść dla pieszych przy ul Umińskiego (między Szkołą Podstawową a Gimnazjum, żłobkiem i kilkoma przedszkolami.

Proszę również o osobisty Pani nadzór nad projektem partycypacyjnym
688 - Bezpieczeństwo dzieci w drodze do szkoły SP312, w jej otoczeniu i na przyszkolnych boiskach. https://app.twojbudzet.um.warszawa.pl/2017/projekt/2328 , który spowodowałby poprawę jakościową korespondencji i informacji zwrotnej od wszystkich interesariuszy projektu (przede wszystkim Zarządu Dróg Miejskich, Wydziałów Infrastruktury by móc doprecyzować projekt)



1. Przejścia dla pieszych przy ul Umińskiego przy SP312 prowadzą przez dwupasmową drogę o dużym natężeniu i są bardzo niebezpieczne. Dochodzi na nich do poważnych wypadków, w których koszty leczenia na pewno przewyższają instalację oświetleń i przebudowę wymuszającą spowolnienie ruchu drogowego. Ostatni bardzo poważny wypadek potrącenia dziecka na przejściu miał miejsce w zeszłym tygodniu i został odnotowany przez media i polityków. Szkoła i mieszkańcy sygnalizują potrzebę zabezpieczenia przejść od wielu lat.

W komunikacji z UM dowiedziałem się, że pomiary i ilość wypadków nie wskazuje na konieczność instalacji ograniczającej ruch i zapewniającej bezpieczeństwo dzieciom. Zarzucałem tym pomiarom  nierzetelność: nieprawidłowe wnioski (małe natężenie i prędkość wynikające ze statystyk faktycznie, że znaczna część kierowców przekracza na tych przejściach prędkość - nieumiejętność interpretacji liczb). Badania te przeprowadzono jeszcze przed ukończeniem osiedli przy Promenadzie  i uruchomieniem ruchu na trasie Siekierkowskiej.
Wydział Zarządzania Kryzysowego UM informował mnie, że przeprowadzone zostaną nowe badania. Rajski Chciałem być obecny podczas badań w 2014/2015 - niestety przeprowadzono je nie informując mnie o nich.

W 2014 po sporze ze strażą miejską nt. prawidłowości parkowania - na łuku drogi samochody parkowały na chodniku przy samym przejściu uniemożliwiając nawet dorosłym ustrzeżenie się przed nadjeżdżającymi samochodami - postawiono jedynie słupki uniemożliwiające parkowanie.

Oba przejścia przez dwupasmową drogę ul Umińskiego zdecydowanie wymagają instalacji sygnalizacji świetlnej, wybudowania wysepek lub innych konstrukcji wymuszających zmniejszenie prędkości, lepszego oświetlenia oraz oznakowania, a także ograniczenia prędkości.


2. Teren boisk przyszkolnych (SP312) placów zabaw, skwerków w obszarze ograniczonym przez Szkołę, kilka przedszkoli po obu stronach Umińskiego, żłobka (teren od pasażu przy "kanałku" do os Wilga Iskra i ul. Komorowskiego) - brak nadzoru policji i straży miejskiej w godzinach nocnych nad boiskami i skwerkami w tym obszarze prowadzi do niszczenia infrastruktury i zagrożenia dzieci (narażenia na obecność pijącej i zażywającej narkotyki młodzieży, która czuje się na tym terenie bezkarnie, niszczenia, zaśmiecania szkłem i niedopałkami nawierzchni tartanowej boisk, niszczenia trybun, fasad budynków, rozrzucania szkła. W pobliżu infrastruktury szkolnej i placów zabaw funkcjonują liczne sklepy posiadające licencję na całodobową sprzedaż alkoholu, punkty gier hazardowych. Teren Wilgi Iskry, teren wokół szkoły SP312, place zabaw po każdej nocy wyglądają jak po wielkiej alkoholowej masowej imprezie.

SP312: Na terenie boisk przyszkolnych zainstalowany jest mało sprawny monitoring, teren jest niedoświetlony. Między boiskami a kortami tenisowymi stale przebywa pijąca alkohol (prawdopodobnie spożywająca narkotyki widziałem woreczki przekazywane przez bramę) i agresywna młodzież. Straż miejska i policja często odmawia interwencji lub ta interwencja jest mało skuteczna a zgłaszający boją się konsekwencji. Woźny każdego ranka zbiera ok 50 butelek po wódce i piwie z ter. szkoły. Jest bezsilny.

Teren boisk, teren wokół szkoły SP312, wymaga modernizacji monitoringu, doświetlenia. Konieczne jest też opracowanie procedury interwencji policji/straży miejskiej na wezwanie Woźnego obsługującego w nocy monitoring, patrolowania terenu wokół SP312.

Jeżeli liczą się koszty, to koszty tylko jednego wypadku, leczenia, rekonwalescencji   są dużo wyższe niż dostosowanie przejść. A koszty dewastacji infrastruktury Szkoły SP312, placów zabaw, skwerków w obszarze Szkoły ale i pobliskiego Gimnazjum, kilku przedszkoli i żłobka - wychowania i zabawy dzieci w zniszczonym i zaśmieconym otoczeniu dużo wyższe niż podatki z kilkudziesięciu koncesji na alkohol w bezp. bliskości infrastruktury szkolnej.

Zgłaszałem sprawę niebezpiecznych przejść i dewastacji boisk  od kilku lat (zał przykładowa korespondencja) i czuję się bezsilny.  Wiem też, że w tej sprawie interweniowała wielokrotnie dyrekcja szkoły SP312 oraz inni rodzice. Zgłosiłem projekt partycypacyjny i proszę Pani urzędników o pomoc w jego prawidłowym uzupełnieniu. Wzywam Panią Prezydent do interwencji i nadzoru.



Pozdrawiam serdecznie,

Jacek Kotowski

VBA: Simple and excellent example of using R from Excel

Acomed Statistik from Leipzig! Thanks for this very simple example of using R from Excel with VBA - here to calculate data for a kernel density plot. I recommend studying this simple example and will certainly try to adapt it to my needs. http://www.acomed-statistics.com/simple-interface-r-excel_engl.html

Another interesting tutorial to show how to drive R scripts from Excel. http://shashiasrblog.blogspot.com/2013/10/vba-front-end-for-r.html

The quickest setup for R available in Excel (UDF's) functions working on Excel ranges, and the easiest one: http://bert-toolkit.com/
eg. I wrote these in BERT config file (a file in R format) to get clustering functionality

KMEANS_CLUST <- function( mat, ncenters ){ kmeans(mat,centers = ncenters) }


HTREE_CLUST<- function ( mat, cutheight ) {
    myclust<-hclust(dist(mat));
    myclust<-cutree(myclust,h=cutheight);             
    return(myclust)
    }
---

A big idea for kid's math education.

Teach kids statistics? One hour that may decide about their whole life's success measured in their ability to understand, to research, to choose? And learn from them. Understand your limitations. Take an ultimate Uni exam.  Absolutely mind-blowing experience of a project. "Statistics for 8-9-yr. olds or can you weigh a dog with a ruller?" http://smarterpoland.pl/index.php/2016/03/statystyka-dla-8-9-latkow-czyli-jak-zwazyc-psa-linijka/#comment-144319

What next? Here is one of the organizers Przemysław Biecek: https://www.youtube.com/watch?v=UEIcsVhFaSs, pity that it is addressed to Polish audience.You would be enchanted.

What next? R package for kids? Data exploration/machine learning for kids? It is already thought about:
https://github.com/pbiecek/BetaBit

Go SmarterPoland!

The last attachment... Ostatni załącznik... (protest rodziców na Gocławiu)

"Wczoraj złożyliśmy ostatni załącznik do podania o prawidłowe zabezpieczenie niebezpiecznego przejścia dla pieszych do Szkoły Podstawowej.". Smutne.Dopiero wypadek, potrącenie dziecka przez kierowcę BMW wymijającego inne auto ustępujące pieszym miejsca - porusza władze i szukające sensacji (bo tylko tym były zainteresowane) media. Musi stać się coś strasznego, wypadek, by obudzić biurokratów i media.

Osobiście postulowałem zabezpieczenie tego przejścia i terenu w pobliżu Szkoły Podstawowej SP312 od kilku lat. Oto jeden z listów do urzędnika Ratusza w odpowiedzi na (moim zdaniem) nierzetelne badania i niechęć do zajęcia się sprawą bezpieczeństwa dzieci w drodze do szkoły z 27 paźdzernika 2014. Korespondencji jest więcej, była wizja lokalna, udało się tylko wymusić słupki by samochody nie parkowały na chodniku przy samym znaku przejścia dla pieszych. Ale zrobiłem za mało. Czuję się odpowiedzialny. A Wy urzędnicy, dziennikarze szukający sensacji a nie faktów, kierowcy wygrażający ze swoich samochodów, coś czujecie? Jakąś odpowiedzialność?

===== Jacek Kotowski 10/27/14 to , Urzad M.St. Wwy, Cc sp312, komenda... Burmistrz===

Szanowny Panie,

Dziękuję za informację.

--badania zarządu dróg---

Pismo dotyczy przejścia przez drogę dwupasmową. Poprzednie badanie, którego fragment otrzymałem od Państwa informuje (zawarte w nich liczby, nie wnioski), że co 6-7 kierowca przekracza znacznie (powyżej 67km/h)  prędkość. Kiedy zostało przeprowadzone nowe badanie przez Zarząd Dróg? Jaka jest jego treść. Jaką metodologię pomiaru zastosowano?

Wspomniany przez Pana w piśmie "Pan Stopek" codziennie (dosłownie!) ryzykuje życie przeprowadzając dzieci przez to przejście. Wspomina Pan w piśmie że kierowcy zatrzymują się widząc przechodniów. Z racji tego, że droga jest dwupasmowa, inni kierowcy często usiłują [wyprzedzać] tych, którzy zwolnili, by przepuścić pieszego. To, że nie dochodzi tam do wypadków wiąże się z dużym poczuciem zagrożenia i nieufnością przechodzących. Kierowcy traktują tę drogę rano jak drogę szybkiego ruchu, rozmawiają przez telefon spiesząc się do pracy. Dla postronnego obserwatora jest to przejście bardzo niebezpieczne. Chętnie umówię się z Panem na wizję lokalną, by mógł pan stwierdzić, jak jest naprawdę.

Nie przyjmuję do wiadomości, że plany inwestycyjne są bardzo napięte. Nie będę wchodzić w szczegóły polityczne finansowania przez miasto wątpliwych tych czy innych  instalacji artystycznych, dla mnie są one oczywistą manifestacją, że miasto wydaje chętnie pieniądze podatników na wywoływanie i kreowanie nierozstrzygalnych konfliktów ideowych a nie na bezpieczeństwo dzieci ... Ponadto miasto w ostatnim czasie miało np. pieniądze na wybudowanie już drugiej plenerowej siłowni przy jeziorku Balaton na Gocławiu i zapraszało do rozdysponowywania tzw. budżetu partycypacyjnego. Świadczy to o dużej rozrzutności miasta gdy istnieje konieczność wydatków alternatywnych.

Jeżeli stolicy nie stać na zainstalowanie świateł na przejściu do szkoły w której uczy się ponad 1000 dzieci, uważam że duże środki i finansowe (z naszych podatków) i zasobów ludzkich angażowane są w takim razie niewłaściwie.

Zapewniam, że bezpośrednia obserwacja tych przejść o 8 rano spowoduje, że zwątpi Pan w rzetelność metodologii prowadzonych badań. (  Zapraszam na wizję lokalną i obserwację. Również jedynie bezpośrednia obserwacja w godzinach wieczornych może Panu przybliżyć sytuację wokół szkoły i na jej terenie. Od dłuższego już czasu nie widziałem żadnego patrolu ani prewencji Policji ani Straży Miejskiej a w pobliżu szkoły znajduje się przecież (ul Umińskiego) duży komisariat policyjny.

Proszę mi wybaczyć moje zdenerwowanie, wierzę, że jest Pan zaangażowany w działania i robi wszystko na co pozwalają Panu kompetencje, ale sprawa będzie załatwiona gdy Miasto Warszawa zapewni bezpieczeństwo dzieciom na odnośnych przejściach dla pieszych i w godzinach wieczornych wokół szkoły SP 312.

Pozdrawiam serdecznie,

Jacek Kotowski

R tutorials in Computerworld.

R in 5 lines series by Sharon Machlis: http://www.computerworld.com/tag/rin5linesorless/?nsdr=true#tk.ctw-infsb

R: Scavenging Github: imDev and TeachingDemos by Dmitry Grapov

Interesting Excel interface to R via RExcel: https://sourceforge.net/projects/imdev
Some interesting tutorials to study: https://github.com/dgrapov/TeachingDemos
https://sourceforge.net/projects/teachingdemos/files/
The author responded next day. I need to follow up!

Excel VBA: Evaluate() or square brackets shorthand.

I recommend this trick to shorten the code for entering/copying/calculating data in spreadsheet ranges:
http://www.ozgrid.com/forum/showthread.php?t=52372&page=2

Example:

Sub Test()
    [A1].Offset(0, 1) = [=SUM(Table1[A])]
End Sub

R appeal to Ministry of Digital Affairs of Poland

Dear Minister Anna Streżyńska


I would like humbly to propose that Polish Ministry of Digital Affairs should support  the development of R community in Poland with a dedicated government program.


R community is a dynamically growing number of users of free R statistical environment which facilitates (indeed makes it easy for everyone) programming, data cleaning, exploration and data mining. In Warsaw alone it includes growing number of  enthusiasts, Warsaw Open University alumni of R courses, regular meetups (http://www.spotkania-entuzjastow-r.pl/), Smarter Poland activities and many others.


R, can provide a single environment for:

-  online access to public statistical data (medical, social, crime, economic, weather),

- a remote interactive teaching data science and programming in schools - teaching the IT skills that will be of immediate use to anyone  from business startup-per to hobbyist building a drone capable of  machine learning),

- visualizing and representing analysis results related to Poland (choropleths or subplots imposed on the map of Poland)

- the environment can also be set up for quick generating very high quality and publication ready reports that can be immediately printed and published on-line.


R can facilitate government transparency and promoting Open Government programme. (http://radar.oreilly.com/2009/07/making-government-transparent.html, http://ropengov.github.io/)


Some ideas of what Ministry of Digital Affairs can coordinate and assist with is:

- development of a dedicated R package for accessing public data, analysing and visualising it on an administrative map of Poland,

-development of an R distance learning package teaching statistics to Polish students (it can be the the matter of preparing a localized quality content, the tool "swirl" is available for that purpose),

- encouragement of public offices to publish current data that can be easily analyzed in R (csv, . This can be current information from treasury, statistical offices, police (geography of crime incidents), weather, health diagnosis and procedures (NHF and Ministry of Health data) - information devoid of personal information but fresh and detailed enough for Polish citizen to make informed decisions.


Advantages for Polish administration and economy:

Free R can be compared to expensive SPSS, Statistica and SAS packages - it can save millions giving powerful mining capabilities to government at any level and all citizens. It has been popular with world best universities, now it is used by big media houses, banks and research labs.

R data mining can be used for evectively analysing any data and drawing right conclusions in  health management, poverty, tax evasion, crime incidence, even counterintelligence and anti-terrorism. R literacy can generate high quality jobs and bring measurable profit because it is a tool facilitating innovation and right decisions based on science.

It gives Poland a unique chance to be among the first to embrace the ideas of true modern democracy expressed in access to information and true modern freedom of making informed choices.


Sincerely Yours,

Jacek Kotowski

Coffee break: Lesson ideas: R and Titanic disaster - trees and forests.

Great ideas for a beginner like me to play with data mining.
Data and challenge proposed by  kaggle.com:
https://www.kaggle.com/c/titanic/dat

Good tutorials and proposed solutins here:
http://trevorstephens.com/post/72916401642/titanic-getting-started-with-r and

https://github.com/trevorstephens/titanic

Also: https://www.kaggle.com/amoyakd/titanic/randomforest-method-v1-0

https://www.kaggle.com/c/titanic/forums

Some reading on R capabilities: 
http://www.edureka.co/blog/implementation-of-decision-tree/
http://www.r-bloggers.com/a-brief-tour-of-the-trees-and-forests/


Other tutorials:
Iris data: http://rischanlab.github.io/RandomForest.html
(R Basics worth exploring: http://rischanlab.github.io/)

http://dni-institute.in/blogs/random-forest-using-r-step-by-step-tutorial/ (might be even better for a start

http://www.edureka.co/blog/implementation-of-decision-tree/

Biostars Tutorial: Machine Learning For Cancer Classification - Part 1 - Preparing The Data Sets
https://www.biostars.org/p/85124/

http://www.tutorialspoint.com/r/r_random_forest.htm

http://www.analyticsvidhya.com/blog/2015/09/random-forest-algorithm-multiple-challenges/

Other important topics to explore:
http://www.analyticsvidhya.com/blog/2015/12/faster-data-manipulation-7-packages/

Rafał Wójcikowski -constructive critique of gov pronatalist policy in Polish Parliament

Coffee break, listened briefly to Polish Parliament debate on pronatalist policy 500PLN/month for each child.
Very good alternative proposed by Rafał Wójcikowski: https://pl.wikipedia.org/wiki/Rafa%C5%82_W%C3%B3jcikowski Based on Milton Friedman's negative income tax https://en.wikipedia.org/wiki/Negative_income_tax Implemented in Israel. Need to read more on this. Will PiS gov have courage and guts to consider it? I wonder what min. Morawiecki thinks. Point for Kukiz15 party.

Small plots on a plot: R

Pie charts instead of points or bubbles in x-y/scatter-plot/bubble plot: Uncle google found this for me:

http://blog.revolutionanalytics.com/2012/09/visualize-complex-data-with-subplots.html
http://www.r-bloggers.com/embeding-a-subplot-in-ggplot-via-subview/
http://vita.had.co.nz/papers/embedded-plots.pdf

ggsubplot

http://stackoverflow.com/questions/26577669/tiny-pie-charts-to-represent-each-point-in-an-scatterplot-using-ggplot2
http://stackoverflow.com/questions/7714677/r-scatterplot-with-too-many-points/16122003#16122003



pieGlyph
http://stackoverflow.com/questions/20465070/barplots-on-a-map/20468836#20468836

Maybe it will be possible in ggvis??
http://ggvis.rstudio.com/ggvis-basics.html


===========
Here a solution without ggplot (p.11):
https://www.r-project.org/doc/Rnews/Rnews_2003-2.pdf

====
plotrix and floating.pie?
http://ask.programmershare.com/2269_10385068/

====
http://gis.stackexchange.com/questions/4568/effectively-displaying-demographic-data-on-a-printed-map

====
ggtree
http://stackoverflow.com/questions/10368180/plotting-pie-graphs-on-map-in-ggplot

mapplots
http://www.molecularecologist.com/2012/09/making-maps-with-r/


Rozwiazanie w Excelu:
http://www.andypope.info/charts/piedatamarkers.htm

R - Machine learning cheatsheet

A syntax for applying some machine learning procedures in R. Misleadingly.... simple :-) http://vitalflux.com/cheat-sheet-10-machine-learning-algorithms-r-commands/

Excel: fill in table from another table.

UPDATE: I am now working on replacing offsets with absolute or structured references, ie. references to Listobject Table. This will make my vba immune from inserting columns and allow hiding the source table:

Private Sub FindCopyValues()
     
     

Dim strKodRange As Range
Dim lObjSource As ListObject: Set lObjSource = Sheets("Pricelist").ListObjects("tblPricelist")
Dim lObjTarget As ListObject: Set lObjTarget = Sheets("Quote").ListObjects("tblQuote")

Dim iTableRowNum As Integer

'set cursor in first cell of column Code in tblPricelist
Dim curAddr As Range: Set curAddr = lObjTarget.DataBodyRange.Cells(1, lObjTarget.ListColumns("Code").Index)

'set what to look for
Dim strKod As String: strKod = curAddr.Value



'what offsets between Code and Price

With lObjTarget
    Dim Code2Price As Integer: Code2Price = .ListColumns("Price").Index - .ListColumns("Code").Index
    Dim Code2Description As Integer: Code2Description = .ListColumns("Description").Index - .ListColumns("Code").Index


End With



      
   Do Until strKod = ""
        
            Set Cell = lObjSource.ListColumns("Code").DataBodyRange.Cells.Find(What:=strKod, MatchCase:=False, LookAt:= _
            xlWhole, SearchFormat:=False)
            
            iTableRowNum = Cell.Row - lObjSource.DataBodyRange.Rows(1).Row + 1
            Debug.Print iTableRowNum
    
'            With Cell
'
'                   curAddr.Offset(0, Code2Price).Value = .Offset(0, 1).Value
'                   curAddr.Offset(0, Code2Description).Value = .Offset(0, 2).Value
'
'            End With
               
            'Albo
                   curAddr.Offset(0, Code2Price).Value = lObjSource.DataBodyRange.Cells(iTableRowNum, lObjSource.ListColumns("Price").Index)
                   curAddr.Offset(0, Code2Description).Value = lObjSource.DataBodyRange.Cells(iTableRowNum, lObjSource.ListColumns("Description").Index)
               
               
               
            Set curAddr = curAddr.Offset(1, 0)
            strKod = curAddr.Text
       
   Loop
       
Application.ScreenUpdating = True
Application.EnableEvents = True
   
   
End Sub

Problem z vlookupem, nie zwraca wartości wraz z linkiem do strony internetowej, zdjęcia czy dokumentu.
Rozwiązanie, np. użycie VBA

Private Sub Worksheet_Change(ByVal Target As Range)
 
Dim strKod As String
 
strKod = Sheets("Baza").Range("G4").Text
  
If Target.Address = "$G$4" Then
 
     Range("$G$4").Select
      
    With Sheets("Aku").Cells.Find(What:=strKod, after:=ActiveCell, LookIn:=xlValues, LookAt:= _
            xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
            False)
             
            .Offset(0, 1).Copy Destination:=Sheets("Baza").Range("H4")
      
            .Offset(0, 2).Copy Destination:=Sheets("Baza").Range("I4")
    End With
   
    End If
 
End Sub

Powtarza się kod .Offset... więc można go połączyć w jedną linię za pomocą Union. Ponadto, nie chcę szukać w całym arkuszu a jedynie w kolumnie tabeli.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim strKod As String

strKod = Sheets("Baza").Range("G4").Text
oListObj = Sheets("Aku").ListObjects("Tabela1").ListColumns("Nazwa aku.")
 
If Target.Address = "$G$4" Then

     Range("$G$4").Select
     
    With oListObj.DataBodyRange.Cells.Find(What:=strKod, after:=ActiveCell, LookIn:=xlValues, LookAt:= _
            xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
            False)
            
            Union(.Offset(0, 1), .Offset(0, 2)).Copy Destination:=Sheets("Baza").Range("H4")
     

    End With

    End If

End Sub

A co jeśli chcemy wyszukać nie tylko w komórce G4 ale w całej kolumnie?

Private Sub FindCopyValues()
   
Dim strKod As String
Dim strKodRange As Range
Dim objList As ListObject
Dim curAddr As Object
 
'Application.ScreenUpdating = False
'Application.EnableEvents = False
 
 
Set objList = Sheets("Aku").ListObjects("Tabela1")
Set curAddr = Worksheets("Baza").Range("$G$4")
   
 
    strKod = curAddr.Value
    
   Do Until strKod = ""
      
            Set cell = objList.ListColumns("Nazwa aku.").DataBodyRange.Cells.Find(What:=strKod, MatchCase:=False, LookAt:= _
            xlWhole, SearchFormat:=False)
  
            With cell
                       
                    Union(.Offset(0, 1), .Offset(0, 2)).Copy Destination:=curAddr.Offset(0, 1)
         
            End With
             
   Set curAddr = curAddr.Offset(1, 0)
   strKod = curAddr.Text
     
   Loop
     
Application.ScreenUpdating = True
Application.EnableEvents = True
 
 
End Sub

---

Inny temat: chcemy skopiować tylko widoczne wiersze do innej tabeli (niewidoczne nas nie interesują).

 

Sub CopyVisibleProducts()

Sheets("cennik").ListObjects("Table1").DataBodyRange.SpecialCells(xlCellTypeVisible).Copy Sheets("pro_forma").Range("b5")
Application.CutCopyMode = False

End Sub

Coffee break: imputation

From CranR:
Simple mean imputation: http://www.r-bloggers.com/example-2014-5-simple-mean-imputation/

knnImputation {DMwR} Function that fills in all NA values using the k Nearest Neighbours of each case with NA values.
centralImputation {DMwR} This function fills in any NA value in all columns of a data frame with the statistic of centrality (given by the function centralvalue()) of the respective column.

Other: kNNImpute {imputation} Imputation using k-nearest neighbors. For each record, identify missinng features. http://www.bioconductor.org/packages/release/bioc/html/impute.html
 impute {Hmisc} http://www.inside-r.org/packages/cran/hmisc/docs/impute http://www.jstatsoft.org/article/view/v023i10 http://docs.zeligproject.org/en/latest/using-Zelig-with-Amelia.html

What u play with on smartphone? I play R stats :-)

I followed this tut, thanks! http://www.r-bloggers.com/install-r-in-android-via-gnuroot-no-root-required/ 
1. Installed GnuRoot app and GnuRoot Wheezy (no real rooting required, it creates its own world!)
2. Refreshed software: apt-get update & apt-get upgrade.
3. Installed R: apt-get install R-base-core. Lots of space is needed, so delete all trash apps from your android phone!

===========
I typed R, created a dataframe to play with, run kmeans on it, with no issues. There is no graphics yet, you will not see plots (probably you can generate them as files), unless you install another heavy MB of linux GUI like lxde. But for my bus trips I use txtplot - it produces density, boxplot, scatter, acf in ASCII.
To see kmeans clusters: txtplot(x,y,pch=cluster_no)
R used phone's internet and downloaded it with no issues - it means you can also download dataframes from a repository online.

 A keyboard with arrows is recommended, like Hacker's Keyboard, so that it should be easy to go through previous commands, modify them and correct typo's inside the string..


GnuRoot autor Corbin Champion also offers a solution to run Octave on your phone. I highly recommend him.  https://play.google.com/store/apps/details?id=champion.gnuroot

More: Google: install r in android:
http://www.r-ohjelmointi.org/?p=1434
http://stackoverflow.com/questions/13047994/are-there-any-android-version-of-r-without-rooting-the-device

Watched "Interstellar" with my son. Impressive

Can we chase and reprogram an old drone just like in a movie? What is tesseract? Event horison? Singularity? Will Earth turn against us? Can we leave Earth now and go, confortably, sustainably live in Space? Hybernate and go towards other planetary systems? How do we know Earth turned against life in the past? Geological layers? How do we know there are other planets around other stars and there is oxygen on them? Spectroscopy? We will probably talk for several weeks about it. Great movie. https://en.wikipedia.org/wiki/Tesseract https://pl.wikipedia.org/wiki/Horyzont_zdarze%C5%84 https://en.wikipedia.org/wiki/Event_horizon https://en.wikipedia.org/wiki/Gravitational_singularity

"Mantra" repeated frequently in the movie: 

Do not go gentle into that good night
Dylan Thomas, 1914 - 1953

Do not go gentle into that good night,
Old age should burn and rave at close of day;
Rage, rage against the dying of the light.

Though wise men at their end know dark is right,
Because their words had forked no lightning they
Do not go gentle into that good night.

Good men, the last wave by, crying how bright
Their frail deeds might have danced in a green bay,
Rage, rage against the dying of the light.

Wild men who caught and sang the sun in flight,
And learn, too late, they grieved it on its way,
Do not go gentle into that good night.

Grave men, near death, who see with blinding sight
Blind eyes could blaze like meteors and be gay,
Rage, rage against the dying of the light.

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

Nie wchodź łagodnie do tej dobrej nocy,
Starość u kresu dnia niech płonie, krwawi;
Buntuj się, buntuj, gdy światło się mroczy.

Mędrcy, choć wiedzą, że ciemność w nich wkroczy –
Bo nie rozszczepią słowami błyskawic –
Nie wchodzą cicho do tej dobrej nocy.

Cnotliwi, płacząc kiedy ich otoczy
Wspomnienie czynów w kruchym wieńcu sławy,
Niech się buntują, gdy światło się mroczy.

Szaleni słońce chwytający w locie,
Wasz śpiew radosny był mu trenem łzawym;
Nie wchodźcie cicho do tej dobrej nocy.

Posępnym, którym śmierć oślepia oczy,
Niech wzrok się w blasku jak meteor pławi;
Niech się buntują, gdy światło się mroczy.

Błogosławieństwem i klątwą niech broczy
Łza twoja, ojcze w niebie niełaskawym.
Nie wchodź łagodnie do tej dobrej nocy.
Buntuj się, buntuj, gdy światło się mroczy.

(wiersz Dylana Thomasa w przekładzie Stanisława Barańczaka)

Coffee break: VBA Class refresher

Very simple and recommendable: http://analystcave.com/vba-vba-class-tutorial/
Where to use: http://stackoverflow.com/questions/118863/when-to-use-a-class-in-vba
Some Access vba reading: http://sourcedaddy.com/ms-access/object-oriented-programming-with-vba.html
http://vbadeveloper.net/optionpricerclassesdesignvba.pdf

very interesting: http://www.thelandbeyondspreadsheets.com/a-simple-example-of-how-to-use-class-modules-for-something-useful-in-excel-vba/
Microsoft article: https://www.microsoftpressstore.com/articles/article.aspx?p=2225071
Indeed Wise Owl: http://www.wiseowl.co.uk/blog/s237/classes.htm

VBA: cleaning data from ListobjectTable without destroying it.

I highly recommend:
http://stackoverflow.com/questions/10220906/how-to-select-clear-table-contents-without-destroying-the-table Version improved for my needs checks if data has not been cleaned already:

    With .ListObjects("Table3")

        .Range.AutoFilter
            If .DataBodyRange.Rows.Count > 1 Then
            .DataBodyRange.Offset(1).Resize(.DataBodyRange.Rows.Count - 1, .DataBodyRange.Columns.Count).Rows.Delete
            End If
        .DataBodyRange.Rows(1).Cells.ClearContents
        
    End With

Poland's S&P Ratings Downgrade by M.Tyrmand

http://www.breitbart.com/london/2016/01/18/polands-sp-ratings-downgrade/

Excel - fast vlookup with Dictionary (MSScriptingRuntime)

I found an interesting entry: http://stackoverflow.com/questions/18656808/how-to-optimize-vlookup-for-high-search-count-alternatives-to-vlookup The formula is a table one and is entered with Ctrl+Shift+Enter. I added Application.Volatile to see live updates. Small limitation: It needs to be entered every 65536 rows if the column is longer.

R vs Python - nice code comparison.

I recommend https://www.dataquest.io/blog/python-vs-r/

Excel VBA to hide and unhide a sheet on entering the right password.

Read this post: http://www.mrexcel.com/forum/excel-questions/380770-visual-basic-applications-hiding%5Cunhiding-very-hidden-sheet-via-password.html to have a nice solution to protect a sheet using "very hidden" object property. Attention: One must also remember to protect VBA project itself with a password, so that the sheet cannot be explored and unhidden in VBA editor.

What is happening in Poland?

http://www.politico.eu/sponsored-content/what-is-really-happening-in-poland/

Data nomad reporting from Sahara

Another day with numbers comforting me. I am Mistah W.Kurtz in my cave amidst wilderness of big city.

Numbers are from only God.
Pure truth. Devoid of adjectives and exclamations. Beautiful beyond any girl you would like to love in your dreams.

Existing beyond death. Will take you to the stars...

Mistah Kurtz contemplates:

A doberman mistreated badly by a former owner tells the new one how happy it is by forming geometrical shapes in the garden with its toys. I contemplate it daily. Thinking about my  family incarcerated, crushed and blacklisted by scientific ideology, in the name of the progress of man.

Adjectives of man are forbidden among us, a taboo.

Numbers are from God. Will protect and help calculate the way from the stars.

The desert conforms me.
I am a Beduin nomad on a camel. I eat dates with my coffee and meet other nomads once a year in a wilderness. Like lone sailors meeting mid-ocean and we talk simple geometry of the universe. finding regularities in the clear sky above.

Greetings from a digital nomad from xxi c. desert in Warsaw. Central Europe.

datascienceplus.com - just joined.

I have just joined . Free R tutorials from contributors. Great idea.

Excel formula to sort into bins.

 With VBA

Function MyBins(MyValue As Single) As String

Application.Volatile

        If SGPValue > 1 Then
               MyBins = "> 100%"
        
       
        ElseIf MyValue <= 1 And MyValue > 0.8 Then
           MyBins = "100%-80%"
        ElseIf MyValue <= 0.8 And MyValue > 0.65 Then
           MyBins = "80%-65%"
        ElseIf MyValue <= 0.65 And MyValue > 0.5 Then
           MyBins = "65%-50%"
        ElseIf MyValue <= 0.5 And MyValue > 0.2 Then
           MyBins = "50%-20%"
        ElseIf MyValue <= 0.2 And MyValue >= 0 Then
           MyBins = "20%-0%"
        ElseIf MyValue < 0 Then
           MyBins = "< 0"
        
       Else
           MyBins = "Please Verify"

           
    End If
 
    
End Function

Without?
Would you use IF for that purpose? And when there are several bins you will have plenty of nested IFs?
The answer is to use LOOKUP. cf.: http://www.mrexcel.com/forum/excel-questions/631008-excel-formula-sort-into-bins.html#post3130377 In this example you can also see how an array, a table, can be encoded in a formula.
I think it is very neat to use ListObject Table to keep the parameters in, then the formula would look like:
=LOOKUP(B3;Table1[a];Table1[b]) where [a] is a column with upper limits and [b] contains the corresponding names of the bins.

Table 1
a            b
-99999  < 1
1           1 to 1,9999
2           2 to 4,9999
5           5 to 9,9999
10         10 and avobe

To count bins to generate a histogram it is probably best to use countifs:
http://exceluser.com/formulas/countifs-frequency-distributions.htm 

Survey Science: an interesting excel macro collection for researchers.

See: http://www.surveyscience.co.uk/index.html

Plus, an interesting article on outliers' detection: http://fernandonunezserrano.com/Outlier_Detector.pdf
(and in R: http://www.rdatamining.com/examples/outlier-detection)

R: mosaic plots with labels? Other graph musings.

Googled out this solution: http://stackoverflow.com/questions/20228326/mosaic-plot-with-labels-in-each-box-showing-a-name-and-percentage-of-all-observa

I looked at these pages as well:
https://ds4ci.files.wordpress.com/2013/09/user08_jimp_custseg_revnov08.pdf
http://tables2graphs.com/doku.php?id=03_descriptive_statistics#figure_2
https://cran.r-project.org/web/packages/vcd/vignettes/residual-shadings.pdf
http://cran.us.r-project.org/web/packages/vcdExtra/vignettes/vcd-tutorial.pdf
http://www.math.ucla.edu/~anderson/rw1001/library/base/html/mosaicplot.html
summary of cat. values http://stackoverflow.com/questions/14235096/data-summary-based-on-multiple-categorical-variables

Clustering segmentation:
http://marketing-yogi.blogspot.com/2012/12/segmentation-tools-in-r-session-5.html
https://ds4ci.files.wordpress.com/2013/09/jimporzak_rfwithr_dmaac_jan07_webinar.pdf


OT: other graphs
http://www.cyclismo.org/tutorial/R/intermediatePlotting.html
http://stats.stackexchange.com/questions/4089/graphical-data-overview-summary-function-in-r?rq=1
http://www.statmethods.net/advstats/cart.html
http://ww2.coastal.edu/kingw/statistics/R-tutorials/graphically.html


Breaking data into bins: http://stackoverflow.com/questions/12979456/r-code-to-categorize-age-into-group-bins-breaks

Aggregating: http://www.statmethods.net/management/aggregate.html
http://davetang.org/muse/2013/05/22/using-aggregate-and-apply-in-r/
http://www.r-statistics.com/2012/01/aggregation-and-restructuring-data-from-r-in-action/
https://www.soa.org/News-and-Publications/Newsletters/Compact/2012/august/The-R-Corner-%E2%80%93-Aggregate-Function.aspx

Early morning R readings: knn imputation of missing values

Keywords: imputation of missing values in r with knn Googled out and briefly read about of using k nearest neigbors for filling missing values: http://stats.stackexchange.com/questions/61110/knn-imputation-r-packages http://finzi.psych.upenn.edu/library/clustergas/html/missvalue.knn.html http://artax.karlin.mff.cuni.cz/r-help/library/scrime/html/knncatimputeLarge.html

Excel VBA: changing value calculates discount, changing discount calculates value.

We want to enter values and have discount calculated in another column. On the other hand we want to change discount and have value calculated... We need VBA and sheet events:

Private oListObj As ListObject
      
'Private Sub Worksheet_Activate()
'    ActiveSheet.Protect "pwd", AllowFiltering:=True
'End Sub

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
      
    Set oListObj = Worksheets("Quotation").ListObjects("tblProForma")
      
    Application.EnableEvents = True
      
        If Not Intersect(Target, oListObj.ListColumns("Price").DataBodyRange) Is Nothing Then
        Application.EnableEvents = False
        Application.AutoCorrect.AutoFillFormulasInLists = False
        Target.Formula = Target.Value
        Application.EnableEvents = True
        End If
          
        If Not Intersect(Target, oListObj.ListColumns("Discount").DataBodyRange) Is Nothing Then
        Application.EnableEvents = False
        Application.AutoCorrect.AutoFillFormulasInLists = False
        Target.Formula = Round(Target.Value, 5)
        Application.EnableEvents = True
        End If

    End Sub


      
    Private Sub Worksheet_Change(ByVal Target As Range)
      
    Dim PriceDiscountOffset As Integer: PriceDiscountOffset = ActiveSheet.Range("tblProForma[[#All],[Price]:[Discount]]").Columns.Count - 1
          
    Set oListObj = Worksheets("Quotation").ListObjects("tblProForma")
      
    Application.EnableEvents = True
    
    '=============
    'This code prevents macro from ruining table if user resizes it.
    '=============http://www.mrexcel.com/forum/excel-questions/844654-can-i-tell-excel-run-macro-every-time-i-manually-resize-listobject-excel-table.html
    Dim sLastUndoStackItem  As String
  
        On Error Resume Next
        sLastUndoStackItem = Application.CommandBars("Standard").FindControl(ID:=128).List(1)
        On Error GoTo 0
    
     If sLastUndoStackItem = "Table Resize" Then
       Exit Sub
     End If
    
    '=============
    
      
        If Not Intersect(Target, oListObj.ListColumns("Price").DataBodyRange) Is Nothing Then
        Application.EnableEvents = False
        Application.AutoCorrect.AutoFillFormulasInLists = False
        
        
        Target.Offset(0, PriceDiscountOffset).Formula = "=IF([@[Price]]<>"""", -([@[Price]]-[@[Pricelist]])/[@[Price]],"""")"
        Application.EnableEvents = True
        End If
          
        If Not Intersect(Target, oListObj.ListColumns("Discount").DataBodyRange) Is Nothing Then
        Application.EnableEvents = False
        
             
        Application.AutoCorrect.AutoFillFormulasInLists = False
        Target.Offset(0, -PriceDiscountOffset).Formula = "=[@[Pricelist]]-([@[Pricelist]]*[@[Discount]])"
        Application.EnableEvents = True
        End If

      
    End Sub

Coffee break. Trees, some reading and stupidly playing with tree clusters...

http://www.exegetic.biz/blog/2013/05/package-party-conditional-inference-trees/ very good read.
http://www.r-bloggers.com/package-party-conditional-inference-trees/
http://www.statmethods.net/advstats/cart.html
https://stat.ethz.ch/R-manual/R-devel/library/stats/html/hclust.html

Played like that:

> drzewko<-ctree(mojedane$y~.,data = mojedane[,c(1:3)])

> predict(drzewko, type = "response") 

R: quick view at data - correlogram, margin plot etc.

Quick get it from excel

> mojedane<-read.table(file="clipboard",dec = ",",header=TRUE) 

Then, after: http://www.statmethods.net/advgraphs/correlograms.html 

> library(corrgram) 

> corrgram(mojedane, order=TRUE, lower.panel=panel.ellipse,
+          upper.panel=panel.pie, text.panel=panel.txt,
+          diag.panel=panel.minmax,
+          main="Korelacje - moje dane") 

To see where the missing data goes.

http://www.inside-r.org/packages/cran/VIM/docs/marginplot

> library("VIM")

> mojedane<-read.table(file="clipboard",dec = ",",header=TRUE,row.names = 1)
> marginplot(mojedane[,c(1,2)])