list = {'masterId': 2, 'name': 'name', 'description': 'xyz', 'signalTypeRefId': 4, 'unitOfMeasureRefId': 1, 'precision': 1, 'min': -125, 'max': 125, 'isDeprecated': False}
I need the above json loaded into a dataframe and tried the following but doesn't work
df = pd.DataFrame.from_dict(list, orient = 'index')
display(df)
Error:
TypeError: field 0: Can not merge type <class 'pyspark.sql.types.LongType'> and <class 'pyspark.sql.types.StringType'>
data = pd.DataFrame([list])
For more info converting JSON to pandas DataFrames please check out link below :)
https://pandas.pydata.org/docs/reference/api/pandas.read_json.html
You need to wrap the dictionary into a list before creating the dataframe:
data = {'masterId': 2, 'name': 'name', 'description': 'xyz', 'signalTypeRefId': 4, 'unitOfMeasureRefId': 1, 'precision': 1, 'min': -125, 'max': 125, 'isDeprecated': False}
df = spark.createDataFrame([data])
df.show()
+-----------+------------+--------+---+----+----+---------+---------------+------------------+
|description|isDeprecated|masterId|max| min|name|precision|signalTypeRefId|unitOfMeasureRefId|
+-----------+------------+--------+---+----+----+---------+---------------+------------------+
| xyz| false| 2|125|-125|name| 1| 4| 1|
+-----------+------------+--------+---+----+----+---------+---------------+------------------+
Or you can convert it to a pandas dataframe and create a Spark dataframe from that, though you still need to wrap the dictionary into a list:
data = {'masterId': 2, 'name': 'name', 'description': 'xyz', 'signalTypeRefId': 4, 'unitOfMeasureRefId': 1, 'precision': 1, 'min': -125, 'max': 125, 'isDeprecated': False}
df = spark.createDataFrame(pd.DataFrame([data]))
df.show()
+--------+----+-----------+---------------+------------------+---------+----+---+------------+
|masterId|name|description|signalTypeRefId|unitOfMeasureRefId|precision| min|max|isDeprecated|
+--------+----+-----------+---------------+------------------+---------+----+---+------------+
| 2|name| xyz| 4| 1| 1|-125|125| false|
+--------+----+-----------+---------------+------------------+---------+----+---+------------+
dct = {'masterId': 2, 'name': 'name', 'description': 'xyz', 'signalTypeRefId': 4, 'unitOfMeasureRefId': 1, 'precision': 1, 'min': -125, 'max': 125, 'isDeprecated': False}
df = pd.DataFrame.from_dict(dct, orient="index")
display(df)
"""
0
masterId 2
name name
description xyz
signalTypeRefId 4
unitOfMeasureRefId 1
precision 1
min -125
max 125
isDeprecated False
"""
To have it as a row, use .transpose()
df.transpose()
"""
Out[15]:
masterId name description ... min max isDeprecated
0 2 name xyz ... -125 125 False
"""
data = pd.json_normalize(list)
https://pandas.pydata.org/docs/reference/api/pandas.json_normalize.html
list! Try to name your variable differently.