Commodity Naming Scheme

[qjhart]

@judyhanna / @blyeo , We get yields and acreages from NASS, and that seems like the best place to start with naming our commodities. You have seen a list of the commodities. Right now, some of our existing budgets do not match these names. I have done the following recasting of the existing budgets to the NASS names. Note that there are sub -categories we could include, but I would think that would be in the future.

For example, alfalfa is the 'HAY' commodity, but of subtype 'HAY, ALFALFA'

Note, because of that, there are multiple different GRASSES budget in OR.

with x(old,nass) as (VALUES
('ALFALFA','HAY'),
('BEAS, DRY EDIBLE','BEANS'),
('CORN GRAINS','CORN'),
('CORN SILAGE','CORN'),
('DRY BEANS','BEANS'),
('FESCUE SEED','GRASSES'),
('GRASS AND HAY','HAY & HAYLAGE'),
('GRASSHAY','HAY & HAYLAGE'),
('LENTIL','LENTILS'),
('ORCHARD GRASS','GRASSES'),
('ORCHARD GRASS SEED','GRASSES'),
('RYEGRASS SEED','GRASSES'),
('SPRING WHEAT','WHEAT'),
('SUDANGRASS','GRASSES'),
('SUGAR BEET','SUGARBEETS'),
('SUGAR BEETS','SUGARBEETS'),
('WINTER WHEAT','WHEAT')
)

[qjhart]

For the record, I have developed production values for all the sub-commodity types in NASS where available. This is actually a bit tricky, because sometimes NASS will not summarize all the subcommodities.

Here's a wheat example:

nass=# select commodity,subcommodity,irrigated,total from harvest_total_and_sum where commodity='WHEAT' and year=2007 and location='06019';

commodity	subcommodity	irrigated	total
WHEAT	{}	31086	33006
WHEAT	{SPRING}	11654	11654
WHEAT	{SPRING,DURUM}	10398	10398
WHEAT	{SPRING,"(EXCL DURUM)"}	1256	1256
WHEAT	{WINTER}	19432	21352

[qjhart]

@blyeo @ncparker. This is similar to the survey based data, however they do not always match. This is for the same region.

nass=# select location,commodity,subcommodity,irrigated,non_irrigated,unspecified from yield_total_and_sum where commodity='WHEAT' and year=2009 and location ~'^06ag51';

location	commodity	subcommodity	irrigated	non_irrigated	unspecified
06ag51	WHEAT	{}			83.75
06ag51	WHEAT	{WINTER}			84.5
06ag51	WHEAT	{WINTER}			83

There are two values for winter wheat, because NASS reports two different values (never saw that before),

[ncparker]

@blyeo @qjhart That wheat example is interesting because there is another nested layer within the Spring wheat subcommodity. Looking at he old budgets/yields. I would say we should keep spring and winter wheat separated but the hay and haylage crops can be combined. Spring and winter wheat occupy the fields at different times which is a factor in the model.

[blyeo]

Please update the yield Excel table and then let me know. I will match the yield data again. Also, there are also missing counties in addition to not matching up commodity. Do you need me to give you a list of missing counties and for what crops?

On May 14, 2015, at 17:28, ncparker notifications@github.com wrote:

That wheat example is interesting because there is another nested layer within the Spring wheat subcommodity. Looking at he old budgets/yields. I would say we should keep spring and winter wheat separated but the hay and haylage crops can be combined. Spring and winter wheat occupy the fields at different times which is a factor in the model.

― Reply to this email directly or view it on GitHub.

[blyeo]

Hi Quinn, Nathan,

Not sure if you got my message below. Curious to know if the yield table has been updated. Is it possible for you to query the data so that it matches the crop name we have on our mastersheet? Also, there are some crop names that do match up but some of the FIPS code are missing. Would you like me to generate a list of crops that do match up by name but are missing the FIPS/County that we have in our mastersheet? Thanks!

OldMASTERNameNewMASTERNamealfalfa hayhayalfalfa haylagehaylagebarleybarleybeans, dry edible beansbeans, dry limabeanscanolacanolacorn grain corn corn silage corn grass hayhaygrass haylagehaylagelentilslentilsoatsoatspoplarpoplar potatoespotatoes spring wheatwheatsugarbeetssugarbeetswinter wheatwheat

