Renaming new split columns with prefix

April 5, 2022

I have a dataframe, which includes two columns which are dicts.

      type                              possession_team
0  {'id': 35, 'name': 'Starting XI'}  {'id':9101,'name':'San Diego Wave'}
1  {'id': 35, 'name': 'Starting XI'}  {'id':9101,'name':'San Diego Wave'}         
2  {'id': 18, 'name': 'Half Start'}   {'id':9101,'name':'San Diego Wave'}         
3  {'id': 18, 'name': 'Half Start'}   {'id':9101,'name':'San Diego Wave'}        
4  {'id': 30, 'name': 'Pass'}         {'id':9101,'name':'San Diego Wave'}

I use

pd.concat([df, df['type'].apply(pd.Series)], axis = 1).drop('type', axis = 1)

to split the columns manually at the minute. How would I use this code, but also add a prefix to the resulting columns that it creates? The prefix being that of the resulting columns that it creates, so I would have;

     type_id type_name                             
0     35    'Starting XI'
1     35    'Starting XI'         
2     18    'Half Start'        
3     18    'Half Start'   
4     30      'Pass'

>Solution :

IIUC, and assuming dictionaries, you could do:

df['type_id'] = df['type'].str['id']
df['type_name'] = df['type'].str['name']

For a more generic approach:

for c in df['type'].explode().unique():
    df[f'type_{c}'] = df['type'].str[c]

And even more generic (apply to all columns):

for col in ['type', 'possession_team']: # or df.columns
    for c in df[col].explode().unique():
        df[f'{col}_{c}'] = df[col].str[c]

output:

                                type                         possession_team  \
0  {'id': 35, 'name': 'Starting XI'}  {'id': 9101, 'name': 'San Diego Wave'}   
1  {'id': 35, 'name': 'Starting XI'}  {'id': 9101, 'name': 'San Diego Wave'}   
2   {'id': 18, 'name': 'Half Start'}  {'id': 9101, 'name': 'San Diego Wave'}   
3   {'id': 18, 'name': 'Half Start'}  {'id': 9101, 'name': 'San Diego Wave'}   
4         {'id': 30, 'name': 'Pass'}  {'id': 9101, 'name': 'San Diego Wave'}   

   type_id    type_name  possession_team_id possession_team_name  
0       35  Starting XI                9101       San Diego Wave  
1       35  Starting XI                9101       San Diego Wave  
2       18   Half Start                9101       San Diego Wave  
3       18   Half Start                9101       San Diego Wave  
4       30         Pass                9101       San Diego Wave