Filtering in R

Filtering

In spreadsheets, filtering is done by clicking on 'Filters' found in the Data menu. Filters selects data that meets a specified criteria. Usually a drop-down box is attached to a column, unique values of all the data in the column appears in the drop-down box. Any value selected will make the software to display occurrences of the number selected, excluding all other values. In R, filtering is done by:
Slicing: Data once read into R software, can sliced to be get bits and pieces that are needed. As discussed from the previous blog, the nearest representation of tabular data (with rows and columns) is a dataframe.  Using RStudio, we read sample data into R in the previous blog. To get a sense of what the data looks like, the 'str' function is used. It stands for structure. Running the command in the console produces:

From the results, we can see that the data has 43 observations of 7 variables, each variable represents a column, there is also the 'factor' term found in the results. In R, category data is called a factor, the categories are called levels. Since columns are variables in R, the sigil sign '$' is used,  which also appears in the results above. To display contents of Region column will mean running the command below

df$Region

Will appear in the console as shown below:

 The diagram shows that Region column has 3 categories of data namely Central, East, West. 

To filter (called slicing in R). The square brackets are used. The rows,columns and filter criteria are written inside the brackets. The syntax is as shown below:

The dataframe [rows{Filter criteria},column]

To illustrate with our data, filtering data from Central Region will mean running the command below:

df2[df2$Region=='Central',]

Will appear in the console as shown below:

The importance of ',' in the syntax cannot be overemphasized because it what determines if rows or columns will be selected. In the example above, the command is for rows to be selected. If columns are needed in the filter, the command will be

df2[df2$Region=='Central',c('OrderDate','Rep','Units')]

Will run in the console to produce:

 The data above shows data from the 'Central' region but with columns specified rather than the entire data.  The next blog will discuss Pivot tables in R.

Enjoy filtering in R

 

Moving to R (Part 2)

Opening a spreadsheet file in R
 

Opening a file in R is called reading the file, as R is a command-line programming language. R like any other programming language requires modules to get stuff done and opening (reading) a spreadsheet file will only happen when the right module is attached. A module is simply a portion of a computer program that can be used independently or with other modules to meet an objective.

 R Studio IDE

R Studio IDE shown above,has got to be the gold standard for what an R IDE should be. Shown below, it's got 4 windows namely:

1)Console: This window has the console which is where the compiler is, the compiler executes the R scripts

2)Workspace and history : This window has a directory-like container where resources connected to a task is kept. It also has a tab ' history 'where lines of codes compiled earlier are stored.

3)Files,plots,packages and help : This window has directory that shows all system files. that shows has a tab that shows graphs and another for packages. Packages are special R programs for doing lots of things.

4)Scripts and Data view: This window has tab for scripts and viewing data. Script written can be executed in the console. The data view tab is used for examining data.

Reading a spreadsheet file
There are several modules for reading spreadsheet file into R. This web page discusses it in details. Using R Studio, we will show how to read spreadsheet data into R. Below, are screenshots  of the windows mentioned above reading file into R.

Scripts and Data view

Above the Script view, script to read  spreadsheet data using two methods, Method 1 used the 'gdata' module using the 'require'  keyword and read.xls function is used to load the spreadsheet file into R. Method 2 involved saving the spreadsheet file as a text or csv (Comma Seperated Value) and using read.csv function, the data was read into R. Observe that each data read has been assigned a value using the '=' assignment operator. Also see two tabs representing views of the data read in. Scripts written in the script window are executed in the Console view by pressing a combination of keys on the keyboard. The keys are :

Ctrl+Shift + Enter

Console

 
Pressing the keys above, makes the console to compile any codes from the script. Any operation in IDE must be translated to a code in the console for it to work. An example selecting a data view tab will be translated to 'view(the data to be viewed)'. The other two windows do not have changes.

Aggregation, Charting, Pivot tables, Filtering and so on, and so forth can be done in R, we will explore further in the next blog.

