First Value for a Unique ID

[#1]

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?

[#2]

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 )

[#3]

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().

[#4]

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:

View more...

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
			)]
		)
	)
)

[#5 · Accepted solution]

Hello,

I believe this will address the question you ACTUALLY asked

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

Cheers,

Brady

[#6]

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