<reponame>sharechanxd/Risk-Index-Covid19
dict_iso={'afghanistan': 'Afghanistan',
 'albania': 'Albania',
 'algeria': 'Algeria',
 'andorra': 'Andorra',
 'angola': 'Angola',
 'antigua-and-barbuda': 'Antigua and Barbuda',
 'argentina': 'Argentina',
 'armenia': 'Armenia',
 'aruba': 'Aruba',
 'australia': 'Australia',
 'austria': 'Austria',
 'azerbaijan': 'Azerbaijan',
 'bahamas': 'Bahamas',
 'bahrain': 'Bahrain',
 'bangladesh': 'Bangladesh',
 'Barbados': 'Barbados',
 'belarus': 'Belarus',
 'belgium': 'Belgium',
 'belize': 'Belize',
 'benin': 'Benin',
 'bermuda': 'Bermuda',
 'bhutan': 'Bhutan',
 'bolivia': 'Bolivia, Plurinational State of',
 'bosnia-and-herzegovina': 'Bosnia and Herzegovina',
 'botswana': 'Botswana',
 'brazil': 'Brazil',
 'bulgaria': 'Bulgaria',
 'burkina-faso': 'Burkina Faso',
 'burundi': 'Burundi',
 'cabo-verde': 'Cape Verde',
 'cambodia': 'Cambodia',
 'cameroon': 'Cameroon',
 'canada': 'Canada',
 'cayman-islands': 'Cayman Islands',
 'central-african-republic': 'Central African Republic',
 'chad': 'Chad',
 'chile': 'Chile',
 'china': 'China',
 'china-hong-kong-sar': 'Hong Kong,China',
 'china-macao-sar': 'Macao,China',
 'colombia': 'Colombia',
 'comoros': 'Comoros',
 'congo': 'Congo',
 'costa-rica': 'Costa Rica',
 'cote-d-ivoire': "Côte d'Ivoire",
 'croatia': 'Croatia',
 'cuba': 'Cuba',
 'cyprus': 'Cyprus',
 'czech-republic': 'Czech Republic',
 'democratic-republic-of-the-congo': 'Congo, the Democratic Republic of the',
 'denmark': 'Denmark',
 'djibouti': 'Djibouti',
 'dominican-republic': 'Dominican Republic',
 'ecuador': 'Ecuador',
 'egypt': 'Egypt',
 'el-salvador': 'El Salvador',
 'equatorial-guinea': 'Equatorial Guinea',
 'eritrea': 'Eritrea',
 'estonia': 'Estonia',
 'ethiopia': 'Ethiopia',
 'faeroe-islands': 'Faroe Islands',
 'fiji': 'Fiji',
 'finland': 'Finland',
 'france': 'France',
 'french-guiana': 'French Guiana',
 'french-polynesia': 'French Polynesia',
 'gabon': 'Gabon',
 'gambia': 'Gambia',
 'georgia': 'Georgia',
 'germany': 'Germany',
 'ghana': 'Ghana',
 'gibraltar': 'Gibraltar',
 'greece': 'Greece',
 'grenada': 'Grenada',
 'guadeloupe': 'Guadeloupe',
 'guatemala': 'Guatemala',
 'guinea': 'Guinea',
 'guinea-bissau': 'Guinea-Bissau',
 'guyana': 'Guyana',
 'haiti': 'Haiti',
 'honduras': 'Honduras',
 'hungary': 'Hungary',
 'iceland': 'Iceland',
 'india': 'India',
 'indonesia': 'Indonesia',
 'iran': 'Iran, Islamic Republic of',
 'iraq': 'Iraq',
 'ireland': 'Ireland',
 'israel': 'Israel',
 'italy': 'Italy',
 'jamaica': 'Jamaica',
 'japan': 'Japan',
 'jordan': 'Jordan',
 'kazakhstan': 'Kazakhstan',
 'kenya': 'Kenya',
 'kuwait': 'Kuwait',
 'kyrgyzstan': 'Kyrgyzstan',
 'latvia': 'Latvia',
 'lebanon': 'Lebanon',
 'lesotho': 'Lesotho',
 'liberia': 'Liberia',
 'libya': 'Libya',
 'liechtenstein': 'Liechtenstein',
 'lithuania': 'Lithuania',
 'luxembourg': 'Luxembourg',
 'macedonia': 'North Macedonia',
 'madagascar': 'Madagascar',
 'malawi': 'Malawi',
 'malaysia': 'Malaysia',
 'maldives': 'Maldives',
 'mali': 'Mali',
 'malta': 'Malta',
 'martinique': 'Martinique',
 'mauritania': 'Mauritania',
 'mauritius': 'Mauritius',
 'mayotte': 'Mayotte',
 'mexico': 'Mexico',
 'moldova': 'Moldova, Republic of',
 'monaco': 'Monaco',
 'mongolia': 'Mongolia',
 'montenegro': 'Montenegro',
 'morocco': 'Morocco',
 'mozambique': 'Mozambique',
 'myanmar': 'Myanmar',
 'namibia': 'Namibia',
 'nepal': 'Nepal',
 'netherlands': 'Netherlands',
 'new-zealand': 'New Zealand',
 'nicaragua': 'Nicaragua',
 'niger': 'Niger',
 'nigeria': 'Nigeria',
 'norway': 'Norway',
 'oman': 'Oman',
 'pakistan': 'Pakistan',
 'panama': 'Panama',
 'papua-new-guinea': 'Papua New Guinea',
 'paraguay': 'Paraguay',
 'peru': 'Peru',
 'philippines': 'Philippines',
 'poland': 'Poland',
 'portugal': 'Portugal',
 'qatar': 'Qatar',
 'reunion': 'Réunion',
 'romania': 'Romania',
 'russia': 'Russia',
 'rwanda': 'Rwanda',
 'saint-kitts-and-nevis': 'Saint Kitts and Nevis',
 'saint-lucia': 'Saint Lucia',
 'sao-tome-and-principe': 'Sao Tome and Principe',
 'saudi-arabia': 'Saudi Arabia',
 'senegal': 'Senegal',
 'serbia': 'Serbia',
 'seychelles': 'Seychelles',
 'sierra-leone': 'Sierra Leone',
 'singapore': 'Singapore',
 'slovakia': 'Slovakia',
 'slovenia': 'Slovenia',
 'somalia': 'Somalia',
 'south-africa': 'South Africa',
 'south-korea': 'South Korea',
 'spain': 'Spain',
 'sri-lanka': 'Sri Lanka',
 'state-of-palestine': 'Palestinian Territory, Occupied',
 'sudan': 'Sudan',
 'suriname': 'Suriname',
 'swaziland': 'Swaziland',
 'sweden': 'Sweden',
 'switzerland': 'Switzerland',
 'syria': 'Syrian Arab Republic',
 'taiwan': 'Taiwan,China',
 'tajikistan': 'Tajikistan',
 'tanzania': 'Tanzania, United Republic of',
 'thailand': 'Thailand',
 'togo': 'Togo',
 'trinidad-and-tobago': 'Trinidad and Tobago',
 'tunisia': 'Tunisia',
 'turkey': 'Turkey',
 'turks-and-caicos-islands': 'Turks and Caicos Islands',
 'uganda': 'Uganda',
 'uk': 'United Kingdom',
 'ukraine': 'Ukraine',
 'united-arab-emirates': 'United Arab Emirates',
 'uruguay': 'Uruguay',
 'us': 'United States',
 'uzbekistan': 'Uzbekistan',
 'venezuela': 'Venezuela, Bolivarian Republic of',
 'viet-nam': 'Viet Nam',
 'western-sahara': 'Western Sahara',
 'yemen': 'Yemen',
 'zambia': 'Zambia',
 'zimbabwe': 'Zimbabwe',
 'faeroe-islands':'Faroe Islands',
'saint-vincent-and-the-grenadines':'Saint Vincent & the Grenadines',
'timor-leste':'Timor-Leste',
'grenada':'Grenada',
'new-caledonia':'New Caledonia',
'laos':'Lao People\'s Democratic Republic',
'dominica':'Dominica',
'falkland-islands-malvinas':'Falkland Islands',
'greenland':'Greenland',
'holy-see':'Holy See (Vatican City State)',
'anguilla':'Anguilla',
'south-sudan':'South Sudan'
}

cate={'china':'east asia',
 'us':'north america',
 'brazil':'south america',
 'russia':'eastern europe',
 'india':'south asia',
 'uk':'western europe',
 'spain':'western europe',
 'peru':'south america',
 'chile':'south america',
 'italy':'western europe',
 'iran':'west asia',
 'mexico':'central america and mexico',
 'pakistan':'west asia',
 'turkey':'west asia',
 'germany':'western europe',
 'saudi-arabia':'west asia',
 'france':'western europe',
 'south-africa':'southern africa',
 'bangladesh':'south asia',
 'canada':'north america',
 'qatar':'west asia',
'democratic-republic-of-the-congo':'central africa',
 'colombia':'south america',
 'egypt':'south-east mediterranean',
 'sweden':'western europe',
 'belarus':'eastern europe',
 'belgium':'western europe',
 'argentina':'south america',
 'ecuador':'south america',
 'indonesia':'southeast asia',
 'netherlands':'western europe',
 'united-arab-emirates':'west asia',
 'iraq':'west asia',
 'kuwait':'west asia',
 'singapore':'southeast asia',
 'ukraine':'eastern europe',
 'portugal':'western europe',
 'oman':'west asia',
 'philippines':'southeast asia',
 'poland':'eastern europe',
 'panama':'central america and mexico',
 'switzerland':'western europe',
 'dominican-republic':'caribbean',
 'afghanistan':'west asia',
 'bolivia':'south america',
 'romania':'eastern europe',
 'bahrain':'west asia',
 'ireland':'western europe',
 'armenia':'eastern europe',
 'nigeria':'west africa',
 'israel':'south-east mediterranean',
 'kazakhstan':'central asia',
 'japan':'east asia',
 'austria':'western europe',
 'honduras':'central america and mexico',
'sao-tome-and-principe':'southeast asia',
'central-african-republic':'central africa',
'gabon':'central africa',
 'ghana':'west africa',
 'azerbaijan':'central asia',
 'guatemala':'central america and mexico',
 'moldova':'eastern europe',
 'serbia':'eastern europe',
 'algeria':'south-east mediterranean',
 'nepal':'south asia',
 'south-korea':'east asia',
 'denmark':'western europe',
 'cameroon':'central africa',
 'morocco':'south-east mediterranean',
 'czech-republic':'eastern europe',
 'sudan':'east africa',
 'cote-d-ivoire':'west africa',
 'norway':'western europe',
 'malaysia':'southeast asia',
 'uzbekistan':'central asia',
 'australia':'pacific region',
 'finland':'western europe',
 'saint-martin':'caribbean',
 'senegal':'west africa',
 'macedonia':'eastern europe',
 'kenya':'east africa',
 'el-salvador':'central america and mexico',
 'guyana':'caribbean',
 'tajikistan':'central asia',
 'ethiopia':'east africa',
 'guinea':'west africa',
 'venezuela':'south america',
 'jamaica':'caribbean',
 'kyrgyzstan':'central asia',
 'bulgaria':'eastern europe',
 'djibouti':'east africa',
 'luxembourg':'western europe',
 'mauritania':'west africa',
 'hungary':'eastern europe',
 'bosnia-and-herzegovina':'eastern europe',
 'french-guiana':'south america',
 'grenada':'caribbean',
 'greece':'western europe',
 'thailand':'southeast asia',
 'costa-rica':'central america and mexico',
 'suriname':'caribbean',
 'somalia':'east africa',
 'croatia':'eastern europe',
 'mayotte':'east africa',
 'albania':'eastern europe',
 'cuba':'caribbean',
 'maldives':'south asia',
 'nicaragua':'central america and mexico',
 'equatorial-guinea':'central africa',
 'mali':'west africa',
 'paraguay':'south america',
 'madagascar':'indian ocean islands',
 'sri-lanka':'south asia',
 'haiti':'caribbean',
 'state-of-palestine':'missing',
 'south-sudan':'east africa',
 'estonia':'eastern europe',
 'iceland':'western europe',
 'lithuania':'eastern europe',
 'lebanon':'south-east mediterranean',
 'slovakia':'eastern europe',
 'guinea-bissau':'west africa',
 'slovenia':'eastern europe',
 'zambia':'southern africa',
 'new-zealand':'pacific region',
 'sierra-leone':'west africa',
 'china-hong-kong-sar':'east asia',
 'tunisia':'south-east mediterranean',
 'cabo-verde':'west africa',
 'benin':'west africa',
 'malawi':'southern africa',
 'jordan':'south-east mediterranean',
 'yemen':'west asia',
 'latvia':'eastern europe',
 'niger':'west africa',
 'cyprus':'south-east mediterranean',
 'burkina-faso':'west africa',
 'uruguay':'south america',
 'georgia':'eastern europe',
 'rwanda':'east africa',
 'chad':'west africa',
 'mozambique':'southern africa',
 'uganda':'east africa',
 'andorra':'western europe',
 'swaziland':'southern africa',
 'liberia':'west africa',
 'libya':'south-east mediterranean',
 'malta':'south-east mediterranean',
 'togo':'west africa',
 'channel-islands':'western europe',
 'zimbabwe':'southern africa',
 'reunion':'indian ocean islands',
 'tanzania':'southern africa',
 'montenegro':'eastern europe',
 'taiwan':'east asia',
 'viet-nam':'southeast asia',
 'mauritius':'west africa',
 'myanmar':'southeast asia',
 'comoros':'indian ocean islands',
 'angola':'southern africa',
 'syria':'south-east mediterranean',
 'martinique':'eastern europe',
 'mongolia':'east asia',
 'cayman-islands':'north america',
 'eritrea':'east africa',
 'namibia':'southern africa',
 'guadeloupe':'caribbean',
 'gibraltar':'north africa',
 'burundi':'east africa',
 'bermuda':'north america',
 'cambodia':'southeast asia',
 'bahamas':'caribbean',
 'monaco':'eastern europe',
 'botswana':'southern africa',
 'bhutan':'south asia',
 'seychelles':'indian ocean islands',
 'antigua-and-barbuda':'caribbean',
 'french-polynesia':'pacific region',
 'china-macao-sar':'east asia',
 'gambia':'west africa',
 'turks-and-caicos-islands':'southern africa',
 'lesotho':'southern africa',
 'belize':'caribbean',
 'curacao':'north america',
 'papua-new-guinea':'pacific region',
 'western-sahara':'west africa',
 'fiji':'pacific region',
 'saint-kitts-and-nevis':'caribbean',
 'saint-lucia':'caribbean',
 'congo':'west africa',
 'trinidad-and-tobago':'caribbean',
 'faeroe-islands':'western europe',
 'Barbados':'caribbean',
 'liechtenstein':'western europe',
 'aruba':'western europe',
 'faeroe-islands':'western europe',
'saint-vincent-and-the-grenadines':'caribbean',
'timor-leste':'pacific region',
'grenada':'caribbean',
'new-caledonia':'pacific region',
'laos':'southeast asia',
'dominica':'caribbean',
'falkland-islands-malvinas':'south america',
'greenland':'north america',
'holy-see':'western europe',
'anguilla':'caribbean',
 }
