Convert lower-case to uppercase with the output to a new column using miller

Question

I want to copy column NAME to column NAME-LOWERCASE. NAME-LOWERCASE should only contain lowercase letters. The uppercase should be untouched in all column expect NAME-LOWERCASE

input

NAME,test
PTC,N
Agri,Y
E-example,N
ForYou,N
Willy Nes,Y

output

NAME,NAME-LOWERCASE,test
PTC,ptc,N
Agri,agri,Y
E-example,E-example,N
ForYou,foryou,N
Willy Nes,willy nes,Y

I know how to create a new column from another column and reorder

mlr -I --csv \
    put '$FIRSTNAME = sub($FULLNAME," .*","")' then \
    reorder -f FULLNAME,LASTNAME,EMAIL,DOMAIN,COMPANY input.csv

And I know how to convert upper to lowercase

mlr --csv -N case -l 

How to combine both commands? Or there is another miller command to reach my goal?

Answer 1

If you run

mlr --csv put '${NAME-LOWERCASE}=tolower($NAME)' then \
reorder -f NAME,NAME-LOWERCASE,test input.csv

you get the NAME-LOWERCASE in lower case.

NAME,NAME-LOWERCASE,test
PTC,ptc,N
Agri,agri,Y
E-example,e-example,N
ForYou,foryou,N
Willy Nes,willy nes,Y

It's tolower function.

Answer 2

If your data really are as simple as you show, with no , and no and no newline characters inside the fields, you could just do something like:

$ awk -F, -v OFS="," '{ $3=$2; } (NR==1) ? $2="NAME-LOWERCASE" : $2=tolower($1)' file
NAME,NAME-LOWERCASE,test
PTC,ptc,N
Agri,agri,Y
E-example,e-example,N
ForYou,foryou,N
Willy Nes,willy nes,Y

Here, we are setting the input field separator to a coma (-F,) and then setting the special variable OFS, the output field separator, to a omma (-v OFS=,). Then, for every line, we add a new third field which has the same value as the current second field ($3=$2). We then use the ternary operator to check if this is the first line (NR==1) and, if it is, we set the second field to the string ($2="NAME-LOWERCASE") and if it isn't, we set the second field to the lower case version of the first ($2=tolower($1)). In awk, the default action when an expression evaluate to true is to print the line and since this expression will always evaluate to true since it will always either be the first line or not, this results in printing each modified line.

---

Alternatively and again assuming your data are as simple as you show, you can use perl:

$ perl -F, -lane '$,=","; $. == 1 ? print $F[0],"NAME-LOWERCASE",$F[1] : print $F[0],lc($F[0]), $F[1]' file 
NAME,NAME-LOWERCASE,test
PTC,ptc,N
Agri,agri,Y
E-example,e-example,N
ForYou,foryou,N
Willy Nes,willy nes,Y

The -a makes perl act like awk, splitting each input line on the character given by -F. The -n means "read the input file line by line and run the script given by -e on each line". The -l removes trailing newlines from the input and adds a trailing newline to each print call. With -a, fields are split into the special array @F, so the first fields is $F[0], the seconds $F[1] and so on. Finally, the special variable $, is the output field separator, here we are setting it to a , to print comma separated output.

The script itself is quite simple: it first sets $, to , and then if this is the first line ($. holds the current line number) it prints the first field, then the string "NAME-LOWERCASE" and then the second field and, for all other lines, it prints the first field, then the first field in lower case (lc($F[0])) and then the second.

You could write the same thing as:

perl -F, -lane '
  if($. == 1){
     print $F[0],"NAME-LOWERCASE",$F[1];
  }
  else{
     print $F[0],lc($F[0]), $F[1];
  }' file 

Answer 3

Using awk:

$ awk 'BEGIN{FS=OFS=","}
NR==1{$(NF+1)="NAME-LOWERCASE";}
NR>1{$(NF+1) = tolower($1)}
{for(i=1;i<=NF;i++) if (i== (NF-1) ) ;
else printf $i OFS; print $(NF-1) }' file

---

Using csvsql:

$ csvsql -I --query 'SELECT NAME,lower(NAME) AS "NAME-LOWERCASE",test  FROM file' file.csv