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>You can use a couple of comprehensions to massage the nested dict into a better format for creation of a DataFrame that flags if an entry for the column exists or not:</p>

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

df = pd.DataFrame([[{z:1 for z in y.split(', ')} for y in x.values()][0] for x in the_dict.values()])
&gt;&gt;&gt; df

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

<p>Once you've made the DataFrame you can simply loop through the columns and convert the values that flagged the existence of the letter into a letter using the <code>where</code> method(below this does where NaN leave as NaN, otherwise it inserts the letter for the column):</p>

<pre><code>for col in df.columns:
    df_mask = df[col].isnull()
    df[col]=df[col].where(df_mask,col)
&gt;&gt;&gt; df

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

<p>Based on @merlin's suggestion you can go straight to the answer within the comprehension:</p>

<pre><code>df = pd.DataFrame([[{z:z for z in y.split(', ')} for y in x.values()][0] for x in the_dict.values()])
&gt;&gt;&gt; df
     A  B  C    D
0    A  B  C  NaN
1    A  B  C    D
2  NaN  B  C    D
</code></pre>

python pandas dataframe