Python Forum
Filtering Excel Document Data Based On Numerical Values
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Filtering Excel Document Data Based On Numerical Values
#1
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 :-

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
Reply
#2
Could someone please help me ?
Reply
#3
Hi there,

My latest Python Code is :-

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'))] 
df["Date"].fillna("No Date", inplace = True)

df['A/C'].unique().tolist()

rename_map = {
    'DAK': 'D',
    'SPIT': 'S',
    'LANC': 'L',
    'HURRI': 'H',
    'PARA': 'P'
}
df['A/C std'] = df['A/C'].replace(rename_map)
print(df['A/C std'].unique().tolist())


selected = df.loc[df['DISPLAY/'] == 'DISPLAY']
#selected = df.loc[df['A/C'].isin(['DS', 'DH', 'DHS', 'SD', 'HSD'])]
groupby_venue_date = selected.groupby(['Venue', 'BID', 'Date', 'DISPLAY/']) 
aircraft = groupby_venue_date['A/C std'].apply(''.join).rename('Aircraft-combined')
def sorted_string(s):
    return ''.join(sorted(s))

def remove_duplicate_chars(s):
    return ''.join(set(s))

aircraft = aircraft.apply(remove_duplicate_chars)
print(aircraft.unique().tolist())

pd.DataFrame(aircraft)
#print(aircraft.shape)
And this is a Sample of the Output :-

Output:
SUNDERLAND 333 2008-07-25 00:00:00 DISPLAY HD No Date DISPLAY HDS SWANSEA 335 2008-07-12 00:00:00 DISPLAY HSL No Date DISPLAY HLS TAIN 433 No Date DISPLAY S TANGMERE 672 No Date DISPLAY H TARRANT RUSHDEN 132 No Date DISPLAY HDS THWAITES 364 No Date DISPLAY HS TWINWOOD AIRFIELD 934 2008-08-30 00:00:00 DISPLAY D No Date DISPLAY D WADDINGTON 666 2008-07-04 00:00:00 DISPLAY HS 2008-07-05 00:00:00 DISPLAY HLS
What do I need to now type, to drop all rows, that don't have a 'D' character in the Aircraft-combined Column, except where there is only a 'D' not followed by anything else or DL or D is with L in another combination i.e. LSHD ? Also how do i change, Where it says HSD or HDS to saying DSH ? or HD to saying DH and where it says SD to saying DS ? in the Aircraft-combined column.

P.S. The Columns for the DataFrame are :-

Venue BID Date DISPLAY/ Aircraft-combined

In that order from Left to Right

And if possible, could someone edit My Output text ?

Any help would be much appreciated

Eddie Winch
Reply
#4
May be try using drop to remove rows with column conditions to your resulting dataframe, with str.match and str.contains to see if that helps you.


indexNames = df[~df['Aircraft-combined'].str.contains('D')].index
df.drop(indexNames , inplace=True)
print(df)#prints all having D and removes remaining rows
df1=pandas.DataFrame(df)
indexNames1 = df1[~df1['Aircraft-combined'].str.match('DL')].index
df1.drop(indexNames1 , inplace=True)
print(df1)#for the filtered df, now it show for matching word DL
Best Regards,
Sandeep

GANGA SANDEEP KUMAR
Reply
#5
Hi GANGA SANDEEP KUMAR,

Many thanks for your help, where do I need to put the Code you show, in my Python Code ?

I put it in the following Order in my Python Code :-

def sorted_string(s):
    return ''.join(sorted(s))

def remove_duplicate_chars(s):
    return ''.join(set(s))

aircraft = aircraft.apply(remove_duplicate_chars)
print(aircraft.unique().tolist())

indexNames = df[~df['A/C'].str.contains('D')].index
df.drop(indexNames , inplace=True)
print(df)#prints all having D and removes remaining rows
df1=pandas.DataFrame(df)
indexNames1 = df1[~df1['A/C'].str.match('DL')].index
df1.drop(indexNames1 , inplace=True)
print(df1)#for the filtered df, now it show for matching word DL

