Joining DataFrames - Revision history

Ollvihe at 20:14, 21 February 2024

2024-02-21T20:14:30Z

← Older revision		Revision as of 20:14, 21 February 2024
Line 12:		Line 12:
	#* '''rightouter''': at least one row is generated for each right side DataFrame row, even if there is no matching other row (in that case ''null'' is given as value for the other columns).		#* '''rightouter''': at least one row is generated for each right side DataFrame row, even if there is no matching other row (in that case ''null'' is given as value for the other columns).
	#* '''outer''': at least one row is generated both for the left and right side DataFrames even if there is no matching other row (in that case ''null'' is given as value for the other columns).		#* '''outer''': at least one row is generated both for the left and right side DataFrames even if there is no matching other row (in that case ''null'' is given as value for the other columns).
	#* '''cross''': One row is generated for each combination of the joined DataFrames. ~~The~~ cross join ~~doesn't have~~ the ~~matching criteria~~ at all. Note that the cross join might be ~~very~~ slow operation.		#* '''cross''': One row is generated for each combination of the joined DataFrames. For the cross join, the columns to match is not defined at all. Note that the cross join might be a slow operation as the number of resulting rows tend to grow.

	Example: inner join:		Example: inner join:

Ollvihe at 20:08, 21 February 2024

2024-02-21T20:08:36Z

← Older revision		Revision as of 20:08, 21 February 2024
Line 35:		Line 35:
	</pre>		</pre>

	Example: inner join with different column names:		Example: inner join with different column names (one column pair):
	<pre>		<pre>
	let left = ToDataFrame([[0, 0, "zerozeroleft"], [0, 1, "zeroleft"], [1, 2, "oneleft"]], ["idleft1", "idleft2", "left"]);		let left = ToDataFrame([[0, 0, "zerozeroleft"], [0, 1, "zeroleft"], [1, 2, "oneleft"]], ["idleft1", "idleft2", "left"]);
Line 48:		Line 48:
	</pre>		</pre>

	Example: inner join with different column names:		Example: inner join with different column names (two column pairs):
	<pre>		<pre>
	let left = ToDataFrame([[0, 0, "zerozeroleft"], [0, 1, "zeroleft"], [1, 2, "oneleft"]], ["idleft1", "idleft2", "left"]);		let left = ToDataFrame([[0, 0, "zerozeroleft"], [0, 1, "zeroleft"], [1, 2, "oneleft"]], ["idleft1", "idleft2", "left"]);

Ollvihe at 20:07, 21 February 2024

2024-02-21T20:07:20Z

← Older revision		Revision as of 20:07, 21 February 2024
Line 14:		Line 14:
	#* '''cross''': One row is generated for each combination of the joined DataFrames. The cross join doesn't have the matching criteria at all. Note that the cross join might be very slow operation.		#* '''cross''': One row is generated for each combination of the joined DataFrames. The cross join doesn't have the matching criteria at all. Note that the cross join might be very slow operation.

	~~Examples~~:		Example: inner join:
	<pre>		<pre>
	let left = ToDataFrame([[0, "zero"], [1, "one"]], ["id", "left"]);		let left = ToDataFrame([[0, "zero"], [1, "one"]], ["id", "left"]);
	let right = ToDataFrame([[0, "zero"], [2, "two"], [3, "three"]], ["id", "right"]);		let right = ToDataFrame([[0, "zero"], [2, "two"], [3, "three"]], ["id", "right"]);
	left.join(right, "id").ToCsv();		left.join(right, "id").ToCsv();
	~~</pre>~~
	Returns:		Returns:
	id;left;right		id;left;right
	0;zero;zero		0;zero;zero
			</pre>

			Example: outer join with matching column names:
	<pre>		<pre>
	let left = ToDataFrame([[0, "zero"], [1, "one"]], ["id", "left"]);		let left = ToDataFrame([[0, "zero"], [1, "one"]], ["id", "left"]);
	let right = ToDataFrame([[0, "zero"], [2, "two"], [3, "three"]], ["id", "right"]);		let right = ToDataFrame([[0, "zero"], [2, "two"], [3, "three"]], ["id", "right"]);
	left.join(right, "id", "leftouter").ToCsv();		left.join(right, "id", "leftouter").ToCsv();
	~~</pre>~~
	Returns:		Returns:
	id;left;right		id;left;right
	0;zero;zero		0;zero;zero
	1;one;		1;one;
			</pre>

			Example: inner join with different column names:
	<pre>		<pre>
	let left = ToDataFrame([[0, 0, "zerozeroleft"], [0, 1, "zeroleft"], [1, 2, "oneleft"]], ["idleft1", "idleft2", "left"]);		let left = ToDataFrame([[0, 0, "zerozeroleft"], [0, 1, "zeroleft"], [1, 2, "oneleft"]], ["idleft1", "idleft2", "left"]);
	let right = ToDataFrame([[0, 0, "zerozeroright"], [0, 1, "zeroright"], [2, 3, "tworight"], [3, 4, "threeright"]], ["idright1", "idright2", "right"]);		let right = ToDataFrame([[0, 0, "zerozeroright"], [0, 1, "zeroright"], [2, 3, "tworight"], [3, 4, "threeright"]], ["idright1", "idright2", "right"]);
	left.join(right, ["idleft1": "idright1"], "inner");		left.join(right, ["idleft1": "idright1"], "inner").ToCsv();
	~~</pre>~~
	Returns:		Returns:
	idleft1;idleft2;left;idright2;right		idleft1;idleft2;left;idright2;right
