AWK regex and Dataset handling

Greetings

I have a task here where I wish to use the power of Regex and AWK to manage datasets. I have picked a movie data set and in some fields, the information is nested with multiple ',' which is already the field separator. I understand that this can be done with GUI tools but I wish to be able to handle it via AWK cli. Let me paste 2 records of the data set to elaborate my problem. I am fairly a beginner here and I will appreciate any guidance. Thank you.

Following are 3 records, including the header from the movie dataset I found on kaggle.

What possible approaches I can use, using regex and AWK to display each record. I can't because the number of fields maybe different in each record but the nested record is contained in . How do I treat any data within as ONE field and still display the whole record using single regex, maybe using an AWK script?

Much obliged for any advice from the regex PROs.

Hello,

Welcome to the forum ! We hope you enjoy your time here, and find this to be a friendly and helpful place.

This can indeed still be handled by awk, with the biggest issue being the one you have correctly identified: the field separator. Some fields here are quoted fields with an embedded comma as part of the data, and so a simple straightforward awk -F, isn't quite going to cut it.

However, there is a solution: awk supports a built-in variable called FPAT, which can be used to specify a field pattern as a regular expression. This is a pattern that you would use to tell awk which patterns of text should be considered fields, and which should not be considered as fields.

You can read all about this at the on-line GNU awk documentation here, along with some sample code:

Splitting By Content (The GNU Awk User's Guide)

Have a read over that, try implementing a solution using FPAT, and let us know how you get on. In the event that you have problems, please do feel free to come back here with the code you've written, the output you get, and a description of the difficulties you're facing, and we can take things from there.

Hope this helps !

Thanks drysdalk

Good lead, I'll get right on it.

Thanks for the welcome indeed, I intend to stay here and have fun learning and then start sharing what I learn.

Two thumbs up...

Here is the answer provided by ChatGPT:

It sounds like you want to extract the data within the square brackets [] and treat them as a single field in your output. One way to do this with awk is to use the match() function to extract the data within the brackets, and then use that as the value for a new field in your output.

Here is an example awk script that demonstrates this approach:

Copy code

# Set the field separator as a comma
BEGIN { FS = "," }

# For each line, extract the data within the brackets and store it in a new field
{
  match($0, /\[.*\]/);
  new_field = substr($0, RSTART, RLENGTH);
  print new_field
}

To use this script, you can save it to a file (e.g. extract_fields.awk) and then run it on your data like this:

Copy code

awk -f extract_fields.awk data.csv

This will output the data within the brackets for each line in the input file. If you want to include the rest of the fields in the output as well, you can use the print statement to print all of the fields, like this:

Copy code

# Set the field separator as a comma
BEGIN { FS = "," }

# For each line, extract the data within the brackets and store it in a new field
{
  match($0, /\[.*\]/);
  new_field = substr($0, RSTART, RLENGTH);

  # Print all of the fields, including the new field
  for (i = 1; i <= NF; i++) {
    printf("%s", $i)
    if (i < NF) {
      printf(",")
    }
  }
  printf("\n")
}

I hope this helps! Let me know if you have any questions.

I think the ChatGPT answer is not good.
It does not address the complexity of nested delimiters in the given input file.
Was the input file given at all?

The FPAT approach is more promising. But complex and maybe still not precise enough.
The best precision (understanding of the delimiter levels) can be achieved with perl.

Here comes a half-way solution with awk FPAT:

awk 'BEGIN { FPAT="[^,]*|\"[^\"]*\"" } { for (i=1; i<=NF; i++) print "f" i, $i }' inputfile

The fields in "[ ]" can be split further, using an explicit split().