太强了！1分钟用ChatGPT处理数据分析！

  

数据结构问题会影响数据的完整性、一致性和可读性。为了解决这些问题，本章将介绍如何使用ChatGPT进行数据格式化转换、合并不同数据源的数据、数据采样和分割以及数据过滤。

一、使用ChatGPT进行数据格式化转换

      数据格式化是指将原始数据转换为可用于数据分析的格式。常见的格式包括CSV、Excel、JSON、XML等。在数据清洗中，数据格式化中进行转换常用的操作包括：

• 字符串操作：将字符串转换为小写或大写字母形式、删除多余的空格或字符、提取特定的子字符串等。

• 时间日期格式转换：将不同的时间日期格式转换为统一的格式，如ISO 8601标准格式，或者将时间戳转换为可读的日期时间格式。

• 数值类型转换：将数值型数据转换为不同的数据类型，如整型、浮点型、布尔型等。

• 数据归一化：将数据缩放到特定的范围内，例如将数据缩放到0和1之间。

• 编码转换：将不同的编码格式转换为统一的编码格式，例如将Unicode编码转换为ASCII编码。

• 数据结构转换：将数据从一种数据结构转换为另一种数据结构，例如将JSON格式的数据转换为CSV格式。

     这些操作是数据清洗中常用的数据格式化转换操作，可以帮助将不同格式的数据转换为一致的格式，以便进行后续的数据处理和分析。使用ChatGPT可以将数据格式化为所需的格式，减少手动操作的复杂度和错误率。以下是一个实例：

   假设小明是公司的销售人员，现他手上有一份销售数据，是JSON格式的，如以下所示：

 JSON格式的销售数据

[
  {
    "customer_name": "john doe",
    "customer_id": 123456,
    "shipping_address": "123 main st., anytown, USA",
    "state": "ny",
    "order_date": "2022-04-01T00:00:00",
    "quantity": "2",
    "price": "10.99",
    "total_amount": "21.98"
  },
  {
  "customer_name": "jane smith",
    "customer_id": 654321,
    "shipping_address": "456 elm st., anytown, USA",
    "state": "ca",
    "order_date": "2022-04-02T00:00:00",
    "quantity": "",
    "price": "23.45",
    "total_amount": "46.90"
  },
  {
    "customer_name": "bob jones",
    "customer_id": 789012,
    "shipping_address": "789 oak st., anytown, USA",
    "state": "fl",
    "order_date": "2022-04-03T00:00:00",
    "quantity": "1",
    "price": "",
    "total_amount": "17.99"
  },
  {
    "customer_name": "jimmy choo",
    "customer_id": "",
    "shipping_address": "101 first ave., anytown, USA",
    "state": "tx",
    "order_date": "04/04/22 12:00:00 AM",
    "quantity": "3",
    "price": "12.34",
    "total_amount": ""
  }
]

       现小明需要对这个数据进行处理，以方便整理和分析。他直接通过ChatGPT来格式化这个数据，提示语如2所示：

ChatGPT格式化数据提示语

