How do I parse a string of URL query parameters into multiple columns of a pandas Dataframe?

Question

Each row in my dataframe has a string containing some URL query parameters, e.g. flt=promotionflag%3A1%3Borganicfilter%3AOrganic&sortBy=MOST_POPULAR. The flt section could contain multiple parameters as in this example.

I want to parse this string into multiple columns like:

flt_promotionflag	flt_organicfilter	sortBy
1	Organic	MOST_POPULAR

There could be lots of different filters so I don't want to hardcode these as column names. If there is already a column with that filter name I want to put the value in the existing column, and if there there isn't a column with that filter name I want to create it.

I've written some code that creates a dictionary in the structure I want in a new column but I think that's probably an unnecessary step.

def createDict(string):
    try:
      d = dict(x.split("=") for x in string.strip("&").split("&"))
      if 'flt' in d:
        if '%3B' in d['flt']:
            d['flt'] = dict(x.split("%3A") for x in d['flt'].split("%3B"))
        else:
            d['flt'] = {d['flt'].split("%3A")[0] : 1}
      else:
        pass
      return d
    except:
      pass

df['Parsed params'] = df['URL Query Parameters'].apply(createDict)

How do I get the data I want in the right columns?

Thanks @RobRaymond I had a go with urllib but it seemed to not be recognising this as a bit of URL because it doesn't start with http/s. Is that right or do I need to persevere with it? Also, my issue isn't parsing the string, it's getting the sections into the right columns. — Abbie
– Abbie, Commented Feb 9, 2021 at 10:07

Rob Raymond · Accepted Answer · 2021-02-09 10:53:04Z

1

All the utilities you need are already in place

urllib.parse.parse_qs() generates a dict from a URL query string
constructor of DataFrame()
further expand parameters that are not part of standard URL parsing

df = pd.DataFrame(urllib.parse.parse_qs("flt=promotionflag%3A1%3Borganicfilter%3AOrganic&sortBy=MOST_POPULAR"))

# expand out parameters semi-colon delimited
df = (df
 .assign(flt=df.flt.str.split(";"))
 .explode("flt")
 .reset_index(drop=True)
)
# change colon delimited into key/value columns
df = df.join(df.flt.apply(lambda s: {"key":s.split(":")[0], "value":s.split(":")[1]}).apply(pd.Series))

	flt	sortBy	key	value
0	promotionflag:1	MOST_POPULAR	promotionflag	1
1	organicfilter:Organic	MOST_POPULAR	organicfilter	Organic

edited Feb 9, 2021 at 10:53

answered Feb 9, 2021 at 10:06

Rob Raymond

31.5k3 gold badges19 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Abbie Over a year ago

Any way of parsing out the different filters to separate columns?

Collectives™ on Stack Overflow

How do I parse a string of URL query parameters into multiple columns of a pandas Dataframe?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related