Python: Replace df1.col values with df2.col2 values
<p>I have two data frames df1 and df2. 
In df1 I have 50 columns and in df2 I have 50+ columns. What I want to achieve is that 
In df1 I have 13000 rows and a column name subject where names of all subjects are given.
In df2 I have 250 rows and along 50+ I have two columns named subject code and subject_name. </p>

<pre><code>        Here is an example of my datasets:

        df1 = 
        index     subjects
        0         Biology
        1         Physicss
        2         Chemistry
        3         Biology
        4         Physics
        5         Physics
        6         Biolgy

    df2 = 
        index     subject_name    subject_code
        0         Biology         BIO
        1         Physics         PHY
        2         Chemistry       CHE
        3         Medical         MED
        4         Programming     PRO
        5         Maths           MAT
        6         Literature      LIT 

My desired output in df1 (after replacing subject_name and fixing the spelling errors) is:
            index     subjects        subject_code
            0         Biology         BIO
            1         Physics         PHY
            2         Chemistry       CHE
            3         Biology         BIO
            4         Physics         PHY
            5         Physics         PHY
            6         Biology         BIO
</code></pre>

<p>What happens at my end is that I want to merge all subject values in df1 with values in df2 subject name values. In df1 there are around 500 rows where I get NAN after I merge both the columns in one as in these 500 rows there is some difference in the spellings of the subject. 
 I have tried solution given at the following links but didn't work for me:
<a href="http://stackoverflow.com/questions/34946913/replace-df-index-values-with-values-from-a-list-but-ignore-empty-strings">replace df index values with values from a list but ignore empty strings</a></p>

<p><a href="http://stackoverflow.com/questions/31751230/python-pandas-replace-values-multiple-columns-matching-multiple-columns-from-an">Python pandas: replace values multiple columns matching multiple columns from another dataframe</a></p>

<pre><code>            Here is my code:

            df_merged = pd.merge(df1_subject,df2_subjectname, left_on='subjects', right_on='subject_name')
        df_merged.head()
</code></pre>

<p>Can anyone tell me how I can fix this issue as I have already spend 8 hours on this issue but am unable tor resolve it.</p>

<p>Cheers</p>

<p>Correct the spelling then merge...</p>

<pre><code>import pandas as pd
import operator, collections

df1 = pd.DataFrame.from_items([("subjects",
                                ["Biology","Physicss","Phsicss","Chemistry",
                                 "Biology","Physics","Physics","Biolgy","navelgazing"])])
df2 = pd.DataFrame.from_items([("subject_name",
                                ["Biology","Physics","Chemistry","Medical",
                                 "Programming","Maths","Literature"]),
                               ("subject_code",
                                ["BIO","PHY","CHE","MED","PRO","MAT","LIT"])])
</code></pre>

<p>Find the  misspellings:</p>

<pre><code>misspelled = set(df1.subjects) - set(df2.subject_name)
</code></pre>

<p>Find the subject that matches the misspelling best and create a dictionary -> {mis_sp : subject_name}</p>

<pre><code>difference = operator.itemgetter(1)
subject = operator.itemgetter(0)
def foo1(word, candidates):
    '''Returns the most likely match for a misspelled word
    '''
    temp = []
    for candidate in candidates:
        count1 = collections.Counter(word)
        count2 = collections.Counter(candidate)
        diff1 = count1 - count2
        diff2 = count2 - count1
        diff = sum(diff1.values())
        diff += sum(diff2.values())
        temp.append((candidate, diff))
    return subject(min(temp, key = difference))

def foo2(words):
    '''Yields (misspelled-word, corrected-word) tuples from misspelled words'''
    for word in words:
        name = foo1(word, df2.subject_name)
        if name:
            yield (word, name)

d = dict(foo2(misspelled))               
</code></pre>

<p>Correct all the misspellings in df1</p>

<pre><code>def foo3(thing):
    return d.get(thing, thing)

df3 = df1.applymap(foo3)
</code></pre>

<p>Merge</p>

<pre><code>df2 = df2.set_index("subject_name")
df3 = df3.merge(df2, left_on = "subjects", right_index = True, how = 'left')
</code></pre>

<hr>

<p><code>foo1</code> might possibly be sufficient for this purpose, but there are better, more sophisticated, algorithms to correct spelling.  maybe, <a href="http://norvig.com/spell-correct.html" rel="nofollow">http://norvig.com/spell-correct.html</a></p>

<p>Just read @conner's solution.  I didn't know difflib was there so a better <code>foo1</code> would be,</p>

<pre><code>def foo1(word, candidates):
    try:
        return difflib.get_close_matches(word, candidates, 1)[0]
    except IndexError as e:
        # there isn't a close match
        return None
</code></pre>

python pandas