![是否有命令行咒语可以按名称将特定列保存在 CSV 文件中?](https://linux22.com/image/72442/%E6%98%AF%E5%90%A6%E6%9C%89%E5%91%BD%E4%BB%A4%E8%A1%8C%E5%92%92%E8%AF%AD%E5%8F%AF%E4%BB%A5%E6%8C%89%E5%90%8D%E7%A7%B0%E5%B0%86%E7%89%B9%E5%AE%9A%E5%88%97%E4%BF%9D%E5%AD%98%E5%9C%A8%20CSV%20%E6%96%87%E4%BB%B6%E4%B8%AD%EF%BC%9F.png)
假设我有一个疯狂的 CSV 数据集:
SOS_VOTERID,COUNTY_NUMBER,COUNTY_ID,LAST_NAME,FIRST_NAME,MIDDLE_NAME,SUFFIX,DATE_OF_BIRTH,REGISTRATION_DATE,PARTY_AFFILIATION,RESIDENTIAL_ADDRESS1,RESIDENTIAL_SECONDARY_ADDR,city,RESIDENTIAL_STATE,zip,RESIDENTIAL_ZIP_PLUS4,RESIDENTIAL_COUNTRY,RESIDENTIAL_POSTALCODE,MAILING_ADDRESS1,MAILING_SECONDARY_ADDRESS,MAILING_CITY,MAILING_STATE,MAILING_ZIP,MAILING_ZIP_PLUS4,MAILING_COUNTRY,MAILING_POSTAL_CODE,CAREER_CENTER,CITY,CITY_SCHOOL_DISTRICT,COUNTY_COURT_DISTRICT,CONGRESSIONAL_DISTRICT,COURT_OF_APPEALS,EDUCATIONAL_SERVICE_CENTER_DISTRICT,EXEMPTED_VILLAGE_SCHOOL_DISTRICT,LIBRARY,LOCAL_SCHOOL_DISTRICT,MUNICIPAL_COURT_DISTRICT,PRECINCT_NAME,PRECINCT_CODE,STATE_BOARD_OF_EDUCATION,STATE_REPRESENTATIVE_DISTRICT,STATE_SENATE_DISTRICT,TOWNSHIP,VILLAGE,WARD,PRIMARY-03/07/2000,GENERAL-11/07/2000,SPECIAL-05/08/2001,GENERAL-11/06/2001,PRIMARY-05/07/2002,GENERAL-11/05/2002,SPECIAL-05/06/2003,GENERAL-11/04/2003,PRIMARY-03/02/2004,GENERAL-11/02/2004,SPECIAL-02/08/2005,PRIMARY-05/03/2005,PRIMARY-09/13/2005,GENERAL-11/08/2005,SPECIAL-02/07/2006,PRIMARY-05/02/2006,GENERAL-11/07/2006,PRIMARY-05/08/2007,PRIMARY-09/11/2007,GENERAL-11/06/2007,PRIMARY-11/06/2007,GENERAL-12/11/2007,PRIMARY-03/04/2008,PRIMARY-10/14/2008,GENERAL-11/04/2008,GENERAL-11/18/2008,PRIMARY-05/05/2009,PRIMARY-09/08/2009,PRIMARY-09/15/2009,PRIMARY-09/29/2009,GENERAL-11/03/2009,PRIMARY-05/04/2010,PRIMARY-07/13/2010,PRIMARY-09/07/2010,GENERAL-11/02/2010,PRIMARY-05/03/2011,PRIMARY-09/13/2011,GENERAL-11/08/2011,PRIMARY-03/06/2012,GENERAL-11/06/2012,PRIMARY-05/07/2013,PRIMARY-09/10/2013,PRIMARY-10/01/2013,GENERAL-11/05/2013,PRIMARY-05/06/2014,GENERAL-11/04/2014,PRIMARY-05/05/2015
OH0012781511,87,26953,HOUSEHOLDER,SHERRY,LEIGH,,11/26/1965,08/19/1988,,211 N GARFIELD ST , ,BLOOMDALE,OH,44817,,,, PO BOX 222 , ,BLOOMDALE,OH,44817,,,,PENTA JVSD,,,,05,06,WOOD EDUC SRV CTR,,,ELMWOOD LOCAL SD,BOWLING-GREEN,BLOOM TWP BLOOMDALE,87-P-ABO,02,03,02,,BLOOMDALE VILLAGE,,D,,,X,,X,,,,X,,,,,,,,,,,,,D,,X,,,,,,X,,,,,,,,,X,,,,,,,
OH0012781528,87,31122,KEATON,JENNIFER,KAI,,11/27/1968,07/13/2015,,110 N GARFIELD ST , ,BLOOMDALE,OH,44817,,,, PO BOX 16 , ,BLOOMDALE,OH,44817,,,,PENTA JVSD,,,,05,06,WOOD EDUC SRV CTR,,,ELMWOOD LOCAL SD,BOWLING-GREEN,BLOOM TWP BLOOMDALE,87-P-ABO,02,03,02,,BLOOMDALE VILLAGE,,,X,,,,,,,,X,,,,,,,,,,,,,,,X,,,,,,X,,,,,,,X,,X,,,,,,,
我只想拯救这座城市和拉链柱;是否有一个简单的命令行拼写,我可以将两个名称指定为参数并仅将这两列保存在新的 CSV 文件中?
答案1
如果您有简单的逗号分隔列,则可以使用 awk 来完成。解析第一行以确定所需的列,然后打印这些列。
wanted_columns=city,zip
wanted_columns=",$wanted_columns," awk -F, '
NR==1 {
for (i=1; i<=NF; i++) {
if (index(ENVIRON["wanted_columns"], ","$i",")) {last=i; columns[i]=","}
columns[last]="\n"
}
}
{
for (i=1; i<=NF; i++) {
if (columns[i]) printf "%s%s", $i, columns[i]
}
}'
或者,您可以使用cut
;如果文件很大,这会更快。首先使用不同的工具解析标题行以确定列号。
wanted_columns=city,zip
{
IFS= read header;
cut_spec=$(printf %s "$header" |
wanted_columns=",$wanted_columns," awk -v RS=, '
index(ENVIRON["wanted_columns"], ","$0",") {printf "%d,", NR}'
);
{ printf %s\\n "$header"; cat; } | cut -d , -f "${cut_spec%,}";
}
如果您的 CSV 文件是实际的 CSV 文件,其中一些带引号的列可以嵌入列或换行符,请使用适当的 CSV 工具。例如,在Python:
#!/usr/bin/env python2
import csv, sys
wanted_columns = set(sys.argv[1:])
reader = csv.reader(sys.stdin)
header = reader.next()
columns = [i for i in range(len(header)) if header[i] in wanted_columns]
writer=csv.writer(sys.stdout)
writer.writerow(sys.argv[1:])
for row in reader: writer.writerow([row[i] for i in columns])
(将所需的列名称作为参数传递给脚本。)
答案2
如果您没有嵌入,
(意味着没有...,"foo,bar",...
),您可以使用cut
:
cut -d, -f13,15
仅选择第 13 列和第 15 列,其中各列由 分隔,
。为什么第 13 列和第 15 列是正确的?我数了逗号
head -n 1 data.csv | sed 's/zip,.*/,/' | tr -dc , | wc -c
解释:获取输入的第一行 ( head
),找到“zip”并将其和该行的其余部分替换为逗号 ( sed
),然后丢弃所有不是逗号 ( tr
) 和计数 ( wc
) 的内容。因此“a,zip,b”被转换为“a,,”,“,,”到2。“zip”是第二个字段。