pandas: dataframe from dict with comma separated values
<p>I'm trying to create a DataFrame from a nested dictionary, where the values are in comma separated strings.</p>

<p>Each value is nested in a dict, such as:</p>

<pre><code>dict = {"1":{
             "event":"A, B, C"},
        "2":{
             "event":"D, B, A, C"},
        "3":{
             "event":"D, B, C"}
        }
</code></pre>

<p>My desired output is:</p>

<pre><code>    A   B   C   D
0   A   B   C   NaN
1   A   B   C   D
2   NaN B   C   D
</code></pre>

<p>All I have so far is converting the dict to dataframe and splitting the items in each list. But I'm not sure this is getting me any closer to my objective.</p>

<pre><code>df = pd.DataFrame(dict)
Out[439]: 
           1           2        3
event  A, B, C  D, B, A, C  D, B, C

In [441]: df.loc['event'].str.split(',').apply(pd.Series)                                                                                                                                                                                 
Out[441]: 
   0   1   2    3
1  A   B   C  NaN
2  D   B   A    C
3  D   B   C  NaN
</code></pre>

<p>Any help is appreciated. Thanks</p>

<p>From what you have(modified the split a little to strip the extra spaces) <code>df1</code>, you can probably just <code>stack</code> the result and use <code>pd.crosstab()</code> on the index and value column:</p>

<pre><code>df1 = df.loc['event'].str.split('\s*,\s*').apply(pd.Series) 

df2 = df1.stack().rename('value').reset_index()
pd.crosstab(df2.level_0, df2.value)

#   value   A   B   C   D
# level_0               
#       1   1   1   1   0
#       2   1   1   1   1
#       3   0   1   1   1
</code></pre>

<p>This is not exactly as you asked for, but I imagine you may prefer this to your desired output.</p>

<p>To get exactly what you are looking for, you can add an extra column which is equal to the value column above and then unstack the index that contains the values:</p>

<pre><code>df2 = df1.stack().rename('value').reset_index()
df2['value2'] = df2.value
df2.set_index(['level_0', 'value']).drop('level_1', axis = 1).unstack(level = 1)

#         value2
#   value   A     B     C     D
# level_0               
#       1   A     B     C  None
#       2   A     B     C     D
#       3   None  B     C     D
</code></pre>

python pandas dataframe