from tqdm import tqdm
import numpy as np
import pandas as pd
from bs4 import BeautifulSoup
import requests
import json
import time
import random
import html5lib
import re
import scipy.stats as st
from pandas.core.frame import DataFrame
import copy
import math
import os
import datetime
#import datetime
x0=datetime.date.today()
x1=datetime.date.today()-datetime.timedelta(days=1)
x2=datetime.date.today()-datetime.timedelta(days=2)
# run_time
ts=[]
ts.append(x0.__format__('%Y%m%d'))
ts.append(x1.__format__('%Y%m%d'))
ts.append(x2.__format__('%Y%m%d'))
print(ts)
headers = { 'Connection': 'close',}
# proxies={'http':'http://127.0.0.1:10080','https':'http://127.0.0.1:10080'}
url='https://www.worldometers.info/coronavirus/#countries'
# url='https://www.worldometers.info/coronavirus/country/us/'
a=requests.get(url,headers=headers)
soup = BeautifulSoup(a.content,'html5lib')
x=soup.body.find_all('tr', attrs={'style': ['','background-color:#F0F0F0','background-color:#EAF7D5']})
# 190 210
def find_start_yesterday(i,j):
    for start in range(i,j):
        one=x[start]
        two=x[start+1]
        l1=one.find_all('a',attrs={'class':'mt_a'})
        l2=two.find_all('a',attrs={'class':'mt_a'})
        if l1==[] or l2==[]:
            continue
        s1=str(l1[0])
        s2=str(l2[0])
        coun1=s1.split('/')
        coun2=s2.split('/')
        if coun1[1]=='china' and coun2[1]=='us':
            return start
#385 410
# def find_end_yesterday(i,j):
#     for end in range(i,j):
#         final_pre=x[end-1]
#         final=x[end]
#         l1=final_pre.find_all('a',attrs={'class':'mt_a'})
#         l2=final.find_all('a',attrs={'class':'mt_a'})
#         if l1==[] or l2==[]:
#             continue
#         s1=str(l1[0])
#         s2=str(l2[0])
#         coun1=s1.split('/')
#         coun2=s2.split('/')
#         if (coun1[1]=='anguilla' and coun2[1]=='saint-pierre-and-miquelon') or (coun2[1]=='anguilla' and coun1[1]=='saint-pierre-and-miquelon'):
#             return end+1

def find_end_yesterday(i,j):
    for end in range(i,j):
        # final_pre=x[end-1]
        final=x[end]
        # l1=final_pre.find_all('a',attrs={'class':'mt_a'})
        l2=final.find_all('a',attrs={'class':'mt_a'})
        if l2==[]:
            continue
        # s1=str(l1[0])
        s2=str(l2[0])
        # coun1=s1.split('/')
        coun2=s2.split('/')
        if coun2[1]=='anguilla':
            return end+1

end=find_end_yesterday(400,440)
end2=find_end_yesterday(630,700)
start=find_start_yesterday(190,250)
start2=find_start_yesterday(440,470)
print('start:{}\tend:{}\tstart2:{}\tend2:{}'.format(start,end,start2,end2))

col_name=['0','#','Country,Other','TotalCases',
 'NewCases','TotalDeaths','NewDeaths','TotalRecovered',
 'NewRecovered','ActiveCases','Serious,Critical','Tot Cases/1M pop',
 'Deaths/1M pop','TotalTests','Tests/1M pop','Population',
 'Continent','17',' 1 Caseevery X', 'ppl1 Deathevery',' X ppl1 Testevery ','X ppl','22',
 'Cases Per 100K Population','Tests Per 100K Population','Active Cases Per 100k Population','Total Test:Positive Ratio',
 'New Positive%','Case Fatality Rate%','New Confirmed Case Growth Rate','New Death Case Growth Rate','Average daily cases per 100,000 people in the past week',
 'New Test','NPI','Region','key-id','Country/District','7 days inc cases','7 days inc deaths']

raw_data=[]
for i in tqdm(range(start,end)):
    text_source=x[i]
    l=text_source.find_all('a',attrs={'class':'mt_a'})
    if l==[]:
        continue
    s=str(l[0])
    coun=s.split('/')
    try:
        region=cate[coun[1]]
        iso=dict_iso[coun[1]]
    except:
        region='missing'
    url='https://www.worldometers.info/coronavirus/country/'+coun[1]+'/'
    print(coun[1])
    a=''
    while a=='':
        try:
            a=requests.get(url,headers=headers)
        except:
            a=''
    soup = BeautifulSoup(a.content,'html5lib')
    r=soup.body.find_all('script',attrs={'type':'text/javascript'})
    p=re.compile(r'categories: \[(.*?)\]',re.S)
    rs=re.findall(p,r[0].text)
    d=rs[0]
    # d=re.sub(r'\"','',d)
    str_pat = re.compile(r'\"(.*?)\"')
    d = str_pat.findall(d)
    date=d
    p1=re.compile(r'name: \'Cases\'.*?\[(.*?)\]',re.S)
    for j in range(10):
        try:
            rs=re.findall(p1,r[j].text)
            d=rs[0]
            d=re.sub(r'\"','',d)
            case=d.split(',')
        except:
            # print('{} cases is not{}'.format(coun[1],j))    
            continue
    p1=re.compile(r'name: \'Deaths\'.*?\[(.*?)\]',re.S)
    for j in range(10):
        try:
            rs=re.findall(p1,r[j].text)
            d=rs[0]
            d=re.sub(r'\"','',d)
            TD=d.split(',')
        except:
            continue
    j={'Date':date,'Total Cases':case,'Total Deaths':TD}
    # print(j)
    print("Date {} TC {} TD {}".format(len(date),len(case),len(TD)))
    if not len(set([len(date),len(case),len(TD)])) == 1:
        continue
    hist_data_of_coun_i=pd.DataFrame(j)
    hist_data_of_coun_i['Total Deaths'][0]=0
    for k in range(len(hist_data_of_coun_i['Total Deaths'])):
        if hist_data_of_coun_i['Total Deaths'][k]=='null':
            data['Total Deaths'][k]=0
    hist_data_of_coun_i['Total Cases']=hist_data_of_coun_i['Total Cases'].astype(int)
    hist_data_of_coun_i['Total Deaths']=hist_data_of_coun_i['Total Deaths'].astype(int)
    hist_data_of_coun_i['case inc']=hist_data_of_coun_i['Total Cases'].diff()
    hist_data_of_coun_i['death inc']=hist_data_of_coun_i['Total Deaths'].diff() 
    #七日新增死亡与cases
    seven_cases=sum([hist_data_of_coun_i.loc[len(date)-i,'case inc'] for i in range(1,8)])
    seven_deaths=sum([hist_data_of_coun_i.loc[len(date)-i,'death inc'] for i in range(1,8)])
       
    inc1=hist_data_of_coun_i.loc[len(date)-1,'case inc']/(7*hist_data_of_coun_i.loc[len(date)-8,'case inc'])
    inc2=hist_data_of_coun_i.loc[len(date)-1,'death inc']/(7*hist_data_of_coun_i.loc[len(date)-8,'death inc'])
    
    inc_1=sum([hist_data_of_coun_i.loc[len(date)-i,'case inc'] for i in range(1,8)])/sum([hist_data_of_coun_i.loc[len(date)-i,'case inc'] for i in range(8,15)])
    inc_2=sum([hist_data_of_coun_i.loc[len(date)-i,'death inc'] for i in range(1,8)])/sum([hist_data_of_coun_i.loc[len(date)-i,'death inc'] for i in range(8,15)])
    adcp=sum([hist_data_of_coun_i.loc[len(date)-i,'case inc'] for i in range(1,8)])/7
    p=1
    while inc1 ==0 and hist_data_of_coun_i.loc[len(date)-1,'Total Cases']>=10000:
        p+=1
        inc1=hist_data_of_coun_i.loc[len(date)-p,'case inc']/(7*hist_data_of_coun_i.loc[len(date)-1-p,'case inc'])
    dd=hist_data_of_coun_i.shift(5)
    hist_data_of_coun_i['inc_p']=np.log(hist_data_of_coun_i['case inc']/dd['case inc'])/5
    hist_data_of_coun_i=hist_data_of_coun_i[~hist_data_of_coun_i.isin([np.nan, np.inf, -np.inf]).any(1)]
    da=hist_data_of_coun_i['inc_p'].values
    try:
        slope,intercept, r_value, p_value, std_err=st.linregress(list(range(30)), da[:30])
    except:
        slope=None

    # print(x[i])
    bo=x[i].text.split('\n')
    # print(bo)
    if bo[6]=='' and bo[7]=='':
        del bo[7]
    if bo[17]=='' and bo[18]=='':
        del bo[18]
    for o in range(start2,end2):
        s1=x[o]
        l1=s1.find_all('a',attrs={'class':'mt_a'})
        if l1==[]:
            continue
        s1=str(l1[0])
        coun1=s1.split('/')
        if coun1[1]==coun[1]:
            bo1=x[o].text.split('\n')
            break
    for h in range(len(bo)):
        bo[h]=bo[h].replace(',','')
        bo[h]=bo[h].replace('+','')
    for h in range(len(bo1)):
        bo1[h]=bo1[h].replace(',','')
        bo1[h]=bo1[h].replace('+','')
    #Cases Per 100K Population
    try:
        bo.append(100000*int(bo[3])/int(bo[15]))
    except:
        print(coun[1])
        continue
        # bo.append(np.nan)
        # print('lack one')
    #Tests Per 100K Population
    try: 
        bo.append(100000*int(bo[13])/int(bo[15]))
    except:
        print(coun[1])
        continue
        # bo.append(np.nan)
        # print('lack one')   
    #'Active Cases Per 100k Population'
    try:
        bo.append(int(bo[9])*100000/int(bo[15]))
    except:
        bo.append(np.nan)
        # print('lack one')
    
    #Total Test:Positive Ratio      
    bo.append(int(bo[3])/int(bo[13]))
    #New Positive
    try:        
        bo.append((int(bo[3])-int(bo1[3]))/(int(bo[13])-int(bo1[13])))
    except:
        bo.append(np.nan)
        # print('lack one')
    #Case Fatality Rate%  
    try:
        if bo[5]=='': 
            bo.append(0)
        else:
            bo.append(int(bo[5])/int(bo[3]))
    except:
        bo.append(np.nan)
    #New Confirmed Case Growth Rate