pd.DataFrame(aircraft)
#print(aircraft.shape)
But I get the following Error Traceback :-

Error:
TypeError: bad operand type for unary ~: 'float'
Do you know, what is causing that Error Message ?

Regards

Eddie Winch
Reply
#6
Try adding na=False and test again.

print(df) # before changes
indexNames = df[~df['Aircraft-combined'].str.contains('D',na=False)].index
df.drop(indexNames , inplace=True)
print(df)#prints all having D and removes remaining rows
df1=pandas.DataFrame(df)
indexNames1 = df1[~df1['Aircraft-combined'].str.match('DL',na=False)].index
df1.drop(indexNames1 , inplace=True)
print(df1)#for the filtered df, now it show for matching word DL
i see line 38-40 of your post have output generated and turned to dataframe, so assign that line 40 to name df to re-process output. Also, you mentioned the output dataframe have column names as Venue BID Date DISPLAY/ Aircraft-combined and you need Aircraft-combined dataframe column re-processed i believe.

print(aircraft.unique().tolist())
df=pd.DataFrame(aircraft)
<<add those lines here, with dataframe columnname and conditions as needed>>



Best Regards,
Sandeep

GANGA SANDEEP KUMAR
Reply
#7
Hi Sandeep, In the Code you kindly posted, I had to Change :-

indexNames = df[~df['Aircraft-combined'].str.contains('D',na=False)].index
And

indexNames1 = df1[~df1['Aircraft-combined'].str.match('DL',na=False)].index
To :-

indexNames = df[~df['A/C'].str.contains('D',na=False)].index
And

indexNames1 = df1[~df1['A/C'].str.match('DL',na=False)].index
Which did give an output, when I ran the Python Code. If I left Aircraft-combined for both lines of text, I got the following Traceback Error :-

Error:
KeyError: 'Aircraft-combined'
Here is the Website Link, to the .xls File i.e. Excel Document File I am using :-

http://web.archive.org/web/2009080423493...6AA506.xls

Here is the Python Code in Full :-

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'))] 
df["Date"].fillna("No Date", inplace = True)

df['A/C'].unique().tolist()

rename_map = {
    'DAK': 'D',
    'SPIT': 'S',
    'LANC': 'L',
    'HURRI': 'H',
    'PARA': 'P'
}
df['A/C std'] = df['A/C'].replace(rename_map)
print(df['A/C std'].unique().tolist())

selected = df.loc[df['DISPLAY/'] == 'DISPLAY']
#selected = df.loc[df['A/C'].isin(['DS', 'DH', 'DHS', 'SD', 'HSD'])]
groupby_venue_date = selected.groupby(['Venue', 'BID', 'Date', 'DISPLAY/']) 
aircraft = groupby_venue_date['A/C std'].apply(''.join).rename('Aircraft-combined')
def sorted_string(s):
    return ''.join(sorted(s))

def remove_duplicate_chars(s):
    return ''.join(set(s))

indexNames = df[~df['A/C'].str.contains('D',na=False)].index
df.drop(indexNames , inplace=True)
print(df)#prints all having D and removes remaining rows
df1=pandas.DataFrame(df)
indexNames1 = df1[~df1['A/C'].str.match('DL',na=False)].index
df1.drop(indexNames1 , inplace=True)
print(df1)#for the filtered df, now it show for matching word DL

pd.DataFrame(aircraft)
#print(aircraft.shape)
Could you run that Code in Jupyter Notebook, and see the Output I got ? And adapt the Code accordingly ?

You will need to change the following in the Code :-

xls = pd.ExcelFile(r'C:\Users\Edward\Desktop\BBMF Schedules And Master Forum Thread Texts\BBMF Display Schedule 2009.xls')
To whatever Path, Directories, and the name you use for the .xls File.

With the output I get, your coding doesn't seem to have made the changes I want, unless the coding is in the wrong order ?

Eddie Winch
Reply
#8
May be we missed something, try below to see if that helps,

import pandas as pd

#i renamed file, please re-add your filename and location on below line  
xls = pd.ExcelFile(r'C:\Users\testuser\Downloads\DL\book12.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'))] 
df["Date"].fillna("No Date", inplace = True)
 
