Python pandas.ExcelWriter类代码示例

def AddSeqComp(mypath):
    """ Loads TestLogAll.h5 from the specified path, then calls 
    MeasurementGroupTools.AddSeqComp to recalculate seq components using FFT  

    Input:  Directory of the measurment campaign, e.g.: "aLabView2"
    Output: Results1.h5, Results1.pdf in the data subdirs.
    """
    from pandas import HDFStore, ExcelWriter
    import MeasurementGroupTools as mgt

    h5logs = HDFStore(mypath + "\\" + 'TestLogsAll.h5')
    TestLog = h5logs['TestLogsAll']

    dirs = TestLog[u'DirName'].unique()
    for dname in dirs:
        mysubdirpath = mypath + "\\" + dname
        print "Processing: " + dname
        mgt.AddSeqComp(mysubdirpath, TestLog, dname)

    h5logs.put('TestLogsAll',TestLog)
    h5logs.close()

    writer = ExcelWriter(mypath + "\\" + 'TestLogsAll.xlsx')
    TestLog.to_excel(writer,'TestLogsAll') # the second argument defines sheet name
    writer.save()

    return

    def build_and_send_email(self, data, options):
        date = timezone.now().date().strftime('%Y_%m_%d')

        if 'recipients' in options:
            print 'yes'
            recipients = options['recipients']
        else:
            print 'no'
            recipients = settings.DEFAULT_WEEKLY_RECIPIENTS

        print 'recipients:', recipients

        message = EmailMessage(subject='Kikar Hamedina, Weekly Report: %s' % date,
                               body='Kikar Hamedina, Weekly Report: %s.' % date,
                               to=recipients)
        w = ExcelWriter('Weekly_report_%s.xlsx' % date)

        for datum in data:
            # csvfile = StringIO.StringIO()
            pd.DataFrame.from_dict(datum['content']).to_excel(w, sheet_name=datum['name'])

        w.save()
        w.close()
        # f = open(w.path, 'r', encoding='utf-8')
        message.attach_file(w.path)
        message.send()

class Excel(object):

    goal_time = 0.2
    params = ['openpyxl', 'xlsxwriter', 'xlwt']
    param_names = ['engine']

    def setup(self, engine):
        N = 2000
        C = 5
        self.df = DataFrame(np.random.randn(N, C),
                            columns=['float{}'.format(i) for i in range(C)],
                            index=date_range('20000101', periods=N, freq='H'))
        self.df['object'] = tm.makeStringIndex(N)
        self.bio_read = BytesIO()
        self.writer_read = ExcelWriter(self.bio_read, engine=engine)
        self.df.to_excel(self.writer_read, sheet_name='Sheet1')
        self.writer_read.save()
        self.bio_read.seek(0)

        self.bio_write = BytesIO()
        self.bio_write.seek(0)
        self.writer_write = ExcelWriter(self.bio_write, engine=engine)

    def time_read_excel(self, engine):
        read_excel(self.bio_read)

    def time_write_excel(self, engine):
        self.df.to_excel(self.writer_write, sheet_name='Sheet1')
        self.writer_write.save()

def DFtoExcel(df, FolderName, FileName):
    write_df = df.loc[:, ["FileName", "hyperlink", "Sheet Name"]]

    # Path Cell_Search_By_Key
    MainFolder = "C:\\Cell_Search_By_Key"
    FolderPath = os.path.join(MainFolder, FolderName)
    if not os.path.exists(FolderPath):
        os.makedirs(FolderPath)
    os.chdir(FolderPath)
    ExcelName = "%s.xlsx" % FileName
    writer = ExcelWriter(ExcelName)
    write_df.to_excel(writer, "Result", index=False)
    writer.save()
    # turn path into hyperlink
    Excel_Path = os.path.join(FolderPath, ExcelName)
    wb = Workbook(Excel_Path)
    # wb = Workbook.caller()
    checkArr = Range("B2").vertical.value
    i = 2
    for check in checkArr:

        RangeName = "B%d" % (i)
        displayRange = "A%d" % (i)
        address = Range(RangeName).value
        display_name = Range(displayRange).value
        i += 1
        try:
            Range(RangeName).add_hyperlink(address, text_to_display=address)
        except:
            pass
    wb.save()
    wb.close()
    return "FINISH"

