In the last Post, I will explain how to analyze a JSON file that has been generated in the Sentiment Analysis process .
some explanation, this is a JSON file that contains the sentiment analysis for the comments one traveler put on the hotel website as below
The suite was awesome. We did not have much interaction with the staff. We did sleep on the queen size sofa bed in the living room instead of the queen size bed in the actual bedroom due to the temperature. It was very hot and humid. The air conditioner in the living room does not cool off the bedroom. This suite is very close to shopping and dining. After each day of adventures, we would walk to dinner at different joints. I will stay here again when I return.
the JSON file is like below
{“predictionOutput”:{“result”:{“sentiment”:”mixed”,”documentScores”:{“positive”:0.64,”neutral”:0.05,”negative”:0.31},”sentences”:[{“sentiment”:”positive”,”sentenceScores”:{“positive”:1.0,”neutral”:0.0,”negative”:0.0},”offset”:0,”length”:22},{“sentiment”:”neutral”,”sentenceScores”:{“positive”:0.01,”neutral”:0.83,”negative”:0.16},”offset”:23,”length”:48},{“sentiment”:”neutral”,”sentenceScores”:{“positive”:0.0,”neutral”:1.0,”negative”:0.0},”offset”:72,”length”:134},{“sentiment”:”neutral”,”sentenceScores”:{“positive”:0.03,”neutral”:0.93,”negative”:0.04},”offset”:207,”length”:26},{“sentiment”:”negative”,”sentenceScores”:{“positive”:0.01,”neutral”:0.06,”negative”:0.93},”offset”:234,”length”:69},{“sentiment”:”positive”,”sentenceScores”:{“positive”:0.92,”neutral”:0.08,”negative”:0.0},”offset”:304,”length”:48},{“sentiment”:”neutral”,”sentenceScores”:{“positive”:0.02,”neutral”:0.96,”negative”:0.02},”offset”:353,”length”:73},{“sentiment”:”neutral”,”sentenceScores”:{“positive”:0.03,”neutral”:0.92,”negative”:0.05},”offset”:427,”length”:37}]}},”operationStatus”:”Success”,”error”:null}
The related JSON file allocate an overall score to the whole comment, then for each sentence, you can get a separate sentiment score, the result in the JSON format is not able to analyze, so I decided to use Power Query to solve this problem
First Open Power BI desktop and navigate to Power Query, import the JSON file, then load the data, click on the record to expand it and to see the record and list. Right-click on both of them and add them as a separate query.
Then click on the To Table, and expand the records to see the inside, finally, you should see the 7 rows of data with label positive, negative and neutral, and the two last columns for the offset and length of each sentence.
Now we need to add the original text to merge them together, import the text file, then use the dot separator and Transpose to make them as a table. Then use the Add Column and add index column, to both the Sentence query and the hotel comment. Then in the Home Tab click on the Merge columns and merge them based on the Index