#     try:
#         q=2
#         while (math.isnan(inc1) or inc1==np.inf) and q<=9:
# #             print(inc1)
#             inc1=hist_data_of_coun_i.loc[len(date)-q,'case inc']/(7*hist_data_of_coun_i.loc[len(date)-q-7,'case inc'])  
#             c=hist_data_of_coun_i.loc[len(date)-q,'case inc']
#             q+=1
# #         print(inc1)
#         if math.isnan(inc1):
#             bo.append(0)
#         elif inc1==np.inf:
#             bo.append(0.01)
#     #    elif c<=100:
#     #        bo.append(0.03)
#         else:
#             bo.append(inc1)
#     except:
#         bo.append(0)
        # print('lack one')
    #New Sum Confirmed Case Growth Rate
    if math.isnan(inc_1) or inc_1=='':
        bo.append(0)
    elif inc_1==np.inf:
        bo.append(0.01)
    else:
        bo.append(inc_1)
    print(bo[-1])
    #New Death Case Growth Rate   
#     try:
#         q=2
#         while (math.isnan(inc2) or inc2==np.inf) and q<=9:
# #             print(inc2)
#             inc2=hist_data_of_coun_i.loc[len(date)-q,'death inc']/(7*hist_data_of_coun_i.loc[len(date)-q-7,'death inc'])
#             q+=1
# #         print(inc2)
#         if math.isnan(inc2):
#             bo.append(0)
#         elif inc2==np.inf:
#             bo.append(0.1)
#         else:
#             bo.append(inc2)
#     except:
#         bo.append(0)
        # print('lack one')
    #New Sum Death Case Growth Rate
    if math.isnan(inc_2) or inc_2=='':
        bo.append(0)
    elif inc_2==np.inf:
        bo.append(0.1)
    else:
        bo.append(inc_2)
    print(bo[-1])

    #Average daily cases per 100,000 people in the past week
    bo.append(adcp*100000/int(bo[15]))

    # New Test
    try:
        bo.append(int(bo[13])-int(bo1[13]))
    except:
        bo.append(np.nan)
        # print('lack one')
    
    bo.append(slope)
    if region=='missing':
        continue
    else:
        bo.append(region)
    bo.append(coun1[1])
    bo.append(iso)
    bo.append(seven_cases)
    bo.append(seven_deaths)
    print(len(bo))
    print(bo)
    if len(bo)!=39:
        os.exit(-1)
    raw_data.append(bo)
raw_data=DataFrame(raw_data,columns=col_name)
brief_raw_data=raw_data[['Country,Other','key-id','Region','Country/District','Population',
'TotalCases','ActiveCases','TotalDeaths','NewDeaths','TotalRecovered','NewRecovered','Serious,Critical','NewCases','New Test','Cases Per 100K Population','Tests Per 100K Population',
'Active Cases Per 100k Population','Total Test:Positive Ratio','New Positive%',
          'Case Fatality Rate%','New Confirmed Case Growth Rate','New Death Case Growth Rate','Average daily cases per 100,000 people in the past week','NPI','7 days inc cases','7 days inc deaths']]
brief_raw_data['week death rate']=brief_raw_data['7 days inc deaths']/brief_raw_data['7 days inc cases']
tf=copy.deepcopy(brief_raw_data)
uni_region=list(set(list(tf['Region'].values)))
    
uni_region.remove('western europe')
data_region=tf[tf['Region']=='western europe']
data_region=data_region.replace(np.nan,'shit')
data_region=data_region.replace(np.inf,'shit')
data_region=data_region.replace('N/A','shit')
data_region=data_region.replace('',0)
data_region=data_region.replace(' ',0)
data_region.loc[data_region['NPI']=='shit','NPI']=0
data_region.loc[data_region['Case Fatality Rate%']=='shit','Case Fatality Rate%']=0
dd=data_region[['TotalCases','ActiveCases','TotalRecovered','Case Fatality Rate%']]
ac=dd[(dd['TotalCases']!='shit')&(dd['ActiveCases']!='shit')&(dd['TotalRecovered']!='shit')&(dd['Case Fatality Rate%']!='shit')]
active_rate_region=sum(ac['ActiveCases'].astype(int))/sum(ac['TotalCases'].astype(int))
data_region.loc[data_region['Active Cases Per 100k Population']=='shit','Active Cases Per 100k Population']=active_rate_region*100000*data_region.loc[data_region['Active Cases Per 100k Population']=='shit','TotalCases'].astype(int)/data_region.loc[data_region['Active Cases Per 100k Population']=='shit','Population'].astype(int)

dd=data_region[['NewCases','New Test']]
ac=dd[dd['New Test']!=0]
new_posi=sum(ac['NewCases'].astype(int))/sum(ac['New Test'])
data_region.loc[data_region['New Test']==0,'New Positive%']=new_posi
final=copy.deepcopy(data_region)
for distri in uni_region:   
    data_region=tf[tf['Region']==distri]
    data_region=data_region.replace(np.nan,'shit')
    data_region=data_region.replace(np.inf,'shit')
    data_region=data_region.replace('N/A','shit')
    data_region=data_region.replace('',0)
    data_region=data_region.replace(' ',0)
    data_region.loc[data_region['NPI']=='shit','NPI']=0
    data_region.loc[data_region['Case Fatality Rate%']=='shit','Case Fatality Rate%']=0
    dd=data_region[['TotalCases','ActiveCases','TotalRecovered','Case Fatality Rate%']]
    ac=dd[(dd['TotalCases']!='shit')&(dd['ActiveCases']!='shit')&(dd['TotalRecovered']!='shit')&(dd['Case Fatality Rate%']!='shit')]
    active_rate_region=sum(ac['ActiveCases'].astype(int))/sum(ac['TotalCases'].astype(int))
    data_region.loc[data_region['Active Cases Per 100k Population']=='shit','Active Cases Per 100k Population']=active_rate_region*100000*data_region.loc[data_region['Active Cases Per 100k Population']=='shit','TotalCases'].astype(int)/data_region.loc[data_region['Active Cases Per 100k Population']=='shit','Population'].astype(int)

    dd=data_region[['NewCases','New Test']]
    ac=dd[dd['New Test']!=0]
    try:
        new_posi=sum(ac['NewCases'].astype(int))/sum(ac['New Test'])
    except:
        new_posi=0
    data_region.loc[data_region['New Test']==0,'New Positive%']=new_posi
    data_region.loc[data_region['New Test']=='shit','New Positive%']=new_posi
    final=pd.concat([final,data_region])
final=final.reset_index(drop=True)
tf2=final[['Country,Other','key-id','Country/District','Region','TotalCases','Cases Per 100K Population','Tests Per 100K Population',
'Active Cases Per 100k Population','Total Test:Positive Ratio','New Positive%',
          'Case Fatality Rate%','New Confirmed Case Growth Rate','New Death Case Growth Rate','Average daily cases per 100,000 people in the past week','NPI']]

#越高越好，即需要降序
# for x in ['Cases Per 100K Population','Active Cases Per 100k Population','Total Test:Positive Ratio','New Positive%',]
x='Tests Per 100K Population'
df=copy.deepcopy(tf2[['Country,Other',x]])
df2=df.sort_values(x,ascending=False,inplace=False)
df2 = df2.reset_index(drop=True)
df2['cum']=df.index+1
df2['cum_prob']=100*df2['cum']/max(df2['cum'])
df3=pd.merge(df,df2,on=['Country,Other'])
# tf2['IND_'+x]=df3['cum_prob']
tf2['IND_'+x]=0
for h in list(tf2['Country,Other'].values):
    tf2.loc[tf2['Country,Other']==h,'IND_'+x]=df3.loc[df3['Country,Other']==h,'cum_prob'].values[0]



for x in ['Cases Per 100K Population','Active Cases Per 100k Population','Total Test:Positive Ratio','New Positive%','Case Fatality Rate%','New Confirmed Case Growth Rate','New Death Case Growth Rate','Average daily cases per 100,000 people in the past week','NPI']:
    i=1
    df=copy.deepcopy(tf2[['Country,Other',x]])
    print(x)
    df2=df.sort_values(x,inplace=False)
    df2 = df2.reset_index(drop=True)
    df2['cum']=df.index+1
    df2['cum_prob']=100*df2['cum']/max(df2['cum'])
    df3=pd.merge(df,df2,on=['Country,Other'])
    tf2['IND_'+x]=0
    for h in list(tf2['Country,Other'].values):
        tf2.loc[tf2['Country,Other']==h,'IND_'+x]=df3.loc[df3['Country,Other']==h,'cum_prob'].values[0]
    i+=1

tf2['Comprehensive Index']=0.15*tf2['IND_Cases Per 100K Population']+0.08*tf2['IND_Tests Per 100K Population']+0.2*tf2['IND_Active Cases Per 100k Population']+0.1*tf2['IND_Total Test:Positive Ratio']+0.13*tf2['IND_New Positive%']+0.05*tf2['IND_Case Fatality Rate%']+ 0.22*tf2['IND_New Confirmed Case Growth Rate']+0.07*tf2['IND_New Death Case Growth Rate']
today=datetime.datetime.now()
tf4=tf2[['Country/District','TotalCases','IND_Cases Per 100K Population','IND_Tests Per 100K Population','IND_Total Test:Positive Ratio',
'IND_New Positive%','IND_Case Fatality Rate%','IND_New Confirmed Case Growth Rate','IND_New Death Case Growth Rate','IND_Active Cases Per 100k Population',
'IND_NPI','IND_Average daily cases per 100,000 people in the past week','Comprehensive Index']]
tf_c=copy.deepcopy(tf4)
tf_c_rename=tf_c.rename({'TotalCases':'TOTAL CASE','IND_Cases Per 100K Population':'IND1_Cases Per 100K Population','IND_Tests Per 100K Population':'IND2_Tests Per 100K Population',
'IND_Active Cases Per 100k Population':'IND8_Active Cases Per 100k Population','IND_Total Test:Positive Ratio':'IND3_Total Test:Positive Ratio',
'IND_New Positive%':'IND4_New Positive%','IND_Case Fatality Rate%':'IND5_Case Fatality Rate%','IND_New Confirmed Case Growth Rate':'IND6_New Confirmed Case Growth Rate',
'IND_New Death Case Growth Rate':'IND7_New Death Case Growth Rate','IND_NPI':'NPI'},axis='columns')
tf_c_rename.to_excel('World_index_{}.xlsx'.format(today),sheet_name=ts[1],index=False)
tf2.to_excel('World_raw_index_{}.xlsx'.format(today),sheet_name=ts[1],index=False)

