Pandas - Concat strings after groupby in column, ignore NaN, ignore duplicates
<p>Depending on the query, my DF can have a column with strings or a column with NaN.</p>

<p>Ex:</p>

<pre><code>  ID     grams   Projects
0  891            4.0      NaN
1  725            9.0      NaN
</code></pre>

<p>or</p>

<pre><code>  ID     grams   Projects
0  890            1.0      P1, P2
1  724            1.0      P1
2  880            1.0      P1, P2
3  943            1.0      P1
4  071            1.0      P1
</code></pre>

<p>I can handle one or the other, but when I try to make a function that is generic I'm failing miserably. I need to ignore the NaN at the end, because I'm sending this DF as JSON response and NaN gives me an invalid format.</p>

<p>The way I'm doing right now is:</p>

<pre><code>#When Projects is a string
df['Projects'] = _df.groupby("ID")['External_Id'].apply(lambda x: ",".join(x))

#When Projects is NaN
df['Projects'] = _df.groupby("ID")['External_Id'].apply(lambda x: "")
</code></pre>

<p>I tried to use <code>fillna()</code> and also to check the dtype of 'x' but it always returns as <strong>object</strong>, so I can't check whether it is a <strong>str</strong> or <strong>NaN</strong></p>

<p>Also, the result of the 'Projects' column should not allow duplicates. Some rows when grouped by ID have important information which will be summed ('grams'), but the 'External_Id' should not appear more than once.
Ex:</p>

<pre><code>  ID       grams      External_Id
0  890        1.0      P1
1  890        1.0      P2
2  890        1.0      P2
3  724        1.0      P1
4  724        1.0      P1
</code></pre>

<p>Result should be</p>

<pre><code>  ID       grams      Projects
0  890        3.0      P1, P2
1  724        2.0      P1
</code></pre>

<p>And not</p>

<pre><code>  ID       grams      Projects
0  890        1.0      P1, P2, P2
1  724        1.0      P1, P1
</code></pre>

<p>I think this should help:    </p>

<pre><code>import numpy
df_new = df.replace(numpy.nan,' ', regex=True)
</code></pre>

<p>EDIT:</p>

<p>I think this <a href="http://stackoverflow.com/questions/25401295/using-python-to-remove-duplicated-contents-in-cells-in-excel">solution</a> could work for you (just as an alternative to the answer of @Ami.</p>

python python-3.x pandas missing-data