Grouping the multiple value corresponding to single Key in csv file using AWK

up vote
2
down vote

favorite

While grouping the values corresponding to single key, I tried this on on a csv format file having more than two fields like :

Keyobject,FieldName,Fieldvalue
1,are you sleeping,yes
1,country of meeting,USA 
2,are you sleeping,' '
2,country of meeting,' '
3,are you sleeping,yes
3,country of meeting,CHINA
4,are you sleeping,yes
4,country of meeting,ITALY

Expected Output :

Keyobject,Are you sleeping,country of meeting
1,yes,USA
2, ,
3,yes,CHINA
4,no,ITALY.

Please do provide the rough idea or any script .

edited 3 hours ago

Goro

6,20152763

asked 5 hours ago

Dinesh Boora

111

New contributor

What have you tried? Where are you stuck?
â€“Â NickD
4 hours ago

@Dinesh Boora. you have discrepancy between input and output. For instance, row#4 the response is yes not no as presented in the output. row #2 in the output must be ',' ' ' please revise the question accordingly!
â€“Â Goro
3 hours ago

This will print the fieldvalue's with a matching keyobject of 1 cat file.csv | awk -F, '$1 == "1" printf $3","' I tried to loop each unique keyobject but im having trouble getting this awk statement to print the output at all. What have you tried so far?
â€“Â SpruceTips
3 hours ago

add a commentÂ |Â

up vote
2
down vote

favorite

While grouping the values corresponding to single key, I tried this on on a csv format file having more than two fields like :

Keyobject,FieldName,Fieldvalue
1,are you sleeping,yes
1,country of meeting,USA 
2,are you sleeping,' '
2,country of meeting,' '
3,are you sleeping,yes
3,country of meeting,CHINA
4,are you sleeping,yes
4,country of meeting,ITALY

Expected Output :

Keyobject,Are you sleeping,country of meeting
1,yes,USA
2, ,
3,yes,CHINA
4,no,ITALY.

Please do provide the rough idea or any script .

edited 3 hours ago

Goro

6,20152763

asked 5 hours ago

Dinesh Boora

111

New contributor

What have you tried? Where are you stuck?
â€“Â NickD
4 hours ago

@Dinesh Boora. you have discrepancy between input and output. For instance, row#4 the response is yes not no as presented in the output. row #2 in the output must be ',' ' ' please revise the question accordingly!
â€“Â Goro
3 hours ago

This will print the fieldvalue's with a matching keyobject of 1 cat file.csv | awk -F, '$1 == "1" printf $3","' I tried to loop each unique keyobject but im having trouble getting this awk statement to print the output at all. What have you tried so far?
â€“Â SpruceTips
3 hours ago

add a commentÂ |Â

up vote
2
down vote

favorite

While grouping the values corresponding to single key, I tried this on on a csv format file having more than two fields like :

Keyobject,FieldName,Fieldvalue
1,are you sleeping,yes
1,country of meeting,USA 
2,are you sleeping,' '
2,country of meeting,' '
3,are you sleeping,yes
3,country of meeting,CHINA
4,are you sleeping,yes
4,country of meeting,ITALY

Expected Output :

Keyobject,Are you sleeping,country of meeting
1,yes,USA
2, ,
3,yes,CHINA
4,no,ITALY.

Please do provide the rough idea or any script .

edited 3 hours ago

Goro

6,20152763

asked 5 hours ago

Dinesh Boora

111

New contributor

While grouping the values corresponding to single key, I tried this on on a csv format file having more than two fields like :

Keyobject,FieldName,Fieldvalue
1,are you sleeping,yes
1,country of meeting,USA 
2,are you sleeping,' '
2,country of meeting,' '
3,are you sleeping,yes
3,country of meeting,CHINA
4,are you sleeping,yes
4,country of meeting,ITALY

Expected Output :

Keyobject,Are you sleeping,country of meeting
1,yes,USA
2, ,
3,yes,CHINA
4,no,ITALY.

