What field is equalent to "site"

[djhocking]

In Kyle's old files there was a column called site that looked like

$ site      : chr  "MADEP_W0013_T1" "MADEP_W0013_T1" "MADEP_W0013_T1" "MADEP_W0013_T1" ...

Where is that information now (table and column) within the Postgres database?

[walkerjeffd]

Kyle's site variable was the combination of agencies.name and locations.name. So you could create it by:

df$site <- paste(df$agency_name, df$location_name, sep='_')

[djhocking]

Thanks. I have too much legacy code to change what I use for site in the short term, so I'll do the recombination for now as you suggest.

[walkerjeffd]

ok. it could also be automatically created in the sql query. but if your using dplyr to get the temp data separate from the daymet data, probably not worth it for now and just do a mutate(site=...).

On Thu, Jan 15, 2015 at 3:00 PM, Daniel J. Hocking <notifications@github.com

wrote:

Thanks. I have too much legacy code to change what I use for site in the short term, so I'll do the recombination for now as you suggest.

— Reply to this email directly or view it on GitHub https://github.com/Conte-Ecology/conteStreamTemperature_web/issues/13#issuecomment-70151294 .

Jeffrey D. Walker, PhD http://walkerjeff.com

[djhocking]

If it could be added to the SQL query within dplyr calling to a connection that would be handy but I'm not sure if there is any equivalent to paste when dplyr is calling to a database. My current code for pulling in the daymet data for predictions is

    tbl_daymet <- tbl(db, 'daymet') %>%
      dplyr::filter(featureid %in% catchmentid) %>%
      dplyr::mutate(airTemp = (tmax + tmin)/2)

This will have to be done MANY times in a loop or equivalent, so anything that can speed it up would be valuable.

[walkerjeffd]

actually yeah i think I can add an equivalent to paste in a dplyr call

you need the site in the daymet table though? i thought daymet would be by featureid, because if you have to two sites in the same catchment you would get two copies of the same daymet timeseries. but if thats the case, we can do that.

[djhocking]

Unfortunately, I will need to recreate the sites in this case. This is just a temporary hack for the presentations in ME next week. I'm using the output from the previous model runs to make predictions using the daymet data and new data in the database. The featureids in the old data are not the same as the new data, so I have to use the site info.

If you have a quick way to add site to my code, I'd greatly appreciate it.

[walkerjeffd]

yeah i can help. either post the script to a gist, or push it to a github repo.

On Thu, Jan 15, 2015 at 4:02 PM, Daniel J. Hocking <notifications@github.com

wrote:

Unfortunately, I will need to recreate the sites in this case. This is just a temporary hack for the presentations in ME next week. I'm using the output from the previous model runs to make predictions using the daymet data and new data in the database. The featureids in the old data are not the same as the new data, so I have to use the site info.

If you have a quick way to add site to my code, I'd greatly appreciate it.

— Reply to this email directly or view it on GitHub https://github.com/Conte-Ecology/conteStreamTemperature_web/issues/13#issuecomment-70161010 .

Jeffrey D. Walker, PhD http://walkerjeff.com

[djhocking]

https://gist.github.com/djhocking/a9cf393edce6274ec899

It's lines ~20-30 where I think adding site would be needed, although I guess I will also have to add it to the top code since catchmentid won't match when I create a loop.

[djhocking]

Thanks. End result from the gist just so it's here to refer back to easily:

  qry_daymet <- tbl(db, 'daymet') %>%
    left_join(select(qry_locations, site, featureid), by='featureid') %>%
    filter(featureid %in% catchmentid) %>%
    mutate(airTemp = (tmax + tmin)/2)

df_daymet <- collect(qry_daymet)