Mixing
Group rows in one file based on column value
Clash Royale CLAN TAG#URR8PPP
up vote
3
down vote
favorite
I have "csv" spreadsheet as below
abc,12345,qwerty,A
xyz,12380,qwetty,R
abc,12389,qwerty,A
xyz,12324,qwetty,R
I would like to combine rows with similar values in columns 1, 2 and 4. Also, i would like to replace the last two alphabets of column 3 by "**". Sample of the output is as below:
abc,123**,qwerty,A
abc,123**,qwerty,A
xyz,123**,qwetty,R
xyz,123**,qwetty,R
Total number of lines is more than one Million!
shell-script
edited 7 hours ago
RalfFriedl
3,7351623
asked 7 hours ago
Arpit
161
New contributor
Arpit is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
 |Â
show 3 more comments
up vote
3
down vote
favorite
I have "csv" spreadsheet as below
abc,12345,qwerty,A
xyz,12380,qwetty,R
abc,12389,qwerty,A
xyz,12324,qwetty,R
I would like to combine rows with similar values in columns 1, 2 and 4. Also, i would like to replace the last two alphabets of column 3 by "**". Sample of the output is as below:
abc,123**,qwerty,A
abc,123**,qwerty,A
xyz,123**,qwetty,R
xyz,123**,qwetty,R
Total number of lines is more than one Million!
shell-script
edited 7 hours ago
RalfFriedl
3,7351623
asked 7 hours ago
Arpit
161
New contributor
Arpit is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Hi @Arpit. You want to ` combine rows with similar values in columns 1, 2 and 4` or organize the rows` OR 'combine rows with similar values in columns 1 or 2 or 4`? thanks for any additional clarifications!
– Goro
7 hours ago
@Goro, i want to organize rows with similar value. Its re arranging rows with similar values.Thanks for correction.
– Arpit
7 hours ago
There is a difference between organizing the rows by one similar value or multiple similar values?
– Goro
7 hours ago
Yes. My requirement to organize based on multiple similar values. To be more specific, all rows with exactly same value in column 1,3 and 4 should be condition, based on which rows are arranged together. And then just replace last 2 digit of row 2 by *
– Arpit
7 hours ago
You can simply run the commandsorton the file that you would like to organize, just sort file ;-)
– Goro
7 hours ago
 |Â
show 3 more comments
up vote
3
down vote
favorite
up vote
3
down vote
favorite
I have "csv" spreadsheet as below
abc,12345,qwerty,A
xyz,12380,qwetty,R
abc,12389,qwerty,A
xyz,12324,qwetty,R
I would like to combine rows with similar values in columns 1, 2 and 4. Also, i would like to replace the last two alphabets of column 3 by "**". Sample of the output is as below:
abc,123**,qwerty,A
abc,123**,qwerty,A
xyz,123**,qwetty,R
xyz,123**,qwetty,R
Total number of lines is more than one Million!
shell-script
edited 7 hours ago
RalfFriedl
3,7351623
asked 7 hours ago
Arpit
161
New contributor
Arpit is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
I have "csv" spreadsheet as below
abc,12345,qwerty,A
xyz,12380,qwetty,R
abc,12389,qwerty,A
xyz,12324,qwetty,R
I would like to combine rows with similar values in columns 1, 2 and 4. Also, i would like to replace the last two alphabets of column 3 by "**". Sample of the output is as below:
abc,123**,qwerty,A
abc,123**,qwerty,A
xyz,123**,qwetty,R
xyz,123**,qwetty,R
Total number of lines is more than one Million!
shell-script
shell-script
edited 7 hours ago
RalfFriedl
3,7351623
asked 7 hours ago
Arpit
161
New contributor
Arpit is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
edited 7 hours ago
RalfFriedl
3,7351623
asked 7 hours ago
Arpit
161
New contributor
Arpit is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
edited 7 hours ago
RalfFriedl
3,7351623
edited 7 hours ago
RalfFriedl
3,7351623
edited 7 hours ago
RalfFriedl
3,7351623
3,7351623
asked 7 hours ago
Arpit
161
New contributor
Arpit is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 7 hours ago
Arpit
161
asked 7 hours ago
Arpit
161
161
New contributor
Arpit is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
Arpit is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Arpit is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Hi @Arpit. You want to ` combine rows with similar values in columns 1, 2 and 4` or organize the rows` OR 'combine rows with similar values in columns 1 or 2 or 4`? thanks for any additional clarifications!
– Goro
7 hours ago
@Goro, i want to organize rows with similar value. Its re arranging rows with similar values.Thanks for correction.
– Arpit
7 hours ago
There is a difference between organizing the rows by one similar value or multiple similar values?
– Goro
7 hours ago
Yes. My requirement to organize based on multiple similar values. To be more specific, all rows with exactly same value in column 1,3 and 4 should be condition, based on which rows are arranged together. And then just replace last 2 digit of row 2 by *
– Arpit
7 hours ago
You can simply run the commandsorton the file that you would like to organize, just sort file ;-)
– Goro
7 hours ago
 |Â
