Python Data Analysis Test Guide
Translated by
ScribdTranslationsPython Data Analysis Test Guide
Translated by
ScribdTranslationsTest - Python for data analysis
In this test, we will validate our knowledge of Python. To achieve this,
You will need to apply what you learned in the previous units.
Read the entire document before starting the individual development, to ensure you have
the maximum score and focus efforts properly.
Prerequisites
1. To fulfill the request, you must create the classicmodels database in your engine.
PostgreSQL. For this, open a terminal window and execute the following
instruction:
psql -h localhost -p 5432 -U postgres -c "CREATE DATABASE classicmodels;"
[Link] the database is created, you must import the [Link] this
database.
psql -h localhost -p 5432 -U postgres -d classicmodels -f [Link]
Description
The commercial area of a company requests to close the year of sales, both for
review if the goals were met, so that we can plan for the next year. For this,
they will consider the data from the dataset [Link] to answer some questions,
carrying out the following tasks.
1. Generate a function calledread_table(table, engine)and use it to read tables
completed from the database in independent dataframes. Using this
function, import the following tables:
order
order details
customers
products
employees
2. Perform the merge between the DataFrames, making sure to use the correct one.
parameter validate to ensure referential integrity.
_ 1
[Link]fi[Link]
3. Add the following columns, considering their name and the associated formula.
sale:quantityOrdered*priceEach
costquantityOrdered*buyPrice
considering the previous columns
4. What was the total sales by product line? Include a total row.
5. How many distinct customers made purchases?
6. Are there any customers who haven't made any purchases yet? How many are there?
7. The creation of two reports is requested, answering the given questions.
What were the 10 clients that reported the highest gross sales in money during
the year 2005? Generate a DataFrame and save it in a Postgre table called
top_10_clients_2005the client's name and their
corresponding sale, cost and profit.
What were the top 10 best-selling items during the year 2005 (considering
net amount)? Generate a DataFrame and save it in a Postgre table called
top 10 products 2005, specifying the name of the product and its
corresponding sale, cost, and profit.
At this point, you should apply the DRY principle, so functions should be used to
perform filtering by dates, generate pivot tables and write table in Postgre. The functions
They must be in a separate file called [Link] and imported into Jupyter.
Notebook. This file should include:
A function that allows filtering a DataFrame by dates, indicating dataframe,
column to filter, start date and end date. The function must return a DataFrame.
A function that allows generating reports based on input parameters
like dataframe, rows, columns, values, and measure (aggregating_function). Use
fill_value = 0. Esta función debe retornar un DataFrame pivotado.
A function that allows writing to the database through saving a
DataFrame depending on input parameters such as DataFrame, name of the
table, engine and behavior in case the table exists (if_exists).
_ 2
[Link]fi[Link]
Requirements
Within the Jupyter Notebook file, you must execute the following actions and
explain what you are doing:
1. Generate and apply Python functions to perform queries on SQL databases.2
Point
2. Manipulating DataFrames to perform table joins, generate columns, and perform
calculations using Python commands. (3 Points)
3. Generate reports from DataFrames, applying filters and exporting tables by
Python command line interface. (3 Points)
4. Create and apply functions in Python applying the DRY principle and best practices.2
Points
Much success!
Considerations and recommendations
You must submit your work in a Jupyter Notebook file, with all the code and
the respective explanations to develop your work.
_ 3
[Link]