<jupyter_start><jupyter_text>Data Wrangling<jupyter_code>ds_1=pd.read_excel('3401055004_OTSP_CountryofCitizenship_Jun21.xlsx', sheet_name='Table 1.1', header=[7])
ds_1=ds_1.drop(ds_1.index[[250,251,252,253,254,255,256,257,258]])
ds_1[datetime.datetime(2020, 11, 1, 0, 0)] = ds_1[datetime.datetime(2020, 11, 1, 0, 0)].astype(float, errors = 'raise')
ds_1
ds_1.info()
ds_1=ds_1.drop(['SACC code(a)','Country of citizenship(a)'],axis=1)
ds_1.isin([0]).all(axis = 1).sum()
# profile = ProfileReport(ds_1,title="Pandas Profiling Report")
# profile
# profile.to_file("C:\\Users\\Moditha\\Desktop\\your_report.html")<jupyter_output><empty_output><jupyter_text>Save to CSV<jupyter_code>ds_1.to_csv("Arrivals_Data.csv", index=False)<jupyter_output><empty_output><jupyter_text>Save to JSON<jupyter_code>ds_1.to_json("Arrivals_Data.json",orient='records')<jupyter_output><empty_output><jupyter_text>Number of Rows and Columns<jupyter_code>ds_1.shape<jupyter_output><empty_output><jupyter_text>Create New Dataframe<jupyter_code>df=pd.DataFrame({'Field_Name':ds_1.columns.to_numpy(),'Pandas Data Type':ds_1.dtypes.to_numpy(),'Unique Values':ds_1.nunique().to_numpy(),
                'Missing Value Count':ds_1.isnull().sum().to_numpy()})
df
df1=pd.DataFrame({'Min Value':ds_1.min().to_numpy(),'Max Value':ds_1.max().to_numpy()})
df1.index=df1.index+1
df1
final_df = pd.concat([df, df1], axis=1, join="outer")
final_df=final_df[['Field_Name','Pandas Data Type','Min Value','Max Value','Unique Values','Missing Value Count']]
final_df<jupyter_output><empty_output><jupyter_text>Graphs<jupyter_code>ds_1=pd.read_excel('3401055004_OTSP_CountryofCitizenship_Jun21.xlsx', sheet_name='Table 1.1', header=[7])
ds_1=ds_1.drop(ds_1.index[[250,251,252,253,254,255,256,257,258]])
ds_1[datetime.datetime(2020, 11, 1, 0, 0)] = ds_1[datetime.datetime(2020, 11, 1, 0, 0)].astype(float, errors = 'raise')
ds_1.drop(ds_1[ds_1[datetime.datetime(2016, 9, 1, 0, 0)] <= 10000].index, inplace = True)
ds_1=ds_1.drop(ds_1.index[[0]])#outlier
ds_1<jupyter_output><empty_output><jupyter_text>Scatter Plot<jupyter_code>fig = px.scatter(ds_1, x='Country of citizenship(a)', y=datetime.datetime(2016, 9, 1, 0, 0))
fig['layout'].update(title='Departures Count on 2016-Sep Vs Country of citizenship', width=1500, height=1500, autosize=False)
fig['layout']['yaxis'].update(title='Arrival Count on 2016-Sep')
fig.show()
fig = px.scatter(ds_1, x=datetime.datetime(2016, 9, 1, 0, 0), y=datetime.datetime(2017, 9, 1, 0, 0))
fig['layout'].update(title='Arrival Count on 2016-Sep Vs 2017-Sep')
fig['layout']['xaxis'].update(title='Arrival Count on 2016-Sep')
fig['layout']['yaxis'].update(title='Arrival Count on 2017-Sep')
fig.show()<jupyter_output><empty_output><jupyter_text>Bar Graph<jupyter_code>ds_1=ds_1.nlargest(10, [datetime.datetime(2016, 9, 1, 0, 0)])
fig = px.bar(ds_1, x='Country of citizenship(a)', y=datetime.datetime(2016, 9, 1, 0, 0),height=2000)
fig['layout'].update(title='Arrival Count on 2016-Sep Vs Country of citizenship', autosize=True)
fig['layout']['yaxis'].update(title='Arrival Count on 2016-Sep')
fig['layout']['xaxis'].update(title='Country of citizenship(a)')
fig.show()<jupyter_output><empty_output><jupyter_text>Pie Graph<jupyter_code>fig = px.pie(ds_1,values=datetime.datetime(2016, 9, 1, 0, 0),names='Country of citizenship(a)',height=2000)
fig['layout'].update(title='Arrival Count on 2016-Sep Vs Country of citizenship', autosize=True)
fig.show()<jupyter_output><empty_output><jupyter_text>Bubble Graph<jupyter_code>fig = px.scatter(ds_1, x=datetime.datetime(2016, 9, 1, 0, 0), y=datetime.datetime(2017, 9, 1, 0, 0),
                 color='Country of citizenship(a)',
                    hover_data=['Country of citizenship(a)'])

fig['layout']['yaxis'].update(title='Arrival Count on 2017-Sep')
fig['layout']['xaxis'].update(title='Arrival Count on 2016-Sep')
  
