topic First Value for a Unique ID in Discussions

First Value for a Unique ID

dkraeuter_sunne — Sat, 10 Jun 2023 23:31:08 GMT

I am trying to select the first value for each unique ID and save it to a new column. For instance in the example below, the 2k71 would be 51 and 3SJ42 would be 49. How would I write a formula to find only the first value for each unique ID?

Re: First Value for a Unique ID

brady_brady — Fri, 04 Jun 2021 15:49:27 GMT

EDIT:

I misread your question... this answers a problem you did not ask. Sorry!

////////////////

Hi,

The formula below will do this. It returns the minimum row number in the column, grouped by ID.

Cheers,

Brady

Col Minimum( Row(), :ID )

Re: First Value for a Unique ID

jthi — Fri, 04 Jun 2021 15:29:55 GMT

If you want the value to be also in rows which have missing value this could work:

ids = Loc(:ID << get as matrix, :ID);
first_val = Min(Loc Nonmissing(:Value[ids]));
:Value[ids[first_val]];

I strongly suggest that you take a look at Loc() function from Scripting Index and possibly run the formula in smaller parts so you understand how it works. There are most likely easier ways to do this, but I like using Loc().

Re: First Value for a Unique ID

ih — Fri, 04 Jun 2021 15:31:02 GMT

Here is one way that:

first finds the first finds the smallest row with and without missing values (red box)
excludes those missing values returning the row for each ID type (purple box) and then
gets the value for that row.

Here is a script you can run to recreate this column with a few steps along the way:

New Table( "First Value for a Unique ID",
	Add Rows( 22 ),
	New Column( "ID",
		Character,
		"Nominal",
		Set Values(
			{"2k", "2k", "2k", "2k", "2k", "2k", "l5", "l5", "l5", "l5", "l5", "l5",
			"l5", "l5", "3s", "3s", "3s", "3s", "3s", "3s", "3s", "3s"}
		)
	),
	New Column( "Value",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values(
			[., ., 51, 50, 52, 54, ., ., ., 53, 54, 55, 57, 58, ., 49, 48, 50, 52,
			56, 58, 60]
		)
	),
	New Column( "Row with First Value with/without missing 2",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Formula( Col Minimum( Row(), :ID, !Is Missing( :Value ) ) )
	),
	New Column( "Row with First Value Exclude Missing",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Formula(
			If( Is Missing( :Value ),
				Empty(),
				Col Minimum( Row(), :ID, !Is Missing( :Value ) )
			)
		)
	),
	New Column( "Row with First Value",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Formula(
			Col Mean(
				If( Is Missing( :Value ),
					Empty(),
					Col Minimum( Row(), :ID, !Is Missing( :Value ) )
				),
				:ID
			)
		)
	),
	New Column( "First Value",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Formula(
			:Value[Col Mean(
				If( Is Missing( :Value ),
					Empty(),
					Col Minimum( Row(), :ID, !Is Missing( :Value ) )
				),
				:ID
			)]
		)
	)
)

Re: First Value for a Unique ID

brady_brady — Fri, 04 Jun 2021 16:06:53 GMT

Hello,

I believe this will address the question you ACTUALLY asked ;)

Col Minimum( Col Cumulative Sum( :Value, :ID ), :ID )

Cheers,

Brady

Re: First Value for a Unique ID

brady_brady — Fri, 04 Jun 2021 16:50:36 GMT

NOTE: the formula above may give incorrect results if negative values exist.

Re: First Value for a Unique ID

hogi — Sun, 29 Dec 2024 16:33:35 GMT

Does

New Column( "First Value",
	Formula( :Value[Col Minimum( If( !Is Missing( :value ), Row() ), :ID )] )
)

work as well?