Pull parts of a column containing stings and creating dictionaries upon which values can be pulled based on common keys

user_9624403 0
Viewed 62

I have a df that contains strings with multiple data that I want to parse and store as a dictionary. I would like to store PubMed Identifier as a pmid key and thew following digits as its values, Embase as euid and the following digits, NCT as trialid and the following number (no space), and disregard the numbers on there own or disregard PubMed Identifier/Embase without trailing/associated digits.

data = {"ORN": [1, 2, 3, 4],
        "EN": ["PubMed Identifier 27955689", "PubMed Identifier 8010359Embase  24208639", "PubMed Identifier 12237786Embase  35148801", "PubMed Identifier NCT02360007 12537613"]
        }

df = pd.DataFrame(data=data)

    ORN EN
0   1   PubMed Identifier 27955689
1   2   PubMed Identifier 8010359Embase 24208639
2   3   PubMed Identifier 12237786Embase 35148801
3   4   PubMed Identifier NCT02360007 12537613


desired_df

    ORN EN
0   1   {"pmid": 27955689}
1   2   {"pmid":  8010359, "euid": 24208639}
2   3   {"pmid": 12237786, "euid": 35148801}
3   4   {"trialid": 02360007}

I can't understand what I should do wrt best approach. My idea of splitting the string across columns with .split(expand=True) and then reordering the columns and then merging back using a to_dict() is the best I can think of but any better suggestions would be great. String manipulations is something I need improving at.

pandas python-3.x dictionary python string
3 Answers

Extract the data with .str.extract()

Build dict with .apply() + dropna() + to_dict():

  1. Extract the data with .str.extract()
df_extract = df['EN'].str.extract(r'PubMed Identifier\s*(?:(?P<pmid>\d+)(?:Embase\s*(?P<euid>\d+))?)|NCT(?P<trialid>\d+)')

or, use:

df_extract = df['EN'].str.extract(r'PubMed Identifier\s*(?P<pmid>\d+)?\s*(?:Embase\s*(?P<euid>\d+))?(?:NCT\s*(?P<trialid>\d+))?')

Result:

print(df_extract)


       pmid      euid   trialid
0  27955689       NaN       NaN
1   8010359  24208639       NaN
2  12237786  35148801       NaN
3       NaN       NaN  02360007
  1. Build dict with .apply() + dropna() + to_dict():
df_extract.apply(lambda x: x.dropna().to_dict(), axis=1)

Result:

0                        {'pmid': '27955689'}
1     {'pmid': '8010359', 'euid': '24208639'}
2    {'pmid': '12237786', 'euid': '35148801'}
3                     {'trialid': '02360007'}
dtype: object
user_150706970

Create a mapping dictionary, then construct a regular expression pattern in order to find all the key value pairs from each row, then remap the key-value pairs to dict

d = {'PubMed Identifier': 'pmid',
     'Embase': 'emid', 'NCT': 'trialid'}

pat = fr'({"|".join(d)})\s*(\d+)'
df['EN'].str.findall(pat).map(lambda l: {d[k]: v for k, v in l})

0                        {'pmid': '27955689'}
1     {'pmid': '8010359', 'emid': '24208639'}
2    {'pmid': '12237786', 'emid': '35148801'}
3                     {'trialid': '02360007'}
Name: EN, dtype: object
user_128331660

You can use str.extract:

df['EN'].str.extract('PubMed Identifier +(?P<pmid>\d+)|NCT(?P<trialid>\d+)|Embase +(?P<euid>[^ ]+)')

output:

       pmid   trialid euid
0  27955689       NaN  NaN
1   8010359       NaN  NaN
2  12237786       NaN  NaN
3       NaN  02360007  NaN

To have as dict, add .T.to_dict():

df['EN'].str.extract('(?=PubMed Identifier\s+(?P<pmid>[^ ]+\d+))?|(?=Embase\s+(?P<euid>\d+))?').T.to_dict()

output:

{0: {'pmid': '27955689', 'euid': nan},
 1: {'pmid': '8010359', 'euid': nan},
 2: {'pmid': '12237786', 'euid': nan},
 3: {'pmid': 'NCT02360007', 'euid': nan}}
user_163434640
Related