df['A/C'].unique().tolist()
 
rename_map = {
    'DAK': 'D',
    'SPIT': 'S',
    'LANC': 'L',
    'HURRI': 'H',
    'PARA': 'P'
}
df['A/C std'] = df['A/C'].replace(rename_map)
print(df['A/C std'].unique().tolist())
 
selected = df.loc[df['DISPLAY/'] == 'DISPLAY']
#selected = df.loc[df['A/C'].isin(['DS', 'DH', 'DHS', 'SD', 'HSD'])]
groupby_venue_date = selected.groupby(['Venue', 'BID', 'Date', 'DISPLAY/']) 
aircraft = groupby_venue_date['A/C std'].apply(''.join).rename('Aircraft-combined')
def sorted_string(s):
    return ''.join(sorted(s))
 
def remove_duplicate_chars(s):
    return ''.join(set(s))
 

#added lines from here
print("****************")


df=pd.DataFrame(aircraft)
print(df) #original df

print("*****************")

indexNames = df[~df['Aircraft-combined'].str.contains('D',na=False)].index
df.drop(indexNames , inplace=True)
print(df)#prints all rows having D and removes remaining rows


print("*****************")


df['Aircraft-combined']=df['Aircraft-combined'].replace(to_replace='SD', value='DS')
df['Aircraft-combined']=df['Aircraft-combined'].replace(to_replace=['HSD','HDS'], value='DHS')
print(df)#replaces the SD with DS and HSD/HDS with DHS

