<jupyter_start><jupyter_text>ETL Pipeline 按照下面的说明，创建你的 ETL 管道。 1. 导入与加载.- 导入 Python 库- 将 `messages.csv` 导入数据框，查看前几行- 将 `categories.csv` 导入数据框，查看前几行<jupyter_code># import libraries
import pandas as pd
from sqlalchemy import Column, String, create_engine
import time
# 测试下计时器的写法
# start = time.time()
# print('Training model...')
# messages = pd.read_csv('disaster_response_pipeline_project/data/messages.csv')
# train_time = time.time() - start
# print("Total time: {:.0f}m {:.0f}s".format(train_time//60, train_time % 60))
# load messages dataset
messages = pd.read_csv('messages.csv')
messages.head()
messages.shape
# load categories dataset
categories = pd.read_csv('categories.csv')
categories.head()
categories.shape
len(messages['id'].unique().tolist())<jupyter_output><empty_output><jupyter_text>2. 合并数据集- 使用相同的 id 合并 messages 和 categories 数据集- 将合并后的数据集传入 `df`，在后续的步骤中要清洗该数据集。<jupyter_code># merge datasets
df = messages.merge(categories, on = 'id', how = 'inner')
df.head()
df.shape<jupyter_output><empty_output><jupyter_text>3. 分割 `categories` - 将 `categories` 列的值根据字符 `;` 进行分割，每个值是一个新列。你会发现 [这个方法](https://pandas.pydata.org/pandas-docs/version/0.23/generated/pandas.Series.str.split.html) 非常有用。确保设置 `expand=True`。- 使用 categories 数据框的第一行来创建类别数据的列名。- 使用新的列名重命名 `categories` 的列。<jupyter_code># create a dataframe of the 36 individual category columns
categories = df['categories'].str.split(';', expand = True)
categories.head()
categories.shape
# select the first row of the categories dataframe
row = categories.loc[0,:]

# use this row to extract a list of new column names for categories.
# one way is to apply a lambda function that takes everything 
# up to the second to last character of each string with slicing
category_colnames = row.apply(lambda x : x.split("-")[0]).tolist()
print(category_colnames)
# rename the columns of `categories`
categories.columns = category_colnames
categories.head()
categories.shape<jupyter_output><empty_output><jupyter_text>4. 转换类别值至数值 0 或 1- 遍历 df 的类别列，只保留每个字符串的最后一个字符（1 或 0）。例如，`related-0` 变成 `0`，`related-1` 变成 `1`。将字符串转换成数值。- 通过在 Series 后加上`.str`，你可以实现 [Pandas Series 上的常见字符串操作](https://pandas.pydata.org/pandas-docs/stable/text.htmlindexing-with-str)，如索引。你可能需要先将 Series 转换成类型字符串，你可以通过 `astype(str)` 实现。<jupyter_code>for column in categories:
    # set each value to be the last character of the string
    categories[column] = categories[column].str.split("-").str.get(1)
    
    # convert column from string to numeric
    categories[column] = pd.to_numeric(categories[column], errors='coerce')
categories.head()<jupyter_output><empty_output><jupyter_text>5. 替换 `df` `categories` 类别列- 从 df 数据框中删除类别列，因为不再需要类别列了。- 将 df 和 categories 数据框连接起来。<jupyter_code># drop the original categories column from `df`
df = df.drop(['categories'], axis=1)

df.head()
# concatenate the original dataframe with the new `categories` dataframe
df = pd.concat([df, categories], axis=1, join_axes=[df.index])
df.head()
df.shape<jupyter_output><empty_output><jupyter_text>6. 删除重复行- 检查这个数据集中有多少重复行。- 删除重复行。- 确认重复行被删除了。<jupyter_code>len(df['id'].unique().tolist())
# check number of duplicates
df.duplicated('id').sum()
# drop duplicates
df = df.drop_duplicates('id')
# check number of duplicates
df.duplicated('id').sum()<jupyter_output><empty_output><jupyter_text>7. 保存整理后的数据集为 sqlite databse 你可以使用 pandas 的 [`to_sql` 方法](https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.to_sql.html) 和SQLAlchemy 库实现这一点。记得在 notebook 的第一个单元格导入 SQLAlchemy 的 `create_engine` 以在下方使用该库。<jupyter_code>engine = create_engine('sqlite:///DisasterResponse.db')
df.to_sql('messages_categories', engine, index=False, if_exists = 'replace')
engine.execute("SELECT * FROM messages_categories limit 10").fetchall()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.13674986723313862, 'fraction_numerical': 0.008762612851832182, 'mean_word_length': 6.429980276134122, 'pattern_counts': {'":': 0, '<': 29, '<?xml version=': 0, '>': 29, 'https://': 3, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '7', 'dir': 'jupyter-structured-clean-dedup', 'id': '372659', 'license': 'FTL', 'n_tokens_mistral': 1563, 'n_tokens_neox': 1601, 'n_words': 304, 'path': 'ETL Pipeline Preparation-zh.ipynb', 'repo_name': 'VickieL/DSND_Disaster_Response_Pipelines', 'size': '3766'}