url='https://raw.githubusercontent.com/owid/covid-19-data/master/public/data/vaccinations/vaccinations.csv'
a=requests.get(url,headers=headers)
with open("vacc.csv",'wb') as f:
    f.write(a.content)
vacc = pd.read_csv('vacc.csv',keep_default_na=False)
ct = list(dict(vacc['location'].value_counts()).keys())
for x in ['total_vaccinations','people_vaccinated','people_fully_vaccinated','total_boosters']:
    vacc[x]=vacc[x].replace('',0)
    vacc[x]=vacc[x].astype(float)
    vacc[x]=vacc[x].astype(int)
img = dict()
for i in ct:
    dt = vacc[vacc['location']==i]
    d=[]
    for x in ['total_vaccinations','people_vaccinated','people_fully_vaccinated','total_boosters']:
        d.append(max(dt[x]))
    img[i]=d
brief_raw_data['total_vaccinations']=0
brief_raw_data['people_vaccinated']=0
brief_raw_data['people_fully_vaccinated']=0
brief_raw_data['total_boosters']=0
for i in img.keys():
    brief_raw_data.loc[((brief_raw_data['Country,Other']==i)|(brief_raw_data['Country/District']==i)),'total_vaccinations'] = int(img[i][0])
    brief_raw_data.loc[((brief_raw_data['Country,Other']==i)|(brief_raw_data['Country/District']==i)),'people_vaccinated'] = int(img[i][1])
    brief_raw_data.loc[((brief_raw_data['Country,Other']==i)|(brief_raw_data['Country/District']==i)),'people_fully_vaccinated'] = int(img[i][2])
    brief_raw_data.loc[((brief_raw_data['Country,Other']==i)|(brief_raw_data['Country/District']==i)),'total_boosters'] = int(img[i][3])
    
brief_raw_data['Population']=brief_raw_data['Population'].astype(int)
brief_raw_data['vacc_per_100']=brief_raw_data['total_vaccinations']*100/brief_raw_data['Population']
brief_raw_data['cases_per_100']=brief_raw_data['Cases Per 100K Population']/1000
brief_raw_data['total_immune']=brief_raw_data['cases_per_100']+brief_raw_data['vacc_per_100']*0.9   

def infer_vaccinated(one_row):
    if one_row['total_vaccinations']!=0 and one_row['people_vaccinated']==0:
        one_row['people_vaccinated']=int(0.7018*one_row['total_vaccinations'])
    if one_row['total_vaccinations']!=0 and one_row['people_fully_vaccinated']==0:
        one_row['people_fully_vaccinated']=int(0.2936*one_row['total_vaccinations'])
    return one_row
brief_raw_data = brief_raw_data.apply(infer_vaccinated,axis=1)

import datetime
from datetime import timedelta
import sys
vacc = pd.read_csv('vacc.csv',keep_default_na=False)
ct = list(dict(vacc['location'].value_counts()).keys())
vacc_daily=copy.deepcopy(vacc)
vacc_daily['daily_vaccinations']=vacc_daily['daily_vaccinations'].replace('',-1)
vacc_daily['daily_vaccinations']=vacc_daily['daily_vaccinations'].astype(int)
vacc_daily = vacc_daily.drop(vacc_daily[vacc_daily['daily_vaccinations']==-1].index)
vacc_daily['date']=pd.to_datetime(vacc_daily['date']).dt.date
daily=dict()
# vacc_daily.loc[vacc_daily['date']==,'daily_vaccinations']
for i in ct:
    rdd=[]
    dt = vacc_daily[vacc_daily['location']==i]
    today=datetime.date.today()
    try:
        a=max(dt['date'])
    #     print(a)
    #     print(today)
    #     print(today-timedelta(14))
#         if today-timedelta(14)<a:
        x=dt.loc[(dt['date']<=today)&(dt['date']>=today-timedelta(14)),'daily_vaccinations']
        note = sum(x)/len(x)
#         else:
#             note = -2
    except:
        note =-2
    daily[i]=int(note)
for x in ['total_vaccinations','people_vaccinated','people_fully_vaccinated','total_boosters']:
    vacc[x]=vacc[x].replace('',-1)
    vacc[x]=vacc[x].astype(float)
    vacc[x]=vacc[x].astype(int)
vacc = vacc.drop(vacc[vacc['total_vaccinations']==-1].index)
vacc['date']=pd.to_datetime(vacc['date'])
rd=dict()
for i in ct:
    rdd=[]
    dt = vacc[vacc['location']==i]
    a=max(dt['date'])
    b=min(dt['date'])
    c=a-b
    Vacc_Days=int(c.days)
    if Vacc_Days==0:
        Vacc_Days=1
    Total_Vacc=int(max(dt['total_vaccinations']))
    Avg_Vac_Daily=Total_Vacc/Vacc_Days
    d=a-timedelta(14)
    if len(list(dt['date']))==1:
        Avg_Vac_Last14D=Total_Vacc
    elif daily[i]==-2:
        Avg_Vac_Last14D=Total_Vacc/Vacc_Days
    else:
        Avg_Vac_Last14D=daily[i]
    rdd=[Vacc_Days,Total_Vacc,Avg_Vac_Daily,Avg_Vac_Last14D]
    rd[i]=rdd        
    

# lst14days=pd.DataFrame(rd,columns=['Country','Vac_Days','Total_Vac','Avg_Vac_Daily','Avg_Vac_Last14D'])
brief_raw_data['累计感染率（%）']=brief_raw_data['Cases Per 100K Population']/100000
brief_raw_data['接种率=（接种疫苗数/总人口）']=brief_raw_data['vacc_per_100']/100
brief_raw_data['VacSpeed_Last14D']=0.05
brief_raw_data['Population']=brief_raw_data['Population'].astype(int)

brief_raw_data['fully vaccinated %']=100*brief_raw_data['people_fully_vaccinated']/brief_raw_data['Population']
brief_raw_data['at least 1 dose vaccinated %']=100*brief_raw_data['people_vaccinated']/brief_raw_data['Population']
brief_raw_data['Fully Vac Ratio %']=100*brief_raw_data['fully vaccinated %']/brief_raw_data['at least 1 dose vaccinated %']
# brief_raw_data['加强针接种率']=brief_raw_data['total_boosters']/brief_raw_data['Population']

for i in rd.keys():
    brief_raw_data.loc[((brief_raw_data['Country,Other']==i)|(brief_raw_data['Country/District']==i)),'VacSpeed_Last14D'] = 100*int(rd[i][3])/brief_raw_data.loc[((brief_raw_data['Country,Other']==i)|(brief_raw_data['Country/District']==i)),'Population']
vaccine_efficacy = pd.read_excel('Vaccine-Country.xlsx',sheet_name='Efficacy')
vaccine_efficacy = vaccine_efficacy.set_index('Vaccine').T.to_dict('list')
vaccine_map=pd.read_excel('Vaccine-Country.xlsx',sheet_name='Vac-Mapping')
vaccine_map = dict(zip(vaccine_map['Location'],vaccine_map['首选疫苗']))
brief_raw_data['Vaccine1']='NA'
brief_raw_data['Vaccine_Efficacy']=0.8
for i in vaccine_map.keys():
    brief_raw_data.loc[((brief_raw_data['Country,Other']==i)|(brief_raw_data['Country/District']==i)),'Vaccine1'] = vaccine_map[i]
    brief_raw_data.loc[((brief_raw_data['Country,Other']==i)|(brief_raw_data['Country/District']==i)),'Vaccine_Efficacy'] = vaccine_efficacy[vaccine_map[i]][1]

brief_raw_data['total_immune_adj']=brief_raw_data['累计感染率（%）']*100+brief_raw_data['vacc_per_100']*brief_raw_data['Vaccine_Efficacy']/2
brief_raw_data['DaystoReachHerdImmunity']=(0.7-brief_raw_data['累计感染率（%）']-brief_raw_data['接种率=（接种疫苗数/总人口）']*brief_raw_data['Vaccine_Efficacy']/2)/(brief_raw_data['VacSpeed_Last14D']*brief_raw_data['Vaccine_Efficacy']*0.01/2)

import datetime
def change_time(x):
    in_date = ts[1]
    # print(x)
    if x<0:
        out_date = 'already'
        return out_date
    elif math.isinf(x):
        out_date = 'never'
        return out_date
    dt = datetime.datetime.strptime(in_date, "%Y%m%d")
    try:
        out_date = (dt + datetime.timedelta(days=int(x))).strftime("%Y-%m-%d")
    except:
        out_date = 'never'
    return out_date

# brief_raw_data.to_excel('World_rawdata_test.xlsx')
brief_raw_data['HerdImmunityDate']=brief_raw_data['DaystoReachHerdImmunity'].map(change_time)
brief_raw_data['Total Boosters']=brief_raw_data['total_boosters']
brief_raw_data['加强针接种率']=brief_raw_data['total_boosters']/brief_raw_data['Population']
brief_raw_data.to_excel('World_rawdata_{}.xlsx'.format(today),sheet_name=ts[1],index=False)

uni_region=list(set(list(brief_raw_data['Region'].values)))
writer = pd.ExcelWriter('Region_World_Rawdata_{}.xlsx'.format(today))
for distri in uni_region:   
    data_region=brief_raw_data[brief_raw_data['Region']==distri]
    data_region.to_excel(writer,sheet_name=distri,index=False)
