extract number after specific of list string in pandas dataframe

July 23, 2022

I have this example dataset

No Text
1  {"duration_incoming_daytime": 6034, 
    "percent_incoming_daytime": 42.73, 
    "percent_other_calls": 42.73, 
    "total_calls": 110}

all I want is extract the number after specific string, "duration_incoming_daytime", "total_calls"
please, dont use str.split(), because my data isn’t ordered like the example
so, it would be like this

No Text                                  duration_incoming_daytime   total_calls
1  {"duration_incoming_daytime": 6034,   6034                        110
    "percent_incoming_daytime": 42.73, 
    "percent_other_calls": 42.73, 
    "total_calls": 110}

Here’s the example dataframe

import pandas as pd   
No = [1]   
Text = [{"duration_incoming_daytime": 6034, "percent_incoming_daytime": 42.73, "percent_other_calls": 42.73, "total_calls": 110}]   

df = pd.DataFrame({"No":No, "Text":Text})

>Solution :

You could use JSON functions here, but given that the JSON is not nested, str.extract can also work:

df["duration_incoming_daytime"] = df["No Text"].str.extract(r'"duration_incoming_daytime"\s*:\s*(\d+)', regex=True)
df["total_calls"] = df["No Text"].str.extract(r'"total_calls"\s*:\s*(\d+)', regex=True)