I have a csv file like below
a,123,xyz
a,345,zyx
b,123,xyz
b,345,zyx
I would like to merge the first column with same value in the row. like below
a,123,xyz
345,zyx
b,123,xyz
345,zyx
I have sorted the file and tried to count the values but not able to proceed as I need to do in shell script
You can obtain the desired output with the following awk snippet:
awk -F, '{ if (f == $1) { for (c=0; c <length($1) + length(FS); c++) printf " "; print $2 FS $3 } else { print $0 } } { f = $1 }' FILE
Or just the awk program formatted:
{
if (f == $1) {
for (c=0; c < length($1) + length(FS); c++)
printf " "
print $2 FS $3
} else {
print $0
}
}
{
f = $1
}
Explanation:
If the first field ($1) matches the first field of the previous line (f, which is assigned at the end of processing each line with f = $1), then we print spaces for the length of the field to be omitted plus the length of the field separator (FS). Else, we just print the entire line ($0).
If the comma needs to be kept, the awk program should be this:
{
if (f == $1) {
for (c=0; c < length($1); c++)
printf " "
print FS $2 FS $3
} else {
print $0
}
}
{
f = $1
}
This will print:
a,123,xyz
,345,zyx
b,123,xyz
,345,zyx
a,123,xyz 345,zyx b,123,xyz 345,zyx It is removing the white spaces in the first column and replacing that with second column. Due to this the header mismatch is happening. Also, I am not able to show here but I would like to Merge two rows in the first column containing only value 'a' and another two rows with value 'b'. Am I missing something?
Just do:
awk '$1==p{sub("[^,]*,",s)}
{p=$1; s = sprintf("%"(1 + length(p))"s","")}1' FS=, OFS=, input
It's much simpler if you don't worry about the leading indentation:
awk '$1==p{sub("[^,]*,","")}{p=$1}1' FS=, OFS=, input
