Convert Pandas Series to Dataframe or run query on series data?

RexRobichaux · ‎03-30-2020

Hello Python Gurus-

I've been doing some experimental design work for a recurring script which aims to pull in hourly json values for air quality monitoring stations from PurpleAir.com, parse the values in a field (PM2_5Value), and then if values exceed a certain threshold (let's say 40 for testing), send smtp email notifications to staff for awareness.

I've been using Jupyter Notebooks and have been able to pull in the json values for all stations, parse the json into a series to show the json results (fields of interest) but have hit a mental block. It seems like while it's in series format, I can't perform sorting / querying of records. I'm unsure if this is by design, bug, or error on my part but DataFrame.sort_values fails, as does just df ['PM2_5Value'] > 40.

I'm, guessing there is something obvious here I'm missing- is there an easier way to approach this, or turn this series table into a proper dataframe so that I can work with the data easier? If I attempt to search for values in the current series (which is probably my problem), I receive the following KeyError:

KeyError                                  Traceback (most recent call last)C:\Program Files\ArcGIS\Pro\bin\Python\envs\arcgispro-py3\lib\site-packages\pandas\core\indexes\base.py in get_loc(self, key, method, tolerance)   2656             try:-> 2657                 return self._engine.get_loc(key)   2658             except KeyError:pandas/_libs/index.pyx in pandas._libs.index.IndexEngine.get_loc()pandas/_libs/index.pyx in pandas._libs.index.IndexEngine.get_loc()pandas/_libs/hashtable_class_helper.pxi in pandas._libs.hashtable.PyObjectHashTable.get_item()pandas/_libs/hashtable_class_helper.pxi in pandas._libs.hashtable.PyObjectHashTable.get_item()KeyError: 'PM2_5Value'

Here's the code so far and the series it produces in Jupyter:

import numpy as np
import pandas as pd
from requests import request
import json
from pandas.io.json import json_normalize 


df = pd.read_json('https://www.purpleair.com/json?show=39183|31221|31179|31185|2827|12785|35779|15947|46911|3095|35885|10286|33089|38627|34099|27405|21109|27825|34579|34109|27401|21111|27873|34747|26961|38253|34153|27807|27833|27863|22355|9930|2221|8244|8248|4427|2944|2514|15019|28651|47173|25361|36607|37575|4591|6612|36139|30739|5512|3157|34933|38835|48625|34951|2239|12016|34803|14687|26249|35693|36965|34847|34797|44553|39593|46293|48047|30169')
df.results.apply(pd.Series)

