Splitting string column into several columns

Question

In Python, I want to split a string column into multiple columns. The problem is, the strings are not identical or the same length.

Example of data:

Movie         Tier  
Movie 1       DK: T1, SE: T3, NO: T1
Movie 2       NO: T2
Movie 3       DK: T2, SE: T2, NO: T3
Movie 4       SE: T2

How I want it to look like:

Movie         Tier_DK   Tier_SE   Tier_NO  
Movie 1       T1        T3        T1 
Movie 2       NaN       NaN       T2
Movie 3       T2        T2        T3
Movie 4       NaN       T2        NaN

Does anyone know how to split the 'Tier'-column into three different columns as shown?

Answer 1

You can use the str.split method from pandas to split a column into multiple columns:

import pandas as pd

df = pd.DataFrame({
    "Movie": ["Movie 1", "Movie 2", "Movie 3", "Movie 4"],
    "Tier": ["DK: T1, SE: T3, NO: T1", "NO: T2", "DK: T2, SE: T2, NO: T3", "SE: T2"]
})

df["Tier"].str.split(",", expand=True)

0	1	2
DK: T1	SE: T3	NO: T1
NO: T2
DK: T2	SE: T2	NO: T3
SE: T2

However, given the fact that the order in the input data is not always the same (e.g. 'SE' and 'NO' are switched between the first and third input) you will get results from different values in the same column. If you know that the input data only contains the specific three values shown here (DK, SE, and NO) you can manually extract the values using str.extract and a regular expression:

(
    df
    .assign(
        Tier_DK = lambda x: x["Tier"].str.extract("DK: (..)"),
        Tier_SE = lambda x: x["Tier"].str.extract("SE: (..)"),
        Tier_NO = lambda x: x["Tier"].str.extract("NO: (..)")
    )
    .drop("Tier", axis=1)
)

Movie	Tier_DK	Tier_SE	Tier_NO
Movie 1	T1	T3	T1
Movie 2	nan	nan	T2
Movie 3	T2	T2	T3
Movie 4	nan	T2	nan