Please do provide the rough idea or any script .

awk

edited 3 hours ago

Goro

6,20152763

asked 5 hours ago

Dinesh Boora

111

New contributor

edited 3 hours ago

Goro

6,20152763

asked 5 hours ago

Dinesh Boora

111

New contributor

edited 3 hours ago

Goro

6,20152763

edited 3 hours ago

Goro

6,20152763

edited 3 hours ago

Goro

6,20152763

asked 5 hours ago

Dinesh Boora

111

New contributor

asked 5 hours ago

Dinesh Boora

111

asked 5 hours ago

Dinesh Boora

111

New contributor

Dinesh Boora is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

What have you tried? Where are you stuck?
â€“Â NickD
4 hours ago

@Dinesh Boora. you have discrepancy between input and output. For instance, row#4 the response is yes not no as presented in the output. row #2 in the output must be ',' ' ' please revise the question accordingly!
â€“Â Goro
3 hours ago

This will print the fieldvalue's with a matching keyobject of 1 cat file.csv | awk -F, '$1 == "1" printf $3","' I tried to loop each unique keyobject but im having trouble getting this awk statement to print the output at all. What have you tried so far?
â€“Â SpruceTips
3 hours ago

add a commentÂ |Â

What have you tried? Where are you stuck?
â€“Â NickD
4 hours ago

@Dinesh Boora. you have discrepancy between input and output. For instance, row#4 the response is yes not no as presented in the output. row #2 in the output must be ',' ' ' please revise the question accordingly!
â€“Â Goro
3 hours ago

This will print the fieldvalue's with a matching keyobject of 1 cat file.csv | awk -F, '$1 == "1" printf $3","' I tried to loop each unique keyobject but im having trouble getting this awk statement to print the output at all. What have you tried so far?
â€“Â SpruceTips
3 hours ago

What have you tried? Where are you stuck?
â€“Â NickD
4 hours ago

@Dinesh Boora. you have discrepancy between input and output. For instance, row#4 the response is yes not no as presented in the output. row #2 in the output must be ',' ' ' please revise the question accordingly!
â€“Â Goro
3 hours ago

This will print the fieldvalue's with a matching keyobject of 1 cat file.csv | awk -F, '$1 == "1" printf $3","' I tried to loop each unique keyobject but im having trouble getting this awk statement to print the output at all. What have you tried so far?
â€“Â SpruceTips
3 hours ago

add a commentÂ |Â

4 Answers
4

active

oldest

votes

up vote
3
down vote

Let's say that your csv file is called "list.csv". I would do it like this:

#!/bin/bash
echo -e "Keyobject","Are you sleeping ", " Country of meeting" # create new header
cat list.csv | sed 1,1d | awk -F, 'NF>1print $NF' | paste -d', ' - - | sed 's/ /,/' | nl


 1 yes,USA 
 2 ' ', ' '
 3 yes,CHINA
 4 yes,ITALY

Explanation:

sed 1,1d # This command excludes the original header in list.csv
awk -F, 'NF>1print $NF' # This command reads the last word in each row
paste -d', ' - - # This command paste the words beside each other 
sed 's/ /,/' # This command replaces blank spaces with commas
nl # This command adds numbering to the rows in the final output

However, this solution assumes that the rows in the original csv file are organized as presented in your example above.

edited 3 hours ago

answered 3 hours ago

Goro

6,20152763

add a commentÂ |Â

up vote
3
down vote

awk -F, '
 NR > 1 ids[$1]; keys[$2]; value[$1,$2] = $3
 END 
 printf "Keyobject"
 for (k in keys)
 printf ",%s", k
 print ""

 for (id in ids) 
 printf id
 for (k in keys)
 printf ",%s", value[id,k]
 print ""
 
 
' file

answered 3 hours ago

glenn jackman

48.3k365105

for(key in array) may iterate through the keys in any order. with original-awk (actually nawk) the lines will be printed 2,3,4,1.
â€“Â mosvy
3 hours ago