show 3 more comments
Hi @Arpit. You want to ` combine rows with similar values in columns 1, 2 and 4` or organize the rows` OR 'combine rows with similar values in columns 1 or 2 or 4`? thanks for any additional clarifications!
– Goro
7 hours ago
@Goro, i want to organize rows with similar value. Its re arranging rows with similar values.Thanks for correction.
– Arpit
7 hours ago
There is a difference between organizing the rows by one similar value or multiple similar values?
– Goro
7 hours ago
Yes. My requirement to organize based on multiple similar values. To be more specific, all rows with exactly same value in column 1,3 and 4 should be condition, based on which rows are arranged together. And then just replace last 2 digit of row 2 by *
– Arpit
7 hours ago
You can simply run the commandsorton the file that you would like to organize, just sort file ;-)
– Goro
7 hours ago
Hi @Arpit. You want to ` combine rows with similar values in columns 1, 2 and 4` or organize the rows` OR 'combine rows with similar values in columns 1 or 2 or 4`? thanks for any additional clarifications!
– Goro
7 hours ago
Hi @Arpit. You want to ` combine rows with similar values in columns 1, 2 and 4` or organize the rows` OR 'combine rows with similar values in columns 1 or 2 or 4`? thanks for any additional clarifications!
– Goro
7 hours ago
@Goro, i want to organize rows with similar value. Its re arranging rows with similar values.Thanks for correction.
– Arpit
7 hours ago
@Goro, i want to organize rows with similar value. Its re arranging rows with similar values.Thanks for correction.
– Arpit
7 hours ago
There is a difference between organizing the rows by one similar value or multiple similar values?
– Goro
7 hours ago
There is a difference between organizing the rows by one similar value or multiple similar values?
– Goro
7 hours ago
Yes. My requirement to organize based on multiple similar values. To be more specific, all rows with exactly same value in column 1,3 and 4 should be condition, based on which rows are arranged together. And then just replace last 2 digit of row 2 by *
– Arpit
7 hours ago
Yes. My requirement to organize based on multiple similar values. To be more specific, all rows with exactly same value in column 1,3 and 4 should be condition, based on which rows are arranged together. And then just replace last 2 digit of row 2 by *
– Arpit
7 hours ago
You can simply run the command
sort on the file that you would like to organize, just sort file ;-)– Goro
7 hours ago
You can simply run the command
sort on the file that you would like to organize, just sort file ;-)– Goro
7 hours ago
 |Â
