[人工智能] 机器学习中的数据分析和管理

开发: C++知识库 Java知识库 JavaScript Python PHP知识库人工智能区块链大数据移动开发嵌入式开发工具数据结构与算法开发测试游戏开发网络协议系统运维
教程: HTML教程 CSS教程 JavaScript教程 Go语言教程 JQuery教程 VUE教程 VUE3教程 Bootstrap教程 SQL数据库教程 C语言教程 C++教程 Java教程 Python教程 Python3教程 C#教程
数码: 电脑笔记本显卡显示器固态硬盘硬盘耳机手机 iphone vivo oppo 小米华为单反装机图拉丁

-> 人工智能 -> 机器学习中的数据分析和管理 -> 正文阅读

[人工智能]机器学习中的数据分析和管理

大数据，标签数据，噪声数据。机器学习项目都需要看数据。数据是机器学习项目的一个关键方面，我们如何处理这些数据是至关重要的。当数据量增长时，需要对它们进行管理，允许它们为多个项目服务，或者只是有一种更好的方法来检索数据，考虑使用数据库系统是很自然的。它可以是关系数据库或平面文件格式。可以是本地的，也可以是远程的。

在这篇文章中，我们将探索不同的格式和库，你可以用来存储和检索。

主要可以学习到以下几方面的内容：

使用SQLite、Python dbm库、Excel和谷歌表管理数据
如何使用外部存储的数据来训练机器学习模型
在机器学习项目中使用数据库的利与弊是什么

在这里插入图片描述

概述

本博客分为七个部分;它们是:

在SQLite中管理数据
SQLite的使用
管理dbm中的数据
在机器学习管道中使用dbm数据库
在Excel中管理数据
管理谷歌表中的数据
数据库的其他用途

在SQLite中管理数据

当我们提到数据库时，它通常是指以表格格式存储数据的关系数据库。
首先，让我们从sklearn.dataset获取一个表格数据集

# Read dataset from OpenML
from sklearn.datasets import fetch_openml
dataset = fetch_openml("diabetes", version=1, as_frame=True, return_X_y=False)["frame"]

上面几行是从OpenML读取“皮马印第安人糖尿病数据集”并创建一个pandas数据框架。这是一个具有多个数值特征和一个二进制类标签的分类数据集。我们可以通过以下方式来探索DataFrame:

print(type(dataset))
print(dataset.head())

可以看到:

<class 'pandas.core.frame.DataFrame'>
   preg   plas  pres  skin   insu  mass   pedi   age            class
0   6.0  148.0  72.0  35.0    0.0  33.6  0.627  50.0  tested_positive
1   1.0   85.0  66.0  29.0    0.0  26.6  0.351  31.0  tested_negative
2   8.0  183.0  64.0   0.0    0.0  23.3  0.672  32.0  tested_positive
3   1.0   89.0  66.0  23.0   94.0  28.1  0.167  21.0  tested_negative
4   0.0  137.0  40.0  35.0  168.0  43.1  2.288  33.0  tested_positive

这不是一个非常大的数据集，但如果它太大，我们可能无法将其放入内存中。关系数据库是一种工具，可以帮助我们有效地管理表格数据，而无需将所有数据都保存在内存中。通常，关系数据库是一种描述数据操作的语言。SQLite是一个无服务器的数据库系统，不需要任何设置，我们在Python中有内置的库支持。在下面，我们将演示如何使用SQLite管理数据，但使用不同的数据库，如MariaDB或PostgreSQL，基本上都是相似的。

现在，让我们开始在SQLite中创建一个内存中的数据库，并获取一个游标对象来执行对新数据库的查询:

import sqlite3

conn = sqlite3.connect(":memory:")
cur = conn.cursor()

如果我们想要我们的数据存储在磁盘上,这样我们可以重用它或者在其他项目中使用,我们可以将数据库存储在一个数据库文件（例如,example.db）,因此:

conn = sqlite3.connect("example.db")

让我们继续为糖尿病数据创建一个新表。

...
create_sql = """
    CREATE TABLE diabetes(
        preg NUM,
        plas NUM, 
        pres NUM,
        skin NUM,
        insu NUM,
        mass NUM,
        pedi NUM,
        age NUM,
        class TEXT
    )
"""
cur.execute(create_sql)

execute()方法执行SQL查询。在本例中，SQL查询创建了具有不同列及其各自数据类型的糖尿病表。
接下来，我们可以从存储在pandas DataFrame中的糖尿病数据集中插入数据到内存中SQL数据库中新创建的糖尿病表中。

# Prepare a parameterized SQL for insert
insert_sql = "INSERT INTO diabetes VALUES (?,?,?,?,?,?,?,?,?)"
# execute the SQL multiple times with each element in dataset.to_numpy().tolist()
cur.executemany(insert_sql, dataset.to_numpy().tolist())

让我们分解一下上面的代码:dataset.to_numpy().tolist()给出了dataset中的数据行列表，我们将把它作为参数传递给cur.executemany()。然后，cur.executemany()多次运行SQL语句，每次都使用dataset.to_numpy().tolist()中的一个元素，这是dataset中的一行数据。参数化的SQL每次都需要一个值列表，因此我们应该将这个列表列表传递给executemany()，这就是dataset.to_numpy().tolist()创建的。

现在，我们可以检查确认所有数据都存储在数据库中:

import pandas as pd

