使用tidyr将长格式数据转换为以no、mid、effectiveDate为列的数据框

Depending on your preferred tooling, here are a few straightforward solutions to transform your dataset into a structured data frame:

1. Command-Line Solution with awk #

If you want a fast, dependency-free command-line approach, awk is ideal for this task. It processes the input file directly and outputs a clean CSV that you can load into any spreadsheet or data analysis tool.

Command: #

BEGIN { 
    # Print the header row first
    print "no,effectiveDate,mid" 
}
# Capture the value when we hit the "no" key
$2 == "no" { 
    no_val = $3 
}
# Capture the date value when we encounter "effectiveDate"
$2 == "effectiveDate" { 
    date_val = $3 
}
# When we reach "mid", we have all three values—print them as a CSV line
$2 == "mid" { 
    print no_val "," date_val "," $3 
}

How to use: #

Save the code above into a file (e.g., convert.awk), then run:

awk -f convert.awk input.txt > output.csv

Or run it as a one-liner:

awk 'BEGIN{print "no,effectiveDate,mid"} $2=="no"{n=$3} $2=="effectiveDate"{d=$3} $2=="mid"{print n","d","$3}' input.txt > output.csv

2. R Solution with tidyr #

If you're working in R, use the tidyr package to reshape the data from long to wide format easily.

Code: #

# Read the input data (replace "input.txt" with your file path)
data <- read.table("input.txt", sep = "", header = FALSE, col.names = c("id", "key", "value"))

# Load the required package
library(tidyr)

# Create groups to bundle every 3 rows into one record
data$group <- ceiling(data$id / 3)

# Reshape to wide format and clean up unnecessary columns
df <- data %>%
  pivot_wider(names_from = key, values_from = value) %>%
  select(-id, -group)

# View the final data frame
print(df)

3. Python Solution with pandas #

For Python users, pandas simplifies this transformation with its built-in pivoting functionality.

Code: #

import pandas as pd

# Read the input data (replace "input.txt" with your file path)
data = pd.read_csv("input.txt", sep="\s+", header=None, names=["id", "key", "value"])

# Create groups to group each complete record together
data["group"] = (data["id"] - 1) // 3

# Pivot to wide format and reset the index
df = data.pivot(index="group", columns="key", values="value").reset_index(drop=True)

# Reorder columns to match your desired sequence
df = df[["no", "effectiveDate", "mid"]]

# View the result
print(df)

All three methods will produce a structured data frame where each row contains a complete record with the columns no, effectiveDate, and mid.

内容的提问来源于stack exchange，提问作者Bartłomiej Fatyga