Line 45:		Line 46:
	0;1;zeroleft;0;zerozeroright		0;1;zeroleft;0;zerozeroright
	0;1;zeroleft;1;zeroright		0;1;zeroleft;1;zeroright
			</pre>

			Example: inner join with different column names:
	<pre>		<pre>
	let left = ToDataFrame([[0, 0, "zerozeroleft"], [0, 1, "zeroleft"], [1, 2, "oneleft"]], ["idleft1", "idleft2", "left"]);		let left = ToDataFrame([[0, 0, "zerozeroleft"], [0, 1, "zeroleft"], [1, 2, "oneleft"]], ["idleft1", "idleft2", "left"]);
	let right = ToDataFrame([[0, 0, "zerozeroright"], [0, 1, "zeroright"], [2, 3, "tworight"], [3, 4, "threeright"]], ["idright1", "idright2", "right"]);		let right = ToDataFrame([[0, 0, "zerozeroright"], [0, 1, "zeroright"], [2, 3, "tworight"], [3, 4, "threeright"]], ["idright1", "idright2", "right"]);
	left.join(right, ["idleft1": "idright1", "idleft2": "idright2"], "inner");		left.join(right, ["idleft1": "idright1", "idleft2": "idright2"], "inner").ToCsv();
	~~</pre>~~
	Returns:		Returns:
	idleft1;idleft2;left;right		idleft1;idleft2;left;right
	0;0;zerozeroleft;zerozeroright		0;0;zerozeroleft;zerozeroright
	0;1;zeroleft;zeroright		0;1;zeroleft;zeroright
			</pre>

			Example: cross join:
			<pre>
			let left = ToDataFrame([[1, "one"], [2, "two"]], ["left number", "left text"]);
			let right = ToDataFrame([[1], [2], [3]], ["right number"]);
			left.join(right, null, "cross").ToCsv();
			Returns:
			left number;left text;right number
			1;one;1
			1;one;2
			1;one;3
			2;two;1
			2;two;2
			2;two;3
			</pre>

Ollvihe at 20:01, 21 February 2024

2024-02-21T20:01:02Z

← Older revision		Revision as of 20:01, 21 February 2024
Line 24:		Line 24:
	0;zero;zero		0;zero;zero

			<pre>
	let left = ToDataFrame([[0, "zero"], [1, "one"]], ["id", "left"]);		let left = ToDataFrame([[0, "zero"], [1, "one"]], ["id", "left"]);
	let right = ToDataFrame([[0, "zero"], [2, "two"], [3, "three"]], ["id", "right"]);		let right = ToDataFrame([[0, "zero"], [2, "two"], [3, "three"]], ["id", "right"]);
Line 33:		Line 34:
	1;one;		1;one;

			<pre>
	let left = ToDataFrame([[0, 0, "zerozeroleft"], [0, 1, "zeroleft"], [1, 2, "oneleft"]], ["idleft1", "idleft2", "left"]);		let left = ToDataFrame([[0, 0, "zerozeroleft"], [0, 1, "zeroleft"], [1, 2, "oneleft"]], ["idleft1", "idleft2", "left"]);
	let right = ToDataFrame([[0, 0, "zerozeroright"], [0, 1, "zeroright"], [2, 3, "tworight"], [3, 4, "threeright"]], ["idright1", "idright2", "right"]);		let right = ToDataFrame([[0, 0, "zerozeroright"], [0, 1, "zeroright"], [2, 3, "tworight"], [3, 4, "threeright"]], ["idright1", "idright2", "right"]);
