Most efficient way to set value in column based on prefix of the index
<p>I have a dataframe like this:</p>

<pre><code>df = pd.DataFrame(index=['pre1_xyz', 'pre1_foo', 'pre3_bar', 'pre3_foo', 'pre10_foo', 'pre10_bar', 'pre10_xyz'])
</code></pre>

<p>to which I want to add a column <code>values</code> whereby the value is determined based on the prefix of the index of the respective row using a function <code>return_something(pref)</code>. Right now I implement that as follows:</p>

<pre><code>import pandas as pd
import numpy as np

# this just returns a random value for the sake of simplicity
def return_something(pref):

    return np.random.choice(len(pref)+10)


df = pd.DataFrame(index=['pre1_xyz', 'pre1_foo', 'pre3_bar', 'pre3_foo', 'pre10_foo', 'pre10_bar', 'pre10_xyz'])

# get all the unique prefixes
unique_pref = set([pi.partition('_')[0] for pi in df.index])

# determine the value for each prefix
val_pref = {pref: return_something(pref) for pref in unique_pref}

# add the values to the dataframe
for prefi, vali in val_pref.items():

    # determine all rows with the same prefix
    rows = [rowi for rowi in df.index if rowi.startswith(prefi+'_')]

    df.loc[rows, 'values'] = vali
</code></pre>

<p>That then gives me the desired output:</p>

<pre><code>           values
pre1_xyz        0
pre1_foo        0
pre3_bar        7
pre3_foo        7
pre10_foo      13
pre10_bar      13
pre10_xyz      13
</code></pre>

<p>Question is whether there is anything smarter than this e.g. a solution which avoids creating <code>unique_pref</code> and/or <code>val_pref</code> and/or makes use of <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.set_value.html" rel="nofollow"><code>set_value</code></a> which seems to be the fastest solution to add values to a dataframe as discussed in <a href="http://stackoverflow.com/questions/13842088/set-value-for-particular-cell-in-pandas-dataframe">this question</a>.</p>

<p>Because you have repeats of the prefix, you want to first separate out the prefix to make sure you don't generate a new random number for the same prefix. Therefore the removal of duplicates is necessary from your prefix list. I did this in a more condensed way by making a new column for the prefix and then using df.prefix.unique(). </p>

<pre><code>df['prefix'] = [i.split('_')[0] for i in df.index]
df['values'] = df.prefix.map(dict(zip(df.prefix.unique(),[return_something(i) for i in df.prefix.unique()])))
</code></pre>

python pandas optimization