title: How to convert two excel columns into json with python?
body: <p>I have an excel file something like this:</p>
  
  <pre><code>name   gender   fac1(radio)   fac2(tv)   fac3(cycle)   fac4(bike)   hasCard   cardNo
  a1     f         y              y           n             y          n         
  a2     m         n              n           y             n          y         AHJS5684
  </code></pre>
  
  <p>How can I get a structure like below from the above xls file</p>
  
  <pre><code>"name": "a1",
  "gender": "f",
  "facilities": ["radio", "tv", "bike"],
  "card": {
     "exists": "n"
     "cardNo": ""
  }
  </code></pre>
  
  <p>I have just read the excel file so far in my code:</p>
  
  <pre><code>import pandas as pd
  #reading excel
  df = pd.read_excel("C:\\Users\\Desktop\\Culture\\Artist_Data\\EZCC\\Madur.xlsx")
  new_df = df.assign(facilities = df.filter(like = 'fac').apply(lambda x: x.str.lower().dropna().tolist(), axis=1))
  d = df.to_dict('records')
  </code></pre>
  
  <p>The above code does not give desired results at all.</p>