concatenating data tables
play

Concatenating data.tables Scott Ritchie Postdoctoral Researcher in - PowerPoint PPT Presentation

Feb 13, 2023 •248 likes •715 views

DataCamp Joining Data with data.table in R JOINING DATA WITH DATA . TABLE IN R Concatenating data.tables Scott Ritchie Postdoctoral Researcher in Systems Genomics DataCamp Joining Data with data.table in R Same columns, different data.tables

  1. DataCamp Joining Data with data.table in R JOINING DATA WITH DATA . TABLE IN R Concatenating data.tables Scott Ritchie Postdoctoral Researcher in Systems Genomics

  2. DataCamp Joining Data with data.table in R Same columns, different data.tables Concatenating data.tables

  3. DataCamp Joining Data with data.table in R Concatenation functions rbind() : concatenate rows from data.tables stored in different variables rbindlist() : concatenate rows from a list of data.tables

  4. DataCamp Joining Data with data.table in R The rbind() function Concatenate two or more data.tables stored as variables # ... takes any number of arguments rbind(...) rbind(sales_2015, sales_2016) quarter amount 1: 1 3200100 2: 2 2950000 3: 3 2980700 4: 4 3420000 5: 1 3350000 6: 2 3000300 7: 3 3120200 8: 4 3670000

  5. DataCamp Joining Data with data.table in R Adding an identifier column The idcol argument adds a column indicating the data.table of origin rbind("2015" = sales_2015, "2016" = sales_2016, idcol = "year") year quarter amount 1: 2015 1 3200100 2: 2015 2 2950000 3: 2015 3 2980700 4: 2015 4 3420000 5: 2016 1 3350000 6: 2016 2 3000300 7: 2016 3 3120200 8: 2016 4 3670000

  6. DataCamp Joining Data with data.table in R Adding an identifier column rbind(sales_2015, sales_2016, idcol = "year") year quarter amount 1: 1 1 3200100 2: 1 2 2950000 3: 1 3 2980700 4: 1 4 3420000 5: 2 1 3350000 6: 2 2 3000300 7: 2 3 3120200 8: 2 4 3670000

  7. DataCamp Joining Data with data.table in R Adding an identifier column rbind(sales_2015, sales_2016, idcol = TRUE) .id quarter amount 1: 1 1 3200100 2: 1 2 2950000 3: 1 3 2980700 4: 1 4 3420000 5: 2 1 3350000 6: 2 2 3000300 7: 2 3 3120200 8: 2 4 3670000

  8. DataCamp Joining Data with data.table in R Handling missing columns rbind("2015" = sales_2015, "2016" = sales_2016, idcol = "year", fill = TRUE)

  9. DataCamp Joining Data with data.table in R Handling missing columns rbind(sales_2015, sales_2016, idcol = "year") Error in rbindlist(l, use.names, fill, idcol) : Item 2 has 3 columns, inconsistent with item 1 which has 2 columns. If instead you need to fill missing columns, use set argument 'fill' to TRUE.

  10. DataCamp Joining Data with data.table in R The rbindlist() function Concatenate rows from a list of data.tables # Read in a list of data.tables table_files <- c("sales_2015.csv", "sales_2016.csv") list_of_tables <- lapply(table_files, fread) rbindlist(list_of_tables) quarter amount 1: 1 3200100 2: 2 2950000 3: 3 2980700 4: 4 3420000 5: 1 3350000 6: 2 3000300 7: 3 3120200 8: 4 3670000

  11. DataCamp Joining Data with data.table in R Adding an identifier column The idcol argument takes names from the input list names(list_of_tables) <- c("2015", "2016") rbindlist(list_of_tables, idcol = "year") year quarter amount 1: 2015 1 3200100 2: 2015 2 2950000 3: 2015 3 2980700 4: 2015 4 3420000 5: 2016 1 3350000 6: 2016 2 3000300 7: 2016 3 3120200 8: 2016 4 3670000

  12. DataCamp Joining Data with data.table in R Handling different column orders rbind("2015" = sales_2015, "2016" = sales_2016, idcol = "year", use.names = TRUE)

  13. DataCamp Joining Data with data.table in R data.tables with different column names rbind("2015" = sales_2015, "2016" = sales_2016, idcol = "year", use.names = FALSE)

  14. DataCamp Joining Data with data.table in R Pitfalls of use.names = FALSE rbind("2015" = sales_2015, "2016" = sales_2016, idcol = "year", use.names = FALSE)

  15. DataCamp Joining Data with data.table in R Differing defaults Default for rbind() is use.names = TRUE Default for rbindlist() is use.names = FALSE unless fill = TRUE .

  16. DataCamp Joining Data with data.table in R JOINING DATA WITH DATA . TABLE IN R Let's practice!

  17. DataCamp Joining Data with data.table in R JOINING DATA WITH DATA . TABLE IN R Set operations Scott Ritchie Postdoctoral Researcher in Systems Genomics

  18. DataCamp Joining Data with data.table in R Set operation functions Given two data.tables with the same columns: fintersect() : what rows do these two data.tables share in common? funion() : what is the unique set of rows across these two data.tables ? fsetdiff() : what rows are unique to this data.table ?

  19. DataCamp Joining Data with data.table in R Set operations: fintersect() Extract rows that are present in both data.tables fintersect(dt1, dt2)

  20. DataCamp Joining Data with data.table in R fintersect() and duplicate rows Duplicate rows are ignored by default: fintersect(dt1, dt2)

  21. DataCamp Joining Data with data.table in R fintersect() and duplicate rows all = TRUE : keep the number of copies present in both data.tables : fintersect(dt1, dt2, all = TRUE)

  22. DataCamp Joining Data with data.table in R Set operations: fsetdiff() Extract rows found exclusively in the first data.table fsetdiff(dt1, dt2)

  23. DataCamp Joining Data with data.table in R fsetdiff() and duplicates Duplicate rows are ignored by default: fsetdiff(dt1, dt2)

  24. DataCamp Joining Data with data.table in R fsetdiff() and duplicates all = TRUE : return all extra copies: fsetdiff(dt1, dt2, all = TRUE)

  25. DataCamp Joining Data with data.table in R Set operations: funion() Extract all rows found in either data.table : funion(dt1, dt2)

  26. DataCamp Joining Data with data.table in R funion() and duplicates Duplicate rows are ignored by default: funion(dt1, dt2)

  27. DataCamp Joining Data with data.table in R funion() and duplicates all = TRUE : return all rows: funion(dt1, dt2, all = TRUE) # rbind()

  28. DataCamp Joining Data with data.table in R Removing duplicates when combining many data.tables Two data.tables : 1. Use funion() to concatenate unique rows Three or more: 1. Concatenate all data.tables using rbind() or rbindlist() 2. Identify and remove duplicates using duplicated() and unique()

  29. DataCamp Joining Data with data.table in R JOINING DATA WITH DATA . TABLE IN R Let's practice!

  30. DataCamp Joining Data with data.table in R JOINING DATA WITH DATA . TABLE IN R Melting data.tables Scott Ritchie Postdoctoral Researcher in Systems Genomics

  31. DataCamp Joining Data with data.table in R Melting a wide data.table

  32. DataCamp Joining Data with data.table in R The melt() function Use measure.vars to specify columns to stack: melt(sales_wide, measure.vars = c("2015", "2016")) quarter variable value 1: 1 2015 3200100 2: 2 2015 2950000 3: 3 2015 2980700 4: 4 2015 3420000 5: 1 2016 3350000 6: 2 2016 3000300 7: 3 2016 3120200 8: 4 2016 3670000

  33. DataCamp Joining Data with data.table in R The melt() function Use variable.name and value.name to rename these columns in the result: melt(sales_wide, measure.vars = c("2015", "2016"), variable.name = "year", value.name = "amount") quarter year amount 1: 1 2015 3200100 2: 2 2015 2950000 3: 3 2015 2980700 4: 4 2015 3420000 5: 1 2016 3350000 6: 2 2016 3000300 7: 3 2016 3120200 8: 4 2016 3670000

  34. DataCamp Joining Data with data.table in R The melt() function Use id.vars to specify columns to keep aside melt(sales_wide, id.vars = "quarter", variable.name = "year", value.name = "amount") quarter year amount 1: 1 2015 3200100 2: 2 2015 2950000 3: 3 2015 2980700 4: 4 2015 3420000 5: 1 2016 3350000 6: 2 2016 3000300 7: 3 2016 3120200 8: 4 2016 3670000

  35. DataCamp Joining Data with data.table in R The melt() function Use both to keep only a subset of columns melt(sales_wide, id.vars = "quarter", measure.vars = "2015", variable.name = "year", value.name = "amount") quarter year amount 1: 1 2015 3200100 2: 2 2015 2950000 3: 3 2015 2980700 4: 4 2015 3420000

  36. DataCamp Joining Data with data.table in R JOINING DATA WITH DATA . TABLE IN R Let's practice!

  37. DataCamp Joining Data with data.table in R JOINING DATA WITH DATA . TABLE IN R Casting data.tables Scott Ritchie Postdoctoral Researcher in Systems Genomics

  38. DataCamp Joining Data with data.table in R Casting a long data.table sales_wide <- dcast(sales_long, quarter ~ year, value.var = "amount")

  39. DataCamp Joining Data with data.table in R The dcast() function The general form of dcast() : dcast(DT, ids ~ group, value.var = "values") | | | | | | | --> column to split | | ----------------------> group labels to split by | ----------------------------> rows to keep behind as identifiers --------------------------------> data.table to reshape

