Python Read-Write Excel Example Application

Read for reading: Python uses xlrd and xlwt to read and write simple uses of Excel

Preface


Earlier, we learned two libraries xlrd and xlwt for simple Python operation Excel. Today, we will take a practical case to enhance the actual combat function.

Case Background


Our marketing students want to analyze the active data of users before and after the advertising time (minute level, first minute to last 5 minutes) to analyze the effect of our advertising, so they need me to help calculate the data. So here we share the actual operation.

Data preparation


1. Advertising time

The time file for advertising broadcasting here is ad.xlsx, which is provided by students of marketing department. Some data are as follows.

2. User active data

Usually from the database calculation export (the SQL operation can see my previous publication of MySQL related knowledge), sometimes also need to calculate through the log, the calculated data uv.xlsx is as follows (minute level). Here we distinguish between the mobile end and the PC end.

Data Computing


After the data is ready, you can calculate it directly, because the use of xlrd and xlwt libraries has been mentioned before. The code py_excel.py is pasted here directly. You can try it in practice. (In order to better view the code, you can access the original text, where the editor can only block references)

#coding:utf-8

import xlrd

import xlwt

import re

import sys

import string

from datetime import datetime

 

#read the ad play time data

programFile = r'ad.xlsx'

#Read Advertising Play Time Data File

adFile = r'ad.xlsx'

 

#Open the file and get the data

adWb = xlrd.open_workbook(adFile)

adSheet = adWb.sheet_by_index(0)

 

#Get the target time series(In the first place3Column, composed of date and time)

adTime = adSheet.col_values(2)

 

lenth_of_time = len(adTime)

 

#readuvdata

uvFile = r'uv.xlsx'

 

uvWb = xlrd.open_workbook(uvFile)

#When data is stored in a table, if there are more than onesheet,To assign values separately

uvSheet = uvWb.sheet_by_index(0)

 

uvTime = uvSheet.col_values(0)

length = len(uvTime)

 

#Initialize write operations

resultFile = xlwt.Workbook()

#Two tables were added to generate the tablesheet

resultMobile = resultFile.add_sheet('mobile',cell_overwrite_ok=True)

resultPC = resultFile.add_sheet('PC',cell_overwrite_ok=True)

 

i=1

while(i<lenth_of_time):

   print (i)

   playTime = xlrd.xldate_as_tuple(adTime[i],adWb.datemode)

   print (playTime)

    j= 1

    while(j<length):

       uvRow = uvSheet.row_values(j)

       uvTime = xlrd.xldate_as_tuple(uvRow[0],uvWb.datemode)

       #Time matching

       if((playTime[0]==uvTime[0] and playTime[1]==uvTime[1] andplayTime[2]==uvTime[2] and ((playTime[3]==uvTime[3] and uvTime[4]-playTime[4]>-2and uvTime[4]-playTime[4]<6) or (uvTime[3]==playTime[3]+1 anduvTime[4]+60-playTime[4]>-2 and uvTime[4]+60-playTime[4]<6) or(uvTime[3]==playTime[3]-1 and uvTime[4]-59==0 and playTime[4]-0==0))) or(playTime[0]==uvTime[0] and playTime[1]==uvTime[1] and playTime[2]==uvTime[2]-1and uvTime[3]==0 and playTime[3]==23 and uvTime[4]+60-playTime[4]<6)):

           gap = uvTime[4]-playTime[4]

 

           if(gap<-50):

                gap=gap+60

           if(gap==59):

                gap=-1

 

           if(uvRow[1]=='mo'):

               resultMobile.write(i,gap+1,uvRow[2])

           if(uvRow[1]=='pc'):

               resultPC.write(i,gap+1,uvRow[2])

       j = j + 1

    i= i + 1

 

#Preserve the results

resultFile.save(r'result.xls')

View the results:

The first line of data represents minutes.

Summary


Above is today's topic content, today share how to use Python to operate Excel to read and write files to generate target data, which is convenient for later data analysis. Hope that through the above operation can help you. If you have any good opinions, suggestions, or different opinions, I would like you to leave a message to communicate and discuss with us.

If you want to contact me quickly, please pay attention to the Wechat Public Number: AiryData.

If you need to reprint, please contact authorization, thank you for your cooperation.


Reference File Address Links:http://pan.baidu.com/s/1geFdfMF Password: xpq6

Tags: Python Excel Mobile Database

Posted on Thu, 27 Jun 2019 16:41:21 -0700 by hoolahoops