fig.show()<jupyter_output><empty_output><jupyter_text>Histogram<jupyter_code>ds_1=pd.read_excel('3401055004_OTSP_CountryofCitizenship_Jun21.xlsx', sheet_name='Table 1.1', header=[7])
ds_1=ds_1.drop(ds_1.index[[250,251,252,253,254,255,256,257,258]])
ds_1[datetime.datetime(2020, 11, 1, 0, 0)] = ds_1[datetime.datetime(2020, 11, 1, 0, 0)].astype(float, errors = 'raise')
ds_1.drop(ds_1[ds_1[datetime.datetime(2016, 9, 1, 0, 0)]>=1000].index, inplace = True)
ds_1=ds_1.drop(ds_1.index[[0]])#outlier
ds_1
fig = px.histogram(ds_1, x=datetime.datetime(2016, 9, 1, 0, 0))
fig['layout'].update(title='No of Country vs Arrival Count on 2016-Sep ')
fig['layout']['yaxis'].update(title='No of countries')
fig['layout']['xaxis'].update(title='Arrival Count on 2016-Sep less than 1000')
fig.show()<jupyter_output><empty_output><jupyter_text>Correlation Matrics<jupyter_code>ds_1=pd.read_excel('3401055004_OTSP_CountryofCitizenship_Jun21.xlsx', sheet_name='Table 1.1', header=[7])
ds_1=ds_1.drop(ds_1.index[[250,251,252,253,254,255,256,257,258]])
ds_1[datetime.datetime(2020, 11, 1, 0, 0)] = ds_1[datetime.datetime(2020, 11, 1, 0, 0)].astype(float, errors = 'raise')
ds_1<jupyter_output><empty_output><jupyter_text>Spearman Correlation Matrics<jupyter_code>correlation_matrix_1=ds_1.corr(method ='spearman')
correlation_matrix_1<jupyter_output><empty_output><jupyter_text>Pearson Correlation Matrics<jupyter_code>correlation_matrix_2=ds_1.corr(method ='pearson')
correlation_matrix_2<jupyter_output><empty_output><jupyter_text>Heat Map Using Plotly<jupyter_code>ds_1.drop(ds_1.iloc[:, 12:59], inplace = True, axis = 1)
cor=ds_1.corr(method ='spearman')

z = cor.values.tolist()
z_text = np.around(z,decimals=3)
x = cor.columns.tolist()


fig = ff.create_annotated_heatmap(z,x=x,y=x,annotation_text=z_text,colorscale='Viridis',showscale=True,
                                  hovertemplate="",colorbar_tickfont_size=10)
fig.update_layout(
    width = 500,
    height =500,)

fig['layout']['font']['size'] = 8
# fig['layout']['xaxis'].rangebreaks[True]
fig['layout'].update(title='Correlation Heatmap')
# fig.layout.xaxis.rangebreaks[True]
    
fig.show()
ds_1.drop(ds_1.iloc[:, 12:59], inplace = True, axis = 1)
cor=ds_1.corr(method ='pearson')

z = cor.values.tolist()
z_text = np.around(z,decimals=3)
x = cor.columns.tolist()

fig = ff.create_annotated_heatmap(z,x=x,y=x,annotation_text=z_text,colorscale='Viridis',showscale=True,
                                  hovertemplate="",colorbar_tickfont_size=10)
fig.update_layout(
    width = 500,
    height = 500,)

fig['layout']['font']['size'] =8
fig['layout'].update(title='Correlation Heatmap')
    
fig.show()<jupyter_output><empty_output><jupyter_text>Folium Maps<jupyter_code>import folium
from geopy.geocoders import Nominatim
from folium.plugins import MarkerCluster
ds_1=pd.read_excel('3401055004_OTSP_CountryofCitizenship_Jun21.xlsx', sheet_name='Table 1.1', header=[7])
ds_1=ds_1.drop(ds_1.index[[250,251,252,253,254,255,256,257,258,119,126,34]])
ds_1[datetime.datetime(2020, 11, 1, 0, 0)] = ds_1[datetime.datetime(2020, 11, 1, 0, 0)].astype(float, errors = 'raise')
ds_1=ds_1.nlargest(10, [datetime.datetime(2016, 9, 1, 0, 0)])
ds_1
geolocator=Nominatim(user_agent='Carolingian')
places=ds_1['Country of citizenship(a)'].tolist()
lat=[]
lon=[]
for i in places:
    place = geolocator.geocode(i)
    if hasattr(place,'latitude') and (place.latitude is not None): 
        x= place.latitude
        lat.append(x)
for i in places:
    place = geolocator.geocode(i)
    if hasattr(place,'longitude') and (place.longitude is not None): 
        y=place.longitude
        lon.append(y)

new=pd.DataFrame({'Country':ds_1['Country of citizenship(a)'],'Latitude':lat,'Longitude':lon,'2016-September Arrivals':ds_1[datetime.datetime(2016, 9, 1, 0, 0)]})
new

world_map= folium.Map(tiles="cartodbpositron")
marker_cluster = MarkerCluster().add_to(world_map)
#for each coordinate, create circlemarker of user percent
for i in range(len(new)):
        lat = new.iloc[i]['Latitude']
        long = new.iloc[i]['Longitude']
        radius = 5
        popup_text = """2016-September Arrivals : {}"""
        popup_text = popup_text.format(new.iloc[i]['2016-September Arrivals'])
        folium.CircleMarker(location = [lat, long], radius=radius, popup= popup_text, fill =True).add_to(marker_cluster)
#show the map
world_map<jupyter_output><empty_output>
StarcoderdataJupyterStructuredCleanDedup