你是一个资深数据分析师，你具备深厚的数据分析技能和丰富的行业经验。你擅长运用各种数据分析工具和技术，对大量数据进行挖掘、整合、分析和解释。你熟练掌握统计学、机器学习、人工智能等领域的理论和应用，能够从数据中发现有价值的信息和洞见，为企业决策提供有力的支持。此外，你还拥有出色的沟通和演示能力，能够将复杂的数据分析结果以简洁、清晰的方式呈现给各级别的管理者和团队成员，帮助他们做出更明智的决策。
现在我有一份销售数据，是json格式的，如下：
---
[
  {
    "customer_name": "john doe",
    "customer_id": 123456,
    "shipping_address": "123 main st., anytown, USA",
    "state": "ny",
    "order_date": "2022-04-01T00:00:00",
    "quantity": "2",
    "price": "10.99",
    "total_amount": "21.98"
  },
  {
    "customer_name": "jane smith",
    "customer_id": 654321,
    "shipping_address": "456 elm st., anytown, USA",
    "state": "ca",
    "order_date": "2022-04-02T00:00:00",
    "quantity": "",
    "price": "23.45",
    "total_amount": "46.90"
  },
  {
    "customer_name": "bob jones",
    "customer_id": 789012,
    "shipping_address": "789 oak st., anytown, USA",
    "state": "fl",
    "order_date": "2022-04-03T00:00:00",
    "quantity": "1",
    "price": "",
    "total_amount": "17.99"
  },
  {
    "customer_name": "jimmy choo",
    "customer_id": "",
    "shipping_address": "101 first ave., anytown, USA",
    "state": "tx",
    "order_date": "04/04/22 12:00:00 AM",
    "quantity": "3",
    "price": "12.34",
    "total_amount": ""
  }
]
---
帮我把上面的数据转换归一化操作后，存到一个excel文件中。
直接输出你处理好后的表格。

    ChatGPT根据需求，处理后输出的表格如表1所示：

表1  数据格式化转换后的表格

       以上表格是通过对原先的JSON数据进行了数据清洗和归一化操作而得到的，对数据进行了以下操作：将order_date列转换为日期时间格式、将total_amount、price和quantity列转换为数字格式、将state列中的所有字符转换为大写，使用str.title()方法将customer_name和shipping_address列中的所有字符串转换为首字母大写的格式。通过这些操作，清洗和规范化了数据，使其更容易被分析和理解，并且能够更好地支持企业决策。最终，处理后的结果被保存为Excel文件。

       由于ChatGPT支持连续对话的，因此我们可以继续要求ChatGPT生成批处理的代码，提示语如以下所示：

批量格式化数据提示语

若本地文件中有n个同格式的json文件，请你帮我写个Python函数，读取这个文件夹下的所有json，格式化后，整合到excel中。
直接以代码块的格式给我输出代码。

      ChatGPT根据需求，给出的处理代码如以下所示：

ChatGPT生成的格式化批处理代码

import os
import pandas as pd


def json_to_excel(folder_path, output_file):
    """
    读取一个文件夹下所有JSON文件，将它们转换为一个Excel文件。


    Args:
        folder_path (str): JSON文件所在文件夹的路径。
        output_file (str): 转换后的Excel文件路径和文件名。


    Returns:
        无返回值。
    """
    # 创建一个空的DataFrame用于存储所有数据
    all_data = pd.DataFrame()


    # 遍历文件夹下的所有JSON文件
    for filename in os.listdir(folder_path):
        if filename.endswith('.json'):
            # 读取JSON文件
            json_file = os.path.join(folder_path, filename)
            data = pd.read_json(json_file)


            # 数据清洗
            data['order_date'] = pd.to_datetime(data['order_date'], format='%Y-%m-%dT%H:%M:%S')
            data['total_amount'] = pd.to_numeric(data['total_amount'], errors='coerce')
            data['price'] = pd.to_numeric(data['price'], errors='coerce')
            data['quantity'] = pd.to_numeric(data['quantity'], errors='coerce')
            data['state'] = data['state'].str.upper()


            # 数据归一化
            data['customer_name'] = data['customer_name'].str.title()
            data['shipping_address'] = data['shipping_address'].str.title()


            # 将数据添加到总的DataFrame中
            all_data = pd.concat([all_data, data])


    # 将结果保存为Excel文件
    all_data.to_excel(output_file, index=False)


    print('All JSON files in the folder have been converted to an Excel file.')

      这个函数实现了从一个文件夹中读取多个同格式JSON文件并转换成一个Excel文件的功能。具体实现方式为，遍历文件夹下的所有JSON文件，将它们读入为DataFrame格式，然后进行数据清洗和归一化操作，将所有数据整合到一个总的DataFrame中，最后将结果保存为一个Excel文件。

       同时，我们也可以直接通过调用ChatGPT API的格式，快捷格式化数据，代码如下所示：

