0
$\begingroup$

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?

$\endgroup$

1 Answer 1

1
$\begingroup$

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
$\endgroup$
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.