如何对Swap交易数据执行复杂行转换操作

问题描述 #

现有Swap交易数据，一个transaction_hash对应多条transfer记录。其中合约发起方为data = trace时的from_address（即向0xbebc44782c7db0a1a60cb6fe97d0b483032ff1c7资金池发送0值的地址）。需要筛选有效信息，将每个交易哈希对应的3行数据合并为1行，最终包含字段：transaction_hash、发送方（from_address）、block_timestamp、value、data、token_address以及swapfor（发送方最终收到的代币）。

原始数据 #

transaction_hash    block_timestamp from_address    to_address  value   data    token_address
10594   0x00016a6fcc4be913b2ba4e33a015f1cb876d22f59d3cbd18cbceb39415d90fe9  2021-10-14 11:28:18 UTC 0x510f3c959ab647681acde24c09b39e252b799dcb  0xbebc44782c7db0a1a60cb6fe97d0b483032ff1c7  0.0 trace   
1302862 0x00016a6fcc4be913b2ba4e33a015f1cb876d22f59d3cbd18cbceb39415d90fe9  2021-10-14 11:28:18 UTC 0x510f3c959ab647681acde24c09b39e252b799dcb  0xbebc44782c7db0a1a60cb6fe97d0b483032ff1c7  158173.620844   transfer    USDC
2094180 0x00016a6fcc4be913b2ba4e33a015f1cb876d22f59d3cbd18cbceb39415d90fe9  2021-10-14 11:28:18 UTC 0xbebc44782c7db0a1a60cb6fe97d0b483032ff1c7  0x510f3c959ab647681acde24c09b39e252b799dcb  158116.695638   transfer    USDT
120546  0x0001e31fe253f8755a9f67174980221f361b341a64206c44c90e8a08249218a5  2020-11-22 20:20:23 UTC 0xf21ae6c185103b349f57ffb90da58399d30d095f  0xbebc44782c7db0a1a60cb6fe97d0b483032ff1c7  0.0 trace   
277556  0x0001e31fe253f8755a9f67174980221f361b341a64206c44c90e8a08249218a5  2020-11-22 20:20:23 UTC 0xf21ae6c185103b349f57ffb90da58399d30d095f  0xbebc44782c7db0a1a60cb6fe97d0b483032ff1c7  3400.0  transfer    DAI
1521560 0x0001e31fe253f8755a9f67174980221f361b341a64206c44c90e8a08249218a5  2020-11-22 20:20:23 UTC 0xbebc44782c7db0a1a60cb6fe97d0b483032ff1c7  0xf21ae6c185103b349f57ffb90da58399d30d095f  3409.370414 transfer    USDC
208703  0x000499e9074acc95aa75d43b49119a0260d6a4d116772df7f561b8bd6b6e36d8  2021-04-17 22:33:33 UTC 0xa55e8f346a2e48045f0418aae297aae166f614ce  0xbebc44782c7db0a1a60cb6fe97d0b483032ff1c7  0.0 trace   
295972  0x000499e9074acc95aa75d43b49119a0260d6a4d116772df7f561b8bd6b6e36d8  2021-04-17 22:33:33 UTC 0xbebc44782c7db0a1a60cb6fe97d0b483032ff1c7  0xa55e8f346a2e48045f0418aae297aae166f614ce  5001.212023640602   transfer    DAI
1360911 0x000499e9074acc95aa75d43b49119a0260d6a4d116772df7f561b8bd6b6e36d8  2021-04-17 22:33:33 UTC 0xa55e8f346a2e48045f0418aae297aae166f614ce  0xbebc44782c7db0a1a60cb6fe97d0b483032ff1c7  5006.0  transfer    USDC
41895   0x0004f6bd95e6d19c6b9c6466055d7d12572f66af007852d59e6e976cdec514ce  2021-06-28 13:56:23 UTC 0xf780db98028aec31a718a25151c98e7e9a5546ba  0xbebc44782c7db0a1a60cb6fe97d0b483032ff1c7  0.0 trace   
1294707 0x0004f6bd95e6d19c6b9c6466055d7d12572f66af007852d59e6e976cdec514ce  2021-06-28 13:56:23 UTC 0xbebc44782c7db0a1a60cb6fe97d0b483032ff1c7  0xf780db98028aec31a718a25151c98e7e9a5546ba  31455.736067    transfer    USDC
2088382 0x0004f6bd95e6d19c6b9c6466055d7d12572f66af007852d59e6e976cdec514ce  2021-06-28 13:56:23 UTC 0xf780db98028aec31a718a25151c98e7e9a5546ba  0xbebc44782c7db0a1a60cb6fe97d0b483032ff1c7  31470.496551    transfer    USDT
26064   0x0005872abe5b6c9e24505f047f638670f09df43173fc6679901eaf330c3cdc58  2021-01-29 17:07:26 UTC 0x0d080a3c3290c98e755d8123908498bce2c5620d  0xbebc44782c7db0a1a60cb6fe97d0b483032ff1c7  0.0 trace   
1720288 0x0005872abe5b6c9e24505f047f638670f09df43173fc6679901eaf330c3cdc58  2021-01-29 17:07:26 UTC 0xbebc44782c7db0a1a60cb6fe97d0b483032ff1c7  0x0d080a3c3290c98e755d8123908498bce2c5620d  662073.333519   transfer    USDC
1994556 0x0005872abe5b6c9e24505f047f638670f09df43173fc6679901eaf330c3cdc58  2021-01-29 17:07:26 UTC 0x0d080a3c3290c98e755d8123908498bce2c5620d  0xbebc44782c7db0a1a60cb6fe97d0b483032ff1c7  660759.862846   transfer    USDT

