How to import xlsx using pd in python

Python uses pandas and xlsxwriter to read and write xlsx files. Here are the relevant steps:

The existing xlsx files are as follows:

1. Read all the data in the first n rows

# coding: utf-8

import pandas as pd

# Read all the data in the first n rows

df = pd.read_excel('school.xlsx')#Read the first sheet in xlsx

data1 = df .head(7) # Read all the data in the first 7 rows, dataFrame structure

data2 = df.values ​​#list format, read all the data in the table

print("Get all Value:\n{0}".format(data1)) #Formatted output

print("Get all values:\n{0}".format(data2)) #Formatted output

2. Read specific rows and columns

# coding: utf-8

import pandas as pd

# Read Get specific rows and columns

df = pd.read_excel('school.xlsx') #Read the first sheet in xlsx

data1 = df.ix[0].values ​​# Read all the data in the first row, 0 means the first row, excluding the header

data2 = df.ix[1,1] #Read the specified row and column position data

data3 = df .ix[[1,2]].values ​​#Read specified multiple rows

data4 = df.ix[:,[0]].values ​​#Read all rows of specified column

#data4 = df[u'class'].values ​​#Same as above

data5 = df.ix[:,[u'class',u'name']].values ​​#Read the specified key value column All lines

print("Data:\n{0}".format(data1))

print("Data:\n{0}".format(data2))

print("Data:\n{0}".format(data3))

print("Data:\n{0}".format(data4))

print("Data:\n{0}".format(data5))

Related recommendations: "Python Video Tutorial"

3. Get xlsx file line number, all column names

# coding: utf-8

import pandas as pd

# Get xlsx file line number, all column names

df = pd.read_excel('school.xlsx') #Read the first sheet in xlsx

print("Output line number list{}".format(df.index.values )) # Get all row numbers of the xlsx file

print("Output column title{}".format(df.columns.values)) #All column names

4. Read xlsx data and convert it into dictionary

# coding: utf-8

import pandas as pd

# Read xlsx data and convert it into dictionary

df = pd.read_excel('school.xlsx') #Read the first sheet in xlsx

test_data=[]

for i in df.index.values:#Get The index of the row number and traverse it:

#Get the data specified in each row based on i and use to_dict to convert it into a dictionary

row_data=df.ix[i,['id ','name','class','data','stature']].to_dict()

test_data.append(row_data)

print("The final data obtained is ：{0}".format(test_data))

5. Write xlsx file

#coding: utf-8

import xlsxwriter

# Create workbook

file_name = "first_book.xlsx"

workbook = xlsxwriter.Workbook(file_name)

# Create worksheet

worksheet = workbook.add_worksheet('sheet1')

# Write cells

worksheet.write(0, 0, 'id')

worksheet.write(0 ,1, 'name')

worksheet.write(0,2, 'class')

worksheet.write(0,3, 'data')

# Write rows

worksheet.write_row(1, 0, [1, 2, 3])

# Write columns, where column D needs to be capitalized

worksheet.write_column(' D2', ['a', 'b', 'c'])

# Close the workbook

workbook.close()

The xlsx file written is as follows ：

##

The above is the detailed content of How to import xlsx using pd in python. For more information, please follow other related articles on the PHP Chinese website!