Line 44:		Line 46:
	0;1;zeroleft;1;zeroright		0;1;zeroleft;1;zeroright

			<pre>
	let left = ToDataFrame([[0, 0, "zerozeroleft"], [0, 1, "zeroleft"], [1, 2, "oneleft"]], ["idleft1", "idleft2", "left"]);		let left = ToDataFrame([[0, 0, "zerozeroleft"], [0, 1, "zeroleft"], [1, 2, "oneleft"]], ["idleft1", "idleft2", "left"]);
	let right = ToDataFrame([[0, 0, "zerozeroright"], [0, 1, "zeroright"], [2, 3, "tworight"], [3, 4, "threeright"]], ["idright1", "idright2", "right"]);		let right = ToDataFrame([[0, 0, "zerozeroright"], [0, 1, "zeroright"], [2, 3, "tworight"], [3, 4, "threeright"]], ["idright1", "idright2", "right"]);

Ollvihe at 20:00, 21 February 2024

2024-02-21T20:00:41Z

← Older revision		Revision as of 20:00, 21 February 2024
Line 2:		Line 2:

	Parameters:		Parameters:
	# '''DataFrame''': ~~The other~~ DataFrame to join.		# '''DataFrame''': Other DataFrame in the join.
	# '''Columns to match''': Columns which the joining is based on, can be defined as follows:		# '''Columns to match''': Columns which the joining is based on, can be defined as follows:
	#* If joining using '''one column having the same name in both DataFrames''', the column name is specified as as string.		#* If joining using '''one column having the same name in both DataFrames''', the column name is specified as as string.
Line 12:		Line 12:
	#* '''rightouter''': at least one row is generated for each right side DataFrame row, even if there is no matching other row (in that case ''null'' is given as value for the other columns).		#* '''rightouter''': at least one row is generated for each right side DataFrame row, even if there is no matching other row (in that case ''null'' is given as value for the other columns).
	#* '''outer''': at least one row is generated both for the left and right side DataFrames even if there is no matching other row (in that case ''null'' is given as value for the other columns).		#* '''outer''': at least one row is generated both for the left and right side DataFrames even if there is no matching other row (in that case ''null'' is given as value for the other columns).
			#* '''cross''': One row is generated for each combination of the joined DataFrames. The cross join doesn't have the matching criteria at all. Note that the cross join might be very slow operation.

	Examples:		Examples:
Line 17:		Line 18:
	let left = ToDataFrame([[0, "zero"], [1, "one"]], ["id", "left"]);		let left = ToDataFrame([[0, "zero"], [1, "one"]], ["id", "left"]);
	let right = ToDataFrame([[0, "zero"], [2, "two"], [3, "three"]], ["id", "right"]);		let right = ToDataFrame([[0, "zero"], [2, "two"], [3, "three"]], ["id", "right"]);
	left.join(right, "id").ToCsv()		left.join(right, "id").ToCsv();
			</pre>
	Returns:		Returns:
	id;left;right		id;left;right
Line 24:		Line 26:
	let left = ToDataFrame([[0, "zero"], [1, "one"]], ["id", "left"]);		let left = ToDataFrame([[0, "zero"], [1, "one"]], ["id", "left"]);
	let right = ToDataFrame([[0, "zero"], [2, "two"], [3, "three"]], ["id", "right"]);		let right = ToDataFrame([[0, "zero"], [2, "two"], [3, "three"]], ["id", "right"]);
	left.join(right, "id", "leftouter").ToCsv()		left.join(right, "id", "leftouter").ToCsv();
			</pre>
	Returns:		Returns:
	id;left;right		id;left;right
Line 33:		Line 36:
	let right = ToDataFrame([[0, 0, "zerozeroright"], [0, 1, "zeroright"], [2, 3, "tworight"], [3, 4, "threeright"]], ["idright1", "idright2", "right"]);		let right = ToDataFrame([[0, 0, "zerozeroright"], [0, 1, "zeroright"], [2, 3, "tworight"], [3, 4, "threeright"]], ["idright1", "idright2", "right"]);
	left.join(right, ["idleft1": "idright1"], "inner");		left.join(right, ["idleft1": "idright1"], "inner");
			</pre>
	Returns:		Returns:
	idleft1;idleft2;left;idright2;right		idleft1;idleft2;left;idright2;right