print("*********************")
Output:
python test1.py [nan, 'L', 'S', 'H', 'LHS', 'D', 'HS', 'P', 'LSSD', 'LSS', 'SS', 'LH', 'DH', 'DHS', 'SSSHH'] **************** Aircraft-combined Venue BID Date DISPLAY/ ANNAN 606 No Date DISPLAY DS ARDINGLY(SUSSEX) 189 No Date DISPLAY HS AUDLEM 706 2008-07-26 00:00:00 DISPLAY S AYLSHAM 717 2008-08-31 00:00:00 DISPLAY LHS BEAULIEU 162 2008-05-25 00:00:00 DISPLAY H No Date DISPLAY H BELTRING 440 2008-07-26 00:00:00 DISPLAY L No Date DISPLAY L BENSON 761 2008-08-27 00:00:00 DISPLAY LHS BERLIN 587 2008-05-12 00:00:00 DISPLAY D BIGGIN HILL 620 2008-06-28 00:00:00 DISPLAY LHS No Date DISPLAY LHS BLACKPOOL 698 No Date DISPLAY HSL BOURNEMOUTH 336 2008-08-20 00:00:00 DISPLAY LHS 2008-08-21 00:00:00 DISPLAY LHS 2008-08-22 00:00:00 DISPLAY LHS 2008-08-23 00:00:00 DISPLAY L BRADFIELD-BERKS 436 No Date DISPLAY L BRIDGEND-CNX 527 2008-05-31 00:00:00 DISPLAY S BRIZE NORTON 939 No Date DISPLAY HS BRUNTINGTHORPE 106 No Date DISPLAY S CAERNARFON 481 2008-07-18 00:00:00 DISPLAY S CAMBRIDGE 180 No Date DISPLAY L CAPEL-LE-FERNE 298 2008-07-12 00:00:00 DISPLAY HS CHOLMONDELEY CASTLE 494 No Date DISPLAY S CLACTON 405 2008-08-28 00:00:00 DISPLAY LHS No Date DISPLAY LHS CORBRIDGE 353 No Date DISPLAY S COSFORD 68 2008-06-28 00:00:00 DISPLAY S 667 2008-06-14 00:00:00 DISPLAY HS No Date DISPLAY L COTTESMORE 757 No Date DISPLAY SD CRANWELL 44 2008-05-20 00:00:00 DISPLAY HS 112 No Date DISPLAY L 823 2008-07-07 00:00:00 DISPLAY S 913 2009-08-20 00:00:00 DISPLAY S CROMER 139 2008-08-19 00:00:00 DISPLAY S DAMYNS HALL 766 2008-08-08 00:00:00 DISPLAY S No Date DISPLAY S DARTMOUTH 83 2008-08-26 00:00:00 DISPLAY LHS DAWLISH 650 2008-08-20 00:00:00 DISPLAY LHS DUNSCORE 600 No Date DISPLAY LHS DUNSFOLD 583 No Date DISPLAY HS DUXFORD 611 No Date DISPLAY LHS 612 2008-08-09 00:00:00 DISPLAY S 613 2008-08-15 00:00:00 DISPLAY D 615 2008-09-06 00:00:00 DISPLAY LHS No Date DISPLAY HSL 616 2008-10-11 00:00:00 DISPLAY S DUXFORD LEGENDS 37 2008-07-11 00:00:00 DISPLAY HS 795 No Date DISPLAY L EAST FORTUNE 406 No Date DISPLAY DHS EAST KIRKBY 28 No Date DISPLAY L EASTBOURNE 407 2008-08-13 00:00:00 DISPLAY LHS 2008-08-14 00:00:00 DISPLAY LHS 2008-08-15 00:00:00 DISPLAY LHS 2008-08-16 00:00:00 DISPLAY LHS ELDWICK 430 No Date DISPLAY S FAIRFORD(RIAT) 625 2008-07-19 00:00:00 DISPLAY SLH No Date DISPLAY LHS GAYDON 129 No Date DISPLAY L GOODWOOD 256 2008-09-18 00:00:00 DISPLAY HS 2008-09-19 00:00:00 DISPLAY HS 2008-09-20 00:00:00 DISPLAY HS GOTHERINGTON 97 2008-05-23 00:00:00 DISPLAY LHS GREAT YARMOUTH 655 2008-09-05 00:00:00 DISPLAY LHS No Date DISPLAY HSL GUERNSEY 582 2008-09-10 00:00:00 DISPLAY DHS HAMPSTHWAITE 828 No Date DISPLAY D HENLOW FAM DAY 146 No Date DISPLAY S ISLAND OF SHEPPY 575 2008-05-02 00:00:00 DISPLAY LHS JERSEY 581 2008-09-10 00:00:00 DISPLAY DHS KEMBLE 316 2008-06-21 00:00:00 DISPLAY SD No Date DISPLAY DHS 330 2008-09-20 00:00:00 DISPLAY HS No Date DISPLAY HSLL 461 No Date DISPLAY D KIDWELLY 59 No Date DISPLAY L KIMBOLTON CASTLE 719 No Date DISPLAY L LEEMING 687 No Date DISPLAY LHS LEUCHARS 573 2008-09-12 00:00:00 DISPLAY LHS LITTLE GRANSDEN 369 No Date DISPLAY L LOWESTOFT 95 2008-07-24 00:00:00 DISPLAY LHS No Date DISPLAY LHS LYNEHAM FAM DAY 662 No Date DISPLAY S MALVERN 26 No Date DISPLAY S MARGATE 332 2008-06-21 00:00:00 DISPLAY LHS No Date DISPLAY LHS MARHAM FAM DAY 663 2008-07-23 00:00:00 DISPLAY LHS MILDENHALL 920 2009-05-15 00:00:00 DISPLAY S MOREBATTLE 601 No Date DISPLAY LHS MORECAMBE 368 2008-09-13 00:00:00 DISPLAY LH No Date DISPLAY S NORTHOLT 532 2008-09-17 00:00:00 DISPLAY HS 863 2009-09-19 00:00:00 DISPLAY HS 870 2009-06-05 00:00:00 DISPLAY S NORTHWICH 514 2008-05-10 00:00:00 DISPLAY S ODIHAM 137 2008-09-09 00:00:00 DISPLAY S ODIHAM FAMS DAY 6 2008-07-22 00:00:00 DISPLAY S OLD BUCKENHAM 424 No Date DISPLAY HSL PENARTH 303 No Date DISPLAY D PLYMOUTH HOE 295 2008-09-03 00:00:00 DISPLAY S 528 2008-06-28 00:00:00 DISPLAY D POCKLINGTON 821 2009-05-10 00:00:00 DISPLAY S PORTRUSH 76 2008-09-05 00:00:00 DISPLAY DHS 2008-09-06 00:00:00 DISPLAY HSD QUORN 314 No Date DISPLAY LHS RAMSEY 896 2009-08-22 00:00:00 DISPLAY S REDHILL 686 No Date DISPLAY S RHYL 647 No Date DISPLAY LHS ROCHESTER 297 No Date DISPLAY L ROMSEY 175 2008-08-31 00:00:00 DISPLAY D No Date DISPLAY DHS ROPLEY 355 2008-06-13 00:00:00 DISPLAY HS No Date DISPLAY HS ROUGHAM 551 2009-08-15 00:00:00 DISPLAY S No Date DISPLAY DL SANICOLE 69 No Date DISPLAY DHS SCARBOROUGH 610 2008-05-25 00:00:00 DISPLAY DS SHAWBURY 183 2008-07-05 00:00:00 DISPLAY S 188 2008-07-24 00:00:00 DISPLAY DS SHOREHAM 366 2008-08-23 00:00:00 DISPLAY HS No Date DISPLAY LHSL SHUTTLEWORTH 529 No Date DISPLAY D SOUTHEND 585 2008-05-25 00:00:00 DISPLAY LHS No Date DISPLAY LHS SOUTHPORT 584 No Date DISPLAY HSD STONELEIGH PARK 563 No Date DISPLAY HS SUNDERLAND 333 2008-07-25 00:00:00 DISPLAY DH No Date DISPLAY SHSD SWANSEA 335 2008-07-12 00:00:00 DISPLAY LHS No Date DISPLAY HSL TAIN 433 No Date DISPLAY S TANGMERE 672 No Date DISPLAY H TARRANT RUSHDEN 132 No Date DISPLAY HSD THWAITES 364 No Date DISPLAY HS TWINWOOD AIRFIELD 934 2008-08-30 00:00:00 DISPLAY D No Date DISPLAY D WADDINGTON 666 2008-07-04 00:00:00 DISPLAY HS 2008-07-05 00:00:00 DISPLAY HSL No Date DISPLAY L WATERBEACH 653 2008-05-30 00:00:00 DISPLAY LHS WATERBEACH(RV HS) 735 2008-06-13 00:00:00 DISPLAY L WHITBY 125 2008-08-22 00:00:00 DISPLAY DHS 2008-08-23 00:00:00 DISPLAY HSD WIGTON 144 2008-07-26 00:00:00 DISPLAY DHS WINDERMERE 138 2008-07-25 00:00:00 DISPLAY HSD 2008-07-26 00:00:00 DISPLAY HSD WITTERING 376 2008-07-03 00:00:00 DISPLAY S WOODHOUSE 545 2008-07-11 00:00:00 DISPLAY HS WOODSTOCK 362 2008-09-13 00:00:00 DISPLAY D No Date DISPLAY HS WOODSTOCK(BLENHEIM) 362 2008-09-12 00:00:00 DISPLAY HS WOODVALE 509 2008-06-06 00:00:00 DISPLAY H WYTON 147 No Date DISPLAY LHS YEOVILTON 549 No Date DISPLAY HS ***************** Aircraft-combined Venue BID Date DISPLAY/ ANNAN 606 No Date DISPLAY DS BERLIN 587 2008-05-12 00:00:00 DISPLAY D COTTESMORE 757 No Date DISPLAY SD DUXFORD 613 2008-08-15 00:00:00 DISPLAY D EAST FORTUNE 406 No Date DISPLAY DHS GUERNSEY 582 2008-09-10 00:00:00 DISPLAY DHS HAMPSTHWAITE 828 No Date DISPLAY D JERSEY 581 2008-09-10 00:00:00 DISPLAY DHS KEMBLE 316 2008-06-21 00:00:00 DISPLAY SD No Date DISPLAY DHS 461 No Date DISPLAY D PENARTH 303 No Date DISPLAY D PLYMOUTH HOE 528 2008-06-28 00:00:00 DISPLAY D PORTRUSH 76 2008-09-05 00:00:00 DISPLAY DHS 2008-09-06 00:00:00 DISPLAY HSD ROMSEY 175 2008-08-31 00:00:00 DISPLAY D No Date DISPLAY DHS ROUGHAM 551 No Date DISPLAY DL SANICOLE 69 No Date DISPLAY DHS SCARBOROUGH 610 2008-05-25 00:00:00 DISPLAY DS SHAWBURY 188 2008-07-24 00:00:00 DISPLAY DS SHUTTLEWORTH 529 No Date DISPLAY D SOUTHPORT 584 No Date DISPLAY HSD SUNDERLAND 333 2008-07-25 00:00:00 DISPLAY DH No Date DISPLAY SHSD TARRANT RUSHDEN 132 No Date DISPLAY HSD TWINWOOD AIRFIELD 934 2008-08-30 00:00:00 DISPLAY D No Date DISPLAY D WHITBY 125 2008-08-22 00:00:00 DISPLAY DHS 2008-08-23 00:00:00 DISPLAY HSD WIGTON 144 2008-07-26 00:00:00 DISPLAY DHS WINDERMERE 138 2008-07-25 00:00:00 DISPLAY HSD 2008-07-26 00:00:00 DISPLAY HSD WOODSTOCK 362 2008-09-13 00:00:00 DISPLAY D ***************** Aircraft-combined Venue BID Date DISPLAY/ ANNAN 606 No Date DISPLAY DS BERLIN 587 2008-05-12 00:00:00 DISPLAY D COTTESMORE 757 No Date DISPLAY DS DUXFORD 613 2008-08-15 00:00:00 DISPLAY D EAST FORTUNE 406 No Date DISPLAY DHS GUERNSEY 582 2008-09-10 00:00:00 DISPLAY DHS HAMPSTHWAITE 828 No Date DISPLAY D JERSEY 581 2008-09-10 00:00:00 DISPLAY DHS KEMBLE 316 2008-06-21 00:00:00 DISPLAY DS No Date DISPLAY DHS 461 No Date DISPLAY D PENARTH 303 No Date DISPLAY D PLYMOUTH HOE 528 2008-06-28 00:00:00 DISPLAY D PORTRUSH 76 2008-09-05 00:00:00 DISPLAY DHS 2008-09-06 00:00:00 DISPLAY DHS ROMSEY 175 2008-08-31 00:00:00 DISPLAY D No Date DISPLAY DHS ROUGHAM 551 No Date DISPLAY DL SANICOLE 69 No Date DISPLAY DHS SCARBOROUGH 610 2008-05-25 00:00:00 DISPLAY DS SHAWBURY 188 2008-07-24 00:00:00 DISPLAY DS SHUTTLEWORTH 529 No Date DISPLAY D SOUTHPORT 584 No Date DISPLAY DHS SUNDERLAND 333 2008-07-25 00:00:00 DISPLAY DH No Date DISPLAY SHSD TARRANT RUSHDEN 132 No Date DISPLAY DHS TWINWOOD AIRFIELD 934 2008-08-30 00:00:00 DISPLAY D No Date DISPLAY D WHITBY 125 2008-08-22 00:00:00 DISPLAY DHS 2008-08-23 00:00:00 DISPLAY DHS WIGTON 144 2008-07-26 00:00:00 DISPLAY DHS WINDERMERE 138 2008-07-25 00:00:00 DISPLAY DHS 2008-07-26 00:00:00 DISPLAY DHS WOODSTOCK 362 2008-09-13 00:00:00 DISPLAY D *********************
Best Regards,
Sandeep

