How to Create a Spreadsheet?

I know how to make a table in R markdown:

1 | 2 | 3
:- |:-: | -:
4 | 5 | 6
7 | 8 | 9

1 2 3
4 5 6
7 8 9

This is a table. I want to create the equivalent but a spreadsheet. I installed the excelR package. I tried this as an example, but it didn't produce any output:

df = cbind(A = c('a', 'b', 'c', 'd','NA'),
                 B = c('a', 'c', 'd', 'e','g'), C = c('e', 'b', 'a', 'f','NA'))

df <- data.frame (df)

There are a variety of excel based packages. One of the better and more consistent ones are xlsx. Have a look here at some common operations and ways of working with it: R xlsx package : A quick start guide to manipulate Excel files in R - Easy Guides - Wiki - STHDA

I don't want to work with excel files. I mean, I want to write in my R markdown document, and have it export to a pdf document.

Can you explain more what you have in mind? It sounds like you want a pdf that looks like a spreadsheet. Does that mean something specific to you regarding formatting?

You should be able to do something like this to get a pdf with a table in it:

---
title: "Untitled"
output: pdf_document
---

```{r}
knitr::kable(mtcars)
```

Yes I want something that looks like that. But how do I use my values from the table I made above instead of some random car data? Could you show me the equivalent spreadsheet for that table using kable?

If you saved your data in a table called df, then:

```{r}
knitr::kable(df)
```

I tried this:

```{r, results='asis'}
cat('Table: (\#tab:df)Numbers

1 | 2 | 3
:- |:-: | -:
4 | 5 | 6
7 | 8 | 9
')
```

```{r spreadsheet}
knitr::kable(df)
```

The table could properly render, but the spreadsheet couldn't. How do I call my table "df"?

It sounds like your underlying question is "how do I load tabular data into R?" There are many tutorials for many ways to do this. The best option will depend on how you have your data and what's most convenient.

It's worth noting that data frame variables in R are supposed to start with a letter or a dot, so you may get unexpected behavior if you try to name a column "1".

https://stat.ethz.ch/R-manual/R-devel/library/base/html/make.names.html

One convenient way to create a data frame, which can then be displayed in your markdown document, is to use the tribble function from the tibble package. The variable names should are preceded with a ~ symbol, but otherwise everything just gets a separating comma.

library(tibble)
df <- tribble(~A, ~B,
               4,  5,
               6,  7)

Thank you. This works quite well. Since it shows A and B at the top, how can I also do something similar for the very left of each row? Also, anything other than numbers are not allowed unfortunately.

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

If you have a query related to it or one of the replies, start a new topic and refer back with a link.