Re: [R] automatic convert list to dataframe
Hello Rui and Bert,
This is a great help! Thank you both!
Here is my job assignment: boss accumulate many years excel reports from same
lab. Each report contents multiple data sheets. Those sheets may/may not
updated, and some sheets are new. Good news is, the same testing result data
will be saved in a data sheet and assign the same sheet name with same fields
name, but does not in same file. Boss wants me to get non.duplicate data for
difference testing from those excel files. So, I want to load all data into R
and assign each data frame name as filename_sheetname. I use the 2nd suggestion
from Rui to get this step done! Thanks Rui again. This method save a lot of
time for me. Now, I can combine data frames (same _sheetname) together and
remove duplicate.
Best,
Kai
On Monday, October 3, 2022 at 03:41:45 PM PDT, Bert Gunter
wrote:
... But why not dispense with multiple files, name juggling, and environments
by simply putting everything in one list of lists (lists are recursive
structures!):
all_files <- lapply(filenames, function(nm)import_list(nm,...))names(all_files)
<- filenames
## Note: the ... are any optional parameters to import_list() that may be
needed.
## Perhaps none.
all_files will then be a single list, each of whose components is a list of
data frames for all the sheets in the file. I would think that such a list
could easily be accessed and manipulated via list indexing and *apply-family
functions. Just seems like a more straightforward approach to me (assuming I
haven't misunderstood, of course).
Bert
On Mon, Oct 3, 2022 at 3:04 PM Rui Barradas wrote:
Hello,
Here are two more attempts at solving the problem.
1. Instead of having 30-40 data.frames per file in the globalenv, not a
good idea, the following code will create as many lists as you have
files and each list is a list of df's.
temp_list <- vector("list", length = length(filenames))
for(i in seq_along(filenames)){
xlfile <- filenames[i]
temp_list[[i]] <- import_list(paste0(xlfile, ".xlsx"))
}
list2env(temp_list, envir = .GlobalEnv)
rm(temp_list)
Now you can access the data with code like
file1$dx1 # a data.frame, first sheet in excel file1
file1[["dx1"]] # equivalent
2. I cannot see a reason why the following shouldn't work. It creates
lots of data.frames in the globalenv, 30-40 per file. This makes the
globalenv messy and is not recommended.
temp_list <- vector("list", length = length(filenames))
for(i in seq_along(filenames)){
# import the data
xlfile <- filenames[i]
temp_list[[i]] <- import_list(paste0(xlfile, ".xlsx"))
# now take care of the names
new_names <- paste(xlfile, names(temp_list[[i]]), sep = "_")
names(temp_list[[i]]) <- new_names
# create the current data.frames in the globalenv
list2env(temp_list[[i]], envir = .GlobalEnv)
}
rm(temp_list)
Hope this helps,
Rui Barradas
Às 20:51 de 03/10/2022, Kai Yang escreveu:
> Hi Rui,
> I copied "list2env(i, envir = .GlobalEnv)" to the code, but I got the same
> error message of "first argument must be a named list". Maybe list2env cannot
> put in loop? the code works very well outside of for loop.
> One more thing, the difference file may have same sheet name. that's why I
> want to add file name in front of sheet name to avoid overwriting. It still
> works well outside of loop, but doesn't work in loop. I don't know how to fix
> the problems.
> Thank you,
> Kai
>
> On Monday, October 3, 2022 at 12:09:04 PM PDT, Rui Barradas
> wrote:
>
> Hello,
>
> If in each iteration i is a list, try removing the call to names().
> Try, in the loop,
>
>
> list2env(i, envir = .GlobalEnv)
>
>
> The error message is telling that list2env's first argument must be a
> named list and names(i) is an unnamed vector, it's i that's the named
> list (you even changed its names in the previous instruction).
>
> Hope this helps,
>
> Rui Barradas
>
> Às 18:38 de 03/10/2022, Kai Yang escreveu:
>> Hi Rui,
>> list2env(file1, envir = .GlobalEnv) is worked very well. Thank you.
>>
>> But when I tried to put the sample code into for loop. I got error message:
>> for(i in filenames){
>> assign(i, import_list(paste0(i, ".xlsx", sep="")))
>> names(i) <- paste(i, names(i), sep = "_")
>> list2env(names(i), envir = .GlobalEnv)
>> }
>> Error in list2env(names(i), envir = .GlobalEnv) : first argument must be a
>> named list
>>
>> It seems I cannot put names(i) into for loop, Could you please help me to
>> debug it?
>> Thank you,Kai On Monday, October 3, 2022 at 10:14:25 AM PDT, Rui Barradas
>> wrote:
>>
>> Hello,
>>
>>
>> list2env(file1, envir = .GlobalEnv)
>>
>>
>> will create data.frames dx1, dx2, etc, in the global environment.
>> If you really need the names file1_dx1, file1_dx2, etc, you can first
>> change the names
>>
>>
>> names(file1) <- paste("file1", names(file1), sep = "_")
>>
>>
>> and then run list2env like above.
>>
>> Hope this helps,
>>
>> Rui Barradas
Re: [R] automatic convert list to dataframe
... But why not dispense with multiple files, name juggling, and
environments by simply putting everything in one list of lists (lists are
recursive structures!):
all_files <- lapply(filenames, function(nm)import_list(nm,...))
names(all_files) <- filenames
## Note: the ... are any optional parameters to import_list() that may be
needed.
## Perhaps none.
all_files will then be a single list, each of whose components is a list of
data frames for all the sheets in the file. I would think that such a list
could easily be accessed and manipulated via list indexing and
*apply-family functions. Just seems like a more straightforward approach to
me (assuming I haven't misunderstood, of course).
Bert
On Mon, Oct 3, 2022 at 3:04 PM Rui Barradas wrote:
> Hello,
>
> Here are two more attempts at solving the problem.
>
> 1. Instead of having 30-40 data.frames per file in the globalenv, not a
> good idea, the following code will create as many lists as you have
> files and each list is a list of df's.
>
>
> temp_list <- vector("list", length = length(filenames))
> for(i in seq_along(filenames)){
>xlfile <- filenames[i]
>temp_list[[i]] <- import_list(paste0(xlfile, ".xlsx"))
> }
> list2env(temp_list, envir = .GlobalEnv)
> rm(temp_list)
>
> Now you can access the data with code like
>
>
> file1$dx1 # a data.frame, first sheet in excel file1
> file1[["dx1"]] # equivalent
>
>
> 2. I cannot see a reason why the following shouldn't work. It creates
> lots of data.frames in the globalenv, 30-40 per file. This makes the
> globalenv messy and is not recommended.
>
>
> temp_list <- vector("list", length = length(filenames))
> for(i in seq_along(filenames)){
># import the data
>xlfile <- filenames[i]
>temp_list[[i]] <- import_list(paste0(xlfile, ".xlsx"))
># now take care of the names
>new_names <- paste(xlfile, names(temp_list[[i]]), sep = "_")
>names(temp_list[[i]]) <- new_names
># create the current data.frames in the globalenv
>list2env(temp_list[[i]], envir = .GlobalEnv)
> }
> rm(temp_list)
>
>
> Hope this helps,
>
> Rui Barradas
>
>
> Às 20:51 de 03/10/2022, Kai Yang escreveu:
> > Hi Rui,
> > I copied "list2env(i, envir = .GlobalEnv)" to the code, but I got the
> same error message of "first argument must be a named list". Maybe list2env
> cannot put in loop? the code works very well outside of for loop.
> > One more thing, the difference file may have same sheet name. that's why
> I want to add file name in front of sheet name to avoid overwriting. It
> still works well outside of loop, but doesn't work in loop. I don't know
> how to fix the problems.
> > Thank you,
> > Kai
> >
> > On Monday, October 3, 2022 at 12:09:04 PM PDT, Rui Barradas <
> ruipbarra...@sapo.pt> wrote:
> >
> > Hello,
> >
> > If in each iteration i is a list, try removing the call to names().
> > Try, in the loop,
> >
> >
> > list2env(i, envir = .GlobalEnv)
> >
> >
> > The error message is telling that list2env's first argument must be a
> > named list and names(i) is an unnamed vector, it's i that's the named
> > list (you even changed its names in the previous instruction).
> >
> > Hope this helps,
> >
> > Rui Barradas
> >
> > Às 18:38 de 03/10/2022, Kai Yang escreveu:
> >>Hi Rui,
> >> list2env(file1, envir = .GlobalEnv) is worked very well. Thank you.
> >>
> >> But when I tried to put the sample code into for loop. I got error
> message:
> >> for(i in filenames){
> >> assign(i, import_list(paste0(i, ".xlsx", sep="")))
> >> names(i) <- paste(i, names(i), sep = "_")
> >> list2env(names(i), envir = .GlobalEnv)
> >> }
> >> Error in list2env(names(i), envir = .GlobalEnv) : first argument must
> be a named list
> >>
> >> It seems I cannot put names(i) into for loop, Could you please help me
> to debug it?
> >> Thank you,KaiOn Monday, October 3, 2022 at 10:14:25 AM PDT, Rui
> Barradas wrote:
> >>
> >>Hello,
> >>
> >>
> >> list2env(file1, envir = .GlobalEnv)
> >>
> >>
> >> will create data.frames dx1, dx2, etc, in the global environment.
> >> If you really need the names file1_dx1, file1_dx2, etc, you can first
> >> change the names
> >>
> >>
> >> names(file1) <- paste("file1", names(file1), sep = "_")
> >>
> >>
> >> and then run list2env like above.
> >>
> >> Hope this helps,
> >>
> >> Rui Barradas
> >>
> >> Às 16:51 de 03/10/2022, Kai Yang via R-help escreveu:
> >>> Hi R team,
> >>> I can use rio package to read excel file into R as a list. The excel
> file content multiple sheets (30 - 40 data sheets). I can convert each data
> elements into dataframe manually. I have multiple excel files with multiple
> data sheets. I need to load them into R and do the comparison for same
> sheet name from difference excel file. My current code is:
> >>> library(rio) setwd ("C:/temp")
> >>> filenames <- gsub("\\.xlsx$","", list.files(pattern="\\.xlsx$"))
> >>> for(i in filenames){
> >>>assign(i, import_list(paste0(i, ".xlsx", sep="")))
> >>> }
> >>> file1_dx1
Re: [R] Creating a year-month indicator and groupby with category
Thanks everyone for being so kind and patient with me throughout the
process! Mr. Barradas and Mr. Lemon, very generous of you for taking the
time and patience to go over my code and data , and taking the time to give
me meaningful feedback!
With your help and suggestion, I was successful in making a graph from my
data. In my main data I have four companies, and just making the graph
process a little more advanced. However after writing the command , I get
the error that I have 4 values but gave only 2 values. Would anyone kindly
guide me what's the mistake and how I can rectify this?
Data is the same. But my main data has 4 companies whereas in R project I
just gave 2 only for convenience.
Code needed to execute it #
library(tidyverse)
library(showtext)
library(usefunc)
library(patchwork)
library(cowplot)
library(rcartocolor)
library(zoo)
# load fonts
font_add_google(name = "Bungee Shade", family = "bungee")
font_add_google(name = "Dosis", family = "dosis")
showtext_auto()
# set colours
f_cols = c("#008080", "#32", "#66b2b2",
"#7fbfbf", "#99", "#cce5e5")
m_cols = c("#4b0082", "#6e329b", "#9366b4",
"#a57fc0", "#b799cd", "#dbcce6")
dat$YearMonth <- as.yearmon(paste(dat$year, " ", dat$month), "%Y %m")
# plot of share of companies per year
p1 <- ggplot(data = dat,
mapping = aes(x = YearMonth, y = share, colour = company)) +
geom_line() +
geom_point(size = 1) +
scale_colour_manual("", values = c(f_cols[1], m_cols[1]), labels = c("F",
"M" , "C" , "G")) +
scale_y_continuous(limits = c(0, 80)) +
coord_cartesian(expand = F) +
labs(x = "Year",
y = "Share of Companies") +
theme(legend.position = c(0.1, 0.9),
legend.title = element_blank(),
legend.text = element_text(family = "dosis", size = 14),
panel.background = element_rect(fill = "#FAFAFA", colour =
"#FAFAFA"),
plot.background = element_rect(fill = "#FAFAFA", colour =
"#FAFAFA"),
legend.background = element_rect(fill = "transparent", colour =
"transparent"),
legend.key = element_rect(fill = "transparent", colour =
"transparent"),
axis.title.y = element_text(margin = margin(0, 20, 0, 0), family =
"dosis"),
axis.text = element_text(family = "dosis"),
plot.margin = unit(c(0.5, 0.8, 0.5, 0.5), "cm"),
panel.grid.major = element_line(colour = "#DEDEDE"),
panel.grid.minor = element_blank())
p1
The error is saying :
└─ggplot2 (local) FUN(X[[i]], ...)
7. ├─base::unlist(...)
8. └─base::lapply(scales$scales, function(scale) scale$map_df(df
= df))
9. └─ggplot2 (local) FUN(X[[i]], ...)
10. └─scale$map_df(df = df)
11. └─ggplot2 (local) f(..., self = self)
12. └─base::lapply(aesthetics, function(j)
self$map(df[[j]]))
13. └─ggplot2 (local) FUN(X[[i]], ...)
14. └─self$map(df[[j]])
15. └─ggplot2 (local) f(..., self = self)
16. └─self$palette(n)
17. └─ggplot2 (local) f(...)
18. └─rlang::abort(glue("Insufficient values
in manual scale. {n} needed but only {length(values)} provided."))
On Mon, 3 Oct 2022 at 02:45, Jim Lemon wrote:
> Hi Tariq,
> There were a couple of glitches in your data structure. Here's an
> example of a simple plot:
>
> dat<-structure(list(year = c(2018, 2019, 2019, 2019, 2019, 2019, 2019,
> 2019, 2017, 2017, 2017, 2017, 2017, 2017, 2017, 2017, 2017, 2017,
> 2017, 2017, 2018, 2018, 2018, 2018, 2018, 2018, 2018, 2018, 2018,
> 2018, 2018, 2018, 2019, 2019, 2019, 2019, 2019), month = c(12,
> 1, 2, 3, 4, 5, 6, 7, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 1,
> 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 1, 2, 3, 4, 5), company = c("ABC",
> "ABC", "ABC", "ABC", "ABC", "ABC", "ABC", "ABC", "FGH", "FGH",
> "FGH", "FGH", "FGH", "FGH", "FGH", "FGH", "FGH", "FGH", "FGH",
> "FGH", "FGH", "FGH", "FGH", "FGH", "FGH", "FGH", "FGH", "FGH",
> "FGH", "FGH", "FGH", "FGH", "FGH", "FGH", "FGH", "FGH", "FGH"
> ), share = c(20, 16.5, 15, 15.5, 15.5, 16, 17, 16.5, 61, 55,
> 53, 53, 54, 53, 58, 54, 50, 47, 55, 50, 52, 51, 51.5, 52, 53,
> 54, 55, 53, 54, 50, 42, 48, 41, 40, 39, 36.5, 35), com_name = c(1,
> 1, 1, 1, 1, 1, 1, 1, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2,
> 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2)), row.names = c(NA,
> -37L), spec = structure(list(cols = list(year = structure(list(), class =
> c("collector_double",
> "collector")), month = structure(list(), class = c("collector_double",
> "collector")), company = structure(list(), class = c("collector_character",
> "collector")), share = structure(list(), class = c("collector_double",
> "collector")), com_name = structure(list(), class = c("collector_double",
> "collector"))), default = structure(list(), class = c("collector_guess",
> "collector")), delim = ","), class = "col_spec"), class = c("spec_tbl_df",
> "tbl_df", "tbl",
Re: [R] automatic convert list to dataframe
Hello,
Here are two more attempts at solving the problem.
1. Instead of having 30-40 data.frames per file in the globalenv, not a
good idea, the following code will create as many lists as you have
files and each list is a list of df's.
temp_list <- vector("list", length = length(filenames))
for(i in seq_along(filenames)){
xlfile <- filenames[i]
temp_list[[i]] <- import_list(paste0(xlfile, ".xlsx"))
}
list2env(temp_list, envir = .GlobalEnv)
rm(temp_list)
Now you can access the data with code like
file1$dx1 # a data.frame, first sheet in excel file1
file1[["dx1"]] # equivalent
2. I cannot see a reason why the following shouldn't work. It creates
lots of data.frames in the globalenv, 30-40 per file. This makes the
globalenv messy and is not recommended.
temp_list <- vector("list", length = length(filenames))
for(i in seq_along(filenames)){
# import the data
xlfile <- filenames[i]
temp_list[[i]] <- import_list(paste0(xlfile, ".xlsx"))
# now take care of the names
new_names <- paste(xlfile, names(temp_list[[i]]), sep = "_")
names(temp_list[[i]]) <- new_names
# create the current data.frames in the globalenv
list2env(temp_list[[i]], envir = .GlobalEnv)
}
rm(temp_list)
Hope this helps,
Rui Barradas
Às 20:51 de 03/10/2022, Kai Yang escreveu:
Hi Rui,
I copied "list2env(i, envir = .GlobalEnv)" to the code, but I got the same error message
of "first argument must be a named list". Maybe list2env cannot put in loop? the code
works very well outside of for loop.
One more thing, the difference file may have same sheet name. that's why I want
to add file name in front of sheet name to avoid overwriting. It still works
well outside of loop, but doesn't work in loop. I don't know how to fix the
problems.
Thank you,
Kai
On Monday, October 3, 2022 at 12:09:04 PM PDT, Rui Barradas
wrote:
Hello,
If in each iteration i is a list, try removing the call to names().
Try, in the loop,
list2env(i, envir = .GlobalEnv)
The error message is telling that list2env's first argument must be a
named list and names(i) is an unnamed vector, it's i that's the named
list (you even changed its names in the previous instruction).
Hope this helps,
Rui Barradas
Às 18:38 de 03/10/2022, Kai Yang escreveu:
Hi Rui,
list2env(file1, envir = .GlobalEnv) is worked very well. Thank you.
But when I tried to put the sample code into for loop. I got error message:
for(i in filenames){
assign(i, import_list(paste0(i, ".xlsx", sep="")))
names(i) <- paste(i, names(i), sep = "_")
list2env(names(i), envir = .GlobalEnv)
}
Error in list2env(names(i), envir = .GlobalEnv) : first argument must be a
named list
It seems I cannot put names(i) into for loop, Could you please help me to debug
it?
Thank you,Kai On Monday, October 3, 2022 at 10:14:25 AM PDT, Rui Barradas
wrote:
Hello,
list2env(file1, envir = .GlobalEnv)
will create data.frames dx1, dx2, etc, in the global environment.
If you really need the names file1_dx1, file1_dx2, etc, you can first
change the names
names(file1) <- paste("file1", names(file1), sep = "_")
and then run list2env like above.
Hope this helps,
Rui Barradas
Às 16:51 de 03/10/2022, Kai Yang via R-help escreveu:
Hi R team,
I can use rio package to read excel file into R as a list. The excel file
content multiple sheets (30 - 40 data sheets). I can convert each data elements
into dataframe manually. I have multiple excel files with multiple data sheets.
I need to load them into R and do the comparison for same sheet name from
difference excel file. My current code is:
library(rio) setwd ("C:/temp")
filenames <- gsub("\\.xlsx$","", list.files(pattern="\\.xlsx$"))
for(i in filenames){
assign(i, import_list(paste0(i, ".xlsx", sep="")))
}
file1_dx1 <- file1[["dx1"]]
file1_dx2 <- file1[["dx2"]]
file1_dx3 <- file1[["dx3"]]
file2_dx1 <- file1[["dx1"]]
file2_dx2 <- file1[["dx2"]]
..
I hope the code can automatic converting the list (may have 30 - 40 lists) by
adding file name (such as: filename_sheetname) and put it in for loop
Thank you,
Kai
[[alternative HTML version deleted]]
__
R-help@r-project.org mailing list -- To UNSUBSCRIBE and more, see
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
__
R-help@r-project.org mailing list -- To UNSUBSCRIBE and more, see
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
Re: [R] automatic convert list to dataframe
Hi Rui,
I copied "list2env(i, envir = .GlobalEnv)" to the code, but I got the same
error message of "first argument must be a named list". Maybe list2env cannot
put in loop? the code works very well outside of for loop.
One more thing, the difference file may have same sheet name. that's why I want
to add file name in front of sheet name to avoid overwriting. It still works
well outside of loop, but doesn't work in loop. I don't know how to fix the
problems.
Thank you,
Kai
On Monday, October 3, 2022 at 12:09:04 PM PDT, Rui Barradas
wrote:
Hello,
If in each iteration i is a list, try removing the call to names().
Try, in the loop,
list2env(i, envir = .GlobalEnv)
The error message is telling that list2env's first argument must be a
named list and names(i) is an unnamed vector, it's i that's the named
list (you even changed its names in the previous instruction).
Hope this helps,
Rui Barradas
Às 18:38 de 03/10/2022, Kai Yang escreveu:
> Hi Rui,
> list2env(file1, envir = .GlobalEnv) is worked very well. Thank you.
>
> But when I tried to put the sample code into for loop. I got error message:
> for(i in filenames){
> assign(i, import_list(paste0(i, ".xlsx", sep="")))
> names(i) <- paste(i, names(i), sep = "_")
> list2env(names(i), envir = .GlobalEnv)
> }
> Error in list2env(names(i), envir = .GlobalEnv) : first argument must be a
> named list
>
> It seems I cannot put names(i) into for loop, Could you please help me to
> debug it?
> Thank you,Kai On Monday, October 3, 2022 at 10:14:25 AM PDT, Rui Barradas
> wrote:
>
> Hello,
>
>
> list2env(file1, envir = .GlobalEnv)
>
>
> will create data.frames dx1, dx2, etc, in the global environment.
> If you really need the names file1_dx1, file1_dx2, etc, you can first
> change the names
>
>
> names(file1) <- paste("file1", names(file1), sep = "_")
>
>
> and then run list2env like above.
>
> Hope this helps,
>
> Rui Barradas
>
> Às 16:51 de 03/10/2022, Kai Yang via R-help escreveu:
>> Hi R team,
>> I can use rio package to read excel file into R as a list. The excel file
>> content multiple sheets (30 - 40 data sheets). I can convert each data
>> elements into dataframe manually. I have multiple excel files with multiple
>> data sheets. I need to load them into R and do the comparison for same sheet
>> name from difference excel file. My current code is:
>> library(rio) setwd ("C:/temp")
>> filenames <- gsub("\\.xlsx$","", list.files(pattern="\\.xlsx$"))
>> for(i in filenames){
>> assign(i, import_list(paste0(i, ".xlsx", sep="")))
>> }
>> file1_dx1 <- file1[["dx1"]]
>>
>> file1_dx2 <- file1[["dx2"]]
>>
>> file1_dx3 <- file1[["dx3"]]
>>
>> file2_dx1 <- file1[["dx1"]]
>>
>> file2_dx2 <- file1[["dx2"]]
>> ..
>>
>> I hope the code can automatic converting the list (may have 30 - 40 lists)
>> by adding file name (such as: filename_sheetname) and put it in for loop
>>
>>
>> Thank you,
>> Kai
>>
>>
>>
>>
>> [[alternative HTML version deleted]]
>>
>> __
>> R-help@r-project.org mailing list -- To UNSUBSCRIBE and more, see
>> https://stat.ethz.ch/mailman/listinfo/r-help
>> PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
>> and provide commented, minimal, self-contained, reproducible code.
>
[[alternative HTML version deleted]]
__
R-help@r-project.org mailing list -- To UNSUBSCRIBE and more, see
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
Re: [R] automatic convert list to dataframe
Hello,
If in each iteration i is a list, try removing the call to names().
Try, in the loop,
list2env(i, envir = .GlobalEnv)
The error message is telling that list2env's first argument must be a
named list and names(i) is an unnamed vector, it's i that's the named
list (you even changed its names in the previous instruction).
Hope this helps,
Rui Barradas
Às 18:38 de 03/10/2022, Kai Yang escreveu:
Hi Rui,
list2env(file1, envir = .GlobalEnv) is worked very well. Thank you.
But when I tried to put the sample code into for loop. I got error message:
for(i in filenames){
assign(i, import_list(paste0(i, ".xlsx", sep="")))
names(i) <- paste(i, names(i), sep = "_")
list2env(names(i), envir = .GlobalEnv)
}
Error in list2env(names(i), envir = .GlobalEnv) : first argument must be a
named list
It seems I cannot put names(i) into for loop, Could you please help me to debug
it?
Thank you,KaiOn Monday, October 3, 2022 at 10:14:25 AM PDT, Rui Barradas
wrote:
Hello,
list2env(file1, envir = .GlobalEnv)
will create data.frames dx1, dx2, etc, in the global environment.
If you really need the names file1_dx1, file1_dx2, etc, you can first
change the names
names(file1) <- paste("file1", names(file1), sep = "_")
and then run list2env like above.
Hope this helps,
Rui Barradas
Às 16:51 de 03/10/2022, Kai Yang via R-help escreveu:
Hi R team,
I can use rio package to read excel file into R as a list. The excel file
content multiple sheets (30 - 40 data sheets). I can convert each data elements
into dataframe manually. I have multiple excel files with multiple data sheets.
I need to load them into R and do the comparison for same sheet name from
difference excel file. My current code is:
library(rio) setwd ("C:/temp")
filenames <- gsub("\\.xlsx$","", list.files(pattern="\\.xlsx$"))
for(i in filenames){
assign(i, import_list(paste0(i, ".xlsx", sep="")))
}
file1_dx1 <- file1[["dx1"]]
file1_dx2 <- file1[["dx2"]]
file1_dx3 <- file1[["dx3"]]
file2_dx1 <- file1[["dx1"]]
file2_dx2 <- file1[["dx2"]]
..
I hope the code can automatic converting the list (may have 30 - 40 lists) by
adding file name (such as: filename_sheetname) and put it in for loop
Thank you,
Kai
[[alternative HTML version deleted]]
__
R-help@r-project.org mailing list -- To UNSUBSCRIBE and more, see
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
__
R-help@r-project.org mailing list -- To UNSUBSCRIBE and more, see
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
Re: [R] automatic convert list to dataframe
Hi Rui,
list2env(file1, envir = .GlobalEnv) is worked very well. Thank you.
But when I tried to put the sample code into for loop. I got error message:
for(i in filenames){
assign(i, import_list(paste0(i, ".xlsx", sep="")))
names(i) <- paste(i, names(i), sep = "_")
list2env(names(i), envir = .GlobalEnv)
}
Error in list2env(names(i), envir = .GlobalEnv) : first argument must be a
named list
It seems I cannot put names(i) into for loop, Could you please help me to debug
it?
Thank you,KaiOn Monday, October 3, 2022 at 10:14:25 AM PDT, Rui Barradas
wrote:
Hello,
list2env(file1, envir = .GlobalEnv)
will create data.frames dx1, dx2, etc, in the global environment.
If you really need the names file1_dx1, file1_dx2, etc, you can first
change the names
names(file1) <- paste("file1", names(file1), sep = "_")
and then run list2env like above.
Hope this helps,
Rui Barradas
Às 16:51 de 03/10/2022, Kai Yang via R-help escreveu:
> Hi R team,
> I can use rio package to read excel file into R as a list. The excel file
> content multiple sheets (30 - 40 data sheets). I can convert each data
> elements into dataframe manually. I have multiple excel files with multiple
> data sheets. I need to load them into R and do the comparison for same sheet
> name from difference excel file. My current code is:
> library(rio) setwd ("C:/temp")
> filenames <- gsub("\\.xlsx$","", list.files(pattern="\\.xlsx$"))
> for(i in filenames){
> assign(i, import_list(paste0(i, ".xlsx", sep="")))
> }
> file1_dx1 <- file1[["dx1"]]
>
> file1_dx2 <- file1[["dx2"]]
>
> file1_dx3 <- file1[["dx3"]]
>
> file2_dx1 <- file1[["dx1"]]
>
> file2_dx2 <- file1[["dx2"]]
> ..
>
> I hope the code can automatic converting the list (may have 30 - 40 lists) by
> adding file name (such as: filename_sheetname) and put it in for loop
>
>
> Thank you,
> Kai
>
>
>
>
> [[alternative HTML version deleted]]
>
> __
> R-help@r-project.org mailing list -- To UNSUBSCRIBE and more, see
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.
[[alternative HTML version deleted]]
__
R-help@r-project.org mailing list -- To UNSUBSCRIBE and more, see
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
Re: [R] automatic convert list to dataframe
Hello,
list2env(file1, envir = .GlobalEnv)
will create data.frames dx1, dx2, etc, in the global environment.
If you really need the names file1_dx1, file1_dx2, etc, you can first
change the names
names(file1) <- paste("file1", names(file1), sep = "_")
and then run list2env like above.
Hope this helps,
Rui Barradas
Às 16:51 de 03/10/2022, Kai Yang via R-help escreveu:
Hi R team,
I can use rio package to read excel file into R as a list. The excel file
content multiple sheets (30 - 40 data sheets). I can convert each data elements
into dataframe manually. I have multiple excel files with multiple data sheets.
I need to load them into R and do the comparison for same sheet name from
difference excel file. My current code is:
library(rio) setwd ("C:/temp")
filenames <- gsub("\\.xlsx$","", list.files(pattern="\\.xlsx$"))
for(i in filenames){
assign(i, import_list(paste0(i, ".xlsx", sep="")))
}
file1_dx1 <- file1[["dx1"]]
file1_dx2 <- file1[["dx2"]]
file1_dx3 <- file1[["dx3"]]
file2_dx1 <- file1[["dx1"]]
file2_dx2 <- file1[["dx2"]]
..
I hope the code can automatic converting the list (may have 30 - 40 lists) by
adding file name (such as: filename_sheetname) and put it in for loop
Thank you,
Kai
[[alternative HTML version deleted]]
__
R-help@r-project.org mailing list -- To UNSUBSCRIBE and more, see
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
__
R-help@r-project.org mailing list -- To UNSUBSCRIBE and more, see
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
[R] automatic convert list to dataframe
Hi R team,
I can use rio package to read excel file into R as a list. The excel file
content multiple sheets (30 - 40 data sheets). I can convert each data elements
into dataframe manually. I have multiple excel files with multiple data sheets.
I need to load them into R and do the comparison for same sheet name from
difference excel file. My current code is:
library(rio) setwd ("C:/temp")
filenames <- gsub("\\.xlsx$","", list.files(pattern="\\.xlsx$"))
for(i in filenames){
assign(i, import_list(paste0(i, ".xlsx", sep="")))
}
file1_dx1 <- file1[["dx1"]]
file1_dx2 <- file1[["dx2"]]
file1_dx3 <- file1[["dx3"]]
file2_dx1 <- file1[["dx1"]]
file2_dx2 <- file1[["dx2"]]
..
I hope the code can automatic converting the list (may have 30 - 40 lists) by
adding file name (such as: filename_sheetname) and put it in for loop
Thank you,
Kai
[[alternative HTML version deleted]]
__
R-help@r-project.org mailing list -- To UNSUBSCRIBE and more, see
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
Re: [R] Creating a year-month indicator and groupby with category
Hi Tariq,
There were a couple of glitches in your data structure. Here's an
example of a simple plot:
dat<-structure(list(year = c(2018, 2019, 2019, 2019, 2019, 2019, 2019,
2019, 2017, 2017, 2017, 2017, 2017, 2017, 2017, 2017, 2017, 2017,
2017, 2017, 2018, 2018, 2018, 2018, 2018, 2018, 2018, 2018, 2018,
2018, 2018, 2018, 2019, 2019, 2019, 2019, 2019), month = c(12,
1, 2, 3, 4, 5, 6, 7, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 1,
2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 1, 2, 3, 4, 5), company = c("ABC",
"ABC", "ABC", "ABC", "ABC", "ABC", "ABC", "ABC", "FGH", "FGH",
"FGH", "FGH", "FGH", "FGH", "FGH", "FGH", "FGH", "FGH", "FGH",
"FGH", "FGH", "FGH", "FGH", "FGH", "FGH", "FGH", "FGH", "FGH",
"FGH", "FGH", "FGH", "FGH", "FGH", "FGH", "FGH", "FGH", "FGH"
), share = c(20, 16.5, 15, 15.5, 15.5, 16, 17, 16.5, 61, 55,
53, 53, 54, 53, 58, 54, 50, 47, 55, 50, 52, 51, 51.5, 52, 53,
54, 55, 53, 54, 50, 42, 48, 41, 40, 39, 36.5, 35), com_name = c(1,
1, 1, 1, 1, 1, 1, 1, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2,
2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2)), row.names = c(NA,
-37L), spec = structure(list(cols = list(year = structure(list(), class =
c("collector_double",
"collector")), month = structure(list(), class = c("collector_double",
"collector")), company = structure(list(), class = c("collector_character",
"collector")), share = structure(list(), class = c("collector_double",
"collector")), com_name = structure(list(), class = c("collector_double",
"collector"))), default = structure(list(), class = c("collector_guess",
"collector")), delim = ","), class = "col_spec"), class = c("spec_tbl_df",
"tbl_df", "tbl", "data.frame"))
# convert year and month fields to dates about the middle of each month
dat$date<-as.Date(paste(dat$year,dat$month,15,sep="-"),"%Y-%m-%d")
# plot the values for one company
plot(dat$date[dat$company=="ABC"],dat$share[dat$company=="ABC"],
main="Plot of dat",xlab="Year",ylab="Share",
xlim=range(dat$date),ylim=range(dat$share),
type="l",col="red")
# add a line for the other one
lines(dat$date[dat$company=="FGH"],dat$share[dat$company=="FGH"],col="green")
# get the x plot limits as they are date values
xspan<-par("usr")[1:2]
# place a legend about in the middle of the plot
legend(xspan[1]+diff(xspan)*0.3,35,c("ABC","FGH"),lty=1,col=c("red","green"))
There are many more elegant ways to plot something like this.
Jim
On Mon, Oct 3, 2022 at 10:05 AM Tariq Khasiri wrote:
>
> Hello,
>
> I have the following data. I want to show in a line plot how each different
> company is earning over the timeline of my data sample.
>
> I'm not sure how I can create the *year-month indicator* to plot it nicely
> in my horizontal axis out of my dataset.
>
> After creating the *year-month* indicator ( which will be in my x axis) I
> want to create a dataframe where I can groupby companies over the
> year-month indicator by putting *share *in the y axis as variables.
>
> ### data is like the following
>
> dput(dat)
> structure(list(year = c(2018, 2019, 2019, 2019, 2019, 2019, 2019,
> 2019, 2017, 2017, 2017, 2017, 2017, 2017, 2017, 2017, 2017, 2017,
> 2017, 2017, 2018, 2018, 2018, 2018, 2018, 2018, 2018, 2018, 2018,
> 2018, 2018, 2018, 2019, 2019, 2019, 2019, 2019), month = c(12,
> 1, 2, 3, 4, 5, 6, 7, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 1,
> 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 1, 2, 3, 4, 5), company = c("ABC",
> "ABC", "ABC", "ABC", "ABC", "ABC", "ABC", "ABC", "FGH", "FGH",
> "FGH", "FGH", "FGH", "FGH", "FGH", "FGH", "FGH", "FGH", "FGH",
> "FGH", "FGH", "FGH", "FGH", "FGH", "FGH", "FGH", "FGH", "FGH",
> "FGH", "FGH", "FGH", "FGH", "FGH", "FGH", "FGH", "FGH", "FGH"
> ), share = c(20, 16.5, 15, 15.5, 15.5, 16, 17, 16.5, 61, 55,
> 53, 53, 54, 53, 58, 54, 50, 47, 55, 50, 52, 51, 51.5, 52, 53,
> 54, 55, 53, 54, 50, 42, 48, 41, 40, 39, 36.5, 35), com_name = c(1,
> 1, 1, 1, 1, 1, 1, 1, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2,
> 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2)), row.names = c(NA,
> -37L), spec = structure(list(cols = list(year = structure(list(), class =
> c("collector_double",
> "collector")), month = structure(list(), class = c("collector_double",
> "collector")), company = structure(list(), class = c("collector_character",
> "collector")), share = structure(list(), class = c("collector_double",
> "collector")), com_name = structure(list(), class = c("collector_double",
> "collector"))), default = structure(list(), class = c("collector_guess",
> "collector")), delim = ","), class = "col_spec"), problems = 0x7fd732028680>, class = c("spec_tbl_df",
> "tbl_df", "tbl", "data.frame"))
>
> [[alternative HTML version deleted]]
>
> __
> R-help@r-project.org mailing list -- To UNSUBSCRIBE and more, see
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.
__
R-help@r-project.org