调用ChatGPT API格式化数据

import os
import json
import pandas as pd
import io
import openai


# 设置 OpenAI API 密钥
openai.api_key = "OpenAIUtils.API_KEY"
def forma_data(data):
    # 设置对话 prompt
    _prompt = f"你是一个资深数据分析师，你具备深厚的数据分析技能和丰富的行业经验。" \
              f"你擅长运用各种数据分析工具和技术，对大量数据进行挖掘、整合、分析和解释。" \
              f"你熟练掌握统计学、机器学习、人工智能等领域的理论和应用，能够从数据中发现有价值的信息和洞见，为企业决策提供有力的支持。" \
              f"此外，你还拥有出色的沟通和演示能力，能够将复杂的数据分析结果以简洁、清晰的方式呈现给各级别的管理者和团队成员，帮助他们做出更明智的决策。\n" \
              f"现在我有一份销售数据，是json格式的，如下\n" \
              f"---\n" \
              f"{data}\n" \
              f"---\n" \
              f"帮我把上面的数据转换归一化操作后，存到一个excel文件中。\n" \
              f"只需要输出处理好的表格，不需要其他内容。"


    # 构建 OpenAI 的请求参数
    _messages = [{"role": "user", "content": _prompt}]
    response = openai.Completion.create(
        model="gpt-3.5-turbo",
        prompt=_prompt,
        temperature=0.5,
        max_tokens=1024,
        top_p=1,
        frequency_penalty=0,
        presence_penalty=0
    )


    # 获取 OpenAI 的响应并返回处理后的结果
    output_text = response[“choices”][0][“message”][“content”]
    return output_text


def read_local_spreadsheet(folder_path):
    # 读取指定文件夹中的所有json文件，将数据合并成一个list
    data = []
    for filename in os.listdir(folder_path):
        if filename.endswith('.json'):
            with open(os.path.join(folder_path, filename), 'r') as f:
                try:
                    file_data = json.load(f)  # 尝试解析JSON数据
                except json.JSONDecodeError:
                    print(f"Skipping {filename}: Invalid JSON format")  # JSON格式错误则跳过该文件
                    continue
                data.extend(file_data)
    return data
def parse_and_save_to_excel(output_string, file_path):
    # 将字符串类型的输出数据解析为DataFrame，按指定分隔符'|'分割
    output_df = pd.read_csv(io.StringIO(output_string), sep='|')
    # 将DataFrame中的数字列转换为数值类型
    output_df = output_df.iloc[:, 1:-1].apply(lambda x: pd.to_numeric(x, errors='ignore'))
    # 将日期列转换为datetime类型，并且只保留日期部分
    output_df['order_date'] = pd.to_datetime(output_df['order_date'], errors='coerce').dt.date
    # 将DataFrame保存为Excel文件
    output_df.to_excel(file_path, index=False)


if __name__ == '__main__':
    # 读取本地的示例表格文件夹
    input_folder = './folder'
    input_table = read_local_spreadsheet(input_folder)


    # 格式化数据并获取处理结果
    output_table = forma_data(input_table)


    print(output_table)


    # 将处理结果保存到本地的 Excel 文件中
    output_file = './output.xlsx'
    parse_and_save_to_excel(output_table, output_file)

      这段代码实现了读取指定文件夹中所有的JSON 文件，将其整合为一个Pandas DataFrame。然后使用 OpenAI API 将 DataFrame 中的数据进行归一化处理，并将处理后的结果写入 Excel 文件中。

      注意：上面代码中“OpenAIUtils.API_KEY”需要用户手动填写自己账户的Key，并且OpenAI会根据用户请求的token数来收费。同时gpt-3.5-turbo模型输入的Prompt的数目限制为4K，若超过该限额，可以采用分批处理，或者直接调用32k限制的gpt4模型。

     总之，使用ChatGPT 进行数据格式化转换可以提高数据处理的效率和准确性，尤其是当需要将大量数据进行归一化处理时。通过使用 OpenAI 的 API 和 ChatGPT 的强大自然语言处理能力，可以在短时间内完成复杂的数据转换和归一化操作，避免了手动处理数据的繁琐和出错风险，提高了数据分析的效率和准确性。

