0

What I have is a CSV file to this effect:

+------------+--------------+
| Category I | Sub-Category |
+------------+--------------+
|       1144 |          128 |
|       1144 |          128 |
|       1000 |          100 |
|       1001 |          100 |
|       1002 |          100 |
|       1002 |          100 |
|       1011 |          102 |
|       1011 |          102 |
|       1011 |          102 |
|       1011 |          102 |
|       1011 |          102 |
|       1011 |          102 |
|       1013 |          103 |
|       1013 |          103 |
|       1013 |          103 |
|       1013 |          103 |
|       1013 |          103 |
|       1013 |          103 |
|       1013 |          103 |
+------------+--------------+

I wish to concatenate the first and second columns above to form a third, new column with a new arbitrary heading, to this effect:

+-------------+--------------+-----------------------+
| Category ID | Sub-Category | Arbitrary New Heading |
+-------------+--------------+-----------------------+
|        1144 |          128 |               1144128 |
|        1144 |          128 |               1144128 |
|        1000 |          100 |               1000100 |
|        1001 |          100 |               1001100 |
|        1002 |          100 |               1002100 |
|        1002 |          100 |               1002100 |
|        1011 |          102 |               1011102 |
|        1011 |          102 |               1011102 |
|        1011 |          102 |               1011102 |
|        1011 |          102 |               1011102 |
|        1011 |          102 |               1011102 |
|        1011 |          102 |               1011102 |
|        1013 |          103 |               1013103 |
|        1013 |          103 |               1013103 |
|        1013 |          103 |               1013103 |
|        1013 |          103 |               1013103 |
|        1013 |          103 |               1013103 |
|        1013 |          103 |               1013103 |
|        1013 |          103 |               1013103 |
+-------------+--------------+-----------------------+

My usual go-to utility, csvkit does not have the means to achieve this, afaik - see https://github.com/wireservice/csvkit/issues/930.

What is a simple solution not requiring advanced programming knowledge, which can achieve this?

I'm vaguely aware of awk and sed as potential solutions, but I don't want to limit the enquiry to those just in case there is a better (i.e. simpler) solution.

The solution must be efficient for very large files, i.e containing 120,000+ lines.

Edit: I have included the sample data for the convenience of those wanting to take a crack at it; download here: https://www.dropbox.com/s/achtyxg7qi1629k/category-subcat-test.csv?dl=0

Improve this question
3
  • 1
    (1) Please post sample data (as text) in your question. (2) If your input file is CSV, show it as such. (3) If you want CSV output, say so (in the question, in an English sentence) and show it as such. (4) Specify (in an English sentence) how you want the first row (i.e., the headers) handled.
    – G-Man Says 'Reinstate Monica'
    Commented Dec 25, 2019 at 4:24
  • Thank you for your feedback. (1) I already have linked the entire text file above , (2) I've mentioned CSV in many places, the file extension is CSV, the question tag is CSV, CSV is in the title. (3) I suppose I could explicitly say csv output, but the parsiminous interpretation is that since I didn't ask for change in file, there's no reason to think that's what I wanted (4) in the original post, I mentioned "a third third, new column with a new arbitrary heading". I'm not perfect, and there's room for improvement with anyone, but I felt it was generally clear is at stands.
    – ptrcao
    Commented Dec 25, 2019 at 5:51
  • @steeldriver @ G-Man Says 'Reinstate Monica' I have converted the images to text now - please kindly reverse the downvote.
    – ptrcao
    Commented Dec 25, 2019 at 6:28

2 Answers 2

Reset to default
6

Using Miller (https://github.com/johnkerl/miller) and this example input file

Category ID,Sub-Category
1001,128
1002,127
1002,126
1004,122

and running

mlr --csv put -S '$fieldName=${Category ID}." ".${Sub-Category}' input.csv >output.csv

you will have

+-------------+--------------+-----------+
| Category ID | Sub-Category | fieldName |
+-------------+--------------+-----------+
| 1001        | 128          | 1001 128  |
| 1002        | 127          | 1002 127  |
| 1002        | 126          | 1002 126  |
| 1004        | 122          | 1004 122  |
+-------------+--------------+-----------+

And you could run also csvsql, it works, in this way

csvsql -I --query 'select *,("Category ID" || " " || "Sub-Category") fieldname from input' input.csv >output.csv
Improve this answer
5
  • With your csvsql solution, I got: [root@server test]# csvsql -I --query 'select *,("Category ID" || " " || "Sub-Category") fieldname from input' category-subcat-test.csv >output.csv (sqlite3.OperationalError) no such table: input [SQL: select *,("Category ID" || " " || "Sub-Category") fieldname from input] (Background on this error at: sqlalche.me/e/e3q8)
    – ptrcao
    Commented Dec 21, 2019 at 11:41
  • Rename please category-subcat-test.csv in input.csv and test again. If it works, you must change the query because you must insert your file name in the query. The name of mine is input.csv
    – aborruso
    Commented Dec 21, 2019 at 11:43
  • I had it as such: csvsql -I --query 'select *,("Category ID" || " " || "Sub-Category") fieldname from input' category-subcat-test.csv >output.csv
    – ptrcao
    Commented Dec 21, 2019 at 11:46
  • 2
    ok, you must change the command in csvsql -I --query 'select *,("Category ID" || " " || "Sub-Category") fieldname from "category-subcat-test"' category-subcat-test.csv >output.csv
    – aborruso
    Commented Dec 21, 2019 at 11:48
  • 1
    Thank you, that worked :)
    – ptrcao
    Commented Dec 21, 2019 at 11:53
0

Command

Tried with below command

awk 'NR>1{print $1,$2,$1$2 }' file| sed '1i Category Sub-Category New Heading'

output

Category Sub-Category New Heading
1144 128 1144128
1144 128 1144128
1000 100 1000100
1001 100 1001100
1002 100 1002100
1002 100 1002100
1011 102 1011102
1011 102 1011102
1011 102 1011102
1011 102 1011102
1011 102 1011102
1011 102 1011102
1013 103 1013103
1013 103 1013103
1013 103 1013103
1013 103 1013103
1013 103 1013103
1013 103 1013103
Improve this answer
1
  • 3
    Can you please make it output to a csv file, and confirm that all the columns are properly delimited? We should be seeing headings also aligning properly with the data columns.
    – ptrcao
    Commented Dec 22, 2019 at 9:16

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .