Unlock 30% off on Manual Testing Annual Plans this Holiday Season.

Copied to Clipboard!

How to use create_column_chart method in SeleniumBase

Best Python code snippet using SeleniumBase

EC_analyze_trends.py

Source:EC_analyze_trends.py

...50        chart.set_title({'name': v_title})51        worksheet.insert_chart(self.row_num, 5 , chart, {'x_offset': 10, 'y_offset': 20})52        self.row_num = self.row_num + x + 105354    def create_column_chart(self, df1, sheet_name,v_title, start_col):55        chart_type = 'line'56        df1.to_excel(self.df_writer, sheet_name=sheet_name, encoding="utf-8", index=False,57                     startrow=self.row_num, startcol=start_col)58        x, y = df1.shape59        # print('df_datagroup', sheet_name, x, y)60        worksheet = self.df_writer.sheets[sheet_name]61        chart = self.workbook.add_chart({'type': chart_type})  # ''subtype': 'percent_stacked'62        # for i in range(start_col, start_col+y):63        for i in range(1, y):64            # print("again",i)65            chart.add_series({66                'name': [sheet_name,self.row_num, start_col+i],67                # 'name': v_title,68                # å¼å§è¡ï¼å¼å§åï¼ç»æè¡ï¼ç»æå69                'categories': [sheet_name, self.row_num+1, start_col, self.row_num+x, start_col],70                'values': [sheet_name, self.row_num+1, start_col+i, self.row_num+x, start_col+i],71                'data_labels': {'value': False},72            })73        # Add a title.74        chart.set_title({'name': v_title})75        worksheet.insert_chart(self.row_num, 5 , chart, {'x_offset': 10, 'y_offset': 20})76        self.row_num = self.row_num + x + 107778    def out_yearly_analyze(self,df_data,prefix):79        df1 = pd.DataFrame()80        lv_fieldname = 'Count'81        lv_row = 082        try:83            # åå»ºä¸ä¸ªexcel84            # gender85            sheet_name = prefix+'00_traw'86            df_data.to_excel(self.df_writer, sheet_name=sheet_name, encoding="utf-8", index=False)8788            df_data = df_data.rename(columns={'ZF Global ID':lv_fieldname})89            df1 = df_data.loc[df_data['NewHP'].isin(date_range)].reset_index(drop=True)9091            sheet_name = prefix+'10_trends_data'92            df11 = df1.groupby(['NewHP'])[lv_fieldname].count().reset_index()93            df11 = df11.sort_values(by=['NewHP'], ascending=True).reset_index(drop = True)94            df11 = df11.rename(columns={'NewHP':'Month'})95            df11 = df11.rename(columns={'Count':'NewHire_No'})96            df11.to_excel(self.df_writer, sheet_name=sheet_name, encoding="utf-8", index=False,startrow=lv_row)9798            lv_row = lv_row + 2099            pivot_df1 = pd.pivot_table(df1,index=['Country'],columns=['NewHP'],100                                       values=[lv_fieldname],aggfunc='count',margins=True).reset_index()101            # print(pivot_df1.head(2))102            pivot_df1.to_excel(self.df_writer, sheet_name=sheet_name, encoding="utf-8",startrow=lv_row)103104            lv_row = lv_row + 20105            pivot_df1 = pd.pivot_table(df1,index=['Division'],columns=['NewHP'],106                                       values=[lv_fieldname],aggfunc='count',margins=True).reset_index()107            pivot_df1.to_excel(self.df_writer, sheet_name=sheet_name, encoding="utf-8",startrow=lv_row)108109            lv_row = lv_row + 20110            pivot_df1 = pd.pivot_table(df1,index=['Employee Class (Label)'],columns=['NewHP'],111                                       values=[lv_fieldname],aggfunc='count',margins=True).reset_index()112            pivot_df1.to_excel(self.df_writer, sheet_name=sheet_name, encoding="utf-8",startrow=lv_row)113114            lv_row = lv_row + 10115            pivot_df1 = pd.pivot_table(df1,index=['Employment Type (Label)'],columns=['NewHP'],116                                       values=[lv_fieldname],aggfunc='count',margins=True).reset_index()117            pivot_df1.to_excel(self.df_writer, sheet_name=sheet_name, encoding="utf-8",startrow=lv_row)118119            lv_row = lv_row + 20120            df2 = df_data.loc[df_data['NewTP'].isin(date_range)].reset_index(drop=True)121            df21 = df2.groupby(['NewTP'])[lv_fieldname].count().reset_index()122            df21 = df21.sort_values(by=['NewTP'], ascending=True).reset_index(drop = True)123            df21 = df21.rename(columns={'NewTP':'Month'})124            df21 = df21.rename(columns={'Count':'Termination No.'})125            df21.to_excel(self.df_writer, sheet_name=sheet_name, encoding="utf-8",startrow=lv_row)126127            lv_row = lv_row + 20128            pivot_df1 = pd.pivot_table(df2,index=['Country'],columns=['NewTP'],129                                       values=[lv_fieldname],aggfunc='count').reset_index()130            # print(pivot_df1.head(2))131            pivot_df1.to_excel(self.df_writer, sheet_name=sheet_name, encoding="utf-8",startrow=lv_row)132133            lv_row = lv_row + 20134            pivot_df1 = pd.pivot_table(df2,index=['Division'],columns=['NewTP'],135                                       values=[lv_fieldname],aggfunc='count',margins=True).reset_index()136            pivot_df1.to_excel(self.df_writer, sheet_name=sheet_name, encoding="utf-8",startrow=lv_row)137138            lv_row = lv_row + 20139            pivot_df1 = pd.pivot_table(df2,index=['Employee Class (Label)'],columns=['NewTP'],140                                       values=[lv_fieldname],aggfunc='count').reset_index()141            pivot_df1.to_excel(self.df_writer, sheet_name=sheet_name, encoding="utf-8",startrow=lv_row)142143            lv_row = lv_row + 10144            pivot_df1 = pd.pivot_table(df2,index=['Employment Type (Label)'],columns=['NewTP'],145                                       values=[lv_fieldname],aggfunc='count',margins=True).reset_index()146            pivot_df1.to_excel(self.df_writer, sheet_name=sheet_name, encoding="utf-8",startrow=lv_row)147148            sheet_name = prefix+'30_combine'149            merge_data = pd.merge(df11, df21, how='outer', on=['Month'], indicator=False).reset_index(drop=True)150            self.create_column_chart(merge_data,sheet_name,'New Hire / Termination Employee No.',1)151            self.d_data = merge_data152153        except Exception as e:154            print('write period data file failed:', sheet_name)155            print('error log', e)156        finally:157            pass158159    def out_period_res(self,df_data,prefix):160161        df_res_local = pd.DataFrame()162        df_res_local2 = pd.DataFrame()163        for month in date_range:164            line = {}165            try:166                line['Month'] = month167                # last_day = month.to_timestamp(how='end')168                # print(last_day)169                line['Active_total'] = len(df_data.loc[(df_data['NewHP'] <= month) &170                                                             ((df_data['NewTP'] > month) | (df_data['NewTP'].isna()))].index)171                                                             # (df_data['NewTP'].isna())].index)172                line['NewHire'] = len(df_data.loc[(df_data['NewHP'] == month)].index)173                line['Termination'] = len(df_data.loc[(df_data['NewTP'] == month)].index)174                line['NT'] = len(df_data.loc[(df_data['NewTP'] == month) & (df_data['NewHP'] == month)].index)175                df_res_local = df_res_local.append(pd.Series(line), ignore_index=True)176                df_res_local = df_res_local[['Month','Active_total','NewHire','Termination','NT']]177178            except Exception as e:179                print('Count Data Exception:', e)180181        try:182            sheet_name = prefix+'30_combine'183            df_res_local['Month'] = df_res_local['Month'].apply(pd.Period)184            # self.d_data = self.d_data.infer_objects()185            frames = [df_res_local,self.d_data]186187            df_res_new = pd.merge(df_res_local, self.d_data, how='outer', on=['Month'], indicator=False).reset_index(drop=True)188            # df_res_new = pd.concat(frames,keys=['Month'], axis=1).reset_index(drop=True)189            # df_res_new = pd.concat(frames, axis=1, ignore_index=True)190            # df_res_new = pd.concat(frames, axis=1)191            self.create_column_chart(df_res_new, sheet_name, 'Combined Res.', 1)192            # df_res_new.to_excel(self.df_writer, sheet_name=sheet_name, encoding="utf-8", index=False)193194            df_res_local['Active_CHG'] = df_res_local.Active_total.diff()195            df_res_local['New_CHG'] = df_res_local.NewHire.diff()196            df_res_local['Term_CHG'] = df_res_local.Termination.diff()197198            df_res_local['Active_CHGR'] = df_res_local.Active_total.pct_change().map(format_p)199            # df_res_local['New_CHGR'] = df_res_local.NewHire.pct_change().map(format_p)200            # df_res_local['Term_CHGR'] = df_res_local.Termination.pct_change().map(format_p)201202            # ç¯æ¯203            # df_res_local['Active_CHGQR'] = df_res_local.Active_total.diff(12).fillna(0,inplace=True)204205            self.create_column_chart(df_res_local, sheet_name, 'Change Trends.', 1)206207            line2 = {}208            for month in date_range:209                try:210                    for cnt in country:211                        # line2['Month'] = month212                        line2['Month'] = month213                        # line2['Country'] = cnt214                        # last_day = month.to_timestamp(how='end')215                        # print(last_day)216                        line2[cnt] = len(df_data.loc[(df_data['Country'] == cnt)&(df_data['NewHP'] <= month) &217                                                               ((df_data['NewTP'] > month) | (218                                                                   df_data['NewTP'].isna()))].index)219                        # (df_data['NewTP'].isna())].index)220                    # df_res_local2.set_index('Country',inplace=True)221                    df_res_local2 = df_res_local2.append(pd.Series(line2), ignore_index=True)222223                except Exception as e:224                    print('Count Data Exception:', e)225226            list_col = []227            list_col.append('Month')228            for cnt in country:229                list_col.append(str(cnt))230            df_res_local2 = df_res_local2[list_col]231232            self.create_column_chart(df_res_local2, sheet_name, 'Headcount Trends.', 1)233234        except Exception as e:235            print('Get active period data file failed:', sheet_name)236            print('error log', e)237        finally:238            pass239240    def main(self,df_data):241        # df_res = df_data.loc[(df_data['Employee Status (Label)'] != 'Dormant') & (df_data['Employee Status (Label)'] != 'Discarded') ]242        df_res = df_data.loc[(df_data['Employee Status (Label)'] == 'Dormant') | (df_data['Employee Status (Label)'] != 'Discarded') ]243        self.out_yearly_analyze(df_res,'AP')244        self.out_period_res(df_res,'AP')245246        self.row_num = 1
...

