Jan-01-2020, 12:40 AM
(This post was last modified: Jan-01-2020, 12:43 AM by eddywinch82.)
Hi there,
I have a code Filtering Data, that I wan't displayed from an Excel Document using Pandas
in Jupyter Notebook. It is for a UK RAF Historic Aircraft Display Team, Year 2009 Appearance Schedule.
Here is my Python Code :-
This is the output I get, when I run the above Python Code in Jupyter Notebook :-
Not just for those two Venues.
Best Regards
Eddie Winch
I have a code Filtering Data, that I wan't displayed from an Excel Document using Pandas
in Jupyter Notebook. It is for a UK RAF Historic Aircraft Display Team, Year 2009 Appearance Schedule.
Here is my Python Code :-
import pandas as pd xls = pd.ExcelFile(r'C:\Users\Edward\Desktop\BBMF Schedules And Master Forum Thread Texts\BBMF Display Schedule 2009.xls') data = pd.read_excel(xls, sheet_name="Sheet1") pd.options.display.max_rows = 1000 df = pd.DataFrame(data, columns= ['Venue','A/C','DISPLAY/','Date','BID']) df[(df['Venue'].str.contains('[a-zA-Z]') & (df['DISPLAY/'].str.contains('DISPLAY') & df['A/C'].str.contains("DHS|DAK|HS|SPIT")) & (df['A/C'] != 'LHS') & (df['A/C'] != 'LANC'))]I am unsure what to type, to filter the Data, for when the Numerical Value in the BID Column, is the same in the BID column in the next row. And also in addition, only when one of the Aircraft in the A/C Column, where both Numerical Values in the BID Column below and above are the same, is DAK, and excluding that principle, only if in a row for the A/C Column shows DHS Could someone please tell me, what I should add to my Python Code to enable this, it would be much appreciated if someone could.
This is the output I get, when I run the above Python Code in Jupyter Notebook :-
Output:Venue A/C DISPLAY/ Date BID
25 SHUTTLEWORTH DAK DISPLAY NaN 529
55 KEMBLE DAK DISPLAY NaN 461
69 NORTHWICH SPIT DISPLAY 2008-05-10 00:00:00 514
72 POCKLINGTON SPIT DISPLAY 2009-05-10 00:00:00 821
75 BERLIN DAK DISPLAY 2008-05-12 00:00:00 587
78 MILDENHALL SPIT DISPLAY 2009-05-15 00:00:00 920
93 DUXFORD HS DISPLAY NaN 611
103 CRANWELL HS DISPLAY 2008-05-20 00:00:00 44
145 SCARBOROUGH DAK DISPLAY 2008-05-25 00:00:00 610
150 SCARBOROUGH SPIT DISPLAY 2008-05-25 00:00:00 610
151 CORBRIDGE SPIT DISPLAY NaN 353
167 BRIDGEND-CNX SPIT DISPLAY 2008-05-31 00:00:00 527
173 TARRANT RUSHDEN HS DISPLAY NaN 132
174 TARRANT RUSHDEN DAK DISPLAY NaN 132
179 NORTHOLT SPIT DISPLAY 2009-06-05 00:00:00 870
214 BRIZE NORTON HS DISPLAY NaN 939
218 ROPLEY HS DISPLAY 2008-06-13 00:00:00 355
223 THWAITES HS DISPLAY NaN 364
231 ROPLEY HS DISPLAY NaN 355
240 COSFORD HS DISPLAY 2008-06-14 00:00:00 667
241 QUORN HS DISPLAY NaN 314
244 COSFORD DAK DISPLAY 2008-06-14 00:00:00 NaN
260 REDHILL SPIT DISPLAY NaN 686
269 KEMBLE DAK DISPLAY NaN 316
270 KEMBLE HS DISPLAY NaN 316
280 KEMBLE SPIT DISPLAY 2008-06-21 00:00:00 316
285 KEMBLE DAK DISPLAY 2008-06-21 00:00:00 316
317 BRUNTINGTHORPE SPIT DISPLAY NaN 106
329 ELDWICK SPIT DISPLAY NaN 430
333 PLYMOUTH HOE DAK DISPLAY 2008-06-28 00:00:00 528
340 OLD BUCKENHAM HS DISPLAY NaN 424
344 COSFORD SPIT DISPLAY 2008-06-28 00:00:00 68
350 TAIN SPIT DISPLAY NaN 433
355 WITTERING SPIT DISPLAY 2008-07-03 00:00:00 376
362 WADDINGTON HS DISPLAY 2008-07-04 00:00:00 666
372 ANNAN DAK DISPLAY NaN 606
373 ANNAN SPIT DISPLAY NaN 606
375 WADDINGTON HS DISPLAY 2008-07-05 00:00:00 666
389 SHAWBURY SPIT DISPLAY 2008-07-05 00:00:00 183
393 CRANWELL SPIT DISPLAY 2008-07-07 00:00:00 823
399 SWANSEA HS DISPLAY NaN 335
403 DUXFORD LEGENDS HS DISPLAY 2008-07-11 00:00:00 37
405 YEOVILTON HS DISPLAY NaN 549
407 WOODHOUSE HS DISPLAY 2008-07-11 00:00:00 545
429 CAPEL-LE-FERNE HS DISPLAY 2008-07-12 00:00:00 298
430 ARDINGLY(SUSSEX) HS DISPLAY NaN 189
439 CAERNARFON SPIT DISPLAY 2008-07-18 00:00:00 481
445 PENARTH DAK DISPLAY NaN 303
448 FAIRFORD(RIAT) SPIT DISPLAY 2008-07-19 00:00:00 625
455 CHOLMONDELEY CASTLE SPIT DISPLAY NaN 494
459 HAMPSTHWAITE DAK DISPLAY NaN 828
465 ODIHAM(CAS COMM) SPIT DISPLAY 2009-07-21 00:00:00 NaN
469 ODIHAM FAMS DAY SPIT DISPLAY 2008-07-22 00:00:00 6
470 HENLOW FAM DAY SPIT DISPLAY NaN 146
475 SHAWBURY DAK DISPLAY 2008-07-24 00:00:00 188
476 SHAWBURY SPIT DISPLAY 2008-07-24 00:00:00 188
477 COTTESMORE SPIT DISPLAY NaN 757
478 COTTESMORE DAK DISPLAY NaN 757
484 SUNDERLAND SPIT DISPLAY NaN 333
487 EAST FORTUNE SPIT DISPLAY NaN 406
489 WINDERMERE HS DISPLAY 2008-07-25 00:00:00 138
490 WINDERMERE DAK DISPLAY 2008-07-25 00:00:00 138
504 WIGTON DHS DISPLAY 2008-07-26 00:00:00 144
506 WINDERMERE HS DISPLAY 2008-07-26 00:00:00 138
507 WINDERMERE DAK DISPLAY 2008-07-26 00:00:00 138
508 SUNDERLAND HS DISPLAY NaN 333
509 SUNDERLAND DAK DISPLAY NaN 333
511 AUDLEM SPIT DISPLAY 2008-07-26 00:00:00 706
524 LYNEHAM FAM DAY SPIT DISPLAY NaN 662
525 MALVERN SPIT DISPLAY NaN 26
527 DAMYNS HALL SPIT DISPLAY 2008-08-08 00:00:00 766
529 DUXFORD SPIT DISPLAY 2008-08-09 00:00:00 612
530 DAMYNS HALL SPIT DISPLAY NaN 766
534 BLACKPOOL HS DISPLAY NaN 698
540 EASTBOURNE HS DISPLAY 2008-08-13 00:00:00 407
543 EASTBOURNE HS DISPLAY 2008-08-14 00:00:00 407
546 EASTBOURNE HS DISPLAY 2008-08-15 00:00:00 407
548 ROUGHAM SPIT DISPLAY 2009-08-15 00:00:00 551
551 DUXFORD DAK DISPLAY 2008-08-15 00:00:00 613
552 ROUGHAM DAK DISPLAY NaN 551
556 EASTBOURNE HS DISPLAY 2008-08-16 00:00:00 407
564 CROMER SPIT DISPLAY 2008-08-19 00:00:00 139
569 CRANWELL SPIT DISPLAY 2009-08-20 00:00:00 913
578 SHOREHAM SPIT DISPLAY NaN 366
581 RAMSEY SPIT DISPLAY 2009-08-22 00:00:00 896
583 WHITBY DAK DISPLAY 2008-08-22 00:00:00 125
584 WHITBY HS DISPLAY 2008-08-22 00:00:00 125
586 WHITBY HS DISPLAY 2008-08-23 00:00:00 125
587 WHITBY DAK DISPLAY 2008-08-23 00:00:00 125
591 SHOREHAM HS DISPLAY 2008-08-23 00:00:00 366
605 TWINWOOD AIRFIELD DAK DISPLAY 2008-08-30 00:00:00 934
608 ROMSEY DAK DISPLAY NaN 175
613 STONELEIGH PARK HS DISPLAY NaN NaN
614 DUNSFOLD HS DISPLAY NaN 583
622 ROMSEY DAK DISPLAY 2008-08-31 00:00:00 175
623 ROMSEY HS DISPLAY NaN 175
625 STONELEIGH PARK HS DISPLAY NaN 563
629 TWINWOOD AIRFIELD DAK DISPLAY NaN 934
633 PLYMOUTH HOE SPIT DISPLAY 2008-09-03 00:00:00 295
646 PORTRUSH DAK DISPLAY 2008-09-05 00:00:00 76
647 PORTRUSH HS DISPLAY 2008-09-05 00:00:00 76
652 DUXFORD HS DISPLAY NaN 615
659 PORTRUSH HS DISPLAY 2008-09-06 00:00:00 76
660 PORTRUSH DAK DISPLAY 2008-09-06 00:00:00 76
667 GREAT YARMOUTH HS DISPLAY NaN 655
678 ODIHAM SPIT DISPLAY 2008-09-09 00:00:00 137
686 GUERNSEY DAK DISPLAY 2008-09-10 00:00:00 582
687 GUERNSEY HS DISPLAY 2008-09-10 00:00:00 582
688 JERSEY DAK DISPLAY 2008-09-10 00:00:00 581
689 JERSEY HS DISPLAY 2008-09-10 00:00:00 581
711 WOODSTOCK(BLENHEIM) HS DISPLAY 2008-09-12 00:00:00 362
717 MORECAMBE SPIT DISPLAY NaN 368
720 WOODSTOCK DAK DISPLAY 2008-09-13 00:00:00 362
721 WOODSTOCK HS DISPLAY NaN 362
744 NORTHOLT HS DISPLAY 2008-09-17 00:00:00 532
746 GOODWOOD HS DISPLAY 2008-09-18 00:00:00 256
755 GOODWOOD HS DISPLAY 2008-09-19 00:00:00 256
756 NORTHOLT HS DISPLAY 2009-09-19 00:00:00 863
763 KEMBLE HS DISPLAY NaN 330
766 KEMBLE HS DISPLAY 2008-09-20 00:00:00 330
774 SANICOLE DAK DISPLAY NaN 69
776 GOODWOOD HS DISPLAY 2008-09-20 00:00:00 256
777 SANICOLE HS DISPLAY NaN 69
790 SOUTHPORT HS DISPLAY NaN 584
791 SOUTHPORT DAK DISPLAY NaN 584
803 DUXFORD SPIT DISPLAY 2008-10-11 00:00:00 616
Also for example with the filtered data for example, I would like :-Output:145 SCARBOROUGH DAK DISPLAY 2008-05-25 00:00:00 610
150 SCARBOROUGH SPIT DISPLAY 2008-05-25 00:00:00 610
Changed to showing the following, i.e. merging the two lines together :-Output:SCARBOROUGH DS DISPLAY 2008-05-25 00:00:00 610
And Output:173 TARRANT RUSHDEN HS DISPLAY NaN 132
174 TARRANT RUSHDEN DAK DISPLAY NaN 132
Changed to showing :-Output:TARRANT RUSHDEN DHS DISPLAY NaN 132
I mean changed to showing, for all those occurances,Not just for those two Venues.
Best Regards
Eddie Winch