show 3 more comments
2 Answers
2
active
oldest
votes
up vote
5
down vote
To sort your file first by col1 then by col2 then by col4:
$ sort -t, -k1,1 -k2,2 -k4,4 file
abc,12345,qwerty,A
abc,12389,qwerty,A
xyz,12324,qwetty,R
xyz,12380,qwetty,R
Then to obfuscate the 2nd field, you could do
$ sort -t, -k1,1 -k2,2 -k4,4 file | sed 's/..,/**,/2'
abc,123**,qwerty,A
abc,123**,qwerty,A
xyz,123**,qwetty,R
xyz,123**,qwetty,R
answered 6 hours ago
glenn jackman
47.7k265104
add a comment |Â
up vote
4
down vote
sort file | awk -F',' ' sub(/..$/,"**",$2) 1' OFS=','
abc,123**,qwerty,A
abc,123**,qwerty,A
xyz,123**,qwetty,R
xyz,123**,qwetty,R
In case you would like to change the comma separator to tab. you can use this command:
sort file | sed 's/,/t/g' | awk -F't' ' sub(/..$/,"**",$2) 1' OFS='t'
abc 123** qwerty A
abc 123** qwerty A
xyz 123** qwetty R
xyz 123** qwetty R
answered 6 hours ago
Goro
3,37441951
2
why are you changing the separator to tab? The question seems pretty clear about commas.
– glenn jackman
4 hours ago
@glenn jackman. Correct! It seems I missed the first part when wrote the answer quickly. Thank you for catching that! ;-)
– Goro
4 hours ago
add a comment |Â
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
5
down vote
To sort your file first by col1 then by col2 then by col4:
$ sort -t, -k1,1 -k2,2 -k4,4 file
abc,12345,qwerty,A
abc,12389,qwerty,A
xyz,12324,qwetty,R
xyz,12380,qwetty,R
Then to obfuscate the 2nd field, you could do
$ sort -t, -k1,1 -k2,2 -k4,4 file | sed 's/..,/**,/2'
abc,123**,qwerty,A
abc,123**,qwerty,A
xyz,123**,qwetty,R
xyz,123**,qwetty,R
answered 6 hours ago
glenn jackman
47.7k265104
add a comment |Â
up vote
5
down vote
To sort your file first by col1 then by col2 then by col4:
$ sort -t, -k1,1 -k2,2 -k4,4 file
abc,12345,qwerty,A
abc,12389,qwerty,A
xyz,12324,qwetty,R
xyz,12380,qwetty,R
Then to obfuscate the 2nd field, you could do
$ sort -t, -k1,1 -k2,2 -k4,4 file | sed 's/..,/**,/2'
abc,123**,qwerty,A
abc,123**,qwerty,A
xyz,123**,qwetty,R
xyz,123**,qwetty,R
answered 6 hours ago
glenn jackman
47.7k265104
add a comment |Â
up vote
5
down vote
up vote
5
down vote
To sort your file first by col1 then by col2 then by col4:
$ sort -t, -k1,1 -k2,2 -k4,4 file
abc,12345,qwerty,A
abc,12389,qwerty,A
xyz,12324,qwetty,R
xyz,12380,qwetty,R
Then to obfuscate the 2nd field, you could do
$ sort -t, -k1,1 -k2,2 -k4,4 file | sed 's/..,/**,/2'
abc,123**,qwerty,A
abc,123**,qwerty,A
xyz,123**,qwetty,R
xyz,123**,qwetty,R
answered 6 hours ago
glenn jackman
47.7k265104
To sort your file first by col1 then by col2 then by col4:
$ sort -t, -k1,1 -k2,2 -k4,4 file
abc,12345,qwerty,A
abc,12389,qwerty,A
xyz,12324,qwetty,R
xyz,12380,qwetty,R
Then to obfuscate the 2nd field, you could do
$ sort -t, -k1,1 -k2,2 -k4,4 file | sed 's/..,/**,/2'
abc,123**,qwerty,A
abc,123**,qwerty,A
xyz,123**,qwetty,R
xyz,123**,qwetty,R
answered 6 hours ago
glenn jackman
47.7k265104
answered 6 hours ago
glenn jackman
47.7k265104
answered 6 hours ago
glenn jackman
47.7k265104
answered 6 hours ago
glenn jackman
47.7k265104
47.7k265104
add a comment |Â
add a comment |Â
up vote
4
down vote
sort file | awk -F',' ' sub(/..$/,"**",$2) 1' OFS=','
abc,123**,qwerty,A
abc,123**,qwerty,A
xyz,123**,qwetty,R
xyz,123**,qwetty,R
In case you would like to change the comma separator to tab. you can use this command:
sort file | sed 's/,/t/g' | awk -F't' ' sub(/..$/,"**",$2) 1' OFS='t'
abc 123** qwerty A
abc 123** qwerty A
xyz 123** qwetty R
xyz 123** qwetty R
answered 6 hours ago
Goro
3,37441951
2
why are you changing the separator to tab? The question seems pretty clear about commas.
– glenn jackman
4 hours ago
@glenn jackman. Correct! It seems I missed the first part when wrote the answer quickly. Thank you for catching that! ;-)
– Goro
4 hours ago
add a comment |Â
up vote
4
down vote
sort file | awk -F',' ' sub(/..$/,"**",$2) 1' OFS=','
abc,123**,qwerty,A
abc,123**,qwerty,A
xyz,123**,qwetty,R
xyz,123**,qwetty,R
In case you would like to change the comma separator to tab. you can use this command:
sort file | sed 's/,/t/g' | awk -F't' ' sub(/..$/,"**",$2) 1' OFS='t'
abc 123** qwerty A
abc 123** qwerty A
xyz 123** qwetty R
xyz 123** qwetty R
answered 6 hours ago
Goro
3,37441951
2
why are you changing the separator to tab? The question seems pretty clear about commas.
– glenn jackman
4 hours ago
@glenn jackman. Correct! It seems I missed the first part when wrote the answer quickly. Thank you for catching that! ;-)
– Goro
4 hours ago
add a comment |Â
up vote
4
down vote
up vote
4
down vote
sort file | awk -F',' ' sub(/..$/,"**",$2) 1' OFS=','
abc,123**,qwerty,A
abc,123**,qwerty,A
xyz,123**,qwetty,R
xyz,123**,qwetty,R
In case you would like to change the comma separator to tab. you can use this command:
sort file | sed 's/,/t/g' | awk -F't' ' sub(/..$/,"**",$2) 1' OFS='t'
abc 123** qwerty A
abc 123** qwerty A
xyz 123** qwetty R
xyz 123** qwetty R
answered 6 hours ago
Goro
3,37441951
sort file | awk -F',' ' sub(/..$/,"**",$2) 1' OFS=','
abc,123**,qwerty,A
abc,123**,qwerty,A
xyz,123**,qwetty,R
xyz,123**,qwetty,R
In case you would like to change the comma separator to tab. you can use this command:
sort file | sed 's/,/t/g' | awk -F't' ' sub(/..$/,"**",$2) 1' OFS='t'
abc 123** qwerty A
abc 123** qwerty A
xyz 123** qwetty R
xyz 123** qwetty R
answered 6 hours ago
Goro
3,37441951
edited 4 hours ago
answered 6 hours ago
Goro
3,37441951
answered 6 hours ago
Goro
3,37441951
answered 6 hours ago
Goro
3,37441951
3,37441951
2
why are you changing the separator to tab? The question seems pretty clear about commas.
– glenn jackman
4 hours ago
@glenn jackman. Correct! It seems I missed the first part when wrote the answer quickly. Thank you for catching that! ;-)
– Goro
4 hours ago
add a comment |Â
2
why are you changing the separator to tab? The question seems pretty clear about commas.
– glenn jackman
4 hours ago
@glenn jackman. Correct! It seems I missed the first part when wrote the answer quickly. Thank you for catching that! ;-)
– Goro
4 hours ago
2
2
why are you changing the separator to tab? The question seems pretty clear about commas.
– glenn jackman
4 hours ago
why are you changing the separator to tab? The question seems pretty clear about commas.
– glenn jackman
4 hours ago
@glenn jackman. Correct! It seems I missed the first part when wrote the answer quickly. Thank you for catching that! ;-)
– Goro
4 hours ago
@glenn jackman. Correct! It seems I missed the first part when wrote the answer quickly. Thank you for catching that! ;-)
– Goro
4 hours ago
add a comment |Â
Arpit is a new contributor. Be nice, and check out our Code of Conduct.
Â
draft saved
draft discarded
Arpit is a new contributor. Be nice, and check out our Code of Conduct.
Arpit is a new contributor. Be nice, and check out our Code of Conduct.
Arpit is a new contributor. Be nice, and check out our Code of Conduct.
Â
draft saved
draft discarded
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
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2funix.stackexchange.com%2fquestions%2f470335%2fgroup-rows-in-one-file-based-on-column-value%23new-answer', 'question_page');
);
Post as a guest
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
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
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
Hi @Arpit. You want to ` combine rows with similar values in columns 1, 2 and 4` or organize the rows` OR 'combine rows with similar values in columns 1 or 2 or 4`? thanks for any additional clarifications!
– Goro
7 hours ago
@Goro, i want to organize rows with similar value. Its re arranging rows with similar values.Thanks for correction.
– Arpit
7 hours ago
There is a difference between organizing the rows by one similar value or multiple similar values?
– Goro
7 hours ago
Yes. My requirement to organize based on multiple similar values. To be more specific, all rows with exactly same value in column 1,3 and 4 should be condition, based on which rows are arranged together. And then just replace last 2 digit of row 2 by *
– Arpit
7 hours ago
You can simply run the command
sorton the file that you would like to organize, just sort file ;-)– Goro
7 hours ago