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

2018-12-05 19:58 Ethan James imported from Stackoverflow

I have web-scraped ~1000 Excel Files into a specific folder on my computer I then read these files in which returned a value of chr [1:1049] I then grouped these files by similar names which was every 6 belonged in one group This returned a List of 175, with values of the group of 6 file names.

I am confused on how I would run a loop that would merge/rbind the 6 file names for each group from that list. I would also need to remove the first row but I know how to do that part with read.xlsx

My code so far is

setwd("C:\\Users\\ewarren\\OneDrive\\Documents\\Reservoir Storage")
files <- list.files()
file_groups <- split(files, ceiling(seq_along(files)/6))

with

for (i in file_groups) {
    print(i) 
}

returning each group of file names

The files for example are: files

They are each compromised of two columns, date and amount I need to add a third to each that is the reservoir name That way when all the rows from all the files are combined theres a date, an amount, and a reservoir. If I do them all at once w/o the reservoir, I wouldnt know which rows belong to which.

1 answer

answered 2018-12-05 20:19 PDM
You can use startRow = 2 to not get the first row in read.xlsx

for merging the groups of file. If you have an identifier e.g. x in each file that matches with their others in the group, but not with the ones which are in other groups.

you have make a list group1 <- list.files(pattern = "x) then use do.call(cbind, group1)

See also questions close to this topic

Color values in boxplot based on x-axis variable in ggplot
I have a dataframe like the following:
```
df = data.frame(cat = rep(c("A", "B", "C", "D"), each = 20), val = runif(80))
```
And an annotation dataframe like the following:
```
ann = data.frame(cat = c("A", "B", "C", "D"), col = c(34, 84, 23, 85))
```
I want to make a boxplot for each of these cats along the x-axis, and the value in the data frame as the y-axis, but I also want to color each boxplot by the value in ann$col (continuous color mapping).

I can get the boxplot like the following:
```
ggplot(df, aes(x = variable, y = BACC)) +
  geom_boxplot(width = 0.12)
```
But I am unsure how to color each boxplot by the category value.

How can this be done?

Thanks, Jack
Loop in R, extracting the last line of the output
Hei guys,

I've been trying to extract the values from a loop for a while but it seems I just can't find the answer.

To make it simple, here is the code I'm using in R programme:
```
seatsInitial <- function (df,x,y) {
for (i in ModQuota) {
filtered = subset(df, Poll> x)
OnlyPoll <- filtered$Poll
Calculus <- OnlyPoll/i
Calculus2 <- floor(Calculus)
print(Calculus2)
if(sum(Calculus2) == y)
  stop("WE GOT IT")
}
}
```
Then, to get the results:
```
seatsInitial(PartiesE, 5, 33)
```
The loop works properly and I am able to get the results I need. The loop stops when the condition is met. However, I would also need to convert the last line of the output (when the loop stops as a result of meeting the condition) into a vector.

Do you have any idea how this could be done?

If I run the loop, I get the following results (showing only the last 5 lines of the output here):
```
[1]  8 14  4  3  5
[1]  8 14  4  3  5
[1]  8 14  4  3  5
[1]  8 14  4  3  5
[1]  7 14  4  3  5
Error in seatsInitial(PartiesEPP, 5, 33) : WE GOT IT
```
I therefore need the last line (7,14,4,3,5) to be converted into a vector.

Thanks for your help!

Remove records matching a value on a single column occurring within a 5 minute window of a different value on the same column

I have a data frame that looks like this:

require(data.table)
require(tidyverse)

df <- as.data.frame(matrix(c(123, "2018-01-05 09:09:02", "Mobile",
                         123, "2018-01-06 11:11:15", "Organic",
                         123, "2018-01-07 13:24:45", "Email",
                         123, "2018-01-07 13:24:55", "Organic",
                         321, "2018-01-05 15:15:29", "Organic",
                         989, "2018-01-08 08:09:21", "Feeds",
                         989, "2018-01-08 08:09:55", "Organic",
                         989, "2018-01-10 10:21:40", "Email"), nrow = 8,
                       ncol = 3, byrow = TRUE, dimnames = list(NULL, c("customer_id", "entry_time",
                                                                       "channel"))))

df$entry_time <- as.POSIXct(df$entry_time)


 df
 customer_id          entry_time channel
1         123 2018-01-05 09:09:02  Mobile
2         123 2018-01-06 11:11:15 Organic
3         123 2018-01-07 13:24:45   Email
4         123 2018-01-07 13:24:55 Organic
5         321 2018-01-05 15:15:29 Organic
6         989 2018-01-08 08:09:21   Feeds
7         989 2018-01-08 08:09:55 Organic
8         989 2018-01-10 10:21:40   Email

What I would like to do is remove all "organic" records occurring within a five minute window of a non-organic record, for each customer.

In other words, I want to remove all records where: 1) channel = organic and 2) entry_time < 5 minutes removed from the previous record and 3) the previous record's channel != Organic. I need to be able to do this for each customer id.