@glenn jackman this is brilliant !!
â€“Â TNT
1 hour ago

add a commentÂ |Â

up vote
1
down vote

Try also

awk -F, -v OFS="," '
NR == 1 HD = "Keyobject,are you sleeping,country of meeting"
 for (MX=n=split (HD, HDArr, OFS); n>0; n--) SRCH[HDArr[n]]
 print HD
 next
 


$2 in SRCH RES[$2] = $3
 RES[HDArr[1]] = $1
 

NR%2 for (i=1; i<=MX; i++) printf "%s%s", RES[HDArr[i]], (i == MX)?ORS:OFS
 split ("", RES)
 

' file
Keyobject,are you sleeping,country of meeting
1,yes,USA 
2,' ',' '
3,yes,CHINA
4,yes,ITALY

When reading line 1, the header, and a search array indexed by the header elements is created, and header printed. In the lines following, the results array indexed by $2 is assigned $3, and redundantly for the "Keyobject" index string. For every second line, the result is printed, and the result array deleted.

answered 3 hours ago

RudiC

1,6969

add a commentÂ |Â

up vote
1
down vote

awk <file -v IV="' '" -v OV=" " -F, '
 $1+0 
 b = $2 == IV ? OV : $2
 a[$1,$2] = $3 == IV ? OV : $3
 if($1 > max) max = $1; t[$2]++;
 
 END
 ORS = ""
 print "Keyobject"
 for(k in t) print FS k
 print RS
 for(i = 1; i <= max; i++)
 print i
 for(k in t)
 print FS (i SUBSEP k in a ? a[i,k] : OV)
 print RS
 
 
'

Adjust the IV and OV variables for what an empty field should be in the input and output respectively.

edited 3 hours ago

answered 3 hours ago

mosvy

1,752110

add a commentÂ |Â

Your Answer

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "106"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
convertImagesToLinks: false,
noModals: false,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);

);

Dinesh Boora is a new contributor. Be nice, and check out our Code of Conduct.

draft saved

draft discarded

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2funix.stackexchange.com%2fquestions%2f473055%2fgrouping-the-multiple-value-corresponding-to-single-key-in-csv-file-using-awk%23new-answer', 'question_page');

);

Post as a guest

Name

4 Answers
4

active

oldest

votes

4 Answers
4

active

oldest

votes

up vote
3
down vote

Let's say that your csv file is called "list.csv". I would do it like this:

#!/bin/bash
echo -e "Keyobject","Are you sleeping ", " Country of meeting" # create new header
cat list.csv | sed 1,1d | awk -F, 'NF>1print $NF' | paste -d', ' - - | sed 's/ /,/' | nl


 1 yes,USA 
 2 ' ', ' '
 3 yes,CHINA
 4 yes,ITALY

Explanation:

sed 1,1d # This command excludes the original header in list.csv
awk -F, 'NF>1print $NF' # This command reads the last word in each row
paste -d', ' - - # This command paste the words beside each other 
sed 's/ /,/' # This command replaces blank spaces with commas
nl # This command adds numbering to the rows in the final output

However, this solution assumes that the rows in the original csv file are organized as presented in your example above.

edited 3 hours ago

answered 3 hours ago

Goro

6,20152763

add a commentÂ |Â

up vote
3
down vote

Let's say that your csv file is called "list.csv". I would do it like this:

#!/bin/bash
echo -e "Keyobject","Are you sleeping ", " Country of meeting" # create new header
cat list.csv | sed 1,1d | awk -F, 'NF>1print $NF' | paste -d', ' - - | sed 's/ /,/' | nl


 1 yes,USA 
 2 ' ', ' '
 3 yes,CHINA
 4 yes,ITALY

Explanation:

sed 1,1d # This command excludes the original header in list.csv
awk -F, 'NF>1print $NF' # This command reads the last word in each row
paste -d', ' - - # This command paste the words beside each other 
sed 's/ /,/' # This command replaces blank spaces with commas
nl # This command adds numbering to the rows in the final output

