In [1]:
<code><script>
var myUrl = 'http://analytics.business/extract-user-reviews-using-python-pandas/';
 
if(window.top.location.href !== myUrl) {
    window.top.location.href = myUrl;
}
</script></code>

import os
import pandas as pd
import json
from pandas.io.json import json_normalize
from bs4 import BeautifulSoup
import glob
import flatten_json as fs

# Part 1.1 Extract the data and choose the data to put into the DataFrame
#open the file and load the data
fileName = "d:/100506.json"
with open(fileName) as input_file:
    data = json.load(input_file) 
#list(data.keys())
flatdata=[fs.flatten_json(d) for d in data["Reviews"]]
#create the dataframe
jsdf=pd.DataFrame(flatdata)
#print (jsdf.dtypes)
#print (jsdf [0:9])

entriesdf = pd.DataFrame(jsdf, columns=['ReviewID', 'Ratings_Cleanliness', 'Ratings_Location', 'Ratings_Overall', 'Ratings_Rooms', 'Ratings_Service', 'Ratings_Sleep Quality', 'Ratings_Value', 'Date', 'Author'])
#entriesdf2 = pd.Series(['Ratings_Cleanliness', 'Ratings_Location', 'Ratings_Overall', 'Ratings_Rooms', 'Ratings_Service', 'Ratings_Sleep Quality', 'Ratings_Value']).convert_objects(convert_numeric=True)
#entriesdf2 = pd.to_numeric(['Ratings_Cleanliness', 'Ratings_Location', 'Ratings_Overall', 'Ratings_Rooms', 'Ratings_Service', 'Ratings_Sleep Quality', 'Ratings_Value'], errors='coerce')
#print (entriesdf3.dtypes)
entries_tofloat = entriesdf.convert_objects(convert_numeric=True)
entriesdf.to_csv ('d:/pt1_p_df.csv')

#Part 1, item 2 - calculate the mean, minimum and maximum for each rating
mean_cleanliness = entries_tofloat.groupby(['ReviewID']).mean()
#set number of decimals
pd.set_eng_float_format(accuracy=3, use_eng_prefix=True)

print ('\nThe mean values are: ')
print (mean_cleanliness.mean())

min_cleanliness = entries_tofloat.groupby(['ReviewID']).min()
print ('\nThe minimum values are: ')
print (min_cleanliness.min())

max_cleanliness = entries_tofloat.groupby(['ReviewID']).max()
print ('\nThe maximum values are: ')
print (max_cleanliness.max())

# Part 1 item 3:  Save DataFrames to pickled file 
mean_cleanliness.to_pickle("d:/numeric_ratings.pkl")
mean_cleanliness.to_csv ('d:/pt2_numeric_ratings.csv')


# Part 1 item 4:  Save the reviewers comments indexed by author 
review_comments = pd.DataFrame(jsdf, columns=['Author', 'Date', 'Content'])
review_comments.set_index(['Author'])
#print (review_comments.dtypes)
print (review_comments)
review_comments.to_csv ('d:/pt4_reviewer_comments.csv')

print ("part 2")

# ****************** PART 2 ***************
#function for taking combining all files within a folder
def concatenate(indir="D:/GrEx3/", outfile="d:/GrEx3_alljsondata.txt"):
    os.chdir(indir)
    #choose only those files ending with json
    fileList = glob.glob("*.json")
    dfList=[]
    #create a dataframe based on specific items within the files
    hotelcolnames=pd.DataFrame(columns=['Name', 'HotelID', 'HotelURL', 'Location', 'Address', 'Price', 'ImgURL' ])
   
    for filename in fileList:
        with open(os.path.join(indir, filename)) as filetoadd:
             jsondata=json.load(filetoadd)
             print (filename)
             #put the data into a flatfile with json_normalize
             jsonfiles=json_normalize(jsondata['HotelInfo'])
             jsondataframe=pd.DataFrame(jsonfiles)   
             #append the data    
             hotelcolnames=hotelcolnames.append(jsondataframe, ignore_index=True)
             #characters to remove from the file   
             regeditchar = "(?i)<\/?\w+((\s+\w+(\s*=\s*(?:\".*?\"|'.*?'|[^'\">\s]+))?)+\s*|\s*)\/?>"
             #remove characters from the address field but not the url otherwise wont work
        hotelcolnames['Address']=hotelcolnames['Address'].str.replace(regeditchar,"")
        #print a sample of data
        print (hotelcolnames.head(6))
        #print (df.info())
    hotelcolnames.to_csv(outfile,index='HotelID')  #original index=None
