Split Data Into Multiple Worksheets Based On Selected Column in R [on hold]
up vote
0
down vote
favorite
I have a table in excel with 5 columns. One of the columns is called "name" and included 5 different names. I would like to split this table base on different names into 5 different sheets. I would like to do it with R, and I am looking for a proper script. Can anyone help?
r excel
edited Nov 8 at 8:45
jwpfox
3,79293640
asked Nov 8 at 8:33
Mitra
1
New contributor
Mitra is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
put on hold as off-topic by jwpfox, MLavoie, Nicholas K, greg-449, shim Nov 8 at 15:31
This question appears to be off-topic. The users who voted to close gave this specific reason:
- "Questions seeking debugging help ("why isn't this code working?") must include the desired behavior, a specific problem or error and the shortest code necessary to reproduce it in the question itself. Questions without a clear problem statement are not useful to other readers. See: How to create a Minimal, Complete, and Verifiable example." – jwpfox, Nicholas K, shim
If this question can be reworded to fit the rules in the help center, please edit the question.
add a comment |
up vote
0
down vote
favorite
I have a table in excel with 5 columns. One of the columns is called "name" and included 5 different names. I would like to split this table base on different names into 5 different sheets. I would like to do it with R, and I am looking for a proper script. Can anyone help?
r excel
edited Nov 8 at 8:45
jwpfox
3,79293640
asked Nov 8 at 8:33
Mitra
1
New contributor
Mitra is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
put on hold as off-topic by jwpfox, MLavoie, Nicholas K, greg-449, shim Nov 8 at 15:31
This question appears to be off-topic. The users who voted to close gave this specific reason:
- "Questions seeking debugging help ("why isn't this code working?") must include the desired behavior, a specific problem or error and the shortest code necessary to reproduce it in the question itself. Questions without a clear problem statement are not useful to other readers. See: How to create a Minimal, Complete, and Verifiable example." – jwpfox, Nicholas K, shim
If this question can be reworded to fit the rules in the help center, please edit the question.
Welcome to StackOverflow! Please read the info about how to ask a good question and how to give a reproducible example. This will make it much easier for others to help you.
– Sotos
Nov 8 at 8:59
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
I have a table in excel with 5 columns. One of the columns is called "name" and included 5 different names. I would like to split this table base on different names into 5 different sheets. I would like to do it with R, and I am looking for a proper script. Can anyone help?
r excel
edited Nov 8 at 8:45
jwpfox
3,79293640
asked Nov 8 at 8:33
Mitra
1
New contributor
Mitra is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
I have a table in excel with 5 columns. One of the columns is called "name" and included 5 different names. I would like to split this table base on different names into 5 different sheets. I would like to do it with R, and I am looking for a proper script. Can anyone help?
r excel
r excel
edited Nov 8 at 8:45
jwpfox
3,79293640
asked Nov 8 at 8:33
Mitra
1
New contributor
Mitra is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
edited Nov 8 at 8:45
jwpfox
3,79293640
asked Nov 8 at 8:33
Mitra
1
New contributor
Mitra is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
edited Nov 8 at 8:45
jwpfox
3,79293640
edited Nov 8 at 8:45
jwpfox
3,79293640
edited Nov 8 at 8:45
jwpfox
3,79293640
3,79293640
asked Nov 8 at 8:33
Mitra
1
New contributor
Mitra is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked Nov 8 at 8:33
Mitra
1
asked Nov 8 at 8:33
Mitra
1
1
New contributor
Mitra is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
Mitra is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Mitra is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
put on hold as off-topic by jwpfox, MLavoie, Nicholas K, greg-449, shim Nov 8 at 15:31
This question appears to be off-topic. The users who voted to close gave this specific reason:
- "Questions seeking debugging help ("why isn't this code working?") must include the desired behavior, a specific problem or error and the shortest code necessary to reproduce it in the question itself. Questions without a clear problem statement are not useful to other readers. See: How to create a Minimal, Complete, and Verifiable example." – jwpfox, Nicholas K, shim
If this question can be reworded to fit the rules in the help center, please edit the question.
put on hold as off-topic by jwpfox, MLavoie, Nicholas K, greg-449, shim Nov 8 at 15:31
This question appears to be off-topic. The users who voted to close gave this specific reason:
- "Questions seeking debugging help ("why isn't this code working?") must include the desired behavior, a specific problem or error and the shortest code necessary to reproduce it in the question itself. Questions without a clear problem statement are not useful to other readers. See: How to create a Minimal, Complete, and Verifiable example." – jwpfox, Nicholas K, shim
If this question can be reworded to fit the rules in the help center, please edit the question.
Welcome to StackOverflow! Please read the info about how to ask a good question and how to give a reproducible example. This will make it much easier for others to help you.
– Sotos
Nov 8 at 8:59
add a comment |
Welcome to StackOverflow! Please read the info about how to ask a good question and how to give a reproducible example. This will make it much easier for others to help you.
– Sotos
Nov 8 at 8:59
Welcome to StackOverflow! Please read the info about how to ask a good question and how to give a reproducible example. This will make it much easier for others to help you.
– Sotos
Nov 8 at 8:59
Welcome to StackOverflow! Please read the info about how to ask a good question and how to give a reproducible example. This will make it much easier for others to help you.
– Sotos
Nov 8 at 8:59
add a comment |
1 Answer
1
active
oldest
votes
up vote
0
down vote
Here's my try:
library(openxlsx)
#XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
# Make adjustments here
#XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
# Path to data
data_path <- "C:/R Scripts/R Upload/" # Adjust path here, don't forget to end with "/"
# Name of Excel-worksheets
data_name <- "Sample_Data"
data_new_name <- "New_File"
# Name of split-variable
data_split_var <- "name"
# Remove split-variable in new sheets?
split_rmv <- TRUE
#XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
# Code
#XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
# Load data
data <- read.xlsx(paste0(data_path, data_name))
# Split
data_list <- split(data, get("data")[,data_split_var])
# Remove split-variable
if(split_rmv){
data_list <- lapply(data_list, function(x){x <- x[!colnames(x) == data_split_var]})
}
# Save as new Excel workbook in the same location
setwd(data_path)
wb <- createWorkbook(data_new_name)
lapply(names(data_list), function(x){addWorksheet(wb, x)})
lapply(names(data_list), function(x){writeData(wb, x, data_list[x], colNames = TRUE, keepNA = FALSE)})
saveWorkbook(wb, paste(data_new_name, ".xlsx", sep = ""), overwrite = TRUE)
In case you don't want to get rid of your split-variable set split_rmv to FALSE. Does this solve your problem?
answered Nov 8 at 8:51
alex_555
481212
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
0
down vote
Here's my try:
library(openxlsx)
#XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
# Make adjustments here
#XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
# Path to data
data_path <- "C:/R Scripts/R Upload/" # Adjust path here, don't forget to end with "/"
# Name of Excel-worksheets
data_name <- "Sample_Data"
data_new_name <- "New_File"
# Name of split-variable
data_split_var <- "name"
# Remove split-variable in new sheets?
split_rmv <- TRUE
#XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
# Code
#XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
# Load data
data <- read.xlsx(paste0(data_path, data_name))
# Split
data_list <- split(data, get("data")[,data_split_var])
# Remove split-variable
if(split_rmv){
data_list <- lapply(data_list, function(x){x <- x[!colnames(x) == data_split_var]})
}
# Save as new Excel workbook in the same location
setwd(data_path)
wb <- createWorkbook(data_new_name)
lapply(names(data_list), function(x){addWorksheet(wb, x)})
lapply(names(data_list), function(x){writeData(wb, x, data_list[x], colNames = TRUE, keepNA = FALSE)})
saveWorkbook(wb, paste(data_new_name, ".xlsx", sep = ""), overwrite = TRUE)
In case you don't want to get rid of your split-variable set split_rmv to FALSE. Does this solve your problem?
answered Nov 8 at 8:51
alex_555
481212
add a comment |
up vote
0
down vote
Here's my try:
library(openxlsx)
#XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
# Make adjustments here
#XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
# Path to data
data_path <- "C:/R Scripts/R Upload/" # Adjust path here, don't forget to end with "/"
# Name of Excel-worksheets
data_name <- "Sample_Data"
data_new_name <- "New_File"
# Name of split-variable
data_split_var <- "name"
# Remove split-variable in new sheets?
split_rmv <- TRUE
#XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
# Code
#XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
# Load data
data <- read.xlsx(paste0(data_path, data_name))
# Split
data_list <- split(data, get("data")[,data_split_var])
# Remove split-variable
if(split_rmv){
data_list <- lapply(data_list, function(x){x <- x[!colnames(x) == data_split_var]})
}
# Save as new Excel workbook in the same location
setwd(data_path)
wb <- createWorkbook(data_new_name)
lapply(names(data_list), function(x){addWorksheet(wb, x)})
lapply(names(data_list), function(x){writeData(wb, x, data_list[x], colNames = TRUE, keepNA = FALSE)})
saveWorkbook(wb, paste(data_new_name, ".xlsx", sep = ""), overwrite = TRUE)
In case you don't want to get rid of your split-variable set split_rmv to FALSE. Does this solve your problem?
answered Nov 8 at 8:51
alex_555
481212
add a comment |
up vote
0
down vote
up vote
0
down vote
Here's my try:
library(openxlsx)
#XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
# Make adjustments here
#XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
# Path to data
data_path <- "C:/R Scripts/R Upload/" # Adjust path here, don't forget to end with "/"
# Name of Excel-worksheets
data_name <- "Sample_Data"
data_new_name <- "New_File"
# Name of split-variable
data_split_var <- "name"
# Remove split-variable in new sheets?
split_rmv <- TRUE
#XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
# Code
#XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
# Load data
data <- read.xlsx(paste0(data_path, data_name))
# Split
data_list <- split(data, get("data")[,data_split_var])
# Remove split-variable
if(split_rmv){
data_list <- lapply(data_list, function(x){x <- x[!colnames(x) == data_split_var]})
}
# Save as new Excel workbook in the same location
setwd(data_path)
wb <- createWorkbook(data_new_name)
lapply(names(data_list), function(x){addWorksheet(wb, x)})
lapply(names(data_list), function(x){writeData(wb, x, data_list[x], colNames = TRUE, keepNA = FALSE)})
saveWorkbook(wb, paste(data_new_name, ".xlsx", sep = ""), overwrite = TRUE)
In case you don't want to get rid of your split-variable set split_rmv to FALSE. Does this solve your problem?
answered Nov 8 at 8:51
alex_555
481212
Here's my try:
library(openxlsx)
#XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
# Make adjustments here
#XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
# Path to data
data_path <- "C:/R Scripts/R Upload/" # Adjust path here, don't forget to end with "/"
# Name of Excel-worksheets
data_name <- "Sample_Data"
data_new_name <- "New_File"
# Name of split-variable
data_split_var <- "name"
# Remove split-variable in new sheets?
split_rmv <- TRUE
#XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
# Code
#XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
# Load data
data <- read.xlsx(paste0(data_path, data_name))
# Split
data_list <- split(data, get("data")[,data_split_var])
# Remove split-variable
if(split_rmv){
data_list <- lapply(data_list, function(x){x <- x[!colnames(x) == data_split_var]})
}
# Save as new Excel workbook in the same location
setwd(data_path)
wb <- createWorkbook(data_new_name)
lapply(names(data_list), function(x){addWorksheet(wb, x)})
lapply(names(data_list), function(x){writeData(wb, x, data_list[x], colNames = TRUE, keepNA = FALSE)})
saveWorkbook(wb, paste(data_new_name, ".xlsx", sep = ""), overwrite = TRUE)
In case you don't want to get rid of your split-variable set split_rmv to FALSE. Does this solve your problem?
answered Nov 8 at 8:51
alex_555
481212
edited Nov 8 at 9:33
answered Nov 8 at 8:51
alex_555
481212
answered Nov 8 at 8:51
alex_555
481212
answered Nov 8 at 8:51
alex_555
481212
481212
add a comment |
add a comment |
Welcome to StackOverflow! Please read the info about how to ask a good question and how to give a reproducible example. This will make it much easier for others to help you.
– Sotos
Nov 8 at 8:59