My desired output looks as follows:

df_desired <- as.data.frame(matrix(c(123, "2018-01-05 09:09:02", "Mobile",
                         123, "2018-01-06 11:11:15", "Organic",
                         123, "2018-01-07 13:24:45", "Email",
                         321, "2018-01-05 15:15:29", "Organic",
                         989, "2018-01-08 08:09:21", "Feeds",
                         989, "2018-01-10 10:21:40", "Email"), nrow = 6,
                       ncol = 3, byrow = TRUE, dimnames = list(NULL, c("customer_id", "entry_time",
                                                                       "channel"))))

df_desired$entry_time <- as.POSIXct(df_desired$entry_time)

df_desired
customer_id          entry_time channel
1         123 2018-01-05 09:09:02  Mobile
2         123 2018-01-06 11:11:15 Organic
3         123 2018-01-07 13:24:45   Email
4         321 2018-01-05 15:15:29 Organic
5         989 2018-01-08 08:09:21   Feeds
6         989 2018-01-10 10:21:40   Email

I am able to do this with the following nested loop (apologies for exposing you to this monstrosity).

dat_splt <- split(df, df$customer_id)


for (h in 1:length(dat_splt)){
dat_splt[[h]]$prox_flag <- 0
if (nrow(dat_splt[[h]]) == 1)
{next}
else
{for (g in 2:nrow(dat_splt[[h]])){
if (dat_splt[[h]][g,]$channel != "Organic")
{next}
else if (dat_splt[[h]][g-1,]$channel != "Organic" &
         as.numeric((difftime(dat_splt[[h]][g,]$entry_time, dat_splt[[h]][g-1,]$entry_time, units = "mins")) < 5))
{dat_splt[[h]][g,]$prox_flag <- 1}
else
{next}
}}
}

dat <- rbindlist(dat_splt)

dat <- dat %>%
   filter(prox_flag != 1)

Needless to say, this does not scale well. Can someone please help me unravel this Gordian knot of a solution into something more practical?

Much thanks in advance.

Excel VBA copy textbox to clipboard
I am currently trying to use a command button to copy text from a Text Box to the clipboard. This is my full code:
```
Private Sub CommandButton1_Click()
    With New MSForms.DataObject
        .SetText TextBox1.Text
        .PutInClipboard
    End With
End Sub
```
I keep getting the error "Run-time error '424' Object required. Debugging causes ".setText TextBox1.Text to highlight and say "MyData
If you cant tell I am a super noob with VBA but I am trying to learn. I've spent a lot of time trying different ways to do so any help would be great. Even if it means me trying a different type of code.

Upload Excel File with Fetch() Convert To JSON in Memory

Looking for best practices on handling Excel File Upload in React/Node and converting to JSON with additional form data.

Requirements:

Post includes form data in addition to Excel File
Handle Excel File on server with Node.js and Multer in Memory ie., multer.memoryStorage() (Not Saving File To Server)
Convert Excel data to JSON, then append JSON data with additional form data before sending to MongoDB.
I will also need to process through NGINX on production, which may be an issue uploading the Excel file. Not an issue in Dev without NGINX.

Current working solution on Dev with React/Redux and Node.js:

Fetch() for post to server
Multer for handling post
xlsx_buffer_json https://www.npmjs.com/package/xlsx_buffer_json

XLSX Buffer JSON Fix for missing cptable reference:

Had to add Externals object to webpack.config:

       externals: [
         {
          './cptable': 'var cptable'
         },
       ]

React Action Code

