机器学习中的数据分析和管理

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

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

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

  • 使用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”:

[图片]

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

我们需要提供一个名称(供我们参考)、一个帐户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一样,我们经常可以使用不同的超参数组合来评估模型的性能。如果模型较大且复杂,我们可能希望将评估分发到不同的机器并收集结果。在程序的末尾添加几行代码,将交叉验证结果写入电子表格数据库,这样我们就可以使用所选的超参数对结果进行制表。将这些数据以结构化的格式存储,可以让我们稍后报告我们的结论。
  • 作为配置模型的工具。无需编写超参数组合和验证分数,我们可以将其作为一种工具,为运行程序提供超参数选择。例如,如果我们决定更改参数,可以简单地打开谷歌表进行更改,而不是修改代码。

总结

本博客主要介绍了如何使用外部数据存储,包括数据库或电子表格。
具体来说,包括:

  • 如何使用SQL语句使Python程序访问关系数据库,如SQLite
  • 如何使用dbm作为键值存储,并像使用Python字典一样使用它
  • 如何从Excel文件读取和写入
  • 如何通过Internet访问谷歌表
  • 我们如何使用所有这些来托管数据集并在机器学习项目中使用它们

参考资料

以下是一些帮助你深入了解的资源:
Books:

api和库

文章

文章出处登录后可见!

已经登录?立即刷新

共计人评分,平均

到目前为止还没有投票!成为第一位评论此文章。

(0)
扎眼的阳光的头像扎眼的阳光普通用户
上一篇 2022年5月24日
下一篇 2022年5月24日

相关推荐