Copy ! pip install pymysql
import pymysql
Copy Looking in indexes: https://pypi.tuna.tsinghua.edu.cn/simple
Requirement already satisfied: pymysql in c:\users\rustynail\miniconda3\envs\d2l\lib\site-packages (1.0.2)
WARNING: Ignoring invalid distribution -rotobuf (c:\users\rustynail\miniconda3\envs\d2l\lib\site-packages)
WARNING: Ignoring invalid distribution -rotobuf (c:\users\rustynail\miniconda3\envs\d2l\lib\site-packages)
WARNING: Ignoring invalid distribution -rotobuf (c:\users\rustynail\miniconda3\envs\d2l\lib\site-packages)
WARNING: Ignoring invalid distribution -rotobuf (c:\users\rustynail\miniconda3\envs\d2l\lib\site-packages)
WARNING: Ignoring invalid distribution -rotobuf (c:\users\rustynail\miniconda3\envs\d2l\lib\site-packages)
WARNING: Ignoring invalid distribution -rotobuf (c:\users\rustynail\miniconda3\envs\d2l\lib\site-packages)
Copy db = pymysql.connect( host = 'x' ,
port = 1 ,
user = 'x' ,
password = 'x' ,
database = 'x' )
cursor = db.cursor()
cursor.execute( "select price, price_updated from raw_item_rs where en_name = %s " , "Red Rebel ice pick" )
all = cursor.fetchall()
Copy from datetime import datetime
import time
all = [{ "price" : x[ 0 ], "time" : int (time.mktime(datetime.strptime(x[ 1 ], "%Y-%m- %d T%H:%M:%S. %f z" ).timetuple()))} for x in all ]
print ( len ( all ))
Copy import json
with open ( "../data/RedRebel.json" , "w" ) as f:
f.write(json.dumps( all ))
f.close()
Copy import matplotlib.pyplot as plt
import numpy as np
all .sort( key =lambda x: x[ 'time' ])
print ( all [: 10 ])
x = np.array([x[ 'time' ] for x in all ])
y = np.array([x[ 'price' ] for x in all ])
plt.plot(x, y)
plt.show()
Copy [{'price': 2999999, 'time': 1656638268}, {'price': 4499999, 'time': 1656688384}, {'price': 5500000, 'time': 1656707919}, {'price': 6000000, 'time': 1656728553}, {'price': 5500000, 'time': 1656797161}, {'price': 4300000, 'time': 1656810858}, {'price': 3911111, 'time': 1656824533}, {'price': 3800000, 'time': 1656838167}, {'price': 3799999, 'time': 1656852073}, {'price': 5555555, 'time': 1656864556}]
Copy import pandas as pd
def mean_norm (df_input): #@save
return df_input.apply( lambda x: (x - x.mean()) / x.std(), axis = 0 )
def de_mean_norm (result, df_input:pd.DataFrame): #@save
return result * df_input.std() + df_input.mean()
Copy X = pd.DataFrame(x)
Y = pd.DataFrame(y)
print (X, Y)
Copy 0
0 1656638268
1 1656688384
2 1656707919
3 1656728553
4 1656797161
.. ...
324 1661098048
325 1661107841
326 1661120091
327 1661131513
328 1661151899
[329 rows x 1 columns] 0
0 2999999
1 4499999
2 5500000
3 6000000
4 5500000
.. ...
324 3840000
325 3900000
326 3942000
327 3555555
328 3958888
[329 rows x 1 columns]
Copy X = mean_norm(X)
Y = mean_norm(Y)
print (X.info, Y.info)
Copy <bound method DataFrame.info of 0
0 -1.826706
1 -1.787782
2 -1.772610
3 -1.756584
4 -1.703298
.. ...
324 1.637094
325 1.644700
326 1.654214
327 1.663085
328 1.678918
[329 rows x 1 columns]> <bound method DataFrame.info of 0
0 -1.917204
1 0.956663
2 2.872577
3 3.830532
4 2.872577
.. ...
324 -0.307836
325 -0.192882
326 -0.112413
327 -0.852808
328 -0.080057
[329 rows x 1 columns]>
Copy import seaborn as sbn
Copy all = pd.DataFrame( all )
all .info
Copy <bound method DataFrame.info of price time
0 2999999 1656638268
1 4499999 1656688384
2 5500000 1656707919
3 6000000 1656728553
4 5500000 1656797161
.. ... ...
324 3840000 1661098048
325 3900000 1661107841
326 3942000 1661120091
327 3555555 1661131513
328 3958888 1661151899
[329 rows x 2 columns]>
Copy sbn.scatterplot( x = "time" , y = 'price' , data = all )
Copy <AxesSubplot:xlabel='time', ylabel='price'>
Copy ## 回归网络
import tensorflow as tf
from d2l import tensorflow as d2l
Copy net = tf.keras.Sequential()
# 添加一个连接层(Dense),输出标量数量为 1 ( w1x1 + w2x2 + b = y)
net.add(tf.keras.layers.Dense( units = 10 , input_dim = 1 , activation = tf.keras.activations.relu))
net.add(tf.keras.layers.Dense( units = 1 , input_dim = 10 , activation = tf.keras.activations.relu))
# 正态分布随机
initializer = tf.initializers.RandomNormal( stddev = 0.1 )
# 获取网络
# net = tf.keras.Sequential()
# 相当初始化一层,用来提供初始化数据
net.add(tf.keras.layers.Dense( 1 , kernel_initializer = initializer))
Copy # 批量随机梯度下降
# trainer = tf.keras.optimizers.SGD(learning_rate=0.0003)
sgd = tf.keras.optimizers.Adam( learning_rate = 0.001 )
net.compile( optimizer = sgd, loss = tf.keras.losses.MeanSquaredError())
Copy history = net.fit(X, Y, batch_size = 20 , epochs = 300 , validation_split = 0.2 , callbacks = [])
net.summary()
Copy Epoch 1/300
14/14 [==============================] - 0s 12ms/step - loss: 1.0883 - val_loss: 0.5398
Epoch 2/300
14/14 [==============================] - 0s 6ms/step - loss: 1.0816 - val_loss: 0.5425
Epoch 300/300
14/14 [==============================] - 0s 6ms/step - loss: 0.6169 - val_loss: 0.3916
Model: "sequential_2"
_________________________________________________________________
Layer (type) Output Shape Param #
=================================================================
dense_6 (Dense) (None, 10) 20
dense_7 (Dense) (None, 1) 11
dense_8 (Dense) (None, 1) 2
=================================================================
Total params: 33
Trainable params: 33
Non-trainable params: 0
_________________________________________________________________
Copy plt.plot(history.history[ "loss" ], label = "Training Loss" )
plt.plot(history.history[ "val_loss" ], label = "Validation Loss" )
plt.legend()
plt.show()
Copy # future 3 day per hour
import datetime as dt
dateRange = pd.date_range( start = dt.datetime.strptime( '2022082201' , '%Y%m %d %H' ), periods = 24 , freq = 'H' )
Copy testX = pd.DataFrame([ int (d.timestamp()) for d in dateRange])
0 0 1661130000 1 1661133600 2 1661137200 3 1661140800 4 1661144400 5 1661148000 6 1661151600 7 1661155200 8 1661158800 9 1661162400 10 1661166000 11 1661169600 12 1661173200 13 1661176800 14 1661180400 15 1661184000 16 1661187600 17 1661191200 18 1661194800 19 1661198400 20 1661202000 21 1661205600 22 1661209200 23 1661212800
Copy testX1 = mean_norm(testX)
0 0 -1.626346 1 -1.484924 2 -1.343503 3 -1.202082 4 -1.060660 5 -0.919239 6 -0.777817 7 -0.636396 8 -0.494975 9 -0.353553 10 -0.212132 11 -0.070711 12 0.070711 13 0.212132 14 0.353553 15 0.494975 16 0.636396 17 0.777817 18 0.919239 19 1.060660 20 1.202082 21 1.343503 22 1.484924 23 1.626346
Copy testY = net.predict(testX1)
Copy array([[ 1.6376313 ],
[ 1.4921811 ],
[ 1.3457209 ],
[ 0.8415806 ],
[ 0.2825816 ],
[-0.27449 ],
[-0.28884372],
[-0.28884372],
[-0.28884372],
[-0.28884372],
[-0.28884372],
[-0.28884372],
[-0.28884372],
[-0.28884372],
[-0.28884372],
[-0.28884372],
[-0.28884372],
[-0.28884372],
[-0.28884372],
[-0.28884372],
[-0.28884372],
[-0.28884372],
[-0.28884372],
[-0.28884372]], dtype=float32)
Copy y1 = [yyy[ 0 ] for yyy in testY]
y1
Copy [1.6376313,
1.4921811,
1.3457209,
0.8415806,
0.2825816,
-0.27449,
-0.28884372,
-0.28884372,
-0.28884372,
-0.28884372,
-0.28884372,
-0.28884372,
-0.28884372,
-0.28884372,
-0.28884372,
-0.28884372,
-0.28884372,
-0.28884372,
-0.28884372,
-0.28884372,
-0.28884372,
-0.28884372,
-0.28884372,
-0.28884372]
Copy y_de = [de_mean_norm(yyy, pd.DataFrame(y)) for yyy in y1]
Copy for n in zip (y_de, testX.values):
print ( type (n[ 1 ][ 0 ]))
print ( " %d --> %s " % (n[ 0 ], dt.datetime.fromtimestamp(n[ 1 ][ 0 ])))
Copy <class 'numpy.int64'>
4855426 --> 2022-08-22 09:00:00
<class 'numpy.int64'>
4779509 --> 2022-08-22 10:00:00
<class 'numpy.int64'>
4703065 --> 2022-08-22 11:00:00
<class 'numpy.int64'>
4439932 --> 2022-08-22 12:00:00
<class 'numpy.int64'>
4148165 --> 2022-08-22 13:00:00
<class 'numpy.int64'>
3857404 --> 2022-08-22 14:00:00
<class 'numpy.int64'>
3849913 --> 2022-08-22 15:00:00
<class 'numpy.int64'>
3849913 --> 2022-08-22 16:00:00
<class 'numpy.int64'>
3849913 --> 2022-08-22 17:00:00
<class 'numpy.int64'>
3849913 --> 2022-08-22 18:00:00
<class 'numpy.int64'>
3849913 --> 2022-08-22 19:00:00
<class 'numpy.int64'>
3849913 --> 2022-08-22 20:00:00
<class 'numpy.int64'>
3849913 --> 2022-08-22 21:00:00
<class 'numpy.int64'>
3849913 --> 2022-08-22 22:00:00
<class 'numpy.int64'>
3849913 --> 2022-08-22 23:00:00
<class 'numpy.int64'>
3849913 --> 2022-08-23 00:00:00
<class 'numpy.int64'>
3849913 --> 2022-08-23 01:00:00
<class 'numpy.int64'>
3849913 --> 2022-08-23 02:00:00
<class 'numpy.int64'>
3849913 --> 2022-08-23 03:00:00
<class 'numpy.int64'>
3849913 --> 2022-08-23 04:00:00
<class 'numpy.int64'>
3849913 --> 2022-08-23 05:00:00
<class 'numpy.int64'>
3849913 --> 2022-08-23 06:00:00
<class 'numpy.int64'>
3849913 --> 2022-08-23 07:00:00
<class 'numpy.int64'>
3849913 --> 2022-08-23 08:00:00
Copy plt.plot([dt.datetime.fromtimestamp(dd[ 0 ]) for dd in testX.values], [ ' %d ' % de for de in y_de])
Copy [<matplotlib.lines.Line2D at 0x24d6f149e80>]
这个价格和目前游戏内的交易价格相差不大#