EN VI

Python - Append lists of elements as rows in df based on groupby?

How to Python - Append lists of elements as rows in df based on groupby

I have two pandas dataframed called df and legend:

df = pd.DataFrame({'object': ['dog', 'dog', 'cat', 'mouse'],
                   'personID': [1, 1, 2, 3],
                   'word': ['paw', 'head', 'whisker', 'tail'], 
                   'included': [1, 1, 1, 1]})

legend = pd.DataFrame({'object': ['dog', 'cat', 'mouse'],
                       'word_lists': [
                            ['paw', 'head', 'nose', 'body'], 
                            ['whisker', 'ears', 'eyes'], 
                            ['ears', 'tail', 'fur']]})

I am trying to append the words in "legend['word_lists']" based on the "object". Specifically, I am trying append these word_lists based df.groupby(['object', 'person']) so that each group of object and person gets these new words.

I am also keeping track of which words were originally included in the column "df['included']". All new words should receive a 0. Here's my desired output:

result_df = pd.DataFrame({
    'object': ['dog', 'dog',  'dog', 'dog', 'dog','dog','cat', 'cat','cat','cat','mouse', 'mouse', 'mouse', 'mouse'],
    'personID': [1, 1, 1, 1, 1, 1, 2, 2, 2, 2, 3, 3, 3, 3],
    'word': ['paw', 'head', 'paw', 'head', 'nose', 'body', 'whisker', 'whisker', 'ears', 'eyes', 'tail', 'ears', 'tail', 'fur'], 
    'included': [1, 1, 0, 0, 0, 0, 1, 0, 0, 0, 0, 1, 0, 0]})

Solution:

IIUC, explode, merge, and concat:

out = (pd.concat([
       df,
       legend
        .explode('word_lists')
        .rename(columns={'word_lists': 'word'})
        .merge(df[['object', 'personID']].drop_duplicates())
        .assign(included=0),
         ])
         .sort_values(['personID', 'object'])
       )

Output:

  object  personID     word  included
0    dog         1      paw         1
1    dog         1     head         1
0    dog         1      paw         0
1    dog         1     head         0
2    dog         1     nose         0
3    dog         1     body         0
2    cat         2  whisker         1
4    cat         2  whisker         0
5    cat         2     ears         0
6    cat         2     eyes         0
3  mouse         3     tail         1
7  mouse         3     ears         0
8  mouse         3     tail         0
9  mouse         3      fur         0