def data_total( DocName, HistoryPath, SavePath ):
    
    files = os.listdir(HistoryPath)
    
    TotalData = pd.DataFrame()
    
    for file in files:    
        historyfile = os.path.join(HistoryPath, file)
        try:
            HistoryBook = pd.ExcelFile(historyfile)
            HistorySheet = HistoryBook.parse('Sheet1', skiprows = 0, index = None)
            
            TotalData = TotalData.append(HistorySheet)
        
        except IOError:
            print "Cannot read " + str(historyfile)
    
    TotalData.dropna(subset = ['ProductID'], inplace = True)
    TotalData.drop_duplicates(inplace = True)    
    
    filename = DocName + '.xlsx'
    filename = os.path.join(SavePath, filename)    
    
    writer = ExcelWriter(filename)
    TotalData.to_excel(writer, 'Sheet1', index = False )   
    writer.save()
    
    TotalData.to_csv(os.path.join(SavePath, DocName + '.txt'),sep=';',index=False, encoding = 'utf-8')

    def dataIO(self, args):
        """
        IO data for possible extension
        """
        writer = ExcelWriter("{}.xlsx".format(args.logFile), engine='xlsxwriter')
        reportDf = pd.DataFrame()
        reportDf.to_excel(writer, sheet_name="Reports")
        contentDf = pd.DataFrame()
        contentDf.to_excel(writer, sheet_name="Contents")
        contentSheet = writer.sheets["Contents"]
        contentSheet.write_string(xl_rowcol_to_cell(self.sheetLinkRow, 0),
                                  "link list for all choices and sub refines".format(args.logFile))
        self.sheetLinkRow += 1

        for dfname in ['1', '2', '3', '4', '5', '6', '7', '8', '9', '10', '11', '12']:
            if dfname in self._rawdf.keys():
                print("--save raw data for {}".format(dfname))
                self._rawdf[dfname].to_excel(writer, "{}".format(dfname))
                link_format = writer.book.add_format({'color': 'blue', 'underline': 1})
                contentSheet.write_url(xl_rowcol_to_cell(self.sheetLinkRow, 0), "internal:{}!A1".format(dfname),
                                       link_format, dfname)
                self.sheetLinkRow += 1
            if dfname in self._rawdf.keys() and dfname in ['2', '3', '5', '6', '8', '9', '11', '12']:
                self.refine(args, writer, dfname)

        # Close the Pandas Excel writer and output the Excel file.
        writer.save()

def build_aggregates():

    writer = None
    years = range(2006,2010)
    for year in years:
        yr = str(year)
#        fname = "Agg_%s.%s" %(str(yr), "xls")
        simu = SurveySimulation()
        simu.set_config(year = yr)
        simu.set_param()
        simu.set_survey()
        inflator = get_loyer_inflator(year)
        simu.inflate_survey({'loyer' : inflator})
        simu.compute()

        agg = Aggregates()
        agg.set_simulation(simu)
        agg.compute()

        if writer is None:
            writer = ExcelWriter(str(fname_all))
        agg.aggr_frame.to_excel(writer, yr, index= False, header= True, float_format="%.2f")
        print agg.aggr_frame.to_string()
        del simu
        del agg
        import gc
        gc.collect()


    writer.save()

    def save_table(self, directory = None, filename = None, table_format = None):
        '''
        Saves the table to some format
        '''
        now = datetime.now()
        if table_format is None:
            if filename is not None:
                extension = filename[-4:]
                if extension == '.xls':
                    table_format = 'xls'
                elif extension == '.csv':
                    table_format = 'csv'
            else:
                table_format = 'xls'

        if directory is None:
            directory = "."
        if filename is None:
            filename = 'Aggregates_%s.%s' % (now.strftime('%d-%m-%Y'), table_format)

        fname = os.path.join(directory, filename)

        try:
            df = self.aggr_frame
            if table_format == "xls":
                writer = ExcelWriter(str(fname))
                df.to_excel(writer, "aggregates", index= False, header= True)
                descr = self.create_description()
                descr.to_excel(writer, "description", index = False, header=False)
                writer.save()
            elif table_format == "csv":
                df.to_csv(fname, "aggregates", index= False, header = True)
        except Exception, e:
                raise Exception("Aggregates: Error saving file", str(e))

def main():
  parser = argparse.ArgumentParser(description = 'Fantasy Data Visualization')
  parser.add_argument('players', metavar='PLAYER', \
                      type=int, nargs='*', help='ids of players to display')
  parser.add_argument('-d', '--display', type=int, \
                      choices=[10,25,50], default=10, help='number of rows to display')
  parser.add_argument('-e', '--excel', dest='excel', \
                      action='store_true', default=False, help='to excel')
  args = parser.parse_args()

  show = int(args.display) # number of stats to show
  stats = pd.DataFrame.from_csv('.cache/res_avg.csv')
  
  # write all stats to excel file
  if (args.excel):
    writer = ExcelWriter('.cache/res_avg.xlsx')
    stats.to_excel(writer, 'Sheet1')
    writer.save()
  
  # display plot
  if len(args.players) > 0:
    plot(stats=stats, players=args.players)

  # print short summary
  print stats.sort_values(by=['avg_2015'], ascending=[False]).head(show)