writer.save()
dict_iso={'afghanistan': 'Afghanistan',
 'albania': 'Albania',
 'algeria': 'Algeria',
 'andorra': 'Andorra',
 'angola': 'Angola',
 'antigua-and-barbuda': 'Antigua and Barbuda',
 'argentina': 'Argentina',
 'armenia': 'Armenia',
 'aruba': 'Aruba',
 'australia': 'Australia',
 'austria': 'Austria',
 'azerbaijan': 'Azerbaijan',
 'bahamas': 'Bahamas',
 'bahrain': 'Bahrain',
 'bangladesh': 'Bangladesh',
 'Barbados': 'Barbados',
 'belarus': 'Belarus',
 'belgium': 'Belgium',
 'belize': 'Belize',
 'benin': 'Benin',
 'bermuda': 'Bermuda',
 'bhutan': 'Bhutan',
 'bolivia': 'Bolivia, Plurinational State of',
 'bosnia-and-herzegovina': 'Bosnia and Herzegovina',
 'botswana': 'Botswana',
 'brazil': 'Brazil',
 'bulgaria': 'Bulgaria',
 'burkina-faso': 'Burkina Faso',
 'burundi': 'Burundi',
 'cabo-verde': 'Cape Verde',
 'cambodia': 'Cambodia',
 'cameroon': 'Cameroon',
 'canada': 'Canada',
 'cayman-islands': 'Cayman Islands',
 'central-african-republic': 'Central African Republic',
 'chad': 'Chad',
 'chile': 'Chile',
 'china': 'China',
 'china-hong-kong-sar': 'Hong Kong,China',
 'china-macao-sar': 'Macao,China',
 'colombia': 'Colombia',
 'comoros': 'Comoros',
 'congo': 'Congo',
 'costa-rica': 'Costa Rica',
 'cote-d-ivoire': "Côte d'Ivoire",
 'croatia': 'Croatia',
 'cuba': 'Cuba',
 'cyprus': 'Cyprus',
 'czech-republic': 'Czech Republic',
 'democratic-republic-of-the-congo': 'Congo, the Democratic Republic of the',
 'denmark': 'Denmark',
 'djibouti': 'Djibouti',
 'dominican-republic': 'Dominican Republic',
 'ecuador': 'Ecuador',
 'egypt': 'Egypt',
 'el-salvador': 'El Salvador',
 'equatorial-guinea': 'Equatorial Guinea',
 'eritrea': 'Eritrea',
 'estonia': 'Estonia',
 'ethiopia': 'Ethiopia',
 'faeroe-islands': 'Faroe Islands',
 'fiji': 'Fiji',
 'finland': 'Finland',
 'france': 'France',
 'french-guiana': 'French Guiana',
 'french-polynesia': 'French Polynesia',
 'gabon': 'Gabon',
 'gambia': 'Gambia',
 'georgia': 'Georgia',
 'germany': 'Germany',
 'ghana': 'Ghana',
 'gibraltar': 'Gibraltar',
 'greece': 'Greece',
 'grenada': 'Grenada',
 'guadeloupe': 'Guadeloupe',
 'guatemala': 'Guatemala',
 'guinea': 'Guinea',
 'guinea-bissau': 'Guinea-Bissau',
 'guyana': 'Guyana',
 'haiti': 'Haiti',
 'honduras': 'Honduras',
 'hungary': 'Hungary',
 'iceland': 'Iceland',
 'india': 'India',
 'indonesia': 'Indonesia',
 'iran': 'Iran, Islamic Republic of',
 'iraq': 'Iraq',
 'ireland': 'Ireland',
 'israel': 'Israel',
 'italy': 'Italy',
 'jamaica': 'Jamaica',
 'japan': 'Japan',
 'jordan': 'Jordan',
 'kazakhstan': 'Kazakhstan',
 'kenya': 'Kenya',
 'kuwait': 'Kuwait',
 'kyrgyzstan': 'Kyrgyzstan',
 'latvia': 'Latvia',
 'lebanon': 'Lebanon',
 'lesotho': 'Lesotho',
 'liberia': 'Liberia',
 'libya': 'Libya',
 'liechtenstein': 'Liechtenstein',
 'lithuania': 'Lithuania',
 'luxembourg': 'Luxembourg',
 'macedonia': 'North Macedonia',
 'madagascar': 'Madagascar',
 'malawi': 'Malawi',
 'malaysia': 'Malaysia',
 'maldives': 'Maldives',
 'mali': 'Mali',
 'malta': 'Malta',
 'martinique': 'Martinique',
 'mauritania': 'Mauritania',
 'mauritius': 'Mauritius',
 'mayotte': 'Mayotte',
 'mexico': 'Mexico',
 'moldova': 'Moldova, Republic of',
 'monaco': 'Monaco',
 'mongolia': 'Mongolia',
 'montenegro': 'Montenegro',
 'morocco': 'Morocco',
 'mozambique': 'Mozambique',
 'myanmar': 'Myanmar',
 'namibia': 'Namibia',
 'nepal': 'Nepal',
 'netherlands': 'Netherlands',
 'new-zealand': 'New Zealand',
 'nicaragua': 'Nicaragua',
 'niger': 'Niger',
 'nigeria': 'Nigeria',
 'norway': 'Norway',
 'oman': 'Oman',
 'pakistan': 'Pakistan',
 'panama': 'Panama',
 'papua-new-guinea': 'Papua New Guinea',
 'paraguay': 'Paraguay',
 'peru': 'Peru',
 'philippines': 'Philippines',
 'poland': 'Poland',
 'portugal': 'Portugal',
 'qatar': 'Qatar',
 'reunion': 'Réunion',
 'romania': 'Romania',
 'russia': 'Russia',
 'rwanda': 'Rwanda',
 'saint-kitts-and-nevis': 'Saint Kitts and Nevis',
 'saint-lucia': 'Saint Lucia',
 'sao-tome-and-principe': 'Sao Tome and Principe',
 'saudi-arabia': 'Saudi Arabia',
 'senegal': 'Senegal',
 'serbia': 'Serbia',
 'seychelles': 'Seychelles',
 'sierra-leone': 'Sierra Leone',
 'singapore': 'Singapore',
 'slovakia': 'Slovakia',
 'slovenia': 'Slovenia',
 'somalia': 'Somalia',
 'south-africa': 'South Africa',
 'south-korea': 'South Korea',
 'spain': 'Spain',
 'sri-lanka': 'Sri Lanka',
 'state-of-palestine': 'Palestinian Territory, Occupied',
 'sudan': 'Sudan',
 'suriname': 'Suriname',
 'swaziland': 'Swaziland',
 'sweden': 'Sweden',
 'switzerland': 'Switzerland',
 'syria': 'Syrian Arab Republic',
 'taiwan': 'Taiwan,China',
 'tajikistan': 'Tajikistan',
 'tanzania': 'Tanzania, United Republic of',
 'thailand': 'Thailand',
 'togo': 'Togo',
 'trinidad-and-tobago': 'Trinidad and Tobago',
 'tunisia': 'Tunisia',
 'turkey': 'Turkey',
 'turks-and-caicos-islands': 'Turks and Caicos Islands',
 'uganda': 'Uganda',
 'uk': 'United Kingdom',
 'ukraine': 'Ukraine',
 'united-arab-emirates': 'United Arab Emirates',
 'uruguay': 'Uruguay',
 'us': 'United States',
 'uzbekistan': 'Uzbekistan',
 'venezuela': 'Venezuela, Bolivarian Republic of',
 'viet-nam': 'Viet Nam',
 'western-sahara': 'Western Sahara',
 'yemen': 'Yemen',
 'zambia': 'Zambia',
 'zimbabwe': 'Zimbabwe',
 'faeroe-islands':'Faroe Islands',
'saint-vincent-and-the-grenadines':'Saint Vincent & the Grenadines',
'timor-leste':'Timor-Leste',
'grenada':'Grenada',
'new-caledonia':'New Caledonia',
'laos':'Lao People\'s Democratic Republic',
'dominica':'Dominica',
'falkland-islands-malvinas':'Falkland Islands',
'greenland':'Greenland',
'holy-see':'Holy See (Vatican City State)',
'anguilla':'Anguilla',
'south-sudan':'South Sudan'
}

cate={'china':'east asia',
 'us':'north america',
 'brazil':'south america',
 'russia':'eastern europe',
 'india':'south asia',
 'uk':'western europe',
 'spain':'western europe',
 'peru':'south america',
 'chile':'south america',
 'italy':'western europe',
 'iran':'west asia',
 'mexico':'central america and mexico',
 'pakistan':'west asia',
 'turkey':'west asia',
 'germany':'western europe',
 'saudi-arabia':'west asia',
 'france':'western europe',
 'south-africa':'southern africa',
 'bangladesh':'south asia',
 'canada':'north america',
 'qatar':'west asia',
'democratic-republic-of-the-congo':'central africa',
 'colombia':'south america',
 'egypt':'south-east mediterranean',
 'sweden':'western europe',
 'belarus':'eastern europe',
 'belgium':'western europe',
 'argentina':'south america',
 'ecuador':'south america',
 'indonesia':'southeast asia',
 'netherlands':'western europe',
 'united-arab-emirates':'west asia',
 'iraq':'west asia',
 'kuwait':'west asia',
 'singapore':'southeast asia',
 'ukraine':'eastern europe',
 'portugal':'western europe',
 'oman':'west asia',
 'philippines':'southeast asia',
 'poland':'eastern europe',
 'panama':'central america and mexico',
 'switzerland':'western europe',
 'dominican-republic':'caribbean',
 'afghanistan':'west asia',
 'bolivia':'south america',
 'romania':'eastern europe',
 'bahrain':'west asia',
 'ireland':'western europe',
 'armenia':'eastern europe',
 'nigeria':'west africa',
 'israel':'south-east mediterranean',
 'kazakhstan':'central asia',
 'japan':'east asia',
 'austria':'western europe',
 'honduras':'central america and mexico',
'sao-tome-and-principe':'southeast asia',
'central-african-republic':'central africa',
'gabon':'central africa',
 'ghana':'west africa',
 'azerbaijan':'central asia',
 'guatemala':'central america and mexico',
 'moldova':'eastern europe',
 'serbia':'eastern europe',
 'algeria':'south-east mediterranean',
 'nepal':'south asia',
 'south-korea':'east asia',
 'denmark':'western europe',
 'cameroon':'central africa',
 'morocco':'south-east mediterranean',
 'czech-republic':'eastern europe',
 'sudan':'east africa',
 'cote-d-ivoire':'west africa',
 'norway':'western europe',
 'malaysia':'southeast asia',
 'uzbekistan':'central asia',
 'australia':'pacific region',
 'finland':'western europe',
 'saint-martin':'caribbean',
 'senegal':'west africa',
 'macedonia':'eastern europe',
 'kenya':'east africa',
 'el-salvador':'central america and mexico',
 'guyana':'caribbean',
 'tajikistan':'central asia',
 'ethiopia':'east africa',
 'guinea':'west africa',
 'venezuela':'south america',
 'jamaica':'caribbean',
 'kyrgyzstan':'central asia',
 'bulgaria':'eastern europe',
 'djibouti':'east africa',
 'luxembourg':'western europe',
 'mauritania':'west africa',
 'hungary':'eastern europe',
 'bosnia-and-herzegovina':'eastern europe',
 'french-guiana':'south america',
 'grenada':'caribbean',
 'greece':'western europe',
 'thailand':'southeast asia',
 'costa-rica':'central america and mexico',
 'suriname':'caribbean',
 'somalia':'east africa',
 'croatia':'eastern europe',
 'mayotte':'east africa',
 'albania':'eastern europe',
 'cuba':'caribbean',
 'maldives':'south asia',
 'nicaragua':'central america and mexico',
 'equatorial-guinea':'central africa',
 'mali':'west africa',
 'paraguay':'south america',
 'madagascar':'indian ocean islands',
 'sri-lanka':'south asia',
 'haiti':'caribbean',
 'state-of-palestine':'missing',
 'south-sudan':'east africa',
 'estonia':'eastern europe',
 'iceland':'western europe',
 'lithuania':'eastern europe',
 'lebanon':'south-east mediterranean',
 'slovakia':'eastern europe',
 'guinea-bissau':'west africa',
 'slovenia':'eastern europe',
 'zambia':'southern africa',
 'new-zealand':'pacific region',
 'sierra-leone':'west africa',
 'china-hong-kong-sar':'east asia',
 'tunisia':'south-east mediterranean',
 'cabo-verde':'west africa',
 'benin':'west africa',
 'malawi':'southern africa',
 'jordan':'south-east mediterranean',
 'yemen':'west asia',
 'latvia':'eastern europe',
 'niger':'west africa',
 'cyprus':'south-east mediterranean',
 'burkina-faso':'west africa',
 'uruguay':'south america',
 'georgia':'eastern europe',
 'rwanda':'east africa',
 'chad':'west africa',
 'mozambique':'southern africa',
 'uganda':'east africa',
 'andorra':'western europe',
 'swaziland':'southern africa',
 'liberia':'west africa',
 'libya':'south-east mediterranean',
 'malta':'south-east mediterranean',
 'togo':'west africa',
 'channel-islands':'western europe',
 'zimbabwe':'southern africa',
 'reunion':'indian ocean islands',
 'tanzania':'southern africa',
 'montenegro':'eastern europe',
 'taiwan':'east asia',
 'viet-nam':'southeast asia',
 'mauritius':'west africa',
 'myanmar':'southeast asia',
 'comoros':'indian ocean islands',
 'angola':'southern africa',
 'syria':'south-east mediterranean',
 'martinique':'eastern europe',
 'mongolia':'east asia',
 'cayman-islands':'north america',
 'eritrea':'east africa',
 'namibia':'southern africa',
 'guadeloupe':'caribbean',
 'gibraltar':'north africa',
 'burundi':'east africa',
 'bermuda':'north america',
 'cambodia':'southeast asia',
 'bahamas':'caribbean',
 'monaco':'eastern europe',
 'botswana':'southern africa',
 'bhutan':'south asia',
 'seychelles':'indian ocean islands',
 'antigua-and-barbuda':'caribbean',
 'french-polynesia':'pacific region',
 'china-macao-sar':'east asia',
 'gambia':'west africa',
 'turks-and-caicos-islands':'southern africa',
 'lesotho':'southern africa',
 'belize':'caribbean',
 'curacao':'north america',
 'papua-new-guinea':'pacific region',
 'western-sahara':'west africa',
 'fiji':'pacific region',
 'saint-kitts-and-nevis':'caribbean',
 'saint-lucia':'caribbean',
 'congo':'west africa',
 'trinidad-and-tobago':'caribbean',
 'faeroe-islands':'western europe',
 'Barbados':'caribbean',
 'liechtenstein':'western europe',
 'aruba':'western europe',
 'faeroe-islands':'western europe',
'saint-vincent-and-the-grenadines':'caribbean',
'timor-leste':'pacific region',
'grenada':'caribbean',
'new-caledonia':'pacific region',
'laos':'southeast asia',
'dominica':'caribbean',
'falkland-islands-malvinas':'south america',
'greenland':'north america',
'holy-see':'western europe',
'anguilla':'caribbean',
 }