Enjoy trying R out.

 

 

Moving over to R

The problem

Data comes in all shapes and sizes. Sometimes they come with some many columns that makes it difficult for one to make sense of . A special kind tool has to be used to work on the data, that tool is R.

R

R is an open source software used for analytics and statistic computing. It has become quite popular because its capabilities. The software is able to do almost everything a spreadsheet does and more.

Structure

R is structured differently from spreadsheets. In spreadsheet, cells stack up to make rows and columns and data is arranged in a tabular form. Not so with R, the closest thing to tabular data representation is called a Dataframe. The closest thing to a column in R is called a vector and for row is list. For example a named column in a spreadsheet is represented as :

In R it is :Tax <- c(34,45,60,70,90,30,45), the 'c' stands for concatenate, which is also a function in spreadsheet for combining data. A named row in a spreadsheet can be:

In R becomes: Tom <- c(Tom,25,London). So a vector contains the same data type while a list do not have the same datatype. 

Interface

Most spreadsheet software have a graphical user interface, that means that users interact with the program with a mouse,touchpad etc. R is a programming language, so interaction is command-line, users have to type in lines of code. R has a GUI module that has to be activated through commandline before use. It's called Rattle. Most R users use an IDE or editor of some sort to write code that R compiler will compile. A search on Google will list out most of them, but RStudio comes highly recommended. Spreadsheets operate on data in cells that stack up to make rows and columns, R being a programming language operates by using variables assigned to vectors and lists that stack up to make Dataframes. 

Assignment operators
People conversant with R must have come across this symbol '<-', it's called a left side assignment operator, it assigns data to names the R software can recognize and work with, just like the example shown earlier, repeated below as :

Tom <- c(Tom,25,London)

There is a right side assignment operator, which looks like this '->', which is just the reverse of the one above, shown below as :

c(Tom,25,London) -> Tom

Finally, the assignment operator used in most programming languages, the '=' sign. This operator is my favourite because it takes just one keystroke to reach unlike the rest two that takes 3 keystrokes (the two symbols + a shift key), shown below as :

Tom = c(Tom,25,London)

Let's stop here for now. There will be another blog on R that will discuss how to do most thing that spreadsheets do in R.

Why don't you give R a go ?

 

What pivot tables can do in spreadsheets

Wikipedia describes pivot table as a data summarization tool found in spreadsheets and business intelligence software. Although, the tool is used mainly for summarization, the tool has been put into other uses in various software. Pivot tables can do graphs, calculate values, percentage changes and so on and so forth.

Making a basic pivot table

To make a basic pivot table, there are few things that a spreadsheet user should

understand. The main objective is to aggregate the values in a numerical field or column. To illustrate, I'll use this web page that shows how to make a basic pivot table. In the data, the amount column has the numerical data and will be a vital component of the pivot table.

Every pivot table summarizes a numerical data from a column in a workbook. It uses filters to select data that fit a certain criteria whilst summarizing a numerical data. There are 3 components of pivot table and they are :

Rows labels: These are row filters applied to pivot tables whilst working on numerical data.

Column labels: These are column filters applied to pivot table whilst working on numerical data

Report Filters : This filter selects rows and columns that meet a set criteria whilst working on data.

Values: This is the numerical data to be worked on. Aggregation functions like average,count,sum, product can be performed on the data. Statistical functions like standard deviation can be performed as well.

Values

These values worked on can be taken directly from the data or derived from a calculation. The values can be formatted and displayed according to user requirements. They can also be displayed in special ways by using the "Show Values As' tab.

 

The diagram above shows the shows the 'Show Value As' tab. The columns or fields in the data can determine how values are displayed. There are are 15 different calculations that can determine data is displayed based on columns or fields in the data. This website explains the process in details. Using fields or columns as base field, it is possible to calculate percentage difference between fields, rankings between fields. Thorough planning is needed to pick a base field that will be able deliver what user intended.

