Data science tools

Data manipulation with R

Star

R dplyr tidyr lubridate

By Afshine Amidi and Shervine Amidi

Main concepts

File management The table below summarizes useful commands to make sure the working directory is correctly set:

Chaining The symbol %>%, also called "pipe", enables to have chained operations and provides better legibility. Here are its different interpretations:

• f(arg_1, arg_2, ..., arg_n) is equivalent to arg_1 %>% f(arg_2, arg_3, ..., arg_n), and also to:
  • arg_1 %>% f(., arg_2, ..., arg_n)
  • arg_2 %>% f(arg_1, ., arg_3, ..., arg_n)
  • arg_n %>% f(arg_1, ..., arg_n-1,  .)


• A common use of pipe is when a dataframe df gets first modified by some_operation_1, then some_operation_2, until some_operation_n in a sequential way. It is done as follows:
  
  
  R

  # df gets some_operation_1, then some_operation_2, ..., then some_operation_n
  df %>%
    some_operation_1(params_1) %>%
    some_operation_2(params_1) %>%
               ...             %>%
    some_operation_n(params_1)
  

Exploring the data The table below summarizes the main functions used to get a complete overview of the data:

Data types The table below sums up the main data types that can be contained in columns:

Data preprocessing

Filtering We can filter rows according to some conditions as follows:

df %>%
  filter(some_col some_operation some_value_or_list_or_col)

where some_operation is one of the following:

Remark: we can filter columns with the select_if() function.

Changing columns The table below summarizes the main column operations:

Conditional column A column can take different values with respect to a particular set of conditions with the case_when() command as follows:

case_when(condition_1 ~ value_1,  # If condition_1 then value_1
          condition_2 ~ value_2,  # If condition_2 then value_2
                ...
          TRUE ~ value_n)         # Otherwise, value_n

Remark: the ifelse(condition_if_true, value_true, value_other) function can be used and is easier to manipulate if there is only one condition.

Mathematical operations The table below sums up the main mathematical operations that can be performed on columns:

Datetime conversion Fields containing datetime values can be stored in two different POSIXt data types:

where format is a string describing the structure of the field and using the commands summarized in the table below:

Remark: data frames only accept datetime in POSIXct format.

Date properties In order to extract a date-related property from a datetime object, the following command is used:

format(datetime_object, format)

Data frame transformation

Merging data frames We can merge two data frames by a given field as follows:

merge(df_1, df_2, join_field, join_type)

where join_field indicates fields where the join needs to happen:

and where join_type indicates the join type, and is one of the following:

Join type	Option	Illustration
Inner join	default
Left join	all.x = TRUE
Right join	all.y = TRUE
Full join	all = TRUE

Remark: if the by parameter is not specified, the merge will be a cross join.

Concatenation The table below summarizes the different ways data frames can be concatenated:

Type	Command	Illustration
Rows	rbind(df_1, ..., df_n)
Columns	cbind(df_1, ..., df_n)

Common transformations The common data frame transformations are summarized in the table below:

Type	Command	Illustration
		Before	After
Long to wide	spread(   df, key = 'key',   value = 'value' )
Wide to long	gather(   df, key = 'key',   value = 'value',   c(key_1, ..., key_n) )

Row operations The following actions are used to make operations on rows of the data frame:

Action	Command	Illustration
		Before	After
Sort with respect to columns	df %>%   arrange(col_1, ..., col_n)
Dropping duplicates	df %>% unique()
Drop rows with at least a null value	df %>% na.omit()

Remark: by default, the arrange() function sorts in ascending order. If we want to sort it in descending order, the - command needs to be used before a column.

Aggregations

Grouping data Aggregate metrics are computed across groups as follows:

The R command is as follows:

df %>%                                                 # Original ungrouped data frame
 group_by(col_1, ..., col_n) %>%                       # Group by some columns
 summarize(agg_metric = some_aggregation(some_cols))   # Aggregation step

Aggregation functions The table below summarizes the main aggregate functions that can be used in an aggregation query:

Window functions

Definition A window function computes a metric over groups and has the following structure:

The R command is as follows:

df %>%                                       # Original ungrouped data frame
 group_by(col_1, ..., col_n) %>%             # Group by some columns
 mutate(win_metric = window_function(col))   # Window function

Remark: applying a window function will not change the initial number of rows of the data frame.

Row numbering The table below summarizes the main commands that rank each row across specified groups, ordered by a specific field:

Values The following window functions allow to keep track of specific types of values with respect to the group: