主页 / unix / 问题 / 683304
Accepted
tucomax
Asked: 2021-12-22 00:50:19 +0800 CST

从日期列中拆分年份并使用 awk 或 sed 创建带有年份的新列

  • 772

我有一个这样的csv格式的数据集（6000行）：

Store,Date,Weekly_Sales,Holiday_Flag,Temperature,Fuel_Price,CPI,Unemployment
1,05-02-2010,1643690.9,0,42.31,2.572,211.0963582,8.106
1,12-02-2010,1641957.44,1,38.51,2.548,211.2421698,8.106
---
---

我想拆分日期列以创建一个仅包含年份的新列。像这样的东西：

Store,Date,Year,Weekly_Sales,Holiday_Flag,Temperature,Fuel_Price,CPI,Unemployment
1,05-02-2010,2010,1643690.9,0,42.31,2.572,211.0963582,8.106
1,12-02-2010,2010,1641957.44,1,38.51,2.548,211.2421698,8.106

我试图使用这段代码，但我不知道如何修改它来解决我的问题： 如何提取年份......

有人可以给我一些建议吗？

bash awk

2 个回答

  1. Best Answer

    我会使用适当的csv解析器，例如csvkit：

    csvsql --query "select Store,Date,strftime('%Y', Date) as Year,Weekly_Sales,Holiday_Flag,Temperature,Fuel_Price,CPI,Unemployment  from data" data.csv

    输出：

    Store,Date,Year,Weekly_Sales,Holiday_Flag,Temperature,Fuel_Price,CPI,Unemployment
1,2010-05-02,2010,1643690.9,0,42.31,2.572,211.0963582,8.106
1,2010-12-02,2010,1641957.44,1,38.51,2.548,211.2421698,8.106

    但是如果你想用 来做awk，这会做：

    awk '
  BEGIN{FS=OFS=","}
  NR==1{$2="Date,Year"}
  NR>1 {sub(/[0-9]{4}/, "&,&", $2)}
1' data.csv
    • 4 
  2.  awk 'BEGIN{FS=OFS=","}NR==1{gsub("Date","Date,year",$0)} NR>1 {$2=$2","substr($2,7,4)}1' filename

Store,Date,year,Weekly_Sales,Holiday_Flag,Temperature,Fuel_Price,CPI,Unemployment
1,05-02-2010,2010,1643690.9,0,42.31,2.572,211.0963582,8.106
1,12-02-2010,2010,1641957.44,1,38.51,2.548,211.2421698,8.106
    • 0

相关问题