I was trying to use CricInfo StatsGuru to find out which batsmen in test cricket history have frequently failed to score significant runs before getting out. This will be slightly more interesting as it will not depend on the batsman's career average rather will calculate the percentage of his under-performing innings compared to his career average. For this I have made the following assumptions -
1. The innings where the batsmen scored 0 to 49 (less than a half century) will be considered as not-so-significant contribution to the team total. While it's subjective and not true (probably 0 to 39 makes more sense), I wanted to count half century as an achievement for that particular batsman in an innings irrespective of where his team stands.
2. I am counting only innings where he was out. So if a player remain o not out or 1 not out, that innings should not be counted as a under performing innings while even if he got out scoring 49 runs in a team total of 88, this method will count that innings as an under performing one.
3. I wanted to exclude bowlers who will have lots of scores (in fact most scores) within this range. For this, I believe a bar of setting at least 2000 test runs scored in career will work as a good filter.
4. The percentage will be calculated as, count of (under performing innings / total number of innings played in career) * 100.
5. I wanted to also mention the number of times the batsman got out scoring a duck.
With the assumptions above in mind, I could only reach to an imperfect table below and would like to have your help on figuring this out. I could not figure out a way to put career runs scored and I had to manually enter the career innings numbers from CricInfo. MS Excel helped me do the percentage calculation automatically though but I would like to limit it till 2 digits after decimal. The table below shows players who have at least played enough number of matches that generated at least 90 matches where he under performed. This criteria I set as I couldn't apply option#3 for filtering the results.
1. The innings where the batsmen scored 0 to 49 (less than a half century) will be considered as not-so-significant contribution to the team total. While it's subjective and not true (probably 0 to 39 makes more sense), I wanted to count half century as an achievement for that particular batsman in an innings irrespective of where his team stands.
2. I am counting only innings where he was out. So if a player remain o not out or 1 not out, that innings should not be counted as a under performing innings while even if he got out scoring 49 runs in a team total of 88, this method will count that innings as an under performing one.
3. I wanted to exclude bowlers who will have lots of scores (in fact most scores) within this range. For this, I believe a bar of setting at least 2000 test runs scored in career will work as a good filter.
4. The percentage will be calculated as, count of (under performing innings / total number of innings played in career) * 100.
5. I wanted to also mention the number of times the batsman got out scoring a duck.
With the assumptions above in mind, I could only reach to an imperfect table below and would like to have your help on figuring this out. I could not figure out a way to put career runs scored and I had to manually enter the career innings numbers from CricInfo. MS Excel helped me do the percentage calculation automatically though but I would like to limit it till 2 digits after decimal. The table below shows players who have at least played enough number of matches that generated at least 90 matches where he under performed. This criteria I set as I couldn't apply option#3 for filtering the results.
Player - Innings - Career Innings - Percentage - 0 / duck SK Warne (Aus) 173 199 86.93467337 34 A Kumble (India) 137 173 79.19075145 17 IA Healy (Aus) 140 182 76.92307692 18 N Kapil Dev (India) 140 184 76.08695652 16 MV Boucher (ICC/SA) 138 194 71.13402062 16 MA Atherton (Eng) 149 212 70.28301887 20 AJ Stewart (Eng) 163 235 69.36170213 14 ST Jayasuriya (SL) 130 188 69.14893617 15 GA Gooch (Eng) 145 215 67.44186047 13 DI Gower (Eng) 137 204 67.15686275 7 CA Walsh (WI) 124 185 67.02702703 43 ME Waugh (Aus) 134 209 64.11483254 19 BC Lara (ICC/WI) 147 232 63.36206897 17 SR Waugh (Aus) 159 260 61.15384615 22 S Chanderpaul (WI) 133 219 60.73059361 13 AR Border (Aus) 160 265 60.37735849 11 SM Gavaskar (India) 129 214 60.28037383 12 R Dravid (ICC/India) 151 257 58.75486381 8 SR Tendulkar (India) 168 288 58.33333333 14 RT Ponting (Aus) 151 259 58.3011583 14 JH Kallis (ICC/SA) 140 244 57.37704918 10
So I gave a post in Cricket Fan Forums where I know there are several expert cricket statisticians who can help me. And I got help within a few hours here - http://cricketfansforum.net/showthread.php?p=391099#post391099 from pappubahry. His list is sorted by failure rate (from lower rate to higher) for players with at least 3000 test run. I can now follow the process he mentioned and prepare separate tables for failure rate between 0-9, 0-19, 0-24 and 0-39. Or even 50-99, 100-149, 150-199 etc.
Player Mat Inns NO Runs Ave ducks 0-49 failure rate DG Bradman 52 80 10 6996 99.94 7 36 45.0% H Sutcliffe 54 84 9 4555 60.73 2 40 47.6% TT Samaraweera 63 98 17 4395 54.25 7 51 52.0% KF Barrington 82 131 15 6806 58.67 5 69 52.7% JB Hobbs 61 102 7 5410 56.94 4 55 53.9% KD Walters 74 125 14 5357 48.26 4 69 55.2% CL Walcott 44 74 7 3798 56.68 1 41 55.4% ED Weekes 48 81 5 4455 58.61 6 45 55.6% GS Chappell 87 151 19 7110 53.86 12 85 56.3% L Hutton 79 138 15 6971 56.67 5 79 57.2% JH Kallis 144 244 37 11677 56.41 10 140 57.4% MEK Hussey 58 101 12 4605 51.74 9 58 57.4% B Mitchell 42 80 9 3471 48.88 3 46 57.5% G Gambhir 37 66 5 3077 50.44 4 38 57.6% DCS Compton 78 131 15 5807 50.06 10 76 58.0% RT Ponting 152 259 28 12363 53.51 14 151 58.3% SR Tendulkar 176 288 31 14532 56.54 14 168 58.3% R Dravid 149 257 29 12027 52.75 8 151 58.8% A Flower 63 112 19 4794 51.54 5 66 58.9% KC Sangakkara 94 156 12 8244 57.25 4 93 59.6% DR Martyn 67 109 14 4406 46.37 7 65 59.6% IVA Richards 121 182 12 8540 50.23 10 109 59.9% GS Sobers 93 160 21 8032 57.78 12 96 60.0% EH Hendren 51 83 9 3525 47.63 4 50 60.2% SM Gavaskar 125 214 16 10122 51.12 12 129 60.3% Javed Miandad 124 189 21 8832 52.57 6 114 60.3% GP Thorpe 100 179 28 6744 44.66 12 108 60.3% AR Border 156 265 44 11174 50.56 11 160 60.4% Saeed Anwar 55 91 2 4052 45.52 8 55 60.4% SM Katich 56 99 6 4188 45.03 4 60 60.6% VVS Laxman 119 196 30 7856 47.32 12 119 60.7% RA Smith 62 112 15 4236 43.67 8 68 60.7% S Chanderpaul 129 219 34 9063 48.98 13 133 60.7% ER Dexter 62 102 8 4502 47.89 6 62 60.8% Mohammad Yousuf 90 156 12 7530 52.29 11 95 60.9% IR Bell 61 105 11 4077 43.37 7 64 61.0% WM Lawry 67 123 12 5234 47.15 6 75 61.0% DPMD Jayawardene 116 190 13 9527 53.82 11 116 61.1% SR Waugh 168 260 46 10927 51.06 22 159 61.2% RB Simpson 62 111 7 4869 46.81 8 68 61.3% Inzamam-ul-Haq 120 200 22 8830 49.6 15 123 61.5% RN Harvey 79 137 10 6149 48.41 7 85 62.0% AC Gilchrist 96 137 20 5570 47.6 14 85 62.0% G Boycott 108 193 23 8114 47.72 10 120 62.2% IR Redpath 66 120 11 4737 43.45 9 75 62.5% CH Lloyd 110 175 14 7515 46.67 4 110 62.9% ML Hayden 103 184 14 8625 50.73 14 116 63.0% AB de Villiers 65 111 13 4702 47.97 2 70 63.1% AN Cook 64 114 7 4941 46.17 3 72 63.2% PBH May 66 106 9 4537 46.77 8 67 63.2% FMM Worrell 51 87 9 3860 49.48 11 55 63.2% AI Kallicharran 66 109 10 4399 44.43 10 69 63.3% BC Lara 131 232 6 11953 52.88 17 147 63.4% DL Haynes 116 202 25 7487 42.29 10 128 63.4% MJ Clarke 68 112 12 4697 46.97 5 71 63.4% WR Hammond 85 140 16 7249 58.45 4 89 63.6% G Kirsten 101 176 15 7289 45.27 13 112 63.6% MC Cowdrey 114 188 15 7624 44.06 9 120 63.8% ME Waugh 128 209 17 8029 41.81 19 134 64.1% Saleem Malik 103 154 22 5768 43.69 12 99 64.3% GC Smith 90 157 9 7422 50.14 10 101 64.3% DJ Cullinan 70 115 12 4554 44.21 10 74 64.3% M Amarnath 69 113 10 4378 42.5 12 73 64.6% IM Chappell 75 136 10 5345 42.42 11 88 64.7% HM Amla 50 88 7 3836 47.35 6 57 64.8% V Sehwag 86 148 6 7670 54.01 12 96 64.9% HP Tillakaratne 83 131 25 4545 42.87 9 85 64.9% RB Kanhai 79 137 6 6227 47.53 7 89 65.0% ME Trescothick 76 143 10 5825 43.79 12 93 65.0% MA Taylor 104 186 13 7525 43.49 5 121 65.1% RB Richardson 86 146 12 5949 44.39 8 95 65.1% RC Fredericks 59 109 7 4334 42.49 7 71 65.1% Younis Khan 65 116 8 5440 50.37 13 76 65.5% RR Sarwan 83 146 8 5759 41.73 10 96 65.8% GR Viswanath 91 155 10 6080 41.93 10 102 65.8% TW Graveney 79 123 13 4882 44.38 8 81 65.9% CG Greenidge 108 185 16 7558 44.72 11 122 65.9% DB Vengsarkar 116 185 22 6868 42.13 15 122 65.9% Mushtaq Mohammad 57 100 7 3643 39.17 4 66 66.0% DM Jones 52 89 11 3631 46.55 11 59 66.3% MD Crowe 77 131 11 5444 45.36 9 87 66.4% N Hussain 96 171 16 5764 37.18 14 114 66.7% PD Collingwood 67 114 10 4246 40.82 6 76 66.7% KWR Fletcher 59 96 14 3272 39.9 6 64 66.7% NS Sidhu 51 78 2 3202 42.13 9 52 66.7% BF Butcher 44 78 6 3104 43.11 3 52 66.7% AL Hassett 43 69 3 3073 46.56 1 46 66.7% CD McMillan 55 91 10 3116 38.46 7 61 67.0% A Ranatunga 93 155 12 5105 35.69 12 104 67.1% DI Gower 117 204 18 8231 44.25 7 137 67.2% KP Pietersen 70 122 6 5630 48.53 7 82 67.2% DC Boon 107 190 20 7422 43.65 16 128 67.4% GA Gooch 118 215 6 8900 42.58 13 145 67.4% TM Dilshan 66 105 11 3990 42.44 11 71 67.6% Zaheer Abbas 78 124 11 5062 44.79 10 84 67.7% AW Greig 58 93 4 3599 40.43 5 63 67.7% PR Umrigar 59 94 8 3631 42.22 5 64 68.1% JL Langer 105 182 12 7696 45.27 11 124 68.1% DL Amiss 50 88 10 3612 46.3 10 60 68.2% AJ Strauss 81 146 6 6024 43.02 12 100 68.5% JH Edrich 77 127 9 5138 43.54 6 87 68.5% SC Ganguly 113 188 17 7212 42.17 13 129 68.6% MJ Slater 74 131 7 5312 42.83 9 90 68.7% M Azharuddin 99 147 9 6215 45.03 5 101 68.7% AG Prince 61 96 16 3487 43.58 6 66 68.8% JC Adams 54 90 17 3012 41.26 7 62 68.9% Imran Khan 88 126 25 3807 37.69 8 87 69.0% Hanif Mohammad 55 97 8 3915 43.98 5 67 69.1% CG Borde 55 97 11 3061 35.59 13 67 69.1% ST Jayasuriya 110 188 14 6973 40.07 15 130 69.1% PA de Silva 93 159 11 6361 42.97 7 110 69.2% CC Hunte 44 78 6 3245 45.06 5 54 69.2% HA Gomes 60 91 11 3171 39.63 5 63 69.2% AJ Stewart 133 235 21 8463 39.54 14 163 69.4% WJ Cronje 68 111 9 3714 36.41 11 77 69.4% FE Woolley 64 98 7 3283 36.07 13 68 69.4% VL Manjrekar 55 92 10 3208 39.12 11 64 69.6% AR Morris 46 79 3 3533 46.48 4 55 69.6% C Hill 49 89 2 3412 39.21 9 62 69.7% CH Gayle 91 159 6 6373 41.65 15 111 69.8% SP Fleming 111 189 10 7172 40.06 16 132 69.8% SM Pollock 108 156 39 3781 32.31 9 109 69.9% CC McDonald 47 83 4 3107 39.32 2 58 69.9% BB McCullum 55 93 6 3232 37.14 8 65 69.9% DL Vettori 103 157 23 4053 30.24 17 110 70.1% CL Cairns 62 104 5 3320 33.53 7 73 70.2% RJ Shastri 80 121 14 3830 35.79 9 85 70.2% MA Atherton 115 212 7 7728 37.69 20 149 70.3% Ijaz Ahmed 60 92 4 3315 37.67 7 65 70.7% A Flintoff 79 130 9 3845 31.77 17 92 70.8% HH Gibbs 90 154 7 6167 41.95 11 109 70.8% NJ Astle 81 137 10 4702 37.02 11 97 70.8% MW Gatting 79 138 14 4409 35.55 16 98 71.0% MV Boucher 138 194 23 5257 30.74 16 138 71.1% APE Knott 95 149 15 4389 32.75 8 106 71.1% MP Vaughan 82 147 9 5719 41.44 9 105 71.4% MA Butcher 71 131 7 4288 34.58 10 94 71.8% VT Trumper 48 89 8 3163 39.04 7 64 71.9% JR Reid 58 108 5 3428 33.28 5 78 72.2% ND McKenzie 58 94 7 3253 37.39 9 68 72.3% Mudassar Nazar 76 116 8 4114 38.09 7 84 72.4% MS Atapattu 90 156 15 5502 39.02 22 113 72.4% KJ Hughes 70 124 6 4415 37.41 10 90 72.6% Majid Khan 63 106 5 3931 38.92 9 77 72.6% Habibul Bashar 50 99 1 3026 30.87 7 72 72.7% JG Wright 82 148 7 5334 37.82 7 108 73.0% AJ Lamb 79 139 10 4656 36.09 9 102 73.4% CL Hooper 102 173 15 5762 36.46 13 127 73.4% Asif Iqbal 58 99 7 3575 38.85 9 73 73.7% PJL Dujon 81 115 11 3322 31.94 8 85 73.9% WPUJC Vaas 111 162 35 3089 24.32 12 120 74.1% BE Congdon 61 114 7 3448 32.22 9 86 75.4% GM Wood 59 112 6 3374 31.83 9 85 75.9% N Kapil Dev 131 184 15 5248 31.05 16 140 76.1% IT Botham 102 161 6 5200 33.54 14 123 76.4% Sir RJ Hadlee 86 134 19 3124 27.16 12 103 76.9% IA Healy 119 182 23 4356 27.39 18 140 76.9% GA Hick 65 114 6 3383 31.32 11 88 77.2% RW Marsh 96 150 13 3633 26.51 12 120 80.0% GW Flower 67 123 6 3457 29.54 16 99 80.5% SK Warne 145 199 17 3154 17.32 34 173 86.9%