title: Pandas: Create Origin-Destination matrix, keeping destination-of-destination on same row
body: <p>I am trying to create a Origin-Destination matrix that takes into account destination-of-destination in one row.</p>
  <p>The dataset I have is similar to the following (<strong>edited</strong> both given_dataset and expected_result based on @mozway comment):</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>origin_id</th>
  <th>link_type</th>
  <th>applied_id</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1</td>
  <td>A</td>
  <td>2</td>
  </tr>
  <tr>
  <td>2</td>
  <td>B</td>
  <td>3</td>
  </tr>
  <tr>
  <td>2</td>
  <td>D</td>
  <td>3</td>
  </tr>
  <tr>
  <td>3</td>
  <td>C</td>
  <td>4</td>
  </tr>
  <tr>
  <td>5</td>
  <td>D</td>
  <td>6</td>
  </tr>
  <tr>
  <td>1</td>
  <td>E</td>
  <td>4</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>And the expected result would be:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>origin_id</th>
  <th>A</th>
  <th>B</th>
  <th>C</th>
  <th>D</th>
  <th>E</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1</td>
  <td>2</td>
  <td>3</td>
  <td>4</td>
  <td>3</td>
  <td>4</td>
  </tr>
  <tr>
  <td>2</td>
  <td></td>
  <td>3</td>
  <td>4</td>
  <td>3</td>
  <td></td>
  </tr>
  <tr>
  <td>3</td>
  <td></td>
  <td></td>
  <td>4</td>
  <td></td>
  <td></td>
  </tr>
  <tr>
  <td>5</td>
  <td></td>
  <td></td>
  <td></td>
  <td>6</td>
  <td></td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>In other words, since <em>1</em> is linked to <em>2</em> via <em>A</em>, and <em>2</em> is linked to <em>3</em> via <em>B</em> and <em>D</em> - ecc. ecc. -, I would like to transpose this path back to the row with <code>origin_id = 1</code>, where <code>link_type</code> becomes my new header.</p>
  <p>Notable mention: there is no scenario where <em>1</em> goes to both <em>2</em> and <em>3</em>, and <em>2</em> goes to <em>3</em> via the same link_type.</p>
  <p>I am currently using <code>pivot_table</code> function (<code>df.pivot_table(values='applied_id', index=&quot;origin__id&quot;, columns='link_type', aggfunc=max)</code>), and, although the result is near to what I am trying to achieve, it is not quite right:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>origin_id</th>
  <th>A</th>
  <th>B</th>
  <th>C</th>
  <th>D</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1</td>
  <td>2</td>
  <td></td>
  <td></td>
  <td></td>
  </tr>
  <tr>
  <td>2</td>
  <td></td>
  <td>3</td>
  <td></td>
  <td>3</td>
  </tr>
  <tr>
  <td>3</td>
  <td></td>
  <td></td>
  <td>4</td>
  <td></td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>What would be an efficient way to achieve my expected result given my starting dataframe?</p>
  <p><strong>EDIT -&gt; More context</strong>:</p>
  <p>I have a dataset that maps any transaction (<code>applied_id</code>) in our ERP with any other transaction (<code>origin_id</code>) that the former has been generated from.</p>
  <p>For instance, an invoice (<code>applied_id</code>) being generated by a sales order (<code>origin_id</code>), via <code>link_type = 'Invoicing'</code></p>
  <p>Then, the same invoice (<code>origin_id</code>) might have a credit memo (<code>applied_id</code>) applied on it (<code>link_type = 'Credit Memo'</code>), because the customer wanted his money back.</p>
  <p>Same for payments applied to invoices.</p>
  <p>My goal is to trace back the invoice, the payment and the credit memo to the original sales order row, as well as the credit memo to the invoice row and payment row, and payment to the invoice row.</p>
  <p>Hopefully this clarifies the goal here.</p>
  <p><strong>EDIT -&gt; Working answer</strong>:</p>
  <pre class="lang-py prettyprint-override"><code>df = pd.DataFrame({'origin_id': ['1', '2', '2', '3', '5', '1'], 
                     'link_type': ['A', 'B', 'D', 'C', 'D', 'E'], 
                     'applied_id':['2', '3', '3', '4', '6', '4']})
  
  G = nx.from_pandas_edgelist(df, source='origin_id', target='applied_id', edge_attr='link_type', create_using=nx.MultiDiGraph)
  dict_for_df = {}
  # Grabbing only link_types I am interested in
  link_type_list = ['A', 'B', 'C', 'D']
  
  for n in df['origin_id'].unique():
      value_dict = {}
      for value in link_type_list:
          # As I want the &quot;arriving&quot; transaction_id for each link_type, I am here grabbing key[1]
          value_list = list(set([key[1] for key, val in nx.get_edge_attributes(G.subgraph({str(n)}|nx.descendants(G, str(n))),'link_type').items() if val == value]))
          value_dict[value] = value_list
      dict_for_df[n] = value_dict
  
  final = pd.DataFrame.from_dict(dict_for_df, orient='index').reset_index().rename(columns={'index':'origin_id'})
  
  # output:
  #    origin_id  A   B   C   D
  # 0  1         [2]  [3] [4] [3]
  # 1  2         []   [3] [4] [3]
  # 2  3         []   []  [4] []
  # 3  5         []   []  []  [6]
  
  </code></pre>