Recommend

Concatenating data Cleaning Data in Python Combining data Data may not always come in 1

Concatenating data Cleaning Data in Python Combining data Data may not always come in 1

CLEANING DATA IN PYTHON Concatenating data Cleaning Data in Python Combining data Data may not always come in 1 huge file 5 million row dataset may be broken into 5 separate datasets Easier to store and share May have

425 views • 26 slides
Concatenating data CLEAN IN G DATA IN P YTH ON Daniel Chen Instructor Combining data Data may

Concatenating data CLEAN IN G DATA IN P YTH ON Daniel Chen Instructor Combining data Data may

Concatenating data CLEAN IN G DATA IN P YTH ON Daniel Chen Instructor Combining data Data may not always come in 1 huge le 5 million row dataset may be broken into 5 separate datasets Easier to store and share May have new data for each

555 views • 31 slides
NZ Data Tables Data tables sit alongside the Active NZ main report The data tables provide

NZ Data Tables Data tables sit alongside the Active NZ main report The data tables provide

Reading the Active NZ Data Tables Data tables sit alongside the Active NZ main report The data tables provide results included in the report split by a range of population sub-groups The data tables are separated into different sheets based

351 views • 20 slides
Improving VHT MU-MIMO Communications by Concatenating Long Data Streams in Consecutive Groups

