Home / ubuntu / Questions / 1184179
In Process
Manoj Kumar
Asked: 2019-10-28 05:06:08 +0800 CST

How to rename the exisitng cell names based on number of their occurnaces in a particular column?

  • 772

I have a column in my excel as below:

REF
---------
BV1013_1
BV1013_1
BV1013_2
BV1013_2
BV1026
BV1040
BV1040
BV1041
BV1041
BV1042
BV1042

Desired OUTPUT: Some of the cell values/names are same at several places. I would like to rename my cell names as below based on their number of occurrences.

REF             Desire
-----------------------
BV1013_1    BV1013_1_1  
BV1013_1    BV1013_1_2
BV1013_2    BV1013_2_1  
BV1013_2    BV1013_2_2
BV1026      BV1026_1
BV1040      BV1040_1
BV1040      BV1040_2
BV1041      BV1041_1
BV1041      BV1041_2
BV1042      BV1042_1
BV1042      BV1042_2

I would like to solve this in excel or using bash scripting. Any help is very valuable for me.

scripts command-line bash xls csv

1 Answers

  1. Using Miller (https://github.com/johnkerl/miller) starting from

    REF
BV1013_1
BV1013_1
BV1013_2
BV1013_2
BV1026
BV1040
BV1040
BV1041
BV1041
BV1042
BV1042

    and running

    mlr --csv step -a counter -f REF -g REF then put '$Desire=$REF."_".$REF_counter' then cut -x -f REF_counter input.csv

    you will have

    REF,Desire
BV1013_1,BV1013_1_1
BV1013_1,BV1013_1_2
BV1013_2,BV1013_2_1
BV1013_2,BV1013_2_2
BV1026,BV1026_1
BV1040,BV1040_1
BV1040,BV1040_2
BV1041,BV1041_1
BV1041,BV1041_2
BV1042,BV1042_1
BV1042,BV1042_2
    • 1