二、使用ChatGPT合并不同数据源的数据

       在数据分析中，合并不同数据源的数据是非常常见的操作。以下是一些常见的合并数据的方法：

•   内连接（Inner Join）：将两个数据源中相同的记录连接起来，即只保留两个数据源中都有的数据。

• 左连接（Left Join）：将左侧数据源的所有记录都保留，并将右侧数据源中与左侧数据源中记录匹配的数据加入结果集中，如果右侧数据源中没有与左侧匹配的数据，则填充为 NULL 值。

•   右连接（Right Join）：将右侧数据源的所有记录都保留，并将左侧数据源中与右侧数据源中记录匹配的数据加入结果集中，如果左侧数据源中没有与右侧匹配的数据，则填充为 NULL 值。

•   全连接（Full Outer Join）：将两个数据源中所有的记录都保留，并将两个数据源中匹配的记录连接在一起，如果没有匹配的记录，则填充为 NULL 值。

•   交叉连接（Cross Join）：将一个数据源的每一条记录与另一个数据源的所有记录都匹配，生成的结果集会是两个数据源中记录的笛卡尔积。

•   自然连接（Natural Join）：在两个数据源中找到相同的列名，然后以这些列名作为连接条件进行连接，相当于执行内连接操作。

•   追加（Append）：将两个数据源中的记录合并在一起，生成的结果集是两个数据源中所有记录的集合。追加通常用于在数据源的底部添加新的记录。

•   堆叠（Stacking）：将两个数据源中的记录沿着垂直方向堆叠在一起，生成的结果集包含所有记录，并将来自两个数据源的记录堆叠在一起。

      这些方法中，内连接、左连接、右连接和全连接是最常见的用于合并数据的方法，它们可以帮助分析人员更好地了解不同数据源之间的关系和数据之间的联系。以下是一个常见的实例：

     假设小李是公司的数据分析员，关于近期的订单，他从多个维度收到了几张表。其中订单表记录了订单的详细信息，包括订单编号、下单时间、订单状态等，如表2所示：

    订单明细表记录了每个订单中的商品明细，包括商品编号、商品名称、单价、数量等，如表3所示：

      商品表记录了所有商品的信息，包括商品编号、商品名称、所属类别等，如表4所示：

      用户表记录了所有用户的信息，包括用户编号、用户名、手机号码等，如表5所示：

     现在需要对这些表进行合并，以便分析订单的销售情况、商品的销售情况、用户购买行为等，因此小李直接利用ChatGPT来整合，提示语如以下所示：

合并数据提示语

你是一个资深数据分析师，你具备深厚的数据分析技能和丰富的行业经验。你擅长运用各种数据分析工具和技术，对大量数据进行挖掘、整合、分析和解释。你熟练掌握统计学、机器学习、人工智能等领域的理论和应用，能够从数据中发现有价值的信息和洞见，为企业决策提供有力的支持。此外，你还拥有出色的沟通和演示能力，能够将复杂的数据分析结果以简洁、清晰的方式呈现给各级别的管理者和团队成员，帮助他们做出更明智的决策。
现在我有N份表格，具体内容如下：
---
订单表（orders）： 记录了订单的详细信息，包括订单编号、下单时间、订单状态等。


订单编号  下单时间  订单状态
10001  2022-01-01 10:00:00  已完成
10002  2022-01-02 11:00:00  已取消
10003  2022-01-03 12:00:00  已完成
10004  2022-01-04 13:00:00  进行中
订单明细表（order_details）： 记录了每个订单中的商品明细，包括商品编号、商品名称、单价、数量等。