def cursor2dataframe(cur):
    """Read the column header from the cursor and then the rows of
    data from it. Afterwards, create a DataFrame"""
    header = [x[0] for x in cur.description]
    # gets data from the last executed SQL query
    data = cur.fetchall()
    # convert the data into a pandas DataFrame
    return pd.DataFrame(data, columns=header)

# get 5 random rows from the diabetes table
select_sql = "SELECT * FROM diabetes ORDER BY random() LIMIT 5"
cur.execute(select_sql)
sample = cursor2dataframe(cur)
print(sample)

在上面的例子中，我们使用SQL中的SELECT语句查询表diabetes中的5个随机行。结果将以元组列表的形式返回(每行一个元组)。然后，通过将名称与每个列关联，将元组列表转换为一个pandas DataFrame。运行上面的代码片段，我们得到如下输出:

   preg  plas  pres  skin  insu  mass   pedi  age            class
0     2    90    68    42     0  38.2  0.503   27  tested_positive
1     9   124    70    33   402  35.4  0.282   34  tested_negative
2     7   160    54    32   175  30.5  0.588   39  tested_positive
3     7   105     0     0     0   0.0  0.305   24  tested_negative
4     1   107    68    19     0  26.5  0.165   24  tested_negative

以下是使用sqlite3从糖尿病数据集的关系数据库中创建、插入和检索示例的完整代码:

import sqlite3

import pandas as pd
from sklearn.datasets import fetch_openml

# Read dataset from OpenML
dataset = fetch_openml("diabetes", version=1, as_frame=True, return_X_y=False)["frame"]
print("Data from OpenML:")
print(type(dataset))
print(dataset.head())

# Create database
conn = sqlite3.connect(":memory:")
cur = conn.cursor()
create_sql = """
    CREATE TABLE diabetes(
        preg NUM,
        plas NUM,
        pres NUM,
        skin NUM,
        insu NUM,
        mass NUM,
        pedi NUM,
        age NUM,
        class TEXT
    )
"""
cur.execute(create_sql)

# Insert data into the table using a parameterized SQL
insert_sql = "INSERT INTO diabetes VALUES (?,?,?,?,?,?,?,?,?)"
rows = dataset.to_numpy().tolist()
cur.executemany(insert_sql, rows)

def cursor2dataframe(cur):
    """Read the column header from the cursor and then the rows of
    data from it. Afterwards, create a DataFrame"""
    header = [x[0] for x in cur.description]
    # gets data from the last executed SQL query
    data = cur.fetchall()
    # convert the data into a pandas DataFrame
    return pd.DataFrame(data, columns=header)

# get 5 random rows from the diabetes table
select_sql = "SELECT * FROM diabetes ORDER BY random() LIMIT 5"
cur.execute(select_sql)
sample = cursor2dataframe(cur)
print("Data from SQLite database:")
print(sample)

# close database connection
conn.commit()
conn.close()

如果数据集不是从Internet获取的，而是随着时间的推移收集的，那么使用数据库的好处是显而易见的。例如，你可能需要花费许多天时间从传感器收集数据。可以使用自动化作业将每小时收集的数据写入数据库。然后，你的机器学习项目可以使用数据库中的数据集运行，随着数据的积累，可能会看到不同的结果。

让我们看看如何将关系数据库构建到机器学习管道中!

SQLite 示例

现在我们已经探索了如何使用sqlite3存储和检索关系数据库中的数据，我们可能会对如何将其集成到机器学习管道中感兴趣。

通常，在这种情况下，我们会有一个收集数据并将其写入数据库的过程(例如，从传感器读取数据需要很多天)。这将类似于前一节中的代码，只是我们更喜欢将数据库写入磁盘以进行持久存储。然后我们将在机器学习过程中从数据库中读取，用于训练或预测。根据模型的不同，有不同的方法使用数据。让我们考虑Keras糖尿病数据集的二元分类模型。我们可以构建一个生成器来读取随机批数据。

def datagen(batch_size):
    conn = sqlite3.connect("diabetes.db", check_same_thread=False)
    cur = conn.cursor()
    sql = f"""
        SELECT preg, plas, pres, skin, insu, mass, pedi, age, class
        FROM diabetes
        ORDER BY random()
        LIMIT {batch_size}
    """
    while True:
        cur.execute(sql)
        data = cur.fetchall()
        X = [row[:-1] for row in data]
        y = [1 if row[-1]=="tested_positive" else 0 for row in data]
        yield np.asarray(X), np.asarray(y)

上面的代码是一个生成器函数，它从SQLite数据库获取batch_size的行数，并以NumPy数组的形式返回它们。我们可以使用这个生成器的数据在我们的分类网络中进行训练:

from keras.models import Sequential
from keras.layers import Dense

# create binary classification model
model = Sequential()
model.add(Dense(16, input_dim=8, activation='relu'))
model.add(Dense(8, activation='relu'))
model.add(Dense(1, activation='sigmoid'))
model.compile(loss='binary_crossentropy', optimizer='adam', metrics=['accuracy'])

# train model
history = model.fit(datagen(32), epochs=5, steps_per_epoch=2000)

运行上面的代码会得到如下输出:

Epoch 1/5
2000/2000 [==============================] - 6s 3ms/step - loss: 2.2360 - accuracy: 0.6730
Epoch 2/5
2000/2000 [==============================] - 5s 2ms/step - loss: 0.5292 - accuracy: 0.7380
Epoch 3/5
2000/2000 [==============================] - 5s 2ms/step - loss: 0.4936 - accuracy: 0.7564
Epoch 4/5
2000/2000 [==============================] - 5s 2ms/step - loss: 0.4751 - accuracy: 0.7662
Epoch 5/5
2000/2000 [==============================] - 5s 2ms/step - loss: 0.4487 - accuracy: 0.7834