def diag_aggregates():

    years = ['2006', '2007', '2008', '2009']

    df_final = None
    for yr in years:
        xls = ExcelFile(fname_all)
        df = xls.parse(yr, hindex_col= True)

        cols = [u"Mesure",
                u"Dépense \n(millions d'€)",
                u"Bénéficiaires \n(milliers)",
                u"Dépenses \nréelles \n(millions d'€)",
                u"Bénéficiaires \nréels \n(milliers)",
                u"Diff. relative \nDépenses",
                u"Diff. relative \nBénéficiaires"]
        selected_cols = [u"Mesure", u"Diff. relative \nDépenses", u"Diff. relative \nBénéficiaires"]
        df = df[selected_cols]
        df['year'] = yr
        df['num'] = range(len(df.index))
        df = df.set_index(['num', u'Mesure', 'year'])
        if df_final is None:
            df_final = df
        else:

            df_final = df_final.append(df, ignore_index=False)

#    DataFrame.groupby()
    df_final = df_final.sortlevel(0)
    print str(fname_all)[:-5]+'_diag.xlsx'
    writer = ExcelWriter(str(fname_all)[:-5]+'_diag.xlsx')
    df_final.to_excel(writer, sheet_name="diagnostics", float_format="%.2f")
    writer.save()

def create_output(regression_dist_dict, closest_curve_dict, reactor_name, name_add):
    '''Converts the dictionaries into dataframes to format for saving as
    an excel. The total resutls on the first sheet and closest curves on the second'''

    #creates a dataframe by looping through the dict and appending the df's together.
    count = 0
    print regression_dist_dict
    for key in regression_dist_dict:
        if count == 0:
            total_results = pd.DataFrame(regression_dist_dict[key], index=[key]*len(regression_dist_dict[key]), columns=['reactor', 'enrichment', 'distance'])
            closest_results = pd.DataFrame([closest_curve_dict[key]], index=[key], columns=['reactor', 'enrichment', 'distance'])
            count += 1
        else:
            total_results = total_results.append(pd.DataFrame(regression_dist_dict[key], index=[key]*len(regression_dist_dict[key]), columns=['reactor', 'enrichment', 'distance']))
            closest_results = closest_results.append(pd.DataFrame([closest_curve_dict[key]], index=[key], columns=['reactor', 'enrichment', 'distance']))

    print 'total_results', total_results
    print 'closest_results', closest_results

    file_name = 'data/%s_regression_results_%s.xlsx' % ('_'.join(map(str, reactor_name)), name_add)

    writer = ExcelWriter(file_name)

    total_results.to_excel(writer, sheet_name='Sheet1')
    closest_results.to_excel(writer, sheet_name='Sheet2')
    writer.save()

def save_xls_name(list_dfs, xls_path, sheet_name):
    '''save function that takes a list as input to name sheets.'''

    #remove ascii characters from dataframes for saving
    for df in list_dfs:
        df.index = remove_non_ascii(df.index)
        for col in df.columns:
        df[col] = remove_non_ascii(df[col])

    #save the df's to an excel file
    writer = ExcelWriter(xls_path)
    for n, df in enumerate(list_dfs):
        df.to_excel(writer, sheet_name[n])
    writer.save()


def remove_non_ascii(col):
    '''remove ascii for saving to excel'''
    new_index = []
    for name in col:
        try:
            for letter in name:
                if ord(letter) > 128:
                    name = name.replace(letter, '')
        except:
            pass
        new_index.append(name)
    return new_index

def writeToExcel(fileName=''):
	print "Writing to Excel File : "+fileName
	data = {'CVE ID Number': cveIDNumber, 'Summary Text': summaryText, 'Publish Date': publishDate, 'Software Type': softwareType, 'Vendor': vendor,'Product':product,'Version':version,'CVSS Score':cvssScore,'Confidentiality Impact':confidentialityImpact,'Integrity Impact':integrityImpact,'Availibility Impact':availibilityImpact,'Access Complexity':accessComplexity,'Authentication':authentication,'Gained Access':gainedAccess,'Vulnerability Type':vulnType}
	df = pd.DataFrame(data,columns=['CVE ID Number','Publish Date', 'Software Type','Vendor','Product','Version','CVSS Score','Confidentiality Impact','Integrity Impact','Availibility Impact','Access Complexity','Authentication','Gained Access','Vulnerability Type','Summary Text'])
	writer = ExcelWriter(fileName)
	df.to_excel(writer,'CVE Details',index=False)
	writer.save()
	print "Completed."

	def saveDialog(self):
		'''Saves the project as an .xls file.'''
		title									= 'Save project as...'
		fileName,f								= QFileDialog.getSaveFileName(self,title,self.path)
		writer									= ExcelWriter(fileName+'.xls')
		for marker in self.markers:
			marker.table.to_excel(writer,marker.name)
		writer.save()

def save_xlsx(list_dfs, xlsx_path):
    writer = ExcelWriter(xlsx_path)
    for n, df in enumerate(list_dfs):
        df.to_excel(writer, '%s' %n)
        print('Saving %s' %n)
    writer.save()
    print('Finished writing to file')
    return None