from tqdm import tqdm
import numpy as np
import pandas as pd
from bs4 import BeautifulSoup
import requests
import json
import time
import random
import html5lib
import re
import scipy.stats as st
from pandas.core.frame import DataFrame
import copy
import math
import os
import datetime
#import datetime
x0=datetime.date.today()
x1=datetime.date.today()-datetime.timedelta(days=1)
x2=datetime.date.today()-datetime.timedelta(days=2)
# run_time
ts=[]
ts.append(x0.__format__('%Y%m%d'))
ts.append(x1.__format__('%Y%m%d'))
ts.append(x2.__format__('%Y%m%d'))
print(ts)
headers = { 'Connection': 'close',}
# proxies={'http':'http://127.0.0.1:10080','https':'http://127.0.0.1:10080'}
url='https://www.worldometers.info/coronavirus/#countries'
# url='https://www.worldometers.info/coronavirus/country/us/'
a=requests.get(url,headers=headers)
soup = BeautifulSoup(a.content,'html5lib')
x=soup.body.find_all('tr', attrs={'style': ['','background-color:#F0F0F0','background-color:#EAF7D5']})
# 190 210
def find_start_yesterday(i,j):
    for start in range(i,j):
        one=x[start]
        two=x[start+1]
        l1=one.find_all('a',attrs={'class':'mt_a'})
        l2=two.find_all('a',attrs={'class':'mt_a'})
        if l1==[] or l2==[]:
            continue
        s1=str(l1[0])
        s2=str(l2[0])
        coun1=s1.split('/')
        coun2=s2.split('/')
        if coun1[1]=='china' and coun2[1]=='us':
            return start
#385 410
# def find_end_yesterday(i,j):
#     for end in range(i,j):
#         final_pre=x[end-1]
#         final=x[end]
#         l1=final_pre.find_all('a',attrs={'class':'mt_a'})
#         l2=final.find_all('a',attrs={'class':'mt_a'})
#         if l1==[] or l2==[]:
#             continue
#         s1=str(l1[0])
#         s2=str(l2[0])
#         coun1=s1.split('/')
#         coun2=s2.split('/')
#         if (coun1[1]=='anguilla' and coun2[1]=='saint-pierre-and-miquelon') or (coun2[1]=='anguilla' and coun1[1]=='saint-pierre-and-miquelon'):
#             return end+1

def find_end_yesterday(i,j):
    for end in range(i,j):
        # final_pre=x[end-1]
        final=x[end]
        # l1=final_pre.find_all('a',attrs={'class':'mt_a'})
        l2=final.find_all('a',attrs={'class':'mt_a'})
        if l2==[]:
            continue
        # s1=str(l1[0])
        s2=str(l2[0])
        # coun1=s1.split('/')
        coun2=s2.split('/')
        if coun2[1]=='anguilla':
            return end+1

end=find_end_yesterday(400,440)
end2=find_end_yesterday(630,700)
start=find_start_yesterday(190,250)
start2=find_start_yesterday(440,470)
print('start:{}\tend:{}\tstart2:{}\tend2:{}'.format(start,end,start2,end2))

col_name=['0','#','Country,Other','TotalCases',
 'NewCases','TotalDeaths','NewDeaths','TotalRecovered',
 'NewRecovered','ActiveCases','Serious,Critical','Tot Cases/1M pop',
 'Deaths/1M pop','TotalTests','Tests/1M pop','Population',
 'Continent','17',' 1 Caseevery X', 'ppl1 Deathevery',' X ppl1 Testevery ','X ppl','22',
 'Cases Per 100K Population','Tests Per 100K Population','Active Cases Per 100k Population','Total Test:Positive Ratio',
 'New Positive%','Case Fatality Rate%','New Confirmed Case Growth Rate','New Death Case Growth Rate','Average daily cases per 100,000 people in the past week',
 'New Test','NPI','Region','key-id','Country/District','7 days inc cases','7 days inc deaths']

raw_data=[]
for i in tqdm(range(start,end)):
    text_source=x[i]
    l=text_source.find_all('a',attrs={'class':'mt_a'})
    if l==[]:
        continue
    s=str(l[0])
    coun=s.split('/')
    try:
        region=cate[coun[1]]
        iso=dict_iso[coun[1]]
    except:
        region='missing'
    url='https://www.worldometers.info/coronavirus/country/'+coun[1]+'/'
    print(coun[1])
    a=''
    while a=='':
        try:
            a=requests.get(url,headers=headers)
        except:
            a=''
    soup = BeautifulSoup(a.content,'html5lib')
    r=soup.body.find_all('script',attrs={'type':'text/javascript'})
    p=re.compile(r'categories: \[(.*?)\]',re.S)
    rs=re.findall(p,r[0].text)
    d=rs[0]
    # d=re.sub(r'\"','',d)
    str_pat = re.compile(r'\"(.*?)\"')
    d = str_pat.findall(d)
    date=d
    p1=re.compile(r'name: \'Cases\'.*?\[(.*?)\]',re.S)
    for j in range(10):
        try:
            rs=re.findall(p1,r[j].text)
            d=rs[0]
            d=re.sub(r'\"','',d)
            case=d.split(',')
        except:
            # print('{} cases is not{}'.format(coun[1],j))    
            continue
    p1=re.compile(r'name: \'Deaths\'.*?\[(.*?)\]',re.S)
    for j in range(10):
        try:
            rs=re.findall(p1,r[j].text)
            d=rs[0]
            d=re.sub(r'\"','',d)
            TD=d.split(',')
        except:
            continue
    j={'Date':date,'Total Cases':case,'Total Deaths':TD}
    # print(j)
    print("Date {} TC {} TD {}".format(len(date),len(case),len(TD)))
    if not len(set([len(date),len(case),len(TD)])) == 1:
        continue
    hist_data_of_coun_i=pd.DataFrame(j)
    hist_data_of_coun_i['Total Deaths'][0]=0
    for k in range(len(hist_data_of_coun_i['Total Deaths'])):
        if hist_data_of_coun_i['Total Deaths'][k]=='null':
            data['Total Deaths'][k]=0
    hist_data_of_coun_i['Total Cases']=hist_data_of_coun_i['Total Cases'].astype(int)
    hist_data_of_coun_i['Total Deaths']=hist_data_of_coun_i['Total Deaths'].astype(int)
    hist_data_of_coun_i['case inc']=hist_data_of_coun_i['Total Cases'].diff()
    hist_data_of_coun_i['death inc']=hist_data_of_coun_i['Total Deaths'].diff() 
    #七日新增死亡与cases
    seven_cases=sum([hist_data_of_coun_i.loc[len(date)-i,'case inc'] for i in range(1,8)])
    seven_deaths=sum([hist_data_of_coun_i.loc[len(date)-i,'death inc'] for i in range(1,8)])
       
    inc1=hist_data_of_coun_i.loc[len(date)-1,'case inc']/(7*hist_data_of_coun_i.loc[len(date)-8,'case inc'])
    inc2=hist_data_of_coun_i.loc[len(date)-1,'death inc']/(7*hist_data_of_coun_i.loc[len(date)-8,'death inc'])
    
    inc_1=sum([hist_data_of_coun_i.loc[len(date)-i,'case inc'] for i in range(1,8)])/sum([hist_data_of_coun_i.loc[len(date)-i,'case inc'] for i in range(8,15)])
    inc_2=sum([hist_data_of_coun_i.loc[len(date)-i,'death inc'] for i in range(1,8)])/sum([hist_data_of_coun_i.loc[len(date)-i,'death inc'] for i in range(8,15)])
    adcp=sum([hist_data_of_coun_i.loc[len(date)-i,'case inc'] for i in range(1,8)])/7
    p=1
    while inc1 ==0 and hist_data_of_coun_i.loc[len(date)-1,'Total Cases']>=10000:
        p+=1
        inc1=hist_data_of_coun_i.loc[len(date)-p,'case inc']/(7*hist_data_of_coun_i.loc[len(date)-1-p,'case inc'])
    dd=hist_data_of_coun_i.shift(5)
    hist_data_of_coun_i['inc_p']=np.log(hist_data_of_coun_i['case inc']/dd['case inc'])/5
    hist_data_of_coun_i=hist_data_of_coun_i[~hist_data_of_coun_i.isin([np.nan, np.inf, -np.inf]).any(1)]
    da=hist_data_of_coun_i['inc_p'].values
    try:
        slope,intercept, r_value, p_value, std_err=st.linregress(list(range(30)), da[:30])
    except:
        slope=None

    # print(x[i])
    bo=x[i].text.split('\n')
    # print(bo)
    if bo[6]=='' and bo[7]=='':
        del bo[7]
    if bo[17]=='' and bo[18]=='':
        del bo[18]
    for o in range(start2,end2):
        s1=x[o]
        l1=s1.find_all('a',attrs={'class':'mt_a'})
        if l1==[]:
            continue
        s1=str(l1[0])
        coun1=s1.split('/')
        if coun1[1]==coun[1]:
            bo1=x[o].text.split('\n')
            break
    for h in range(len(bo)):
        bo[h]=bo[h].replace(',','')
        bo[h]=bo[h].replace('+','')
    for h in range(len(bo1)):
        bo1[h]=bo1[h].replace(',','')
        bo1[h]=bo1[h].replace('+','')
    #Cases Per 100K Population
    try:
        bo.append(100000*int(bo[3])/int(bo[15]))
    except:
        print(coun[1])
        continue
        # bo.append(np.nan)
        # print('lack one')
    #Tests Per 100K Population
    try: 
        bo.append(100000*int(bo[13])/int(bo[15]))
    except:
        print(coun[1])
        continue
        # bo.append(np.nan)
        # print('lack one')   
    #'Active Cases Per 100k Population'
    try:
        bo.append(int(bo[9])*100000/int(bo[15]))
    except:
        bo.append(np.nan)
        # print('lack one')
    
    #Total Test:Positive Ratio      
    bo.append(int(bo[3])/int(bo[13]))
    #New Positive
    try:        
        bo.append((int(bo[3])-int(bo1[3]))/(int(bo[13])-int(bo1[13])))
    except:
        bo.append(np.nan)
        # print('lack one')
    #Case Fatality Rate%  
    try:
        if bo[5]=='': 
            bo.append(0)
        else:
            bo.append(int(bo[5])/int(bo[3]))
    except:
        bo.append(np.nan)
    #New Confirmed Case Growth Rate