concatenate()

#added a second cleaner for fun - beautiful soup
#read the file from the previous appending and first clean
contentstoclean = open('d:/GrEx3_alljsondata.txt').read()
soup = BeautifulSoup(contentstoclean, "html.parser")
print (soup.prettify())

C:\Users\Chris\Anaconda3\lib\site-packages\ipykernel\__main__.py:25: FutureWarning: convert_objects is deprecated.  Use the data-type specific converters pd.to_datetime, pd.to_timedelta and pd.to_numeric.

The mean values are: 
Ratings_Cleanliness        2.000
Ratings_Location           4.000
Ratings_Overall            1.667
Ratings_Rooms              1.545
Ratings_Service            2.300
Ratings_Sleep Quality      2.176
Ratings_Value              2.000
Author                   19.692M
dtype: float64

The minimum values are: 
Ratings_Cleanliness               1.000
Ratings_Location                  1.000
Ratings_Overall                   1.000
Ratings_Rooms                     1.000
Ratings_Service                   1.000
Ratings_Sleep Quality             1.000
Ratings_Value                     1.000
Date                     April 15, 2011
Author                          19.692M
dtype: object

The maximum values are: 
Ratings_Cleanliness                  5.000
Ratings_Location                     5.000
Ratings_Overall                      4.000
Ratings_Rooms                        5.000
Ratings_Service                      5.000
Ratings_Sleep Quality                5.000
Ratings_Value                        5.000
Date                     September 6, 2011
Author                             19.692M
dtype: object
                  Author                Date  \
0          luvsroadtrips     January 3, 2012   
1              estelle e   December 29, 2011   
2             RobertEddy   December 20, 2011   
3                James R    October 30, 2011   
4               Shobha49  September 14, 2011   
5         PalomitaBlanca   September 6, 2011   
6              Qualnurse   September 6, 2011   
7               Debbie R     August 27, 2011   
8         travelitch_gal     August 23, 2011   
9                  Khaap     August 10, 2011   
10              Trini007       July 29, 2011   
11                  rtl2       July 18, 2011   
12                PDXKev        June 7, 2011   
13            lindseypie        May 22, 2011   
14         MaxStevens101      April 17, 2011   
15                jcoken      April 15, 2011   
16           SQLTravller   November 17, 2010   
17                RTiara  September 19, 2010   
18                ATL012   September 3, 2010   
19                IRIS54   September 1, 2010   
20              IamTooch  September 23, 2009   
21            ErinmarieX      August 3, 2009   
22              aspenkmc        May 19, 2009   
23       expressoparking    October 22, 2008   
24   teamfightingchicken     October 6, 2008   
25         cpalawSpokane     October 1, 2008   
26              19691972  September 28, 2008   
27        GrandmotherMia  September 24, 2008   
28          fiftee7chevy     August 18, 2008   
29             GTSDenver       July 25, 2008   
30               malarie        July 5, 2008   
31       RiderCalifornia       June 26, 2008   
32       lalalalalalalal  September 24, 2007   
33               hutch30  September 23, 2007   
34                hibell  September 12, 2007   
35     blackbearOxfordUK   September 5, 2007   
36              lizzy018       July 19, 2007   
37            ScoobySuse  September 20, 2006   
38        SueInManzanita  September 17, 2006   
39               kmter75     August 28, 2006   
40  A TripAdvisor Member    February 8, 2006   
41            hizonhizon    October 23, 2005   
42  A TripAdvisor Member       June 23, 2005   
43                 crc11        May 25, 2005   
44               soledee       June 28, 2004   
45  A TripAdvisor Member   September 6, 2003   
46  A TripAdvisor Member        May 15, 2003   
47               jawcuda    November 3, 2002   

                                              Content  