On Thu, May 14, 2015 at 6:19 PM, Boon-Ling Yeo blyeo@ucdavis.edu wrote:

Please update the yield Excel table and then let me know. I will match the yield data again. Also, there are also missing counties in addition to not matching up commodity. Do you need me to give you a list of missing counties and for what crops?

On May 14, 2015, at 17:28, ncparker notifications@github.com wrote:

That wheat example is interesting because there is another nested layer within the Spring wheat subcommodity. Looking at he old budgets/yields. I would say we should keep spring and winter wheat separated but the hay and haylage crops can be combined. Spring and winter wheat occupy the fields at different times which is a factor in the model.

— Reply to this email directly or view it on GitHub https://github.com/CSTARS/farm-budgets-data/issues/21#issuecomment-102208785 .

[blyeo]

Hi Quinn,

I just wanted to follow-up on my message below. Nathan had mentioned that the table I cut and pasted below had appeared to look like a garbel on git hub.

The first column is the commodity name we have in the master sheet and the second column is what's in Quinn's yield Excel file. The two column names need to match up in order for me to match the yield data. So we have two options:

1) Quinn query the data again so that he can get the data in terms of alfalfa hay, alfalfa haylage, etc. instead of just hay or halage (for example). 2) I can ASSUME that alfalfa hay = hay, alfalfa haylage = haylage in Quinn's data.

Nathan says: The issue for Quinn is the your OLDMastername commodities are made up of multiple commodities in the NASS data which is what Quinn was pointing out in his comments. Spring Wheat has 3 different sub commodities in NASS and if you sum them up you will end up double counting stuff. So you need to know which ones to use. For Spring Wheat, I would only use "Spring Wheat" and ignore "Spring Wheat, Durum" and "Spring Wheat, Excluding Durum" because those two sum to the "Spring Wheat" value. I can't say how this should be done for all of them though because I don't know what they are.

For the other commodities in NASS that we cannot match with the commodities in the "OLD MASTERName" column, we can ask Steve how best to grop them as I am not very familiar with these crops either.

Thanks, Boon Ling

On Fri, May 15, 2015 at 2:48 PM, Boon-Ling Yeo blyeo@ucdavis.edu wrote:

Hi Quinn, Nathan,

Not sure if you got my message below. Curious to know if the yield table has been updated. Is it possible for you to query the data so that it matches the crop name we have on our mastersheet? Also, there are some crop names that do match up but some of the FIPS code are missing. Would you like me to generate a list of crops that do match up by name but are missing the FIPS/County that we have in our mastersheet? Thanks!

OldMASTERNameNewMASTERNamealfalfa hayhayalfalfa haylagehaylagebarleybarleybeans, dry edible beansbeans, dry limabeanscanolacanolacorn grain corn corn silagecorn grass hayhaygrass haylagehaylagelentilslentilsoatsoatspoplar poplarpotatoespotatoes spring wheatwheatsugarbeetssugarbeetswinter wheat wheat

On Thu, May 14, 2015 at 6:19 PM, Boon-Ling Yeo blyeo@ucdavis.edu wrote:

Please update the yield Excel table and then let me know. I will match the yield data again. Also, there are also missing counties in addition to not matching up commodity. Do you need me to give you a list of missing counties and for what crops?

On May 14, 2015, at 17:28, ncparker notifications@github.com wrote:

That wheat example is interesting because there is another nested layer within the Spring wheat subcommodity. Looking at he old budgets/yields. I would say we should keep spring and winter wheat separated but the hay and haylage crops can be combined. Spring and winter wheat occupy the fields at different times which is a factor in the model.

— Reply to this email directly or view it on GitHub https://github.com/CSTARS/farm-budgets-data/issues/21#issuecomment-102208785 .

[blyeo]

Hi Quinn,

I haven't heard from you. Just writing to follow-up. Would you be able to send me the updated yield numbers with the crop name specification as I've listed below sometime today?