#     try:
#         q=2
#         while (math.isnan(inc1) or inc1==np.inf) and q<=9:
# #             print(inc1)
#             inc1=hist_data_of_coun_i.loc[len(date)-q,'case inc']/(7*hist_data_of_coun_i.loc[len(date)-q-7,'case inc'])  
#             c=hist_data_of_coun_i.loc[len(date)-q,'case inc']
#             q+=1
# #         print(inc1)
#         if math.isnan(inc1):
#             bo.append(0)
#         elif inc1==np.inf:
#             bo.append(0.01)
#     #    elif c<=100:
#     #        bo.append(0.03)
#         else:
#             bo.append(inc1)
#     except:
#         bo.append(0)
        # print('lack one')
    #New Sum Confirmed Case Growth Rate
    if math.isnan(inc_1) or inc_1=='':
        bo.append(0)
    elif inc_1==np.inf:
        bo.append(0.01)
    else:
        bo.append(inc_1)
    print(bo[-1])
    #New Death Case Growth Rate   
#     try:
#         q=2
#         while (math.isnan(inc2) or inc2==np.inf) and q<=9:
# #             print(inc2)
#             inc2=hist_data_of_coun_i.loc[len(date)-q,'death inc']/(7*hist_data_of_coun_i.loc[len(date)-q-7,'death inc'])
#             q+=1
# #         print(inc2)
#         if math.isnan(inc2):
#             bo.append(0)
#         elif inc2==np.inf:
#             bo.append(0.1)
#         else:
#             bo.append(inc2)
#     except:
#         bo.append(0)
        # print('lack one')
    #New Sum Death Case Growth Rate
    if math.isnan(inc_2) or inc_2=='':
        bo.append(0)
    elif inc_2==np.inf:
        bo.append(0.1)
    else:
        bo.append(inc_2)
    print(bo[-1])

    #Average daily cases per 100,000 people in the past week
    bo.append(adcp*100000/int(bo[15]))

    # New Test
    try:
        bo.append(int(bo[13])-int(bo1[13]))
    except:
        bo.append(np.nan)
        # print('lack one')
    
    bo.append(slope)
    if region=='missing':
        continue
    else:
        bo.append(region)
    bo.append(coun1[1])
    bo.append(iso)
    bo.append(seven_cases)
    bo.append(seven_deaths)
    print(len(bo))
    print(bo)
    if len(bo)!=39:
        os.exit(-1)
    raw_data.append(bo)
raw_data=DataFrame(raw_data,columns=col_name)
brief_raw_data=raw_data[['Country,Other','key-id','Region','Country/District','Population',
'TotalCases','ActiveCases','TotalDeaths','NewDeaths','TotalRecovered','NewRecovered','Serious,Critical','NewCases','New Test','Cases Per 100K Population','Tests Per 100K Population',
'Active Cases Per 100k Population','Total Test:Positive Ratio','New Positive%',
          'Case Fatality Rate%','New Confirmed Case Growth Rate','New Death Case Growth Rate','Average daily cases per 100,000 people in the past week','NPI','7 days inc cases','7 days inc deaths']]
brief_raw_data['week death rate']=brief_raw_data['7 days inc deaths']/brief_raw_data['7 days inc cases']
tf=copy.deepcopy(brief_raw_data)
uni_region=list(set(list(tf['Region'].values)))
    
uni_region.remove('western europe')
data_region=tf[tf['Region']=='western europe']
data_region=data_region.replace(np.nan,'shit')
data_region=data_region.replace(np.inf,'shit')
data_region=data_region.replace('N/A','shit')
data_region=data_region.replace('',0)
data_region=data_region.replace(' ',0)
data_region.loc[data_region['NPI']=='shit','NPI']=0
data_region.loc[data_region['Case Fatality Rate%']=='shit','Case Fatality Rate%']=0
dd=data_region[['TotalCases','ActiveCases','TotalRecovered','Case Fatality Rate%']]
ac=dd[(dd['TotalCases']!='shit')&(dd['ActiveCases']!='shit')&(dd['TotalRecovered']!='shit')&(dd['Case Fatality Rate%']!='shit')]
active_rate_region=sum(ac['ActiveCases'].astype(int))/sum(ac['TotalCases'].astype(int))
data_region.loc[data_region['Active Cases Per 100k Population']=='shit','Active Cases Per 100k Population']=active_rate_region*100000*data_region.loc[data_region['Active Cases Per 100k Population']=='shit','TotalCases'].astype(int)/data_region.loc[data_region['Active Cases Per 100k Population']=='shit','Population'].astype(int)

dd=data_region[['NewCases','New Test']]
ac=dd[dd['New Test']!=0]
new_posi=sum(ac['NewCases'].astype(int))/sum(ac['New Test'])
data_region.loc[data_region['New Test']==0,'New Positive%']=new_posi
final=copy.deepcopy(data_region)
for distri in uni_region:   
    data_region=tf[tf['Region']==distri]
    data_region=data_region.replace(np.nan,'shit')
    data_region=data_region.replace(np.inf,'shit')
    data_region=data_region.replace('N/A','shit')
    data_region=data_region.replace('',0)
    data_region=data_region.replace(' ',0)
    data_region.loc[data_region['NPI']=='shit','NPI']=0
    data_region.loc[data_region['Case Fatality Rate%']=='shit','Case Fatality Rate%']=0
    dd=data_region[['TotalCases','ActiveCases','TotalRecovered','Case Fatality Rate%']]
    ac=dd[(dd['TotalCases']!='shit')&(dd['ActiveCases']!='shit')&(dd['TotalRecovered']!='shit')&(dd['Case Fatality Rate%']!='shit')]
    active_rate_region=sum(ac['ActiveCases'].astype(int))/sum(ac['TotalCases'].astype(int))
    data_region.loc[data_region['Active Cases Per 100k Population']=='shit','Active Cases Per 100k Population']=active_rate_region*100000*data_region.loc[data_region['Active Cases Per 100k Population']=='shit','TotalCases'].astype(int)/data_region.loc[data_region['Active Cases Per 100k Population']=='shit','Population'].astype(int)

    dd=data_region[['NewCases','New Test']]
    ac=dd[dd['New Test']!=0]
    try:
        new_posi=sum(ac['NewCases'].astype(int))/sum(ac['New Test'])
    except:
        new_posi=0
    data_region.loc[data_region['New Test']==0,'New Positive%']=new_posi
    data_region.loc[data_region['New Test']=='shit','New Positive%']=new_posi
    final=pd.concat([final,data_region])
final=final.reset_index(drop=True)
tf2=final[['Country,Other','key-id','Country/District','Region','TotalCases','Cases Per 100K Population','Tests Per 100K Population',
'Active Cases Per 100k Population','Total Test:Positive Ratio','New Positive%',
          'Case Fatality Rate%','New Confirmed Case Growth Rate','New Death Case Growth Rate','Average daily cases per 100,000 people in the past week','NPI']]

#越高越好，即需要降序
# for x in ['Cases Per 100K Population','Active Cases Per 100k Population','Total Test:Positive Ratio','New Positive%',]
x='Tests Per 100K Population'
df=copy.deepcopy(tf2[['Country,Other',x]])
df2=df.sort_values(x,ascending=False,inplace=False)
df2 = df2.reset_index(drop=True)
df2['cum']=df.index+1
df2['cum_prob']=100*df2['cum']/max(df2['cum'])
df3=pd.merge(df,df2,on=['Country,Other'])
# tf2['IND_'+x]=df3['cum_prob']
tf2['IND_'+x]=0
for h in list(tf2['Country,Other'].values):
    tf2.loc[tf2['Country,Other']==h,'IND_'+x]=df3.loc[df3['Country,Other']==h,'cum_prob'].values[0]



for x in ['Cases Per 100K Population','Active Cases Per 100k Population','Total Test:Positive Ratio','New Positive%','Case Fatality Rate%','New Confirmed Case Growth Rate','New Death Case Growth Rate','Average daily cases per 100,000 people in the past week','NPI']:
    i=1
    df=copy.deepcopy(tf2[['Country,Other',x]])
    print(x)
    df2=df.sort_values(x,inplace=False)
    df2 = df2.reset_index(drop=True)
    df2['cum']=df.index+1
    df2['cum_prob']=100*df2['cum']/max(df2['cum'])
    df3=pd.merge(df,df2,on=['Country,Other'])
    tf2['IND_'+x]=0
    for h in list(tf2['Country,Other'].values):
        tf2.loc[tf2['Country,Other']==h,'IND_'+x]=df3.loc[df3['Country,Other']==h,'cum_prob'].values[0]
    i+=1

tf2['Comprehensive Index']=0.15*tf2['IND_Cases Per 100K Population']+0.08*tf2['IND_Tests Per 100K Population']+0.2*tf2['IND_Active Cases Per 100k Population']+0.1*tf2['IND_Total Test:Positive Ratio']+0.13*tf2['IND_New Positive%']+0.05*tf2['IND_Case Fatality Rate%']+ 0.22*tf2['IND_New Confirmed Case Growth Rate']+0.07*tf2['IND_New Death Case Growth Rate']
today=datetime.datetime.now()
tf4=tf2[['Country/District','TotalCases','IND_Cases Per 100K Population','IND_Tests Per 100K Population','IND_Total Test:Positive Ratio',
'IND_New Positive%','IND_Case Fatality Rate%','IND_New Confirmed Case Growth Rate','IND_New Death Case Growth Rate','IND_Active Cases Per 100k Population',
'IND_NPI','IND_Average daily cases per 100,000 people in the past week','Comprehensive Index']]
tf_c=copy.deepcopy(tf4)
tf_c_rename=tf_c.rename({'TotalCases':'TOTAL CASE','IND_Cases Per 100K Population':'IND1_Cases Per 100K Population','IND_Tests Per 100K Population':'IND2_Tests Per 100K Population',
'IND_Active Cases Per 100k Population':'IND8_Active Cases Per 100k Population','IND_Total Test:Positive Ratio':'IND3_Total Test:Positive Ratio',
'IND_New Positive%':'IND4_New Positive%','IND_Case Fatality Rate%':'IND5_Case Fatality Rate%','IND_New Confirmed Case Growth Rate':'IND6_New Confirmed Case Growth Rate',
'IND_New Death Case Growth Rate':'IND7_New Death Case Growth Rate','IND_NPI':'NPI'},axis='columns')
tf_c_rename.to_excel('World_index_{}.xlsx'.format(today),sheet_name=ts[1],index=False)
tf2.to_excel('World_raw_index_{}.xlsx'.format(today),sheet_name=ts[1],index=False)

url='https://raw.githubusercontent.com/owid/covid-19-data/master/public/data/vaccinations/vaccinations.csv'
a=requests.get(url,headers=headers)
with open("vacc.csv",'wb') as f:
    f.write(a.content)
vacc = pd.read_csv('vacc.csv',keep_default_na=False)
ct = list(dict(vacc['location'].value_counts()).keys())
for x in ['total_vaccinations','people_vaccinated','people_fully_vaccinated','total_boosters']:
    vacc[x]=vacc[x].replace('',0)
    vacc[x]=vacc[x].astype(float)
    vacc[x]=vacc[x].astype(int)
img = dict()
for i in ct:
    dt = vacc[vacc['location']==i]
    d=[]
    for x in ['total_vaccinations','people_vaccinated','people_fully_vaccinated','total_boosters']:
        d.append(max(dt[x]))
    img[i]=d
