在R语言中将Dataframe转换为指定格式的HTML表格

嘿，我来帮你搞定这个DataFrame转HTML表格的需求！看起来你的数据是按月份分组，每个月份行下面跟着A到E的子项，要转成带分组合并单元格的HTML表格对吧？我给你两种常用语言的解决方案，你可以按需选用~

用Python（Pandas）实现 #

首先我们需要先把原始数据整理成结构化的格式，给每个子项匹配对应的月份，然后用Pandas的Styler工具生成带分组样式的HTML表格。

步骤1：整理数据 #

import pandas as pd

# 把你的原始数据转换成列表格式
raw_data = [
    ["May-17", 75, "0.00%", "34,500"],
    ["A", 10, "11.11%", "1,000"],
    ["B", 15, "25.00%", "1,500"],
    ["C", 12, "20.00%", "2,500"],
    ["D", 18, "-5.26%", "4,500"],
    ["E", 20, "-20.00%", "..."],
    ["Apr-17", 75, "4.17%", "..."],
    ["A", 9, "12.50%", "..."],
    ["B", 12, "-7.69%", "..."],
    ["C", 10, "11.11%", "..."],
    ["D", 19, "5.56%", "..."],
    ["E", 25, "4.17%", "..."],
    ["Mar-17", 72, "0.00%", "..."],
    ["A", 8, "0.00%", "..."],
    ["B", 13, "0.00%", "..."],
    ["C", 9, "0.00%", "..."],
    ["D", 18, "0.00%", "..."],
    ["E", 24, "0.00%", "..."]
]

# 给每个子项填充对应的月份，同时标记总览行
current_month = None
formatted_rows = []
for row in raw_data:
    # 判断当前行是否是月份总览行
    if row[0] in ["May-17", "Apr-17", "Mar-17"]:
        current_month = row[0]
        formatted_rows.append({
            "Month": current_month,
            "Category": "Total",
            "No": row[1],
            "Inc_%": row[2],
            "Total": row[3]
        })
    else:
        formatted_rows.append({
            "Month": current_month,
            "Category": row[0],
            "No": row[1],
            "Inc_%": row[2],
            "Total": row[3]
        })

# 转换成DataFrame
df = pd.DataFrame(formatted_rows)

步骤2：生成带分组样式的HTML表格 #

def generate_styled_html(df):
    # 创建Styler对象
    styler = df.style
    
    # 设置基础样式：居中对齐、表头样式
    styler.set_table_styles([
        {'selector': 'th', 'props': [('text-align', 'center'), ('padding', '8px')]},
        {'selector': 'td', 'props': [('text-align', 'center'), ('padding', '8px')]},
        {'selector': 'caption', 'props': [('font-size', '1.2em'), ('margin-bottom', '10px'), ('font-weight', 'bold')]}
    ])
    
    # 合并相同月份的单元格，并给总览行添加高亮样式
    prev_month = None
    group_indices = []
    for idx, row in df.iterrows():
        if row["Month"] != prev_month:
            # 处理上一个月份组的合并和样式
            if group_indices:
                # 给总览行加粗加背景色
                styler.apply(lambda x: ['font-weight: bold; background-color: #f5f5f5;' if x.name == group_indices[0] else '' for _ in x], axis=1)
                # 合并月份列的单元格
                styler.merge_cells(start_row=group_indices[0], end_row=group_indices[-1], start_col=0, end_col=0)
            prev_month = row["Month"]
            group_indices = [idx]
        else:
            group_indices.append(idx)
    
    # 处理最后一个月份组
    if group_indices:
        styler.apply(lambda x: ['font-weight: bold; background-color: #f5f5f5;' if x.name == group_indices[0] else '' for _ in x], axis=1)
        styler.merge_cells(start_row=group_indices[0], end_row=group_indices[-1], start_col=0, end_col=0)
    
    # 添加表格标题
    styler.set_caption("Monthly Data Summary")
    
    # 转换为HTML字符串
    return styler.to_html()

# 生成HTML表格
html_output = generate_styled_html(df)
# 可以直接打印或者保存到文件
print(html_output)

这个代码会生成一个带合并月份单元格、总览行高亮的HTML表格，完全符合分组展示的需求。

用R语言实现 #

如果你习惯用R，可以用tidyverse和kableExtra包来实现同样的效果：

步骤1：整理数据 #

library(tidyverse)
library(kableExtra)
library(zoo)

# 构造原始数据框
raw_data <- tibble(
  Month = c("May-17", "A", "B", "C", "D", "E", "Apr-17", "A", "B", "C", "D", "E", "Mar-17", "A", "B", "C", "D", "E"),
  No = c(75L, 10L, 15L, 12L, 18L, 20L, 75L, 9L, 12L, 10L, 19L, 25L, 72L, 8L, 13L, 9L, 18L, 24L),
  Inc_% = c("0.00%", "11.11%", "25.00%", "20.00%", "-5.26%", "-20.00%", "4.17%", "12.50%", "-7.69%", "11.11%", "5.56%", "4.17%", "0.00%", "0.00%", "0.00%", "0.00%", "0.00%", "0.00%"),
  Total = c("34,500", "1,000", "1,500", "2,500", "4,500", "...", "...", "...", "...", "...", "...", "...", "...", "...", "...", "...", "...", "...")
)

# 填充月份并标记总览行
formatted_data <- raw_data %>%
  # 把非月份行的Month设为NA，然后用前向填充补全月份
  mutate(Month = ifelse(str_detect(Month, "\\d{2}"), Month, NA)) %>%
  mutate(Month = na.locf(Month)) %>%
  # 添加Category列，标记总览行和子项行
  mutate(Category = case_when(
    row_number() %in% c(1,7,13) ~ "Total",
    TRUE ~ raw_data$Month[row_number()]
  )) %>%
  relocate(Category, .after = Month)

步骤2：生成分组HTML表格 #

formatted_data %>%
  kable("html", caption = "Monthly Data Summary", align = "c") %>%
  kable_styling(full_width = FALSE, bootstrap_options = "striped") %>%
  # 合并月份列的相同单元格
  collapse_rows(columns = 1, valign = "middle") %>%
  # 给总览行添加高亮样式
  row_spec(which(formatted_data$Category == "Total"), bold = TRUE, background = "#f5f5f5")

运行这段代码后，会直接生成带分组合并单元格的HTML表格，样式美观且符合需求。

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