Many thanks, Boon Ling

On Mon, May 18, 2015 at 1:05 PM, Boon-Ling Yeo blyeo@ucdavis.edu wrote:

Hi Quinn,

I just wanted to follow-up on my message below. Nathan had mentioned that the table I cut and pasted below had appeared to look like a garbel on git hub.

The first column is the commodity name we have in the master sheet and the second column is what's in Quinn's yield Excel file. The two column names need to match up in order for me to match the yield data. So we have two options:

1) Quinn query the data again so that he can get the data in terms of alfalfa hay, alfalfa haylage, etc. instead of just hay or halage (for example). 2) I can ASSUME that alfalfa hay = hay, alfalfa haylage = haylage in Quinn's data.

Nathan says: The issue for Quinn is the your OLDMastername commodities are made up of multiple commodities in the NASS data which is what Quinn was pointing out in his comments. Spring Wheat has 3 different sub commodities in NASS and if you sum them up you will end up double counting stuff. So you need to know which ones to use. For Spring Wheat, I would only use "Spring Wheat" and ignore "Spring Wheat, Durum" and "Spring Wheat, Excluding Durum" because those two sum to the "Spring Wheat" value. I can't say how this should be done for all of them though because I don't know what they are.

For the other commodities in NASS that we cannot match with the commodities in the "OLD MASTERName" column, we can ask Steve how best to grop them as I am not very familiar with these crops either.

Thanks, Boon Ling

On Fri, May 15, 2015 at 2:48 PM, Boon-Ling Yeo blyeo@ucdavis.edu wrote:

Hi Quinn, Nathan,

Not sure if you got my message below. Curious to know if the yield table has been updated. Is it possible for you to query the data so that it matches the crop name we have on our mastersheet? Also, there are some crop names that do match up but some of the FIPS code are missing. Would you like me to generate a list of crops that do match up by name but are missing the FIPS/County that we have in our mastersheet? Thanks!

OldMASTERNameNewMASTERNamealfalfa hayhayalfalfa haylagehaylagebarley barleybeans, dry edible beansbeans, dry limabeanscanolacanolacorn grain corn corn silagecorn grass hayhaygrass haylagehaylagelentilslentilsoats oatspoplarpoplarpotatoespotatoes spring wheatwheatsugarbeetssugarbeetswinter wheatwheat

On Thu, May 14, 2015 at 6:19 PM, Boon-Ling Yeo blyeo@ucdavis.edu wrote:

Please update the yield Excel table and then let me know. I will match the yield data again. Also, there are also missing counties in addition to not matching up commodity. Do you need me to give you a list of missing counties and for what crops?

On May 14, 2015, at 17:28, ncparker notifications@github.com wrote:

That wheat example is interesting because there is another nested layer within the Spring wheat subcommodity. Looking at he old budgets/yields. I would say we should keep spring and winter wheat separated but the hay and haylage crops can be combined. Spring and winter wheat occupy the fields at different times which is a factor in the model.

— Reply to this email directly or view it on GitHub https://github.com/CSTARS/farm-budgets-data/issues/21#issuecomment-102208785 .

[qjhart]

@blyeo @ncparker I have updated the NASS data to include all sub commodities, further, I've added a file, commodity_list that shows all commodities, and whether there was harvest, yield, and/or price data. I can see some issues here, but nothing that shoud affect this study. I would suggest we use NASS names, with the following table somewhat filled in, but I'm no expert on these budgets.

current	nass
alfalfa hay	HAY, ALFALFA
alfalfa haylage	HAYLAGE, ALFALFA
barley	BARLEY
beans,dry edible	BEANS, DRY EDIBLE
beans, dry lima	These are in above class, BEANS, DRY EDIBLE, LIMA
canola	CANOLA
corn grain	CORN, GRAIN
corn silage	CORN, SILAGE
grass hay	HAY, TAME, (EXCL ALFALFA & SMALL GRAIN)
grass haylage	HAYLAGE, (EXCL ALFALFA)
lentils	LENTILS
oats	OATS
poplar	POPLAR
potatoes	POTATOES
spring wheat	WHEAT, SPRING
sugarbeets	SUGARBEETS
winter wheat	WHEAT, WINTER