However, this solution assumes that the rows in the original csv file are organized as presented in your example above.

edited 3 hours ago

answered 3 hours ago

Goro

6,20152763

add a commentÂ |Â

up vote
3
down vote

Let's say that your csv file is called "list.csv". I would do it like this:

#!/bin/bash
echo -e "Keyobject","Are you sleeping ", " Country of meeting" # create new header
cat list.csv | sed 1,1d | awk -F, 'NF>1print $NF' | paste -d', ' - - | sed 's/ /,/' | nl


 1 yes,USA 
 2 ' ', ' '
 3 yes,CHINA
 4 yes,ITALY

Explanation:

sed 1,1d # This command excludes the original header in list.csv
awk -F, 'NF>1print $NF' # This command reads the last word in each row
paste -d', ' - - # This command paste the words beside each other 
sed 's/ /,/' # This command replaces blank spaces with commas
nl # This command adds numbering to the rows in the final output

However, this solution assumes that the rows in the original csv file are organized as presented in your example above.

edited 3 hours ago

answered 3 hours ago

Goro

6,20152763

Let's say that your csv file is called "list.csv". I would do it like this:

#!/bin/bash
echo -e "Keyobject","Are you sleeping ", " Country of meeting" # create new header
cat list.csv | sed 1,1d | awk -F, 'NF>1print $NF' | paste -d', ' - - | sed 's/ /,/' | nl


 1 yes,USA 
 2 ' ', ' '
 3 yes,CHINA
 4 yes,ITALY

Explanation:

sed 1,1d # This command excludes the original header in list.csv
awk -F, 'NF>1print $NF' # This command reads the last word in each row
paste -d', ' - - # This command paste the words beside each other 
sed 's/ /,/' # This command replaces blank spaces with commas
nl # This command adds numbering to the rows in the final output

However, this solution assumes that the rows in the original csv file are organized as presented in your example above.

edited 3 hours ago

answered 3 hours ago

Goro

6,20152763

edited 3 hours ago

answered 3 hours ago

Goro

6,20152763

answered 3 hours ago

Goro

6,20152763

answered 3 hours ago

Goro

6,20152763

add a commentÂ |Â

up vote
3
down vote

awk -F, '
 NR > 1 ids[$1]; keys[$2]; value[$1,$2] = $3
 END 
 printf "Keyobject"
 for (k in keys)
 printf ",%s", k
 print ""

 for (id in ids) 
 printf id
 for (k in keys)
 printf ",%s", value[id,k]
 print ""
 
 
' file

answered 3 hours ago

glenn jackman

48.3k365105

for(key in array) may iterate through the keys in any order. with original-awk (actually nawk) the lines will be printed 2,3,4,1.
â€“Â mosvy
3 hours ago

@glenn jackman this is brilliant !!
â€“Â TNT
1 hour ago

add a commentÂ |Â

up vote
3
down vote

awk -F, '
 NR > 1 ids[$1]; keys[$2]; value[$1,$2] = $3
 END 
 printf "Keyobject"
 for (k in keys)
 printf ",%s", k
 print ""

 for (id in ids) 
 printf id
 for (k in keys)
 printf ",%s", value[id,k]
 print ""
 
 
' file

answered 3 hours ago

glenn jackman

48.3k365105

for(key in array) may iterate through the keys in any order. with original-awk (actually nawk) the lines will be printed 2,3,4,1.
â€“Â mosvy
3 hours ago

@glenn jackman this is brilliant !!
â€“Â TNT
1 hour ago

add a commentÂ |Â

up vote
3
down vote

awk -F, '
 NR > 1 ids[$1]; keys[$2]; value[$1,$2] = $3
 END 
 printf "Keyobject"
 for (k in keys)
 printf ",%s", k
 print ""

 for (id in ids) 
 printf id
 for (k in keys)
 printf ",%s", value[id,k]
 print ""
 
 
