Join two dataframes on values within the second dataframe
<p>I am trying to join two dataframes off of values within the dataset:</p>

<pre><code>df1     t0      t1      text0   text1
ID                                  
2133    7.0     3.0     NaN     NaN
1234    10.0    8.0     NaN     NaN
7352    9.0     7.0     NaN     NaN
2500    7.0     6.0     NaN     NaN
3298    10.0    8.0     NaN     NaN
</code></pre>

<p>df1 (seen above)</p>

<pre><code>df2     score   text_org
ID                                  
2133    7.0     asdf
2500    7.0     cccc
3298    8.0     ytyt  
2133    3.0     qwer
1234    10.0    pois
7352    9.0     ijsd
7352    7.0     bdcs
3298    10.0    swed
1234    8.0     zzzz
2500    6.0     erer
</code></pre>

<p>and df2 (seen above)</p>

<p>I am trying to combine the two dataframes so that the NaNs in df1 are replaced with the text_org from df2. As you can see, we get the text by matching the ID with the score from either t0 or t1. Ideally it would look something like this:</p>

<pre><code> df1     t0     t1      text0   text1
ID                                  
2133    7.0     3.0     asdf    qwer
1234    10.0    8.0     pois    zzzz
7352    9.0     7.0     ijsd    bdcs
2500    7.0     6.0     cccc    erer
3298    10.0    8.0     swed    ytyt
</code></pre>

<p>I was trying to use pd.merge - doing to joins, but I haven't been getting anywhere. Thanks for any help! </p>

<p>You can use first <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.melt.html" rel="nofollow"><code>melt</code></a> for reshaping with <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.drop.html" rel="nofollow"><code>drop</code></a> empty columns <code>text0</code> and <code>text1</code>:</p>

<pre><code>df = pd.melt(df1.drop(['text0','text1'], axis=1), id_vars='ID', value_name='score')
print (df)
     ID variable  score
0  2133       t0    7.0
1  1234       t0   10.0
2  7352       t0    9.0
3  2500       t0    7.0
4  3298       t0   10.0
5  2133       t1    3.0
6  1234       t1    8.0
7  7352       t1    7.0
8  2500       t1    6.0
9  3298       t1    8.0
</code></pre>

<p>Then <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.merge.html" rel="nofollow"><code>merge</code></a> by inner join (parameter <code>how='inner'</code> is by default, so it is omit) and also is omit <code>on=['ID','score']</code> because in both <code>DataFrames</code> are only common this 2 columns:</p>

<pre><code>df = pd.merge(df2, df)
print (df)
     ID  score text_org variable
0  2133    7.0     asdf       t0
1  2500    7.0     cccc       t0
2  3298    8.0     ytyt       t1
3  2133    3.0     qwer       t1
4  1234   10.0     pois       t0
5  7352    9.0     ijsd       t0
6  7352    7.0     bdcs       t1
7  3298   10.0     swed       t0
8  1234    8.0     zzzz       t1
9  2500    6.0     erer       t1
</code></pre>

<p>Last reshape again by <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.unstack.html" rel="nofollow"><code>unstack</code></a> and set column names by <code>df1</code> without first column (<code>[1:]</code>):</p>

<pre><code>df = df.set_index(['ID','variable']).unstack()
df.columns = df1.columns[1:]
print (df)
        t0   t1 text0 text1
ID                         
1234  10.0  8.0  pois  zzzz
2133   7.0  3.0  asdf  qwer
2500   7.0  6.0  cccc  erer
3298  10.0  8.0  swed  ytyt
7352   9.0  7.0  ijsd  bdcs
</code></pre>

<p>EDIT by comment:</p>

<p>You get:</p>

<blockquote>
  <p>ValueError: Index contains duplicate entries, cannot reshape</p>
</blockquote>

<p>Problem is if <code>df2</code> have duplicates by column <code>ID</code> and <code>score</code>.</p>

<p>e.g. new row is added to the end and it has same <code>ID</code> and <code>score</code> as first row (<code>2133</code> and <code>7.0</code>) - so get duplicates:</p>

<pre><code>print (df2)
      ID  score text_org
0   2133    7.0     asdf
1   2500    7.0     cccc
2   3298    8.0     ytyt
3   2133    3.0     qwer
4   1234   10.0     pois
5   7352    9.0     ijsd
6   7352    7.0     bdcs
7   3298   10.0     swed
8   1234    8.0     zzzz
9   2500    6.0     erer
10  2133    7.0  new_val
</code></pre>

<p>After merge you can check first and second column - for same <code>ID</code> with <code>score</code> you get 2 values - <code>asdf</code> and <code>new_val</code>, so get error:</p>

<pre><code>df = pd.merge(df2, df)
print (df)
      ID  score text_org variable
0   2133    7.0     asdf       t0
1   2133    7.0  new_val       t0
2   2500    7.0     cccc       t0
3   3298    8.0     ytyt       t1
4   2133    3.0     qwer       t1
5   1234   10.0     pois       t0
6   7352    9.0     ijsd       t0
7   7352    7.0     bdcs       t1
8   3298   10.0     swed       t0
9   1234    8.0     zzzz       t1
10  2500    6.0     erer       t1
</code></pre>

<p>Solution is <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.pivot_table.html" rel="nofollow"><code>pivot_table</code></a> with some aggreagate function or remove duplicates in <code>df2</code> (e.g. use <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.drop_duplicates.html" rel="nofollow"><code>drop_duplicates</code></a>):</p>

<pre><code>#aggregate function is first
df3 = df.pivot_table(index='ID', columns='variable', aggfunc='first')
df3.columns = df1.columns[1:]
print (df3)
      t0 t1 text0 text1
ID                     
1234  10  8  pois  zzzz
2133   7  3  asdf  qwer
2500   7  6  cccc  erer
3298  10  8  swed  ytyt
7352   9  7  ijsd  bdcs

#aggregate function is last
df4 = df.pivot_table(index='ID', columns='variable', aggfunc='last')
df4.columns = df1.columns[1:]
print (df4)
      t0 t1    text0 text1
ID                        
1234  10  8     pois  zzzz
2133   7  3  new_val  qwer
2500   7  6     cccc  erer
3298  10  8     swed  ytyt
7352   9  7     ijsd  bdcs
</code></pre>

<p>Better explanation with another sample in <a class='doc-link' href="http://stackoverflow.com/documentation/pandas/1463/reshaping-and-pivoting/4771/pivoting-with-aggregating#t=201610130747216681484">SO documentation</a>.</p>

python python-3.x pandas join dataframe