订单编号  商品编号  商品名称  单价  数量
10001  001  商品1  100  2
10001  002  商品2  200  1
10002  003  商品3  150  3
10003  001  商品1  100  2
10003  002  商品2  200  1
10004  003  商品3  150  1
商品表（products）： 记录了所有商品的信息，包括商品编号、商品名称、所属类别等。
商品编号  商品名称  商品类别
001  商品1  A类商品
002  商品2  B类商品
003  商品3  A类商品
004  商品4  C类商品
用户表（users）： 记录了所有用户的信息，包括用户编号、用户名、手机号码等。


用户编号  用户名  手机号码
001  张三  13812345678
002  李四  13987654321
003  王五  13788888888
004  赵六  13377777777
---
请从你的专业角度，将我上面的表格合并成一个完整的表格。
直接输出最终的表格。

       根据提供的表格信息，ChatGPT按照订单、订单明细、商品和用户四个维度进行关联，合并成一个完整的表格，如下表所示：

       最终合并后的表格包含了订单的详细信息、订单中的商品明细、商品的类别信息以及下单用户的信息。这个表格可以帮助企业进行订单分析、商品销售分析和用户行为分析等，提供有力的决策支持。

     当数据表格比较多的时候，可以利用ChatGPT支持连续对话的特性，要求ChatGPT生成自动合并的代码，提示语如下所示：

自动合并数据提示语

从你的专业角度，给我一个python代码，和上面的功能类似，需要读取一个文件夹下面的所有Excel表格，然后整合成一个表格，存到新的文件中。
直接以代码块的格式输出。

      ChatGPT根据需求，给出的自动合并代码如以下所示：

                            ChatGPT生成的自动合并代码

import os
import pandas as pd


# 定义要读取的文件夹路径
folder_path = 'path/to/folder'


# 定义要输出的文件路径和文件名
output_file = 'path/to/output/file.xlsx'


# 获取文件夹下所有Excel文件的路径
excel_files = [os.path.join(folder_path, file) for file in os.listdir(folder_path) if file.endswith('.xlsx')]


# 定义一个空的数据框用于存储所有Excel数据
all_data = pd.DataFrame()


# 循环读取每个Excel文件，并将其合并到 all_data 数据框中
for file in excel_files:
    data = pd.read_excel(file)
    all_data = pd.concat([all_data, data], ignore_index=True)


# 将合并后的数据框写入新的Excel文件
all_data.to_excel(output_file, index=False)

      上面的 Python 代码实现了一个读取指定文件夹下所有 Excel 表格并将其合并成一个表格的功能。具体来说，代码使用 pandas 库读取了指定文件夹下所有 Excel 文件的数据，然后将它们合并成一个数据框，最后将合并后的数据框写入到一个新的 Excel 文件中。通过这个功能，我们可以方便地将多个 Excel 表格中的数据整合成一个表格，并用于后续的数据分析和处理。

   当然，我们也可以直接通过调用OpenAI API的方式来合并数据，如下所示：

                       调用ChatGPT API 合并数据

import os
import pandas as pd
import io
import openai


# 定义 OpenAI API 密钥
openai.api_key = "OpenAIUtils.API_KEY"
# 定义要读取的文件夹路径
folder_path = "./data"


# 定义要输出的文件路径和文件名
output_file = "./output/merge_table.xlsx"


def read_tables(folder_path):
    # 获取文件夹下所有Excel文件的路径
    excel_files = [os.path.join(folder_path, file) for file in os.listdir(folder_path) if file.endswith(".xlsx")]


    # 定义一个空列表，用于存储所有Excel表格的字符串形式
    tables = []


    # 循环读取每个Excel表格，并将其转换成字符串格式后加入tables列表中
    for file in excel_files:
        data = pd.read_excel(file)
        table_str = data.to_string(index=False)
        tables.append(table_str)


    # 将所有表格字符串整合成一个大字符串，并返回
    all_tables = "\n".join(tables)
    return all_tables


