how to merge two columns to one column with the repeating word on column2 as a header
up vote
0
down vote
favorite
i've been trying to figure this for a couple of hours now, here's what i need
my table:
- 161.226.252.55 NY
- 171.226.252.60 NY
- 131.226.252.50 NY
- 150.178.157.16 LA
- 162.163.137.23 LA
- 142.163.137.27 WY
- 182.163.137.22 NL
desired output:
- NY
- 161.226.252.55
- 171.226.252.60
- 131.226.252.50
- LA
- 150.178.157.16
- and so on...
sorry for the bad format, it's my first question, thanks
bash
add a comment |
up vote
0
down vote
favorite
i've been trying to figure this for a couple of hours now, here's what i need
my table:
- 161.226.252.55 NY
- 171.226.252.60 NY
- 131.226.252.50 NY
- 150.178.157.16 LA
- 162.163.137.23 LA
- 142.163.137.27 WY
- 182.163.137.22 NL
desired output:
- NY
- 161.226.252.55
- 171.226.252.60
- 131.226.252.50
- LA
- 150.178.157.16
- and so on...
sorry for the bad format, it's my first question, thanks
bash
Welcome to SO. Please provide a Minimal, Complete, and Verifiable example. Show us the code for your latest attempt and where you got stuck. and explain why the result is not what you expected. Edit your question to include the code, please don't add it in a comment, as it will probably be unreadable. stackoverflow.com/help/mcve
– Dragonthoughts
Nov 22 at 11:54
i tried various grep/sort/gawk/awk, the list is great, i'm stuck in the part that brings the duplicate word from column 2 and sorts beneath it the matching rows
– locutos
Nov 22 at 11:59
1
Please show us your code, if you would like assistance.
– Dragonthoughts
Nov 22 at 12:00
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
i've been trying to figure this for a couple of hours now, here's what i need
my table:
- 161.226.252.55 NY
- 171.226.252.60 NY
- 131.226.252.50 NY
- 150.178.157.16 LA
- 162.163.137.23 LA
- 142.163.137.27 WY
- 182.163.137.22 NL
desired output:
- NY
- 161.226.252.55
- 171.226.252.60
- 131.226.252.50
- LA
- 150.178.157.16
- and so on...
sorry for the bad format, it's my first question, thanks
bash
i've been trying to figure this for a couple of hours now, here's what i need
my table:
- 161.226.252.55 NY
- 171.226.252.60 NY
- 131.226.252.50 NY
- 150.178.157.16 LA
- 162.163.137.23 LA
- 142.163.137.27 WY
- 182.163.137.22 NL
desired output:
- NY
- 161.226.252.55
- 171.226.252.60
- 131.226.252.50
- LA
- 150.178.157.16
- and so on...
sorry for the bad format, it's my first question, thanks
bash
bash
edited Nov 22 at 15:33
RavinderSingh13
25.1k41437
25.1k41437
asked Nov 22 at 11:38
locutos
31
31
Welcome to SO. Please provide a Minimal, Complete, and Verifiable example. Show us the code for your latest attempt and where you got stuck. and explain why the result is not what you expected. Edit your question to include the code, please don't add it in a comment, as it will probably be unreadable. stackoverflow.com/help/mcve
– Dragonthoughts
Nov 22 at 11:54
i tried various grep/sort/gawk/awk, the list is great, i'm stuck in the part that brings the duplicate word from column 2 and sorts beneath it the matching rows
– locutos
Nov 22 at 11:59
1
Please show us your code, if you would like assistance.
– Dragonthoughts
Nov 22 at 12:00
add a comment |
Welcome to SO. Please provide a Minimal, Complete, and Verifiable example. Show us the code for your latest attempt and where you got stuck. and explain why the result is not what you expected. Edit your question to include the code, please don't add it in a comment, as it will probably be unreadable. stackoverflow.com/help/mcve
– Dragonthoughts
Nov 22 at 11:54
i tried various grep/sort/gawk/awk, the list is great, i'm stuck in the part that brings the duplicate word from column 2 and sorts beneath it the matching rows
– locutos
Nov 22 at 11:59
1
Please show us your code, if you would like assistance.
– Dragonthoughts
Nov 22 at 12:00
Welcome to SO. Please provide a Minimal, Complete, and Verifiable example. Show us the code for your latest attempt and where you got stuck. and explain why the result is not what you expected. Edit your question to include the code, please don't add it in a comment, as it will probably be unreadable. stackoverflow.com/help/mcve
– Dragonthoughts
Nov 22 at 11:54
Welcome to SO. Please provide a Minimal, Complete, and Verifiable example. Show us the code for your latest attempt and where you got stuck. and explain why the result is not what you expected. Edit your question to include the code, please don't add it in a comment, as it will probably be unreadable. stackoverflow.com/help/mcve
– Dragonthoughts
Nov 22 at 11:54
i tried various grep/sort/gawk/awk, the list is great, i'm stuck in the part that brings the duplicate word from column 2 and sorts beneath it the matching rows
– locutos
Nov 22 at 11:59
i tried various grep/sort/gawk/awk, the list is great, i'm stuck in the part that brings the duplicate word from column 2 and sorts beneath it the matching rows
– locutos
Nov 22 at 11:59
1
1
Please show us your code, if you would like assistance.
– Dragonthoughts
Nov 22 at 12:00
Please show us your code, if you would like assistance.
– Dragonthoughts
Nov 22 at 12:00
add a comment |
3 Answers
3
active
oldest
votes
up vote
0
down vote
accepted
AWK solution using associative array:
awk '{ ips[$2]=ips[$2] $1 RS; } END { for ( i in ips ) printf("%s%s%s",i,RS,ips[i]); }' table
Output:
WY
142.163.137.27
NL
182.163.137.22
LA
150.178.157.16
162.163.137.23
NY
161.226.252.55
171.226.252.60
131.226.252.50
In case You need order as in Your provided output:
awk '!ips[$2] { ipso[++order]=$2; } { ips[$2]=ips[$2] $1 RS; } END { for (i=1;i<=order;i++) printf("%s%s%s",ipso[i],RS,ips[ipso[i]]); }' table
Output:
NY
161.226.252.55
171.226.252.60
131.226.252.50
LA
150.178.157.16
162.163.137.23
WY
142.163.137.27
NL
182.163.137.22
thanks a lot for the reply, it worked. my bad but i forgot to mention that my columns has titles, and it shows up above the location, how can i remove them and only them?
– locutos
Nov 22 at 12:26
in case you have one header just add condition NR>1. Like: awk ' NR>1 { ips[$2]=ips[$2] $1 RS; } END { for ( i in ips ) printf("%s%s%s",i,RS,ips[i]); }' table_headers
– Kubator
Nov 22 at 12:50
add a comment |
up vote
0
down vote
You said you tried gawk/awk, you can create an array, in fact, a hashtable in awk, the key is your 2nd column. The values are those ips. When you put value in it, you check if there is value with that key, if true, append to the existing value. Finally, you can loop through the array, print the keys and values out, it should be in your desired format.
1
This does not provide an answer to the question. To critique or request clarification from an author, leave a comment below their post. - From Review
– hivert
Nov 22 at 14:05
@hivert this is not a comment. it is exactly an answer to the question, even if it doesn't contain any codes. It explainshow to merge two columns to one column with the repeating word on column2 as a header
– Kent
Nov 22 at 14:16
add a comment |
up vote
0
down vote
Could you please try following, it will give you output in same order in which ids are present in Input_file then try following.
awk '
!a[$NF]++{
b[++count]=$NF
}
{
c[$NF]=c[$NF]?c[$NF] ORS $1:$1
}
END{
for(i=1;i<=count;i++){
print b[i] ORS c[b[i]]
}
}' Input_file
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
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: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53430154%2fhow-to-merge-two-columns-to-one-column-with-the-repeating-word-on-column2-as-a-h%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
0
down vote
accepted
AWK solution using associative array:
awk '{ ips[$2]=ips[$2] $1 RS; } END { for ( i in ips ) printf("%s%s%s",i,RS,ips[i]); }' table
Output:
WY
142.163.137.27
NL
182.163.137.22
LA
150.178.157.16
162.163.137.23
NY
161.226.252.55
171.226.252.60
131.226.252.50
In case You need order as in Your provided output:
awk '!ips[$2] { ipso[++order]=$2; } { ips[$2]=ips[$2] $1 RS; } END { for (i=1;i<=order;i++) printf("%s%s%s",ipso[i],RS,ips[ipso[i]]); }' table
Output:
NY
161.226.252.55
171.226.252.60
131.226.252.50
LA
150.178.157.16
162.163.137.23
WY
142.163.137.27
NL
182.163.137.22
thanks a lot for the reply, it worked. my bad but i forgot to mention that my columns has titles, and it shows up above the location, how can i remove them and only them?
– locutos
Nov 22 at 12:26
in case you have one header just add condition NR>1. Like: awk ' NR>1 { ips[$2]=ips[$2] $1 RS; } END { for ( i in ips ) printf("%s%s%s",i,RS,ips[i]); }' table_headers
– Kubator
Nov 22 at 12:50
add a comment |
up vote
0
down vote
accepted
AWK solution using associative array:
awk '{ ips[$2]=ips[$2] $1 RS; } END { for ( i in ips ) printf("%s%s%s",i,RS,ips[i]); }' table
Output:
WY
142.163.137.27
NL
182.163.137.22
LA
150.178.157.16
162.163.137.23
NY
161.226.252.55
171.226.252.60
131.226.252.50
In case You need order as in Your provided output:
awk '!ips[$2] { ipso[++order]=$2; } { ips[$2]=ips[$2] $1 RS; } END { for (i=1;i<=order;i++) printf("%s%s%s",ipso[i],RS,ips[ipso[i]]); }' table
Output:
NY
161.226.252.55
171.226.252.60
131.226.252.50
LA
150.178.157.16
162.163.137.23
WY
142.163.137.27
NL
182.163.137.22
thanks a lot for the reply, it worked. my bad but i forgot to mention that my columns has titles, and it shows up above the location, how can i remove them and only them?
– locutos
Nov 22 at 12:26
in case you have one header just add condition NR>1. Like: awk ' NR>1 { ips[$2]=ips[$2] $1 RS; } END { for ( i in ips ) printf("%s%s%s",i,RS,ips[i]); }' table_headers
– Kubator
Nov 22 at 12:50
add a comment |
up vote
0
down vote
accepted
up vote
0
down vote
accepted
AWK solution using associative array:
awk '{ ips[$2]=ips[$2] $1 RS; } END { for ( i in ips ) printf("%s%s%s",i,RS,ips[i]); }' table
Output:
WY
142.163.137.27
NL
182.163.137.22
LA
150.178.157.16
162.163.137.23
NY
161.226.252.55
171.226.252.60
131.226.252.50
In case You need order as in Your provided output:
awk '!ips[$2] { ipso[++order]=$2; } { ips[$2]=ips[$2] $1 RS; } END { for (i=1;i<=order;i++) printf("%s%s%s",ipso[i],RS,ips[ipso[i]]); }' table
Output:
NY
161.226.252.55
171.226.252.60
131.226.252.50
LA
150.178.157.16
162.163.137.23
WY
142.163.137.27
NL
182.163.137.22
AWK solution using associative array:
awk '{ ips[$2]=ips[$2] $1 RS; } END { for ( i in ips ) printf("%s%s%s",i,RS,ips[i]); }' table
Output:
WY
142.163.137.27
NL
182.163.137.22
LA
150.178.157.16
162.163.137.23
NY
161.226.252.55
171.226.252.60
131.226.252.50
In case You need order as in Your provided output:
awk '!ips[$2] { ipso[++order]=$2; } { ips[$2]=ips[$2] $1 RS; } END { for (i=1;i<=order;i++) printf("%s%s%s",ipso[i],RS,ips[ipso[i]]); }' table
Output:
NY
161.226.252.55
171.226.252.60
131.226.252.50
LA
150.178.157.16
162.163.137.23
WY
142.163.137.27
NL
182.163.137.22
edited Nov 22 at 13:40
answered Nov 22 at 12:17
Kubator
5579
5579
thanks a lot for the reply, it worked. my bad but i forgot to mention that my columns has titles, and it shows up above the location, how can i remove them and only them?
– locutos
Nov 22 at 12:26
in case you have one header just add condition NR>1. Like: awk ' NR>1 { ips[$2]=ips[$2] $1 RS; } END { for ( i in ips ) printf("%s%s%s",i,RS,ips[i]); }' table_headers
– Kubator
Nov 22 at 12:50
add a comment |
thanks a lot for the reply, it worked. my bad but i forgot to mention that my columns has titles, and it shows up above the location, how can i remove them and only them?
– locutos
Nov 22 at 12:26
in case you have one header just add condition NR>1. Like: awk ' NR>1 { ips[$2]=ips[$2] $1 RS; } END { for ( i in ips ) printf("%s%s%s",i,RS,ips[i]); }' table_headers
– Kubator
Nov 22 at 12:50
thanks a lot for the reply, it worked. my bad but i forgot to mention that my columns has titles, and it shows up above the location, how can i remove them and only them?
– locutos
Nov 22 at 12:26
thanks a lot for the reply, it worked. my bad but i forgot to mention that my columns has titles, and it shows up above the location, how can i remove them and only them?
– locutos
Nov 22 at 12:26
in case you have one header just add condition NR>1. Like: awk ' NR>1 { ips[$2]=ips[$2] $1 RS; } END { for ( i in ips ) printf("%s%s%s",i,RS,ips[i]); }' table_headers
– Kubator
Nov 22 at 12:50
in case you have one header just add condition NR>1. Like: awk ' NR>1 { ips[$2]=ips[$2] $1 RS; } END { for ( i in ips ) printf("%s%s%s",i,RS,ips[i]); }' table_headers
– Kubator
Nov 22 at 12:50
add a comment |
up vote
0
down vote
You said you tried gawk/awk, you can create an array, in fact, a hashtable in awk, the key is your 2nd column. The values are those ips. When you put value in it, you check if there is value with that key, if true, append to the existing value. Finally, you can loop through the array, print the keys and values out, it should be in your desired format.
1
This does not provide an answer to the question. To critique or request clarification from an author, leave a comment below their post. - From Review
– hivert
Nov 22 at 14:05
@hivert this is not a comment. it is exactly an answer to the question, even if it doesn't contain any codes. It explainshow to merge two columns to one column with the repeating word on column2 as a header
– Kent
Nov 22 at 14:16
add a comment |
up vote
0
down vote
You said you tried gawk/awk, you can create an array, in fact, a hashtable in awk, the key is your 2nd column. The values are those ips. When you put value in it, you check if there is value with that key, if true, append to the existing value. Finally, you can loop through the array, print the keys and values out, it should be in your desired format.
1
This does not provide an answer to the question. To critique or request clarification from an author, leave a comment below their post. - From Review
– hivert
Nov 22 at 14:05
@hivert this is not a comment. it is exactly an answer to the question, even if it doesn't contain any codes. It explainshow to merge two columns to one column with the repeating word on column2 as a header
– Kent
Nov 22 at 14:16
add a comment |
up vote
0
down vote
up vote
0
down vote
You said you tried gawk/awk, you can create an array, in fact, a hashtable in awk, the key is your 2nd column. The values are those ips. When you put value in it, you check if there is value with that key, if true, append to the existing value. Finally, you can loop through the array, print the keys and values out, it should be in your desired format.
You said you tried gawk/awk, you can create an array, in fact, a hashtable in awk, the key is your 2nd column. The values are those ips. When you put value in it, you check if there is value with that key, if true, append to the existing value. Finally, you can loop through the array, print the keys and values out, it should be in your desired format.
answered Nov 22 at 12:14
Kent
143k25152213
143k25152213
1
This does not provide an answer to the question. To critique or request clarification from an author, leave a comment below their post. - From Review
– hivert
Nov 22 at 14:05
@hivert this is not a comment. it is exactly an answer to the question, even if it doesn't contain any codes. It explainshow to merge two columns to one column with the repeating word on column2 as a header
– Kent
Nov 22 at 14:16
add a comment |
1
This does not provide an answer to the question. To critique or request clarification from an author, leave a comment below their post. - From Review
– hivert
Nov 22 at 14:05
@hivert this is not a comment. it is exactly an answer to the question, even if it doesn't contain any codes. It explainshow to merge two columns to one column with the repeating word on column2 as a header
– Kent
Nov 22 at 14:16
1
1
This does not provide an answer to the question. To critique or request clarification from an author, leave a comment below their post. - From Review
– hivert
Nov 22 at 14:05
This does not provide an answer to the question. To critique or request clarification from an author, leave a comment below their post. - From Review
– hivert
Nov 22 at 14:05
@hivert this is not a comment. it is exactly an answer to the question, even if it doesn't contain any codes. It explains
how to merge two columns to one column with the repeating word on column2 as a header
– Kent
Nov 22 at 14:16
@hivert this is not a comment. it is exactly an answer to the question, even if it doesn't contain any codes. It explains
how to merge two columns to one column with the repeating word on column2 as a header
– Kent
Nov 22 at 14:16
add a comment |
up vote
0
down vote
Could you please try following, it will give you output in same order in which ids are present in Input_file then try following.
awk '
!a[$NF]++{
b[++count]=$NF
}
{
c[$NF]=c[$NF]?c[$NF] ORS $1:$1
}
END{
for(i=1;i<=count;i++){
print b[i] ORS c[b[i]]
}
}' Input_file
add a comment |
up vote
0
down vote
Could you please try following, it will give you output in same order in which ids are present in Input_file then try following.
awk '
!a[$NF]++{
b[++count]=$NF
}
{
c[$NF]=c[$NF]?c[$NF] ORS $1:$1
}
END{
for(i=1;i<=count;i++){
print b[i] ORS c[b[i]]
}
}' Input_file
add a comment |
up vote
0
down vote
up vote
0
down vote
Could you please try following, it will give you output in same order in which ids are present in Input_file then try following.
awk '
!a[$NF]++{
b[++count]=$NF
}
{
c[$NF]=c[$NF]?c[$NF] ORS $1:$1
}
END{
for(i=1;i<=count;i++){
print b[i] ORS c[b[i]]
}
}' Input_file
Could you please try following, it will give you output in same order in which ids are present in Input_file then try following.
awk '
!a[$NF]++{
b[++count]=$NF
}
{
c[$NF]=c[$NF]?c[$NF] ORS $1:$1
}
END{
for(i=1;i<=count;i++){
print b[i] ORS c[b[i]]
}
}' Input_file
answered Nov 22 at 13:11
RavinderSingh13
25.1k41437
25.1k41437
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53430154%2fhow-to-merge-two-columns-to-one-column-with-the-repeating-word-on-column2-as-a-h%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Welcome to SO. Please provide a Minimal, Complete, and Verifiable example. Show us the code for your latest attempt and where you got stuck. and explain why the result is not what you expected. Edit your question to include the code, please don't add it in a comment, as it will probably be unreadable. stackoverflow.com/help/mcve
– Dragonthoughts
Nov 22 at 11:54
i tried various grep/sort/gawk/awk, the list is great, i'm stuck in the part that brings the duplicate word from column 2 and sorts beneath it the matching rows
– locutos
Nov 22 at 11:59
1
Please show us your code, if you would like assistance.
– Dragonthoughts
Nov 22 at 12:00