R language combining different tables

Question

I have one table

grade       gradepoint
  o             10
  A              9
  B              8

I have second table

subjectname       credits
  englishI          3
   mI               3
   AC Lab           2

I have third table containing grades for different subjects like this

rollno     englishI        mI             ACLab
  1           A            B               o
  2           B            B               o

now I need a table containing subcolumns in each column in third table

rollno      englishI                        mI                    ACLab         
         grade   gradepoint Credits  grade  gradepoint Credit   grade gradepoint credit 
1           A      9           3      B       8          3       o      10                                             2
2           B      8           3      B       8          3       o      10                     2

how to join these dataframes into one dataframe and how to access subcolumns in a column

Welcome to StackOverflow! Please read the info about [how to ask a good question](http://stackoverflow.com/help/how-to-ask) and how to give a [reproducible example](http://stackoverflow.com/questions/5963269). This will make it much easier for others to help you. — Sotos, Jul 13 '17 at 06:37
I have first 3 tables(inputs) and I want to combine 3 tables data in one table like 4 th one here(output). — pavan kumar, Jul 13 '17 at 06:57
That isn't addressing the suggestion. It's good to know that you "*want to combine 3 tables data in one table*", but your examples are not sufficient or formatted well for easy demonstration and use by us. If you read the links suggested by Sotos, you'll see suggestions to use commands like `dput(head(x))`, *edited into your question*, so that we can copy/paste and more easily demonstrate techniques with appropriate sample data. Those links also suggest including code that you've tried, so we have a starting point (and we can see effort on your part). — r2evans, Jul 13 '17 at 07:17

www · Answer 1 · 2017-07-17T04:00:18.207

A solution using dplyr and tidyr. Notice that like comments mentioned subcolumns are not well supported in data frame, so I created combined column names, such as englishI_credits, for each column. dt4 is the final output.

# Load package
library(dplyr)
library(tidyr)

# Create table 1
df1 <- read.table(text = "grade       gradepoint
  o             10
                  A              9
                  B              8",
                  header = TRUE, stringsAsFactors = FALSE)

# Create table 2
df2 <- read.table(text = "subjectname       credits
  englishI          3
                  mI               3
                  ACLab            2",
                  header = TRUE, stringsAsFactors = FALSE)

# Create table 3
df3 <- read.table(text = "rollno     englishI        mI             ACLab
  1           A            B               o
                  2           B            B               o",
                  header = TRUE, stringsAsFactors = FALSE)

# Process the data
df4 <- df3 %>%
  gather(subjectname, grade, -rollno) %>%
  left_join(df1, by = "grade") %>%
  left_join(df2, by = "subjectname") %>%
  gather(info, value, grade:credits) %>%
  unite(item, subjectname, info) %>%
  spread(item, value) %>%
  # Convert character numbers to numeric
  mutate_if(function(Col){all(!grepl("\\D", Col))}, as.numeric)

thank you. so I think no subcolumns are possible.please explain what is the last line do i.e.,mutate_if(function(Col){all(!grepl("\\D", Col))}, as.numeric) — pavan kumar, Jul 17 '17 at 05:52
It determines if a column's elements are all numbers. It `TRUE`, convert it to numeric. You could remove that line if you don't need those columns to be numeric. — www, Jul 17 '17 at 05:58

R language combining different tables

1 Answers1