注意，我们只在生成器函数中读取批数据，而不是所有数据。我们依赖数据库为我们提供数据，而不关心数据库中的数据集有多大。尽管SQLite不是客户端-服务端数据库系统，因此不能扩展到网络，但有其他数据库系统可以做到这一点。因此，可以想象，当我们的机器学习应用程序只提供有限的内存时，可以使用一个非常大的数据集。

以下是完整的代码，从准备数据库到使用实时读取数据训练Keras模型:

import sqlite3

import numpy as np
from sklearn.datasets import fetch_openml
from tensorflow.keras.models import Sequential
from tensorflow.keras.layers import Dense

# Create database
conn = sqlite3.connect("diabetes.db")
cur = conn.cursor()
cur.execute("DROP TABLE IF EXISTS diabetes")
create_sql = """
    CREATE TABLE diabetes(
        preg NUM,
        plas NUM,
        pres NUM,
        skin NUM,
        insu NUM,
        mass NUM,
        pedi NUM,
        age NUM,
        class TEXT
    )
"""
cur.execute(create_sql)

# Read data from OpenML, insert data into the table using a parameterized SQL
dataset = fetch_openml("diabetes", version=1, as_frame=True, return_X_y=False)["frame"]
insert_sql = "INSERT INTO diabetes VALUES (?,?,?,?,?,?,?,?,?)"
rows = dataset.to_numpy().tolist()
cur.executemany(insert_sql, rows)

# Commit to flush change to disk, then close connection
conn.commit()
conn.close()

# Create data generator for Keras classifier model
def datagen(batch_size):
    """A generator to produce samples from database
    """
    # Tensorflow may run in different thread, thus needs check_same_thread=False
    conn = sqlite3.connect("diabetes.db", check_same_thread=False)
    cur = conn.cursor()
    sql = f"""
        SELECT preg, plas, pres, skin, insu, mass, pedi, age, class
        FROM diabetes
        ORDER BY random()
        LIMIT {batch_size}
    """
    while True:
        # Read rows from database
        cur.execute(sql)
        data = cur.fetchall()
        # Extract features
        X = [row[:-1] for row in data]
        # Extract targets, encode into binary (0 or 1)
        y = [1 if row[-1]=="tested_positive" else 0 for row in data]
        yield np.asarray(X), np.asarray(y)

# create binary classification model
model = Sequential()
model.add(Dense(16, input_dim=8, activation='relu'))
model.add(Dense(8, activation='relu'))
model.add(Dense(1, activation='sigmoid'))
model.compile(loss='binary_crossentropy', optimizer='adam', metrics=['accuracy'])

# train model
history = model.fit(datagen(32), epochs=5, steps_per_epoch=2000)

在继续下一节之前，我们应该强调所有数据库都有一些不同。我们使用的SQL语句在其他数据库实现中可能不是最优的。另外，要注意SQLite不是很高级，因为它的目标是成为一个不需要服务器设置的数据库。使用大型数据库以及如何优化使用是一个很大的主题。

管理dbm中的数据

关系数据库非常适合表格数据，但并非所有数据集都是在表格结构中。有时候，数据最好存储在像Python字典这样的结构中，即键值存储。有许多键值数据存储。MongoDB可能是最有名的一个，它需要像PostgreSQL一样的服务器部署。与SQLite一样，GNU dbm是一个无服务器的存储，它安装在几乎所有的Linux系统中。在Python的标准库中，我们有使用它的dbm模块。

让我们探索Python的dbm库。这个库支持两种不同的dbm实现:

import sklearn.datasets

# get digits dataset (8x8 images of digits)
digits = sklearn.datasets.load_digits()

dbm库使用一个类似字典的接口来存储和检索dbm文件中的数据，将键映射到值，其中键和值都是字符串。将数字数据集存储在文件数字中的代码。DBM为:

import dbm
import pickle

# create file if not exists, otherwise open for read/write
with dbm.open("digits.dbm", "c") as db:
    for idx in range(len(digits.target)):
        db[str(idx)] = pickle.dumps((digits.images[idx], digits.target[idx]))

上面的代码片段创建了一个新的文件digits.dbm 。然后，我们选择每个digits image(来自digits.images)和标签(来自digits.target)，并创建一个元组。我们使用数据的偏移量作为键，使用元组的pickle字符串作为值存储在数据库中。与Python的字典不同，dbm只允许字符串的键和序列化值存储。因此，我们使用str(idx)将键强制转换为字符串，存储pickled的数据。

下面是我们如何从数据库中读取数据:

import random
import numpy as np

# number of images that we want in our sample
batchsize = 4
images = []
targets = []

# open the database and read a sample
with dbm.open("digits.dbm", "r") as db:
    # get all keys from the database
    keys = db.keys()
    # randomly samples n keys
    for key in random.sample(keys, batchsize):
        # go through each key in the random sample
        image, target = pickle.loads(db[key])
        images.append(image)
        targets.append(target)
    print(np.asarray(images), np.asarray(targets))

