如何统计两列（start、end）的值并生成指定格式统计结果

现有两列数据（START、END），二者的取值集合完全相同，需要对这两列的值进行统计，生成包含以下字段的结果：

• TIME：两列中去重后的所有值
• COUNT1：对应值在START列中的出现次数
• COUNT2：对应值在END列中的出现次数

示例输入 #

START        END
----------------------
afternoon    evening
evening      night
evening      night
afternoon    evening
night        morning
night        morning

期望输出 #

TIME         COUNT1      COUNT2
-------------------------------
morning      0             2
afternoon    2             0
evening      2             2
night        2             2

方法一：SQL实现 #

通过生成去重时间集合，再分别关联两列的统计结果，即可得到目标输出：

WITH all_times AS (
    -- 获取所有去重的时间值
    SELECT START AS time FROM your_table
    UNION
    SELECT END AS time FROM your_table
),
count_start AS (
    -- 统计START列各值的出现次数
    SELECT START AS time, COUNT(*) AS count1
    FROM your_table
    GROUP BY START
),
count_end AS (
    -- 统计END列各值的出现次数
    SELECT END AS time, COUNT(*) AS count2
    FROM your_table
    GROUP BY END
)
-- 左连接并填充空值为0
SELECT 
    at.time AS TIME,
    COALESCE(cs.count1, 0) AS COUNT1,
    COALESCE(ce.count2, 0) AS COUNT2
FROM all_times at
LEFT JOIN count_start cs ON at.time = cs.time
LEFT JOIN count_end ce ON at.time = ce.time
ORDER BY TIME;

方法二：Python（Pandas）实现 #

用Pandas可以快速完成表格统计：

import pandas as pd

# 构造示例数据（实际可替换为读取CSV/Excel等操作）
data = {
    'START': ['afternoon', 'evening', 'evening', 'afternoon', 'night', 'night'],
    'END': ['evening', 'night', 'night', 'evening', 'morning', 'morning']
}
df = pd.DataFrame(data)

# 分别统计两列的频次
count1 = df['START'].value_counts().rename('COUNT1')
count2 = df['END'].value_counts().rename('COUNT2')

# 合并统计结果，填充缺失值为0，整理列名
result = pd.concat([count1, count2], axis=1).fillna(0).astype(int).reset_index().rename(columns={'index': 'TIME'})

# 按TIME排序，匹配示例输出顺序
result = result.sort_values('TIME').reset_index(drop=True)

# 打印结果
print(result.to_string(index=False))

运行后输出：

TIME  COUNT1  COUNT2
   morning       0       2
 afternoon       2       0
   evening       2       2
     night       2       2

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