Mfcttrf

Independence of Mean and Variance of Discrete Uniform Distributions

Why doesn't mathematics collapse down, even though humans quite often make mistakes in their proofs?

Number of matrices with bounded products of rows and columns

Is there a commercial liquid with refractive index greater than n=2?

Have only girls been born for a long time in this village?

Are unaudited server logs admissible in a court of law?

Metal that glows when near pieces of itself

On the relation between the asymptotics of a Dirichlet series' coefficients and the series' analytic continuability

How best to join tables, which have different lengths on the same column values which exist in both tables?

Earliest evidence of objects intended for future archaeologists?

Wristwatches in the cockpit

Can sulfuric acid itself be electrolysed?

Why should I pay for an SSL certificate?

Why do balloons get cold when they deflate?

How can I train a replacement without letting my bosses and the replacement knowing?

Playing a fast but quiet Alberti bass

Did Wernher von Braun really have a "Saturn V painted as the V2"?

Where is this New York City Broadway location from Fall 1958?

Lazy brainfuck programmer

Check disk usage of files returned with spaces

Control GPIO pins from C

Expand def in write18

What is "super" in superphosphate?

Quick destruction of a helium filled airship?

How best to join tables, which have different lengths on the same column values which exist in both tables?

List of different values which have to be formatted differentlyArgmax in a ListHow to create a Table of Tables with indexed variablesRelational joining of tablesHow to use ImageMultiply[] over a list of strings?What is the Mathematica way of joining two tables?Is it possible to assign different values to a list of symbol, where they have the same argument?How to create a table of tables with different table lengths?How can I pair the values in 2 different tables of the same length together to make a table with 2 variables x, y?

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;

4

$begingroup$

I think my problem is pretty simple, and in SQL this would be trivial. I have two tables

TableOne = a, x1, b, x2, c, x3;
TableTwo = a, y1, c, y2 , a, y3, a, y4, b, y5, c,y6, c, y7

I want to abe able to join these two tables where the values of column 1 in both tables match such that:

DesiredResult = a, x1, a, y1, c, x3 , c, y2 , a, x1, a, y3, a, x1, a, y4, b, x2, b, y5, c, x3, c, y6, c, x3, c, y7

I've tried with both Select[] statements inside a Table[] structure and also looked into JoinAcross[] but haven't been able to acheive the desire effect. In SQL it would be simple, something like:

SELECT Col1.Table1, Col2.Table1, Col1.Table2, Col2.Table2, FROM table2 INNER JOIN table1 ON Col1.Table1 = Col1.Table2

Or something similar.

list-manipulation table

share|improve this question

asked 13 hours ago

QuantumPenguinQuantumPenguin

57433 silver badges1818 bronze badges

$endgroup$

add a comment | 

4

$begingroup$

I think my problem is pretty simple, and in SQL this would be trivial. I have two tables

TableOne = a, x1, b, x2, c, x3;
TableTwo = a, y1, c, y2 , a, y3, a, y4, b, y5, c,y6, c, y7

I want to abe able to join these two tables where the values of column 1 in both tables match such that:

DesiredResult = a, x1, a, y1, c, x3 , c, y2 , a, x1, a, y3, a, x1, a, y4, b, x2, b, y5, c, x3, c, y6, c, x3, c, y7

I've tried with both Select[] statements inside a Table[] structure and also looked into JoinAcross[] but haven't been able to acheive the desire effect. In SQL it would be simple, something like:

SELECT Col1.Table1, Col2.Table1, Col1.Table2, Col2.Table2, FROM table2 INNER JOIN table1 ON Col1.Table1 = Col1.Table2

Or something similar.

list-manipulation table

share|improve this question

asked 13 hours ago

QuantumPenguinQuantumPenguin

57433 silver badges1818 bronze badges

$endgroup$

add a comment | 

$begingroup$

I think my problem is pretty simple, and in SQL this would be trivial. I have two tables