ID	Label	DEVICE_LOCATIONTYPE	THINGSPEAK_PRIMARY_ID	THINGSPEAK_PRIMARY_ID_READ_KEY	THINGSPEAK_SECONDARY_ID	THINGSPEAK_SECONDARY_ID_READ_KEY	Lat	Lon	PM2_5Value	...	pm2_5_atm	pm10_0_atm	isOwner	humidity	temp_f	pressure	AGE	Stats	ParentID	Flag
0	31185	1307 Highland Cir	outside	771600	ARNTKJE62A5YN65X	771601	2ADRDG27LBM5DMY4	37.221680	-80.394267	0.86	...	0.86	0.86	0	16	74	936.04	0	{"v":0.86,"v1":0.89,"v2":0.98,"v3":1.15,"v4":3...	NaN	NaN
1	31186	1307 Highland Cir B	NaN	771602	UDXTMLB1DM0RNWHI	771603	1B895L3NPZ431PZS	37.221680	-80.394267	0.93	...	0.93	1.2	0	NaN	NaN	NaN	0	{"v":0.93,"v1":0.78,"v2":0.83,"v3":0.99,"v4":3...	31185.0	NaN
2	46293	1625 N Quincy St	outside	962830	NID89HFS1SIXVFUP	962832	G05WHJ0G5OG6VX2F	38.891215	-77.107814	2.14	...	2.14	2.53	0	21	78	1000.33	2	{"v":2.14,"v1":2.32,"v2":3.3,"v3":5.63,"v4":15...	NaN	0.0
3	46294	1625 N Quincy St B	NaN	962833	VYPA6FS3H1542DNX	962835	5HNX1XDCTA5HCCE0	38.891215	-77.107814	2.05	...	2.05	2.19	0	NaN	NaN	NaN	2	{"v":2.05,"v1":2.27,"v2":3.23,"v3":5.52,"v4":1...	46293.0	NaN
4	48047	Arlington Heights	outside	983128	NSU8T3TTQ9XILKLS	983129	5NCXVTW2WJ6GH3X1	38.869764	-77.091506	1.52	...	1.52	1.52	0	19	83	1002.44	0	{"v":1.52,"v1":2.31,"v2":3.08,"v3":5.16,"v4":1...	NaN	NaN
5	48048	Arlington Heights B	NaN	983130	IJF0DUGUK7WV7I5J	983131	YP7IVMLJ4WR490DM	38.869764	-77.091506	0.96	...	0.96	0.96	0	NaN	NaN	NaN	0	{"v":0.96,"v1":1.68,"v2":2.51,"v3":4.57,"v4":1...	48047.0	NaN
6	2944	Berkeley Springs, WV	outside	318200	AZPYYS7KAQZ39ET6	318201	2XO6L3559B3XEP2E	39.648592	-78.199345	0.41	...	0.41	0.41	0	23	71	985.12	1	{"v":0.41,"v1":0.39,"v2":0.44,"v3":0.51,"v4":3...	NaN	NaN
7	2945	Berkeley Springs, WV B	NaN	318202	TZSB40PD8JP31PVZ	318203	24TBUFMMRG1ABCJM	39.648592	-78.199345	0.3	...	0.3	0.3	0	NaN	NaN	NaN	1	{"v":0.3,"v1":0.73,"v2":1.08,"v3":1.33,"v4":4....	2944.0	1.0
8	36607	Black Cat Manor	outside	839539	PJ4PHDJPSQNFSI8W	839540	PYXGS0EJN4NDPXKF	39.272077	-77.680283	0.36	...	0.36	0.36	0	19	76	992.3	0	{"v":0.36,"v1":0.58,"v2":1.03,"v3":2.88,"v4":1...	NaN	NaN
9	36608	Black Cat Manor B	NaN	839541	NKVRKKFCBKJMLUBI	839542	CLHQPSCLYECRQL72	39.272077	-77.680283	0.0	...	0.0	0.0	0	NaN	NaN	NaN	0	{"v":0.0,"v1":0.01,"v2":0.08,"v3":0.45,"v4":2....	36607.0	1.0
10	35885	BlackmoreWX Hampton, VA	outside	834095	BEZHF80Z7CYE6S6W	834096	F0S87B4DA9HYLXEI	37.088940	-76.422320	1.5	...	1.5	1.74	0	17	86	1012.25	1	{"v":1.5,"v1":1.61,"v2":5.04,"v3":8.86,"v4":15...	NaN	NaN
11	35886	BlackmoreWX Hampton, VA B	NaN	834097	6I8U5L7FGLPH6R4U	834098	17SGOW0FGT5FQWXL	37.088940	-76.422320	1.19	...	1.19	1.19	0	NaN	NaN	NaN	1	{"v":1.19,"v1":1.68,"v2":4.81,"v3":8.29,"v4":1...	35885.0	NaN
12	34933	Centreville	outside	825872	6D1621QIHLPGD5TX	825873	LP2LI15ULRG7AFF1	38.811130	-77.436316	0.66	...	0.66	0.66	0	16	78	1001.53	0	{"v":0.66,"v1":0.8,"v2":1.15,"v3":2.96,"v4":13...	NaN	1.0
13	34934	Centreville B	NaN	825874	C0SJ6M218BYMFG0A	825875	5R5V7H6WRS9LL2TM	38.811130	-77.436316	0.79	...	0.79	1.24	0	NaN	NaN	NaN	0	{"v":0.79,"v1":0.78,"v2":1.21,"v3":3.06,"v4":1...	34933.0	NaN
14	38835	Chantilly High School Science Project	outside	860940	L764W9N28SGZMOTK	860941	9ZF41YZNGSD44T8W	38.876441	-77.398977	0.72	...	0.72	0.78	0	16	81	996.32	1	{"v":0.72,"v1":1.4,"v2":2.01,"v3":4.42,"v4":13...	NaN	NaN
15	38836	Chantilly High School Science Project B	NaN	860942	X1W5VR6ORLNG99NW	860943	FQOT1A66TS1MIJ7S	38.876441	-77.398977	0.72	...	0.72	1.09	0	NaN	NaN	NaN	1	{"v":0.72,"v1":1.16,"v2":1.71,"v3":4.13,"v4":1...	38835.0	NaN
16	12785	Clean Air Carolina Halifax County	outside	536307	J5GBI48AB0WYWUOY	536309	9TZNZWDDCBBJZZTG	36.475129	-77.660187	0.56	...	0.56	0.56	0	13	90	1008.39	1	{"v":0.56,"v1":0.62,"v2":0.58,"v3":0.57,"v4":5...	NaN	NaN
17	12786	Clean Air Carolina Halifax County B	NaN	536311	QKJWMK24HDW1S5M4	536312	0882XIES75X9KTXU	36.475129	-77.660187	0.0	...	0.0	0.0	0	NaN	NaN	NaN	1	{"v":0.0,"v1":0.33,"v2":1.96,"v3":2.42,"v4":3....	12785.0	1.0
18	30169	Courthouse	outside	751725	6VZULVDOOG4WWFLN	751727	ICHVDCPGD8IKKSEA	38.888120	-77.088094	2.17	...	2.17	2.64	0	NaN	NaN	NaN	1	{"v":2.17,"v1":2.14,"v2":2.96,"v3":5.03,"v4":1...	NaN	NaN
19	30170	Courthouse B	NaN	751728	U4R9FWN5JRYDMGKY	751730	D0CY0IMJBA6Z4HQC	38.888120	-77.088094	1.76	...	1.76	1.76	0	NaN	NaN	NaN	1	{"v":1.76,"v1":2.04,"v2":2.78,"v3":4.85,"v4":1...	30169.0	NaN
20	2827	Crump Farm - Wylliesburg, VA	outside	317192	TCFK6ZTOJGLX7OU8	317193	D79WE9O2TLXMJETY	36.844269	-78.555124	1.26	...	1.26	1.26	0	25	76	994.8	1	{"v":1.26,"v1":1.19,"v2":1.16,"v3":1.1,"v4":6....	NaN	1.0
21	2828	Crump Farm - Wylliesburg, VA B	NaN	317194	OT22QIL10O5NZQTN	317195	QK7B3TFH37PZBY8I	36.844269	-78.555124	2.36	...	2.36	2.55	0	NaN	NaN	NaN	1	{"v":2.36,"v1":1.95,"v2":1.9,"v3":1.81,"v4":8....	2827.0	NaN
22	5512	Digital Bear Consulting	outside	391865	X3OHYCYU7RLGNG5P	391866	LZZ3G8LT0ELZC5D4	38.784090	-77.494766	2.48	...	2.48	2.65	0	17	84	1002.26	1	{"v":2.48,"v1":1.08,"v2":1.33,"v3":3.16,"v4":1...	NaN	NaN
23	5513	Digital Bear Consulting B	NaN	391867	KAHNK34BLY0X825H	391868	U0FEUI4PQBQ8GWQE	38.784090	-77.494766	2.57	...	2.57	2.71	0	NaN	NaN	NaN	1	{"v":2.57,"v1":1.15,"v2":1.43,"v3":3.36,"v4":1...	5512.0	NaN
24	15019	Downtown Martinsburg	outside	570005	FSFIUN3UA90PYQD8	570006	N5RTIIXR3I8IN7S1	39.456469	-77.965935	0.58	...	0.58	0.58	0	21	75	993.79	0	{"v":0.58,"v1":0.75,"v2":0.79,"v3":1.03,"v4":9...	NaN	NaN
25	15020	Downtown Martinsburg B	NaN	570007	PM7BPJBG7QNP04Q7	570008	5GMSH2PDSIZLP910	39.456469	-77.965935	0.38	...	0.38	0.38	0	NaN	NaN	NaN	0	{"v":0.38,"v1":0.58,"v2":0.62,"v3":0.9,"v4":10...	15019.0	NaN
26	30739	Eastgate	outside	762634	8T35M0N3C4101ATM	762635	AD4BF1TNA6E8444N	38.910599	-77.494886	1.22	...	1.22	1.78	0	20	82	999.09	0	{"v":1.22,"v1":1.75,"v2":5.71,"v3":9.84,"v4":1...	NaN	NaN
27	30740	Eastgate B	NaN	762636	I0QLAJVCUMZV6M6Y	762637	B0TBB8WR2SVYCB33	38.910599	-77.494886	0.7	...	0.7	0.98	0	NaN	NaN	NaN	0	{"v":0.7,"v1":1.06,"v2":5.09,"v3":9.23,"v4":12...	30739.0	NaN
28	8248	ENST 1: Methodist Church	outside	443667	VMBIM75NF3BURRHR	443669	87H11G75H0BG41U7	38.264689	-76.454042	3.14	...	3.14	3.5	0	13	95	1005.82	0	{"v":3.14,"v1":3.58,"v2":4.84,"v3":6.33,"v4":1...	NaN	NaN
29	8249	ENST 1: Methodist Church B	NaN	443671	91JRVN3T4KC2ZJJT	443672	CLQTHF01XE9PFRMN	38.264689	-76.454042	2.41	...	2.41	2.83	0	NaN	NaN	NaN	0	{"v":2.41,"v1":3.03,"v2":4.32,"v3":5.72,"v4":9...	8248.0	NaN
...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...
106	27873	UR_02_Vasen_dec	outside	719388	DVEBUI9MZQ15W7P3	719389	HV035CPT31F5CN22	37.570268	-77.473054	1.84	...	1.84	1.84	0	15	81	1004.36	1	{"v":1.84,"v1":3.23,"v2":3.84,"v3":5.23,"v4":9...	NaN	1.0
107	27874	UR_02_Vasen_dec B	NaN	719390	WZJIXJE3H70SKVAR	719391	WG7RRDC1FZNPTI1U	37.570268	-77.473054	1.09	...	1.09	1.16	0	NaN	NaN	NaN	1	{"v":1.09,"v1":2.44,"v2":3.05,"v3":4.29,"v4":8...	27873.0	1.0
108	27825	UR_03_SixPts_e34	outside	717219	179C16Z3X62CA5FJ	717220	LBTPX5P9VGG9V8B4	37.570367	-77.418162	3.81	...	3.81	3.93	0	14	84	1005.12	1	{"v":3.81,"v1":2.14,"v2":2.48,"v3":4.39,"v4":1...	NaN	NaN
109	27826	UR_03_SixPts_e34 B	NaN	717221	E7YMOT4T9DI0CY1Y	717222	MCL5U5ABZ4B0EURO	37.570367	-77.418162	2.41	...	2.41	2.41	0	NaN	NaN	NaN	1	{"v":2.41,"v1":1.88,"v2":2.25,"v3":4.04,"v4":9...	27825.0	NaN
110	27833	UR_04_Campus_dc8	outside	717235	XQ4FPWQCST3IQYTJ	717236	65S6WZBP0V96NNIU	37.578117	-77.537013	1.46	...	1.46	1.59	0	13	86	1005.09	1	{"v":1.46,"v1":1.23,"v2":1.3,"v3":2.03,"v4":6....	NaN	NaN
111	27834	UR_04_Campus_dc8 B	NaN	717237	NL4US20A32Q8AH4S	717238	UL185JBQFXN1N8BS	37.578117	-77.537013	0.48	...	0.48	0.61	0	NaN	NaN	NaN	1	{"v":0.48,"v1":0.47,"v2":0.48,"v3":0.76,"v4":3...	27833.0	NaN
112	27807	UR_05_Campus_de3	outside	717183	NEQOLJIV2KHIWKAZ	717184	1FO753A6WOJ0EJMU	37.575426	-77.539231	1.48	...	1.48	1.6	0	13	84	1006.05	1	{"v":1.48,"v1":1.11,"v2":1.19,"v3":1.9,"v4":6....	NaN	NaN
113	27808	UR_05_Campus_de3 B	NaN	717185	2DOTEYLF7PIW0F7I	717186	YMWZ0K8AAW93CJOA	37.575426	-77.539231	1.76	...	1.76	1.8	0	NaN	NaN	NaN	1	{"v":1.76,"v1":1.46,"v2":1.47,"v3":2.18,"v4":7...	27807.0	NaN
114	27401	UR_06_SMV_d8bc	outside	710114	JHHI709D03NCALCE	710115	C1YG1WCNAX1ZVPDH	37.562716	-77.465423	2.26	...	2.26	2.39	0	13	86	1004.82	0	{"v":2.26,"v1":1.83,"v2":2.03,"v3":3.52,"v4":9...	NaN	NaN
115	27402	UR_06_SMV_d8bc B	NaN	710116	KDCNZCB5GZ53SB2U	710117	S73F578WL9JEI2K5	37.562716	-77.465423	1.87	...	1.87	2.6	0	NaN	NaN	NaN	0	{"v":1.87,"v1":1.75,"v2":2.03,"v3":3.61,"v4":9...	27401.0	NaN
116	27405	UR_07_NRCFulton_d6ff	outside	710122	YLWXIL3N8L4AMRHT	710123	KZF1OOED3HISK0RX	37.514527	-77.395296	1.45	...	1.45	1.77	0	17	82	1006.8	1	{"v":1.45,"v1":2.16,"v2":2.47,"v3":4.17,"v4":1...	NaN	NaN
117	27406	UR_07_NRCFulton_d6ff B	NaN	710124	JNRO82EG4U1AV94R	710125	5B9SN4ZFGI6NJYTY	37.514527	-77.395296	1.88	...	1.88	1.88	0	NaN	NaN	NaN	1	{"v":1.88,"v1":1.63,"v2":1.99,"v3":3.58,"v4":9...	27405.0	NaN
118	26961	UR_09_VUUnorth_d31c	outside	707419	ELXSA3LZDYYTKKJ0	707420	9X7RQWX70GDG5ZC4	37.573436	-77.449911	1.65	...	1.65	2.18	0	16	84	1005.22	1	{"v":1.65,"v1":1.5,"v2":1.75,"v3":3.37,"v4":9....	NaN	NaN
119	26962	UR_09_VUUnorth_d31c B	NaN	707421	QF4ZPUMCK8K6QS0F	707422	DNPALA7LSPK4J28D	37.573436	-77.449911	1.79	...	1.79	1.86	0	NaN	NaN	NaN	1	{"v":1.79,"v1":1.69,"v2":2.01,"v3":3.74,"v4":9...	26961.0	1.0
120	33089	UUFP Sancuary	outside	799339	CUQM5NZXZNF821K6	799340	FSWHOPTYMTER49SW	37.099396	-76.502742	1.28	...	1.28	1.64	0	19	83	1011.67	0	{"v":1.28,"v1":1.83,"v2":5.45,"v3":9.39,"v4":1...	NaN	NaN
121	33090	UUFP Sancuary B	NaN	799341	9ZNRPG8A448V3BF2	799342	1UN1NIG85O10U4UC	37.099396	-76.502742	0.83	...	0.83	1.03	0	NaN	NaN	NaN	0	{"v":0.83,"v1":1.57,"v2":4.67,"v3":8.11,"v4":1...	33089.0	NaN
122	21109	VCU2-MSiC	outside	646707	JHOUMNPBBW8P3BN2	646708	NPX9M6Y29WBXGABC	37.556600	-77.400512	1.31	...	1.31	1.31	0	18	83	1004.65	0	{"v":1.31,"v1":1.09,"v2":1.52,"v3":3.34,"v4":8...	NaN	NaN
123	21110	VCU2-MSiC B	NaN	646709	6KBHZB33806PB32E	646710	NC1C0TAJOAKT4P7D	37.556600	-77.400512	1.42	...	1.42	1.58	0	NaN	NaN	NaN	0	{"v":1.42,"v1":1.22,"v2":1.77,"v3":3.81,"v4":9...	21109.0	NaN
124	21111	vcu6	outside	646711	XKF3VLRU7W3OU685	646712	LZ3XGBJOC03PZ8PL	37.541368	-77.512390	1.82	...	1.82	1.95	0	19	80	1005.88	1	{"v":1.82,"v1":1.48,"v2":1.56,"v3":2.23,"v4":6...	NaN	NaN
125	21112	vcu6 B	NaN	646713	J1MUHRGMCOTSES21	646714	01ENTKOPNSARPXHS	37.541368	-77.512390	2.0	...	2.0	2.17	0	NaN	NaN	NaN	1	{"v":2.0,"v1":1.51,"v2":1.56,"v3":2.18,"v4":6....	21111.0	NaN
126	2239	viennaxmas	outside	304517	5842OI0FVJ4MYV8M	304518	3EYD5JZCS53WHGVP	38.900192	-77.265591	1.12	...	1.12	1.41	0	16	80	996.23	0	{"v":1.12,"v1":1.16,"v2":2.02,"v3":4.32,"v4":1...	NaN	1.0
127	2240	viennaxmas B	NaN	304519	GUOAE89ILQAXXGFN	304520	62DXT8RYGLG5Y1AA	38.900192	-77.265591	1.1	...	1.1	1.1	0	NaN	NaN	NaN	0	{"v":1.1,"v1":1.02,"v2":1.73,"v3":4.21,"v4":15...	2239.0	1.0
128	31179	Virginia Tech	outside	771588	RWSK6YODJ7BUJFKU	771589	GP336K961UO52FFK	37.231350	-80.423963	0.68	...	0.68	1.32	0	12	72	941.5	0	{"v":0.68,"v1":0.57,"v2":0.68,"v3":0.88,"v4":3...	NaN	NaN
129	31180	Virginia Tech B	NaN	771590	1326KOMM4Y8V72G6	771591	LG9ZV9539Q9LWO85	37.231350	-80.423963	0.74	...	0.74	0.88	0	NaN	NaN	NaN	0	{"v":0.74,"v1":0.64,"v2":0.76,"v3":0.98,"v4":3...	31179.0	NaN
130	31221	Walls Branch	outside	771672	W07JDR5KIYYAP7GG	771673	38WX8RNVHXDROE06	37.203450	-80.484575	1.12	...	1.12	1.41	0	13	86	946.08	0	{"v":1.12,"v1":1.07,"v2":1.92,"v3":2.22,"v4":4...	NaN	NaN
131	31222	Walls Branch B	NaN	771674	05MMNSBEZMEJGYYW	771675	I321B9XZBZY903QJ	37.203450	-80.484575	0.46	...	0.46	0.77	0	NaN	NaN	NaN	0	{"v":0.46,"v1":0.94,"v2":1.77,"v3":2.03,"v4":4...	31221.0	NaN
132	3157	weatherCENTRE	outside	324124	4RYTYM7ROTFH8JBW	324125	OUR97HQ5HD3OFLZH	38.844998	-77.455174	0.83	...	0.83	0.83	0	19	79	1000.11	1	{"v":0.83,"v1":1.17,"v2":4.65,"v3":8.82,"v4":1...	NaN	NaN
133	3158	weatherCENTRE B	NaN	324126	CO9R5AUJYFRQZQZF	324127	OYZIS449TAT5WFWC	38.844998	-77.455174	1.23	...	1.23	1.45	0	NaN	NaN	NaN	1	{"v":1.23,"v1":1.18,"v2":4.86,"v3":8.87,"v4":1...	3157.0	NaN
134	4427	West Campus	outside	366435	SXRZMFJ1AL2IYU8K	366436	UERPDAM9SQQU45LO	38.973145	-78.247077	0.26	...	0.26	0.0	0	17	78	988.59	0	{"v":0.26,"v1":0.41,"v2":0.49,"v3":0.6,"v4":3....	NaN	NaN
135	4428	West Campus B	NaN	366437	OYVZD2ZAB7G0Q9HS	366438	74AG80VHYFPFYWGX	38.973145	-78.247077	0.0	...	0.0	0.0	0	NaN	NaN	NaN	0	{"v":0.0,"v1":0.0,"v2":0.01,"v3":0.02,"v4":0.0...	4427.0	NaN

136 rows × 41 columns

In [ ]:

GeoJosh · ‎03-30-2020

Hey Rex,

I'd first use the requests module to get the json response, then load it as a dictionary. From there, you can use panda's from_dict function to create the DataFrame from the series. Something like this:

import numpy as np
import pandas as pd
import requests
import json

r = requests.get('https://www.purpleair.com/json?show=39183|31221|31179|31185|2827|12785|35779|15947|46911|3095|35885|10286|33089|38627|34099|27405|21109|27825|34579|34109|27401|21111|27873|34747|26961|38253|34153|27807|27833|27863|22355|9930|2221|8244|8248|4427|2944|2514|15019|28651|47173|25361|36607|37575|4591|6612|36139|30739|5512|3157|34933|38835|48625|34951|2239|12016|34803|14687|26249|35693|36965|34847|34797|44553|39593|46293|48047|30169')

dictionary = json.loads(r.text)
results = dictionary["results"]

df = pd.read_json(df.results.to_json(), orient='index')

Now the data is a DataFrame, as opposed to a Series. Hope this helps!

Best,

Josh

Edit: Modified the last line of code to reflect @joshua Bixby's suggestion. Thanks a lot!

View solution in original post

GeoJosh · ‎03-30-2020

Hey Rex,

I'd first use the requests module to get the json response, then load it as a dictionary. From there, you can use panda's from_dict function to create the DataFrame from the series. Something like this:

import numpy as np
import pandas as pd
import requests
import json

r = requests.get('https://www.purpleair.com/json?show=39183|31221|31179|31185|2827|12785|35779|15947|46911|3095|35885|10286|33089|38627|34099|27405|21109|27825|34579|34109|27401|21111|27873|34747|26961|38253|34153|27807|27833|27863|22355|9930|2221|8244|8248|4427|2944|2514|15019|28651|47173|25361|36607|37575|4591|6612|36139|30739|5512|3157|34933|38835|48625|34951|2239|12016|34803|14687|26249|35693|36965|34847|34797|44553|39593|46293|48047|30169')

dictionary = json.loads(r.text)
results = dictionary["results"]

df = pd.read_json(df.results.to_json(), orient='index')

Now the data is a DataFrame, as opposed to a Series. Hope this helps!

Best,

Josh

Edit: Modified the last line of code to reflect @joshua Bixby's suggestion. Thanks a lot!

JoshuaBixby · ‎03-31-2020

You can replace your last line with:

df = pd.read_json(df.results.to_json(), orient='index')

RexRobichaux · ‎04-01-2020

Hello and a huge thank you to you both @Joshua Herman and @Joshua Bixby. From quick testing it looks like this is exactly what I needed. I've been swamped this week but will follow-up as soon as I can dig into this script a little more. Thanks again for the help!

RexRobichaux · ‎04-02-2020

Thanks again for the assistance with this script all! I have it to a point to where it's working for some bare-bones functionality. At this point, it's is searching for any values exceeding a threshold, and if any records exist above that set threshold, it will fire off an email with the count of records in a pre-scripted message to recipients to notify them.

I was wondering if it's difficult to embed the dataframe results (only the offending stations if they exists) into the email body or as an html attachment? I found a few methods to convert either a dataframe to html (https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.to_html.html )

but am having issues getting it to embed properly into the body of the email as described here. I normally just end up with {None} getting embedded which indicates to me that it's not properly pulling in my offending values from the dataframe. The current, clean / working version of the code is below which should trigger if any records return values of 2.5 ATM values over 15 (this is easy to change in the script for testing). Any thoughts of an easy way to get additional data (Station Name / ID, etc.) added to the email notification?

Thanks again everyone. The help is much appreciated!

import numpy as np
import pandas as pd
import requests
import json
import smtplib


# In case of AQM Alert notification, this script will email the identified DEQ recipients. These configuration
# variables can be altered as needed.

scriptname = "AirJSONCalls.py"
primary_email = "Rex xxxxxxx <Rex.xxxxxxx@xxx.virginia.gov>"
backup_email = "xxxxxxx<xxxxxx@xxxx.virginia.gov>"
from_email = "xxx-xxx-xxxx <xxx-xxx-xxxx@xxx.virginia.gov>"
SMTP_server = "xxx.virginia.gov"
#email = (df2)
def pyMailer (alert):
    SERVER = SMTP_server
    FROM = from_email
    TO = [primary_email]
    SUBJECT = "AIR Quality Alert Notification"
    MSG = (f"This is an automated message. \n\rThere are Virginia AQM sites reporting concerning levels of AQI data. There are {alert} stations currently exceeding established thresholds. Please invesitage further here: https://www.purpleair.com/map?opt=1/i/mAQI/a10/cC0&select=38835#6.09/38.171/-79.871")
    # Building the message
    print ("Building the message...")
    MESSAGE = """\
    From: %s
    To: %s
    Subject: %s

    %s
    """ % (FROM, TO, SUBJECT, MSG)

    # Send the email
    print ("Sending the email...")
    server = smtplib.SMTP(SERVER)
    server.sendmail(FROM, TO, MESSAGE)
    server.quit()

#get VA Station Data and organize json into python dictionary to function as pandas dataframe:

r = requests.get('http://www.purpleair.com/json?show=39183|31221|31179|31185|2827|12785|35779|15947|46911|3095|35885|10286|33089|38627|34099|27405|21109|27825|34579|34109|27401|21111|27873|34747|26961|38253|34153|27807|27833|27863|22355|9930|2221|8244|8248|4427|2944|2514|15019|28651|47173|25361|36607|37575|4591|6612|36139|30739|5512|3157|34933|38835|48625|34951|2239|12016|34803|14687|26249|35693|36965|34847|34797|44553|39593|46293|48047|30169')
dictionary = json.loads(r.text)
results = dictionary["results"]

#Set options for dataframe:

pd.options.display.max_columns = None
pd.set_option('display.max_rows', None)

#View results and convert appropriate field (pm2_5_atm) from string to float for quantitative analysis:

df = pd.DataFrame(results)
df.pm2_5_atm = df.pm2_5_atm.astype(float)

#Return / retain only values that exceed threshold (set at 26 to represent close to 100 AQI- need to confirm this number)
df2 = df[df.pm2_5_atm > 15]
#df.sort_values('pm2_5_atm', ascending=False)

df2
total_rows=len(df2.axes[0])
print("Number of Rows: "+str(total_rows))


total_rows=len(df2.axes[0])
print("Number of Rows: "+str(total_rows))
rowcount=(total_rows)
#print (rowcount)
alert = rowcount

#email = email.format(df2=df.to_html())


if rowcount > 1:
    pyMailer (alert) 
else:
    print ("Concluded AQM script, no exceedingly high AQI records were found.")‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

RexRobichaux · ‎04-03-2020

Sometimes... a little sleep and fresh eyes is all it takes to have an obvious "ah-ha" moment. Turns out dataframe content can be inserted into an email text by simply passing in the variable for the dataframe you want to include. Duh... and it doesn't look great- but not terrible either. Final script is:

import numpy as np
import pandas as pd
import requests
import json
import smtplib


# In case of AQM Alert notification, this script will email the identified DEQ recipients. These configuration
# variables can be altered as needed.

scriptname = "AirJSONCalls.py"
primary_email = "Rex xxxxx <xxxxxx@xxxx.virginia.gov>"
backup_email = "xxxx xxxxx <xxxxxx@xxxx.virginia.gov>"
from_email = "xxx-xxx-xxx <xxx-xxx-xxx@xxxx.virginia.gov>"
SMTP_server = "xxxx.xxxx.virginia.gov"

def pyMailer (alert):
    SERVER = SMTP_server
    FROM = from_email
    TO = [primary_email, backup_email]
    SUBJECT = "AIR Quality Alert Notification"
    MSG = (f"This is an automated message. \n\rThere are Virginia AQM sites reporting concerning levels of AQI data. There are {alert} stations currently exceeding established thresholds. Please invesitage further here: https://www.purpleair.com/map?opt=1/i/mAQI/a10/cC0&select=38835#6.09/38.171/-79.871  \n\rStation Details can be found below:\n\r{df2}")
    # Building the message
    print ("Building the message...")
    MESSAGE = """\
    From: %s
    To: %s
    Subject: %s

    %s
    """ % (FROM, TO, SUBJECT, MSG)

    # Send the email
    print ("Sending the email...")
    server = smtplib.SMTP(SERVER)
    server.sendmail(FROM, TO, MESSAGE)
    server.quit()

#get VA Station Data and organize json into python dictionary to function as pandas dataframe:

r = requests.get('http://www.purpleair.com/json?show=39183|31221|31179|31185|2827|12785|35779|15947|46911|3095|35885|10286|33089|38627|34099|27405|21109|27825|34579|34109|27401|21111|27873|34747|26961|38253|34153|27807|27833|27863|22355|9930|2221|8244|8248|4427|2944|2514|15019|28651|47173|25361|36607|37575|4591|6612|36139|30739|5512|3157|34933|38835|48625|34951|2239|12016|34803|14687|26249|35693|36965|34847|34797|44553|39593|46293|48047|30169')
dictionary = json.loads(r.text)
results = dictionary["results"]

#Set options for dataframe:

pd.options.display.max_columns = None
pd.set_option('display.max_rows', None)

#View results and convert appropriate field (pm2_5_atm) from string to float for quantitative analysis:

df = pd.DataFrame(results)
df.pm2_5_atm = df.pm2_5_atm.astype(float)

#Return / retain only values that exceed threshold (set at 26 to represent close to 100 AQI- need to confirm this number)
df2 = df[df.pm2_5_atm > 10]
#df.sort_values('pm2_5_atm', ascending=False)

df2
total_rows=len(df2.axes[0])
print("Number of Rows: "+str(total_rows))


total_rows=len(df2.axes[0])
print("Number of Rows: "+str(total_rows))
rowcount=(total_rows)
#print (rowcount)
alert = rowcount

#Check to see if any offending values are found for stations and if they are, send notification email. If not, script concludes.
if rowcount > 0:
    pyMailer (alert) 
else:
    print ("Concluded AQM script, no exceedingly high AQI records were found.")‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