I have a CSV file with the general format shown in the picture below.
In that CSV there are multiple rows that belong to a certain column (desc
) and I would like to to extract those items and add them to new columns called name, size, weight, glass
, respectively. I have highlighted (in red) those sub-row items for the entries.
Original Structure:
Expected Structure:
The original CSV:
page,item,id,desc,price
1,2,F1,Alpha,111.11
1,,,380 x 2280 mm (size),
1,,,Weight: 33.0 kg,
1,,,Glass: AAA,
1,,,XXX,
1,3,F2,Beta,222.22
1,,,880 x 2280 mm (size),
1,,,Weight: 68.4 kg,
1,,,Glass: BBB,
1,,,YYY,
1,4,F3,Gamma,333.33
1,,,1980 x 1580 mm (size),
1,,,Weight: 78.2 kg,
1,,,Glass: CCC,
1,,,XXX,
1,,,YYY,
1,,,ZZZ,
Expected resulting CSV:
page,item,id,name,size,weight,glass,price
1,2,F1,Alpha,380 x 2280,33.0,AAA,111.11
1,3,F2,Beta,880 x 2280,68.4,BBB,222.22
1,4,F3,Gamma,1980 x 1580,78.2,CCC,333.33
where name would take the place of the first row in desc.
UPDATE:
Under certain conditions, some Awk solutions may work with the above, but fail when adding a 4th item. To fully test, consider adding this to the above:
1,7,F4,Delta,111.11
1,,,11 x 22 mm (size),
1,,,Weight: 33.0 kg,
1,,,Glass: DDD,
1,,,Random-1,
So 3 important points:
- The number of sub-rows in the
desc
column may vary. - Any sub-rows after
Glass:...
should be ignored. - There may be items that doesn't have any sub-rows in the
desc
column, they should also be ignored.
Q: How can I remap those sub-rows into new columns, using Awk?
(Or are there more suitable tools for doing this in bash?)
Possibly related (but not very helpful) Questions:
item
value ever be the number0
?awk -F'[,"]' '($2~/[0-9]+/ && $3~/F[0-9]+/) || (!$2 && !$3)'
, because there may be rows that look like:sometimes,shit,happens,with,data
.-F'[,"]'
is pretty suspect. Actually so are the other conditions. If you post a new question we can help you with that filtering.