世界很少是完美的，现实生活中的数据也是如此。
创建信息就像任何创作过程一样。它从查找材料（在本例中为数据源）开始，然后继续清理、连接和整理数据集。当你完成时，你已经建立了一些美丽的东西，并获得了新的见解来分享——然后你又重新开始。
Python 是信息创建中使用最广泛的语言之一，它受到数据科学家、工程师和分析师的喜爱，因为它具有用于数据整理的现有库的强大生态系统。
这篇博文探讨了三个这样的库：pandas,ddlgenerator和psycopg2. 这三个使我们能够清理数据集并将其推送到 PostgreSQL 数据库，稍后可以在该数据库中查询数据并将其暴露给各种各样的公司数据。
今天，目标很简单：我们将探索一个幸福数据集，并尝试找出我们应该搬到世界上的哪个地方才能过上幸福的生活！
0. 获取数据集
如前所述，首先要查找数据集。Kaggle是一个广泛用于数据科学社区的网站，提供用于挑战、竞赛或学习的数据集。他们有这个关于世界幸福的很好的数据集，只需要下载一个登录名。它包含 5 个 CSV 文件，每年一个，列出了各个国家的幸福排名以及其他一些指标。
让我们创建一个名为的文件夹happiness和一个名为data存储文件文件的子文件夹：
mkdir -p happiness/data
cd happiness
接下来，从Kaggle下载数据并将 5 个 CSV 放入happiness/data文件夹中。
1.清理数据
由于它经常发生，源数据集并不总是整洁干净。这也适用于我们的幸福数据集。
在浏览文件时，我们可以注意到2015.csv并2016.csv共享相同的列名和数据格式。在2017.csv有Region柱缺失，一些人命名和排序不同（Generosity和Trust被交换）。这同样适用于文件2018.csv以及2019.csv我们无法再找到置信区间的地方。此外，年份仅包含在文件名中，而不包含在列中。
在开始我们的清理工作之前，每次我们开始一个新的 Python 项目时创建一个单独的虚拟环境是一个很好的做法。这可确保我们的默认 Python 环境不会因可能存在冲突的库而过载。我们可以创建一个虚拟环境并像这样启用它：
python -m venv python_pg
source python_pg/bin/activate
这些文件需要做一些工作才能产生一致的数据视图，所以让我们戴上我们的 Python 超级英雄面具。首先安装pandas库，这将帮助我们进行数据操作和分析，方法是在终端中执行以下代码：
pip install pandas
现在我们可以prepare.py在我们的happiness文件夹中创建一个名为的文件，并开始在其中包含一些 Python 代码。我们将首先导入之前安装的库，以及默认情况下应该安装的库，os并glob分别启用与操作系统的交互和路径名模式扩展。
import pandas as pd
import glob, os
接下来，定义我们将在目标 DataFrame 中使用的列名称，all_years_df用于存储清理后的数据集。将以下行添加到prepare.py文件中：
column_names = ['survey_yr', 'country', 'overall_rank',
'score', 'gdp', 'generosity', 'freedom',
'social_support', 'life_exp', 'gov_trust']
all_years_df = pd.DataFrame()
现在是时候让混乱恢复秩序并解析文件data夹中的所有 CSV 文件了。将以下代码添加到prepare.py文件中：
files = glob.glob('data/*.csv')
for fp in files:
# Handling file names as new column
file_year = os.path.basename(fp).split('.')[0]
file_content = pd.read_csv(fp).assign(Year=int(file_year)-2000)
# Picking the right column for each file based on the year
uniformed_columns = find_columns(file_content, file_year)
# Conforming column names and appending the parsed year data to all_years_df
uniformed_columns.columns = column_names
all_years_df = pd.concat([all_years_df, uniformed_columns])
all_years_df.to_csv('results.csv', index=False)
该glob函数返回文件happiness/data夹中的文件列表，我们循环遍历。对于每个文件，我们使用 读取文件os.path.basename名，在点 ( .)上拆分名称并取第一部分，仅提取年份信息。该file_content行使用 pandas 的read_csv函数加载文件数据并Year使用assign年份的最后两位数字 ( int(file_year)-2000)填充新列（使用该函数）。
接下来，我们调用find_columns即将定义的函数，根据年份从源文件中选择正确的列。
之后，我们合并uniformed_columns仅包含一年数据的DataFrame 中的列名称，然后all_years_df在流程结束时将其附加到将包含整个数据集的DataFrame。最后，我们将最终结果存储在文件夹中命名的 CSV 文件results.csv中happiness。
选择正确的列
缺少的部分是find_columns我们合并列名和顺序的函数定义。如上所述，根据年份，我们需要从源文件中选择正确的列。将以下代码添加到prepare.py文件中，就在import语句之后，以在不同年份执行该技巧：
def find_columns(file_content, file_year):
if file_year in ['2015', '2016']:
uniformed_columns = file_content[[
'Year', 'Country', 'Happiness Rank', 'Happiness Score',
'Economy (GDP per Capita)', 'Generosity', 'Freedom', 'Family',
'Health (Life Expectancy)', 'Trust (Government Corruption)'
]]
elif file_year in ['2017']:
uniformed_columns = file_content[[
'Year', 'Country', 'Happiness.Rank', 'Happiness.Score',
'Economy..GDP.per.Capita.', 'Generosity', 'Freedom', 'Family',
'Health..Life.Expectancy.', 'Trust..Government.Corruption.'
]]
else:
uniformed_columns = file_content[[
'Year', 'Country or region', 'Overall rank', 'Score',
'GDP per capita', 'Generosity', 'Freedom to make life choices',
'Social support', 'Healthy life expectancy',
'Perceptions of corruption'
]]
return uniformed_columns
该函数为每一年选择正确的列并返回结果数据帧。
现在是检查管道是否正常工作的时候了。终端位于happiness文件夹中，让我们执行以下操作：
python prepare.py
现在检查文件夹results.csv中的happiness文件以验证代码是否有效。结果应如下所示：
2.创建一个PostgreSQL实例
第二步是将我们的数据存储在 PostgreSQL 表中。但是在存储之前，我们需要一个 PG 数据库，让我们在终端中使用Aiven CLI创建一个
avn service create demo-pg \
-t pg \
--cloud google-europe-west3 \
-p hobbyist
上面的命令使用计划创建了一个名为demo-pgon的 PostgreSQL 实例。您可以在我们的定价页面查看所有 PostgreSQL 计划。连接到它，我们需要的像包含的信息，以及默认的用户。我们可以通过以下方式检索它：google-europe-west3hobbyistservice urihostportavnadminpassword
avn service get demo-pg --format '{service_uri}'
现在放松几分钟，同时在终端中等待实例准备好以下调用
avn service wait demo-pg
3.推送数据到PostgreSQL
服务运行后，返回 Python 并准备数据推送。
我们将使用ddlgenerator库自动生成 SQL 语句来创建 PostgreSQL 表，并使用psycopg2与数据库进行交互。我们可以在终端中使用以下行安装它们：
pip install psycopg2 ddlgenerator
现在创建一个新push.py文件并添加导入和连接到数据库语句。将以下代码中的<SERVICE_URI_OUTPUT>参数替换为上述avn service get命令的输出：
import psycopg2
conn = psycopg2.connect('<SERVICE_URI_OUTPUT>')
连接已建立。我们现在可以创建表并推送数据。ddlgenerator提供了一个很好的功能来从 CSV 文件创建表 DDL 和插入语句。我们可以将以下代码附加到push.py文件中：
from ddlgenerator.ddlgenerator import Table
table = Table('results.csv', table_name='HAPPINESS')
sql = table.sql('postgresql', inserts=True)
从 导入Table类后ddlgenerator，代码创建一个解析results.csv文件的实例，目标表名为HAPPINESS. 最后一行为CREATEPostgreSQL创建表定义 SQL，包括每一行的插入语句 ( inserts=True)。如果我们打印 SQL 语句，它应该如下所示：
DROP TABLE IF EXISTS happiness;
CREATE TABLE happiness (
survey_yr INTEGER NOT NULL,
country VARCHAR(24) NOT NULL,
overall_rank INTEGER NOT NULL,
score DECIMAL(15, 14) NOT NULL,
gdp DECIMAL(17, 16) NOT NULL,
generosity DECIMAL(16, 16) NOT NULL,
freedom DECIMAL(16, 16) NOT NULL,
social_support DECIMAL(16, 15) NOT NULL,
life_exp DECIMAL(17, 16) NOT NULL,
gov_trust DECIMAL(16, 16)
);
INSERT INTO happiness (survey_yr, country, overall_rank, score, gdp, generosity, freedom, social_support, life_exp, gov_trust) VALUES (19, 'Finland', 1, 7.769, 1.34, 0.153, 0.596, 1.587, 0.986, 0.393);
INSERT INTO happiness (survey_yr, country, overall_rank, score, gdp, generosity, freedom, social_support, life_exp, gov_trust) VALUES (19, 'Denmark', 2, 7.6, 1.383, 0.252, 0.592, 1.573, 0.996, 0.41);
...
我们现在可以通过将以下代码添加到push.py文件中来对数据库执行 sql 语句
cur = conn.cursor()
cur.execute(sql)
conn.commit()
cur.close()
conn.close()
我们创建了一个游标，执行了 sql 语句，将更改提交到数据库并关闭了游标和连接。现在是运行代码的时候了。使用happiness文件夹上的相同终端，运行以下命令：
python push.py
恭喜，数据现在已加载到 PostgreSQL 中。
查询PostgreSQL中的数据
让我们验证数据是否正确填充到 PostgreSQLhappiness表中。创建一个新的 Python 文件query.py，内容如下（和之前一样，修改连接service uri）：
import psycopg2
conn = psycopg2.connect('<SERVICE_URI_OUTPUT>')
cur = conn.cursor()
cur.execute('''
SELECT SURVEY_YR, COUNTRY, OVERALL_RANK
from HAPPINESS
WHERE OVERALL_RANK <= 3
ORDER BY SURVEY_YR, OVERALL_RANK
''')
print(cur.fetchall())
cur.close()
conn.close()
根据push.py文件，我们正在创建与 PostgreSQL 的连接，然后执行查询以检查哪些国家/地区在不同年份中排名前三。结果应该类似于：
[
(15, 'Switzerland', 1), (15, 'Iceland', 2), (15, 'Denmark', 3),
(16, 'Denmark', 1), (16, 'Switzerland', 2), (16, 'Iceland', 3),
(17, 'Norway', 1), (17, 'Denmark', 2), (17, 'Iceland', 3),
(18, 'Finland', 1), (18, 'Norway', 2), (18, 'Denmark', 3),
(19, 'Finland', 1), (19, 'Denmark', 2), (19, 'Norway', 3)
]
看来北欧国家很适合居住！请特别注意艾文的祖国Finland在过去两年中的表现如何。
这些数据现在可以在 PostgreSQL 关系表中使用，所有主要的分析和报告工具都可以查询和公开，从而让更广泛的受众可以访问这些数据。
Wrapping up
找到正确的数据集只是一个开始：清理、整理和发布数据会创建信息，帮助人们做出决策。Python 非常适合这一领域，并被数据社区广泛采用。博客中提到的pandas,ddlgenerator和psycopg2库可以轻松处理数据并将其推送到 PostgreSQL。这为数据发现或公司报告奠定了坚实的基础。
