Read for reading: Python uses xlrd and xlwt to read and write simple uses of Excel
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.
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.
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.
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)
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)
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)
playTime = xlrd.xldate_as_tuple(adTime[i],adWb.datemode)
uvRow = uvSheet.row_values(j)
uvTime = xlrd.xldate_as_tuple(uvRow,uvWb.datemode)
if((playTime==uvTime and playTime==uvTime andplayTime==uvTime and ((playTime==uvTime and uvTime-playTime>-2and uvTime-playTime<6) or (uvTime==playTime+1 anduvTime+60-playTime>-2 and uvTime+60-playTime<6) or(uvTime==playTime-1 and uvTime-59==0 and playTime-0==0))) or(playTime==uvTime and playTime==uvTime and playTime==uvTime-1and uvTime==0 and playTime==23 and uvTime+60-playTime<6)):
gap = uvTime-playTime
j = j + 1
i= i + 1
#Preserve the results
View the results:
The first line of data represents minutes.
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