export function importAssets(assetData, importFile) {
    let systemAssetData = new FormData();
    systemAssetData.append('systemId', assetData.systemid);
    systemAssetData.append('productId', assetData.productid);
    systemAssetData.append('fileData', importFile);

    const config = {
        method: 'POST',
        headers: {'Accept':'application/json'},
        body: systemAssetData,
    };

   return (dispatch) => {
        dispatch(requestImportAsset(assetData));

        return fetch('/asset/importAssets', config)
            .then(response =>
                response.json().then(assets => ({assets, response})),
            ).then(({assets, response}) => {
                if (!response.ok) {
                    dispatch(requestImportAssetError(assets.message));
                    return Promise.reject(assets.message);
                }
                dispatch(requestImportAssetSuccess(assets));
                return 0;
            }).catch(err => console.log('Action Error: ', err)); 
    };
}

Node/Express Route Code

var express = require('express');
var router = express.Router();
var requestpromise = require('request-promise');
var multer  = require('multer');
var xlsxtojson = require("xlsx_buffer_json")
var path = require("path");
var storage = multer.memoryStorage();
var upload = multer({ storage: storage });

router.post('/importAssets', upload.single('fileData'),  (req, res) => {
    let config = req.app.get('config');
        try {
            xlsxtojson({
                input: req.file.buffer,
                output: null, 
                lowerCaseHeaders:true
            }, function(err,result){
                if(err) {
                    console.log('xlsxtojson error:  ' + err);
                    return res.json({error_code:1,err_desc:err, data: null});
                }
                result.push({"productid":req.body.productId});
                result.push({"systemid":req.body.systemId});
                
                //send result object JSON to API Server for insert into MongoDB
            });
        } catch (e){
            console.log('corrupt file error:  ' + e);
            res.json({error_code:1,err_desc:"Corupted excel file"});
        }
  res.status(200).send('Excel File Upload Succes')
});

While this works, it's not the cleanest solution in my mind. I haven't seen a better working solution to date.

Any thoughts?

VBA Forecasting Excel Solver

I need to do a forecast for a product sales and I need to find the optimal α, β, γ using maybe excel solver. But my program is dynamic and ισ updated with new sales. So a vba code was created to calculate the Mean square error as new data are available. How can use excel solver in this case to find α,β, γ ?
Merge 2 fields from hubspot with random digits

I would like to generate customer codes from HUBSPOT fields. I have a workflow on HUBSPOT that sends a webhook to ZAPIER when the conditions are met. I would like, thanks to this webhook, to perform the following action: Customer code = 5 characters of the name without accent or space + same for the first name + 2 random digits. I look at the side of zapier's javascript function to try to do that, but I'm a bit in trouble. Can you help me and tell me if this is possible?
Pandas merge...desired outut

i have 2 pandas dataframes.

No year a cost_a 123 2017 2 0.6 456 2016 6 1.8 456 2017 36 10.8 456 2018 42 12.6 789 2017 4 1.2 789 2018 1 0.3

No year b cost_b 456 2012 9 0.9 456 2013 3 0.3 456 2015 3 0.3 456 2016 11 1.1 456 2017 3 0.3 456 2018 2 0.2 987 2015 2 0.2 987 2017 2 0.2 789 2017 2 0.2

and i want below output:

No year a cost_a b cost_b 123 2017 2 0.6 nan nan 456 2012 nan nan 9 0.9 456 2013 nan nan 3 0.3 456 2015 nan nan 3 0.3 456 2016 6 1.8 11 1.1 456 2017 36 10.8 3 0.3 456 2018 42 12.6 2 0.2 789 2017 4 1.2 2 0.2 789 2018 1 0.3 nan nan 987 2015 nan nan 2 0.2 987 2017 nan nan 2 0.2

please help me out.

Pandas: Merge dataframes and keep only the minimum value associated with merged unique pairs

I'm having a tougher problem with pandas.

I am merging two dataframes on a column V which defines groups.

Both dataframes have also a unique ID column and a Time column.

After merging I compute the Timedelta between those two columns and filter out the negative values:

import pandas as pd