Charts

This is basically the process of visualizing data from pivot tables. The advantage of this graph is the use of filters to visualize data that meets a given criteria. This website explains the process in details. Pivot chart is the quickest way of graphs that are supposed to meet multiple criteria. The diagram below is an example of a pivot chart. See the filter buttons that change the graphs depending on what filter is applied.

There's limit on how data can be manipulated with pivot tables, all is needed is planning to figure out what the end result should look like.

Enjoy working with pivot table.

Why data lookups fail in spreadsheets

To be able to lookup data is quite an important skill to have for a spreadsheet user. However, most users struggle with this skill because of Cell Referencing.

The website spreadsheets.about.com describes cell reference  as identification of a cell or a group of cells in a worksheet. So data lookup will not work without cell referencing. There are 3 types of cell referencing, namely:

Relative cell reference: This is the default setting in spreadsheets. Row and Columns change with formula with  this cell reference.
Absolute cell reference: This cell reference has instructions to peg a row or column. It is done by inserting the '$' sign in front row column notation. For instance $A1, will peg reference to column A, the cell reference will change only for row numbers.
Mixed cell reference: This reference combines relative and mixed cell reference to look up data for formula. To find out more on cell referencing , check this page 

Formulas that look up data
There are so many formulas that look up data. It depends on what the user's objective is for the data. Is data the final product or work-in-progress that goes into another formula. This web page provides guidelines on how to decide what formula to use and offers tutorials on how to use these formulas. I will discuss the impact of cell referencing on a few of these formulas with an example.

Let's assume that there are two tables in a worksheet, the first table is just as shown below.

The other table will be looking up data from this table above. This table has got data validation tool that enables it to pick data from a list, this means that it can look up price data for the entire item on the list. It looks as shown below.

Any item picked from the list will have it's price displayed by looking it up from the list above. The lookups has been done with formulas VLOOKUP,INDEX MATCH and OFFSET MATCH. I'll be discussing the cell referencing used to look up the data.

VLOOKUP

The syntax for VLOOKUP can be summarized as shown below:

vlookup(what is to be looked up, where to search, what column to search, the kind of match needed) 

In the formula above, E1 in blue is the item to be looked up, $A$2: $B$5 in red is range of cells to search, 2 means that search should narrowed to the 2nd column which is price, 0 tells the program that exact match is required. For this formula, mixed cell referencing has been used, the character in blue has relative cell reference, while those in red have absolute cell reference. This way, any item picked (having a relative cell reference) can be looked up because search boundaries have been set with absolute cell references in red.

INDEX MATCH

The formula combines two formulas to look up data and the syntax is as follows:

