Python Forum

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?

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)

kiran

kiran