如何基于Pandas表格数据更新sample.txt，实现增量更新而非全量追加？

问题描述 #

现有sample.txt文件包含如下表格数据：

|    | Name              | Price       | 1h     | 24h     | MarketCap   | Volume     |
|---:|:------------------|:------------|:-------|:--------|:------------|:-----------|
| 21 | CRAZY PEPE        | 0.0...01394 | 20.70% | 278.85% | 139,419     | 470,435    |
| 12 | IL CAPO OF CRYPTO | 0.005524    | 20.11% | 191.03% | 5,524,020   | 10,996,033 |
| 14 | SPOODY            | 0.0...05661 | 4.26%  | 102.13% | 237,778     | 577,283    |
| 28 | Mad Bears Club    | 0.0238      | 0.60%  | 100.00% | 24          | --         |
| 29 | Solcasino Token   | 0.002232    | 0.69%  | 89.51%  | 22,320,931  | 2,530,342  |

当前执行以下代码时，会把Pandas生成的完整Markdown表格追加到sample.txt末尾，而非更新原有内容：

Sample = "sample.txt"
open(Sample, 'a').close() #-- 以追加模式打开

import requests 
import pandas as pd
from tabulate import tabulate

url = "https://coinmarketcap.com/new/"
page = requests.get(url,headers={'User-Agent': 'Mozilla/5.0'}, timeout=1)
pagedata = page.text
usecols = ["Name", "Price", "1h", "24h", "MarketCap", "Volume"]

df = pd.read_html(page.text)[0]
df[["Name", "Symbol"]] = df["Name"].str.split(r"\d+", expand=True)
df = (df.rename(columns={"Fully Diluted Market Cap": "MarketCap"})[usecols]
          .sort_values("24h", ascending=False, key=lambda ser: ser.str.replace("%", "").astype(float))
          .replace(r"^\$", "", regex=True)
     )

numcols = df.columns[~df.columns.isin(['Name'])]
df = df.head(5).to_markdown(index=True)
print (df)

with open(Sample, 'a') as f:
    f.write("{}\n".format(df))

需求 #

每次执行代码时，更新sample.txt的内容，而非追加：

• 支持添加新列（比如Blockchain、新增历史1h/24h列）
• 支持更新对应行的数据（比如索引21、28的Name字段）

期望的更新效果示例：

|    | Name              | Price       | 1h     | 24h     | MarketCap   | Volume     | Blockchain   | 1h     | 24h     |
|---:|:------------------|:------------|:-------|:--------|:------------|:-----------|:-------------|:-------|:--------|
| 21 | CRAZY PEPE        | 0.0...01397 | 20.31% | 279.52% | 139,665     | 470,454    | BNB          | 21.45% | 265.10% |
| 12 | IL CAPO OF CRYPTO | 0.005693    | 12.26% | 199.93% | 5,692,970   | 11,009,159 | BNB          | 150.00%| 500.00% |
| 14 | SPOODY            | 0.0...05658 | 5.21%  | 102.00% | 237,618     | 577,283    | Ethereum     | 15.95% | 98.00%  |
| 28 | Mad Bears Club    | 0.0238      | 0.31%  | 100.00% | 24          | --         | Solana       | 4.76%  | 175.87% |
| 29 | Solcasino Token   | 0.002252    | 1.69%  | 91.19%  | 22,519,338  | 2,530,229  | Solana       | 15.97% | 101.45% |

后续执行代码后，更新指定行数据：

|    | Name              | Price       | 1h     | 24h     | MarketCap   | Volume     | Blockchain   | 1h     | 24h     |
|---:|:------------------|:------------|:-------|:--------|:------------|:-----------|:-------------|:-------|:--------|
| 21 | BUTTER            | 0.0...01397 | 10.87% | 165.00% | 5,010       | 100,000    | Ethereum     | 81.00% | 765.10% |
| 12 | IL CAPO OF CRYPTO | 0.005693    | 12.26% | 199.93% | 5,692,970   | 11,009,159 | BNB          | 150.00%| 500.00% |
| 14 | SPOODY            | 0.0...05658 | 5.21%  | 102.00% | 237,618     | 577,283    | Ethereum     | 15.95% | 98.00%  |
| 28 | GUCCI             | 0.0238      | 0.31%  | 100.00% | 24          | --         | Solana       | 4.76%  | 175.87% |
| 29 | Solcasino Token   | 0.002252    | 1.69%  | 91.19%  | 22,519,338  | 2,530,229  | Solana       | 15.97% | 101.45% |

解决方案 #

要实现文件内容的更新而非追加，核心思路是：读取原有表格转为DataFrame，合并新爬取的数据，最后覆盖写入文件。修改后的代码如下：

import requests 
import pandas as pd
from tabulate import tabulate

Sample = "sample.txt"
url = "https://coinmarketcap.com/new/"
usecols = ["Name", "Price", "1h", "24h", "MarketCap", "Volume"]

# 1. 爬取并处理新数据
page = requests.get(url, headers={'User-Agent': 'Mozilla/5.0'}, timeout=1)
df_new = pd.read_html(page.text)[0]
df_new[["Name", "Symbol"]] = df_new["Name"].str.split(r"\d+", expand=True)
df_new = (df_new.rename(columns={"Fully Diluted Market Cap": "MarketCap"})[usecols]
           .sort_values("24h", ascending=False, key=lambda ser: ser.str.replace("%", "").astype(float))
           .replace(r"^\$", "", regex=True)
           .head(5)
         )

# 2. 读取已有文件中的表格（如果存在）
try:
    with open(Sample, 'r') as f:
        existing_content = f.read()
    # 解析现有Markdown表格为DataFrame
    df_existing = pd.read_csv(pd.io.common.StringIO(existing_content), sep='|', skipinitialspace=True)
    # 清理Markdown格式带来的冗余列和行
    df_existing = df_existing.drop(df_existing.columns[[0, -1]], axis=1).rename(columns=lambda x: x.strip())
    df_existing = df_existing.drop(df_existing.index[1]).reset_index(drop=True)
    df_existing = df_existing.rename(columns={'': 'index'}).set_index('index')
    # 合并新旧数据：新数据覆盖旧数据对应字段，保留原有新增列
    df_combined = df_existing.combine_first(df_new)
    # 保持和新数据一致的行顺序
    df_combined = df_combined.loc[df_new.index]
except FileNotFoundError:
    # 首次运行文件不存在时，直接使用新数据
    df_combined = df_new

# 3. 转为Markdown并覆盖写入文件
markdown_table = df_combined.to_markdown(index=True)
with open(Sample, 'w') as f:
    f.write(markdown_table)

print(markdown_table)

代码说明 #

• 解析现有表格：通过pd.read_csv处理Markdown表格，清理分隔符带来的冗余内容，转为标准DataFrame
• 合并逻辑：使用combine_first实现新数据覆盖旧数据，同时保留原有文件中手动添加的列（如Blockchain）
• 覆盖写入：采用'w'模式打开文件，直接替换原有内容，避免追加
• 兼容首次运行：如果文件不存在，自动写入新爬取的表格

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