[FIXED] How to split a column and assign values to different specific columns in pandas?

Issue

I have a dataframe that looks like this:

data = {'Name': ['Mat', 'Sally', 'Bob'],
     "What's Important for you?": ["Public Transport, Universities & Gyms, Shops", "Universities & Gyms", "Public Transport, Shops"]}

df = pd.DataFrame.from_dict(data)

Name	What's important to you?
Mat	Public Transport, Universities & Gyms, Shops
Sally	Universities & Gyms
Bob	Public Transport, Shops

I want to split the What's important to you? column into multiple columns, output shown below.

data = {'Name': ['Mat', 'Sally', 'Bob'],
        "impo1": ["Public Transport", " ", "Public Transport"],
        "impo2": ["Universities & Gyms", "Universities & Gyms", ""],
        "impo3": ["Shops", " ", "Shops"]}

df1 = pd.DataFrame.from_dict(data)
df1

Name	impo1	impo2	impo3
Mat	Public Transport	Universities & Gyms	Shops
Sally		Universities & Gyms
Bob	Public Transport		Shops

Solution

You can split, explode, and pivot:

(df
 .assign(**{"What's Important for you?":
            df["What's Important for you?"].str.split(', ')})
 .explode("What's Important for you?")
 .assign(col=lambda d: d["What's Important for you?"].factorize()[0]+1)
 .pivot('Name', 'col', "What's Important for you?")
 .add_prefix('impo')
)

output:

col               impo1                impo2  impo3
Name                                               
Bob    Public Transport                  NaN  Shops
Mat    Public Transport  Universities & Gyms  Shops
Sally               NaN  Universities & Gyms    NaN

Answered By - mozway