Q:

create a pandas data frame from dictionary

d = {'X':{'a':['a1'], 'b':['b1']}, 'Y':{'c':['c1','c2'],'d':['d1','d2']}}
df = pd.DataFrame({'variable':['a1','b1','c1','c2','d1','d2'], 'value':[54,67,25,16,34,24]})

I'm trying to replace the values in d ('a1','b1', etc) with the corresponding values in df (54,67,etc). Ideally I would like to store these in a pandas data frame, and add X and Y with the combined values like this:
X  [54, 67]
a  [54]
b  [67]
Y  [25,16,34,24]
c  [25,16]
d  [34,24]

So far I have created list comprehensions to loop through the dictionary values, but it looks a bit clumsy.
Is there a more direct approach to achieve the desired output?

A:

In short:
repl_values = dict(df.to_dict(orient='split')['data']) 

_df = pd.DataFrame(d).unstack().dropna().apply(
      lambda x: pd.Series(x).replace(repl_values).tolist())

> _df.groupby(level=0).sum() 

X            [54, 67]
Y    [25, 16, 34, 24]
dtype: object

> _df.groupby(level=1).sum() 

a        [54]
b        [67]
c    [25, 16]
d    [34, 24]
dtype: object

Explained:
To do it with pandas, you can start by converting df to a dictionary to make it easier to manipulate:
repl_values = dict(df.to_dict(orient='split')['data'])    

> repl_values
{'a1': 54, 'b1': 67, 'c1': 25, 'c2': 16, 'd1': 34, 'd2': 24}

Then we can play with the d to try to make a dataframe were we can substitute the values by the ones in repl_values
> pd.DataFrame(d)
      X         Y
a  [a1]       NaN
b  [b1]       NaN
c   NaN  [c1, c2]
d   NaN  [d1, d2]

We can try to group these values and remove the NaN:
grouped = pd.DataFrame(d).unstack().dropna()

> grouped

X  a        [a1]
   b        [b1]
Y  c    [c1, c2]
   d    [d1, d2]
dtype: object

The values in grouped can be replaced using pandas.DataFrame.replace. The trick here is that, since each cell contains a list, we can apply replace to each cell by previously converting it to a pd.Series:
> grouped.apply(lambda x: pd.Series(x).replace(repl_values))  

        0     1
X a  54.0   NaN
  b  67.0   NaN
Y c  25.0  16.0
  d  34.0  24.0

To have the values in lists, we can use pd.Series.tolist():
_df = grouped.apply(lambda x: pd.Series(x).replace(repl_values).tolist()) 
> _df

X  a        [54]
   b        [67]
Y  c    [25, 16]
   d    [34, 24]
dtype: object

In this last dataframe we have all the info we need. To obtain the values grouped we just need to do:  
_df.groupby(level=0).sum() 

X            [54, 67]
Y    [25, 16, 34, 24]
dtype: object

or: 
_df.groupby(level=1).sum() 

a        [54]
b        [67]
c    [25, 16]
d    [34, 24]
dtype: object

