How to combine rows with same value in one column?

Agustin — Wed, 19 Jun 2024 12:48:25 GMT

I have a data table that follows this format:

dt = New Table( "Example",
	Add Rows( 5 ),
	New Column( "Time Stamp",
		Character,
		"Nominal",
		Set Values( {"1", "2", "3", "4", "5"} )
	),
	New Column( "Sample Name",
		Character,
		"Nominal",
		Set Values( {"Sample_1", "Sample_1", "Sample_2", "Sample_2", "Sample_2"} )
	),
	New Column( "Test1",
		Numeric,
		"Nominal",
		Set Values( {1, 0, 1, 0, 0} )
	),
		New Column( "Test2",
		Numeric,
		"Nominal",
		Set Values( {0, 1, 0, 1, 0} )
	),
			New Column( "Test3",
		Numeric,
		"Nominal",
		Set Values( {0, 0, 0, 0, 1} )
	)
);

dt = New Table( "Example",
	Add Rows( 2 ),
	New Column( "Time Stamp",
		Character,
		"Nominal",
		Set Values( {"1", "3"} )
	),
	New Column( "Sample Name",
		Character,
		"Nominal",
		Set Values( {"Sample_1", "Sample_2"} )
	),
	New Column( "Test1",
		Numeric,
		"Nominal",
		Set Values( {1, 1} )
	),
		New Column( "Test2",
		Numeric,
		"Nominal",
		Set Values( {1, 1} )
	),
			New Column( "Test3",
		Numeric,
		"Nominal",
		Set Values( {0, 1} )
	)
);

So Sample_1 is tested against Test1 and Test2 and each row shows the result for that test. If one of the tests is not done for that row, the default status for that test is 0. That's why in row 1, Test2 and Test3 show as 0.

I want to combine these so that the final output is:

dt = New Table( "Result",
	Add Rows( 2 ),
	New Column( "Time Stamp",
		Character,
		"Nominal",
		Set Values( {"1", "3"} )
	),
	New Column( "Sample Name",
		Character,
		"Nominal",
		Set Values( {"Sample_1", "Sample_2"} )
	),
	New Column( "Test1",
		Numeric,
		"Nominal",
		Set Values( {1, 1} )
	),
		New Column( "Test2",
		Numeric,
		"Nominal",
		Set Values( {1, 1} )
	),
			New Column( "Test3",
		Numeric,
		"Nominal",
		Set Values( {0, 1} )
	)
);

I don't care too much about the Time Stamp column. In reality there are many other columns that do not have the same value for the repeat samples. But for this analysis I would like to keep the values of the first row with that sample name. As shown in the result table.

Somehow I need to select the rows for which the sample name is the same, sum the relevant columns together and combine into a results table. But I'm getting stuck on how to even start. Any help is appreciated!

Thanks

Re: How to combine rows with same value in one column?

jthi — Wed, 19 Jun 2024 12:53:13 GMT

Maybe Summary table is enough for you?

You might have to do additional join to get the extra columns

Re: How to combine rows with same value in one column?

Agustin — Wed, 19 Jun 2024 12:56:07 GMT

Oh I'll try this! If it works that would be perfect and so much easier than the convoluted process I was trying.

topic Re: How to combine rows with same value in one column? in Discussions

How to combine rows with same value in one column?

Re: How to combine rows with same value in one column?

Re: How to combine rows with same value in one column?