index(the result area,match(what is to be looked up, the area to search, the kind of match needed)

In the formula, the area in pink shows the boundary for the search, the character in red is the value to be searched for in the search boundary. The areas in blue is where the result data can be found. Like in the formula above, mixed cell referencing is used, the item to be searched has relative cell reference, while the result and search boundaries do have absolute cell referencing.

OFFSET MATCH

Just like the formula above, this formula is also a fusion of two formulas, the syntax is as follows:

offset(the first row of search area,match(what is to be looked up, other rows in the search area , match needed), the number of adjacent rows to return)

The the first row is pegged with absolute cell reference in blue, the item to be searched for has relative cell reference ( this way, any item can be looked up), the search area is pegged with an absolute reference in red.

Cell referencing establishes search boundaries for formulas to use during look up. The rule of the thumb is to have items to be looked with relative cell references, search areas with absolute cell references so that the search areas are confined and the right data looked up.

Enjoy looking up your data

Working with date and times in a spreadsheet.

There's quite a lot date and time in data. Knowledge of how to manipulate date and time can be very beneficial. Dates and times are made of seconds,minutes,Hours and Days. The representation in spreadsheets is not too different.

Dates in Spreadsheets.

Dates in spreadsheets are represented by serial numbers different from regular numbers. Operations like sorting,addition,subtraction on these numbers do not provide expected results. According to Microsoft, serial number 1 represents 1st January 1900 at 12:00:00 am. Increasing or decreasing these serial numbers produces different dates.The number 30010 represents 28th February, 1982. There are several functions for manipulating date in spreadsheet.

Time in Spreadsheets

Times are stored in spreadsheets as decimal numbers that goes from .0 to .99999, .0 represents 00:00:00 while .99999 is 23:59:59. As in dates, increasing and reducing these numbers will produce different times.

Manipulating Dates and Times

 

Above is the format that represents date and time in Spreadsheets. The 0 represents a digit. The representation is DD/MM/YY HH:MM:SS. YY can also be YYYY. The software checks the 1st two digits, if the number is equal to or less than 31, that becomes the day value, it then checks the next two, if the number is equal or less than 12, that's the month value. The last two or four becomes the year value. The software will recognize any subset from the above mentioned number pattern.

'12-03' typed into a cell will be interpreted by the software as 12th of March. '03/2004' will be March of 2004.

Did you know that?

Gives

Date and Time functions in Spreadsheets

  

There are a lot functions in spreadsheet that manipulate date and time. This web page from Microsoft discusses all these functions in details. A few thing to note about these functions:

1. Most functions work by splitting up components that make up the date and time in spreadsheets. For instance 'HOUR' function takes the hour function in date and time for calculation.
2. Functions can be combined or 'nested' with other function to achieve objectives whilst calculating.

 

Above is an example of nested functions to get the serial date for the last day of the current year.

Enjoy working with dates and times.

 

 

The best way to capture and annotate images on the monitor

At some point, there will be a need to capture images on monitors and annotate , for use in reports, presentation, writings etc.

The basic way of getting this done is by using the 'PrtSc' button on the keyboard and annotating with a graphic software like Paint (for windows users). The process is the same for Linux user, 'PrtSc' prints the entire window. There are so many software that capture screen across the platforms, a search on Google will show most of them.

For users of Windows Vista, 7 , 8 and 10 , there's this wonderful software called Snipping Tool, it has to be in my view the best tool for screen capture. , represented by the icon shown below.

 

Click on the icon above (which can be found through a search or the favourite icons section), displays the window below.

 

Clicking on 'New' displays snipe options , which has been explained by windows.microsoft.com as :

 

Snips once made, can be saved as :

An Image file: Snips can be saved as (PNG,GIF and JPEG) file which be annotated and used for various purposes.

A HTML file: They can be also saved as a special type of HTML file called MHTML (Mime Hypertext Markup Language) which is optimised for emails, meaning that snips can be sent in emails

The software has one big flaw, it cannot annotate, does not have any text functions. Annotation can be done with any graphic software like Paint. However there's one graphic software that does the job quite well. It's called Draw.

The Draw software is an open source graphic software, originally part of the Openoffice Suite, that came with Writer,Calc, Impress as a component in the productivity suite from Apache. A fork of the software was named Libre was made when Apache was acquired by Oracle. The Open office and Libreoffice Draw are both available for downloads.

Open office Draw download page 

Libreoffice Draw comes as part of the suite and cannot be downloaded separately, to download suite ,go to Libre Office Download page.

The reason for the choosing of 'Draw' over other graphic software is more tools to work with. It's got more connectors, symbols and font work, it's just like Microsoft Word that is optimized for graphics.

To load image , click on the 'Insert' from the horizontal menu and select image from from the drop-down menu and then choose 'File' to select the filepath to the image file , choose 'Scan' to get image from scanner, choose 'Fontwork gallery' to use click-art from the system.

Once image has been uploaded to page, annotations and symbols can be added with the tools at the bottom of the page . Completed work can be saved as PDF (Portable Document Format) but a good alternative is snipe the work directly from the monitor. This method enables for cropping to the right size to be done at the same time.

Enjoy Snipping.