EC_analyze_function.py

Source:EC_analyze_function.py

...40        chart.set_title({'name': v_title})41        worksheet.insert_chart(self.row_num, 5 , chart, {'x_offset': 10, 'y_offset': 20})42        self.row_num = self.row_num + x + 104344    def create_column_chart(self, df1, sheet_name,v_title, start_col):45        chart_type = 'column'46        df1.to_excel(self.df_writer, sheet_name=sheet_name, encoding="utf-8", index=False,47                     startrow=self.row_num, startcol=start_col)4849        x, y = df1.shape50        # print('df_datagroup', sheet_name, x, y)51        worksheet = self.df_writer.sheets[sheet_name]52        chart = self.workbook.add_chart({'type': chart_type})  # ''subtype': 'percent_stacked'53        # for i in range(start_col, start_col+y):54        for i in range(1, y):55            # print("again",i)56            chart.add_series({57                'name': [sheet_name,self.row_num, i+1],58                # 'name': v_title,59                # å¼å§è¡ï¼å¼å§åï¼ç»æè¡ï¼ç»æå60                'categories': [sheet_name, self.row_num+1, start_col, self.row_num+x, start_col],61                'values': [sheet_name, self.row_num+1, start_col+i, self.row_num+x, start_col+i],62                'data_labels': {'value': False},63            })64        # Add a title.65        chart.set_title({'name': v_title})66        worksheet.insert_chart(self.row_num, 5 , chart, {'x_offset': 10, 'y_offset': 20})67        self.row_num = self.row_num + x + 106869    def out_static_analyze(self,df_data):70        df1 = pd.DataFrame()71        lv_fieldname = 'Count'72        try:73            # åå»ºä¸ä¸ªexcel74            # gender75            sheet_name = '00_raw'76            df_data.to_excel(self.df_writer, sheet_name=sheet_name, encoding="utf-8", index=False)7778            df_data = df_data.rename(columns={'ZF Global ID':lv_fieldname})7980            # gender81            sheet_name = 'StaticRes'82            df1 = df_data.groupby(['Gender'])[lv_fieldname].count().reset_index()83            df1 = df1.sort_values(by=[lv_fieldname], ascending=False).reset_index(drop = True)84            self.create_pi_chart(df1,sheet_name,'Gender',1)85            # Division86            df1 = df_data.groupby(['Division'])[lv_fieldname].count().reset_index()87            df1 = df1.sort_values(by=lv_fieldname, ascending=False).reset_index(drop = True)88            self.create_pi_chart(df1,sheet_name,'Division',1)89            #Country90            df1 = df_data.groupby(['Country'])[lv_fieldname].count().reset_index()91            df1 = df1.sort_values(by=lv_fieldname, ascending=False).reset_index(drop = True)92            self.create_pi_chart(df1,sheet_name,'Country',1)93            #Grade Level94            df1 = df_data.groupby(['EmploymentType'])[lv_fieldname].count().reset_index()95            df1 = df1.sort_values(by=lv_fieldname, ascending=False).reset_index(drop = True)96            self.create_pi_chart(df1,sheet_name,'Employment Type',1)97            #Job Family Level98            df1 = df_data[df_data['JF'] != 'N.'].groupby(['JF'])[lv_fieldname].count().reset_index()99            df1 = df1.sort_values(by=lv_fieldname, ascending=False).reset_index(drop = True)100            self.create_pi_chart(df1,sheet_name,'Job Family',1)101            #Location Group102            df1 = df_data.groupby(['Location Group (Name)'])[lv_fieldname].count().reset_index()103            df1 = df1.sort_values(by=lv_fieldname, ascending=False).reset_index(drop = True)104            self.create_pi_chart(df1,sheet_name,'Location Group (Name)',1)105            #Employee Class106            df1 = df_data.groupby(['Employee Class (Label)'])[lv_fieldname].count().reset_index()107            df1 = df1.sort_values(by=lv_fieldname, ascending=False).reset_index(drop = True)108            self.create_pi_chart(df1,sheet_name,'Employee Class (Label)',1)109110        #     Year Date, split China and AP111            self.row_num = 1112            sheet_name = 'DateRes'113            # Service Year114            df1 = df_data[df_data['ServiceYear']!='Unkown'].groupby(['ServiceYear'])[lv_fieldname].count().reset_index()115            df1 = df1.sort_values(by=['ServiceYear'], ascending=True).reset_index(drop = True)116            # self.create_column_chart(df1,sheet_name,'ServiceYear',1)117            df1 = df1.rename(columns={'Count':'AllCount'})118119            df2 = df_data[(df_data['ServiceYear']!='Unkown')&(df_data['Country'] == 'CN')].groupby(['ServiceYear'])[lv_fieldname].count().reset_index()120            df2 = df2.sort_values(by=['ServiceYear'], ascending=True).reset_index(drop = True)121            df2 = df2.rename(columns={'Count':'CHNCount'})122            merge_data = pd.merge(df1, df2, how='left', on='ServiceYear', left_index=True,indicator=False)123            self.create_column_chart(merge_data,sheet_name,' ServiceYear Data',1)124125            # Age126            df1 = df_data[(df_data['Age']!='Unkown')].groupby(['Age'])[lv_fieldname].count().reset_index()127            df1 = df1.sort_values(by=['Age'], ascending=True).reset_index(drop = True)128            # self.create_column_chart(df1,sheet_name,'China Age',1)129            df1 = df1.rename(columns={'Count':'AllCount'})130131            df2 = df_data[(df_data['Age']!='Unkown')&(df_data['Country'] == 'CN')].groupby(['Age'])[lv_fieldname].count().reset_index()132            df2 = df2.sort_values(by=['Age'], ascending=True).reset_index(drop = True)133            df2 = df2.rename(columns={'Count':'CHNCount'})134            merge_data = pd.merge(df1, df2, how='left', on='Age', left_index=True,indicator=False)135            self.create_column_chart(merge_data,sheet_name,'Age Data',1)136137            # Admin group138            self.row_num = self.row_num + 10139            df3 = df_data.groupby(['Country','Division','Admin Group (ID)'])[lv_fieldname].count().reset_index()140            pivot_df1 = pd.pivot_table(df3,index=['Division'], values=['Count'],aggfunc='mean').sort_values('Count',ascending=False)141            pivot_df1['Count'] = pivot_df1['Count'].map(format)142            pivot_df1 = pivot_df1.rename(columns={'Count':'AveragePerAdmin'})143144            pivot_df1.to_excel(self.df_writer, sheet_name=sheet_name, encoding="utf-8",145                               startrow=self.row_num,startcol=1)146            self.row_num = self.row_num + 20147            pivot_df1 = pd.pivot_table(df3,index=['Country'], values=['Count'],aggfunc='mean').sort_values('Count',ascending=False)148            pivot_df1['Count'] = pivot_df1['Count'].map(format)149            pivot_df1 = pivot_df1.rename(columns={'Count':'AveragePerAdmin'})
...

