import pandas as pd
import re

from datetime import date, datetime, timedelta
from geopy.geocoders import Nominatim

#pip install geopy

#Read historical file
data = pd.read_csv(r'D:\Anaconda Files\Earthquakes\Earthquake_History1.csv', decimal=',' ,sep='\t', encoding='utf-8')

file = pd.read_csv(r'D:\Anaconda Files\Earthquakes\Earthquake_History2.csv', decimal=',' ,sep='\t', encoding='utf-8')
data = pd.concat([data, file], axis=0)

file = pd.read_csv(r'D:\Anaconda Files\Earthquakes\Earthquake_History3.csv', decimal=',' ,sep='\t', encoding='utf-8')
data = pd.concat([data, file], axis=0)

file = pd.read_csv(r'D:\Anaconda Files\Earthquakes\Earthquake_History4.csv', decimal=',' ,sep='\t', encoding='utf-8')
data = pd.concat([data, file], axis=0)

file = pd.read_csv(r'D:\Anaconda Files\Earthquakes\Earthquake_History5.csv', decimal=',' ,sep='\t', encoding='utf-8')
data = pd.concat([data, file], axis=0)


#file = file.iloc[0:0]

#Read historical file
#data = pd.read_csv(r'D:\Anaconda Files\Earthquakes\Earthquake_History*.csv', decimal=',' ,sep='\t', encoding='utf-8')
data['Date'] = pd.to_datetime(data['Date'])
data = data[['Date', 'Magnitude', 'Latitude', 'Depth', 'Place']]

data['Magnitude'] = data['Magnitude'].astype(float)

data.insert(5,"City", '')
data.insert(6,"Country", '')

Cityframe = pd.DataFrame(columns=['City', 'Country'])
i=0
for _, row in data.iterrows():
#Get city
    s = data['Place'].iloc[i]
    # initializing substrings
    sub1 = "of"
    sub2 = ","

    # getting index of substrings
    try:
     idx1 = s.index(sub1)
     idx2 = s.index(sub2)
     city = s[idx1 + len(sub1) + 1: idx2]
   
    except:  
     city = ''
    
#Get contry
    # initializing substrings
    sub1 = ","
    sub2 = ","

    # getting index of substrings
    try:
     idx1 = s.index(sub1)
     idx2 = s.index(sub2)
     country = s[idx2+2: len(s)]
   
    except:  
     country = ''
    
    
    #print( str(i) + ' ' + city + ' ' + country)    
    Cityframe = Cityframe.append({'City': city, 'Country': country} , ignore_index=True)

    i=i+1

#Merge
#TF = pd.merge(data, Cityframe, how='left')

data['City'] = Cityframe['City']
data['Country'] = Cityframe['Country']

data.reset_index(drop=True, inplace=True)
data.to_csv(r'D:\Anaconda Files\Earthquakes\Earthquakes.csv',  decimal=',' , sep='\t', encoding='utf-8')

geolocator = Nominatim(user_agent="geoapiExercises")
def city_state_country(coord):
    location = geolocator.reverse(coord, exactly_one=True)
    address = location.raw['address']
    city = address.get('city', '')
    state = address.get('state', '')
    country = address.get('country', '')
    return city, state, country
print(city_state_country("47.470706, -99.704723"))

data[data["Magnitude"]>= 7]

s = data['Place'].iloc[0]
city = re.search('of(.*),', s)
country = re.search(',(.*)

city

# initializing substrings
sub1 = "of"
sub2 = ","
  
# getting index of substrings
idx1 = s.index(sub1)
idx2 = s.index(sub2)

idx1

s

res = s[idx1 + len(sub1) + 1: idx2]

res

data

city

Cityframe.append({'City': city} , ignore_index=True)

Cityframe

data

data['Magnitude'].describe()

#Read historical file data = pd.read_csv(r'D:\Anaconda Files\Earthquakes\Earthquake_History*.csv', decimal=',' ,sep='\t', encoding='utf-8') #data = pd.read_csv(r'D:\Anaconda Files\Earthquakes\Earthquake_History4.csv', decimal=',' ,sep='\t', encoding='utf-8')

file = file.iloc[0:0]

file