title: Converting Excel to JSON using Pandas in Python 3.9
body: <p>this is my first ever post here so go easy! :) I am attempting to convert data from Excel to JSON using the Python Pandas library.</p>
  <p>I have data in Excel that looks like the table below, the columns detailed as &quot;Unnamed: x&quot; are blank, I used these headers as that's how they are output when converting to JSON. There are around 20 tests formatted like the sample below:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th style="text-align: left;">Unnamed: 1</th>
  <th style="text-align: left;">Unnamed: 2</th>
  <th style="text-align: left;">Unnamed: 3</th>
  <th style="text-align: left;">Unnamed: 4</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td style="text-align: left;"></td>
  <td style="text-align: left;"></td>
  <td style="text-align: left;"></td>
  <td style="text-align: left;"></td>
  </tr>
  <tr>
  <td style="text-align: left;"></td>
  <td style="text-align: left;"></td>
  <td style="text-align: left;"></td>
  <td style="text-align: left;"></td>
  </tr>
  <tr>
  <td style="text-align: left;">Test 1</td>
  <td style="text-align: left;">Menu</td>
  <td style="text-align: left;">Setting</td>
  <td style="text-align: left;">Value</td>
  </tr>
  <tr>
  <td style="text-align: left;"></td>
  <td style="text-align: left;">Menu1</td>
  <td style="text-align: left;">Setting1</td>
  <td style="text-align: left;">Value1</td>
  </tr>
  <tr>
  <td style="text-align: left;"></td>
  <td style="text-align: left;"></td>
  <td style="text-align: left;"></td>
  <td style="text-align: left;"></td>
  </tr>
  <tr>
  <td style="text-align: left;"></td>
  <td style="text-align: left;"></td>
  <td style="text-align: left;"></td>
  <td style="text-align: left;"></td>
  </tr>
  <tr>
  <td style="text-align: left;">Test 2</td>
  <td style="text-align: left;">A</td>
  <td style="text-align: left;">B</td>
  <td style="text-align: left;">C</td>
  </tr>
  <tr>
  <td style="text-align: left;"></td>
  <td style="text-align: left;">1</td>
  <td style="text-align: left;">2</td>
  <td style="text-align: left;">3</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>I would like to put these in to JSON to look something like this:</p>
  <pre><code>{
      &quot;Test 1&quot;: [ 
      &quot;Menu&quot;:&quot;Menu1&quot;,
      &quot;Setting&quot;:&quot;Setting1&quot;,
      &quot;Value&quot;:&quot;Value1&quot;,
      ]
  }
  </code></pre>
  <p>And so on...</p>
  <p>I can convert the current code to JSON (but not the format detailed above, and I have been experimenting with creating different Pandas dataframes in Python. At the moment the JSON data I get looks something like this:</p>
  <pre><code>&quot;3&quot;:[
          {
              &quot;Unnamed: 0&quot;:&quot;Test1&quot;,
              &quot;Unnamed: 1&quot;:&quot;Menu&quot;,
              &quot;Unnamed: 2&quot;:&quot;Setting&quot;,
              &quot;Unnamed: 2&quot;:&quot;Value&quot;
          }
  &quot;4&quot;:[
          {
              &quot;Unnamed: 1&quot;:&quot;Menu1&quot;,
              &quot;Unnamed: 2&quot;:&quot;Setting1&quot;,
              &quot;Unnamed: 2&quot;:&quot;Value1&quot;
          }
  </code></pre>
  <p>So I am doing some manual work (copying and pasting) to set it up in the desired format.</p>
  <p>Here is my current code:</p>
  <pre><code>import pandas
  
  # Pointing to file location and specifying the sheet name to convert
  excel_data_fragment = pandas.read_excel('C:\\Users\\user_name\\tests\\data.xls', sheet_name='Tests')
  
  # Converting to data frame
  df = pandas.DataFrame(excel_data_fragment)
  
  # This will get the values in Column A and removes empty values
  test_titles = df['Unnamed: 0'].dropna(how=&quot;all&quot;)
  
  # This is the first set of test values
  columnB = df['Unnamed: 1'].dropna(how=&quot;all&quot;)
  
  # Saving original data in df and removing rows which contain all NaN values to mod_df
  mod_df = df.dropna(how=&quot;all&quot;)
  
  # Converting data frame with NaN values removed to json
  df_json = mod_df.apply(lambda x: [x.dropna()], axis=1).to_json()
  
  print(mod_df)
  </code></pre>