In [1]:

    

import json
import pandas as pd
import requests


    

In [2]:

    

# with open('data/courses.csv', 'r') as courses:
#     courses_df = pd.read_csv(courses)

# with open('data/schools.csv', 'r') as schools:
#     schools_df = pd.read_csv(schools)
    
with open('data/schools.json', 'r') as schools_json:
    schools_json_df = pd.read_json(schools_json)


    

In [5]:

    

schools = []
for index, row in schools_json_df.iterrows():
    schools.append(row['schools'])


    

In [6]:

    

schools_df = pd.DataFrame(schools)


    

In [7]:

    

schools_df.describe()


    

    
Out[7]:







  

    

      

      
contacts
      
cricos_provider_code
      
cricos_url
      
institution_name
      
institution_trading_name
      
institution_type
      
location_capacity
      
locations
      
postal_address
      
website
    
  
  

    

      
count
      
1336
      
1336
      
1336
      
1336
      
1336
      
1336
      
1336
      
1336
      
1336
      
1058
    
    

      
unique
      
1132
      
1143
      
1143
      
1105
      
1105
      
2
      
405
      
1151
      
1127
      
874
    
    

      
top
      
[{'contact_name': 'Gabriela Rodriguez', 'conta...
      
01682E
      
http://cricos.education.gov.au/Institution/Ins...
      
Study Group Australia Pty Limited
      
Study Group Australia Pty Limited
      
Private
      
20
      
[{'location_name': 'Adelaide (South Australia)...
      
Level 6\n505 George Street\nSYDNEY\nNew South ...
      
http://www.holmesinstitute.edu.au/
    
    

      
freq
      
6
      
6
      
6
      
6
      
6
      
1308
      
69
      
6
      
8
      
8
    
  

In [8]:

    

schools_df['locations'][1][0].get('courses')


    

    
Out[8]:





[{'qualifications': {'Field Of Education - 1st Qualification': {'broad_field': '07 - Education',
    'narrow_field': '0700 - Education',
    'detailed_field': '070000 - Education, n.f.d.',
    'course_level': 'Primary School Studies',
    'foundation_studies': 'No',
    'work_component': 'No',
    'course_language': 'English',
    'duration_(weeks)': '364',
    'estimated_total_course_cost': '71,540'}},
  'course_name': 'Primary Years K-6',
  'cricos_course_code': '063549G',
  'dual_qualification': 'No'},
 {'qualifications': {'Field Of Education - 1st Qualification': {'broad_field': '07 - Education',
    'narrow_field': '0700 - Education',
    'detailed_field': '070000 - Education, n.f.d.',
    'course_level': 'Junior Secondary Studies',
    'foundation_studies': 'No',
    'work_component': 'No',
    'course_language': 'English',
    'duration_(weeks)': '208',
    'estimated_total_course_cost': '55,280'}},
  'course_name': 'Secondary Years 7 - 10',
  'cricos_course_code': '063550D',
  'dual_qualification': 'No'}]

In [10]:

    

schools_df['contacts'][0]


    

    
Out[10]:





[{'contact_name': 'Neil Haynes',
  'contact_title': 'Chief Operating Officer',
  'contact_phone_number': '0297457591',
  'contact_facsimile_number': '',
  'contact_email': None}]

In [14]:

    

schools_df.head(1)['postal_address']


    

    
Out[14]:





0    9 Wentworth Street\nLevel 6\nParramatta\nNew S...
Name: postal_address, dtype: object

In [5]:

    

courses_df = courses_df.fillna('')


    

In [6]:

    

school_data = []
for index, row in schools_df.fillna('').iterrows():
    course = courses_df[courses_df['Key']==row['Name']]
    courses_data = []
    if len(course) > 0:
        for index, val in course.iterrows():
            course_data = {
                'course_name': val['Course'].strip(),
                'course_length': val['Course Lenght'],
                'level_length': val['Length(Weeks)'],
                'min_level_length': val['Min'],
                'max_level_length': val['Max'],
                'am_tuition_offshore': val[' AM Tuition fee (Offshore)'].strip(),
                'am_tuition_onshore': val[' AM Tuition fee (Onshore)'].strip(),
                'pm_tuition_offshore': val[' PM Tuition fee (Offshore)'].strip(),
                'pm_tuition_onshore': val[' PM Tuition fee (Onshore)'],
                'enrolment_fee': val[' Enrolment Fee'].replace('$','').strip(),
                'material_fee': val[' Material Fee'].replace('$','').strip(),
                'mat_week_frequency': val['Mat Week frecuency'],
                'morning_timetable': val[' Morning Timetable'].strip(),
                'afternoon_timetable': val['Afternoon Timetable'].strip(),
                'night_timetable': val['Night Timetable'].strip()
            }
            courses_data.append(course_data)
    
    school = {
        'name': row['Name'],
        'address': row['Address'],
        'city': row['City'],
        'amenities': row['Amenities'],
        'surrounding': row['Surrounding'],
        'logo_url': row['Logo URL'],
        'latitude': row['Latitude'],
        'longitude': row['Longitude'],
        'courses': courses_data
    }
    school_data.append(school)

for data in school_data:
    data['courses'] = [
        {
            'name': val['course_name'].strip(),
            'course_length': val['course_length'],
            'level_length': val['level_length'],
            'max_level_length': val['max_level_length'],
            'min_level_length': val['min_level_length'],
            'am_tuition_offshore': 0 if val['am_tuition_offshore'].replace('$','').strip() == '' 
                 else float(val['am_tuition_offshore'].replace('$','').strip()),
            'am_tuition_onshore': 0 if val['am_tuition_onshore'].replace('$','').strip() == ''
                 else float(val['am_tuition_onshore'].replace('$','').strip()),
            'pm_tuition_offshore': 0 if val['pm_tuition_offshore'].replace('$','').strip() == ''
                 else float(val['pm_tuition_offshore'].replace('$','').strip()),
            'pm_tuition_onshore': 0 if val['pm_tuition_onshore'].replace('$','').strip() == ''
                 else float(val['pm_tuition_onshore'].replace('$','').strip()),
            'enrolment_fee': 0 if val['enrolment_fee'].replace('$','').strip() == ''
                 else float(val['enrolment_fee'].replace('$','').strip()),
            'material_fee': 0 if val['material_fee'].replace('$','').strip() == ''
                 else float(val['material_fee'].replace('$','').strip()),
            'mat_week_frequency': 0 if val['mat_week_frequency'] == '' else int(val['mat_week_frequency']),
            'morning_timetable': val['morning_timetable'],
            'afternoon_timetable': val['afternoon_timetable'],
            'night_timetable': val['night_timetable']
        } for val in data['courses']
    ]
        
print(school_data[0])


    

    




{'name': 'Ability - Melbourne', 'address': '55 Swanston St, Melbourne', 'city': 'Melbourne', 'amenities': 'Salon de descanso para estudiantes, Cocina, Salones con proyectores, Neveras, Accesso a silla de ruedas, Internet gratis, Sala de computacion, Sala de juegos', 'surrounding': 'Estacion de tren de Flinders street, Catedral de San Paul, Federation Square, Melbourne Town Hall, Biblioteca de la ciudad Flinders lane', 'logo_url': 'https://res.cloudinary.com/martin-labs/image/upload/v1557699189/passpor/school_logo/Ability.png', 'latitude': -37.8164, 'longitude': 144.9666, 'courses': [{'name': 'General English', 'course_length': 52, 'level_length': '1-12 weeks', 'max_level_length': 11, 'min_level_length': 1, 'am_tuition_offshore': 265.0, 'am_tuition_onshore': 265.0, 'pm_tuition_offshore': 225.0, 'pm_tuition_onshore': 225.0, 'enrolment_fee': 220.0, 'material_fee': 10.0, 'mat_week_frequency': 1, 'morning_timetable': 'Lun - Vie 8:30 - 2:30pm; 9:00am - 3:00pm', 'afternoon_timetable': 'Lun - Vie 5:00pm - 9:30pm', 'night_timetable': ''}, {'name': 'General English', 'course_length': 52, 'level_length': 'More than 12 weeks', 'max_level_length': 52, 'min_level_length': 12, 'am_tuition_offshore': 260.0, 'am_tuition_onshore': 260.0, 'pm_tuition_offshore': 225.0, 'pm_tuition_onshore': 225.0, 'enrolment_fee': 220.0, 'material_fee': 10.0, 'mat_week_frequency': 1, 'morning_timetable': 'Lun - Vie 8:30 - 2:30pm; 9:00am - 3:00pm', 'afternoon_timetable': 'Lun - Vie 5:00pm - 9:30pm', 'night_timetable': ''}]}

In [13]:

    

api = "http://localhost:8080/api/v1/schools/bulk"

try:
    response = requests.post(api, json=school_data)
    print(response)
    print(response.raise_for_status())
except Exception as e:
    print(e)


    

    




<Response [201]>
None

In [7]:

    

api = "http://passporr.com/api/v1/schools/bulk"

try:
    response = requests.post(api, json=school_data)
    print(response)
    print(response.raise_for_status())
except Exception as e:
    print(e)


    

    




<Response [201]>
None

In [ ]: