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())

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`

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)

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

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

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

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

much simpler:

df = pd.DataFrame({'address': [{'state': 'MI', 'town': 'Dearborn'} , {'state': 'CA', 'town': 'Los Angeles'}], 'name':['John', 'Jane']})

df = df.join(df['address'].apply(pd.Series))

then

df.drop(columns='address')

I can use rdd to get the columns, data and create dataframe with this.

rdd = sc.textFile('test.txt')

import json
cols = rdd.map(lambda x: json.loads(x)['columns']).take(1)[0]
data = rdd.map(lambda x: json.loads(x)['data']).take(1)[0]

df = spark.createDataFrame(data, cols)
df.show(truncate=False)

+--------------+-----------+--------------+---------------------+------------+-----------------+-------------------------------+-----------+--------------+---------+------------+----------------------+------------+-------------+---------------------------+------------------+----------------------+-------------+----------------------------+-------------------------------+-------------------------+-----------+------------------+-------------------+--------------------------------+-----------------------+---------------------------+------------------------------------+------------------+-------------------+------------------------------------+---------------------------------+------------------------------+----------------+--------------+
|ApplicationNum|eads59Us01S|HouseDeal_flag|Liability_Asset_Ratio|CBRAvailPcnt|CMSFairIsaacScore|OweTaxes_or_IRAWithdrawalHistry|eads14Fi02S|GuarantorCount|CBRRevMon|CBRInstalMon|CMSApprovedToRequested|SecIncSource|eads59Us01S_4|Liability_Asset_Ratio_40_90|CBRAvailPcnt_20_95|CMSFairIsaacScore_Fund|eads14Fi02S_2|InstalMonthlyPayments_400_3k|RevolvingMonthlyPayments_1k_cap|ApprovedToRequested_0_100|NoSecIncome|coef_eads59Us01S_4|coef_HouseDeal_flag|coef_Liability_Asset_Ratio_40_90|coef_CBRAvailPcnt_20_95|coef_CMSFairIsaacScore_Fund|coef_OweTaxes_or_IRAWithdrawalHistry|coef_eads14Fi02S_2|coef_GuarantorCount|coef_RevolvingMonthlyPayments_1k_cap|coef_InstalMonthlyPayments_400_3k|coef_ApprovedToRequested_0_100|coef_NoSecIncome|coef_Intercept|
+--------------+-----------+--------------+---------------------+------------+-----------------+-------------------------------+-----------+--------------+---------+------------+----------------------+------------+-------------+---------------------------+------------------+----------------------+-------------+----------------------------+-------------------------------+-------------------------+-----------+------------------+-------------------+--------------------------------+-----------------------+---------------------------+------------------------------------+------------------+-------------------+------------------------------------+---------------------------------+------------------------------+----------------+--------------+
|569325.0      |2          |0.0           |1                    |92          |825              |0.0                            |4          |1.0           |74       |854         |0.51                  |2           |2.0          |0.9                        |92.0              |825.0                 |4.0          |854.0                       |1000.0                         |0.51                     |0.0        |0.11716245        |0.299528064        |0.392119645                     |-0.010826643           |-0.004957868               |0.339407077                         |0.061509795       |0.3685047          |1.67603E-4                          |2.25742E-4                       |0.902205454                   |-0.371734864    |2.788087559   |
+--------------+-----------+--------------+---------------------+------------+-----------------+-------------------------------+-----------+--------------+---------+------------+----------------------+------------+-------------+---------------------------+------------------+----------------------+-------------+----------------------------+-------------------------------+-------------------------+-----------+------------------+-------------------+--------------------------------+-----------------------+---------------------------+------------------------------------+------------------+-------------------+------------------------------------+---------------------------------+------------------------------+----------------+--------------+

If it's a flat json then you can try :-

new_df = pd.DataFrame(df['tickers'].tolist())

The Dataframe constructor takes in a list of dictionary objects and turns the key into columns as default orientation, this is the simplest way if your data is standardized and doesn't have a complex nested structure.