CODEWITHSUNDEEP
rexcelimportexport
Jack
Jack

Reputation: 857

Why R is only reading half of the row that is exported from Excel?

I am exporting data on inflation from Excel to R that extends from 1960 to 2020 in 29 countries. The issue is that R does not read the row of the years that follows 2007.

enter image description here

Another issue is that the years columns are read as character by R from 1960 until 1997. Then it changes to double as you can see in the picture below.

enter image description here

I tried to change the format of the row of the years to number in excel, but it did not solve the problem.

I split the data and copied the years that follows 2007 to another sheet. Then i was able to export it (years of 2008 to 2020) separately and R was able to read the first row of the years with no problem whatsoever. I am not sure what that means. But my aim is to export all the data at once from the same sheet. Could someone help me with that please?

Here is the code:

library(readxl)
CPI <- read_excel("~/Desktop/Excel sheets/CPI.xlsx")

Here is the data:

https://docs.google.com/spreadsheets/d/14Xrr_0Beul4fA2LD6YIeEiBP58P0SMoF/edit?usp=sharing&ouid=100310480084555611403&rtpof=true&sd=true

Upvotes: 1

Views: 71

Answers (1)

Tech Commodities
Tech Commodities

Reputation: 1959

Your issue is that missing values are denoted with "..", so read_excel() considers these cells as characters and so marks the whole column as a character, where there is a "..".

Easily solved, though, specify the na string, e.g., CPI <- read_excel(path = "...", na = "..")

Upvotes: 2

Related Questions