How to invert the table?

General
1 
data.frame(
       treat = c(0L, 1L),
        sqft = c(1752.55949511866, 1915.44172494172),
         age = c(37.8983701646175, 33.1706002331002),
  housevalue = c(215160.493464319, 240666.26238345)
)

Currently, there are 2 rows and 4 columns. I want it to become 3*2 table. Three rows are sqft, age, housevalue. Two columns are treat=0 and treat=1.

How should I do?

2 
dat <- data.frame(
  treat = c(0L, 1L),
  sqft = c(1752.55949511866, 1915.44172494172),
  age = c(37.8983701646175, 33.1706002331002),
  housevalue = c(215160.493464319, 240666.26238345)
)
# get rid of the spurious precision
dat <- round(dat,0)
m <- as.matrix(dat[2:4], nrow = 2, ncol = 3)
dimnames(m) <- list(c(0,1))
t(m)
#>           0      1
#> [1,]   1753   1915
#> [2,]     38     33
#> [3,] 215160 240666

Created on 2020-03-21 by the reprex package (v0.3.0)

3

Thanks, but how to change row names to control and treatment?

4

Convert it from a matrix back to a data frame. You'll get numeric row names automatically, the column names, 0 and 1 will carry over.

dat <- data.frame(
  treat = c(0L, 1L),
  sqft = c(1752.55949511866, 1915.44172494172),
  age = c(37.8983701646175, 33.1706002331002),
  housevalue = c(215160.493464319, 240666.26238345)
)
# get rid of the spurious precision
dat <- round(dat,0)
m <- as.matrix(dat[2:4], nrow = 3, ncol = 2)
dimnames(m) <- list(c(0,1))
as.data.frame(t(m))
#>        0      1
#> 1   1753   1915
#> 2     38     33
#> 3 215160 240666

Created on 2020-03-21 by the reprex package (v0.3.0)

5 
kable(m, "html", align=rep('c',3),
      col.names=c("Square Footage","Age","Housevalue"), 
      caption="SUMMARY STATISTICS") %>%
      kable_styling(full_width = F)

But what I want is 3 rows and 2 columns. So 2 column names are treatment group and control group, and 3 row names are "Square Footage","Age","Housevalue"

6

The kable argument specifies 3 columns, not the 2 columns by 3 rows given by

m <- as.data.frame(t(m))
7

So I want:

kable(m, "html", align=rep('c',2),
      col.names=c("Control group","Treatment group"), 
      caption="SUMMARY STATISTICS") %>%
      kable_styling(full_width = F)
8

so I want my table to become a 3 rows and 2 columns. Three rows are sqft, age, housevalue. Two columns are treat=0 and treat=1.

9

It's not clear what you're having trouble with: You original post shows you know how to make a data frame and choose column names -- can you not use the same approach to get the table you want?

10

my original data set is 2 rows and 4 columns (treat, sqft, age, housevalue). I need to convert it to a 3 rows and 2 columns.

Three rows are sqft, age, housevalue. Two columns are treat=0 and treat=1.

Are you clear?

11

It's what you want in your table, but it's not clear where your trouble in creating the table is: Do you know how to use the data.frame() command that appears in your first post?

12

No. I of course know how to create a table manually. But do we have a code that can convert my current data frame in the 1st post to my desired table?

13

Maybe the thing to do here is take a breath, and restate an updated reprex with your changes made since the first post. so its clear where you've gotten to and you could help identify where you are stuck.

1 Like
14

You could add a new column with the row names in it.

1 Like
15 
# current table:
data.frame(
       treat = c(0L, 1L),
        sqft = c(1752.55949511866, 1915.44172494172),
         age = c(37.8983701646175, 33.1706002331002),
  housevalue = c(215160.493464319, 240666.26238345)
)
#  treat sqft   age housevalue
#1     0 1753 37.90     215160
#2     1 1915 33.17     240666

What I want is:

#                 treat=0                treat=1
#sqft               1753                1915
#age               37.9                 33.17
#housevalue   215160            240666
16

t() gets you most of the way there. Rename the columns and drop the first row and you will have your result.

Just to clarify, t() transposes the input.

2 Likes
17

Using tidyverse, you can make use of pivot_longer and pivot_wider:

dat %>% pivot_longer(cols = c("sqft", "age", "housevalue"), names_to = "variable", values_to = "values") %>% 
  mutate(treat = paste0("treat=", treat)) %>% 
  pivot_wider(names_from = treat, values_from = values)

which gives:

#A tibble: 3 x 3

  variable   `treat=0` `treat=1`
  <chr>          <dbl>     <dbl>
1 sqft          1753.     1915. 
2 age             37.9      33.2
3 housevalue  215160.   240666.

But now I see you want to convert it into a Kable table, so you need to drop the first column:

m = dat %>% pivot_longer(cols = c("sqft", "age", "housevalue"), names_to = "variable", values_to = "values") %>% 
  mutate(treat = paste0("treat=", treat)) %>% 
  pivot_wider(names_from = treat, values_from = values) %>% 
  select(-variable)
18

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.