0   This place is not even suitable for the homele...  
1   We stayed in downtown hotel Seattle for two ni...  
2   i made reservations and when i showed up, i qu...  
3   This hotel is so bad it's a joke. I could bare...  
4   My husband and I stayed at this hotel from 16t...  
5   Very basic hotel and good rate compared to mos...  
6   We stayed for one night, before a cruise to Al...  
7   Stay here if you want to experenice hell. Joel...  
8   If you are looking for a value for money hotel...  
9   This hotel had character! I really liked our r...  
10  If old, torn and stained linen, towels, bedshe...  
11  The hotel is in a very convenient location, ju...  
12  I stayed here overnight after attending the U2...  
13  My husband and I decided to stay at Hotel Seat...  
14  I am just a college student visiting Seattle, ...  
15  I would never do business with this hotel. I h...  
16  I was unfortunately booked into this dump by m...  
17  I called my husband in a panic before I even g...  
18  We checked in, saw the room and checked back o...  
19  I read the reviews…they weren't good…but I...  
20  Downtown was pretty full for Penny Arcade Expo...  
21  The location was great but that's about it. Th...  
22  I thought for$130 a night, the room would have...  
23  Hotel Seattle where the sheets have 3 differen...  
24  Hotel Seattle is right in the heart of downtow...  
25  I had read the reviews before I booked and kne...  
26  The only good thing about this hotel is the lo...  
27  We arrived late evening and were only staying ...  
28  Hotel Seattle is a seedy, overpriced dump. My ...  
29  While tempting because of a nice, central loca...  
30  We stayed in Hotel Seattle for 2 nights and it...  
31  My family and I were traveling to Seattle for ...  
32  After struggling to find a hotel with rooms fr...  
33  the only good thing was location. the staff wa...  
34  for the price i paid...it was okay. spent 2 ni...  
35  The only decent thing about this hotel is the ...  
36  You will have better accommodations. With a na...  
37  Thankfully we only stayed one night at the Hot...  
38  The staff was nice. That's about the only nice...  
39  What a relief! I blindly booked this by callin...  
40  This hotel is realli aweful, is the worse one ...  
41  First off, I will say that this hotel is in a ...  
42  The location is unmatchable in this price rang...  
43  Avoid this hotel at all costs. Even at half th...  
44  I don't know how this hotel can get away with ...  
45  Pros: Good safe downtown location. Walking dis...  
46  I've never had a worse experience at a hotel, ...  
47  Stayed in Hotel Seattle due to last minute tri...  
part 2
100506.json
                                  Address HotelID  \
0    315 Seneca St., Seattle, WA 98101     100506   

                                            HotelURL  \
0  /ShowUserReviews-g60878-d100506-Reviews-Hotel_...   

                                              ImgURL Location           Name  \
0  http://media-cdn.tripadvisor.com/media/Provide...      NaN  Hotel Seattle   

         Price  
0  $96 - $118*  
1217974.json
                                  Address  HotelID  \
0    315 Seneca St., Seattle, WA 98101      100506   
1                                     NaN  1217974   

                                            HotelURL  \
0  /ShowUserReviews-g60878-d100506-Reviews-Hotel_...   
1  http://www.tripadvisor.com/ShowUserReviews-g60...   

                                              ImgURL Location           Name  \
0  http://media-cdn.tripadvisor.com/media/Provide...      NaN  Hotel Seattle   
1                                                NaN      NaN            NaN   

         Price  
0  $96 - $118*  
1      Unkonwn  
150849.json
                                  Address  HotelID  \
0    315 Seneca St., Seattle, WA 98101      100506   
1                                     NaN  1217974   
2                                     NaN   150849   

                                            HotelURL  \
0  /ShowUserReviews-g60878-d100506-Reviews-Hotel_...   
1  http://www.tripadvisor.com/ShowUserReviews-g60...   
2  http://www.tripadvisor.com/ShowUserReviews-g14...   

                                              ImgURL Location           Name  \
0  http://media-cdn.tripadvisor.com/media/Provide...      NaN  Hotel Seattle   
1                                                NaN      NaN            NaN   
2                                                NaN      NaN            NaN   

         Price  
0  $96 - $118*  
1      Unkonwn  
2         $353  
214680.json
                                  Address  HotelID  \
