# AWK regex and Dataset handling **URL:** \ **Category:** Shell Programming and Scripting\ **Created:** [December 18, 2022, 8:48am UTC](https://community.unix.com/t/awk-regex-and-dataset-handling/389264 "2022-12-18T08:48:06Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![alrai](https://community.unix.com/letter_avatar/alrai/32/6_22734026db12803ebb3e059622ac3534.png) [@alrai](https://community.unix.com/u/alrai)\ **Post date:** [December 18, 2022, 8:48am UTC](https://community.unix.com/t/awk-regex-and-dataset-handling/389264/1 "2022-12-18T08:48:07Z")
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. > [@](#): > > adult,belongs\_to\_collection,budget,genres,homepage,id,imdb\_id,original\_language,original\_title,overview,popularity,poster\_path,production\_companies,production\_countries,release\_date,revenue,runtime,spoken\_languages,status,tagline,title,video,vote\_average,vote\_count > [@](#): > > False,"{'id': 10194, 'name': 'Toy Story Collection', 'poster\_path': '/7G9915LfUQ2lVfwMEEhDsn3kT4B.jpg', 'backdrop\_path': '/9FBwqcd9IRruEDUrTdcaafOMKUq.jpg'}",30000000,"[{'id': 16, 'name': 'Animation'}, {'id': 35, 'name': 'Comedy'}, {'id': 10751, 'name': 'Family'}]", movieurl,862,tt0114709,en,Toy Story,"Led by Woody, Andy's toys live happily in his room until Andy's birthday brings Buzz Lightyear onto the scene. Afraid of losing his place in Andy's heart, Woody plots against Buzz. But when circumstances separate Buzz and Woody from their owner, the duo eventually learns to put aside their differences.",21.946943,/rhIRbceoE9lR4veEXuwCC2wARtG.jpg,"[{'name': 'Pixar Animation Studios', 'id': 3}]","[{'iso\_3166\_1': 'US', 'name': 'United States of America'}]",1995-10-30,373554033,81.0,"[{'iso\_639\_1': 'en', 'name': 'English'}]",Released,,Toy Story,False,7.7,5415 > > False,,65000000,"[{'id': 12, 'name': 'Adventure'}, {'id': 14, 'name': 'Fantasy'}, {'id': 10751, 'name': 'Family'}]",,8844,tt0113497,en,Jumanji,"When siblings Judy and Peter discover an enchanted board game that opens the door to a magical world, they unwittingly invite Alan -- an adult who's been trapped inside the game for 26 years -- into their living room. Alan's only hope for freedom is to finish the game, which proves risky as all three find themselves running from giant rhinoceroses, evil monkeys and other terrifying creatures.",17.015539,/vzmL6fP7aPKNKPRTFnZmiUfciyV.jpg,"[{'name': 'TriStar Pictures', 'id': 559}, {'name': 'Teitler Film', 'id': 2550}, {'name': 'Interscope Communications', 'id': 10201}]","[{'iso\_3166\_1': 'US', 'name': 'United States of America'}]",1995-12-15,262797249,104.0,"[{'iso\_639\_1': 'en', 'name': 'English'}, {'iso\_639\_1': 'fr', 'name': 'Français'}]",Released,Roll the dice and unleash the excitement!,Jumanji,False,6.9,2413 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. ---
**Author:** ![drysdalk](https://community.unix.com/letter_avatar/drysdalk/32/6_22734026db12803ebb3e059622ac3534.png) [@drysdalk](https://community.unix.com/u/drysdalk)\ **Post date:** [December 18, 2022, 9:32am UTC](https://community.unix.com/t/awk-regex-and-dataset-handling/389264/2 "2022-12-18T09:32:18Z")
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)](https://www.gnu.org/software/gawk/manual/html_node/Splitting-By-Content.html) 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 ! ---
**Author:** ![alrai](https://community.unix.com/letter_avatar/alrai/32/6_22734026db12803ebb3e059622ac3534.png) [@alrai](https://community.unix.com/u/alrai)\ **Post date:** [December 18, 2022, 9:51am UTC](https://community.unix.com/t/awk-regex-and-dataset-handling/389264/3 "2022-12-18T09:51:22Z")
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... ---
**Author:** ![system](https://community.unix.com/uploads/default/original/2X/c/c7e9e16a595259edafe88615719d5c5dcda035c2.png) [@system](https://community.unix.com/u/system)\ **Post date:** [December 28, 2022, 9:51am UTC](https://community.unix.com/t/awk-regex-and-dataset-handling/389264/4 "2022-12-28T09:51:40Z")
Closed on December 28, 2022, 9:51am UTC This topic was automatically closed 10 days after the last reply. New replies are no longer allowed. ---
**Author:** ![chatgpt](https://community.unix.com/user_avatar/community.unix.com/chatgpt/32/19766_2.png) [@chatgpt](https://community.unix.com/u/chatgpt)\ **Post date:** [January 4, 2023, 3:13am UTC](https://community.unix.com/t/awk-regex-and-dataset-handling/389264/5 "2023-01-04T03:13:40Z")
Here is the answer provided by [ChatGPT](https://chat.openai.com/chat): > 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 > > ```plaintext > # 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 > > ```plaintext > 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 > > ```plaintext > # 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. > [@Replying to Questions Using ChatGPT](https://community.unix.com/t/replying-to-questions-using-chatgpt/389421): > > I have just provided over 10 examples of using [ChatGPT](https://chat.openai.com/chat) to answer questions. All members can do the same, but please state / reference that you are using ChatGPT if you do so. We may create a (future) rule for this, I think (have not discussed with the team yet). In the meantime, anyone who posts a reply using ChatGPT should say so. FWIW, some of the replies I have read in 2022 seem to have originated from ChatGPT, TBH... Thanks: Examples Using ChatGPT .... ---
**Author:** ![MadeInGermany](https://community.unix.com/letter_avatar/madeingermany/32/6_22734026db12803ebb3e059622ac3534.png) [@MadeInGermany](https://community.unix.com/u/MadeInGermany)\ **Post date:** [January 4, 2023, 9:27am UTC](https://community.unix.com/t/awk-regex-and-dataset-handling/389264/6 "2023-01-04T09:27:10Z")
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: ```plaintext 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()`.