TableOne = a, x1, b, x2, c, x3;
TableTwo = a, y1, c, y2 , a, y3, a, y4, b, y5, c,y6, c, y7

I want to abe able to join these two tables where the values of column 1 in both tables match such that:

DesiredResult = a, x1, a, y1, c, x3 , c, y2 , a, x1, a, y3, a, x1, a, y4, b, x2, b, y5, c, x3, c, y6, c, x3, c, y7

I've tried with both Select[] statements inside a Table[] structure and also looked into JoinAcross[] but haven't been able to acheive the desire effect. In SQL it would be simple, something like:

SELECT Col1.Table1, Col2.Table1, Col1.Table2, Col2.Table2, FROM table2 INNER JOIN table1 ON Col1.Table1 = Col1.Table2

Or something similar.

list-manipulation table

share|improve this question

asked 13 hours ago

QuantumPenguinQuantumPenguin

57433 silver badges1818 bronze badges

$endgroup$

TableOne = a, x1, b, x2, c, x3;
TableTwo = a, y1, c, y2 , a, y3, a, y4, b, y5, c,y6, c, y7

DesiredResult = a, x1, a, y1, c, x3 , c, y2 , a, x1, a, y3, a, x1, a, y4, b, x2, b, y5, c, x3, c, y6, c, x3, c, y7

SELECT Col1.Table1, Col2.Table1, Col1.Table2, Col2.Table2, FROM table2 INNER JOIN table1 ON Col1.Table1 = Col1.Table2

share|improve this question

asked 13 hours ago

QuantumPenguinQuantumPenguin

57433 silver badges1818 bronze badges

share|improve this question

asked 13 hours ago

QuantumPenguinQuantumPenguin

57433 silver badges1818 bronze badges

asked 13 hours ago

QuantumPenguinQuantumPenguin

57433 silver badges1818 bronze badges

asked 13 hours ago

QuantumPenguinQuantumPenguin

57433 silver badges1818 bronze badges

3 Answers
3

active

oldest

votes

5

$begingroup$

I think you should stay away from associations because your keys aren't unique. Maybe just go over all tuples of elements and pick those that match your criterion:

Reap[Outer[If[#1[[1]] == #2[[1]], Sow@Join@##] &, TableOne, TableTwo, 1]][[2, 1]]

(* a, x1, a, y1, a, x1, a, y3, a, x1, a, y4, b, x2, b, y5, c, x3, c, y2,
 c, x3, c, y6, c, x3, c, y7 *)

share|improve this answer

answered 13 hours ago

RomanRoman

15.7k11 gold badge2121 silver badges5252 bronze badges

$endgroup$

• 
  
  
  

  
  

  
  
  
  
  
  

  
  $begingroup$
  That'll do it, thanks again Roman.
  $endgroup$
  – QuantumPenguin
  13 hours ago
  

  
  
  
  

add a comment | 

3

$begingroup$

assocOne = AssociationThread[First /@ #, #] & @ TableOne;

f = Join[assocOne[First @ #], #]&;

Map[f] @ TableTwo 

a, x1, a, y1, c, x3, c, y2, a, x1, a, y3, a, x1, a, y4, b,
x2, b, y5, c, x3, c, y6, c, x3, c, y7

share|improve this answer

answered 10 hours ago

kglrkglr

212k1010 gold badges242242 silver badges485485 bronze badges

$endgroup$

• 
  
  
  

  
  

  
  
  
  
  
  

  
  $begingroup$
  Also a very nice solution, +1!
  $endgroup$
  – QuantumPenguin
  8 hours ago
  

  
  
  
  

add a comment | 

0

$begingroup$

Flatten[Table[Join[n, #] & /@ Cases[TableTwo, n[[1]], _], n, TableOne], 1]

 (* a, x1, a, y1, a, x1, a, y3, a, x1, a, y4, b, x2, b, y5, c, 
 x3, c, y2, c, x3, c, y6, c, x3, c, y7 *)

share|improve this answer

answered 3 hours ago

MelaGoMelaGo

2,53111 gold badge11 silver badge77 bronze badges

$endgroup$

add a comment | 

Your Answer

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "387"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fmathematica.stackexchange.com%2fquestions%2f203913%2fhow-best-to-join-tables-which-have-different-lengths-on-the-same-column-values%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

5

$begingroup$

I think you should stay away from associations because your keys aren't unique. Maybe just go over all tuples of elements and pick those that match your criterion:

Reap[Outer[If[#1[[1]] == #2[[1]], Sow@Join@##] &, TableOne, TableTwo, 1]][[2, 1]]

(* a, x1, a, y1, a, x1, a, y3, a, x1, a, y4, b, x2, b, y5, c, x3, c, y2,
 c, x3, c, y6, c, x3, c, y7 *)

share|improve this answer

answered 13 hours ago

RomanRoman

15.7k11 gold badge2121 silver badges5252 bronze badges

$endgroup$

• 
  
  
  

  
  

  
  
  
  
  
  

  
  $begingroup$
  That'll do it, thanks again Roman.
  $endgroup$
  – QuantumPenguin
  13 hours ago
  

  
  
  
  

add a comment | 

5

$begingroup$

I think you should stay away from associations because your keys aren't unique. Maybe just go over all tuples of elements and pick those that match your criterion:

Reap[Outer[If[#1[[1]] == #2[[1]], Sow@Join@##] &, TableOne, TableTwo, 1]][[2, 1]]

(* a, x1, a, y1, a, x1, a, y3, a, x1, a, y4, b, x2, b, y5, c, x3, c, y2,
 c, x3, c, y6, c, x3, c, y7 *)

share|improve this answer

answered 13 hours ago

RomanRoman

15.7k11 gold badge2121 silver badges5252 bronze badges

$endgroup$

• 
  
  
  

  
  

  
  
  
  
  
  

  
  $begingroup$
  That'll do it, thanks again Roman.
  $endgroup$
  – QuantumPenguin
  13 hours ago
  

  
  
  
  

add a comment | 

$begingroup$

I think you should stay away from associations because your keys aren't unique. Maybe just go over all tuples of elements and pick those that match your criterion:

Reap[Outer[If[#1[[1]] == #2[[1]], Sow@Join@##] &, TableOne, TableTwo, 1]][[2, 1]]

(* a, x1, a, y1, a, x1, a, y3, a, x1, a, y4, b, x2, b, y5, c, x3, c, y2,
 c, x3, c, y6, c, x3, c, y7 *)

share|improve this answer

answered 13 hours ago

RomanRoman

15.7k11 gold badge2121 silver badges5252 bronze badges

$endgroup$

Reap[Outer[If[#1[[1]] == #2[[1]], Sow@Join@##] &, TableOne, TableTwo, 1]][[2, 1]]

(* a, x1, a, y1, a, x1, a, y3, a, x1, a, y4, b, x2, b, y5, c, x3, c, y2,
 c, x3, c, y6, c, x3, c, y7 *)

share|improve this answer

answered 13 hours ago

RomanRoman

15.7k11 gold badge2121 silver badges5252 bronze badges

answered 13 hours ago

RomanRoman

15.7k11 gold badge2121 silver badges5252 bronze badges

answered 13 hours ago

RomanRoman

15.7k11 gold badge2121 silver badges5252 bronze badges

3

$begingroup$

assocOne = AssociationThread[First /@ #, #] & @ TableOne;

f = Join[assocOne[First @ #], #]&;

Map[f] @ TableTwo 

a, x1, a, y1, c, x3, c, y2, a, x1, a, y3, a, x1, a, y4, b,
x2, b, y5, c, x3, c, y6, c, x3, c, y7

share|improve this answer

answered 10 hours ago

kglrkglr

212k1010 gold badges242242 silver badges485485 bronze badges

$endgroup$

• 
  
  
  

  
  

  
  
  
  
  
  

  
  $begingroup$
  Also a very nice solution, +1!
  $endgroup$
  – QuantumPenguin
  8 hours ago
  

  
  
  
  

add a comment | 

3

$begingroup$

assocOne = AssociationThread[First /@ #, #] & @ TableOne;

f = Join[assocOne[First @ #], #]&;

Map[f] @ TableTwo 

a, x1, a, y1, c, x3, c, y2, a, x1, a, y3, a, x1, a, y4, b,
x2, b, y5, c, x3, c, y6, c, x3, c, y7

share|improve this answer

answered 10 hours ago

kglrkglr

212k1010 gold badges242242 silver badges485485 bronze badges

$endgroup$

• 
  
  
  

  
  

  
  
  
  
  
  

  
  $begingroup$
  Also a very nice solution, +1!
  $endgroup$
  – QuantumPenguin
  8 hours ago
  

  
  
  
  

add a comment | 

$begingroup$

assocOne = AssociationThread[First /@ #, #] & @ TableOne;

f = Join[assocOne[First @ #], #]&;

Map[f] @ TableTwo 

a, x1, a, y1, c, x3, c, y2, a, x1, a, y3, a, x1, a, y4, b,
x2, b, y5, c, x3, c, y6, c, x3, c, y7

share|improve this answer

answered 10 hours ago

kglrkglr

212k1010 gold badges242242 silver badges485485 bronze badges

$endgroup$

assocOne = AssociationThread[First /@ #, #] & @ TableOne;

f = Join[assocOne[First @ #], #]&;

Map[f] @ TableTwo 

share|improve this answer

answered 10 hours ago

kglrkglr

212k1010 gold badges242242 silver badges485485 bronze badges

answered 10 hours ago

kglrkglr

212k1010 gold badges242242 silver badges485485 bronze badges

answered 10 hours ago

kglrkglr

212k1010 gold badges242242 silver badges485485 bronze badges

0

$begingroup$

Flatten[Table[Join[n, #] & /@ Cases[TableTwo, n[[1]], _], n, TableOne], 1]

 (* a, x1, a, y1, a, x1, a, y3, a, x1, a, y4, b, x2, b, y5, c, 
 x3, c, y2, c, x3, c, y6, c, x3, c, y7 *)

share|improve this answer

answered 3 hours ago

MelaGoMelaGo

2,53111 gold badge11 silver badge77 bronze badges

$endgroup$

add a comment | 

0

$begingroup$

Flatten[Table[Join[n, #] & /@ Cases[TableTwo, n[[1]], _], n, TableOne], 1]

 (* a, x1, a, y1, a, x1, a, y3, a, x1, a, y4, b, x2, b, y5, c, 
 x3, c, y2, c, x3, c, y6, c, x3, c, y7 *)

share|improve this answer

answered 3 hours ago

MelaGoMelaGo

2,53111 gold badge11 silver badge77 bronze badges

$endgroup$

add a comment | 

$begingroup$

Flatten[Table[Join[n, #] & /@ Cases[TableTwo, n[[1]], _], n, TableOne], 1]

 (* a, x1, a, y1, a, x1, a, y3, a, x1, a, y4, b, x2, b, y5, c, 
 x3, c, y2, c, x3, c, y6, c, x3, c, y7 *)

share|improve this answer

answered 3 hours ago

MelaGoMelaGo

2,53111 gold badge11 silver badge77 bronze badges

$endgroup$

Flatten[Table[Join[n, #] & /@ Cases[TableTwo, n[[1]], _], n, TableOne], 1]

 (* a, x1, a, y1, a, x1, a, y3, a, x1, a, y4, b, x2, b, y5, c, 
 x3, c, y2, c, x3, c, y6, c, x3, c, y7 *)

share|improve this answer

answered 3 hours ago

MelaGoMelaGo

2,53111 gold badge11 silver badge77 bronze badges

answered 3 hours ago

MelaGoMelaGo

2,53111 gold badge11 silver badge77 bronze badges

answered 3 hours ago

MelaGoMelaGo

2,53111 gold badge11 silver badge77 bronze badges