title: Convert csv to nested json format using pandas
body: <p>Below  is the csv file which needs to be converted to nested json form. I was able to get the details for individual servers in json form but was unable to convert it into nested form. There are 20 more parameters in the csv file but I have added only 6 parameters.</p>
  <pre><code>    ,How many servers are required?*  ,3,,,
      
      ,Server Type,AWS,,,
      
      ,,,,,
      
      ,,,Server 1,Server 2,Server 3
      
      ,,,,,
      
      1,OS Type*,,Windows,Unix,Windows
      
      ,,,,,
      
      2,OS Version*,,Windows Server 2019,RHEL 
      7.9,Windows Server 2019
      
      ,,,,,
      
      ,,,,,
      
      3,Environment Tags ,,,,
      
      ,Name,,A,B,C
      
      ,Contact*,,abc@gmail.com,abc@gmail.com,a
      bc@gmail.com
      
      ,Owner,,ABC,EFG,HIJ
      
      ,,,,,
      
      4,EC2 Instance Type*,,t2.large,t2.large,
      t3.small
      
      ,,,,,
      
      5,Server environment*,,Non-production,No
      n-production,Non-production
      
      ,,,,,
      
      6,AWS Region*,,USEast_NorthVirginia,APAC
      _Singapore,USWest_Oregon
  </code></pre>
  <p>expected output:</p>
  <pre><code>{
  &quot;How many servers required&quot; : 3,
  &quot;server type&quot;: &quot;AWS&quot;
  
  &quot;Server1&quot;:{
  
     &quot;Build_data&quot;: { 
    
        &quot;OSType&quot;: &quot;Windows&quot;,
        &quot;OSVersion&quot;: &quot;Windows Server 2019&quot;,
        &quot;EnvironmentTags&quot;: 
         {
           &quot;Name&quot;: &quot;sasaasasa&quot;,
           &quot;Contact&quot;: &quot;abc@gmail.com&quot;,
           &quot;Owner&quot;: &quot;NA&quot;,
          }
        &quot;EC2 instance Type&quot;: &quot;t2.large&quot;,
                 }
  
      &quot;Post_build_data&quot;: {
  
         &quot;Serverenvironment&quot;: &quot;Non-production&quot;,
         &quot;AWSRegion: &quot;USEast_NorthVirginia&quot;
                  }
  },
  &quot;Server2&quot;:{
  
     &quot;Build_data&quot;: { 
    
        &quot;OSType&quot;: &quot;Windows&quot;,
        &quot;OSVersion&quot;: &quot;Windows Server 2019&quot;,
        &quot;EnvironmentTags&quot;: 
         {
           &quot;Name&quot;: &quot;sasaasasa&quot;,
           &quot;Contact&quot;: &quot;abc@gmail.com&quot;,
           &quot;Owner&quot;: &quot;NA&quot;,
          }
        &quot;EC2 instance Type&quot;: &quot;t2.large&quot;,
                 }
  
      &quot;Post_build_data&quot;: {
  
         &quot;Serverenvironment&quot;: &quot;Non-production&quot;,
         &quot;AWSRegion: &quot;USEast_NorthVirginia&quot;
                  }
  }
  So on 
  
  }
  </code></pre>
  <p>Below code works fine to get the data for all the servers but I need this in the nested format. As this is something new to me, your ideas will be of great help! If I have missed anything or if some more information is required, please let me know. <a href="https://i.stack.imgur.com/99Bb5.png" rel="nofollow noreferrer">csv image for reference</a></p>
  <pre><code>import pandas as pd
  df = pd.read_csv('C:/Users/Downloads/Three_servers_2022_06_16_18_50_04.csv',engine ='python',encoding= 'unicode_escape').dropna(how='all', axis=1).dropna(how='all')
  df.replace(&quot;\u00a0&quot;,&quot;&quot;, inplace=True)
  df = df.drop(df.columns[[0]], axis=1).fillna(&quot;NA&quot;)
  df = df.rename(columns=df.iloc[0]).drop(df.index[0])
  df[&quot;NA&quot;] = df[&quot;NA&quot;].str.replace(' ', '')
  df[&quot;NA&quot;] = df[&quot;NA&quot;].str.replace('*', '')
  df[&quot;NA&quot;] = df[&quot;NA&quot;].str.replace('?', '')
  df = df.rename(index= df[&quot;NA&quot;]).drop(df.columns[[0]], axis=1)
  result = df.to_json('C:/Users/Downloads/check.json', orient=&quot;columns&quot;)
  </code></pre>