topic Re: How to create number based on group in a column in Discussions

How to create number based on group in a column

dadawasozo — Fri, 14 Jul 2023 00:00:28 GMT

Hi,

I have a categorical column that has repeated same string order randomly. I want to label number to each group that has same string like the example in picture. How I can do to create the column of result?

Re: How to create number based on group in a column

WebDesignesCrow — Fri, 14 Jul 2023 01:12:46 GMT

how about using "recode new colum";

script:

// New data table
// → Data Table( "Untitled" )
New Table( "Untitled" );
// Recode column: result
Local( {dt, col1},
	dt = Data Table( "Untitled" );
	dt << Begin Data Update;
	col1 = dt << New Column( dt:Column 1 );
	col1 << Set Name( "result" );
	dt << Move Selected Columns( {col1}, after( dt:Column 1 ) );
	dt << Recode Column(
		dt:Column 1,
		{Map Value(
			_rcOrig,
			{"aaa", "1", "bb", "2", "ccc", "3", "ddd", "4", "eee", "5"},
			Unmatched( _rcNow )
		)},
		Update Properties( 1 ),
		Target Column( col1 )
	);
	dt << End Data Update;
);

Re: How to create number based on group in a column

dadawasozo — Fri, 14 Jul 2023 02:50:30 GMT

I'm collecting new data daily and will have a lot of new string. Need a better way to do it than hard coded using recode new column

Re: How to create number based on group in a column

jthi — Fri, 14 Jul 2023 05:15:21 GMT

Depending on what type of column you want and how much scripting you are willing to do, there are quite a few options. Below is one option which might work

Names Default To Here(1);

dt = New Table("Untitled",
	Add Rows(9),
	Compress File When Saved(1),
	New Column("Column 1",
		Character,
		"Nominal",
		Set Values({"aa", "bb", "cc", "aa", "bb", "cc", "aa", "bb", "cc"})
	)
);

dt << New Column("col", Numeric, Ordinal, << Set Each Value(
	vals = As Constant(Associative Array(:Column 1 << get values) << get keys);
	Contains(vals, :Column 1)
));

Re: How to create number based on group in a column

Thierry_S — Fri, 14 Jul 2023 05:25:45 GMT

Hi,

Here is an ugly approach that will assign a numerical ID to your list of IDs

Col Rank( :Column1, <<tie( "minimum" ) )

The problem is that those unique IDs will not be created in increments of 1.

I am still working on a better way but this might get started.

Best.

Re: How to create number based on group in a column

Thierry_S — Fri, 14 Jul 2023 05:53:39 GMT

Hi Again,

I got it (see below):

aa = Associative Array( :GROUP );
kk = Associative Array( :GROUP ) << Get Keys;
For( i = 1, i <= N Items( kk ), i++,
	aa[kk[i]] = i
);
aa[:GROUP];

I hope it works for your application.

Best,

Re: How to create number based on group in a column

ron_horne — Fri, 14 Jul 2023 21:50:14 GMT

here is another way of getting it done.

this way creates a table summary and gives a number to each unique value. by default this is done in ascending order but could be descending or any other order you wish.

the current formula of numbering can also be more sophisticated for different units or increments as desired.

all steps can also be done by clicking buttons in the menus
let us know if it works.

// create data table to work with
dt1 = New Table( "Untitled", Add Rows( 11 ),
	New Column( "Column 1",	Character, "Nominal",
	Set Values({"aaa", "bb", "ccc", "aaa", "ddd", "eee", "ccc", "bb", "bb", "ccc", "aaa"})
	)
);

// start working
// Data table summary
dt2 = dt1 << Summary(	Group( :Column 1 ),	Freq( "None" ),	Weight( "None" ), Link to original data table( 0 ));


// Delete column: N Rows
dt2 << Delete Columns( :N Rows );

// New column: result
dt2 << New Column( "result", Numeric, "Continuous", Format( "Best", 12 ),Formula( Row() ));

// Delete column formula: result
dt2:result << Delete Formula;

// Update data tables
dt1 << Update(
	With( Data Table( dt2 ) ),
	Match Columns( :Column 1 = :Column 1 ),
	Replace Columns in Main Table( None )
);

// Close Data Table: Untitled By (Column 1)
Close( dt2, no save );