R; Rbind Excel files from a List of vectors of files in R

See also questions close to this topic

• Why is .png file not looking clear after arranging using grid.arrange?

  I am trying to arrange 9 png files (which I had created using plot() function) in 2 columns. But these files are not looking clear after using grid.arrange to arrange them.

  I think if page size is increased, the images will be clearly visible. Or there might be some problem with the size of my plotes converted to .png.

  Please suggest me a solution.

  Following is the code I am trying:

  [install.packages('png')
  install.packages('gridExtra')
  library(png)
  library(grid)
  library(gridExtra)
  
  p1 <- readPNG('p1.png')
  p2 <- readPNG('p2.png')
  p3 <- readPNG('p3.png')
  p4 <- readPNG('p4.png')
  p5 <- readPNG('p5.png')
  p6 <- readPNG('p6.png')
  p7 <- readPNG('p7.png')
  p8 <- readPNG('p8.png')
  p9 <- readPNG('p9.png')
  
  
  grid.arrange(rasterGrob(p1), rasterGrob(p2),rasterGrob(p3),rasterGrob(p4),rasterGrob(p5),
               rasterGrob(p6),rasterGrob(p7),rasterGrob(p8),rasterGrob(p9),ncol=2)][1]
  

  After arranging
• How to use map/reduce and read_csv to import a subset of columns from multiple files

  I need to import multiple files into R. From each file I only need a subset of columns. I have working code that will import ALL 97 columns from an arbitrary number of files, but cannot figure out how to limit the import to only the 4 columns I need.

  I rely on the tidyverse for many of my functions. R version: 3.6.0

  I have created code that imports limited columns from a SINGLE file but cannot expand this to all files.

  #this code works correctly and imports all columns from multiple files
  setwd('R:\\Data\\Raw Data Files')
  files <- c('686960.csv','686962.csv','686963.csv','623056.csv','608910.csv','608911.csv','609248.csv','609246.csv', '722178.csv', '722177.csv', '756542.csv')
  
  data <- files %>% 
  map(read_csv) %>% 
    reduce(rbind)
  
  #this code works correctly and imports 4 columns from 1 file
  test <- test_file %>%  read_csv(
                  col_types = 
                    cols_only(
                      'Member ID' = col_character()
                      ,'Patient Age' = col_character()
                      ,'Plan Payment Amount' = col_character()
                      ,'Check Date' = col_character()
                    )
                  ,n_max = 10
  )
  
  
  #this code produces an error
  setwd('R:\\Data\\Raw Data Files')
  files <- c('686960.csv','686962.csv','686963.csv','623056.csv','608910.csv','608911.csv','609248.csv','609246.csv', '722178.csv', '722177.csv', '756542.csv')
  
  data_test <- files %>% 
    map(read_csv(
      col_types = 
        cols_only(
          'Member ID' = col_character()
          ,'Patient Age' = col_character()
          ,'Plan Payment Amount' = col_character()
          ,'Check Date' = col_character()
        )
      ,n_max = 10
    )) %>% 
    reduce(rbind)
  
  #Error in read_delimited(file, tokenizer, col_names = col_names, col_types = #col_types,  : 
  #  argument "file" is missing, with no default
  
  
• How to make sure the user of a shiny app is using the right package versions in R

  Due to recent experience with several bugs created by updating packages, I wonder what the best approach is for the following problem:

  I currently provide a stand alone version so to say of my shiny App (just the script files to run it locally) and run a long list of require() functions to load / install the needed packages. However, in the end I would like to use fixed package versions to avoid bugs created by changes in packages.

  Is there a way to ensure that the user, who may have older or newer versions of packages on their computer, is using the right version of all the packages my app needs?