Line 43:		Line 47:
	let right = ToDataFrame([[0, 0, "zerozeroright"], [0, 1, "zeroright"], [2, 3, "tworight"], [3, 4, "threeright"]], ["idright1", "idright2", "right"]);		let right = ToDataFrame([[0, 0, "zerozeroright"], [0, 1, "zeroright"], [2, 3, "tworight"], [3, 4, "threeright"]], ["idright1", "idright2", "right"]);
	left.join(right, ["idleft1": "idright1", "idleft2": "idright2"], "inner");		left.join(right, ["idleft1": "idright1", "idleft2": "idright2"], "inner");
			</pre>
	Returns:		Returns:
	idleft1;idleft2;left;right		idleft1;idleft2;left;right
	0;0;zerozeroleft;zerozeroright		0;0;zerozeroleft;zerozeroright
	0;1;zeroleft;zeroright		0;1;zeroleft;zeroright
	~~</pre>~~

Ollvihe: Created page with "Join function for DataFrame performs a [https://en.wikipedia.org/wiki/Relational_algebra#Joins_and_join-like_operators joining operation] between two DataFrames. Parameters:..."

2022-12-08T22:08:17Z

Created page with "Join function for DataFrame performs a [https://en.wikipedia.org/wiki/Relational_algebra#Joins_and_join-like_operators joining operation] between two DataFrames. Parameters:..."

New page

Join function for DataFrame performs a [https://en.wikipedia.org/wiki/Relational_algebra#Joins_and_join-like_operators joining operation] between two DataFrames.

Parameters:
# '''DataFrame''': The other DataFrame to join.
# '''Columns to match''': Columns which the joining is based on, can be defined as follows:
#* If joining using '''one column having the same name in both DataFrames''', the column name is specified as as string.
#* If joining using '''several columns having the same names in both DataFrames''', the column names are specified as a string array.
#* If joining using '''columns having different names between the DataFrames''', columns are specified as an array of key-value pairs, where the key is the column name in the left side DataFrame, and value is the column name in the right side DataFrame.
# '''Join type''' which can be
#* '''inner''' (default): row is generated if both DataFrames have the key.
#* '''leftouter''': at least one row is generated for each left side DataFrame row, even if there is no matching other row (in that case ''null'' is given as value for the other columns).
#* '''rightouter''': at least one row is generated for each right side DataFrame row, even if there is no matching other row (in that case ''null'' is given as value for the other columns).
#* '''outer''': at least one row is generated both for the left and right side DataFrames even if there is no matching other row (in that case ''null'' is given as value for the other columns).

Examples:
<pre>
let left = ToDataFrame([[0, "zero"], [1, "one"]], ["id", "left"]);
let right = ToDataFrame([[0, "zero"], [2, "two"], [3, "three"]], ["id", "right"]);
left.join(right, "id").ToCsv()
Returns:
id;left;right
0;zero;zero

let left = ToDataFrame([[0, "zero"], [1, "one"]], ["id", "left"]);
let right = ToDataFrame([[0, "zero"], [2, "two"], [3, "three"]], ["id", "right"]);
left.join(right, "id", "leftouter").ToCsv()
Returns:
id;left;right
0;zero;zero
1;one;

let left = ToDataFrame([[0, 0, "zerozeroleft"], [0, 1, "zeroleft"], [1, 2, "oneleft"]], ["idleft1", "idleft2", "left"]);
let right = ToDataFrame([[0, 0, "zerozeroright"], [0, 1, "zeroright"], [2, 3, "tworight"], [3, 4, "threeright"]], ["idright1", "idright2", "right"]);
left.join(right, ["idleft1": "idright1"], "inner");
Returns:
idleft1;idleft2;left;idright2;right
0;0;zerozeroleft;0;zerozeroright
0;0;zerozeroleft;1;zeroright
0;1;zeroleft;0;zerozeroright
0;1;zeroleft;1;zeroright

let left = ToDataFrame([[0, 0, "zerozeroleft"], [0, 1, "zeroleft"], [1, 2, "oneleft"]], ["idleft1", "idleft2", "left"]);
let right = ToDataFrame([[0, 0, "zerozeroright"], [0, 1, "zeroright"], [2, 3, "tworight"], [3, 4, "threeright"]], ["idright1", "idright2", "right"]);
left.join(right, ["idleft1": "idright1", "idleft2": "idright2"], "inner");
Returns:
idleft1;idleft2;left;right
0;0;zerozeroleft;zerozeroright
0;1;zeroleft;zeroright
</pre>