Shulou(Shulou.com)05/31 Report--

This article mainly explains "how to use pandas to solve the Excel table of python data analysis". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn how to use pandas to solve the Excel table in python data analysis.

(1) to read other documents

Next we read three types of files csvtsvtxt files, it is worth noting that these three types of files are read using the same method, that is, pd.read_csv (file), when reading for the excel table should pay attention to the delimiter, using the parameter sep='' to separate. Let's take a look at how it works in excel and pandas!

1.excel reads other files

Import external data from excel

1.1 Import csv Fil

When you import a csv file, select a comma for the delimiter.

1.2 Import tsv files

Import the tsv file, select the tab key for the delimiter

1.3 Import txt text files

When importing txt files, pay attention to what symbols are separated in the text and customize the delimiters.

2.pandas reads other files

In pandas, whether it is to read csv files or tsv files or txt files, it is read by the method of read_csv (), separated by the sep () parameter.

2.1 read csv file import pandas as pd# import csv file test1 = pd.read_csv ('. / excel/test12.csv',index_col= "ID") df1 = pd.DataFrame (test1) print (df1) 2.2 read tsv file

The tab key is represented by\ t

Import pandas as pd# imports tsv file test3 = pd.read_csv (". / excel/test11.tsv", sep='\ t') df3 = pd.DataFrame (test3) print (df3) 2.3 read txt file import pandas as pd# imports txt file test2 = pd.read_csv (". / excel/test13.txt", sep=' |') df2 = pd.DataFrame (test2) print (df2)

Results:

(II) PivotTable

There are many kinds of data in excel, and there are many types, so it is very convenient and intuitive to use PivotTable to analyze all kinds of data for us.

Example: draw the following data into a PivotTable, and draw the annual sales by category!

1. Making PivotTable in excel

If you want to split by year, we need to split the date column and split the year. Then select the PivotTable under the data bar and select the area.

Then drag each part of the data to each area.

Results:

In this way, the PivotTable is completed in excel.

So how do you achieve this effect in pandas?

two。 Draw PivotTable in pandas

The function to draw the PivotTable is: df.pivot_lable (index,columns,values), and then sum the data.

Import pandas as pdimport numpy as nppd.options.display.max_columns = 999test = pd.read_excel ('. / excel/test14.xlsx') df = pd.DataFrame (test) # take out the year and create a new column named year df ['year'] = pd.DatetimeIndex (df [' Date']). Year# draws the PivotTable table = df.pivot_table (index=' Category', columns='year',values=' sales' Aggfunc=np.sum) df1 = pd.DataFrame (table) df1 ['Total'] = df1 [[2011Magi 2012Met 2013joct2014] .sum (axis=1) print (df1)

Results:

In addition, you can also use the groupby function to draw a data table. Here, the total category and year are grouped to find the total sales and sales quantity.

Import pandas as pdimport numpy as nppd.options.display.max_columns = 999test = pd.read_excel ('. / excel/test14.xlsx') df = pd.DataFrame (test) # take out the year and create a new column named year df ['year'] = pd.DatetimeIndex (df [' Date']). Year# groupby method group = df.groupby (['general class') 'year']) s = group [' sales'] .sum () c = group ['ID'] .count () table = pd.DataFrame ({' sum':s,'total':c}) print (table)

Results:

At this point, I believe you have a deeper understanding of "how to use pandas to solve the Excel table in python data analysis". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.