Reference Column Name with Spaces-CodePudding

Beginner question: I am trying to use the following line of code but am getting syntax errors:

db = db.drop('Aggregated Alliance Products', axis=1).join(db.Aggregated Alliance Products.str.split(', ', expand=True).stack().to_frame('Aggregated Alliance Products').reset_index(1, drop=True))

When I use db.Aggregated Alliance Products.str.split(', ', expand=True), how do I adjust the column name Aggregated Alliance Products to accomodate the spaces in it?

Sample:

A	B	Aggregated Alliance Products
1	2	"1,2,4"
3	4	"5,6"

Desired Output:

A	B	Aggregated Alliance Products
1	2	1
1	2	2
1	2	4
3	4	5
3	4	6

CodePudding user response：

EDIT

or you can use assign to acheive your goal without modifying original data as follows:

db.assign(**{'Aggregated Alliance Products': db['Aggregated Alliance Products'].str.split(',')}).explode('Aggregated Alliance Products')

if you can modify db itself, you can use explode func like as follows:

db = pd.DataFrame([(1, 2, '1,2,4'), (3, 4, '5,6')], columns=['A', 'B', 'Aggregated Alliance Products'])
db['Aggregated Alliance Products'] = db['Aggregated Alliance Products'].apply(lambda x: x.split(','))
db.explode('Aggregated Alliance Products')

CodePudding user response：

You can use this:

k=db['Aggregated Alliance Products'].str.split(',', expand=True).stack().to_frame('Aggregated Alliance Products')

db[["A","B"]].merge(k, left_on=db.index.get_level_values(0), right_on=k.index.get_level_values(0)).drop('key_0', axis=1)

Output:

    A   B   Aggregated Alliance Products
0   1   2   1
1   1   2   2
2   1   2   4
3   3   4   5
4   3   4   6