期望输出 #

transaction_hash    block_timestamp from_address  value   data    token_address swapfor
0x00016a6fcc4be913b2ba4e33a015f1cb876d22f59d3cbd18cbceb39415d90fe9  2021-10-14 11:28:18 UTC 0x510f3c959ab647681acde24c09b39e252b799dcb  0.0 trace USDC USDT
0x0001e31fe253f8755a9f67174980221f361b341a64206c44c90e8a08249218a5  2020-11-22 20:20:23 UTC 0xf21ae6c185103b349f57ffb90da58399d30d095f  0.0 trace DAI USDC
0x000499e9074acc95aa75d43b49119a0260d6a4d116772df7f561b8bd6b6e36d8  2021-04-17 22:33:33 UTC 0xa55e8f346a2e48045f0418aae297aae166f614ce  0.0 trace USDC DAI
0x0004f6bd95e6d19c6b9c6466055d7d12572f66af007852d59e6e976cdec514ce  2021-06-28 13:56:23 UTC 0xf780db98028aec31a718a25151c98e7e9a5546ba  0.0 trace USDT USDC
0x0005872abe5b6c9e24505f047f638670f09df43173fc6679901eaf330c3cdc58  2021-01-29 17:07:26 UTC 0x0d080a3c3290c98e755d8123908498bce2c5620d  0.0 trace USDT USDC

实现代码 #

通过遍历原始数据，按交易哈希分组整合信息，最终生成目标格式的DataFrame：

def transform_data(data):
    output = []
    tx_dict = {}

    for _, row in data.iterrows():
        transaction_hash = row["transaction_hash"]

        # 初始化当前交易哈希的存储字典
        if transaction_hash not in tx_dict:
            tx_dict[transaction_hash] = {
                "transaction_hash": transaction_hash,
                "from_address": None,
                "block_timestamp": None,
                "token_address": None,
                "value": None,
                "swapfor": None
            }

        # 提取trace行的发起方和时间戳信息
        if row["data"] == "trace":
            tx_dict[transaction_hash]["from_address"] = row["from_address"]
            tx_dict[transaction_hash]["block_timestamp"] = row["block_timestamp"]
        # 提取transfer行的代币信息
        elif row["data"] == "transfer":
            # 转入资金池的是用户卖出的代币
            if row["to_address"] == "0xbebc44782c7db0a1a60cb6fe97d0b483032ff1c7":
                tx_dict[transaction_hash]["token_address"] = row["token_address"]
                tx_dict[transaction_hash]["value"] = row["value"]
            # 从资金池转出的是用户买入的代币（swapfor）
            else:
                tx_dict[transaction_hash]["swapfor"] = row["token_address"]

        # 当当前交易的所有字段都填充完成后，加入输出列表并移除临时存储
        if all(tx_dict[transaction_hash][k] is not None for k in tx_dict[transaction_hash]):
            output.append(tx_dict[transaction_hash])
            tx_dict.pop(transaction_hash)

    return output

# 调用函数转换数据并生成DataFrame
df_transformed = transform_data(df)
df_transformed = pd.DataFrame(df_transformed)

内容的提问来源于stack exchange，提问作者