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'
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