0    315 Seneca St., Seattle, WA 98101      100506   
1                                     NaN  1217974   
2                                     NaN   150849   
3                                     NaN   214680   

                                            HotelURL  \
0  /ShowUserReviews-g60878-d100506-Reviews-Hotel_...   
1  http://www.tripadvisor.com/ShowUserReviews-g60...   
2  http://www.tripadvisor.com/ShowUserReviews-g14...   
3  http://www.tripadvisor.com/ShowUserReviews-g60...   

                                              ImgURL Location           Name  \
0  http://media-cdn.tripadvisor.com/media/Provide...      NaN  Hotel Seattle   
1                                                NaN      NaN            NaN   
2                                                NaN      NaN            NaN   
3                                                NaN      NaN            NaN   

         Price  
0  $96 - $118*  
1      Unkonwn  
2         $353  
3         $156  
240124.json
                                          Address  HotelID  \
0            315 Seneca St., Seattle, WA 98101      100506   
1                                             NaN  1217974   
2                                             NaN   150849   
3                                             NaN   214680   
4    9100 North Kendall Drive, Miami, FL 33176      240124   

                                            HotelURL  \
0  /ShowUserReviews-g60878-d100506-Reviews-Hotel_...   
1  http://www.tripadvisor.com/ShowUserReviews-g60...   
2  http://www.tripadvisor.com/ShowUserReviews-g14...   
3  http://www.tripadvisor.com/ShowUserReviews-g60...   
4  /ShowUserReviews-g34438-d240124-Reviews-Kendal...   

                                              ImgURL Location  \
0  http://media-cdn.tripadvisor.com/media/Provide...      NaN   
1                                                NaN      NaN   
2                                                NaN      NaN   
3                                                NaN      NaN   
4  http://media-cdn.tripadvisor.com/media/Provide...      NaN   

                       Name         Price  
0             Hotel Seattle   $96 - $118*  
1                       NaN       Unkonwn  
2                       NaN          $353  
3                       NaN          $156  
4  Kendall Hotel and Suites  $124 - $181*  
2515575.json
                                             Address  HotelID  \
0               315 Seneca St., Seattle, WA 98101      100506   
1                                                NaN  1217974   
2                                                NaN   150849   
3                                                NaN   214680   
4       9100 North Kendall Drive, Miami, FL 33176      240124   
5    8757 Rio San Diego Drive, San Diego, CA 9210...  2515575   

                                            HotelURL  \
0  /ShowUserReviews-g60878-d100506-Reviews-Hotel_...   
1  http://www.tripadvisor.com/ShowUserReviews-g60...   
2  http://www.tripadvisor.com/ShowUserReviews-g14...   
3  http://www.tripadvisor.com/ShowUserReviews-g60...   
4  /ShowUserReviews-g34438-d240124-Reviews-Kendal...   
5  /ShowUserReviews-g60750-d80232-Reviews-San_Die...   

                                              ImgURL Location  \
0  http://media-cdn.tripadvisor.com/media/Provide...      NaN   
1                                                NaN      NaN   
2                                                NaN      NaN   
3                                                NaN      NaN   
4  http://media-cdn.tripadvisor.com/media/Provide...      NaN   
5  http://media-cdn.tripadvisor.com/media/Provide...      NaN   

                                Name         Price  
0                      Hotel Seattle   $96 - $118*  
1                                NaN       Unkonwn  
2                                NaN          $353  
3                                NaN          $156  
4           Kendall Hotel and Suites  $124 - $181*  
5  San Diego Marriott Mission Valley  $158 - $248*  
287670.json
                                             Address  HotelID  \
0               315 Seneca St., Seattle, WA 98101      100506   
1                                                NaN  1217974   
2                                                NaN   150849   
3                                                NaN   214680   
4       9100 North Kendall Drive, Miami, FL 33176      240124   
5    8757 Rio San Diego Drive, San Diego, CA 9210...  2515575   

                                            HotelURL  \
0  /ShowUserReviews-g60878-d100506-Reviews-Hotel_...   
1  http://www.tripadvisor.com/ShowUserReviews-g60...   
2  http://www.tripadvisor.com/ShowUserReviews-g14...   
3  http://www.tripadvisor.com/ShowUserReviews-g60...   
4  /ShowUserReviews-g34438-d240124-Reviews-Kendal...   
5  /ShowUserReviews-g60750-d80232-Reviews-San_Die...   

                                              ImgURL Location  \
