Separate text with starts with the same phrase in a object list into two text columns in pandas dataframe

KateOCM 0
Viewed 61

I am trying to split the object lists starting with C - :\t & A - :\t strings in the dataframe cell into two separate column with the sentences or dialogue for A and another column for C. I need to remain in a pandas dataframe as each cell has a unique key that links to other data. I have managed to find examples with lists but my data is in a pandas dataframe, and I don't seem to be able to get it to work. I am hoping this community can help me in someway.

df_txt['dialogue_sentences']**[10]** = ['',
 'C - :\t hello',
 'A - :\t good morning can i speak to you nox',
 'C - :\t hello',
 'A - :\t hy sien',
 'C - :\t hawu',
 'A - :\t ok',
 'C - :\t yebo ngimi',
 "A - :\t hello good morning i am thomas we're calling you from a financial services how are you",
 'C - :\t ngiyaphila',
 "A - :\t great to know you Nox we're calling you today in relation to your vehicle the mechanic warranty has been expired i'm sure you are aware of this year",
 'C - :\t no',
 "A - :\t it's a mechanical plan on the ford",
 'C - :\t hayi so i sold it',
 "A - :\t so you don't have this car anymore the ford",
 "C - :\t ja yes i don't phone it",
 "A - :\t or you don't have",
 'C - :\t ne',
 'A - :\t any car',
 'C - :\t in',
 "A - :\t okay alright no problem i'll make a note for all right",
 'C - :\t thank you',
 'A - :\t righto sharp bye',
 '']

Outcome to look like this:

df_txt['dialogue_c']**[10]** = ['hello',
 'hello',
 'hawu',
 'yebo ngimi',
 'ngiyaphila',
 'no',
 'hayi so i sold it',
 'ja yes i don't phone it',
 'ne',
 'in',
 'thank you']

and

df_txt['dialogue_a']**[10]** = ['good morning can i speak to you nox',
 'hy sien',
 'ok',
 'hello good morning i am thomas we're calling you from a financial services how are you',
 'great to know you Nox we're calling you today in relation to your vehicle the mechanic warranty has been expired i'm sure you are aware of this year',
 'it's a mechanical plan on the ford',
 'so you don't have this car anymore the ford',
 'or you don't have',
 'any car',
 'okay alright no problem i'll make a note for all right',
 'righto sharp bye']

The data arrives with no punctuation except for contractions with apostrophes. Each conversation differs in length but always alternates between C - :\t & A - :\t sentences or dialogue.

text dataframe pandas python
2 Answers
for letter in ('A', 'C'):
    df_txt[f'dialogue_{letter.lower()}'] = df_txt[df_txt['dialogue_sentences'].str.startswith(f'{letter} - :\t')][
        'dialogue_sentences'].str.replace(f'{letter} - :\t', '', regex=False).str.strip()
pd.options.display.width = None
print(df_txt)

Output:

                                   dialogue_sentences                                         dialogue_a               dialogue_c
0                                                                                                    NaN                      NaN
1                                       C - :\t hello                                                NaN                    hello
2         A - :\t good morning can i speak to you nox                good morning can i speak to you nox                      NaN
3                                       C - :\t hello                                                NaN                    hello
4                                     A - :\t hy sien                                            hy sien                      NaN
5                                        C - :\t hawu                                                NaN                     hawu
6                                          A - :\t ok                                                 ok                      NaN
7                                  C - :\t yebo ngimi                                                NaN               yebo ngimi
8   A - :\t hello good morning i am thomas we're c...  hello good morning i am thomas we're calling y...                      NaN
...
Алексей Р0

This code creates the dataframe

    import pandas as pd
    
    #dialogue for one row
    dialogue = ['',
     'C - :\t hello',
     'A - :\t good morning can i speak to you nox',
     'C - :\t hello',
     'A - :\t hy sien',
     'C - :\t hawu',
     'A - :\t ok',
     'C - :\t yebo ngimi',
     "A - :\t hello good morning i am thomas we're calling you from a financial services how are you",
     'C - :\t ngiyaphila',
     "A - :\t great to know you Nox we're calling you today in relation to your vehicle the mechanic warranty has been expired i'm sure you are aware of this year",
     'C - :\t no',
     "A - :\t it's a mechanical plan on the ford",
     'C - :\t hayi so i sold it',
     "A - :\t so you don't have this car anymore the ford",
     "C - :\t ja yes i don't phone it",
     "A - :\t or you don't have",
     'C - :\t ne',
     'A - :\t any car',
     'C - :\t in',
     "A - :\t okay alright no problem i'll make a note for all right",
     'C - :\t thank you',
     'A - :\t righto sharp bye',
     '']
    
    df_txt1 = pd.DataFrame(dialogue, columns=['dialogue_sentences']).assign(call=0)
    df_txt2 = pd.DataFrame(dialogue, columns=['dialogue_sentences']).assign(call=1)
    df_txt3 = pd.DataFrame(dialogue, columns=['dialogue_sentences']).assign(call=2)
    
    frames = [df_txt1, df_txt2, df_txt3]
    features = ['call', 'dialogue_sentences']
    
    df_txt = pd.concat(frames)
    df_txt = df_txt[features]
    #df_txt.head(50)
    
    # group rows into list
    df = df_txt.groupby('call', as_index=False ).agg({'dialogue_sentences': lambda x: x.tolist()})
   
    df.head()

The Dataframe with Dialogue per call

so that

df['dialogue_sentences'][0]

gives Dialogue for row 0

Use Explode Function Explode function is used to split the list dialogue for each call.

df = df.explode('dialogue_sentences').reset_index(drop=True)

Exploded Dataframe

Now use Алексей Р code in Answer 1 above

for letter in ('A', 'C'):
    df[f'dialogue_{letter.lower()}'] = df[df['dialogue_sentences'].str.startswith(f'{letter} - :\t')][
        'dialogue_sentences'].str.replace(f'{letter} - :\t', '', regex=False).str.strip()
pd.options.display.width = None
df

This gives Dialogue split in A and C

Final Outcome Required

call_a = pd.DataFrame(df.groupby(['call'], as_index=False).agg({f'dialogue_a': lambda x: [i for i in x.tolist() if str(i) != "nan"]}))
call_c = pd.DataFrame(df.groupby(['call'], as_index=False).agg({f'dialogue_c': lambda x: [i for i in x.tolist() if str(i) != "nan"]}))

calls = pd.merge(call_a, call_c, how='inner', left_on=['call'] ,right_on=['call'])
calls.head()

Final Outcome Required

A thank you to @Алексей Р for his answer (answer 1 above) which helped me solve my problem.

KateOCM0
Related