 # How to invert the 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)
)
``````

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?

``````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)

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

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)

``````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"

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

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

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)
``````

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.

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?

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?

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?

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?

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

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

1 Like
``````# 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
``````

`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

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)
``````

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