Numbers in 'text' format not importing correctly from Excel

General
1

Hi. Brand new to R and RStudio.

I have imported a spreadsheet from Excel. My second variable (column) contains a reference number in the format XX.XX (ie. 2 decimal places)

In Excel they are formatted as 'Text'. They are imported into R as . and 'glimpse' shows them as .

Most of the imported values display correctly, but some randomly display with 15 decimal places.

I have tried the TRUNC function on the data in Excel before formatting as text in Excel and then re-importing to R Studio, but problem persists.

Is there any simple way to manage this within RStudio (for a complete novice)?

Thanks

image

2

Hi, welcome to the forum.

You have not told us how you imported the data.

Can you post the code here?
Paste it between
```

```

which will give us nicely formatted code that is easy to read and work with.

At the moment, my guess is that whatever package you used to import the data simply ignored the Excel formatting and said, "Those looks like numbers to to me".

There are ways to get around this, here for example is a way for readxl' Cell and Column Types' but we need to know which package (library) you are using and possibly some other information.

3

Hi John
Thank you for your very quick response.

Please see the import code below. I just copied the code from the import dialogue, but then ran it from the Script.

Thanks

jrkrideau
May 27 |

  • | - |

Hi, welcome to the forum.

You have not told us how you imported the data.

Can you post the code here?
Paste it between

library(readxl)
iComments <- read_excel("~/innerintensive/innerintensivedata.xlsx",
range = "A2:P102")

4

Thanks but is better to paste code between
```

```

it gives us better formatting.

library(readxl)
iComments <- read_excel("~/innerintensive/innerintensivedata.xlsx",
range = "A2:P102")

Anyway, I think I may have a solution from the link I supplied earlier.

I am assuming that all the rest of the variables are being read in correctly.

What we need to do is specify the formats of all the columns, firmly telling {readxl} that Track is text. So try this:

## Build col_types vector
xx <- rep("guess,", 14)
cols <- c("guess", "text", xx)

## Read Excel file
iComments <- read_excel("~/innerintensive/innerintensivedata.xlsx", col_types = cols, range = "A2:P102")

AS a matter of good programming style and to clean up any dubiously formatted variable names such as ones with spaces I would recommend installing the {janitor} package and running its `clean_names() function.

install.packages("janitor"

This would give you

library(readxl)
library(janitor)

iComments <- read_excel("~/innerintensive/innerintensivedata.xlsx", col_types = cols, range = "A2:P102") |>  clean_names()

Good luck.

5

Hi John

This evening I was doing some work on the source data for my spreadsheet (via Google Forms > Google Sheets) before I copied it to Excel.

I may have something to do with how the 'options' were created in Google Forms multiple-choice question(typed in vs. copy and paste), because I noticed that some of the data/observations in Google Sheets also had the 15 decimal places. These 'observations' 01.01 to 01.20 were then used as column headings in my spreadsheet.

Whatever the source of the 15 decimal places, it would be very helpful to know how to get rid of them from within RStudio so all my Varaiables are in XX.XX format only.

Thanks

6

This note arrived just as I was replying to your earlier one!

I don"t know what to think about this problem as I have never used Google Forms or Sheets, and as a Linux user I don't even have Excel. :laughing:

I'd be happy to take a look at the problem; I can read Excel files using LibreOffice Calc but I or others here who may be better qualified to help probably need to see the Excel file or a truncated version of it.

If the data is not confidential, would it be possible to post it at a file hosting site such as dropbox or mediafire?

You could also just email it to me at jrkrideau at gmail period com.

7

If you just want to round the numbers to two decimal places, try:

iComments$Track = round(iComments$Track, 2)

The issue itself probably arises from floating-point representation in Google Forms/Sheets or Excel.

8

My first thought was YES! but if

 These 'observations' 01.01 to 01.20 were then used as column headings in my spreadsheet.

I think this could really mangle nortonr's variable names. It may be that they will just have to rename the tibble. Annoying and tedious with 16 variables but not impossible.