在上面的代码片段中，我们从数据库中获取4个随机键，然后获取它们对应的值，并使用pickle.loads()反序列化。我们知道，反序列化后的数据将是一个元组;我们将它们分配给变量image和target，然后收集image和target列表中的每个随机样本。为了便于使用scikit-learn或Keras进行训练，我们通常喜欢将整个批作为NumPy数组。
运行上面的代码会得到输出:

[[[ 0.  0.  1.  9. 14. 11.  1.  0.]
  [ 0.  0. 10. 15.  9. 13.  5.  0.]
  [ 0.  3. 16.  7.  0.  0.  0.  0.]
  [ 0.  5. 16. 16. 16. 10.  0.  0.]
  [ 0.  7. 16. 11. 10. 16.  5.  0.]
  [ 0.  2. 16.  5.  0. 12.  8.  0.]
  [ 0.  0. 10. 15. 13. 16.  5.  0.]
  [ 0.  0.  0.  9. 12.  7.  0.  0.]]
...
] [6 8 7 3]

然后从dbm数据库中创建、插入和采样的代码:

import dbm
import pickle
import random

import numpy as np
import sklearn.datasets

# get digits dataset (8x8 images of digits)
digits = sklearn.datasets.load_digits()

# create file if not exists, otherwise open for read/write
with dbm.open("digits.dbm", "c") as db:
    for idx in range(len(digits.target)):
        db[str(idx)] = pickle.dumps((digits.images[idx], digits.target[idx]))

# number of images that we want in our sample
batchsize = 4
images = []
targets = []

# open the database and read a sample
with dbm.open("digits.dbm", "r") as db:
    # get all keys from the database
    keys = db.keys()
    # randomly samples n keys
    for key in random.sample(keys, batchsize):
        # go through each key in the random sample
        image, target = pickle.loads(db[key])
        images.append(image)
        targets.append(target)
    print(np.array(images), np.array(targets))

接下来，让我们看看如何在机器学习管道中使用新创建的dbm数据库!

在机器学习管道(Pipeline)中使用dbm数据库

在这里，我们为digits classification创建一个生成器和Keras模型，就像我们在SQLite数据库示例中所做的那样。下面是我们修改后的代码。首先是生成器函数。我们只需要在循环中随机选择一批键，然后从dbm存储中获取数据:

def datagen(batch_size):
    """A generator to produce samples from database
    """
    with dbm.open("digits.dbm", "r") as db:
        keys = db.keys()
        while True:
            images = []
            targets = []
            for key in random.sample(keys, batch_size):
                image, target = pickle.loads(db[key])
                images.append(image)
                targets.append(target)
            yield np.array(images).reshape(-1,64), np.array(targets)

