pandas: How split column string to 2 rows with same others columns

Question

My problem is how split string in column='color' with one ' ' or two ' ' empty space to two rows with same others columns

import pandas as pd
data = {'color': ['black green', 'red', ' white orange', 'jack','blue  green'],
        'Name': ['Tom', 'nick', 'krish', 'jack','bob'],
        'level_1': [2, -3, 7, -4,-2],
        }

df = pd.DataFrame(data)

print(df)
print('the solution must be like:')
solution = {'color': ['black','green', 'red', ' white','orange', 'jack','blue','green'],
        'Name': ['Tom', 'Tom','nick', 'krish', 'krish', 'jack','bob','bob'],
        'level_1': [2, 2,-3,7, 7, -4,-2,-2],
       }

df2 = pd.DataFrame(solution)

print(df2)

score 1 · Accepted Answer · answered May 08 '23 at 11:19

You can first split the color column by spaces, then explode on that column:

import pandas as pd

data = {
    'color': ['black green', 'red', ' white orange', 'jack','blue  green'],
    'Name': ['Tom', 'nick', 'krish', 'jack','bob'],
    'level_1': [2, -3, 7, -4,-2],
}

df = pd.DataFrame(data)

df['color'] = df['color'].str.strip().str.split(r'\s+', regex=True)
df = df.explode('color').reset_index(drop=True)
print(df)

Output:

    color   Name  level_1
0   black    Tom        2
1   green    Tom        2
2     red   nick       -3
3   white  krish        7
4  orange  krish        7
5    jack   jack       -4
6    blue    bob       -2
7   green    bob       -2

pandas: How split column string to 2 rows with same others columns

1 Answers1