How to deal with analysis data in batch in database
This article introduces the relevant knowledge of "how to deal with analytical data in batches in the database". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!
Demand: 20 brands have produced a total of 127 categories of products, screening the total sales of nearly one year TOP5 brands and the corresponding sales.
Idea: first do the first one and then do the same and summarize the results.
The data information is as follows:
Load data:
Next, we want to summarize the sales of different brands under this segment. We want to summarize the sales of each brand for the past year (September 2018-August 2019). First, let's see if the date is correct:
The monthly sales of each brand are calculated as follows:
According to brand grouping, calculate the top five brands with total sales:
Here is a detail, the final summary is the sales top5 of all the segments of the industry, tagging the category to which they belong.
The final result:
Import timestart = time.time () result = pd.DataFrame () for name in os.listdir (): df = pd.read_excel (name) df ['sales'] = df ['number of visitors'] * df ['conversion rate'] * df ['customer unit price'] df_sum = df.groupby ('brand',) ['sales'] .sum (). Reset_index () df_sum ['category'] = name.replace (".xlsx", ") result = pd.concat ([result') Df_sum]) final = result.groupby ('brand') ['sales'] .sum (). Reset_index (). Sort_values ('sales', ascending=False) final.head ()
For the strange-looking sales, pandas took the initiative to display the actual sales in the form of scientific notation. To restore the values, you need to change the original settings:
This is the end of the content of "how to deal with analytical data in batches in the database". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!
