Pandas explode on separator but retain suffix in both new records

user_19402221 0
Viewed 42

I have the current code that splits records if a / occurs in the value but I want it to be cloned and retain the non med or 65+ suffix (best method to identify the suffix is probably by using the first space as a delimiter). The Desired out is what I want the output to look like. Current Out is what the code below is outputting

    dfRosters=(dfRosters.set_index(['Volume', 'Premium'])
    .apply(lambda x: x.str.split('/').explode())
    .reset_index())

In
29   312889.0  159834.15       5455/5456 (non med)
56     4168.0    2984.15       7065/7066 65+
26    45405.0   21013.45          5113.0


Desired Out

29   312889.0  159834.15       5455 (non med)
29   312889.0  159834.15       5456 (non med)
56     4168.0    2984.15       7065
56     4168.0    2984.15       7066 65+
26    45405.0   21013.45          5113.0

Current Out

29   312889.0  159834.15       5455
29   312889.0  159834.15       5456 (non med)
56     4168.0    2984.15       7065 65+
56     4168.0    2984.15       7066 65+
26    45405.0   21013.45          5113.0
pandas python
1 Answers

Given:

   col1      col2       col3                 col4
0    29  312889.0  159834.15  5455/5456 (non med)
1    56    4168.0    2984.15        7065/7066 65+
2    26   45405.0   21013.45               5113.0

Doing:

# Split them into separate columns on the first space:
df[['col4', 'col5']] = df.col4.str.split(' ', 1, expand=True)

# Split col4 on '/' and explode it:
df.col4 = df.col4.str.split('/')
df = df.explode('col4')
print(df)

Output:

   col1      col2       col3    col4       col5
0    29  312889.0  159834.15    5455  (non med)
0    29  312889.0  159834.15    5456  (non med)
1    56    4168.0    2984.15    7065        65+
1    56    4168.0    2984.15    7066        65+
2    26   45405.0   21013.45  5113.0       None

Optional:

# Re-combine the columns:
df.col4 = df.col4.astype(int).astype(str)
df.col4 += ' ' + df.col5.fillna('')
df = df.drop('col5', axis=1)
print(df)

# Output:

   col1      col2       col3            col4
0    29  312889.0  159834.15  5455 (non med)
0    29  312889.0  159834.15  5456 (non med)
1    56    4168.0    2984.15        7065 65+
1    56    4168.0    2984.15        7066 65+
2    26   45405.0   21013.45            5113
user_118659560
Related