[ncparker]

@qjhart @blyeo When I was checking the baseline pixel based data against the areas that were in BCAM a long time ago, I had to match the NASS names to the BCAM crop names. The table Quinn made looks the same as what I had. The two missing items are below.

current | NASS GrassHay | HAY, TAME, (EXCL ALFALFA & SMALL GRAIN) GrassHaylage | HAYLAGE, (EXCL ALFALFA)

On Tue, May 19, 2015 at 4:25 PM Quinn Hart notifications@github.com wrote:

@blyeo https://github.com/blyeo @ncparker https://github.com/ncparker I have updated the NASS data to include all sub commodities, further, I've added a file, commodity_list that shows all commodities, and whether there was harvest, yield, and/or price data. I can see some issues here, but nothing that shoud affect this study. I would suggest we use NASS names, with the following table somewhat filled in, but I'm no expert on these budgets. current nass alfalfa hay HAY, ALFALFA alfalfa haylage HAYLAGE, ALFALFA barley BARLEY beans,dry edible BEANS, DRY EDIBLE beans, dry lima These are in above class, BEANS, DRY EDIBLE, LIMA canola CANOLA corn grain CORN, GRAIN corn silage CORN, SILAGE grass hay DON'T KNOW grass haylage DON'T KNOW lentils LENTILS oats OATS poplar POPLAR potatoes POTATOES spring wheat WHEAT, SPRING sugarbeets SUGARBEETS winter wheat WHEAT, WINTER

— Reply to this email directly or view it on GitHub https://github.com/CSTARS/farm-budgets-data/issues/21#issuecomment-103696656 .

[blyeo]

Awesome!! I will take a closer look at this after I sent this e-mail out.

I'd like to follow-up with you on another thing. We still need to assign land rental rate, irrigation cost, for poplar trees... were you planning on getting poplar yield information at FIPS (county), CMZ, and state level? That way I can match the land rental rate for poplar at the FIPS (county) level, if it is not available, I will match to Ag district, if that's not available, I will use state level rental rate. This is also true for the irrigation cost.

Thank you, Boon Ling

On Tue, May 19, 2015 at 4:25 PM, Quinn Hart notifications@github.com wrote:

@blyeo https://github.com/blyeo @ncparker https://github.com/ncparker I have updated the NASS data to include all sub commodities, further, I've added a file, commodity_list that shows all commodities, and whether there was harvest, yield, and/or price data. I can see some issues here, but nothing that shoud affect this study. I would suggest we use NASS names, with the following table somewhat filled in, but I'm no expert on these budgets. current nass alfalfa hay HAY, ALFALFA alfalfa haylage HAYLAGE, ALFALFA barley BARLEY beans,dry edible BEANS, DRY EDIBLE beans, dry lima These are in above class, BEANS, DRY EDIBLE, LIMA canola CANOLA corn grain CORN, GRAIN corn silage CORN, SILAGE grass hay DON'T KNOW grass haylage DON'T KNOW lentils LENTILS oats OATS poplar POPLAR potatoes POTATOES spring wheat WHEAT, SPRING sugarbeets SUGARBEETS winter wheat WHEAT, WINTER

— Reply to this email directly or view it on GitHub https://github.com/CSTARS/farm-budgets-data/issues/21#issuecomment-103696656 .

[ncparker]

@qjhart @blyeo I vote for poplar modeled at the county level. We were thinking of taking the whole SWAP model to the county level, correct?

[blyeo]

Hi--I uploaded the yield file this morning. The title of the file name is: commodity_yield_bly

On Wed, May 27, 2015 at 3:11 PM, Quinn Hart notifications@github.com wrote:

@blyeo https://github.com/blyeo, you didn't upload a new file yet. However, I id'd you on an issue CSTARS/nass-summary#2 https://github.com/CSTARS/nass-summary/issues/2 where I will try and pull production data from the nass census data.

— Reply to this email directly or view it on GitHub https://github.com/CSTARS/farm-budgets-data/issues/21#issuecomment-106094351 .