Improving VHT MU-MIMO Communications by Concatenating Long Data Streams in Consecutive Groups

Improving VHT MU-MIMO Communications by Concatenating Long Data Streams in Consecutive Groups Aitizaz Uddin Syed asyed@sfu.ca M.Eng. Presentation Communication Networks Laboratory School of Engineering Science Simon Fraser University

735 views • 25 slides
Presentation of medical data. Frequency tables and contingency tables. Visualization.

Presentation of medical data. Frequency tables and contingency tables. Visualization.

Presentation of medical data. Frequency tables and contingency tables. Visualization. Standardization. THE DATA This is a small part of real data about patients with stroke from Uzhgorod regional clinical hospital, neurological department

573 views • 7 slides
Hash tables Most data structures that were going to see are about storing and manipulating data

Hash tables Most data structures that were going to see are about storing and manipulating data

Hash tables Most data structures that were going to see are about storing and manipulating data When only operations Insert , Search and Delete as dictionary op- erations are needed, hash tables can be quite good Many variations of hash tables

1.04k views • 17 slides
Module 4: Creating Data Types and Tables Overview Creating Data Types Creating Tables

Module 4: Creating Data Types and Tables Overview Creating Data Types Creating Tables

Module 4: Creating Data Types and Tables Overview Creating Data Types Creating Tables Generating Column Values Generating Scripts Creating Data Types System-supplied Data Types Creating and Dropping User-defined Data

