I have a table of this nature and I want to transform it to another format, how can I go through this in R where Variable 4 - 8 represents years like 2001 - 2005
|Country|Code|Variable1|Variable2|Variable3|Variable4|Variable5|Variable6|Variable7|Variable8|
|-------|----|---------|---------|---------|---------|---------|---------|---------|---------|
|Brazil | BR| 10| 20| 30| 40| 50| 60| 70| 80|
|-------|----|---------|---------|---------|---------|---------|---------|---------|---------|
|Niger | NG| 80| 70| 10| 00| 70| 30| 20| 50|
|-------|----|---------|---------|---------|---------|---------|---------|---------|---------|
|Kenya | KE| 580| 270| 510| 600| 770| 730| 220| 650|
|-------|----|---------|---------|---------|---------|---------|---------|---------|---------|
|Taiwan | TW| 180| 270| 310| 400| 570| 630| 720| 850|
|-------|----|---------|---------|---------|---------|---------|---------|---------|---------|
|Benin | BN| 910| 820| 430| 40| 350| 260| 170| 980|
|-------|----|---------|---------|---------|---------|---------|---------|---------|---------|
What I want should look like the one below after processing in R
|Country|Code|Year|New Column to Put the digits above|
|-------|----|----|----------------------------------|
|Brazil |BR |2001| 40 |
|-------|----|----|----------------------------------|
|Brazil |BR |2002| 50 |
|-------|----|----|----------------------------------|
|Brazil |BR |2003| 50 |
|-------|----|----|----------------------------------|
|Brazil |BR |2004| 50 |
|-------|----|----|----------------------------------|
|Brazil |BR |2005| 50 |
|-------|----|----|----------------------------------|
|Niger |NG |2001| 00 |
|-------|----|----|----------------------------------|
|Niger |NG |2002| 70 |
|-------|----|----|----------------------------------|
|Niger |NG |2003| 30 |
|-------|----|----|----------------------------------|
|Niger |NG |2004| 20 |
|-------|----|----|----------------------------------|
|Niger |NG |2005| 50 |
|-------|----|----|----------------------------------|
I have tried to read the first table in an excel format using R as follows
library(readxl)
library(here)
library(data.table)
Table1 <- read_excel("dataentry.xlsx")
ColNames <- colnames(Table1[5:ncol(Table1)])
# Creating a table by appending the data via while loop
while (i <- nrow(EdAttainment)){
Table2 <- rep(data.frame(c(Table1$Country`[i],Table1$Code[i]),
length(colnames(Table1[5:ncol(Table1)])))
i = i+1
}
This loop is endless and it doesn't yield any dataframe. Any idea is highly appreciated. Thanks for your time