' file

answered 3 hours ago

glenn jackman

48.3k365105

awk -F, '
 NR > 1 ids[$1]; keys[$2]; value[$1,$2] = $3
 END 
 printf "Keyobject"
 for (k in keys)
 printf ",%s", k
 print ""

 for (id in ids) 
 printf id
 for (k in keys)
 printf ",%s", value[id,k]
 print ""
 
 
' file

answered 3 hours ago

glenn jackman

48.3k365105

answered 3 hours ago

glenn jackman

48.3k365105

answered 3 hours ago

glenn jackman

48.3k365105

answered 3 hours ago

glenn jackman

48.3k365105

for(key in array) may iterate through the keys in any order. with original-awk (actually nawk) the lines will be printed 2,3,4,1.
â€“Â mosvy
3 hours ago

@glenn jackman this is brilliant !!
â€“Â TNT
1 hour ago

add a commentÂ |Â

for(key in array) may iterate through the keys in any order. with original-awk (actually nawk) the lines will be printed 2,3,4,1.
â€“Â mosvy
3 hours ago

@glenn jackman this is brilliant !!
â€“Â TNT
1 hour ago

for(key in array) may iterate through the keys in any order. with original-awk (actually nawk) the lines will be printed 2,3,4,1.
â€“Â mosvy
3 hours ago

@glenn jackman this is brilliant !!
â€“Â TNT
1 hour ago

add a commentÂ |Â

up vote
1
down vote

Try also

awk -F, -v OFS="," '
NR == 1 HD = "Keyobject,are you sleeping,country of meeting"
 for (MX=n=split (HD, HDArr, OFS); n>0; n--) SRCH[HDArr[n]]
 print HD
 next
 


$2 in SRCH RES[$2] = $3
 RES[HDArr[1]] = $1
 

NR%2 for (i=1; i<=MX; i++) printf "%s%s", RES[HDArr[i]], (i == MX)?ORS:OFS
 split ("", RES)
 

' file
Keyobject,are you sleeping,country of meeting
1,yes,USA 
2,' ',' '
3,yes,CHINA
4,yes,ITALY

answered 3 hours ago

RudiC

1,6969

add a commentÂ |Â

up vote
1
down vote

Try also

awk -F, -v OFS="," '
NR == 1 HD = "Keyobject,are you sleeping,country of meeting"
 for (MX=n=split (HD, HDArr, OFS); n>0; n--) SRCH[HDArr[n]]
 print HD
 next
 


$2 in SRCH RES[$2] = $3
 RES[HDArr[1]] = $1
 

NR%2 for (i=1; i<=MX; i++) printf "%s%s", RES[HDArr[i]], (i == MX)?ORS:OFS
 split ("", RES)
 

' file
Keyobject,are you sleeping,country of meeting
1,yes,USA 
2,' ',' '
3,yes,CHINA
4,yes,ITALY

answered 3 hours ago

RudiC

1,6969

add a commentÂ |Â

up vote
1
down vote

Try also

awk -F, -v OFS="," '
NR == 1 HD = "Keyobject,are you sleeping,country of meeting"
 for (MX=n=split (HD, HDArr, OFS); n>0; n--) SRCH[HDArr[n]]
 print HD
 next
 


$2 in SRCH RES[$2] = $3
 RES[HDArr[1]] = $1
 

NR%2 for (i=1; i<=MX; i++) printf "%s%s", RES[HDArr[i]], (i == MX)?ORS:OFS
 split ("", RES)
 

' file
Keyobject,are you sleeping,country of meeting
1,yes,USA 
2,' ',' '
3,yes,CHINA
4,yes,ITALY

answered 3 hours ago

RudiC

1,6969

Try also

awk -F, -v OFS="," '
NR == 1 HD = "Keyobject,are you sleeping,country of meeting"
 for (MX=n=split (HD, HDArr, OFS); n>0; n--) SRCH[HDArr[n]]
 print HD
 next
 


