Overview
I have attached the complete assignment instructions with the general strategy of how to complete it and the most common error made by students however, in short, I need to construct a Jupyter notebook that reads in data from the supplied Excel file, then performs the necessary data manipulations to obtain two Pandas DataFrames. I couldn't attach the Excel file because the system is not allowing me to since it's over 20MB or the Jupyter Notebook file however I did supply screenshots of them. If there is another way I can send them to you I would be happy to do that.
General Strategy:
- Read the data into a series of dataframes
- Filter the data so you only have the data from the 27 capital cities
- Create a days column that counts from 0 to 150 (151 total rows because Python starts at 0)
- Create days_sq and days_cube columns by squaring and cubing the days column
- Create pop (the city’s population), pop_sq and pop_density
- Create temp by filtering the temp df by the data from the 27 capital cities and merging it
Most Common Error:
A huge amount of effort is spent on trying to match the days to the cities and all sorts of complexities. This is where knowing the dataset comes in handy. Knowing that the data is already sorted by date means you can just create a list using list(range()) and then multiply it by 27 to produce a list that has the correct count before adding it to your df.
Below is as far as I got which was importing the data. If there is a better way to name these variables I am all for it. I would greatly appreciate your help. Thank you in advance.
# Common Imports
import pandas as pd
import numpy as np
# Insert as many cells as you need, but be sure your code is very neat and very well documented# Importing from Excel File
fileName = "BrazilCOVIDData.xlsx"
xls = pd.ExcelFile(fileName)
df1 = pd.read_excel(xls, 'Brazil Covid-19 data')
df2 = pd.read_excel(xls, 'Original Column Names')
df3 = pd.read_excel(xls, 'Temperature by State')
df4 = pd.read_excel(xls, 'Brazil State Stats')
df5 = pd.read_excel(xls, 'City area')
Best,