title: Pandas: count number of times every value in one column appears in another column
body: <p>I want to count the number of times a value in Child column appears in Parent column then display this count in new column renamed child count. See previews df below.</p>
  <p>I have this done via VBA (COUNTIFS) but now need dynamic visualization and animated display with data fed from a dir. So I resorted to Python and Pandas and tried below code after searching and reading answers like: <a href="https://stackoverflow.com/questions/24810526/countif-in-pandas-python-over-multiple-columns-with-multiple-conditions">Countif in pandas with multiple conditions</a> | <a href="https://stackoverflow.com/questions/21319929/how-to-determine-whether-a-pandas-column-contains-a-particular-value">Determine if value is in pandas column</a> | <a href="https://stackoverflow.com/questions/16476924/how-to-iterate-over-rows-in-a-dataframe-in-pandas">Iterate over rows in Pandas df</a> | many others...
  but still can't get the expected preview as illustrated in image below.</p>
  <p>Any help will be very much appreciated. Thanks in advance.</p>
  <pre><code>#import libraries
  import pandas as pd
  import numpy as np
  import os
  
  #get datasets
  path_dataset = r'D:\Auto'
  df_ns = pd.read_csv(os.path.join(path_dataset, 'Scripts', 'data.csv'), index_col = False, encoding = 'ISO-8859-1', engine = 'python')
  
  #preview dataframe
  df_ns
  
  #tried
  df_ns.groupby(['Child','Parent', 'Site Name']).size().reset_index(name='child count')
  
  #preview output
  df_ns.groupby(['Child','Parent', 'Site Name']).size().reset_index(name='child count')
  </code></pre>
  <h1>preview dataframe</h1>
  <p><a href="https://i.stack.imgur.com/cZMCO.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/cZMCO.png" alt="enter image description here" /></a></p>
  <h1>preview output</h1>
  <p><a href="https://i.stack.imgur.com/YpE2y.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/YpE2y.png" alt="enter image description here" /></a></p>
  <h1>expected output</h1>
  <p><a href="https://i.stack.imgur.com/vNvYD.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/vNvYD.png" alt="enter image description here" /></a></p>
  <p>[Edited] My data</p>
  <p>Child = ['Tkt01', 'Tkt02', 'Tkt03', 'Tkt04', 'Tkt05', 'Tkt06', 'Tkt07', 'Tkt08', 'Tkt09', 'Tkt10']</p>
  <p>Parent = [' ', ' ', 'Tkt03',' ',' ', 'Tkt03',' ', 'Tkt03',' ',' ', 'Tkt06',' ',' ',' ',]</p>
  <p>Site_Name =[Yaounde','Douala','Bamenda','Bafoussam','Kumba','Garoua','Maroua','Ngaoundere','Buea','Ebolowa']</p>