Python filling string column "forward" and groupby attaching groupby result to dataframe
<p>I have a dataframe looking generated by:</p>

<pre><code>df = pd.DataFrame([[100, ' tes  t  ', 3], [100, np.nan, 2], [101, ' test1', 3 ], [101,'   ', 4]])
</code></pre>

<p>It looks like</p>

<pre><code>         0      1      2
     0  100    tes t   3
     1  100    NaN     2
     2  101   test1    3
     3  101            4
</code></pre>

<p>I would like to a fill column 1 "forward" with test and test1. I believe one approach would be to work with replacing whitespace by np.nan, but it is difficult since the words contain whitespace as well. I could also groupby column 0 and then use the first element of each group to fill forward. Could you provide me with some code for both alternatives I do not get it coded?</p>

<p>Additionally, I would like to add a column that contains the group means that is
the final dataframe should look like this</p>

<pre><code>         0      1      2  3
     0  100   tes t    3  2.5
     1  100   tes t    2  2.5
     2  101   test1    3  3.5
     3  101   test1    4  3.5
</code></pre>

<p>Could you also please advice how to accomplish something like this?</p>

<p>Many thanks please let me know in case you need further information.</p>

<p>IIUC, you could use <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.Series.str.strip.html" rel="nofollow"><code>str.strip</code></a> and then check if the stripped string is empty.
Then, perform <code>groupby</code> operations and filling the <code>Nans</code> by the method <code>ffill</code> and calculating the means using <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.core.groupby.GroupBy.transform.html" rel="nofollow"><code>groupby.transform</code></a> function as shown: </p>

<pre><code>df[1] = df[1].str.strip().dropna().apply(lambda x: np.NaN if len(x) == 0 else x)

df[1] = df.groupby(0)[1].fillna(method='ffill')
df[3] = df.groupby(0)[2].transform(lambda x: x.mean())
df
</code></pre>

<p><a href="http://i.stack.imgur.com/OH80S.png" rel="nofollow"><img src="http://i.stack.imgur.com/OH80S.png" alt="Image"></a></p>

<p>Note: If you must forward fill <code>NaN</code> values with first element of that group, then you must do this:</p>

<pre><code>df.groupby(0)[1].apply(lambda x: x.fillna(x.iloc[0]))
</code></pre>

<hr>

<p><strong>Breakup of steps:</strong></p>

<p>Since we want to apply the function only on strings, we drop all the <code>NaN</code> values present before, else we would be getting the <code>TypeError</code> due to both floats and string elements present in the column and complains of float having no method as <code>len</code>.</p>

<pre><code>df[1].str.strip().dropna()

0    tes  t    # operates only on indices where strings are present(empty strings included)
2     test1
3          
Name: 1, dtype: object
</code></pre>

<p>The reindexing part isn't a necessary step as it only computes on the indices where strings are present.</p>

<p>Also, the <code>reset_index(drop=True)</code> part was indeed unwanted as the groupby object returns a series after <code>fillna</code> which could be assigned back to column 1.</p>

python pandas