有诗有田野的城市？？？Python 和 PostgreSQL 告诉你，在哪

向右看齐 2022-08-30 11:53 79阅读 0赞

![在这里插入图片描述][20210624100754583.jpg_pic_center]

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3NjE1MDk4_size_16_color_FFFFFF_t_70]

## 世界很少是完美的，现实生活中的数据也是如此。 ##

创建信息就像任何创作过程一样。它从查找材料（在本例中为数据源）开始，然后继续清理、连接和整理数据集。当你完成时，你已经建立了一些美丽的东西，并获得了新的见解来分享——然后你又重新开始。

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文件以验证代码是否有效。结果应如下所示：

![在这里插入图片描述][20210624101336858.png]  
**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。这为数据发现或公司报告奠定了坚实的基础。

[20210624100754583.jpg_pic_center]: /images/20220829/3d7295dc18134acda89d2cb6a74c8ab9.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3NjE1MDk4_size_16_color_FFFFFF_t_70]: /images/20220829/e981157d290b4bcaafa649db5747809e.png
[20210624101336858.png]: /images/20220829/028cb5eca3544dfca78a4e615d207d68.png