create_column_chart.py

Source:create_column_chart.py

1import csv23# final desired data format4# - Charts [["Test Name", <NumberOfAsserts>, <NumberOfFailedAsserts>]]56# Read in the data from file7data_list = []8with open("scripting_tests/TestAnalysisData.csv") as csv_file:9    # Read in data using csv reader10    file_reader = csv.reader(csv_file)11    for row in file_reader:12        data_list.append(row)1314# New list is initialized with headers15chart_data = [data_list[0]]16for row in data_list[1:]:17    num_asserts = int(row[1])18    num_failed_asserts = int(row[2])19    chart_data.append([row[0], num_asserts, num_failed_asserts])2021# Create the html for the chart22from string import Template23# First substitution is the header, the rest is the data24html_string = Template("""<html>25<head>26<script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>27<script type="text/javascript">28  google.charts.load('current', {packages: ['corechart']});29  google.charts.setOnLoadCallback(drawChart);30  function drawChart(){31      var data = google.visualization.arrayToDataTable([32           $labels,33           $data34          ],35          false); // 'false' means that the first row contains labels, not data.36      // Instantiate and draw our chart, passing in some options.37      var chart = new google.visualization.ColumnChart(document.getElementById('chart_div'));38      chart.draw(data);39  }40</script>41</head>42<body>43    <div id="chart_div" style="width:800; height:600"></div>44</body>45</html>46""")4748chart_data_str = ''4950for row in chart_data[1:]:51    # Create the data string52    chart_data_str += '%s,\n'%row5354# Substitute the data into the template55completed_html = html_string.substitute(labels=chart_data[0], data=chart_data_str)5657with open('scripting_tests/create_column_chart.html', 'w') as f:58    # Write the html string you've create to a file
...

Automation Testing Tutorials

Learn to execute automation testing from scratch with LambdaTest Learning Hub. Right from setting up the prerequisites to run your first automation test, to following best practices and diving deeper into advanced test scenarios. LambdaTest Learning Hubs compile a list of step-by-step guides to help you be proficient with different test automation frameworks i.e. Selenium, Cypress, TestNG etc.