def write_table(table_str, output_file):
    # 使用 OpenAI GPT-3 API 来合并所有表格
    prompt = f"你是一个资深数据分析师，你具备深厚的数据分析技能和丰富的行业经验。" \
             f"你擅长运用各种数据分析工具和技术，对大量数据进行挖掘、整合、分析和解释。你熟练掌握统计学、机器学习、人工智能等领域的理论和应用，能够从数据中发现有价值的信息和洞见，为企业决策提供有力的支持。" \
             f"此外，你还拥有出色的沟通和演示能力，能够将复杂的数据分析结果以简洁、清晰的方式呈现给各级别的管理者和团队成员，帮助他们做出更明智的决策。\n" \
             f"现在我有N份表格，具体内容如下：\n" \
             f"---\n" \
             f"{table_str}\n" \
             f"---\n" \
             f"请从你的专业角度，将我上面的表格合并成一个完整的表格。直接输出最终的表格。"
    messages = [{"role": "user", "content": prompt}]
    response = openai.ChatCompletion.create(
        model="gpt-3.5-turbo",
        messages=messages,
        temperature=0.5,
        frequency_penalty=0.0,
        presence_penalty=0.0,
        stop=None,
        stream=False)
 table_output = response["choices"][0]["message"]["content"]
    
    # 将 table_output 字符串转换为 DataFrame
    df = pd.read_table(io.StringIO(table_output), sep='\s+')


    # 构造商品信息 DataFrame
    goods_df = df[['商品编号', '商品名称', '商品类别', '单价']].drop_duplicates()


    # 构造用户信息 DataFrame
    users_df = df[['用户编号', '用户名', '手机号码']].drop_duplicates()


    # 将订单信息中的商品编号和用户编号替换为商品信息和用户信息
    df['商品信息'] = df['商品编号'].map(
        goods_df.set_index('商品编号').apply(lambda x: f"{x['商品名称']}({x['商品类别']}) ￥{x['单价']}", axis=1))
    df['用户信息'] = df['用户编号'].map(users_df.set_index('用户编号').apply(lambda x: f"{x['用户名']} {x['手机号码']}", axis=1))


    # 选择需要的列
    df = df[['订单编号', '下单时间', '订单状态', '商品信息', '数量', '用户信息']]


    # 将 DataFrame 存储到 Excel 文件中
    with pd.ExcelWriter('output.xlsx') as writer:
        df.to_excel(writer, sheet_name='订单信息', index=False)
        goods_df.to_excel(writer, sheet_name='商品信息', index=False)
        users_df.to_excel(writer, sheet_name='用户信息', index=False)


if __name__ == "__main__":
    # 读取表格
    table_str = read_tables(folder_path)
    # 调用 OpenAI API 合并表格
    write_table(table_str, output_file)

      以上这段代码实现了一个将多个Excel 表格合并为一个完整表格的过程。具体来说，代码将 Excel 表格读取为字符串格式，然后调用 OpenAI API 来合并所有表格。合并后的表格包括订单信息、商品信息和用户信息，并将其存储到一个 Excel 文件中。在合并过程中，代码使用 Pandas 库来处理和转换数据，并使用 OpenAI GPT-3 API 来生成合并后的表格。

     说明：上面代码中“OpenAIUtils.API_KEY”需要用户手动填写自己账户的Key，并且OpenAI会根据用户请求的token数来收费。

       综上所述，使用ChatGPT可以轻松地合并不同数据源的数据，从而实现更全面、准确的数据分析和预测。ChatGPT是一种基于深度学习的自然语言处理模型，具有强大的语义理解和生成能力，能够对文本数据进行高效、自动化的处理和分析。与传统的数据合并方法相比，使用ChatGPT不仅可以避免手动处理数据的繁琐过程，还可以充分利用多样的数据来源，提高数据处理的效率和准确性。

       本文章出自北京大学出版社《巧用ChatGPT快速搞定数据分析》一书中，经授权此公号，略有修改，经出版纸质书为准。