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>Say you start with</p>

<pre><code>In [37]: df = pd.DataFrame({'a': [1, 1, 2, 2], 'b': [1, None, 2, 4], 'c': ['foo', 'sho', 'sha', 'bar']})

In [43]: df
Out[43]: 
   a    b    c
0  1  1.0  foo
1  1  NaN  foo
2  2  2.0  sha
3  2  4.0  bar
</code></pre>

<p>Then you can apply the same function to either <code>b</code> or <code>c</code>, taking care of the NaNs and duplicates:</p>

<pre><code>In [44]: df.b.groupby(df.a).apply(lambda x: '' if x.isnull().any() else ','.join(set(x.astype(str).values)))
Out[44]: 
a
1           
2    2.0,4.0
dtype: object

In [45]: df.c.groupby(df.a).apply(lambda x: '' if x.isnull().any() else ','.join(set(x.astype(str).values)))
Out[45]: 
a
1        foo
2    sha,bar
dtype: object
</code></pre>

python python-3.x pandas missing-data