`然后，我们可以为数据创建一个简单的MLP模型:

import tensorflow as tf
from tensorflow.keras.models import Sequential
from tensorflow.keras.layers import Dense

model = Sequential()
model.add(Dense(32, input_dim=64, activation='relu'))
model.add(Dense(32, activation='relu'))
model.add(Dense(10, activation='softmax'))
model.compile(loss="sparse_categorical_crossentropy",
              optimizer="adam",
              metrics=["sparse_categorical_accuracy"])

history = model.fit(datagen(32), epochs=5, steps_per_epoch=1000)

运行上面的代码会得到如下输出:

Epoch 1/5
1000/1000 [==============================] - 3s 2ms/step - loss: 0.6714 - sparse_categorical_accuracy: 0.8090
Epoch 2/5
1000/1000 [==============================] - 2s 2ms/step - loss: 0.1049 - sparse_categorical_accuracy: 0.9688
Epoch 3/5
1000/1000 [==============================] - 2s 2ms/step - loss: 0.0442 - sparse_categorical_accuracy: 0.9875
Epoch 4/5
1000/1000 [==============================] - 2s 2ms/step - loss: 0.0484 - sparse_categorical_accuracy: 0.9850
Epoch 5/5
1000/1000 [==============================] - 2s 2ms/step - loss: 0.0245 - sparse_categorical_accuracy: 0.9935

这就是我们使用dbm数据库训练MLP。使用dbm训练模型的完整代码在这里:

import dbm
import pickle
import random

import numpy as np
import sklearn.datasets
from tensorflow.keras.models import Sequential
from tensorflow.keras.layers import Dense

# get digits dataset (8x8 images of digits)
digits = sklearn.datasets.load_digits()

# create file if not exists, otherwise open for read/write
with dbm.open("digits.dbm", "c") as db:
    for idx in range(len(digits.target)):
        db[str(idx)] = pickle.dumps((digits.images[idx], digits.target[idx]))

# retrieving data from database for model
def datagen(batch_size):
    """A generator to produce samples from database
    """
    with dbm.open("digits.dbm", "r") as db:
        keys = db.keys()
        while True:
            images = []
            targets = []
            for key in random.sample(keys, batch_size):
                image, target = pickle.loads(db[key])
                images.append(image)
                targets.append(target)
            yield np.array(images).reshape(-1,64), np.array(targets)

# Classification model in Keras
model = Sequential()
model.add(Dense(32, input_dim=64, activation='relu'))
model.add(Dense(32, activation='relu'))
model.add(Dense(10, activation='softmax'))
model.compile(loss="sparse_categorical_crossentropy",
              optimizer="adam",
              metrics=["sparse_categorical_accuracy"])

# Train with data from dbm store
history = model.fit(datagen(32), epochs=5, steps_per_epoch=1000)

在更高级的系统中，如MongoDB或Couchbase，我们可能只是要求数据库系统为我们读取随机记录，而不是从所有键的列表中随机选取样本。但思想是一样的;我们可以依赖外部存储来保存数据和管理数据集，而不是在我们的Python脚本中。

在Excel中管理数据

有时候，内存并不是我们将数据保存在机器学习脚本之外的原因。因为有更好的工具来处理数据。也许我们希望有工具在屏幕上显示所有数据，并允许我们滚动，格式化和高亮显示等。或者，我们可能想与不关心我们的Python程序的其他人共享数据。在可以使用关系数据库的情况下，使用Excel管理数据是很常见的。虽然Excel可以读取和导出CSV文件，但我们可能希望直接处理Excel文件。

在Python中，有几个库来处理Excel文件，OpenPyXL是其中最著名的一个。我们需要安装这个库才能使用它:

pip install openpyxl

Excel使用文件名以.xlsx结尾的“Open XML电子表格”格式。旧的Excel文件是文件名后缀为.xls的二进制格式，OpenPyXL不支持这种格式(在OpenPyXL中，可以使用xlrd和xlwt模块进行读写)。

让我们考虑我们在上面的SQLite中使用的相同示例。我们可以打开一个新的Excel工作簿，并将糖尿病数据集写入工作表:

import pandas as pd
from sklearn.datasets import fetch_openml
import openpyxl

# Read dataset from OpenML
dataset = fetch_openml("diabetes", version=1, as_frame=True, return_X_y=False)["frame"]
header = list(dataset.columns)
data = dataset.to_numpy().tolist()

# Create Excel workbook and write data into the default worksheet
wb = openpyxl.Workbook()
sheet = wb.active # use the default worksheet
sheet.title = "Diabetes"
for n,colname in enumerate(header):
    sheet.cell(row=1, column=1+n, value=colname)
for n,row in enumerate(data):
    for m,cell in enumerate(row):
        sheet.cell(row=2+n, column=1+m, value=cell)
# Save
wb.save("MLM.xlsx")

上面的代码是为工作表中的每个单元格准备数据(由行和列指定)。当我们创建一个新的Excel文件时，默认会有一个工作表。然后，单元格由行和列偏移量标识，从1开始。我们使用以下语法写入单元格:

sheet.cell(row=3, column=4, value="my data")

要从单元格读取数据，我们使用:

sheet.cell(row=3, column=4).value

单元向Excel中写入数据是乏味的，实际上我们可以逐行添加数据。下面是我们如何修改上面的代码，以行操作，而不是单元格:

import pandas as pd
from sklearn.datasets import fetch_openml
import openpyxl

# Read dataset from OpenML
dataset = fetch_openml("diabetes", version=1, as_frame=True, return_X_y=False)["frame"]
header = list(dataset.columns)
data = dataset.to_numpy().tolist()

# Create Excel workbook and write data into the default worksheet
wb = openpyxl.Workbook()
sheet = wb.create_sheet("Diabetes")  # or wb.active for default sheet
sheet.append(header)
for row in data:
    sheet.append(row)
# Save
wb.save("MLM.xlsx")

一旦我们将数据写入文件，我们就可以使用Excel可视化地浏览数据，添加格式，等等:
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-zboQqGma-1653037489929)(https://machinelearningmastery.com/wp-content/uploads/2022/04/excel.png)]

机器学习项目中使用它并不比使用SQLite数据库更难。下面是Keras中相同的二分类模型，但生成器是从Excel文件中读取的:

import random

import numpy as np
import openpyxl
from sklearn.datasets import fetch_openml
from tensorflow.keras.models import Sequential
from tensorflow.keras.layers import Dense

# Read data from OpenML
dataset = fetch_openml("diabetes", version=1, as_frame=True, return_X_y=False)["frame"]
header = list(dataset.columns)
rows = dataset.to_numpy().tolist()

# Create Excel workbook and write data into the default worksheet
wb = openpyxl.Workbook()
sheet = wb.active
sheet.title = "Diabetes"
sheet.append(header)
for row in rows:
    sheet.append(row)
# Save
wb.save("MLM.xlsx")

# Create data generator for Keras classifier model
def datagen(batch_size):
    """A generator to produce samples from database
    """
    wb = openpyxl.load_workbook("MLM.xlsx", read_only=True)
    sheet = wb.active
    maxrow = sheet.max_row
    while True:
        # Read rows from Excel file
        X = []
        y = []
        for _ in range(batch_size):
            # data starts at row 2
            row_num = random.randint(2, maxrow)
            rowdata = [cell.value for cell in sheet[row_num]]
            X.append(rowdata[:-1])
            y.append(1 if rowdata[-1]=="tested_positive" else 0)
        yield np.asarray(X), np.asarray(y)

# create binary classification model
model = Sequential()
model.add(Dense(16, input_dim=8, activation='relu'))
model.add(Dense(8, activation='relu'))
model.add(Dense(1, activation='sigmoid'))
model.compile(loss='binary_crossentropy', optimizer='adam', metrics=['accuracy'])

# train model
history = model.fit(datagen(32), epochs=5, steps_per_epoch=20)

在上面的代码中，我们特意给fit()函数提供了参数step_per_epoch =20，因为上面的代码会非常慢。这是因为在Python中实现OpenPyXL是为了最大化兼容性，但却牺牲了编译模块所能提供的速度。因此，最好避免每次从Excel逐行读取数据。如果我们需要使用Excel，一个更好的选择是一次性读取整个数据到内存中，然后直接使用它:

import random

import numpy as np
import openpyxl
from sklearn.datasets import fetch_openml
from tensorflow.keras.models import Sequential
from tensorflow.keras.layers import Dense

# Read data from OpenML
dataset = fetch_openml("diabetes", version=1, as_frame=True, return_X_y=False)["frame"]
header = list(dataset.columns)
rows = dataset.to_numpy().tolist()

# Create Excel workbook and write data into the default worksheet
wb = openpyxl.Workbook()
sheet = wb.active
sheet.title = "Diabetes"
sheet.append(header)
for row in rows:
    sheet.append(row)
# Save
wb.save("MLM.xlsx")

# Read entire worksheet from the Excel file
wb = openpyxl.load_workbook("MLM.xlsx", read_only=True)
sheet = wb.active
X = []
y = []
for i, row in enumerate(sheet.rows):
    if i==0:
        continue # skip the header row
    rowdata = [cell.value for cell in row]
    X.append(rowdata[:-1])
    y.append(1 if rowdata[-1]=="tested_positive" else 0)
X, y = np.asarray(X), np.asarray(y)

# create binary classification model
model = Sequential()
model.add(Dense(16, input_dim=8, activation='relu'))
model.add(Dense(8, activation='relu'))
model.add(Dense(1, activation='sigmoid'))
model.compile(loss='binary_crossentropy', optimizer='adam', metrics=['accuracy'])

# train model
history = model.fit(X, y, epochs=5)

通过 Google Sheets 管理数据

除了Excel工作簿之外，有时我们可能会发现Google Sheets处理数据更方便，因为它“在云中”。我们还可以使用类似于Excel的谷歌Sheets来管理数据。但首先，我们需要安装一些模块，然后才能在Python中访问它:

pip install google-api-python-client google-auth-httplib2 google-auth-oauthlib

假设您有一个Gmail帐户，并且创建了一张Google Sheets。你在地址栏上看到的URL，就在/edit部分之前，告诉你工作表的ID，我们稍后会使用这个ID:
在这里插入图片描述
要从Python程序访问此表，最好为你的代码创建一个服务帐户。这是一个机器可操作的帐户，使用密钥进行身份验证，但可由帐户所有者管理。您可以控制此服务帐户可以做什么以及何时到期。你也可以在任何时候撤销服务帐户，因为它与您的Gmail帐户是分开的。

要创建一个服务帐户，首先，您需要进入谷歌开发人员控制台https://console.developers.google.com，并通过单击“创建项目”按钮创建一个项目:
在这里插入图片描述
你需要提供一个项目名，然后你可以点击“创建”:

它将返回到控制台，但你的项目名称将出现在搜索框的旁边。下一步是点击搜索框下方的“Enable APIs and Services”，启用这些api

因为我们要创建一个服务帐户来使用Google Sheets，所以我们在搜索框中搜索“Sheets”:
在这里插入图片描述
然后单击Google Sheets API:

然后启动它

之后，我们将被送回控制台主界面，点击右上角的Create Credentials创建服务帐户:

[图片]

有不同的类型，我们选择“服务帐户”:
【图片】

我们需要提供一个名称(供我们参考)、一个帐户ID(作为项目的唯一标识符)和一个描述。“服务帐户编号”框下方显示的电邮地址是此服务帐户的电邮地址。复制它，稍后我们将把它添加到谷歌Sheet中。当我们创建了所有这些，我们可以跳过其余的，点击“完成”:

【图片】

当我们完成后，我们将被送回主控制台屏幕，如果我们在“service account”部分看到它，我们就知道服务帐户已经创建:
【图片】

接下来，我们需要点击账户右侧的铅笔图标，这将带我们进入以下屏幕:

我们需要为这个帐户创建一个密钥，而不是密码。我们点击顶部的“Keys”页面，然后点击“Add Key”，选择“Create new Key”:

键有两种不同的格式，JSON是首选的一种。选择JSON，点击底部的“创建”，将密钥下载成JSON文件:

JSON文件如下所示:

{
  "type": "service_account",
  "project_id": "mlm-python",
  "private_key_id": "3863a6254774259a1249",
  "private_key": "-----BEGIN PRIVATE KEY-----\n
                  MIIEvgIBADANBgkqh...
                  -----END PRIVATE KEY-----\n",
  "client_email": "ml-access@mlm-python.iam.gserviceaccount.com",
  "client_id": "11542775381574",
  "auth_uri": "https://accounts.google.com/o/oauth2/auth",
  "token_uri": "https://oauth2.googleapis.com/token",
  "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
  "client_x509_cert_url": "https://www.googleapis.com/robot/v1/metadata/x509/ml-access%40mlm-python.iam.gserviceaccount.com"
}

保存JSON文件之后，我们可以返回谷歌工作表，并与我们的服务帐户共享工作表。点击右上角的“分享”按钮，输入服务账号的邮箱地址。你可以跳过通知，点击“分享”。那么我们都准备好了!

现在，我们已经准备好使用Python程序中的服务帐户访问这个特定的谷歌表。要写入到谷歌工作表，可以使用谷歌的API。我们依赖于刚刚为服务帐户下载的JSON文件(mlm-python.json)先创建一个连接:

from oauth2client.service_account import ServiceAccountCredentials
from googleapiclient.discovery import build
from httplib2 import Http

cred_file = "mlm-python.json"
scopes = ['https://www.googleapis.com/auth/spreadsheets']
cred = ServiceAccountCredentials.from_json_keyfile_name(cred_file, scopes)
service = build("sheets", "v4", http=cred.authorize(Http()))
sheet = service.spreadsheets()

如果我们刚刚创建了它，那么该文件中应该只有一个工作表，它的ID为0。所有使用谷歌的API的操作都是JSON格式的。例如，下面是我们如何使用刚刚创建的连接删除整个工作表上的所有内容:

...

sheet_id = '12Pc2_pX3HOSltcRLHtqiq3RSOL9RcG72CZxRqsMeRul'
body = {
    "requests": [{
        "deleteRange": {
            "range": {
                "sheetId": 0
            },
            "shiftDimension": "ROWS"
        }
    }]
}
action = sheet.batchUpdate(spreadsheetId=sheet_id, body=body)
action.execute()

假设我们像上面的第一个例子那样将糖尿病数据集读入DataFrame。然后，我们可以一次性将整个数据集写入谷歌Sheet。为此，我们需要创建一个嵌套的列表，以反映工作表上单元格的2D数组结构，然后将数据放入API查询:

...
rows = [list(dataset.columns)]
rows += dataset.to_numpy().tolist()
maxcol = max(len(row) for row in rows)
maxcol = chr(ord("A") - 1 + maxcol)
action = sheet.values().append(
    spreadsheetId = sheet_id,
    body = {"values": rows},
    valueInputOption = "RAW",
    range = "Sheet1!A1:%s" % maxcol
)
action.execute()

在上面的示例中，我们假设工作表的名称为“Sheet1”(默认名称，您可以在屏幕底部看到)。我们将在左上角对齐写入数据，向前填充单元A1(左上角)。我们使用dataset.to_numpy().tolist()将所有数据收集到一个列表的列表中，但我们还在开头添加列标题作为额外的行。

从谷歌工作表读取回数据的过程与此类似。下面是我们如何读取随机数据行:

...
# Check the sheets
sheet_properties = sheet.get(spreadsheetId=sheet_id).execute()["sheets"]
print(sheet_properties)
# Read it back
maxrow = sheet_properties[0]["properties"]["gridProperties"]["rowCount"]
maxcol = sheet_properties[0]["properties"]["gridProperties"]["columnCount"]
maxcol = chr(ord("A") - 1 + maxcol)
row = random.randint(1, maxrow)
readrange = f"A{row}:{maxcol}{row}"
data = sheet.values().get(spreadsheetId=sheet_id, range=readrange).execute()

先，我们可以通过检查它的属性来知道工作表中有多少行。上面的print()语句将产生以下结果:

[{'properties': {'sheetId': 0, 'title': 'Sheet1', 'index': 0,
'sheetType': 'GRID', 'gridProperties': {'rowCount': 769, 'columnCount': 9}}}]

因为我们只有一个工作表，所以列表只包含一个属性字典。使用这个信息，我们可以选择一个随机的行并指定要读取的范围。上面的变量data将是一个像下面这样的字典，数据将以列表的列表的形式出现，可以使用data[“values”]访问:

{'range': 'Sheet1!A536:I536',
 'majorDimension': 'ROWS',
 'values': [['1',
   '77',
   '56',
   '30',
   '56',
   '33.3',
   '1.251',
   '24',
   'tested_negative']]}

将所有这些联系在一起，以下是加载数据到谷歌工作表并从它读取随机行的完整代码:(确保在运行它时更改sheet_id)

import random

from googleapiclient.discovery import build
from httplib2 import Http
from oauth2client.service_account import ServiceAccountCredentials
from sklearn.datasets import fetch_openml

# Connect to Google Sheet
cred_file = "mlm-python.json"
scopes = ['https://www.googleapis.com/auth/spreadsheets']
cred = ServiceAccountCredentials.from_json_keyfile_name(cred_file, scopes)
service = build("sheets", "v4", http=cred.authorize(Http()))
sheet = service.spreadsheets()

# Google Sheet ID, as granted access to the service account
sheet_id = '12Pc2_pX3HOSltcRLHtqiq3RSOL9RcG72CZxRqsMeRul'

# Delete everything on spreadsheet 0
body = {
    "requests": [{
        "deleteRange": {
            "range": {
                "sheetId": 0
            },
            "shiftDimension": "ROWS"
        }
    }]
}
action = sheet.batchUpdate(spreadsheetId=sheet_id, body=body)
action.execute()

# Read dataset from OpenML
dataset = fetch_openml("diabetes", version=1, as_frame=True, return_X_y=False)["frame"]
rows = [list(dataset.columns)]       # column headers
rows += dataset.to_numpy().tolist()  # rows of data

# Write to spreadsheet 0
maxcol = max(len(row) for row in rows)
maxcol = chr(ord("A") - 1 + maxcol)
action = sheet.values().append(
    spreadsheetId = sheet_id,
    body = {"values": rows},
    valueInputOption = "RAW",
    range = "Sheet1!A1:%s" % maxcol
)
action.execute()

# Check the sheets
sheet_properties = sheet.get(spreadsheetId=sheet_id).execute()["sheets"]
print(sheet_properties)

# Read a random row of data
maxrow = sheet_properties[0]["properties"]["gridProperties"]["rowCount"]
maxcol = sheet_properties[0]["properties"]["gridProperties"]["columnCount"]
maxcol = chr(ord("A") - 1 + maxcol)
row = random.randint(1, maxrow)
readrange = f"A{row}:{maxcol}{row}"
data = sheet.values().get(spreadsheetId=sheet_id, range=readrange).execute()
print(data)

不可否认，以这种方式访问谷歌Sheets过于冗长。因此，我们有一个第三方模块gspread可用来简化操作。在我们安装模块后，我们可以检查电子表格的大小，简单如下:

import gspread

cred_file = "mlm-python.json"
gc = gspread.service_account(filename=cred_file)
sheet = gc.open_by_key(sheet_id)
spreadsheet = sheet.get_worksheet(0)
print(spreadsheet.row_count, spreadsheet.col_count)

要清除工作表，可以将行写入其中，并读取随机行，操作如下:

...
# Clear all data
spreadsheet.clear()
# Write to spreadsheet
spreadsheet.append_rows(rows)
# Read a random row of data
maxcol = chr(ord("A") - 1 + spreadsheet.col_count)
row = random.randint(2, spreadsheet.row_count)
readrange = f"A{row}:{maxcol}{row}"
data = spreadsheet.get(readrange)
print(data)

因此，前面的例子可以简化为以下简短得多的形式:

import random

import gspread
from sklearn.datasets import fetch_openml

# Google Sheet ID, as granted access to the service account
sheet_id = '12Pc2_pX3HOSltcRLHtqiq3RSOL9RcG72CZxRqsMeRul'

# Connect to Google Sheet
cred_file = "mlm-python.json"
gc = gspread.service_account(filename=cred_file)
sheet = gc.open_by_key(sheet_id)
spreadsheet = sheet.get_worksheet(0)

# Clear all data
spreadsheet.clear()

# Read dataset from OpenML
dataset = fetch_openml("diabetes", version=1, as_frame=True, return_X_y=False)["frame"]
rows = [list(dataset.columns)]       # column headers
rows += dataset.to_numpy().tolist()  # rows of data

# Write to spreadsheet
spreadsheet.append_rows(rows)

# Check the number of rows and columns in the spreadsheet
print(spreadsheet.row_count, spreadsheet.col_count)

# Read a random row of data
maxcol = chr(ord("A") - 1 + spreadsheet.col_count)
row = random.randint(2, spreadsheet.row_count)
readrange = f"A{row}:{maxcol}{row}"
data = spreadsheet.get(readrange)
print(data)

与读取Excel类似，使用存储在谷歌Sheet中的数据集，最好一次性读取，而不是在训练循环期间逐行读取。这是因为每次读取时，都发送一个网络请求，并等待来自谷歌服务器的响应。这不会很快发生，因此最好避免。下面是一个我们如何将来自谷歌表的数据与Keras代码进行训练的示例:

import random

import numpy as np
import gspread
from sklearn.datasets import fetch_openml
from tensorflow.keras.models import Sequential
from tensorflow.keras.layers import Dense

# Google Sheet ID, as granted access to the service account
sheet_id = '12Pc2_pX3HOSltcRLHtqiq3RSOL9RcG72CZxRqsMeRul'

# Connect to Google Sheet
cred_file = "mlm-python.json"
gc = gspread.service_account(filename=cred_file)
sheet = gc.open_by_key(sheet_id)
spreadsheet = sheet.get_worksheet(0)

# Clear all data
spreadsheet.clear()

# Read dataset from OpenML
dataset = fetch_openml("diabetes", version=1, as_frame=True, return_X_y=False)["frame"]
rows = [list(dataset.columns)]       # column headers
rows += dataset.to_numpy().tolist()  # rows of data

# Write to spreadsheet
spreadsheet.append_rows(rows)

# Read the entire spreadsheet, except header
maxrow = spreadsheet.row_count
maxcol = chr(ord("A") - 1 + spreadsheet.col_count)
data = spreadsheet.get(f"A2:{maxcol}{maxrow}")
X = [row[:-1] for row in data]
y = [1 if row[-1]=="tested_positive" else 0 for row in data]
X, y = np.asarray(X).astype(float), np.asarray(y)

# create binary classification model
model = Sequential()
model.add(Dense(16, input_dim=8, activation='relu'))
model.add(Dense(8, activation='relu'))
model.add(Dense(1, activation='sigmoid'))
model.compile(loss='binary_crossentropy', optimizer='adam', metrics=['accuracy'])

# train model
history = model.fit(X, y, epochs=5)

数据库的其他用途

面的例子向您展示了如何从电子表格访问数据库。我们假设数据集在训练循环中由机器学习模型存储和使用。虽然这是使用外部数据存储的一种方法，但不是唯一的方法。数据库的其他一些用例是:

为日志的存储，用来记录程序的详细信息，例如，某个脚本在什么时候执行。如果脚本要更改某些内容，例如下载某个文件并覆盖旧版本，这对于跟踪更改特别有用
作为收集数据的工具。就像我们可以使用scikit-learn中的GridSearchCV一样，我们经常可以使用不同的超参数组合来评估模型的性能。如果模型较大且复杂，我们可能希望将评估分发到不同的机器并收集结果。在程序的末尾添加几行代码，将交叉验证结果写入电子表格数据库，这样我们就可以使用所选的超参数对结果进行制表。将这些数据以结构化的格式存储，可以让我们稍后报告我们的结论。
作为配置模型的工具。无需编写超参数组合和验证分数，我们可以将其作为一种工具，为运行程序提供超参数选择。例如，如果我们决定更改参数，可以简单地打开谷歌表进行更改，而不是修改代码。