GANGA SANDEEP KUMAR
Reply
#9
Many thanks Sandeep,

I really appreciate your help.

Eddie Winch
Reply
#10
Hi Sandeep,

Thankyou so much for sorting this out for me, you have almost cracked it !

What needs to be added, or changed in the code i.e. :-

So that only the following Data Displays, not all the other Data :-

Output:
Aircraft-combined Venue BID Date DISPLAY/ ANNAN 606 No Date DISPLAY DS BERLIN 587 2008-05-12 00:00:00 DISPLAY D COTTESMORE 757 No Date DISPLAY DS DUXFORD 613 2008-08-15 00:00:00 DISPLAY D EAST FORTUNE 406 No Date DISPLAY DHS GUERNSEY 582 2008-09-10 00:00:00 DISPLAY DHS HAMPSTHWAITE 828 No Date DISPLAY D JERSEY 581 2008-09-10 00:00:00 DISPLAY DHS KEMBLE 316 2008-06-21 00:00:00 DISPLAY DS No Date DISPLAY DHS 461 No Date DISPLAY D PENARTH 303 No Date DISPLAY D PLYMOUTH HOE 528 2008-06-28 00:00:00 DISPLAY D PORTRUSH 76 2008-09-05 00:00:00 DISPLAY DHS 2008-09-06 00:00:00 DISPLAY DHS ROMSEY 175 2008-08-31 00:00:00 DISPLAY D No Date DISPLAY DHS ROUGHAM 551 No Date DISPLAY DL SANICOLE 69 No Date DISPLAY DHS SCARBOROUGH 610 2008-05-25 00:00:00 DISPLAY DS SHAWBURY 188 2008-07-24 00:00:00 DISPLAY DS SHUTTLEWORTH 529 No Date DISPLAY D SOUTHPORT 584 No Date DISPLAY DHS SUNDERLAND 333 2008-07-25 00:00:00 DISPLAY DH No Date DISPLAY SHSD TARRANT RUSHDEN 132 No Date DISPLAY DHS TWINWOOD AIRFIELD 934 2008-08-30 00:00:00 DISPLAY D No Date DISPLAY D WHITBY 125 2008-08-22 00:00:00 DISPLAY DHS 2008-08-23 00:00:00 DISPLAY DHS WIGTON 144 2008-07-26 00:00:00 DISPLAY DHS WINDERMERE 138 2008-07-25 00:00:00 DISPLAY DHS 2008-07-26 00:00:00 DISPLAY DHS WOODSTOCK 362 2008-09-13 00:00:00 DISPLAY D ​
But in the 'pretty' version Font in Jupyter Notebook ? And also I want all Solo, 'D' in the Aircraft-combined Column Rows got rid of i.e. :-