• Excel VBA: Create Array from Filter Field Items?

  A report I am creating in Excel involves several very similar pivot tables needing to be specifically filtered many times (i.e. a Year-to-Date table, a Quarter-to-Date table, etc, all needing to be filtered the exact same way before exported, then filtered again, then exported, etc)

  So I looked into VBA as a way of accepting a few filter criteria, then filtering multiple tables that way, before looping.

  However, I'm having a very tough time properly targeting PivotTables and specific fields, as it appears an integrated Value field is targeted and filtered via code differently than, say, a "filter' field I have attached to the top of the PivotTables, where they can accept no "begins with", "contains", etc, strings. They are just checkboxes, and one or multiple can be selected.

  So it's one thing for me to tell it via VBA to select one item, and having it select all but one item. The latter requires the code to target every single possible value, but not the one that I want excluded.

  My idea for this, then, is to create an array from every possible existing value in this filter field, then going through a loop where each value is added to my code as a value to check.

  I have some code so far:

  
  ActiveSheet.PivotTables("QTD_Pivot_By_Category").PivotFields( _
          "[Range].[Address_1].[Address_1]").VisibleItemsList = Array( _
          "[Range].[Address_1].&", "[Range].[Address_1].&[0]", "[Range].[Address_1].&[101]" _
          , "[Range].[Address_1].&[INC]", "[Range].[Address_1].&[KRT]", _
          "[Range].[Address_1].&[LTD]", "[Range].[Address_1].&[RPO]", _
          "[Range].[Address_1].&[ INC]", "[Range].[Address_1].&[CORP]", _
          "[Range].[Address_1].&[INC.]", "[Range].[Address_1].&[LTD.]", _
          "[Range].[Address_1].&[LTEE]", "[Range].[Address_1].&[PAWS]", _
  
  
  

  Now, if I just record this macro from actions in Excel, and do "select All", then de-select the one I don't want, it will error. It errors because it's selecting ~300 values, and while it's 'writing' this code, it errors when it hits the limit of "_" delimited breaks in one straight line of VBA code.

  If my field is called "Address_1" as above, part of the range..."Range" (not sure where that's defined or why, but it works), can I get some help as to the most efficient way to define said ".VisibleItemList" as all POSSIBLE items in the list from a dynamic array rather than needing to be selected manually? This list will be different day-to-day so it can't just be a hardcoded flat list.

  Ideally, also in a way that circumvents the max limit on "_" line breaks in a line of code in VBA for Excel.

  If it's of any use for context, my table looks like this. See that checkbox drop-down? I want a snapshot of every updated value sitting in there to be put into an array and then iterated upon being added in a way similar to my example code:

  

  Edit: Since that filter field's values are being pulled from a local datasource, I decided to just grab those and make an array that way! So I'm starting my code this way:

  
  Dim OGDataRange As Range, OGDataLastRow As Long
  Dim ValueArray As Variant
  
  
  OGDataLastRow = Worksheets("DATA QTD").Range("U2").End(xlDown).Row
  Set OGDataRange = Worksheets("DATA QTD").Range("U2:U" & OGDataLastRow)
  ValueArray = OGDataRange.Value
  
  

  "ValueArray" is now my array. So I need help one-by-one pulling the values of this array, and adding them to my VisibleItemList as seen above.

  Thank you so much for any assistance.
• VBA Excel - Sort a daily delivery manifest into groups based on complex rules

  I am trying to come up with a way to sort my Excel-Table. I wrote a very basic VBA to do this when it was a random sort only. Now there's complex parameters/rules I have to meet and it's way outside my skillset.

  The problem: I receive a daily file with a list of items via shipments like the example below. The list can have as few as 1 and as many as 24 items. I have to sort these by restaurant.

  Example Original List
  
  ITEM    SHIPMENT
  
  Oranges 1
  Apples  1
  Grapes  1
  Pears   2
  Pork    3
  Chicken 4
  Rice    5
  Peas    5
  Beans   5
  Water   5
  Corn    5
  Milk    5
  Eggs    5
  Salmon  6
  Tofu    7
  Juice   8
  Cheese  8
  Salt    8
  Pepper  9
  Onions  10
  Oats    11
  Barley  11
  Kale    11
  Chips   12
  

  The items need to be sorted out to 6 restaurants and there are complex rules:

  Overall Rules

  • No Restaurant can have more than 1 item from a shipment
  • No Restaurant can have more than 4 items

  Sorting Rules

  1. Restaurant 1 always gets the first two items (Items 1-2)
  2. Restaurant 2-5 evenly gets the next 16 items (Items 3-18)
  3. Restaurant 6 gets the next 2 items (Items 19-20)
  4. Restaurant 1 and 6 then evenly get the last 4 items (Items 21-24)
  5. If there are more than 6 items in a shipment (more items than restaurants) the extra items stay in the warehouse.
  6. The Overall Rules override the sorting rules. For example in our example list Restaurant 1 cannot have both Oranges and Apples since they are from the same shipment so the sort changes.

  Example Sort
  
  Restaurant 1    Shipment
  Oranges         1
  Pears           2
  Rice            5
  Kale            11
  
  Restaurant 2    
  Apples          1
  Peas            5
  Salmon          6
  Salt            8
  
  Restaurant 3    
  Grapes          1
  Beans           5
  Tofu            7
  Pepper          9
  
  Restaurant 4
  Pork            3
  Water           5
  Juice           8
  Onions          10
  
  Restaurant 5
  Chicken         4
  Corn            5
  Cheese          8
  Oats            11
  
  Restaurant 6
  Milk            5
  Barley          11
  Chips           12
  
  Warehouse Items
  Eggs            5
  

  Looking at it as a whole now I'm not even sure this is possible and I have no idea how to go about doing it. If anyone has any input I'd love to hear it. Thank you so much for your help.
• R: rulled data frame to automate changes on it

  I would like to create a sort of data.frame where I can add rules on it just like excel tables. For instance, if I work with symetric 2x2 matrices, I would like that if I change the value at [1,2] then the value at [2,1] will be automatically changed to the same value. I have search for this but I cannot find anything related. Any help is appreciated.
• Merge two access database when keys are not unique

  Here is the situation. I would need to merge two identical database, which have been used in two differents place for years. Now they would like one merge database. Problem is, some autonum are involed and therefore if I simple copy the data from one to the other, it will have duplicated keys. Moreover, if I write a program to somehow manage the duplicated key by assigning a new key, I would require to go over all table with the linked key and change the data accordingly.

  I would like to avoid that as it has a lot of tables but I wonder if there is some tools that would help me do that.

  So my question is : Is there a tool that can merge two access database, update the keys, as well as any linked column from other table or will I have to do it myself ?

  Thanks

  EDIT : What I am trying to prevent is having to do a program that will go into one of the database, insert all the data while keeping track of the keys generated (they're pretty much all autonum) in order to prevent data loss.
• Weird problem with merge in R, producing NAs

  I have a data table, first_median,which includes a column location. Another data table that has location and name of the city in it.

  I want to merge them so the initial data table, first_median, gets the city names.

  The problem is that it produces NAs for some of those. To be more clear, the coordinate 44.03125_-123.09375 has the name Eugene. After merging, the first two repetition of 44.03125_-123.09375 are mapped to Eugene, but the rest are mapped to NA.

  Next weird part is that I convert the first_median to data frame, (as.data.frame(first_median), and then back to data table, data.table(first_median), and then I do the merge, then it works!!!

  Please take a look at the image.

  Any idea what is going on?

  

  Also, I changed the code to

  first_medians_merged_before <- merge(first_medians, LOI, by="location", 
  
  all.x=T)
  dput(head(first_medians_merged_before, 5))
  
  first_medians <- as.data.frame(first_medians)
  first_medians <- data.table(first_medians)
  first_medians_merged_after <- merge(first_medians, LOI, by="location", all.x=T)
  dput(head(first_medians_merged_after, 5))
  

  To be more clear, and the outputs of the dput are below:

  > dput(head(first_medians_merged_before, 5))
  structure(list(location = c("44.03125_-123.09375", "44.03125_-123.09375", 
  "44.03125_-123.09375", "44.03125_-123.09375", "44.03125_-123.09375"
  ), time_period = c("1950-2005", "1950-2005", "1979-2015", "1979-2015", 
  "2006-2025"), emission = c("RCP 4.5", "RCP 8.5", "RCP 4.5", "RCP 8.5", 
  "RCP 4.5"), median = c(72, 72, 68, 68, 78), city = c("Eugene", 
  "Eugene", NA, NA, NA)), sorted = "location", class = c("data.table", 
  "data.frame"), row.names = c(NA, -5L), .internal.selfref = <pointer: 0x1028114e0>)
  

  ---

  > dput(head(first_medians_merged_after, 5))
  structure(list(location = c("44.03125_-123.09375", "44.03125_-123.09375", 
  "44.03125_-123.09375", "44.03125_-123.09375", "44.03125_-123.09375"
  ), time_period = c("1950-2005", "1950-2005", "1979-2015", "1979-2015", 
  "2006-2025"), emission = c("RCP 4.5", "RCP 8.5", "RCP 4.5", "RCP 8.5", 
  "RCP 4.5"), median = c(72, 72, 68, 68, 78), city = c("Eugene", 
  "Eugene", "Eugene", "Eugene", "Eugene")), sorted = "location", class = c("data.table", 
  "data.frame"), row.names = c(NA, -5L), .internal.selfref = <pointer: 0x1028114e0>)
  > 
  
• Merge branches depending on each other

  sorry for question, which is asked so many times, but I just cant find solution for my problem.

  I have a lot of tasks, some of them are depended on others tasks, so lets say, we have:

  • task 1: add table (merge required)
  • task 2: using table from task 1, add button to add something using form (merge required)
  • task 3: using previous solutions, but after adding something into table, you are able to click on it to see details (merge required)

  So as you can see, task 1 needs to be merged into master, task 2 is depended on task 1, so you have to include it, also merge is required and task 3 require both task 1 and 2.

  And as you know, code review takes a lot of time, so I dont want to just sit and wait, but I want to continue on task 2 while task 1 is reviewing, and then working on task 3 while task 2 is reviewing.

  Is there a way how to do it? Im using VS Code with Gitlens extension, also using terminal to push.

  Thanks for your time.