There are many possible solutions. Generally though, you'll probably want to:

1. Not loop over fields; instead let Pandas split the fields for you
2. Use an actual missing value
   • But later if you want to represent it differently, you can do that, e.g. using the na_rep parameter to df.style.format

For the first step, you can look at Split / Explode a column of dictionaries into separate columns with pandas. I'll use Lech Birek's solution (json_normalize) then drop the "id" columns and rename the "value" columns.

headers_mapping = {'1': 'field1', '2': 'field2', '3': 'field3', '4': 'field4'}
(
    pd.json_normalize(df['json_field'])
    .filter(like='value')
    .rename(columns=lambda label: headers_mapping[label.rstrip('.value')])
)

   field1  field2  field3  field4
0  value1  value2     NaN     NaN
1  value1     NaN  value3     NaN
2     NaN     NaN  value3  value4

If you also need to sort the columns, tack this on at the end:

.reindex(columns=headers_mapping.values())

I hope I've understood your question well. Try:

from ast import literal_eval

df["experimental_properties"] = df["experimental_properties"].apply(
    lambda x: {d["name"]: d["property"] for d in literal_eval(x)}
)
df = pd.concat([df, df.pop("experimental_properties").apply(pd.Series)], axis=1)

print(df)

Prints:

            Boiling Point                                Density
0                115.3 °C                                    NaN
1  91 °C @ Press: 20 Torr                                    NaN
2  58 °C @ Press: 12 Torr  0.8753 g/cm<sup>3</sup> @ Temp: 20 °C

The below code worked for me `

Rating = result['Ratings'].values.tolist()
 rate = pd.DataFrame(Rating,columns =['Service', 'Cleanliness','Overall'])


   Service   Cleanliness     Overall
         0        5               5
         1        4               4`

You can achieve this using read_json() to covert this json to a pandas dataframe

    df = pd.read_json(json_name)

The documentation for it can be found here: https://pandas.pydata.org/docs/reference/api/pandas.read_json.html

IIUC this is exactly what json_normalize is for:

import pandas as pd

data = {"_id": {
    "$oid": "6090ba8fccd167ce183b5ebc"
  },
  "day": "2021-05-04",
  "sensorType": "eurecam",
  "sensorid": "74",
  "first": {
    "$numberLong": "1620097683000"
  },
  "last": {
    "$numberLong": "1620103653000"
  },
  "nsamples": 200,
  "samples": [
    {
      "occupancy_state": "1,0,0,0",
      "rtc_utc_time": "2021-05-04 03:07:58",
      "sdcard_site": "BE",
      "sdcard_chain": "DAG",
      "sdcard_line": "1,BE",
      "utc": {
        "$numberLong": "1620097683000"
      }
    },
    {
      "occupancy_state": "1,0,0,0",
      "rtc_utc_time": "2021-05-04 03:08:27",
      "sdcard_site": "BE",
      "sdcard_chain": "DAG",
      "sdcard_line": "1,BE",
      "utc": {
        "$numberLong": "1620097712000"
      }
    },
    {
      "occupancy_state": "1,0,0,0",
      "rtc_utc_time": "2021-05-04 03:08:57",
      "sdcard_site": "BE",
      "sdcard_chain": "DAG",
      "sdcard_line": "1,BE",
      "utc": {
        "$numberLong": "1620097742000"
      }
    }
  ]
}

df = pd.json_normalize(data, record_path="samples")
print(df)

Output:

  occupancy_state         rtc_utc_time sdcard_site sdcard_chain sdcard_line utc.$numberLong
0         1,0,0,0  2021-05-04 03:07:58          BE          DAG        1,BE   1620097683000
1         1,0,0,0  2021-05-04 03:08:27          BE          DAG        1,BE   1620097712000
2         1,0,0,0  2021-05-04 03:08:57          BE          DAG        1,BE   1620097742000

You can use split with removing [] by strip:

df1 = df.pop('values').str.strip('[]').str.split(',',expand=True).astype(float)
df[['values_a', 'values_b', 'values_c']] = df1

Solution if There is no NaNs:

L = [x.split(',') for x in df.pop('values').str.strip('[]').values.tolist()]
df[['values_a', 'values_b', 'values_c']] = pd.DataFrame(L).astype(float)

solutions with converting columns first to list and then is used DataFrame constructor:

import ast
s = df.pop('values').apply(ast.literal_eval)
df[['values_a', 'values_b', 'values_c']] = pd.DataFrame(s.values.tolist()).astype(float)

Similar:

df = pd.read_csv(file converters={'values':ast.literal_eval})
print (df)
   value                                  values
0   56.0      [-0.5554548, 10.0748005, 4.232949]
1   72.0  [-0.1953888, 0.15093994, -0.058532715]

df1 = pd.DataFrame(df.pop('values').tolist()).astype(float)
df[['values_a', 'values_b', 'values_c']] = df1

Final:

print (df)
   value  values_a   values_b  values_c
0   56.0 -0.555455  10.074801  4.232949
1   72.0 -0.195389   0.150940 -0.058533

EDIT:

If is possible in some column is more as 3 value then is not possible assign to 3 new columns. Solution is use join:

df = df.join(df1.add_prefix('val'))
print (df)
   value      val0       val1      val2
0   56.0 -0.555455  10.074801  4.232949
1   72.0 -0.195389   0.150940 -0.058533

Since the json file's schema is unknown, there is no way to guess what's going wrong. Update the question with a sample raw json file from the url where you are fetching it from.

Usually you don't have to do much other than read_json.

for example in the below snippet, json is already loaded and dataframe is created with corresponding columns,

import pandas as pd

df = pd.read_json("https://api.github.com/users/microsoft/repos")
print(df.id)

Output:
0       6104546
1     104510411
2      58656723
3      13121042
...
28     13141936
29     20027360
Name: id, dtype: int64

Edit: Updated with OP's json schema.

import pandas as pd
import json

df = pd.read_json("https://sejabtg.com/api/offices.json")   
def parse_registrations(row):
    registration = pd.json_normalize(row.result)
    return registration
        
s = df.apply(parse_registrations, axis=1)
print(type(s))
print(f"df3.0: {s[0].cnpj}")
print(f"df3.1: {s[10].cnpj}")

df = pd.DataFrame()
# iterate over the series and concatenate the dataframes
for i in s:
    df = pd.concat([df, i], axis=0)
print(type(df))
print(df.cnpj)

Output:

<class 'pandas.core.series.Series'>
df3.0: 0    15673644000118
Name: cnpj, dtype: object
df3.1: 0    15410039000154
Name: cnpj, dtype: object
<class 'pandas.core.frame.DataFrame'>
0    15673644000118
0    15673644000118
0    15673644000118
0    24151517000140
0    18146649000180
          ...      
0    15410039000154
0    15410039000154
0    15410039000154
0    17263248000148
0    17263248000148
Name: cnpj, Length: 362, dtype: object

should add ignore_index=True argument in explode function to make sure the following join is not messed up.

df = pd.DataFrame(data).explode('countries', ignore_index=True)
df = df.join(pd.json_normalize(df.pop('countries')))
print(df)

You could have to_json write to a StringIO object and then use json loads/dumps to format to your liking:

import pandas as pd
import StringIO, json
df = pd.read_csv('data.csv')
nielson = StringIO.StringIO()
df.groupby('id').apply(lambda x: x.set_index('date')['val'].to_dict()).to_json(nielson, orient='index')
print(json.dumps(json.loads(nielson.getvalue()),indent=2))

This produces:

{
  "123": {
    "2015-12-14": 0, 
    "2015-12-15": 1, 
    "2015-12-16": 1, 
    "2015-12-17": 0, 
    "2015-12-12": 1, 
    "2015-12-13": 1, 
    "2015-12-18": 1
  }, 
  "456": {
    "2015-12-14": 0, 
    "2015-12-15": 1, 
    "2015-12-12": 1, 
    "2015-12-13": 1
  }
}

See help(json.dumps) for other formatting options. See help(json.dump) for details on how to write to a file (basic example shown below):

with open('nielsen','w') as f:
    json.dump(json.loads(nielson.getvalue()), f, indent=2)