[R] R interpreting numeric field as a boolean field

[Duncan Murdoch]

On 30/01/2024 11:10 a.m., Paul Bernal wrote:
> Dear friends,
> 
> Hope you are doing well. I am currently using R version 4.3.2, and I have a
> .xlsx file that has 46 sheets on it. I basically combined  all 46 sheets
> and read them as a single dataframe in R using package rio.
> 
> I read a solution using package readlx, as suggested in a StackOverflow
> discussion as follows:
> df <- read_excel(path = filepath, sheet = sheet_name, guess_max = 100000).
> Now, when you have so many sheets (46 in my case) in an Excel file, the rio
> methodology is more practical.
> 
> This is what I did:
> path =
> "C:/Users/myuser/Documents/DataScienceF/Forecast_and_Econometric_Analysis_FIGI
> (4).xlsx"
> figidat = import_list(path, rbind = TRUE) #here figidat refers to my dataset
> 
> Now, it successfully imports and merges all records, however, some fields
> (despite being numeric), R interprets as a boolean field.
> 
> Here is the structure of the field that is causing me problems (I apologize
> for the length):
> structure(list(StoreCharges = c(NA, NA, NA, NA, NA, NA, NA, NA,
> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
...
> FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, NA, NA,
> FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE,
> FALSE, FALSE, FALSE)), class = c("tbl_df", "tbl", "data.frame"
> ), row.names = c(NA, -7033L))
> 
> As you can see, when I do the dput, it gives me a bunch of TRUE and FALSE
> values, when in reality I have records with value $0, records with amounts
>> $0 and also a bunch of blank records.
> 
> Any help will be greatly appreciated.

I don't know how read_excel() determines column types, but some 
functions look only at the first n rows to guess the type.  It appears 
you have a lot of NA values at the start.  That is a logical value, so 
that might be what is going wrong.

In read.table() and related functions, you can specify the types of 
column explicitly.  It sounds as though that's what you should do if 
read_excel() offers that as a possibility.

Duncan Murdoch