Plot the environmental concentration of organofluorine compounds on a map using open data

Let's visualize the concentration of organic fluorine compounds by utilizing the data recorded in ChemTHEATER

What is an organic fluorine compound?

It has both hydrophilic and lipophilic properties, and is used in a wide range of applications such as water repellents, digestive agents, oil agents, and etching agents.

For an easy-to-understand explanation of organofluorine compounds, see NHK's Close-up Gendai HP. https://www.nhk.or.jp/gendai/articles/4280/index.html

Due to its harmfulness, the manufacture, use, import and export of perfluorooctanoic acid (PFOA) among organofluorine compounds will be banned internationally from 2019, and perfluorooctanesulfonate (PFOS) will be banned from 2009. Manufacturing, use and import / export are restricted.

Standard values for organic fluorine compounds, etc.

In recent years, both substances have become problems such as being detected in well water and flowing out into rivers. In response to this, the Ministry of Health, Labor and Welfare added PFOS and PFOA to the water quality management target setting items (enforced on April 1, 2nd year of Reiwa). The target value of the tap water quality standard is 50 ng / L, which is the total value of PFOS and PFOA. https://www.mhlw.go.jp/stf/seisakunitsuite/bunya/topics/bukyoku/kenkou/suido/kijun/index.html

In addition, the Ministry of the Environment conducted a nationwide abundance survey of PFOS and PFOA in the water environment, and conducted "Enforcement of Environmental Standards for the Protection of Human Health Related to Water Pollution (Notice)" (May 28, 2nd year of Ordinance). (Appendix), we set 50 ng / L as the total value of PFOS and PFOA as a provisional target value for the water environment. Results of PFOA and PFOA national abundance survey in the first year of Reiwa https://www.env.go.jp/press/108091.html

By the way, in the survey results for water purification plants released by the Ministry of Health, Labor and Welfare in June 2nd year of Reiwa, there was no place where the provisional target value of tap water, PFOS + PFOA 50 ng / L, was exceeded. https://www.mhlw.go.jp/content/10900000/000638290.pdf

Obtain environmental concentration data of organofluorine compounds from ChemTHEATER

ChemTHEATER publishes monitoring data of chemical substance concentrations in the environment, and also includes data on organofluorine compounds. Let's actually see at what concentration PFOS and PFOA were detected, and how high or low the total value is compared to the reference value.

1. Select "Sample Search" from the menu bar of ChemTHEATER.
2. Select "Abiotic --Water" from "Sample Type".
3. Select "Perfluoroalkyl and polyfluoroalkyl substances" from the "Chemicals Group".
4. Select "Asia" from "Collection area" and set the collection country to "Japan".
5. Click the "Search" button to output a list of samples that meet the conditions.
6. "Export samples" outputs the sample information, and "Export measured data" outputs the measured values of the target chemical substance as a tab-delimited text file.

Save the exported file in any directory and use it for analysis.

Format the exported data

Load the required library.

import pandas as pd

Read information on measured values of chemical substances.

data_file = "measureddata_20200521044415.tsv"
data = pd.read_csv(data_file, delimiter="\t")
data

3092 rows × 13 columns

Then, the sample information is read.

sample_file = "samples_20200521044410.tsv"
sample = pd.read_csv(sample_file, delimiter="\t")
sample

276 rows × 66 columns

pfos = data[data["ChemicalName"] == "PFOS"] #Extract only data whose Chemical Name is PFOS
pfoa = data[data["ChemicalName"] == "PFOA"] #Extract only data with Chemical Name PFOA

If you check the contents of each, it looks like the following.

pfos

276 rows × 13 columns

pfoa

252 rows × 13 columns

From the extracted data of PFOS and PFOA, only the columns of SampleID and BeautifulValue are extracted, and the column names of the measured values are both MeasuredValue, so change them to PFOS and PFOA, respectively.

pfos = pfos[["SampleID","MeasuredValue"]].rename(columns={'MeasuredValue': 'PFOS'})
pfoa = pfoa[["SampleID","MeasuredValue"]].rename(columns={'MeasuredValue': 'PFOA'})

Merge PFOS and PFOA data frames with SampleID.

df = pd.merge(pfos, pfoa, on="SampleID").astype({"PFOS": float}, {"PFOA": float})
df

252 rows × 3 columns

What I want to know this time is the total value of the concentrations of PFOS and PFOA, so create a column called TOTAL and enter the total value there.

df['TOTAL'] = df.sum(axis=1, numeric_only=True)
df

252 rows × 4 columns

Extract only the Sample ID and latitude / longitude data from the sample table.

sample = sample[["SampleID", "CollectionLongitudeFrom", "CollectionLatitudeFrom"]]
sample

276 rows × 3 columns

Merge this with the previously created concentration table and SampleID.

df = pd.merge(df, sample, on="SampleID")
df

252 rows × 6 columns

Save the completed file in csv format.

df.to_csv("sum_pfcs.csv")

Load this into QGIS.

Display concentration data on a map using QGIS

Download QGIS from below. https://www.qgis.org/ja/site/ Please refer to the various sites that explain how to use QGIS.

Map information (GML shape file) is downloaded from the GIS homepage of the Ministry of Land, Infrastructure, Transport and Tourism. https://nlftp.mlit.go.jp/index.html

For the time being, this time, we will use the administrative area of the 2. policy area from the download of national land numerical information. Select and download the "nationwide" data from the page below. Please choose the year according to your purpose.

Launch QGIS, click the Vector tab from Data Source Manager, select the national administrative area shapefile downloaded above and click "Add".

The map of Japan will be loaded as shown below, so change it to your favorite color.

Again, click the Delimited Text tab from the Data Source Manager and select the CSV file (sum_pfcs.csv) created in the previous section. After that, select longitude (CollectionLongitudeFrom) in the X field of the geometry definition and latitude (CollectionLatitudeFrom) in the Y field, and click "Add".

Then, the sampling points are plotted on the map. Next, color-code by density. Double-click "sum_pfcs" from the layer to open the layer properties. Select the Symbolism tab. Select "Divided into stages" for the symbol, and select "TOTAL" for the column because you want to color-code by the concentration of PFOS + PFOA. Change the symbol to your liking. If you change the number of classifications while keeping the mode at equal intervals, the classifications at equal intervals are automatically output. Here, the number of classifications is 2, and the value of a group of small numbers is 0-0.05. (In ChemTHEATER, the concentration of chemical substances in water is unified at µg / L, so if you want to color-code whether or not it exceeds the standard value of 50 ng / L, you need to set it to 0.05.) By default, Since the high density plot will be displayed in the lower layer, select "Symbol level" from "Advanced settings", check the checkbox of Enable symbol level, and set the layer of the high density group to 1. To do.

As a result, the points where PFOS + PFOA exceeds 50 ng / L and the points where it does not exceed are displayed in different colors. If you want to output this figure, select "Import / Export" from "Project", select "Export Map to Image" or "Export Map to PDF", and copy it to the clipboard or file it in any directory. Save as.

How about? Did you do it? In this way, we would appreciate it if you could utilize the data recorded in ChemTHEATER by expressing it on a map. Return to "Learning Python with ChemTHEATER"