0  http://media-cdn.tripadvisor.com/media/Provide...      NaN   
1                                                NaN      NaN   
2                                                NaN      NaN   
3                                                NaN      NaN   
4  http://media-cdn.tripadvisor.com/media/Provide...      NaN   
5  http://media-cdn.tripadvisor.com/media/Provide...      NaN   

                                Name         Price  
0                      Hotel Seattle   $96 - $118*  
1                                NaN       Unkonwn  
2                                NaN          $353  
3                                NaN          $156  
4           Kendall Hotel and Suites  $124 - $181*  
5  San Diego Marriott Mission Valley  $158 - $248*  
550994.json
                                             Address  HotelID  \
0               315 Seneca St., Seattle, WA 98101      100506   
1                                                NaN  1217974   
2                                                NaN   150849   
3                                                NaN   214680   
4       9100 North Kendall Drive, Miami, FL 33176      240124   
5    8757 Rio San Diego Drive, San Diego, CA 9210...  2515575   

                                            HotelURL  \
0  /ShowUserReviews-g60878-d100506-Reviews-Hotel_...   
1  http://www.tripadvisor.com/ShowUserReviews-g60...   
2  http://www.tripadvisor.com/ShowUserReviews-g14...   
3  http://www.tripadvisor.com/ShowUserReviews-g60...   
4  /ShowUserReviews-g34438-d240124-Reviews-Kendal...   
5  /ShowUserReviews-g60750-d80232-Reviews-San_Die...   

                                              ImgURL Location  \
0  http://media-cdn.tripadvisor.com/media/Provide...      NaN   
1                                                NaN      NaN   
2                                                NaN      NaN   
3                                                NaN      NaN   
4  http://media-cdn.tripadvisor.com/media/Provide...      NaN   
5  http://media-cdn.tripadvisor.com/media/Provide...      NaN   

                                Name         Price  
0                      Hotel Seattle   $96 - $118*  
1                                NaN       Unkonwn  
2                                NaN          $353  
3                                NaN          $156  
4           Kendall Hotel and Suites  $124 - $181*  
5  San Diego Marriott Mission Valley  $158 - $248*  
655424.json
                                             Address  HotelID  \
0               315 Seneca St., Seattle, WA 98101      100506   
1                                                NaN  1217974   
2                                                NaN   150849   
3                                                NaN   214680   
4       9100 North Kendall Drive, Miami, FL 33176      240124   
5    8757 Rio San Diego Drive, San Diego, CA 9210...  2515575   

                                            HotelURL  \
0  /ShowUserReviews-g60878-d100506-Reviews-Hotel_...   
1  http://www.tripadvisor.com/ShowUserReviews-g60...   
2  http://www.tripadvisor.com/ShowUserReviews-g14...   
3  http://www.tripadvisor.com/ShowUserReviews-g60...   
4  /ShowUserReviews-g34438-d240124-Reviews-Kendal...   
5  /ShowUserReviews-g60750-d80232-Reviews-San_Die...   

                                              ImgURL Location  \
0  http://media-cdn.tripadvisor.com/media/Provide...      NaN   
1                                                NaN      NaN   
2                                                NaN      NaN   
3                                                NaN      NaN   
4  http://media-cdn.tripadvisor.com/media/Provide...      NaN   
5  http://media-cdn.tripadvisor.com/media/Provide...      NaN   

                                Name         Price  
0                      Hotel Seattle   $96 - $118*  
1                                NaN       Unkonwn  
2                                NaN          $353  
3                                NaN          $156  
4           Kendall Hotel and Suites  $124 - $181*  
5  San Diego Marriott Mission Valley  $158 - $248*  
677703.json
                                             Address  HotelID  \
0               315 Seneca St., Seattle, WA 98101      100506   
1                                                NaN  1217974   
2                                                NaN   150849   
3                                                NaN   214680   
4       9100 North Kendall Drive, Miami, FL 33176      240124   
5    8757 Rio San Diego Drive, San Diego, CA 9210...  2515575   

                                            HotelURL  \
