Create single objects form a table containing them

Question

Here is my problem with R:

I have a table similar to this:

TABLE_NAME          COLUM_NAME          DATA_TYPE 
table_1               DATA              DATE
table_1               NAME              VARCHAR2
table_1               SURNAME           VARCHAR2
table_2               DATA              DATE
table_2               PACK              NUMBER

what i want to do is to create 2 different table from this based on the TABLE_NAME value that will have TABLE_NAME as name. Like this

table_1

COLUM_NAME          DATA_TYPE 
   DATA              DATE
   NAME              VARCHAR2
   SURNAME           VARCHAR2

table_2

COLUM_NAME          DATA_TYPE
DATA              DATE
PACK              NUMBER

This way i can create a catalog of my tables, synonym and view of my db (with ROracle is not possible to fetch such metadata from the connection).

How can i Achieve this?

akrun · Accepted Answer · 2019-03-19T14:40:58.223

3

We can use split to create a list of data.frames

lst1 <- split(df1[-1], df1[[1]])
lst1
#$table_1
#  COLUM_NAME DATA_TYPE
#1       DATA      DATE
#2       NAME  VARCHAR2
#3    SURNAME  VARCHAR2

#$table_2
#  COLUM_NAME DATA_TYPE
#4       DATA      DATE
#5       PACK    NUMBER

Here, split is splitting the data.frame based on the factor provided (f in split). It looks for rows that have the same elements in 'TABLE_NAME' and group them together and return a list of those similar rows

edited Mar 19 '19 at 14:40

answered Mar 19 '19 at 14:29

akrun

874,273
37
540
662

1

thanks this is exactly what i was searching. Can you explain a bit more how this works? Thanks – RevivedPicard Mar 19 '19 at 14:38
Thanks perfect, and it is possible to group with multiple variable ? – RevivedPicard Mar 19 '19 at 15:02
1

@RevivedPicard Yes, you can just specify `df1[c("grp1", "grp2")]` as `f` and also to remove the unused combinations `drop = TRUE` – akrun Mar 19 '19 at 15:03

nycrefugee · Answer 2 · 2019-03-19T14:43:55.163

1

from tidyverse you can filter and them remove the first column using select:

table1 <- df %>%
  filter(TABLE_NAME == "table_1") %>% 
  select(-TABLE_NAME)

table2 <- df %>%
  filter(TABLE_NAME == "table_2") %>% 
  select(-TABLE_NAME)

You could also place in a function to work through a load of data frames:

table_fun <- function(x) {
  df %>%
    filter(TABLE_NAME == x) %>%
    select(-TABLE_NAME)
}

table_2 <- table_fun("table_2")

# A tibble: 2 x 2
  COLUM_NAME DATA_TYPE
  <chr>      <chr>    
1 DATA       DATE     
2 PACK       NUMBER

edited Mar 19 '19 at 14:43

answered Mar 19 '19 at 14:34

nycrefugee

1,629
1
10
23

Imagine there are more than 2 values in TABLE_NAME column. – zx8754 Mar 19 '19 at 15:24

tmfmnk · Answer 3 · 2019-03-19T15:30:21.103

1

With dplyr you can also try:

df %>%
 group_split(TABLE_NAME)

[[1]]
# A tibble: 3 x 3
  TABLE_NAME COLUM_NAME DATA_TYPE
  <chr>      <chr>      <chr>    
1 table_1    DATA       DATE     
2 table_1    NAME       VARCHAR2 
3 table_1    SURNAME    VARCHAR2 

[[2]]
# A tibble: 2 x 3
  TABLE_NAME COLUM_NAME DATA_TYPE
  <chr>      <chr>      <chr>    
1 table_2    DATA       DATE     
2 table_2    PACK       NUMBER

edited Mar 19 '19 at 15:30

answered Mar 19 '19 at 14:39

tmfmnk

38,881
4
47
67

@zx8754 I think it could be helpful to keep it for people looking for this in the future. – tmfmnk Mar 19 '19 at 15:34

Create single objects form a table containing them

3 Answers3