title: Iterate json with multiple values per key for matching pattern: Set dataframe column value depending on value of other column (Faster Python Solution)
body: <p>Below is the Problem statement.</p>
  <p><strong>Inputs: -</strong></p>
  <ol>
  <li>csv containing Time when a particular URL is hit.</li>
  <li>json containing multiple url_categories with the matching url patters for each url_categories.</li>
  </ol>
  <p><strong>Required Output: -</strong>
  csv, containing Time, url, and url_category. Where url_category is decided based on URL of input csv and url_patterns mentioned per url_category. If the URL doesn't match any of the patterns then category should be marked as 'Other'.</p>
  <p><strong>Objective: -</strong>
  Python code should create the required output is a FAST way.</p>
  <p>Input csv (simplified) which contains the time when a particular URL is hit, like below.</p>
  <pre><code>TIME,URL
  11:51,/url3a/partC 
  12:51,/url6/partA
  13:51,/url7/partA/partA/partB
  14:51,/url5/partA/partB/part1
  15:51,/url3b/partA
  16:51,/url8/partA/partB
  17:51,/url2a/
  18:51,/url5/partA/partB
  19:51,/url1/part1/part2
  20:51,/url4b/partA
  21:51,/url9/partA/partA/partB
  22:51,/url2/partA/partB
  23:51,/url1a/partD
  00:51,/url3/partA/partB
  01:51,/url9/partA/partA/partB
  02:51,/url4a/
  03:51,/url5b/partA/partE
  04:51,/url7/partA/partA/partB
  05:51,/url1b/part1
  </code></pre>
  <p>Input json (simplified) describing URL categories with URL patterns, like below.</p>
  <pre><code>{
      &quot;category1&quot;: [ &quot;/url1/part1/part2&quot;, &quot;/url1a/&quot;, &quot;/url1b/part1&quot; ],
      &quot;category2&quot;: [ &quot;/url2/partA/partB&quot;, &quot;/url2a/&quot;, &quot;/url2b/partA&quot; ],
      &quot;category3&quot;: [ &quot;/url3/partA/partB&quot;, &quot;/url3a/&quot;, &quot;/url3b/partA&quot; ],
      &quot;category4&quot;: [ &quot;/url4/partA/partB&quot;, &quot;/url4a/&quot;, &quot;/url4b/partA&quot; ],
      &quot;category5&quot;: [ &quot;/url5/partA/partB&quot;, &quot;/url5a/&quot;, &quot;/url5b/partA&quot; ],
  }
  </code></pre>
  <p>I have a python code which achieves but it is very slow as I'm iterating through each dataframe row and each key &amp; values in the json. <strong>Need a solution which in which the code executes much faster</strong>, as my input csv has many rows and input json also has many url categories and many url patterns associated with each url category.</p>
  <pre><code>json1 = '{&quot;category1&quot;: [&quot;/url1/part1/part2&quot;, &quot;/url1a/&quot;, &quot;/url1b/part1&quot;], &quot;category2&quot;: [&quot;/url2/partA/partB&quot;, &quot;/url2a/&quot;, &quot;/url2b/partA&quot;], &quot;category3&quot;: [&quot;/url3/partA/partB&quot;, &quot;/url3a/&quot;, &quot;/url3b/partA&quot;], &quot;category4&quot;: [&quot;/url4/partA/partB&quot;, &quot;/url4a/&quot;, &quot;/url4b/partA&quot;], &quot;category5&quot;: [&quot;/url5/partA/partB&quot;, &quot;/url5a/&quot;, &quot;/url5b/partA&quot;]}'
  print(json1)
  json2 = json.loads(json1)
  print(f&quot;---json2: {json2}; \ntype(json2): {type(json2)}&quot;)
  
  df = pd.read_csv(in_csv_path)
  print(df)
  
  for i in range(len(df)) :
      for key in json2:
          for url_pattern in json2[key]:
              if str(df.loc[i, &quot;URL&quot;]).find(str(url_pattern)) != -1:
                  df.loc[i, &quot;CATEGORY&quot;] = key
  
  df.fillna('Other', inplace=True)
  print(df)
  
  df.to_csv(out_csv, index=False)
  </code></pre>
  <p>Below is the output csv.</p>
  <pre><code>TIME,URL,CATEGORY
  11:51,/url3a/partC ,category3
  12:51,/url6/partA,Other
  13:51,/url7/partA/partA/partB,Other
  14:51,/url5/partA/partB,category5
  15:51,/url3b/partA,category3
  16:51,/url8/partA/partB,Other
  17:51,/url2a/,category2
  18:51,/url5/partA/partB,category5
  19:51,/url1/part1/part2,category1
  20:51,/url4b/partA,category4
  21:51,/url9/partA/partA/partB,Other
  22:51,/url2/partA/partB,category2
  23:51,/url1a/,category1
  00:51,/url3/partA/partB,category3
  01:51,/url9/partA/partA/partB,Other
  02:51,/url4a/,category4
  03:51,/url5b/partA,category5
  04:51,/url7/partA/partA/partB,Other
  05:51,/url1b/part1,category1
  </code></pre>