Excel中如何生成两列唯一值之间的0/1映射交叉表

下面提供三种不同场景下的实现方案：

1. R语言实现（适合生物信息分析场景） #

你可以直接使用tidyverse套件的宽表转换功能完成需求，代码如下：

# 加载依赖包
library(tidyverse)
# 读入原始制表符分隔的表格
raw_df <- read.delim("你的原始文件路径.txt", sep = "\t", header = TRUE)
# 转换为0/1映射表
onehot_df <- raw_df %>%
  # 新增标记列，代表对应行的Ortho和Path存在关联
  mutate(associate = 1) %>%
  # 转换为宽表，列名取自Path的唯一值，无关联的位置填充0
  pivot_wider(
    names_from = Path,
    values_from = associate,
    values_fill = list(associate = 0)
  )
# 导出结果为制表符分隔文件
write.table(onehot_df, "输出文件路径.txt", sep = "\t", row.names = FALSE, quote = FALSE)

2. Python实现（适合通用数据处理场景） #

使用pandas的透视表功能即可快速生成结果：

import pandas as pd
# 读入原始数据
raw_df = pd.read_csv("你的原始文件路径.txt", sep="\t")
# 新增关联标记
raw_df["flag"] = 1
# 生成0/1映射透视表
onehot_df = pd.pivot_table(
    raw_df,
    index="Ortho",
    columns="Path",
    values="flag",
    fill_value=0
).reset_index()
# 导出结果
onehot_df.to_csv("输出文件路径.txt", sep="\t", index=False)

3. Linux命令行实现（适合大文件快速处理） #

不需要打开任何数据分析软件，直接用awk命令即可完成计算，适合超大规模的注释文件处理：

awk '
BEGIN{FS=OFS="\t"}
# 跳过表头行
NR==1{next}
{
    # 分别存储Ortho、Path的唯一值，以及两者的关联映射
    ortho_set[$1] = 1
    path_set[$2] = 1
    assoc_map[$1,$2] = 1
}
END{
    # 打印表头
    printf "Ortho"
    for (p in path_set) printf OFS p
    print ""
    # 逐行打印每个Ortho的关联结果
    for (o in ortho_set) {
        printf o
        for (p in path_set) printf OFS (assoc_map[o,p] ? 1 : 0)
        print ""
    }
}' 你的原始文件.txt > 输出结果.txt

注意：你提供的示例输出中存在重复的行名（比如重复出现L20795），属于示例错误，上述方法输出的结果每个唯一Ortho只会出现一行，符合统计逻辑。

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