-3

I have a data file that contains a single region and then a varying number of cities/towns as follows;-

South,London,Guildford,Winchester
North,Manchester,Leeds
East,Norwich

I need to produce an output like this using AWK

South,London
South,Guildford,
South,Winchester
North,Manchester etc

It must loop through only when there is a city/town present.

  • What is the relevance of the condition given on the last line of the question ("It must loop through only when there is a city/town present")? – Kusalananda Jun 7 at 8:06
2
$ awk -F ',' 'BEGIN { OFS=FS } { for (i=2; i<=NF; ++i) print $1, $i }' file
South,London
South,Guildford
South,Winchester
North,Manchester
North,Leeds
East,Norwich

The awk programs simply loops over the comma-delimited fields of each line, from field two onwards, and outputs each field together with the first field on the same line.

The BEGIN block just sets the output field delimiter to the same as the input field delimiter.

This would work if your input data is in a "simple CSV" format, i.e. if it is comma-delimited and if none of the fields have embedded commas or newlines.

  • That's great - and if I wanted to have totals by region too such as...South,London,3,South,Guildford,3,South,Winchester,3,North,Manchester,2, etc? – Jon Jun 7 at 8:31
  • @Jon I don't really do follow-up questions in comments. If you have additional issues, you should ask a new question. Make sure that question is complete from the start. – Kusalananda Jun 7 at 8:43
  • I understand, no problem - thanks for the answer :-) – Jon Jun 7 at 9:00
0

I have done in below method and it worked fine

line_count=`awk '{print NR}' p.txt | sort -rn| sed -n '1p'`

for ((i=1;i<=$line_count;i++)); do No_of_columns=`awk -v i="$i" -F "," 'NR==i{print NF}' p.txt`; value=`awk -v i="$i" -F "," 'NR==i{print $1}' p.txt`;  colum_count=`awk -v i="$i" -F "," 'NR==i {print NF}' p.txt`; for ((d=2;d<=$colum_count;d++)); do awk -v i="$i" -v value="$value" -v d="$d" -F ","  'NR==i{print value","$d}' p.txt; done; done

output

South,London
South,Guildford
South,Winchester
North,Manchester
North,Leeds
East,Norwich

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.