540 views • 17 slides
Ch 7+8: Tables, Spatial Data Arrange not much about the ones in the middle Express

Ch 7+8: Tables, Spatial Data Arrange not much about the ones in the middle Express

News VAD Ch 7: Arrange Tables Arrange tables clarification on artery vis Encode Axis Orientation Express Values Rectilinear Parallel Radial diverging colormap since doctors care about high and low values Ch 7+8: Tables, Spatial

77 views • 3 slides
TIMES TABLES HOW WE TEACH TIMES TABLES AND HOW YOU CAN HELP WHY ARE TIMES TABLES IMPORTANT?

TIMES TABLES HOW WE TEACH TIMES TABLES AND HOW YOU CAN HELP WHY ARE TIMES TABLES IMPORTANT?

TIMES TABLES HOW WE TEACH TIMES TABLES AND HOW YOU CAN HELP WHY ARE TIMES TABLES IMPORTANT? New from 2019: all children in year 4 will sit an online times table test. Times Tables knowledge and recall underpin so many different areas of

526 views • 25 slides
csci 210: Data Structures Maps and Hash Tables Summary Topics the Map ADT

csci 210: Data Structures Maps and Hash Tables Summary Topics the Map ADT

csci 210: Data Structures Maps and Hash Tables Summary Topics the Map ADT implementation of Map: hash tables Hashing READING: LC textbook chapter 14 and 15 Map ADT A Map is an abstract data structure

964 views • 36 slides
Introduction Types of Charts Data Tables Summarizing Data Cross-Tabulation

Introduction Types of Charts Data Tables Summarizing Data Cross-Tabulation

Introduction Types of Charts Data Tables Summarizing Data Cross-Tabulation Data visualization - the process of displaying data (often in large quantities) in a meaningful fashion to provide insights that will support better

1.07k views • 80 slides
Data Layouts Data Structures For a Simple Compiler Joseph Bergin 1/12/99 1 Symbol Tables

Data Layouts Data Structures For a Simple Compiler Joseph Bergin 1/12/99 1 Symbol Tables

Data Layouts Data Structures For a Simple Compiler Joseph Bergin 1/12/99 1 Symbol Tables Information about user defined names Joseph Bergin 1/12/99 2 Symbol Table Symbol Tables are organized for fast lookup. Items are typically

774 views • 46 slides
Business Statistics CONTENTS Contingency tables Independence of categorical variables 2 2

Business Statistics CONTENTS Contingency tables Independence of categorical variables 2 2

CONTINGENCY TABLES: TESTS Business Statistics CONTENTS Contingency tables Independence of categorical variables 2 2 -tables Old exam question Further study CONTINGENCY TABLES Contingency table (see Summarizing data): matrix with

283 views • 24 slides
Concatenating bipartite graphs Paul Seymour (Princeton) joint with Maria Chudnovsky, Patrick

Concatenating bipartite graphs Paul Seymour (Princeton) joint with Maria Chudnovsky, Patrick

Concatenating bipartite graphs Paul Seymour (Princeton) joint with Maria Chudnovsky, Patrick Hompe, Alex Scott and Sophie Spirkl 1 / 12 Caccetta-Hggkvist conjecture, 1978 For every integer k 1 , if G is an n-vertex digraph and every

667 views • 33 slides
Data Visualisation with Google Fusion Tables Workshop Exercises Dr Luc Small | 12 April 2017 |

Data Visualisation with Google Fusion Tables Workshop Exercises Dr Luc Small | 12 April 2017 |

Data Visualisation with Google Fusion Tables Workshop Exercises Dr Luc Small | 12 April 2017 | 1.5 Data Visualisation with Google Fusion Tables Page 1 of 33 1 Introduction Google Fusion Tables is shaping up to be a powerful and accessible data

