nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

First Value for a Unique ID

dkraeuter_sunne

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?

Accepted answers

brady_brady

Hello,

I believe this will address the question you ACTUALLY asked

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

Cheers,

Brady

All comments

brady_brady

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 )

jthi

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

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

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

brady_brady

Hello,

I believe this will address the question you ACTUALLY asked

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

Cheers,

Brady

hogi

Does

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

work as well?

Quick Links