0  /ShowUserReviews-g60878-d100506-Reviews-Hotel_...   
1  http://www.tripadvisor.com/ShowUserReviews-g60...   
2  http://www.tripadvisor.com/ShowUserReviews-g14...   
3  http://www.tripadvisor.com/ShowUserReviews-g60...   
4  /ShowUserReviews-g34438-d240124-Reviews-Kendal...   
5  /ShowUserReviews-g60750-d80232-Reviews-San_Die...   

                                              ImgURL Location  \
0  http://media-cdn.tripadvisor.com/media/Provide...      NaN   
1                                                NaN      NaN   
2                                                NaN      NaN   
3                                                NaN      NaN   
4  http://media-cdn.tripadvisor.com/media/Provide...      NaN   
5  http://media-cdn.tripadvisor.com/media/Provide...      NaN   

                                Name         Price  
0                      Hotel Seattle   $96 - $118*  
1                                NaN       Unkonwn  
2                                NaN          $353  
3                                NaN          $156  
4           Kendall Hotel and Suites  $124 - $181*  
5  San Diego Marriott Mission Valley  $158 - $248*  
,Address,HotelID,HotelURL,ImgURL,Location,Name,Price
0,"  315 Seneca St., Seattle, WA 98101   ",100506,/ShowUserReviews-g60878-d100506-Reviews-Hotel_Seattle-Seattle_Washington.html,http://media-cdn.tripadvisor.com/media/ProviderThumbnails/dirs/7d/22/7d22f3e6c0682df4f389acd30fe4e3d45large.jpg,,Hotel Seattle,$96 - $118*
1,,1217974,http://www.tripadvisor.com/ShowUserReviews-g60878-d1217974-r22522839-Deca_Hotel-Seattle_Washington.html,,,,Unkonwn
2,,150849,http://www.tripadvisor.com/ShowUserReviews-g147293-d150849-r23387184-Riu_Bambu-Punta_Cana_Dominican_Republic.html,,,,$353
3,,214680,http://www.tripadvisor.com/ShowUserReviews-g60982-d214680-r23326648-Park_Shore_Waikiki-Honolulu_Oahu_Hawaii.html,,,,$156
4,"  9100 North Kendall Drive, Miami, FL 33176   ",240124,/ShowUserReviews-g34438-d240124-Reviews-Kendall_Hotel_and_Suites-Miami_Florida.html,http://media-cdn.tripadvisor.com/media/ProviderThumbnails/dirs/41/26/4126535dd6c83348b47318c03433a1d66large.jpg,,Kendall Hotel and Suites,$124 - $181*
5,"  8757 Rio San Diego Drive, San Diego, CA 92108   ",2515575,/ShowUserReviews-g60750-d80232-Reviews-San_Diego_Marriott_Mission_Valley-San_Diego_California.html,http://media-cdn.tripadvisor.com/media/ProviderThumbnails/dirs/af/31/af315f083868045b55af52277e8edf711large.jpg,,San Diego Marriott Mission Valley,$158 - $248*
6,"c/ Argenteria 37, 08003 Barcelona, Spain ",287670,/ShowUserReviews-g187497-d287670-Reviews-Hotel_Banys_Orientals-Barcelona_Catalonia.html,http://media-cdn.tripadvisor.com/media/photo-s/01/05/0a/44/barcellona.jpg,,Hotel Banys Orientals,$115 - $131*
7,"Campomanes 7, 28013 Madrid, Spain ",550994,/ShowUserReviews-g187514-d550994-Reviews-Meninas_Hotel-Madrid.html,http://media-cdn.tripadvisor.com/media/photo-s/02/46/1c/33/twin-room-habitacion.jpg,,Meninas Hotel,$111 and up*
8,,655424,http://www.tripadvisor.com/ShowUserReviews-g297697-d655424-r7674138-Balisandy_Cottages-Kuta_Bali.html,,,,Unkonwn
9,,677703,http://www.tripadvisor.com/ShowUserReviews-g150800-d677703-r20087386-Plaza_Madrid-Mexico_City_Central_Mexico_and_Gulf_Coast.html,,,,Unkonwn