L11 = ['V1','V1','V1','V2','V2','V3','V3','V3','V3']
L12 = [1,2,3,4,5,6,7,8,9]
L13 = [pd.Timestamp("1.1.1980 12:12:12"),
       pd.Timestamp("1.1.1980 13:12:12"),
       pd.Timestamp("1.2.1980 01:12:12"),
       pd.Timestamp("1.1.1980 14:12:12"),
       pd.Timestamp("1.1.1980 16:12:12"),
       pd.Timestamp("1.1.1980 16:12:12"),
       pd.Timestamp("1.1.1980 14:12:12"),
       pd.Timestamp("1.1.1980 13:12:12"),
       pd.Timestamp("1.2.1980 10:12:12")]

L21 = ['V1','V1','V2','V3','V3','V3','V3','V3','V3']
L22 = [11,12,13,14,15,16,17,18,19]
L23 = [pd.Timestamp("1.1.1980 12:12:12"),
       pd.Timestamp("1.1.1980 13:12:12"),
       pd.Timestamp("1.1.1980 14:12:12"),
       pd.Timestamp("1.1.1980 14:12:12"),
       pd.Timestamp("1.1.1980 16:12:12"),
       pd.Timestamp("1.1.1980 18:12:12"),
       pd.Timestamp("1.1.1980 11:12:12"),
       pd.Timestamp("1.1.1980 12:12:12"),
       pd.Timestamp("1.2.1980 10:12:12")]

df1 = pd.DataFrame({'V':L11,'ID1':L12,'Time1':L13})
df2 = pd.DataFrame({'V':L21,'ID2':L22,'Time2':L23})

df = pd.merge(df1,df2,on='V')
df["Delta"] = df.Time1-df.Time2
df = df[df.Delta>pd.Timedelta(0)].copy()
df = df.drop(["Time1","Time2"],axis=1)

Additionally I count how many entries per V-group there are in each dataframe and get the lower value which I'm calling Max because it will be the maximum allowed value of merged entries per group. This ensures that on both sides the ID-values per V-group can be unique.

df1g = df1.groupby("V").ID1.count().reset_index().rename(columns={"ID1":"C1"})
df2g = df2.groupby("V").ID2.count().reset_index().rename(columns={"ID2":"C2"})
df12g = pd.merge(df1g,df2g,on='V')
df12g["Max"] = df12g[["C1","C2"]].min(axis=1)
df = pd.merge(df,df12g[['V','Max']],on='V')
df = df.sort_values(['V','Delta']).reset_index(drop=True)

This is my sorted example data:

     V  ID1  ID2    Delta  Max
0   V1    2   11 01:00:00    2
1   V1    3   12 12:00:00    2
2   V1    3   11 13:00:00    2
3   V2    5   13 02:00:00    1
4   V3    8   18 01:00:00    4
5   V3    6   14 02:00:00    4
6   V3    7   18 02:00:00    4
7   V3    8   17 02:00:00    4
8   V3    7   17 03:00:00    4
9   V3    6   18 04:00:00    4
10  V3    6   17 05:00:00    4
11  V3    9   16 16:00:00    4
12  V3    9   15 18:00:00    4
13  V3    9   14 20:00:00    4
14  V3    9   18 22:00:00    4
15  V3    9   17 23:00:00    4

Group V1 has 3 entries but is only allowed 2
Group V2 has 1 entry and is only allowed 1
Group V3 has 12 entries but is only allowed 4

I now need to find for each ID1 the ID2 entry with the lowest Delta but the combinations must be unique.

That means because in line 4 ID1 8 is paired with ID2 18 in line 6 ID1 7 must not be paired with ID2 18.

The result I want is essentially this:

     V  ID1  ID2    Delta  Max
0   V1    2   11 01:00:00    2
1   V1    3   12 12:00:00    2
3   V2    5   13 02:00:00    1
4   V3    8   18 01:00:00    4
5   V3    6   14 02:00:00    4
8   V3    7   17 03:00:00    4
11  V3    9   16 16:00:00    4

And I can't wrap my head around how to achieve this.

Simple approaches like

df1 = df.drop_duplicates('ID1')
df2 = df.drop_duplicates('ID2')
result = pd.merge(df1,df2)

obviously don't work out properly.

Is it even possible to solve this without iterating over the sorted rows and building a memory of already occupied ID2-values?

Related questions

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

1 answer

See also questions close to this topic