$2 in SRCH RES[$2] = $3
 RES[HDArr[1]] = $1
 

NR%2 for (i=1; i<=MX; i++) printf "%s%s", RES[HDArr[i]], (i == MX)?ORS:OFS
 split ("", RES)
 

' file
Keyobject,are you sleeping,country of meeting
1,yes,USA 
2,' ',' '
3,yes,CHINA
4,yes,ITALY

answered 3 hours ago

RudiC

1,6969

answered 3 hours ago

RudiC

1,6969

answered 3 hours ago

RudiC

1,6969

answered 3 hours ago

RudiC

1,6969

add a commentÂ |Â

up vote
1
down vote

awk <file -v IV="' '" -v OV=" " -F, '
 $1+0 
 b = $2 == IV ? OV : $2
 a[$1,$2] = $3 == IV ? OV : $3
 if($1 > max) max = $1; t[$2]++;
 
 END
 ORS = ""
 print "Keyobject"
 for(k in t) print FS k
 print RS
 for(i = 1; i <= max; i++)
 print i
 for(k in t)
 print FS (i SUBSEP k in a ? a[i,k] : OV)
 print RS
 
 
'

Adjust the IV and OV variables for what an empty field should be in the input and output respectively.

edited 3 hours ago

answered 3 hours ago

mosvy

1,752110

add a commentÂ |Â

up vote
1
down vote

awk <file -v IV="' '" -v OV=" " -F, '
 $1+0 
 b = $2 == IV ? OV : $2
 a[$1,$2] = $3 == IV ? OV : $3
 if($1 > max) max = $1; t[$2]++;
 
 END
 ORS = ""
 print "Keyobject"
 for(k in t) print FS k
 print RS
 for(i = 1; i <= max; i++)
 print i
 for(k in t)
 print FS (i SUBSEP k in a ? a[i,k] : OV)
 print RS
 
 
'

Adjust the IV and OV variables for what an empty field should be in the input and output respectively.

edited 3 hours ago

answered 3 hours ago

mosvy

1,752110

add a commentÂ |Â

up vote
1
down vote

awk <file -v IV="' '" -v OV=" " -F, '
 $1+0 
 b = $2 == IV ? OV : $2
 a[$1,$2] = $3 == IV ? OV : $3
 if($1 > max) max = $1; t[$2]++;
 
 END
 ORS = ""
 print "Keyobject"
 for(k in t) print FS k
 print RS
 for(i = 1; i <= max; i++)
 print i
 for(k in t)
 print FS (i SUBSEP k in a ? a[i,k] : OV)
 print RS
 
 
'

Adjust the IV and OV variables for what an empty field should be in the input and output respectively.

edited 3 hours ago

answered 3 hours ago

mosvy

1,752110

awk <file -v IV="' '" -v OV=" " -F, '
 $1+0 
 b = $2 == IV ? OV : $2
 a[$1,$2] = $3 == IV ? OV : $3
 if($1 > max) max = $1; t[$2]++;
 
 END
 ORS = ""
 print "Keyobject"
 for(k in t) print FS k
 print RS
 for(i = 1; i <= max; i++)
 print i
 for(k in t)
 print FS (i SUBSEP k in a ? a[i,k] : OV)
 print RS
 
 
'

Adjust the IV and OV variables for what an empty field should be in the input and output respectively.

edited 3 hours ago

answered 3 hours ago

mosvy

1,752110

edited 3 hours ago

answered 3 hours ago

mosvy

1,752110

answered 3 hours ago

mosvy

1,752110

answered 3 hours ago

mosvy

1,752110

add a commentÂ |Â

Dinesh Boora is a new contributor. Be nice, and check out our Code of Conduct.

draft saved

draft discarded

Dinesh Boora is a new contributor. Be nice, and check out our Code of Conduct.

draft saved

draft discarded

Post as a guest

Name

Search This Blog

Iyfjky