# AMET v1.4 error soccerplot

**URL:** <https://forum.cmascenter.org/t/amet-v1-4-error-soccerplot/2619>\
**Category:** AMET\
**Created:** [August 26, 2021, 2:00pm UTC](https://forum.cmascenter.org/t/amet-v1-4-error-soccerplot/2619 "2021-08-26T14:00:01Z")\
**Posts on this page:** 12\
**Page:** 1

<div class="post-metadata">

**Author:** ![huangj1311](https://avatars.discourse-cdn.com/v4/letter/h/8edcca/32.png) [@huangj1311](https://forum.cmascenter.org/u/huangj1311)\
**Post date:** [August 26, 2021, 2:00pm UTC](https://forum.cmascenter.org/t/amet-v1-4-error-soccerplot/2619/1 "2021-08-26T14:00:01Z")

</div>

Hi @wyat.appel et al.,

I’ve installed AMET v1.4 and using it now, and having some questions about it. Just wonder can you help me to find the potential reasons.

1. 

```auto
Inserting metadata for station 01-001-0003 for toar network
 Error in .local(conn, statement, ...) :
  could not run statement: Unknown column 'co_network' in 'field list'
[1] "Failed to add sites to database with the error: Error in .local(conn, statement, ...) : \n could not run statement: Unknown column 'co_network' in 'field list'\n."

```

is this related to [https://github.com/USEPA/AMET/issues/78](https://github.com/USEPA/AMET/issues/78)

does this impact the data structure on database?

1. I am testing the tool using example data downloading from UNC google drive,  
batch plots and most plots went OK. However, I found some glitches in soccerplot,  
If I only assign one AMET\_AQSPECIES, it works fine. I’ve tried most species they are OK, eg

```auto
setenv AMET_AQSPECIES O3

```

However, if I assign multiple species

```auto
setenv AMET_AQSPECIES O3, EC, PM_TOT

```

it returns

```auto
Error in lsfit(data.df$ob_val, data.df$mod_val) :
  only 0 cases, but 2 variables
Calls: DomainStats -> lsfit
In addition: Warning message:
In lsfit(data.df$ob_val, data.df$mod_val) : 315825 missing values deleted

```

I modified R and output  
stats\_all.df  
data\_all.df

but there are some issues when matching up obs and mod data, I double check the mod data are not organized correctly.

any suggestion will be very helpful  
Thanks  
Joey

---

<div class="post-metadata">

**Author:** ![wyat.appel](https://avatars.discourse-cdn.com/v4/letter/w/3d9bf3/32.png) [@wyat.appel](https://forum.cmascenter.org/u/wyat.appel)\
**Post date:** [August 26, 2021, 2:24pm UTC](https://forum.cmascenter.org/t/amet-v1-4-error-soccerplot/2619/2 "2021-08-26T14:24:59Z")

</div>

Hi Joey,

Let me quickly answer your first question, and then I’ll get back to you on your second question. You are correct. The error you are getting regarding co\_network is the same as described in the thread you linked. So, you can use the solution described in that thread to fix the issue you’re having. Let me know if you still have trouble after implementing that fix.

I’ll get back to you on your second issue once I have a chance to sort through it. Hopefully something simple.

Wyat

---

<div class="post-metadata">

**Author:** ![huangj1311](https://avatars.discourse-cdn.com/v4/letter/h/8edcca/32.png) [@huangj1311](https://forum.cmascenter.org/u/huangj1311)\
**Post date:** [August 26, 2021, 2:32pm UTC](https://forum.cmascenter.org/t/amet-v1-4-error-soccerplot/2619/3 "2021-08-26T14:32:41Z")

</div>

Wyat,

Thanks for the quick response, but I do have this correction in my script.

```auto
 co_network varchar(200)

```

I compared the data\_all.df (v1.3 and v1.4), I noticed there is a column shift between v1.3 and v1.4.

 ![image](https://canada1.discourse-cdn.com/flex027/uploads/cmas/original/2X/7/7fdb2b411cfd63a68cdf05af6296ce63295e62fd.png)

Joey

---

<div class="post-metadata">

**Author:** ![wyat.appel](https://avatars.discourse-cdn.com/v4/letter/w/3d9bf3/32.png) [@wyat.appel](https://forum.cmascenter.org/u/wyat.appel)\
**Post date:** [August 26, 2021, 2:54pm UTC](https://forum.cmascenter.org/t/amet-v1-4-error-soccerplot/2619/4 "2021-08-26T14:54:35Z")

</div>

Is this data.all.df specifically from the soccergoal plot script? It sounds like most things were working for you, so I assume this is related specifically to the soccergoal plot.

Wyat

---

<div class="post-metadata">

**Author:** ![huangj1311](https://avatars.discourse-cdn.com/v4/letter/h/8edcca/32.png) [@huangj1311](https://forum.cmascenter.org/u/huangj1311)\
**Post date:** [August 26, 2021, 3:05pm UTC](https://forum.cmascenter.org/t/amet-v1-4-error-soccerplot/2619/5 "2021-08-26T15:05:12Z")

</div>

I changed a line in AQ\_Soccerplot.R “l” starting from 9 instead of 10, and this fixed the issue

```auto
  # l <- 10
  l <- 9 #by JH
   for (i in 1:length(species)) { # For each species, calculate several statistics
     data_all.df <- data.frame(network=I(aqdat_query.df$network),stat_id=I(aqdat_query.df$stat_id),lat=aqdat_query.df$lat,lon=aqdat_query.df$lon,ob_val=aqdat_query.df[,l],mod_val=aqdat_query.df[,(l+1)]) # Create properly formatted dataframe to use with DomainStats function
      good_count <- sum(!is.na(data_all.df$ob_val)) # Count the number of non-missing values

```

Does this mean my data structure in database is corrupted ?

this is possible due to the co\_network column issue ?

Joey

---

<div class="post-metadata">

**Author:** ![wyat.appel](https://avatars.discourse-cdn.com/v4/letter/w/3d9bf3/32.png) [@wyat.appel](https://forum.cmascenter.org/u/wyat.appel)\
**Post date:** [August 26, 2021, 3:10pm UTC](https://forum.cmascenter.org/t/amet-v1-4-error-soccerplot/2619/6 "2021-08-26T15:10:41Z")

</div>

Here’s, I believe, a fix to the issue. In the AQ\_Soccerplot.R script (found in AMETBASE/R\_analysis\_code), please make the following minor change to line 76:

l ← 10  
to  
l ← 9

I believe there may be an indexing issue I corrected in my most recent (yet to be released) code. Hopefully that corrects the problem you’re seeing in the 1.4 code.

Let me know if that works or not. Thanks!

Wyat

---

<div class="post-metadata">

**Author:** ![huangj1311](https://avatars.discourse-cdn.com/v4/letter/h/8edcca/32.png) [@huangj1311](https://forum.cmascenter.org/u/huangj1311)\
**Post date:** [August 26, 2021, 3:11pm UTC](https://forum.cmascenter.org/t/amet-v1-4-error-soccerplot/2619/7 "2021-08-26T15:11:36Z")

</div>

Yes, that is what I did, thanks!!!  
How about the CO network column issue?

Thanks

---

<div class="post-metadata">

**Author:** ![wyat.appel](https://avatars.discourse-cdn.com/v4/letter/w/3d9bf3/32.png) [@wyat.appel](https://forum.cmascenter.org/u/wyat.appel)\
**Post date:** [August 26, 2021, 3:15pm UTC](https://forum.cmascenter.org/t/amet-v1-4-error-soccerplot/2619/8 "2021-08-26T15:15:04Z")

</div>

So are you still receiving the same error you originally reported relating to the co\_network field?

Unknown column ‘co\_network’ in ‘field list’

---

<div class="post-metadata">

**Author:** ![huangj1311](https://avatars.discourse-cdn.com/v4/letter/h/8edcca/32.png) [@huangj1311](https://forum.cmascenter.org/u/huangj1311)\
**Post date:** [August 26, 2021, 3:17pm UTC](https://forum.cmascenter.org/t/amet-v1-4-error-soccerplot/2619/9 "2021-08-26T15:17:10Z")

</div>

Yes, I have the correction in my AQ\_setup\_dbase.R, but still showing the error.

---

<div class="post-metadata">

**Author:** ![wyat.appel](https://avatars.discourse-cdn.com/v4/letter/w/3d9bf3/32.png) [@wyat.appel](https://forum.cmascenter.org/u/wyat.appel)\
**Post date:** [August 26, 2021, 3:42pm UTC](https://forum.cmascenter.org/t/amet-v1-4-error-soccerplot/2619/10 "2021-08-26T15:42:16Z")

</div>

Gotcha. So, adding the co\_network varchar(200) to the script will fix the problem for any future databases you create, but does not fix the problem with the existing database, specifically in the site\_metadata table. To fix the existing table (without starting from scratch), you can do the following, which I pasted for the thread discussion you linked discussing this issue. If you’re comfortable working with MySQL, I suggest the third option I present below.

**Note that if you’ve already run the AMET setup, you’ll need to either manually delete the “site\_metadata” table from your AMET database so that it can be recreated without the column missing, or delete your entire AMET setup and start fresh using the script provided with the AMET code. I would recommend that if you’ve already done analysis and loaded data into the database that you attempt to manually delete “site\_metadata” table and have it recreated automatically when you run the aqExample\_post\_only.csh script.**

**A third option would be to simply edit your existing “site\_metadata” table in the MySQL database to add the missing column manually. This can be accomplished by logging into MySQL with your username and password, switching to your AMET database, and then editing the existing table. An example is given below.**

**\> mysql -h mysql\_host\_name -u user\_name -p**  
**\> enter password**  
**\> use AMET\_DATABASE;**  
**\> alter table site\_metadata add column co\_network varchar(200) after network;**  
**\> exit**

**You should then re-run your aqExample\_post\_only.csh script with the reload\_metadata flag set to T. That should then properly load your site metadata.**

---

<div class="post-metadata">

**Author:** ![huangj1311](https://avatars.discourse-cdn.com/v4/letter/h/8edcca/32.png) [@huangj1311](https://forum.cmascenter.org/u/huangj1311)\
**Post date:** [August 26, 2021, 4:32pm UTC](https://forum.cmascenter.org/t/amet-v1-4-error-soccerplot/2619/11 "2021-08-26T16:32:56Z")

</div>

Does this impact AMET v1.3 data upload?

I have v1.3 and v1.4 on the same machine, and I will still use v1.3 for model evaluation, before I am 100% sure v1.4 is working properly. I am worrying this might mess up the v1.3 settings?

Thanks

---

<div class="post-metadata">

**Author:** ![wyat.appel](https://avatars.discourse-cdn.com/v4/letter/w/3d9bf3/32.png) [@wyat.appel](https://forum.cmascenter.org/u/wyat.appel)\
**Post date:** [August 27, 2021, 6:32pm UTC](https://forum.cmascenter.org/t/amet-v1-4-error-soccerplot/2619/12 "2021-08-27T18:32:14Z")

</div>

Sorry for the delay getting back to you. So, do you have v1.3 and 1.4 data in the same AMET database? Or do you have separate databases for your v1.3 and v1.4 work?

I don’t think it should actually matter. The change I’m proposing simply adds an additional column to the site\_metadata table (each database has it’s own site\_metadata table). The error you note above occurs because AMET is trying to populate the site\_metadata table with co\_network data, but the co\_network column does not exist. By adding the co\_network column to the site\_metadata table, the table will be able to properly populate.

I think you should be fine making this update to the site\_metadata table. It will not directly affect any of the data you have stored in project tables in the database. The site\_metadata table simply houses the information for all the sites (state, lat, lon, elevation, etc.). So, effectively you’re just updating the site information available to AMET, which should primarily be new sites that have come online, or in this case, additional site information I’ve added to the site metadata table.

Hopefully that’s clear. If not, I’m happy to discuss further.

Wyat