How do I deconcatenate or unconcatenate a string value in a pandas dataframe?

Viewed 566

im new to python and want to understand how to work with dataframes

I have a dataframe-

 desc13month
Out[45]: 
                                            OutputValues  CntOutputValues
0                                  12-99-Annual (AE)                  217
1             21-581-Ineligible  Services(IPS)                        210
2      125-99-Annual (AE),126-22-Jermaine (JE)                        196
3                                  22-99-Annual (AE)                  181
4                                  21-50-Prime (PE)                  169

I want the OutputValues column to be unconcatenated. If you notice, the last string for example "Annual" depends on the second string 99. if its is 22 it changes to Jermaine.Sometimes there could be multiple like we see for row 2. My final output should indicate these columns and an additional column which has that last string

illustration-

desc13month
Out[45]: 
                                            OutputValues  CntOutputValues   final
0                                  12-99-Annual (AE)                  217   Annual (AE)
1             21-581-Ineligible  Services(IPS)                        210   Ineligible  Services(IPS) 
2      125-99-Annual (AE),126-22-Jermaine (JE)                        196   Annual (AE),Jermaine (JE) 
3                                  22-99-Annual (AE)                  181   Annual (AE)
4                                  21-50-Prime (PE)                   169   Prime (PE)
2 Answers

IIUC, Let's try Series.str.replace:

df['final'] = df['OutputValues'].str.replace(r'\d+-\d+-', '')

                              OutputValues  CntOutputValues                      final
0                        12-99-Annual (AE)              217                Annual (AE)
1         21-581-Ineligible  Services(IPS)              210  Ineligible  Services(IPS)
2  125-99-Annual (AE),126-22-Jermaine (JE)              196  Annual (AE),Jermaine (JE)
3                        22-99-Annual (AE)              181                Annual (AE)
4                         21-50-Prime (PE)              169                 Prime (PE)

There are two parts to your question, one is handling the strings, and the other, applying that to the data frame. For handling the strings, if the patterns remain the same meaning you are sure that each string will be digits-digits-chars and multiple values are separated by ',' then you can use something like this function:

def deconcat(output_value):
    output_value = output_value.split(',')
    result = ''
    for part in output_value:
        _, _, item = part.split('-')
        result += item + ", "
    return result.rstrip(', ')

The function takes a string, splits it by ',' if there are multiple values, then for each value, splits by '-' and adds the third part to a resulting string.

Now you only have to apply this function to the whole dataframe and create your new column:

df['final'] = df.OutputValues.apply(deconcat)

This will apply the function to each row of the OutputValues in the dataframe, and add the resulting string to a new column called 'final'.

Related