brief_raw_data['total_vaccinations']=0
brief_raw_data['people_vaccinated']=0
brief_raw_data['people_fully_vaccinated']=0
brief_raw_data['total_boosters']=0
for i in img.keys():
    brief_raw_data.loc[((brief_raw_data['Country,Other']==i)|(brief_raw_data['Country/District']==i)),'total_vaccinations'] = int(img[i][0])
    brief_raw_data.loc[((brief_raw_data['Country,Other']==i)|(brief_raw_data['Country/District']==i)),'people_vaccinated'] = int(img[i][1])
    brief_raw_data.loc[((brief_raw_data['Country,Other']==i)|(brief_raw_data['Country/District']==i)),'people_fully_vaccinated'] = int(img[i][2])
    brief_raw_data.loc[((brief_raw_data['Country,Other']==i)|(brief_raw_data['Country/District']==i)),'total_boosters'] = int(img[i][3])
    
brief_raw_data['Population']=brief_raw_data['Population'].astype(int)
brief_raw_data['vacc_per_100']=brief_raw_data['total_vaccinations']*100/brief_raw_data['Population']
brief_raw_data['cases_per_100']=brief_raw_data['Cases Per 100K Population']/1000
brief_raw_data['total_immune']=brief_raw_data['cases_per_100']+brief_raw_data['vacc_per_100']*0.9   

def infer_vaccinated(one_row):
    if one_row['total_vaccinations']!=0 and one_row['people_vaccinated']==0:
        one_row['people_vaccinated']=int(0.7018*one_row['total_vaccinations'])
    if one_row['total_vaccinations']!=0 and one_row['people_fully_vaccinated']==0:
        one_row['people_fully_vaccinated']=int(0.2936*one_row['total_vaccinations'])
    return one_row
brief_raw_data = brief_raw_data.apply(infer_vaccinated,axis=1)

import datetime
from datetime import timedelta
import sys
vacc = pd.read_csv('vacc.csv',keep_default_na=False)
ct = list(dict(vacc['location'].value_counts()).keys())
vacc_daily=copy.deepcopy(vacc)
vacc_daily['daily_vaccinations']=vacc_daily['daily_vaccinations'].replace('',-1)
vacc_daily['daily_vaccinations']=vacc_daily['daily_vaccinations'].astype(int)
vacc_daily = vacc_daily.drop(vacc_daily[vacc_daily['daily_vaccinations']==-1].index)
vacc_daily['date']=pd.to_datetime(vacc_daily['date']).dt.date
daily=dict()
# vacc_daily.loc[vacc_daily['date']==,'daily_vaccinations']
for i in ct:
    rdd=[]
    dt = vacc_daily[vacc_daily['location']==i]
    today=datetime.date.today()
    try:
        a=max(dt['date'])
    #     print(a)
    #     print(today)
    #     print(today-timedelta(14))
#         if today-timedelta(14)<a:
        x=dt.loc[(dt['date']<=today)&(dt['date']>=today-timedelta(14)),'daily_vaccinations']
        note = sum(x)/len(x)
#         else:
#             note = -2
    except:
        note =-2
    daily[i]=int(note)
for x in ['total_vaccinations','people_vaccinated','people_fully_vaccinated','total_boosters']:
    vacc[x]=vacc[x].replace('',-1)
    vacc[x]=vacc[x].astype(float)
    vacc[x]=vacc[x].astype(int)
vacc = vacc.drop(vacc[vacc['total_vaccinations']==-1].index)
vacc['date']=pd.to_datetime(vacc['date'])
rd=dict()
for i in ct:
    rdd=[]
    dt = vacc[vacc['location']==i]
    a=max(dt['date'])
    b=min(dt['date'])
    c=a-b
    Vacc_Days=int(c.days)
    if Vacc_Days==0:
        Vacc_Days=1
    Total_Vacc=int(max(dt['total_vaccinations']))
    Avg_Vac_Daily=Total_Vacc/Vacc_Days
    d=a-timedelta(14)
    if len(list(dt['date']))==1:
        Avg_Vac_Last14D=Total_Vacc
    elif daily[i]==-2:
        Avg_Vac_Last14D=Total_Vacc/Vacc_Days
    else:
        Avg_Vac_Last14D=daily[i]
    rdd=[Vacc_Days,Total_Vacc,Avg_Vac_Daily,Avg_Vac_Last14D]
    rd[i]=rdd        
    

# lst14days=pd.DataFrame(rd,columns=['Country','Vac_Days','Total_Vac','Avg_Vac_Daily','Avg_Vac_Last14D'])
brief_raw_data['累计感染率（%）']=brief_raw_data['Cases Per 100K Population']/100000
brief_raw_data['接种率=（接种疫苗数/总人口）']=brief_raw_data['vacc_per_100']/100
brief_raw_data['VacSpeed_Last14D']=0.05
brief_raw_data['Population']=brief_raw_data['Population'].astype(int)

brief_raw_data['fully vaccinated %']=100*brief_raw_data['people_fully_vaccinated']/brief_raw_data['Population']
brief_raw_data['at least 1 dose vaccinated %']=100*brief_raw_data['people_vaccinated']/brief_raw_data['Population']
brief_raw_data['Fully Vac Ratio %']=100*brief_raw_data['fully vaccinated %']/brief_raw_data['at least 1 dose vaccinated %']
# brief_raw_data['加强针接种率']=brief_raw_data['total_boosters']/brief_raw_data['Population']

for i in rd.keys():
    brief_raw_data.loc[((brief_raw_data['Country,Other']==i)|(brief_raw_data['Country/District']==i)),'VacSpeed_Last14D'] = 100*int(rd[i][3])/brief_raw_data.loc[((brief_raw_data['Country,Other']==i)|(brief_raw_data['Country/District']==i)),'Population']
vaccine_efficacy = pd.read_excel('Vaccine-Country.xlsx',sheet_name='Efficacy')
vaccine_efficacy = vaccine_efficacy.set_index('Vaccine').T.to_dict('list')
vaccine_map=pd.read_excel('Vaccine-Country.xlsx',sheet_name='Vac-Mapping')
vaccine_map = dict(zip(vaccine_map['Location'],vaccine_map['首选疫苗']))
brief_raw_data['Vaccine1']='NA'
brief_raw_data['Vaccine_Efficacy']=0.8
for i in vaccine_map.keys():
    brief_raw_data.loc[((brief_raw_data['Country,Other']==i)|(brief_raw_data['Country/District']==i)),'Vaccine1'] = vaccine_map[i]
    brief_raw_data.loc[((brief_raw_data['Country,Other']==i)|(brief_raw_data['Country/District']==i)),'Vaccine_Efficacy'] = vaccine_efficacy[vaccine_map[i]][1]

brief_raw_data['total_immune_adj']=brief_raw_data['累计感染率（%）']*100+brief_raw_data['vacc_per_100']*brief_raw_data['Vaccine_Efficacy']/2
brief_raw_data['DaystoReachHerdImmunity']=(0.7-brief_raw_data['累计感染率（%）']-brief_raw_data['接种率=（接种疫苗数/总人口）']*brief_raw_data['Vaccine_Efficacy']/2)/(brief_raw_data['VacSpeed_Last14D']*brief_raw_data['Vaccine_Efficacy']*0.01/2)

import datetime
def change_time(x):
    in_date = ts[1]
    # print(x)
    if x<0:
        out_date = 'already'
        return out_date
    elif math.isinf(x):
        out_date = 'never'
        return out_date
    dt = datetime.datetime.strptime(in_date, "%Y%m%d")
    try:
        out_date = (dt + datetime.timedelta(days=int(x))).strftime("%Y-%m-%d")
    except:
        out_date = 'never'
    return out_date

# brief_raw_data.to_excel('World_rawdata_test.xlsx')
brief_raw_data['HerdImmunityDate']=brief_raw_data['DaystoReachHerdImmunity'].map(change_time)
brief_raw_data['Total Boosters']=brief_raw_data['total_boosters']
brief_raw_data['加强针接种率']=brief_raw_data['total_boosters']/brief_raw_data['Population']
brief_raw_data.to_excel('World_rawdata_{}.xlsx'.format(today),sheet_name=ts[1],index=False)

uni_region=list(set(list(brief_raw_data['Region'].values)))
writer = pd.ExcelWriter('Region_World_Rawdata_{}.xlsx'.format(today))
for distri in uni_region:   
    data_region=brief_raw_data[brief_raw_data['Region']==distri]
    data_region.to_excel(writer,sheet_name=distri,index=False)
writer.save()
#import datetime # run_time # proxies={'http':'http://127.0.0.1:10080','https':'http://127.0.0.1:10080'} #countries' # url='https://www.worldometers.info/coronavirus/country/us/' #F0F0F0','background-color:#EAF7D5']}) # 190 210 #385 410 # def find_end_yesterday(i,j): #     for end in range(i,j): #         final_pre=x[end-1] #         final=x[end] #         l1=final_pre.find_all('a',attrs={'class':'mt_a'}) #         l2=final.find_all('a',attrs={'class':'mt_a'}) #         if l1==[] or l2==[]: #             continue #         s1=str(l1[0]) #         s2=str(l2[0]) #         coun1=s1.split('/') #         coun2=s2.split('/') #         if (coun1[1]=='anguilla' and coun2[1]=='saint-pierre-and-miquelon') or (coun2[1]=='anguilla' and coun1[1]=='saint-pierre-and-miquelon'): #             return end+1 # final_pre=x[end-1] # l1=final_pre.find_all('a',attrs={'class':'mt_a'}) # s1=str(l1[0]) # coun1=s1.split('/') # d=re.sub(r'\"','',d) # print('{} cases is not{}'.format(coun[1],j)) # print(j) #七日新增死亡与cases # print(x[i]) # print(bo) #Cases Per 100K Population # bo.append(np.nan) # print('lack one') #Tests Per 100K Population # bo.append(np.nan) # print('lack one') #'Active Cases Per 100k Population' # print('lack one') #Total Test:Positive Ratio #New Positive # print('lack one') #Case Fatality Rate% #New Confirmed Case Growth Rate #     try: #         q=2 #         while (math.isnan(inc1) or inc1==np.inf) and q<=9: # #             print(inc1) #             inc1=hist_data_of_coun_i.loc[len(date)-q,'case inc']/(7*hist_data_of_coun_i.loc[len(date)-q-7,'case inc']) #             c=hist_data_of_coun_i.loc[len(date)-q,'case inc'] #             q+=1 # #         print(inc1) #         if math.isnan(inc1): #             bo.append(0) #         elif inc1==np.inf: #             bo.append(0.01) #     #    elif c<=100: #     #        bo.append(0.03) #         else: #             bo.append(inc1) #     except: #         bo.append(0) # print('lack one') #New Sum Confirmed Case Growth Rate #New Death Case Growth Rate #     try: #         q=2 #         while (math.isnan(inc2) or inc2==np.inf) and q<=9: # #             print(inc2) #             inc2=hist_data_of_coun_i.loc[len(date)-q,'death inc']/(7*hist_data_of_coun_i.loc[len(date)-q-7,'death inc']) #             q+=1 # #         print(inc2) #         if math.isnan(inc2): #             bo.append(0) #         elif inc2==np.inf: #             bo.append(0.1) #         else: #             bo.append(inc2) #     except: #         bo.append(0) # print('lack one') #New Sum Death Case Growth Rate #Average daily cases per 100,000 people in the past week # New Test # print('lack one') #越高越好，即需要降序 # for x in ['Cases Per 100K Population','Active Cases Per 100k Population','Total Test:Positive Ratio','New Positive%',] # tf2['IND_'+x]=df3['cum_prob'] # vacc_daily.loc[vacc_daily['date']==,'daily_vaccinations'] #     print(a) #     print(today) #     print(today-timedelta(14)) #         if today-timedelta(14)<a: #         else: #             note = -2 # lst14days=pd.DataFrame(rd,columns=['Country','Vac_Days','Total_Vac','Avg_Vac_Daily','Avg_Vac_Last14D']) # brief_raw_data['加强针接种率']=brief_raw_data['total_boosters']/brief_raw_data['Population'] # print(x) # brief_raw_data.to_excel('World_rawdata_test.xlsx')