I'm working onn below code to extract data from excel using python - Printable Version +- Python Forum (https://python-forum.io) +-- Forum: Python Coding (https://python-forum.io/forum-7.html) +--- Forum: Data Science (https://python-forum.io/forum-44.html) +--- Thread: I'm working onn below code to extract data from excel using python (/thread-5809.html) |
I'm working onn below code to extract data from excel using python - kiran - Oct-23-2017 code import xlrd #import mysqldb import datetime import pandas as pd ExcelFileName = "H:\Data.xlsx" workbook = xlrd.open_workbook(ExcelFileName) worksheet = workbook.sheet_by_name("Timecard Details") num_rows = worksheet.nrows num_cols = worksheet.ncols for r in range(1,num_rows): ClientNo =worksheet.cell(r,9).value data =ClientNo.split('|') date = worksheet.cell(r, 6).value dt=xlrd.xldate_as_datetime(date, workbook.datemode) hours = worksheet.cell(r, 7).value df = pd.DataFrame({'customer': [data[2]], 'time': [hours], 'Date': [dt], 'Mainetance type': [data[1]], 'Maintenance': [data[0]]}) print(df) Output: I want the below output to be under one header like all dates under data and all hours under hours. Date Mainetance type Maintenance customer time 0 2017-10-03 BENCHMARKS MAINTENANCE 7702 0.25 Date Mainetance type Maintenance customer time 0 2017-10-04 TASK DOCUMENTATION MAINTENANCE 2037 1.5 Date Mainetance type Maintenance customer time 0 2017-10-03 BENCHMARK SETTER MAINTENANCE 7809 0.25 Date Mainetance type Maintenance customer time 0 2017-10-05 AS DESIGN MAINTENANCE 1233 2.5 Date Mainetance type Maintenance customer time 0 2017-10-05 AS REPORTS MAINTENANCE 7992 1.5 Hi guys can you please help me on this? RE: I'm working onn below code to extract data from excel using python - kiran - Oct-24-2017 import xlrd import pandas as pd #import mysqldb import datetime ExcelFileName = "D:\Oracle_Oct 2 to 6.xlsx" workbook = xlrd.open_workbook(ExcelFileName) worksheet = workbook.sheet_by_name("Timecard Details") num_rows = worksheet.nrows num_cols = worksheet.ncols for r in range(1,num_rows): ClientNo =worksheet.cell(r,9).value data =ClientNo.split('|') date = worksheet.cell(r, 6).value dt=xlrd.xldate_as_datetime(date, workbook.datemode) hours = worksheet.cell(r, 7).value if 'MAINTENANCE' in data[0]: values=(data[2], '|',hours,'|',dt, '|', data[1],'|',data[0]) df = pd.DataFrame({'customer': [data[2]],'time':[hours],'Date':[dt],'Mainetance type':[data[1]],'Maintenance':[data[0]] }) writer = pd.ExcelWriter("D:\Data.xlsx", engine='xlsxwriter') df.to_excel(writer, sheet_name=' MAINTENANCE ') writer.save() print(df) |