In [205]:
#%reload_ext autoreload
#%autoreload 2
from algae_app.models import *
from django.db.models import Q, Sum, Count
import pandas as pd
from django.utils import timezone
from django.core.files import File
import cv2
import uuid
import numpy as np
import os
In [206]:
fname=os.path.join('media','data','AG-RW-CM51-5805.xlsx')
df=pd.read_excel(io=fname,sheetname=1)
df.drop(inplace=True, axis=1, labels=df.columns[10]) #time
df.drop(inplace=True, axis=1, labels=df.columns[:3]) #place Group original_date
for i in [0,1,2,4,5,6]: #first 6 columns
col=df.columns[i]
df[df.columns[i]].replace('-', np.nan, inplace=True)
df.dropna(inplace=True, axis=0, subset=[col])
df.head()
Out[206]:
date
time
Turbidity
(NTU.)
pH
Conductivity
(umho/cm)
DO
(mgl)
OC
(mg/l)
Cyclotella sp.
Aulocoseria sp.
Melosira granulata
(Aulocoseria granulata)
...
Synedra ulna
Anabaena sp.
Lyngbya sp.
Oscillatoria sp.
Chlorella sp.
Closterium sp.
Filter Algae
Taste&Odor Algae
Polution Algae
Total Algae
93
2012-01-18
10.35
31.2
8.35
238
3.35
3.32
0
720
0
...
80
80
0
1040
0
480
2400
160
5440
6960
94
2012-02-02
9.33
25.6
7.87
207
4.60
2.45
880
1240
0
...
240
0
0
560
880
240
3800
240
2560
5800
95
2012-02-08
9.48
24.4
7.9
230
4.24
2.61
2560
1680
0
...
240
0
0
1520
800
160
7440
240
4720
11440
96
2012-03-05
9.25
22.9
7.76
203
5.46
2.37
1680
1200
0
...
0
0
0
1360
400
160
6880
160
3440
10880
97
2012-03-15
10.01
19.3
7.72
206
5.08
1.82
720
800
0
...
240
0
0
2560
1840
240
6560
240
6240
9040
5 rows × 24 columns
In [207]:
for i in df[1:].iterrows():
df.set_value(index=i[0],col="date",value=i[1]["date"].replace(hour=int(i[1]["time"])))
In [208]:
df.shape
Out[208]:
(164, 24)
In [209]:
df.drop(axis=1,labels="time",inplace=True)
df.replace('-', 0,inplace=True)
df.replace(r'\s+', np.nan,inplace=True,regex=True)
df.fillna(inplace=True, method="ffill")
df
Out[209]:
date
Turbidity
(NTU.)
pH
Conductivity
(umho/cm)
DO
(mgl)
OC
(mg/l)
Cyclotella sp.
Aulocoseria sp.
Melosira granulata
(Aulocoseria granulata)
Nitzschia sp.
...
Synedra ulna
Anabaena sp.
Lyngbya sp.
Oscillatoria sp.
Chlorella sp.
Closterium sp.
Filter Algae
Taste&Odor Algae
Polution Algae
Total Algae
93
2012-01-18 00:00:00
31.20
8.35
238.0
3.35
3.32
0
720
0
480
...
80
80
0
1040
0
480
2400
160
5440
6960
94
2012-02-02 09:00:00
25.60
7.87
207.0
4.60
2.45
880
1240
0
240
...
240
0
0
560
880
240
3800
240
2560
5800
95
2012-02-08 09:00:00
24.40
7.90
230.0
4.24
2.61
2560
1680
0
80
...
240
0
0
1520
800
160
7440
240
4720
11440
96
2012-03-05 09:00:00
22.90
7.76
203.0
5.46
2.37
1680
1200
0
160
...
0
0
0
1360
400
160
6880
160
3440
10880
97
2012-03-15 10:00:00
19.30
7.72
206.0
5.08
1.82
720
800
0
320
...
240
0
0
2560
1840
240
6560
240
6240
9040
98
2012-03-23 11:00:00
16.60
7.68
222.0
5.06
2.21
480
1120
0
160
...
0
0
0
2960
2000
160
7360
0
8400
11520
99
2012-03-29 10:00:00
18.20
7.53
232.0
4.10
2.37
160
80
0
800
...
0
0
0
1560
720
0
2760
0
3800
4520
100
2012-04-03 10:00:00
22.20
7.54
255.0
3.35
2.84
0
0
0
1360
...
0
160
0
2240
4720
320
7440
160
8800
9840
101
2012-04-10 09:00:00
20.60
7.58
275.0
3.45
2.84
40
80
0
400
...
0
0
0
1440
480
80
2280
0
2560
2920
102
2012-04-18 11:00:00
19.50
7.58
305.0
3.54
4.00
2600
12200
0
800
...
200
0
0
3200
1000
200
19200
200
5000
21400
103
2012-04-19 12:00:00
21.80
7.76
304.0
3.99
4.24
600
13200
0
200
...
200
0
0
6200
400
0
20400
200
7400
23600
104
2012-04-20 11:00:00
9.70
7.79
319.0
3.78
3.76
800
17200
0
2400
...
0
200
0
7000
400
600
27000
200
10600
31400
105
2012-04-21 12:00:00
20.70
7.76
362.0
3.94
4.16
1800
13000
2200
0
...
200
0
0
6600
2800
0
24200
200
10400
29400
106
2012-04-22 12:00:00
21.60
7.94
312.0
3.70
4.00
1600
13400
0
1800
...
200
200
0
6200
1200
200
22800
400
9400
25400
107
2012-04-23 11:00:00
21.50
7.95
307.0
3.62
4.00
1800
12000
0
2400
...
0
200
0
6000
1000
0
21000
200
9600
24800
108
2012-04-24 12:00:00
26.10
7.54
297.0
3.70
3.51
1400
8800
0
1200
...
0
0
0
4400
1000
0
16200
0
7200
18800
109
2012-04-25 12:00:00
25.50
7.74
288.0
3.94
3.28
200
7000
0
1000
...
0
0
0
5800
1400
800
16200
0
8600
18400
110
2012-04-26 12:00:00
20.20
7.57
281.0
3.61
2.81
2000
5000
0
400
...
0
200
0
5400
1000
0
14800
200
7200
16400
111
2012-04-27 11:00:00
21.20
7.41
279.0
3.62
2.97
1200
5600
0
1600
...
0
0
0
2400
600
400
10400
0
4600
14200
112
2012-04-29 12:00:00
20.60
7.81
275.0
4.12
2.89
4400
5600
0
1400
...
0
0
0
7000
4600
600
23400
0
13400
26000
113
2012-04-30 12:00:00
20.70
7.63
278.0
4.03
3.36
800
7800
0
2200
...
0
0
0
3200
800
400
14200
0
7200
18800
114
2012-05-02 10:00:00
16.90
7.68
282.0
3.91
3.04
3000
5200
0
1600
...
0
0
0
7800
4000
600
20600
0
14000
26600
115
2012-05-02 11:00:00
19.50
7.78
265.0
3.92
3.12
4000
8000
0
1600
...
0
0
0
8200
3600
0
25200
0
14200
29800
116
2012-05-04 11:00:00
19.80
7.49
284.0
3.77
3.12
6800
6000
0
200
...
0
0
0
6400
2800
600
24000
0
9800
27200
117
2012-05-06 11:00:00
18.90
7.78
289.0
3.78
3.36
2400
6800
0
2200
...
0
0
0
2400
2600
200
16000
0
7600
19200
118
2012-05-08 14:00:00
21.30
7.78
289.0
3.92
3.05
1000
6600
0
600
...
600
0
0
3600
3200
800
18000
600
8200
21800
119
2012-05-10 11:00:00
17.70
7.75
318.0
2.74
3.12
7200
15000
0
1000
...
400
0
0
4800
3600
600
32800
400
10200
36600
120
2012-05-13 11:00:00
22.20
7.78
281.0
4.38
2.97
4200
8800
0
1000
...
0
0
0
1400
800
400
15800
0
3800
19400
121
2012-05-16 11:00:00
27.70
7.82
310.0
4.50
4.41
1400
41000
0
2000
...
600
0
0
8400
600
200
52600
600
12200
58400
122
2012-05-18 10:00:00
23.50
7.85
285.0
5.96
3.42
2400
129600
0
800
...
2800
0
0
4800
2000
400
139200
2800
9200
145600
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
245
2014-08-10 10:00:00
33.90
0.00
359.0
3.24
3.84
8200
0
9200
3600
...
0
0
0
11000
800
1000
23400
0
16600
40600
246
2014-08-13 09:00:00
19.20
0.00
348.0
3.65
3.79
12400
8600
0
1600
...
0
0
0
7800
400
200
29600
0
10400
34200
247
2014-09-02 10:00:00
33.60
0.00
291.4
3.46
4.99
2600
2800
0
800
...
0
0
0
8400
600
400
14800
0
10600
17000
248
2014-09-08 10:00:00
71.00
0.00
241.0
4.32
4.05
800
0
2600
0
...
0
0
0
4000
1200
0
6200
0
6400
11400
249
2014-10-01 10:00:00
30.40
0.00
283.0
3.69
4.29
4200
2400
0
400
...
0
0
0
3800
800
0
11600
0
5400
13000
250
2014-10-03 10:00:00
31.00
0.00
275.0
5.11
4.91
10200
2000
0
400
...
0
200
0
9400
1000
600
23400
200
11000
26000
251
2014-10-08 09:00:00
38.60
0.00
242.0
4.25
4.05
200
0
8400
5800
...
0
0
0
0
0
0
23400
200
11000
26000
252
2014-11-05 09:00:00
25.20
0.00
321.0
2.98
4.03
1900
1700
0
100
...
0
0
0
3500
200
0
7300
0
4000
8300
253
2014-11-28 11:00:00
34.80
7.26
305.0
3.90
4.06
880
800
0
0
...
0
0
0
1200
0
160
3040
0
1200
3280
254
2014-12-08 10:00:00
31.80
7.16
339.0
3.39
4.27
2700
1000
0
100
...
100
0
0
2100
500
100
6400
100
2900
7200
255
2014-12-11 10:00:00
29.30
7.21
344.0
3.47
4.18
2800
600
0
400
...
0
0
0
1000
1000
200
5800
200
2400
6600
256
2014-12-15 10:00:00
22.40
7.43
352.0
3.91
4.03
1000
1000
0
0
...
0
0
0
1600
800
0
4600
600
3200
6600
257
2015-01-08 10:00:00
17.50
0.00
393.0
4.51
4.58
4400
600
0
0
...
0
0
0
200
600
100
6900
600
1000
7800
258
2015-01-14 10:00:00
12.40
7.58
423.0
5.04
3.87
0
100
0
0
...
0
0
0
100
400
0
1000
200
500
1200
259
2015-02-04 10:00:00
16.00
7.62
331.0
4.70
3.44
400
1600
0
0
...
0
0
0
600
200
0
2800
0
800
3400
260
2015-02-12 09:00:00
15.20
7.99
313.0
5.09
2.77
1400
1200
0
800
...
0
0
0
1000
400
200
4200
0
2200
6200
262
2015-03-03 09:00:00
19.30
8.34
333.0
4.73
2.57
31600
3200
0
600
...
0
0
0
2000
28400
1000
68000
0
31000
69000
264
2015-03-10 11:00:00
17.20
7.45
341.0
4.71
2.84
200
200
0
400
...
200
0
0
200
1800
400
2800
400
2400
3800
265
2015-03-24 10:00:00
17.10
0.00
316.0
4.06
2.32
7000
1600
0
800
...
0
0
0
2600
2400
400
15000
0
5800
16600
266
2015-04-01 10:00:00
13.80
7.53
419.0
4.18
2.21
8800
1600
0
1400
...
0
0
0
5200
1000
600
19800
0
8400
24000
267
2015-04-09 10:00:00
13.00
7.45
430.0
3.26
2.96
8800
2200
0
200
...
0
0
0
7200
5800
200
28400
200
13400
29800
268
2015-04-16 10:00:00
12.60
7.33
465.0
3.49
2.73
5400
600
0
400
...
0
0
0
5400
7600
200
20200
0
13400
22600
270
2015-05-06 10:00:00
7.36
7.32
318.0
2.66
2.37
10200
3000
0
1400
...
0
0
0
3200
3000
200
21800
0
8400
25400
289
2015-05-29 10:00:00
10.10
0.00
350.0
5.86
2.88
66000
35600
0
400
...
0
0
0
3600
19200
0
136800
2800
24400
147600
290
2015-05-30 11:00:00
12.70
0.00
328.0
6.30
3.04
9600
42400
0
1200
...
1200
0
0
3600
5600
0
66800
3200
18400
82800
293
2015-06-02 11:00:00
13.10
0.00
340.0
7.30
3.12
64800
5000
0
1200
...
0
0
0
7200
16000
1600
105000
400
25200
111000
294
2015-06-03 10:00:00
17.70
0.00
373.0
6.58
4.56
42000
9200
0
0
...
0
0
0
1200
10800
800
64800
3200
12800
74800
295
2015-06-04 10:00:00
15.70
0.00
433.0
5.06
3.92
76800
55600
0
0
...
0
0
0
3200
16000
400
159600
1200
19600
169600
296
2015-06-05 10:00:00
17.80
0.00
415.0
5.67
3.92
17600
52000
0
0
...
4000
0
0
3200
2000
1200
85200
6400
5200
118400
300
2015-06-08 10:00:00
14.40
0.00
439.0
5.35
3.68
58800
40800
0
0
...
0
0
0
2400
15600
0
125600
800
19200
136000
164 rows × 23 columns
In [210]:
import matplotlib.pyplot as plt
import matplotlib
matplotlib.style.use('ggplot')
%matplotlib inline
In [211]:
df.columns
Out[211]:
Index([u'date', u'Turbidity \n(NTU.)', u'pH', u'Conductivity \n(umho/cm)',
u'DO \n(mgl)', u'OC \n(mg/l)', u'Cyclotella sp.', u'Aulocoseria sp.',
u'Melosira granulata\n(Aulocoseria granulata)', u'Nitzschia sp.',
u'Nitzschia palae', u'Skeletonema sp.', u'Synedra acus',
u'Synedra ulna', u'Anabaena sp.', u'Lyngbya sp.', u'Oscillatoria sp.',
u'Chlorella sp.', u'Closterium sp.', u'Filter Algae',
u'Taste&Odor Algae', u'Polution Algae', u'Total Algae'],
dtype='object')
In [269]:
##water
df_water=df[df.columns[0:6]]
ax1 = plt.subplot(212)
ax1.set_title('Water Property')
df_water.plot(x='date', logy=True, ax=ax1)
plt.setp(ax1.get_xticklabels(), fontsize=10, visible=True)
plt.setp(ax1.get_yticklabels(), fontsize=14)
##algae
algae_columns=df.columns[6:19].insert(0,df.columns[0])
df_water=df[algae_columns]
ax2 = plt.subplot(211, sharex=ax1)
ax2.set_title('Algae Count')
df_water.plot.area(x='date', ax=ax2)
plt.setp(ax2.get_xticklabels(), fontsize=10, visible=True)
plt.setp(ax2.get_yticklabels(), fontsize=14)
plt.ticklabel_format(style='sci', axis='y', scilimits=(0,0))
fig = matplotlib.pyplot.gcf()
fig.set_size_inches(20,20)
fname=os.path.join('static','graph.png')
fig.savefig(fname, dpi=120, bbox_inches='tight')
In [265]:
x=ax1.get_xticklabels()[0]
In [268]:
Out[268]:
<matplotlib.axis.XAxis at 0x107c52b0>
In [ ]:
Content source: wasit7/algae2
Similar notebooks: