title: How to combine two dataframes and naturally sort on a column of mixed alphanumeric types?
body: <p>I have <a href="https://docs.google.com/spreadsheets/d/15OU7SwaxqW4vds3E2uN-exPiXOQg2bMJmrGcxIYalaI/edit?usp=sharing" rel="nofollow noreferrer">a dataframe <code>df1</code></a>:</p>
  <pre class="lang-py prettyprint-override"><code>   QID Questions    B Answer1 Answer2 Answer3  F  G  H  I  J
  0    3         a  4.0       a       a       a  a  e  g  i  l
  1    4         b  5.0       b       b       b  a  r  h  m  p
  2    5         d  5.0     NaN       e       d  b  u  e  i  z
  3    6         e  5.0       d       h       r  b  c  z  i  3
  </code></pre>
  <p>And <a href="https://drive.google.com/file/d/1-fecLRvgTpAZ9UmPk_lQ4gFnTFIvoA6p/view?usp=sharing" rel="nofollow noreferrer">another one, <code>new_dataframe</code></a> that I would like to add between the lines of <code>df_1</code>.</p>
  <pre class="lang-py prettyprint-override"><code>   QID Questions    B Answer1 Answer2 Answer3  F  G  H  I  J
  2  4_1         z  5.0       b       k       b  a  r  h  m  p
  3  4_2         w  4.0       b       k       b  c  r  h  m  p
  </code></pre>
  <p>Indeed, I would like to obtain:</p>
  <pre class="lang-py prettyprint-override"><code>   QID Questions    B Answer1 Answer2 Answer3  F  G  H  I  J
  0    3         a  4.0       a       a       a  a  e  g  i  l
  1    4         b  5.0       b       b       b  a  r  h  m  p
  2  4_1         z  5.0       b       k       b  a  r  h  m  p
  3  4_2         w  4.0       b       k       b  c  r  h  m  p
  4    5         d  5.0     NaN       e       d  b  u  e  i  z
  5    6         e  5.0       d       h       r  b  c  z  i  3
  </code></pre>
  <p>So I want to integrate the lines of the second data frame <code>new_dataframe</code> whose QID is composed of a digit and a sub-digit, following the lines of <code>df1</code>. For example the lines in <code>new_dataframe</code> which QID is <code>4_1</code>, <code>4_2</code> ... should be integrated after <code>4</code>.</p>
  <p>Until now I tried the following:</p>
  <pre><code># Now I would like to join this new dataframe with dataframe_1, respecting the index, sort it and so on.
  for i, row in df1.iterrows():
      qid = row['QID']
      # test if there is such a QID in new_dataframe
      repeated_question = new_dataframe[new_dataframe['QID'].split(&quot;_&quot;)[0] == qid]
      # insert them 
  </code></pre>
  <h1>Update</h1>
  <p>I tried Trenton McKinney's updated answer with the actual data and got an exception:</p>
  <pre><code>from natsort import index_natsorted as ins
  import numpy as np
  import pandas as pd
  
  # read the files
  df1 = pd.read_csv(&quot;/content/drive/My Drive/Auspex/QuestionBank_06082020_QGrid_and_CINT.csv&quot;, dtype={'QID': str}, low_memory=False)
  df1.drop(columns=['Unnamed: 0'], inplace=True)
  
  df2 = pd.read_csv('/content/drive/My Drive/Auspex/new_df.csv', dtype={'QID': str})
  df2.drop(columns=['Unnamed: 0'], inplace=True)
  
  # concat them
  df = pd.concat([df1, df2])
  
  # sort the values using the key parameter in sort_values
  df.sort_values(by='QID', key=lambda col: np.argsort(ins(col))).reset_index(drop=True)
  </code></pre>
  <p>I got:</p>
  <pre><code>---------------------------------------------------------------------------
  TypeError                                 Traceback (most recent call last)
  &lt;ipython-input-18-d9f848d1f6bf&gt; in &lt;module&gt;()
       14 
       15 # sort the values using the key parameter in sort_values
  ---&gt; 16 df.sort_values(by='QID', key=lambda col: np.argsort(ins(col))).reset_index(drop=True)
  
  TypeError: sort_values() got an unexpected keyword argument 'key'
  </code></pre>