695 views • 33 slides
Tables The main purpose of an HTML table is to organize data in a two-dimensional grid. Many web

Tables The main purpose of an HTML table is to organize data in a two-dimensional grid. Many web

CS125 Spring 2014 Interim Tables The main purpose of an HTML table is to organize data in a two-dimensional grid. Many web designers have also used tables for the layout of entire web pages. This usage is now deprecated. CSS layouts are meant

959 views • 10 slides
csci 210: Data Structures Maps and Hash Tables Summary Topics the Map ADT Map

csci 210: Data Structures Maps and Hash Tables Summary Topics the Map ADT Map

csci 210: Data Structures Maps and Hash Tables Summary Topics the Map ADT Map vs Dictionary implementation of Map: hash tables Hashing READING: GT textbook chapter 9.1 and 9.2 Binary Search Tree BST:

654 views • 40 slides
CS261 Data Structures Hash Tables Buckets/Chaining Hash Tables:

CS261 Data Structures Hash Tables Buckets/Chaining Hash Tables:

CS261 Data Structures Hash Tables Buckets/Chaining Hash Tables: Resolving Collisions There are two general approaches to resolving collisions: 1. Open address

293 views • 12 slides
SQL Workshop Insert, Update, Delete Doug Shook Test Tables So far we have only read data

SQL Workshop Insert, Update, Delete Doug Shook Test Tables So far we have only read data

SQL Workshop Insert, Update, Delete Doug Shook Test Tables So far we have only read data When testing code that may change data, it is important to NEVER use the production DB Run the statements on test tables before deploying

588 views • 31 slides
1Table A System for Managing Structured Web Data Yang Zhang with: Alon Halevy, Mike Cafarella,

1Table A System for Managing Structured Web Data Yang Zhang with: Alon Halevy, Mike Cafarella,

1Table A System for Managing Structured Web Data Yang Zhang with: Alon Halevy, Mike Cafarella, Nodira Khoussainova, Eugene Wu and Daisy Zhe Structured Web Data No tables Web is more than just text Other tables, tags, lists, etc tables Data

398 views • 22 slides
Categorical data Reasoning by diagrams R.W. Oldford Crossed data - tables The main data

Categorical data Reasoning by diagrams R.W. Oldford Crossed data - tables The main data

Categorical data Reasoning by diagrams R.W. Oldford Crossed data - tables The main data structure for crossed categorical data is a table . Each variate has a finite number of values (categories) city &lt;- c (&quot;Kitchener&quot;,

487 views • 47 slides
Working with Hash Tables Daniel Petrolito (ANZ Bank) Working With Hash Tables Daniel SAS

Working with Hash Tables Daniel Petrolito (ANZ Bank) Working With Hash Tables Daniel SAS

Working with Hash Tables Daniel Petrolito (ANZ Bank) Working With Hash Tables Daniel SAS 3+ years Programming 6 years ANZ Data Scientist Combining Data Combining tables Base table Lookup table(s) Want

486 views • 12 slides
Communicating NCI Data: Tables and Graphics and Dashboards OH MY! NCI Annual Meeting August 3,

Communicating NCI Data: Tables and Graphics and Dashboards OH MY! NCI Annual Meeting August 3,

Communicating NCI Data: Tables and Graphics and Dashboards OH MY! NCI Annual Meeting August 3, 2016 Session goals: 1. Review how NCI data are currently being presented and communicated 2. Discuss state priorities for NCI data presentation

406 views • 18 slides
csci 210: Data Structures Maps and Hash Tables Summary Topics the Map ADT Map

csci 210: Data Structures Maps and Hash Tables Summary Topics the Map ADT Map

csci 210: Data Structures Maps and Hash Tables Summary Topics the Map ADT Map vs Dictionary implementation of Map: hash tables READING: GT textbook chapter 9.1 and 9.2 Map ADT A Map is an abstract

603 views • 39 slides

More recommend