BERLIN 587 2008-05-12 00:00:00 DISPLAY D

WOODSTOCK 362 2008-09-13 00:00:00 DISPLAY D

etc

Regards

Eddie
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Copy Paste excel files based on the first letters of the file name Viento 2 348 Feb-07-2024, 12:24 PM
Last Post: Viento
  Unexpected Output - Python Dataframes: Filtering based on Overlapping Dates Xensor 5 656 Nov-15-2023, 06:54 PM
Last Post: deanhystad
  Search Excel File with a list of values huzzug 4 1,147 Nov-03-2023, 05:35 PM
Last Post: huzzug
  What data types can I use for default values? Mark17 1 485 Oct-09-2023, 02:07 PM
Last Post: buran
  Copy data from Excel and paste into Discord (Midjourney) Joe_Wright 4 1,926 Jun-06-2023, 05:49 PM
Last Post: rajeshgk
  restrict user input to numerical values MCL169 2 869 Apr-08-2023, 05:40 PM
Last Post: MCL169
  Reading data from excel file –> process it >>then write to another excel output file Jennifer_Jone 0 1,047 Mar-14-2023, 07:59 PM
Last Post: Jennifer_Jone
  Grouping Data based on 30% bracket purnima1 4 1,142 Mar-10-2023, 07:38 PM
Last Post: deanhystad
Question Inserting Numerical Value to the Element in Optionlist and Printing it into Entry drbilgehanbakirhan 1 777 Jan-30-2023, 05:16 AM
Last Post: deanhystad
  How to properly format rows and columns in excel data from parsed .txt blocks jh67 7 1,802 Dec-12-2022, 08:22 PM
Last Post: jh67

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020