Posts: 218
Threads: 27
Joined: May 2018
Jan-06-2020, 06:06 PM
(This post was last modified: Jan-06-2020, 06:06 PM by eddywinch82.)
Hi there,
I have a Python Code, which runs fine, but I want the Correct complete Date shown, in the Date Column, in each Row of the DataFrame Output.
Due to the layout of the Schedule Table, at that particular URL, only the day is shown in the Date Column, in each Row displayed.
Here is the Code :-
import pandas as pd
import requests
from bs4 import BeautifulSoup
res = requests.get("http://web.archive.org/web/20041020000138/http://www.raf.mod.uk/bbmf/displaydates.html")
soup = BeautifulSoup(res.content,'lxml')
table = soup.find_all('table', align="CENTER")[0]
df = pd.read_html(str(table))
df = df[0]
df = df.rename(columns=df.iloc[0])
df = df.iloc[2:]
df.head(15)
pd.options.display.max_rows = 1000
display = df[(df['Location'].str.contains('[a-zA-Z]')) & (df['Lancaster'].str.contains('X')) & (df['Spitfire'].str.contains('X', na=True)) & (df['Dakota'].str.contains('X', na=True))]
display
#display.drop('Dakota', axis=1, inplace=True)
display.dropna(subset=['Spitfire', 'Hurricane'], how='all') And this is the Output :-
Output: Date Location Lancaster Spitfire Hurricane Dakota
21 14 Digby X X X NaN
22 15 Harlaxton X X X NaN
23 15 Coleraine X X X NaN
30 16 New Leake X X X X
31 16 Blackpool X X X NaN
32 16 Elvington X X X NaN
46 30 Conington X X X NaN
47 30 Enfield X X X NaN
48 30 Southend X X X NaN
51 31 Southend X X X NaN
53 31 Sharnbrook X X X NaN
54 31 Peterborough X X X NaN
62 5 Portsmouth X X x 2 NaN NaN
63 5 Sandown (IoW) X X x 2 NaN NaN
64 5 Yarmouth Harbour (IoW) X X x 2 NaN NaN
68 6 Newhaven Fort X X x 2 NaN NaN
71 6 Westerham X X x 2 NaN NaN
72 6 Arromanche X X x 2 NaN NaN
75 7 Normandy X X x 2 NaN X
79 12 Digby X X NaN NaN
80 12 Bracebridge Heath X X NaN NaN
81 12 Newark X X x 2 X X
82 12 Castle Donington X X NaN NaN
87 13 Barrow in Furness X X X NaN
88 13 Cosford X X X X
89 13 Loughborough X X X NaN
91 13 Little Bytham X X NaN NaN
128 26 Woodston X X X NaN
129 26 Barrowden X X X NaN
130 26 Skillington X X X NaN
131 26 East Bridgeford X X X NaN
132 26 Waddington X X X X
139 27 Waddington X X X X
151 3 Tattershall X X NaN NaN
162 3 Branston X X X NaN
163 3 Ripley Castle X X X NaN
175 4 Spilsby X X NaN NaN
185 10 East Fortune X X X NaN
186 10 Thirsk X X X NaN
203 14 Culdrose X X X NaN
204 14 Shrivenham X X X NaN
208 17 Old Brampton X X X NaN
209 17 Glenridding X X X NaN
210 17 Carlisle X X X NaN
219 23 Llandiloes X X X X
220 23 Benson X NaN X NaN
221 24 Farnborough X X X NaN
222 24 North Coates X X X NaN
223 24 Scunthorpe X X X NaN
224 24 Breighton X X X NaN
225 24 Sunderland X X X NaN
226 25 Sunderland X X X NaN
227 25 York X X X NaN
228 25 Breighton X X X NaN
229 25 Luddington X X X NaN
230 25 North Coates X X X NaN
231 25 Sutton on Sea X X X NaN
235 29 Lowestoft X X X NaN
236 30 Lowestoft X X X NaN
240 31 Lincoln Showground X X X NaN
241 31 Kielder Forest X X X NaN
242 31 Windermere X X X NaN
244 1 Windermere X X X NaN
245 1 Cudworth X X X NaN
246 1 Doncaster X X X NaN
247 1 Lincoln Showground X X X NaN
250 13 Eastbourne X X X NaN
251 14 Eastbourne X X X NaN
258 15 Eastbourne X X X NaN
260 15 Barnack X X X NaN
271 21 Shepway X X X NaN
274 22 Falaise X X NaN NaN
277 27 Dartmouth X X X NaN
278 28 Henstridge X X X NaN
279 28 Shoreham X X X NaN
280 28 Kent Showground X X X NaN
281 29 Shoreham X X X NaN
285 29 Little Gransden X X X NaN
289 30 Aylsham X X X NaN
291 30 Grimsthorpe X NaN X NaN
303 4 Duxford X X X NaN
304 4 Goodwood X X X NaN
305 4 Fareham X X X NaN
306 5 Goodwood X X X NaN
308 5 Duxford X X X NaN
315 9 Guernsey X X X X
316 9 Jersey X X X X
319 11 Southport X X X NaN
320 11 Morecambe X X X NaN
321 11 Leuchars X X X NaN
325 12 Morecambe X X X NaN
326 12 Southport X X X NaN
350 18 Yeovilton X X X NaN
351 18 Little Casterton X X X NaN
364 25 Lichfield X X NaN NaN
If you type, and go to the Url, you will see where the Month Is displayed. What should I type, so that all the full Dates are shown, in the Date Column ?
For example for the Venue Ripley the date would show as, 02/05/2004
For Blackpool it would show as 16/05/2004
And that pattern, supplied for every Row, in the Output DataFrame ?
Sorry in the Output I posted, the layout is incorrect. If you run the Code, in Jupyter Notebook, you will see,
what I was trying to post.
Any help would be appreciated
Regards
Eddie Winch
Posts: 50
Threads: 2
Joined: Nov 2019
Jan-08-2020, 07:14 AM
(This post was last modified: Jan-08-2020, 07:14 AM by sandeep_ganga.)
I manually entered year, column names information for code to process in beforehand, I tried something like below, I wish, hope it works. Any edits and ideas to shorten are welcome.
import pandas as pd
import requests
from bs4 import BeautifulSoup
res = requests.get("http://web.archive.org/web/20041020000138/http://www.raf.mod.uk/bbmf/displaydates.html")
soup = BeautifulSoup(res.content,'lxml')
table = soup.find_all('table', align="CENTER")[0]
df = pd.read_html(str(table))
df = df[0]
##################
##################
##################
#make df[0] to list
list=[]
for i in df[0]:
list.append(i)
#reverse the list to make split to sublist easier
list.reverse()
#split list to sublist using condition len(val)> 2
size = len(list)
idx_list = [idx + 1 for idx, val in
enumerate(list) if len(val) > 2]
res = [list[i: j] for i, j in
zip([0] + idx_list, idx_list +
([size] if idx_list[-1] != size else []))]
#make monthname to numbers and print
for i in res:
for j in range(len(i)):
if i[j].upper()=='JUNE':
i[j]='6'
elif i[j].upper() =='MAY':
i[j]='5'
elif i[j].upper() == 'APRIL':
i[j]='4'
elif i[j].upper() =='JANUARY':
i[j]='1'
elif i[j].upper() == 'FEBRUARY':
i[j]='2'
elif i[j].upper() =='MARCH':
i[j]='3'
elif i[j].upper() == 'JULY':
i[j]='7'
elif i[j].upper() =='AUGUST':
i[j]='8'
elif i[j].upper() == 'SEPTEMBER':
i[j]='9'
elif i[j].upper() =='OCTOBER':
i[j]='10'
elif i[j].upper() == 'NOVEMBER':
i[j]='11'
elif i[j].upper() =='DECEMBER':
i[j]='12'
#append string and append to new list
finallist=[]
for i in res:
for j in range(len(i)):
if j < len(i) - 1:
#print(f'2004-{i[-1]}-{i[j]}')
finallist.append(f'2004-{i[-1]}-{i[j]}')
#print(finallist)
finallist.reverse()
print("\n=== ORIGINAL DF ===\n")
print(df)
#convert dataframe to list
listtemp1=df.values.tolist()
#replace found below values with 0000_removable
removelist=['LOCATION','LANCASTER','SPITFIRE','HURRICANE','DAKOTA','DATE','JUNE','JANUARY','FEBRUARY','MARCH','MAY','JULY','AUGUST','SEPTEMBER','OCTOBER','NOVEMBER','DECEMBER','APRIL']
for i in listtemp1:
for j in range(len(i)):
for place in removelist:
if str(i[j]).upper()==place:
i[j]='0000_removable'
else:
pass
#remove sublists with the replaced values we redirected
dellist=['0000_removable', '0000_removable', '0000_removable', '0000_removable', '0000_removable', '0000_removable']
res = [i for i in listtemp1 if i != dellist]
#assign back to dataframe DF3
df3=pd.DataFrame()
df3=pd.DataFrame(res, columns=['Date','LOCATION','LANCASTER','SPITFIRE','HURRICANE','DAKOTA'])
print("\n=== AFTER REMOVE month and column names from DF, assigned to new as DF3 ===\n")
print(df3)
#now assign that sorted date list to dataframe DF3
idx = 0
df3.insert(loc=idx, column='EDITED_DATE_FORMAT', value=finallist)
pd.options.display.max_rows = 500
print("\n=== FINAL DF3 after joining the edited date format column list ===\n")
print(df3)
#validation logic if needed compare processed date from new joined "edited_Date_format" column with already existing "Date" column
#df3['ED1']= pd.to_datetime(df3['EDITED_DATE_FORMAT'],format='%Y-%m-%d').dt.day
#df3['validation of date'] = df3.apply(lambda x: str(x['ED1']) == x['Date'], axis=1)
#convert df3['EDITED_DATE_FORMAT'] column from object to datetime64 foramt
#df3['EDITED_DATE_FORMAT']= pd.to_datetime(df3['EDITED_DATE_FORMAT'],format='%Y-%m-%d')
##################
##################
##################
df = df.rename(columns=df.iloc[0])
df = df.iloc[2:]
df.head(15)
pd.options.display.max_rows = 1000
display = df[(df['Location'].str.contains('[a-zA-Z]')) & (df['Lancaster'].str.contains('X')) & (df['Spitfire'].str.contains('X', na=True)) & (df['Dakota'].str.contains('X', na=True))]
display
#display.drop('Dakota', axis=1, inplace=True)
#display.dropna(subset=['Spitfire', 'Hurricane'], how='all')
#print(display) Output: python test3.py
=== ORIGINAL DF ===
0 1 2 3 4 5
0 Date Location Lancaster Spitfire Hurricane Dakota
1 April April April April April April
2 25 Waddington X NaN NaN NaN
3 25 Pollington X NaN NaN NaN
4 May May May May May May
.. ... ... ... ... ... ...
363 25 Bovingdon NaN X NaN NaN
364 25 Lichfield X X NaN NaN
365 25 Grimsby X NaN NaN NaN
366 26 Finningley NaN X NaN NaN
367 26 Flixton X NaN NaN NaN
[368 rows x 6 columns]
=== AFTER REMOVE month and column names from DF, assigned to new as DF3 ===
Date LOCATION LANCASTER SPITFIRE HURRICANE DAKOTA
0 25 Waddington X NaN NaN NaN
1 25 Pollington X NaN NaN NaN
2 2 Ripley NaN X NaN X
3 2 Abingdon NaN X NaN NaN
4 2 Horley NaN NaN NaN X
.. ... ... ... ... ... ...
356 25 Bovingdon NaN X NaN NaN
357 25 Lichfield X X NaN NaN
358 25 Grimsby X NaN NaN NaN
359 26 Finningley NaN X NaN NaN
360 26 Flixton X NaN NaN NaN
[361 rows x 6 columns]
=== FINAL DF3 after joining the edited date format column list ===
EDITED_DATE_FORMAT Date LOCATION LANCASTER SPITFIRE HURRICANE DAKOTA
0 2004-4-25 25 Waddington X NaN NaN NaN
1 2004-4-25 25 Pollington X NaN NaN NaN
2 2004-5-2 2 Ripley NaN X NaN X
3 2004-5-2 2 Abingdon NaN X NaN NaN
4 2004-5-2 2 Horley NaN NaN NaN X
5 2004-5-2 2 Waddington NaN NaN NaN X
6 2004-5-3 3 Woodmancote NaN X X NaN
7 2004-5-3 3 Wakefield NaN X X NaN
8 2004-5-4 4 Manchester NaN NaN NaN X
9 2004-5-5 5 Assen X NaN NaN NaN
10 2004-5-8 8 Langar 13:10 X NaN NaN NaN
11 2004-5-8 8 Faldingworth 13:30 X NaN NaN NaN
12 2004-5-8 8 Hibaldstow NaN X NaN NaN
13 2004-5-8 8 Haworth NaN X NaN NaN
14 2004-5-8 8 Stoke Goldington NaN NaN NaN X
15 2004-5-9 9 Northwich NaN X NaN NaN
16 2004-5-9 9 Jersey NaN NaN NaN X
17 2004-5-9 9 Stoke Goldington NaN NaN NaN X
18 2004-5-14 14 Digby X X X NaN
19 2004-5-15 15 Harlaxton X X X NaN
20 2004-5-15 15 Coleraine X X X NaN
21 2004-5-15 15 Coltishall NaN NaN X NaN
22 2004-5-15 15 Tilford NaN X NaN X
23 2004-5-15 15 Abingdon NaN X NaN X
24 2004-5-15 15 Hyde Heath Village NaN X NaN X
25 2004-5-16 16 Hardwick Hall NaN NaN NaN X
26 2004-5-16 16 Woodhall Spa X NaN NaN X
27 2004-5-16 16 New Leake X X X X
28 2004-5-16 16 Blackpool X X X NaN
29 2004-5-16 16 Elvington X X X NaN
30 2004-5-16 16 Kelstern X NaN NaN NaN
31 2004-5-19 19 Waterbeach NaN X NaN NaN
32 2004-5-20 20 Shrivenham X NaN NaN NaN
33 2004-5-20 20 High Wycombe X NaN NaN NaN
34 2004-5-21 21 Halton NaN X NaN NaN
35 2004-5-22 22 Newton Linford NaN NaN NaN X
36 2004-5-22 22 Sedbergh NaN NaN NaN X
37 2004-5-22 22 Newark NaN NaN NaN X
38 2004-5-23 23 Aldbourne NaN NaN NaN X
39 2004-5-23 23 Newark X NaN NaN NaN
40 2004-5-23 23 Waddington X NaN NaN NaN
41 2004-5-29 29 Perth NaN NaN NaN X
42 2004-5-29 29 Blackpool NaN NaN NaN X
43 2004-5-30 30 Conington X X X NaN
44 2004-5-30 30 Enfield X X X NaN
45 2004-5-30 30 Southend X X X NaN
46 2004-5-31 31 Falmouth NaN NaN NaN X
47 2004-5-31 31 Corbridge NaN X X X
48 2004-5-31 31 Southend X X X NaN
49 2004-5-31 31 Colchester NaN X NaN NaN
50 2004-5-31 31 Sharnbrook X X X NaN
51 2004-5-31 31 Peterborough X X X NaN
52 2004-6-2 2 Chivenor NaN X NaN X
53 2004-6-3 3 Plymouth NaN X NaN X
54 2004-6-4 4 Plymouth NaN X NaN X
55 2004-6-4 4 Yarmouth Harbour (IoW) NaN NaN NaN X
56 2004-6-4 4 Linton NaN X NaN NaN
57 2004-6-4 4 Coningsby NaN X NaN NaN
58 2004-6-5 5 Portsmouth X X x 2 NaN NaN
59 2004-6-5 5 Sandown (IoW) X X x 2 NaN NaN
60 2004-6-5 5 Yarmouth Harbour (IoW) X X x 2 NaN NaN
61 2004-6-5 5 Normandy 14:00 and 18:30 NaN NaN NaN X
62 2004-6-6 6 Ranville 11:30 NaN NaN NaN X
63 2004-6-6 6 St Mere-Eglise NaN NaN NaN X
64 2004-6-6 6 Newhaven Fort X X x 2 NaN NaN
65 2004-6-6 6 Staplehurst NaN X x 2 NaN NaN
66 2004-6-6 6 Maidstone X NaN NaN NaN
67 2004-6-6 6 Westerham X X x 2 NaN NaN
68 2004-6-6 6 Arromanche X X x 2 NaN NaN
69 2004-6-6 6 Duxford NaN X X NaN
70 2004-6-6 6 Felixstowe NaN X X NaN
71 2004-6-7 7 Normandy X X x 2 NaN X
72 2004-6-8 8 Uxbridge NaN X NaN NaN
73 2004-6-10 10 Cambridge NaN NaN NaN X
74 2004-6-10 10 Shrivenham 20:00 NaN X X X
75 2004-6-12 12 Digby X X NaN NaN
76 2004-6-12 12 Bracebridge Heath X X NaN NaN
77 2004-6-12 12 Newark X X x 2 X X
78 2004-6-12 12 Castle Donington X X NaN NaN
79 2004-6-12 12 Bourne NaN X X X
80 2004-6-12 12 Loughborough NaN X X X
81 2004-6-12 12 Netherseal NaN X X X
82 2004-6-12 12 Barrow in Furness NaN X X X
83 2004-6-13 13 Barrow in Furness X X X NaN
84 2004-6-13 13 Cosford X X X X
85 2004-6-13 13 Loughborough X X X NaN
86 2004-6-13 13 Cambridge X NaN NaN NaN
87 2004-6-13 13 Little Bytham X X NaN NaN
88 2004-6-13 13 Bourne X NaN NaN NaN
89 2004-6-13 13 Waltham Windmill NaN X NaN X
90 2004-6-13 13 Scunthorpe NaN X NaN X
91 2004-6-13 13 Newark NaN X NaN NaN
92 2004-6-15 15 Fairford NaN NaN NaN X
93 2004-6-18 18 Blakeney NaN NaN NaN X
94 2004-6-18 18 Volkel NaN X X X
95 2004-6-19 19 Volkel NaN X X X
96 2004-6-19 19 Alresford NaN NaN NaN X
97 2004-6-19 19 Waterbeach X NaN NaN NaN
98 2004-6-19 19 Welton X NaN NaN NaN
99 2004-6-19 19 Matlock X NaN NaN NaN
100 2004-6-19 19 Hawarden X NaN NaN NaN
101 2004-6-19 19 Merseyside X NaN NaN NaN
102 2004-6-19 19 Grantham NaN X NaN NaN
103 2004-6-19 19 Kirkby Underwood NaN X NaN NaN
104 2004-6-19 19 Peterborough NaN X NaN NaN
105 2004-6-19 19 Coalville NaN X NaN NaN
106 2004-6-19 19 Marchington St Peters NaN X NaN NaN
107 2004-6-19 19 Glenfield NaN X NaN NaN
108 2004-6-19 19 Gainsborough NaN X NaN NaN
109 2004-6-20 20 Merseyside X NaN NaN NaN
110 2004-6-20 20 Woodsetts X NaN NaN NaN
111 2004-6-20 20 Kemble NaN X X NaN
112 2004-6-20 20 Ashby Magna NaN X X NaN
113 2004-6-20 20 Gedling NaN X X NaN
114 2004-6-20 20 Grantham NaN X NaN NaN
115 2004-6-20 20 Castle Bytham NaN X NaN NaN
116 2004-6-20 20 Stamford NaN X NaN NaN
117 2004-6-20 20 Wyton ATC NaN X NaN NaN
118 2004-6-20 20 Wyton Concert NaN X NaN NaN
119 2004-6-20 20 Hickling NaN X NaN NaN
120 2004-6-20 20 Brinsley NaN X NaN NaN
121 2004-6-20 20 Digby NaN X NaN NaN
122 2004-6-22 22 Bentley Priory NaN X NaN NaN
123 2004-6-24 24 Chicksands NaN X NaN NaN
124 2004-6-26 26 Woodston X X X NaN
125 2004-6-26 26 Barrowden X X X NaN
126 2004-6-26 26 Skillington X X X NaN
127 2004-6-26 26 East Bridgeford X X X NaN
128 2004-6-26 26 Waddington X X X X
129 2004-6-26 26 Bletchley Park NaN X NaN X
130 2004-6-26 26 Eldwick NaN X NaN NaN
131 2004-6-26 26 Leeming NaN X NaN NaN
132 2004-6-26 26 Kemble NaN X NaN NaN
133 2004-6-26 26 Poulshot NaN X NaN NaN
134 2004-6-26 26 Aldridge NaN X NaN NaN
135 2004-6-27 27 Waddington X X X X
136 2004-6-27 27 Butterley, Ripley NaN NaN NaN X
137 2004-6-27 27 Bletchley NaN NaN NaN X
138 2004-6-27 27 Skegness NaN NaN NaN X
139 2004-6-27 27 Upperthong X NaN NaN NaN
140 2004-7-1 1 Elvington X NaN NaN NaN
141 2004-7-2 2 Linton NaN X NaN NaN
142 2004-7-3 3 Cropwell Bishop NaN X NaN NaN
143 2004-7-3 3 Gunthorpe NaN X NaN NaN
144 2004-7-3 3 Selby NaN X NaN NaN
145 2004-7-3 3 Hartlepool NaN X NaN NaN
146 2004-7-3 3 Tattershall X X NaN NaN
147 2004-7-3 3 Badwell Ash X NaN NaN NaN
148 2004-7-3 3 Bluntisham X NaN NaN NaN
149 2004-7-3 3 Crowland X NaN NaN NaN
150 2004-7-3 3 Doddington X NaN NaN NaN
151 2004-7-3 3 Gipsy Bridge NaN X X X
152 2004-7-3 3 Bletchley NaN NaN NaN X
153 2004-7-3 3 Severn Bridge Railway NaN X X NaN
154 2004-7-3 3 Bromsgrove NaN X X X
155 2004-7-3 3 Woodspring NaN X X X
156 2004-7-3 3 Bristol NaN X X NaN
157 2004-7-3 3 Branston X X X NaN
158 2004-7-3 3 Ripley Castle X X X NaN
159 2004-7-4 4 Woodspring NaN X X X
160 2004-7-4 4 Capel Le Ferne 14:50 NaN X X NaN
161 2004-7-4 4 Spalding NaN X X NaN
162 2004-7-4 4 Bletchley NaN NaN NaN X
163 2004-7-4 4 Old NaN NaN NaN X
164 2004-7-4 4 Codnor NaN NaN NaN X
165 2004-7-4 4 Newark NaN X NaN X
166 2004-7-4 4 East Kirkby X NaN NaN NaN
167 2004-7-4 4 Bridlington X NaN NaN NaN
168 2004-7-4 4 Teeside X NaN NaN NaN
169 2004-7-4 4 Harlepool X NaN NaN NaN
170 2004-7-4 4 Spilsby X X NaN NaN
171 2004-7-4 4 Little Harrowden NaN X NaN NaN
172 2004-7-4 4 North Hykenham NaN X NaN NaN
173 2004-7-4 4 Doncaster NaN X NaN NaN
174 2004-7-4 4 Winterton NaN X NaN NaN
175 2004-7-4 4 Bridlington NaN X NaN NaN
176 2004-7-10 10 Duxford NaN X X X
177 2004-7-10 10 Cirencester NaN X X X
178 2004-7-10 10 Brize Norton NaN X X X
179 2004-7-10 10 North Killingholme X NaN NaN NaN
180 2004-7-10 10 East Fortune X X X NaN
181 2004-7-10 10 Thirsk X X X NaN
182 2004-7-10 10 Bardney NaN X NaN NaN
183 2004-7-10 10 Boultham NaN X NaN NaN
184 2004-7-10 10 North Scarle NaN X NaN NaN
185 2004-7-10 10 Lichfield NaN X NaN NaN
186 2004-7-10 10 Overseal NaN X NaN NaN
187 2004-7-10 10 Osbournby NaN X NaN NaN
188 2004-7-10 10 Thurcaston NaN X NaN NaN
189 2004-7-11 11 Duxford NaN X X X
190 2004-7-11 11 Cirencester NaN X X X
191 2004-7-11 11 Market Deeping NaN X X X
192 2004-7-11 11 Stanley Village 13:00 X NaN NaN NaN
193 2004-7-11 11 Spilsby NaN X NaN NaN
194 2004-7-11 11 West Stockwith NaN X NaN NaN
195 2004-7-11 11 Lichfield NaN X NaN NaN
196 2004-7-11 11 Saffron Walden NaN X NaN NaN
197 2004-7-13 13 Metheringham X NaN NaN NaN
198 2004-7-14 14 Culdrose X X X NaN
199 2004-7-14 14 Shrivenham X X X NaN
200 2004-7-15 15 RIAT Fairford NaN X x 2 NaN X
201 2004-7-16 16 RIAT Fairford NaN X x 2 NaN X
202 2004-7-17 17 RIAT Fairford NaN X x 2 NaN X
203 2004-7-17 17 Old Brampton X X X NaN
204 2004-7-17 17 Glenridding X X X NaN
205 2004-7-17 17 Carlisle X X X NaN
206 2004-7-18 18 Thurston NaN X NaN NaN
207 2004-7-18 18 Kimbolton NaN X NaN NaN
208 2004-7-18 18 Bletchley NaN X NaN NaN
209 2004-7-18 18 Loughborough NaN X NaN NaN
210 2004-7-18 18 Stamford NaN X NaN NaN
211 2004-7-18 18 RIAT Fairford NaN X x 2 NaN X
212 2004-7-22 22 Benson NaN X NaN NaN
213 2004-7-23 23 Farnborough NaN X NaN NaN
214 2004-7-23 23 Llandiloes X X X X
215 2004-7-23 23 Benson X NaN X NaN
216 2004-7-24 24 Farnborough X X X NaN
217 2004-7-24 24 North Coates X X X NaN
218 2004-7-24 24 Scunthorpe X X X NaN
219 2004-7-24 24 Breighton X X X NaN
220 2004-7-24 24 Sunderland X X X NaN
221 2004-7-25 25 Sunderland X X X NaN
222 2004-7-25 25 York X X X NaN
223 2004-7-25 25 Breighton X X X NaN
224 2004-7-25 25 Luddington X X X NaN
225 2004-7-25 25 North Coates X X X NaN
226 2004-7-25 25 Sutton on Sea X X X NaN
227 2004-7-25 25 Audlem NaN NaN NaN X
228 2004-7-25 25 Farnborough NaN X NaN NaN
229 2004-7-29 29 Marham NaN X NaN NaN
230 2004-7-29 29 Lowestoft X X X NaN
231 2004-7-30 30 Lowestoft X X X NaN
232 2004-7-30 30 Hardwick Hall NaN NaN NaN X
233 2004-7-31 31 Middle Wallop NaN X X NaN
234 2004-7-31 31 Colwick NaN NaN NaN X
235 2004-7-31 31 Lincoln Showground X X X NaN
236 2004-7-31 31 Kielder Forest X X X NaN
237 2004-7-31 31 Windermere X X X NaN
238 2004-8-1 1 Windermere X X X NaN
239 2004-8-1 1 Cudworth X X X NaN
240 2004-8-1 1 Doncaster X X X NaN
241 2004-8-1 1 Lincoln Showground X X X NaN
242 2004-8-1 1 Peterborough NaN NaN X X
243 2004-8-1 1 Birchington NaN X NaN NaN
244 2004-8-13 13 Eastbourne X X X NaN
245 2004-8-14 14 Eastbourne X X X NaN
246 2004-8-14 14 Scarborough NaN X NaN NaN
247 2004-8-14 14 Whitby NaN X NaN NaN
248 2004-8-14 14 Dalston NaN X NaN X
249 2004-8-14 14 Elvington NaN NaN NaN X
250 2004-8-14 14 Campbletown NaN X NaN X
251 2004-8-14 14 Aldergrove NaN NaN NaN X
252 2004-8-15 15 Eastbourne X X X NaN
253 2004-8-15 15 Kenley NaN X X NaN
254 2004-8-15 15 Barnack X X X NaN
255 2004-8-15 15 Redhill X NaN NaN NaN
256 2004-8-15 15 Bletchley X NaN NaN NaN
257 2004-8-15 15 Campbletown NaN X NaN X
258 2004-8-15 15 Sunderland NaN X NaN X
259 2004-8-15 15 Whitby NaN X NaN NaN
260 2004-8-15 15 Scarborough NaN X NaN NaN
261 2004-8-15 15 Scampton NaN X NaN X
262 2004-8-15 15 Elvington NaN NaN NaN X
263 2004-8-20 20 East Kirkby X NaN NaN NaN
264 2004-8-21 21 Elvington NaN X X X
265 2004-8-21 21 Shepway X X X NaN
266 2004-8-21 21 Ramsey NaN NaN X NaN
267 2004-8-22 22 Elvington NaN X X X
268 2004-8-22 22 Falaise X X NaN NaN
269 2004-8-26 26 Clacton NaN X X X
270 2004-8-27 27 Clacton NaN X X X
271 2004-8-27 27 Dartmouth X X X NaN
272 2004-8-28 28 Henstridge X X X NaN
273 2004-8-28 28 Shoreham X X X NaN
274 2004-8-28 28 Kent Showground X X X NaN
275 2004-8-29 29 Shoreham X X X NaN
276 2004-8-29 29 Egerton NaN X X NaN
277 2004-8-29 29 Chart Sutton NaN X X NaN
278 2004-8-29 29 Kent Showground X NaN NaN NaN
279 2004-8-29 29 Little Gransden X X X NaN
280 2004-8-29 29 Elsham Wolds X NaN NaN NaN
281 2004-8-30 30 Ripley NaN NaN NaN X
282 2004-8-30 30 Caernarfon NaN NaN NaN X
283 2004-8-30 30 Aylsham X X X NaN
284 2004-8-30 30 Boxted NaN X NaN NaN
285 2004-8-30 30 Grimsthorpe X NaN X NaN
286 2004-8-30 30 Ripley NaN X NaN NaN
287 2004-8-30 30 Dinton NaN X NaN NaN
288 2004-9-3 3 Linton NaN X NaN NaN
289 2004-9-3 3 Leeming NaN X NaN NaN
290 2004-9-4 4 Peterlee NaN X NaN NaN
291 2004-9-4 4 Elvington NaN X NaN NaN
292 2004-9-4 4 Shackerstone NaN NaN NaN X
293 2004-9-4 4 Whittington NaN NaN NaN X
294 2004-9-4 4 White Waltham X NaN NaN X
295 2004-9-4 4 Brize Norton NaN NaN NaN X
296 2004-9-4 4 Duxford X X X NaN
297 2004-9-4 4 Goodwood X X X NaN
298 2004-9-4 4 Fareham X X X NaN
299 2004-9-5 5 Goodwood X X X NaN
300 2004-9-5 5 Staplehurst NaN X X NaN
301 2004-9-5 5 Duxford X X X NaN
302 2004-9-5 5 Ludford Manor X NaN NaN NaN
303 2004-9-5 5 Sutton on Trent NaN NaN NaN X
304 2004-9-5 5 Gedling Notts NaN NaN NaN X
305 2004-9-5 5 Chesterfield NaN NaN NaN X
306 2004-9-5 5 Elvington NaN NaN NaN X
307 2004-9-5 5 Wragby NaN NaN NaN X
308 2004-9-9 9 Guernsey X X X X
309 2004-9-9 9 Jersey X X X X
310 2004-9-10 10 Fylingdales NaN X NaN NaN
311 2004-9-10 10 Leeming NaN X NaN NaN
312 2004-9-11 11 Southport X X X NaN
313 2004-9-11 11 Morecambe X X X NaN
314 2004-9-11 11 Leuchars X X X NaN
315 2004-9-11 11 Rennes NaN X NaN X
316 2004-9-11 11 St Omer NaN X NaN X
317 2004-9-12 12 Moffat NaN X NaN NaN
318 2004-9-12 12 Morecambe X X X NaN
319 2004-9-12 12 Southport X X X NaN
320 2004-9-12 12 Newhaven Fort NaN X NaN NaN
321 2004-9-12 12 Isle of Sheppey NaN NaN NaN X
322 2004-9-12 12 Somerby NaN NaN NaN X
323 2004-9-12 12 Rotherham NaN NaN NaN X
324 2004-9-12 12 Thorpe Camp NaN NaN NaN X
325 2004-9-12 12 Newark NaN X NaN NaN
326 2004-9-12 12 Cosford NaN X NaN NaN
327 2004-9-13 13 Kings Lynn NaN X NaN NaN
328 2004-9-13 13 Norwich City 12:00 NaN X NaN NaN
329 2004-9-13 13 Norwich County 18:00 NaN X NaN NaN
330 2004-9-15 15 Coltishall NaN X NaN NaN
331 2004-9-15 15 Wyton NaN X NaN NaN
332 2004-9-15 15 Boston 11:00 NaN X NaN NaN
333 2004-9-15 15 Bentley Priory 19:00 NaN X NaN NaN
334 2004-9-15 15 Northolt 19:15 NaN X NaN NaN
335 2004-9-15 15 Hemswell X NaN NaN NaN
336 2004-9-16 16 Bicester 18:45 NaN X NaN NaN
337 2004-9-17 17 Shrivenham NaN X X NaN
338 2004-9-18 18 Biggin Hill NaN X x 2 X NaN
339 2004-9-18 18 Stanmore 16:25 NaN X NaN NaN
340 2004-9-18 18 Bentley Priory 16:30 NaN X NaN NaN
341 2004-9-18 18 Ginkelheide 11:00 NaN NaN NaN X
342 2004-9-18 18 Arnhem 18:30 NaN NaN NaN X
343 2004-9-18 18 Yeovilton X X X NaN
344 2004-9-18 18 Little Casterton X X X NaN
345 2004-9-19 19 Biggin Hill NaN X x 2 X NaN
346 2004-9-19 19 Croydon NaN X x 2 X NaN
347 2004-9-19 19 Sheringham NaN X NaN NaN
348 2004-9-19 19 West Runton NaN X NaN NaN
349 2004-9-19 19 Norwich NaN X NaN NaN
350 2004-9-19 19 Boston NaN X NaN NaN
351 2004-9-19 19 Arnham NaN NaN NaN X
352 2004-9-19 19 Nijmagen NaN NaN NaN X
353 2004-9-19 19 Oosterbeek NaN NaN NaN X
354 2004-9-25 25 Waddington NaN NaN NaN X
355 2004-9-25 25 Elvington NaN NaN NaN X
356 2004-9-25 25 Bovingdon NaN X NaN NaN
357 2004-9-25 25 Lichfield X X NaN NaN
358 2004-9-25 25 Grimsby X NaN NaN NaN
359 2004-9-26 26 Finningley NaN X NaN NaN
360 2004-9-26 26 Flixton X NaN NaN NaN
Best Regards,
Sandeep
GANGA SANDEEP KUMAR
Posts: 218
Threads: 27
Joined: May 2018
Jan-08-2020, 07:25 AM
(This post was last modified: Jan-08-2020, 07:32 AM by eddywinch82.)
Hi Sandeep,
Many many thanks, for sorting this out for me, in your free time. I will try the modified Code, when I get home. I sent you a private message, did you get it ?
Regards
Eddie Winch
Posts: 218
Threads: 27
Joined: May 2018
Jan-08-2020, 08:29 PM
(This post was last modified: Jan-08-2020, 08:29 PM by eddywinch82.)
Hi Sandeep,
Your doing a great job for me, many thanks ))
Could you run the following, Original Code again in Jupyter Notebook :-
import pandas as pd
import requests
from bs4 import BeautifulSoup
res = requests.get("http://web.archive.org/web/20041020000138/http://www.raf.mod.uk/bbmf/displaydates.html")
soup = BeautifulSoup(res.content,'lxml')
table = soup.find_all('table', align="CENTER")[0]
df = pd.read_html(str(table))
df = df[0]
df = df.rename(columns=df.iloc[0])
df = df.iloc[2:]
df.head(15)
pd.options.display.max_rows = 1000
display = df[(df['Location'].str.contains('[a-zA-Z]')) & (df['Lancaster'].str.contains('X')) & (df['Spitfire'].str.contains('X', na=True)) & (df['Dakota'].str.contains('X', na=True))]
display
#display.drop('Dakota', axis=1, inplace=True)
display.dropna(subset=['Spitfire', 'Hurricane'], how='all') The Data showing, as the final Output, when I run the Code, you modified from mine, in Jupyter Notebook. It does not match the outputted Data, when I run my Original Code. All the Data you will get, when you run my Original Code, is the data that I would like showing, and should be showing. Having a new Column called :- EDITED_DATE_FORMAT in addition to the others is fine, but if they could be in the original Date Column, that would be better, replacing what is there already.
The only difference is in the Date Column, I would like the full dates showing, not just the day. also is it possible to, instead of having the dates, in the format Year Month Day, i.e. 2004/08/21. Could the dates be shown, in the format Day Month Year instead, i.e. 21/08/2004 etc ?
Many thanks for sorting out the Dates issue, but the data displaying, i.e. in these Columns :-
Output: Location Lancaster Spitfire Hurricane Dakota
Doesn't match, as you will see, if you run the My original Code. I am not sure why ?
Also how do you get the outputted Data, to display in the Jupyter Notebook Font, i.e. like in the Output you get, when you run my Original Code ?
And also, Could you please read my reply, before my last reply, i.e reply
number 12 on this page, in the following Thread ? :-
https://python-forum.io/Thread-Filtering...ues?page=2
And reply back accordingly, your help has been marvelous, it's very
much appreciated by me.
Best Regards
Eddie Winch
Posts: 218
Threads: 27
Joined: May 2018
Hi Sandeep,
Could you read my last Post on this Thread again, could you help me achieve, what I want
with the Python Code, on here ? If you could run my Python Code, in Jupyter Notebook, You will see, the Output I would like to achieve, only the full dates, in the Date Column, being the additional change, all the other Data the same, in that Jupyter Notebook Font.
If someone else could help me, that would be Excellent too.
Any help would be much appreciated
Best Regards
Eddie Winch
Posts: 218
Threads: 27
Joined: May 2018
Can anyone help me ?
Regards
Eddie Winch
Posts: 218
Threads: 27
Joined: May 2018
Hi Sandeep,
I have sent you a private message, about this thread.
Regards
Eddie Winch
Posts: 50
Threads: 2
Joined: Nov 2019
Jan-22-2020, 04:47 PM
(This post was last modified: Jan-22-2020, 04:48 PM by sandeep_ganga.)
Hi,
I was finally able to manage install and use jupyter notebook. Google helped.
python -m pip install jupyterlab
python -m pip install jupyter
python -m pip install notebook
python -m notebook
#it redirects to browser and navigate to jupyter,.
The same code, which pasted earlier worked on Jupyter too.
I just added below to expand columns on jupyter output.
pd.set_option('display.max_rows', 500)
pd.set_option('display.max_columns', 500)
pd.set_option('display.width', 1000) Re-Adding complete code below, you may now edit df3 dataframe in below code as needed for further processings that you require.
import pandas as pd
import requests
from bs4 import BeautifulSoup
res = requests.get("http://web.archive.org/web/20041020000138/http://www.raf.mod.uk/bbmf/displaydates.html")
soup = BeautifulSoup(res.content,'lxml')
table = soup.find_all('table', align="CENTER")[0]
df = pd.read_html(str(table))
df = df[0]
##################
##################
##################
pd.set_option('display.max_rows', 500)
pd.set_option('display.max_columns', 500)
pd.set_option('display.width', 1000)
#make df[0] to list
list=[]
for i in df[0]:
list.append(i)
#reverse the list to make split to sublist easier
list.reverse()
#split list to sublist using condition len(val)> 2
size = len(list)
idx_list = [idx + 1 for idx, val in
enumerate(list) if len(val) > 2]
res = [list[i: j] for i, j in
zip([0] + idx_list, idx_list +
([size] if idx_list[-1] != size else []))]
#make monthname to numbers and print
for i in res:
for j in range(len(i)):
if i[j].upper()=='JUNE':
i[j]='6'
elif i[j].upper() =='MAY':
i[j]='5'
elif i[j].upper() == 'APRIL':
i[j]='4'
elif i[j].upper() =='JANUARY':
i[j]='1'
elif i[j].upper() == 'FEBRUARY':
i[j]='2'
elif i[j].upper() =='MARCH':
i[j]='3'
elif i[j].upper() == 'JULY':
i[j]='7'
elif i[j].upper() =='AUGUST':
i[j]='8'
elif i[j].upper() == 'SEPTEMBER':
i[j]='9'
elif i[j].upper() =='OCTOBER':
i[j]='10'
elif i[j].upper() == 'NOVEMBER':
i[j]='11'
elif i[j].upper() =='DECEMBER':
i[j]='12'
#append string and append to new list
finallist=[]
for i in res:
for j in range(len(i)):
if j < len(i) - 1:
#print(f'2004-{i[-1]}-{i[j]}')
finallist.append(f'2004-{i[-1]}-{i[j]}')
#print(finallist)
finallist.reverse()
#print("\n=== ORIGINAL DF ===\n")
#print(df)
#convert dataframe to list
listtemp1=df.values.tolist()
#replace found below values with 0000_removable
removelist=['LOCATION','LANCASTER','SPITFIRE','HURRICANE','DAKOTA','DATE','JUNE','JANUARY','FEBRUARY','MARCH','MAY','JULY','AUGUST','SEPTEMBER','OCTOBER','NOVEMBER','DECEMBER','APRIL']
for i in listtemp1:
for j in range(len(i)):
for place in removelist:
if str(i[j]).upper()==place:
i[j]='0000_removable'
else:
pass
#remove sublists with the replaced values we redirected
dellist=['0000_removable', '0000_removable', '0000_removable', '0000_removable', '0000_removable', '0000_removable']
res = [i for i in listtemp1 if i != dellist]
#assign back to dataframe DF3
df3=pd.DataFrame()
df3=pd.DataFrame(res, columns=['Date','LOCATION','LANCASTER','SPITFIRE','HURRICANE','DAKOTA'])
#print("\n=== AFTER REMOVE month and column names from DF, assigned to new as DF3 ===\n")
#print(df3)
#now assign that sorted date list to dataframe DF3
idx = 0
df3.insert(loc=idx, column='EDITED_DATE_FORMAT', value=finallist)
pd.options.display.max_rows = 500
print("\n=== FINAL DF3 after joining the edited date format column list ===\n")
print(df3)
#validation logic if needed compare processed date from new joined "edited_Date_format" column with already existing "Date" column
#df3['ED1']= pd.to_datetime(df3['EDITED_DATE_FORMAT'],format='%Y-%m-%d').dt.day
#df3['validation of date'] = df3.apply(lambda x: str(x['ED1']) == x['Date'], axis=1)
#convert df3['EDITED_DATE_FORMAT'] column from object to datetime64 foramt
#df3['EDITED_DATE_FORMAT']= pd.to_datetime(df3['EDITED_DATE_FORMAT'],format='%Y-%m-%d')
##################
##################
##################
#df3 = df3.rename(columns=df.iloc[0])
#df3 = df.iloc[2:]
#df3.head(15)
pd.options.display.max_rows = 1000
display = df3[(df3['LOCATION'].str.contains('[a-zA-Z]')) & (df3['LANCASTER'].str.contains('X')) & (df3['SPITFIRE'].str.contains('X', na=True)) & (df3['DAKOTA'].str.contains('X', na=True))]
#print(display )
#display.drop('DAKOTA', axis=1, inplace=True)
#display.dropna(subset=['SPITFIRE', 'HURRICANE'], how='all')
#print(display) Output: === FINAL DF3 after joining the edited date format column list ===
EDITED_DATE_FORMAT Date LOCATION LANCASTER SPITFIRE HURRICANE DAKOTA
0 2004-4-25 25 Waddington X NaN NaN NaN
1 2004-4-25 25 Pollington X NaN NaN NaN
2 2004-5-2 2 Ripley NaN X NaN X
3 2004-5-2 2 Abingdon NaN X NaN NaN
4 2004-5-2 2 Horley NaN NaN NaN X
5 2004-5-2 2 Waddington NaN NaN NaN X
6 2004-5-3 3 Woodmancote NaN X X NaN
7 2004-5-3 3 Wakefield NaN X X NaN
8 2004-5-4 4 Manchester NaN NaN NaN X
9 2004-5-5 5 Assen X NaN NaN NaN
10 2004-5-8 8 Langar 13:10 X NaN NaN NaN
11 2004-5-8 8 Faldingworth 13:30 X NaN NaN NaN
12 2004-5-8 8 Hibaldstow NaN X NaN NaN
13 2004-5-8 8 Haworth NaN X NaN NaN
14 2004-5-8 8 Stoke Goldington NaN NaN NaN X
15 2004-5-9 9 Northwich NaN X NaN NaN
16 2004-5-9 9 Jersey NaN NaN NaN X
17 2004-5-9 9 Stoke Goldington NaN NaN NaN X
18 2004-5-14 14 Digby X X X NaN
19 2004-5-15 15 Harlaxton X X X NaN
20 2004-5-15 15 Coleraine X X X NaN
21 2004-5-15 15 Coltishall NaN NaN X NaN
22 2004-5-15 15 Tilford NaN X NaN X
23 2004-5-15 15 Abingdon NaN X NaN X
24 2004-5-15 15 Hyde Heath Village NaN X NaN X
25 2004-5-16 16 Hardwick Hall NaN NaN NaN X
26 2004-5-16 16 Woodhall Spa X NaN NaN X
27 2004-5-16 16 New Leake X X X X
28 2004-5-16 16 Blackpool X X X NaN
29 2004-5-16 16 Elvington X X X NaN
30 2004-5-16 16 Kelstern X NaN NaN NaN
31 2004-5-19 19 Waterbeach NaN X NaN NaN
32 2004-5-20 20 Shrivenham X NaN NaN NaN
33 2004-5-20 20 High Wycombe X NaN NaN NaN
34 2004-5-21 21 Halton NaN X NaN NaN
35 2004-5-22 22 Newton Linford NaN NaN NaN X
36 2004-5-22 22 Sedbergh NaN NaN NaN X
37 2004-5-22 22 Newark NaN NaN NaN X
38 2004-5-23 23 Aldbourne NaN NaN NaN X
39 2004-5-23 23 Newark X NaN NaN NaN
40 2004-5-23 23 Waddington X NaN NaN NaN
41 2004-5-29 29 Perth NaN NaN NaN X
42 2004-5-29 29 Blackpool NaN NaN NaN X
43 2004-5-30 30 Conington X X X NaN
44 2004-5-30 30 Enfield X X X NaN
45 2004-5-30 30 Southend X X X NaN
46 2004-5-31 31 Falmouth NaN NaN NaN X
47 2004-5-31 31 Corbridge NaN X X X
48 2004-5-31 31 Southend X X X NaN
49 2004-5-31 31 Colchester NaN X NaN NaN
50 2004-5-31 31 Sharnbrook X X X NaN
51 2004-5-31 31 Peterborough X X X NaN
52 2004-6-2 2 Chivenor NaN X NaN X
53 2004-6-3 3 Plymouth NaN X NaN X
54 2004-6-4 4 Plymouth NaN X NaN X
55 2004-6-4 4 Yarmouth Harbour (IoW) NaN NaN NaN X
56 2004-6-4 4 Linton NaN X NaN NaN
57 2004-6-4 4 Coningsby NaN X NaN NaN
58 2004-6-5 5 Portsmouth X X x 2 NaN NaN
59 2004-6-5 5 Sandown (IoW) X X x 2 NaN NaN
60 2004-6-5 5 Yarmouth Harbour (IoW) X X x 2 NaN NaN
61 2004-6-5 5 Normandy 14:00 and 18:30 NaN NaN NaN X
62 2004-6-6 6 Ranville 11:30 NaN NaN NaN X
63 2004-6-6 6 St Mere-Eglise NaN NaN NaN X
64 2004-6-6 6 Newhaven Fort X X x 2 NaN NaN
65 2004-6-6 6 Staplehurst NaN X x 2 NaN NaN
66 2004-6-6 6 Maidstone X NaN NaN NaN
67 2004-6-6 6 Westerham X X x 2 NaN NaN
68 2004-6-6 6 Arromanche X X x 2 NaN NaN
69 2004-6-6 6 Duxford NaN X X NaN
70 2004-6-6 6 Felixstowe NaN X X NaN
71 2004-6-7 7 Normandy X X x 2 NaN X
72 2004-6-8 8 Uxbridge NaN X NaN NaN
73 2004-6-10 10 Cambridge NaN NaN NaN X
74 2004-6-10 10 Shrivenham 20:00 NaN X X X
75 2004-6-12 12 Digby X X NaN NaN
76 2004-6-12 12 Bracebridge Heath X X NaN NaN
77 2004-6-12 12 Newark X X x 2 X X
78 2004-6-12 12 Castle Donington X X NaN NaN
79 2004-6-12 12 Bourne NaN X X X
80 2004-6-12 12 Loughborough NaN X X X
81 2004-6-12 12 Netherseal NaN X X X
82 2004-6-12 12 Barrow in Furness NaN X X X
83 2004-6-13 13 Barrow in Furness X X X NaN
84 2004-6-13 13 Cosford X X X X
85 2004-6-13 13 Loughborough X X X NaN
86 2004-6-13 13 Cambridge X NaN NaN NaN
87 2004-6-13 13 Little Bytham X X NaN NaN
88 2004-6-13 13 Bourne X NaN NaN NaN
89 2004-6-13 13 Waltham Windmill NaN X NaN X
90 2004-6-13 13 Scunthorpe NaN X NaN X
91 2004-6-13 13 Newark NaN X NaN NaN
92 2004-6-15 15 Fairford NaN NaN NaN X
93 2004-6-18 18 Blakeney NaN NaN NaN X
94 2004-6-18 18 Volkel NaN X X X
95 2004-6-19 19 Volkel NaN X X X
96 2004-6-19 19 Alresford NaN NaN NaN X
97 2004-6-19 19 Waterbeach X NaN NaN NaN
98 2004-6-19 19 Welton X NaN NaN NaN
99 2004-6-19 19 Matlock X NaN NaN NaN
100 2004-6-19 19 Hawarden X NaN NaN NaN
101 2004-6-19 19 Merseyside X NaN NaN NaN
102 2004-6-19 19 Grantham NaN X NaN NaN
103 2004-6-19 19 Kirkby Underwood NaN X NaN NaN
104 2004-6-19 19 Peterborough NaN X NaN NaN
105 2004-6-19 19 Coalville NaN X NaN NaN
106 2004-6-19 19 Marchington St Peters NaN X NaN NaN
107 2004-6-19 19 Glenfield NaN X NaN NaN
108 2004-6-19 19 Gainsborough NaN X NaN NaN
109 2004-6-20 20 Merseyside X NaN NaN NaN
110 2004-6-20 20 Woodsetts X NaN NaN NaN
111 2004-6-20 20 Kemble NaN X X NaN
112 2004-6-20 20 Ashby Magna NaN X X NaN
113 2004-6-20 20 Gedling NaN X X NaN
114 2004-6-20 20 Grantham NaN X NaN NaN
115 2004-6-20 20 Castle Bytham NaN X NaN NaN
116 2004-6-20 20 Stamford NaN X NaN NaN
117 2004-6-20 20 Wyton ATC NaN X NaN NaN
118 2004-6-20 20 Wyton Concert NaN X NaN NaN
119 2004-6-20 20 Hickling NaN X NaN NaN
120 2004-6-20 20 Brinsley NaN X NaN NaN
121 2004-6-20 20 Digby NaN X NaN NaN
122 2004-6-22 22 Bentley Priory NaN X NaN NaN
123 2004-6-24 24 Chicksands NaN X NaN NaN
124 2004-6-26 26 Woodston X X X NaN
125 2004-6-26 26 Barrowden X X X NaN
126 2004-6-26 26 Skillington X X X NaN
127 2004-6-26 26 East Bridgeford X X X NaN
128 2004-6-26 26 Waddington X X X X
129 2004-6-26 26 Bletchley Park NaN X NaN X
130 2004-6-26 26 Eldwick NaN X NaN NaN
131 2004-6-26 26 Leeming NaN X NaN NaN
132 2004-6-26 26 Kemble NaN X NaN NaN
133 2004-6-26 26 Poulshot NaN X NaN NaN
134 2004-6-26 26 Aldridge NaN X NaN NaN
135 2004-6-27 27 Waddington X X X X
136 2004-6-27 27 Butterley, Ripley NaN NaN NaN X
137 2004-6-27 27 Bletchley NaN NaN NaN X
138 2004-6-27 27 Skegness NaN NaN NaN X
139 2004-6-27 27 Upperthong X NaN NaN NaN
140 2004-7-1 1 Elvington X NaN NaN NaN
141 2004-7-2 2 Linton NaN X NaN NaN
142 2004-7-3 3 Cropwell Bishop NaN X NaN NaN
143 2004-7-3 3 Gunthorpe NaN X NaN NaN
144 2004-7-3 3 Selby NaN X NaN NaN
145 2004-7-3 3 Hartlepool NaN X NaN NaN
146 2004-7-3 3 Tattershall X X NaN NaN
147 2004-7-3 3 Badwell Ash X NaN NaN NaN
148 2004-7-3 3 Bluntisham X NaN NaN NaN
149 2004-7-3 3 Crowland X NaN NaN NaN
150 2004-7-3 3 Doddington X NaN NaN NaN
151 2004-7-3 3 Gipsy Bridge NaN X X X
152 2004-7-3 3 Bletchley NaN NaN NaN X
153 2004-7-3 3 Severn Bridge Railway NaN X X NaN
154 2004-7-3 3 Bromsgrove NaN X X X
155 2004-7-3 3 Woodspring NaN X X X
156 2004-7-3 3 Bristol NaN X X NaN
157 2004-7-3 3 Branston X X X NaN
158 2004-7-3 3 Ripley Castle X X X NaN
159 2004-7-4 4 Woodspring NaN X X X
160 2004-7-4 4 Capel Le Ferne 14:50 NaN X X NaN
161 2004-7-4 4 Spalding NaN X X NaN
162 2004-7-4 4 Bletchley NaN NaN NaN X
163 2004-7-4 4 Old NaN NaN NaN X
164 2004-7-4 4 Codnor NaN NaN NaN X
165 2004-7-4 4 Newark NaN X NaN X
166 2004-7-4 4 East Kirkby X NaN NaN NaN
167 2004-7-4 4 Bridlington X NaN NaN NaN
168 2004-7-4 4 Teeside X NaN NaN NaN
169 2004-7-4 4 Harlepool X NaN NaN NaN
170 2004-7-4 4 Spilsby X X NaN NaN
171 2004-7-4 4 Little Harrowden NaN X NaN NaN
172 2004-7-4 4 North Hykenham NaN X NaN NaN
173 2004-7-4 4 Doncaster NaN X NaN NaN
174 2004-7-4 4 Winterton NaN X NaN NaN
175 2004-7-4 4 Bridlington NaN X NaN NaN
176 2004-7-10 10 Duxford NaN X X X
177 2004-7-10 10 Cirencester NaN X X X
178 2004-7-10 10 Brize Norton NaN X X X
179 2004-7-10 10 North Killingholme X NaN NaN NaN
180 2004-7-10 10 East Fortune X X X NaN
181 2004-7-10 10 Thirsk X X X NaN
182 2004-7-10 10 Bardney NaN X NaN NaN
183 2004-7-10 10 Boultham NaN X NaN NaN
184 2004-7-10 10 North Scarle NaN X NaN NaN
185 2004-7-10 10 Lichfield NaN X NaN NaN
186 2004-7-10 10 Overseal NaN X NaN NaN
187 2004-7-10 10 Osbournby NaN X NaN NaN
188 2004-7-10 10 Thurcaston NaN X NaN NaN
189 2004-7-11 11 Duxford NaN X X X
190 2004-7-11 11 Cirencester NaN X X X
191 2004-7-11 11 Market Deeping NaN X X X
192 2004-7-11 11 Stanley Village 13:00 X NaN NaN NaN
193 2004-7-11 11 Spilsby NaN X NaN NaN
194 2004-7-11 11 West Stockwith NaN X NaN NaN
195 2004-7-11 11 Lichfield NaN X NaN NaN
196 2004-7-11 11 Saffron Walden NaN X NaN NaN
197 2004-7-13 13 Metheringham X NaN NaN NaN
198 2004-7-14 14 Culdrose X X X NaN
199 2004-7-14 14 Shrivenham X X X NaN
200 2004-7-15 15 RIAT Fairford NaN X x 2 NaN X
201 2004-7-16 16 RIAT Fairford NaN X x 2 NaN X
202 2004-7-17 17 RIAT Fairford NaN X x 2 NaN X
203 2004-7-17 17 Old Brampton X X X NaN
204 2004-7-17 17 Glenridding X X X NaN
205 2004-7-17 17 Carlisle X X X NaN
206 2004-7-18 18 Thurston NaN X NaN NaN
207 2004-7-18 18 Kimbolton NaN X NaN NaN
208 2004-7-18 18 Bletchley NaN X NaN NaN
209 2004-7-18 18 Loughborough NaN X NaN NaN
210 2004-7-18 18 Stamford NaN X NaN NaN
211 2004-7-18 18 RIAT Fairford NaN X x 2 NaN X
212 2004-7-22 22 Benson NaN X NaN NaN
213 2004-7-23 23 Farnborough NaN X NaN NaN
214 2004-7-23 23 Llandiloes X X X X
215 2004-7-23 23 Benson X NaN X NaN
216 2004-7-24 24 Farnborough X X X NaN
217 2004-7-24 24 North Coates X X X NaN
218 2004-7-24 24 Scunthorpe X X X NaN
219 2004-7-24 24 Breighton X X X NaN
220 2004-7-24 24 Sunderland X X X NaN
221 2004-7-25 25 Sunderland X X X NaN
222 2004-7-25 25 York X X X NaN
223 2004-7-25 25 Breighton X X X NaN
224 2004-7-25 25 Luddington X X X NaN
225 2004-7-25 25 North Coates X X X NaN
226 2004-7-25 25 Sutton on Sea X X X NaN
227 2004-7-25 25 Audlem NaN NaN NaN X
228 2004-7-25 25 Farnborough NaN X NaN NaN
229 2004-7-29 29 Marham NaN X NaN NaN
230 2004-7-29 29 Lowestoft X X X NaN
231 2004-7-30 30 Lowestoft X X X NaN
232 2004-7-30 30 Hardwick Hall NaN NaN NaN X
233 2004-7-31 31 Middle Wallop NaN X X NaN
234 2004-7-31 31 Colwick NaN NaN NaN X
235 2004-7-31 31 Lincoln Showground X X X NaN
236 2004-7-31 31 Kielder Forest X X X NaN
237 2004-7-31 31 Windermere X X X NaN
238 2004-8-1 1 Windermere X X X NaN
239 2004-8-1 1 Cudworth X X X NaN
240 2004-8-1 1 Doncaster X X X NaN
241 2004-8-1 1 Lincoln Showground X X X NaN
242 2004-8-1 1 Peterborough NaN NaN X X
243 2004-8-1 1 Birchington NaN X NaN NaN
244 2004-8-13 13 Eastbourne X X X NaN
245 2004-8-14 14 Eastbourne X X X NaN
246 2004-8-14 14 Scarborough NaN X NaN NaN
247 2004-8-14 14 Whitby NaN X NaN NaN
248 2004-8-14 14 Dalston NaN X NaN X
249 2004-8-14 14 Elvington NaN NaN NaN X
250 2004-8-14 14 Campbletown NaN X NaN X
251 2004-8-14 14 Aldergrove NaN NaN NaN X
252 2004-8-15 15 Eastbourne X X X NaN
253 2004-8-15 15 Kenley NaN X X NaN
254 2004-8-15 15 Barnack X X X NaN
255 2004-8-15 15 Redhill X NaN NaN NaN
256 2004-8-15 15 Bletchley X NaN NaN NaN
257 2004-8-15 15 Campbletown NaN X NaN X
258 2004-8-15 15 Sunderland NaN X NaN X
259 2004-8-15 15 Whitby NaN X NaN NaN
260 2004-8-15 15 Scarborough NaN X NaN NaN
261 2004-8-15 15 Scampton NaN X NaN X
262 2004-8-15 15 Elvington NaN NaN NaN X
263 2004-8-20 20 East Kirkby X NaN NaN NaN
264 2004-8-21 21 Elvington NaN X X X
265 2004-8-21 21 Shepway X X X NaN
266 2004-8-21 21 Ramsey NaN NaN X NaN
267 2004-8-22 22 Elvington NaN X X X
268 2004-8-22 22 Falaise X X NaN NaN
269 2004-8-26 26 Clacton NaN X X X
270 2004-8-27 27 Clacton NaN X X X
271 2004-8-27 27 Dartmouth X X X NaN
272 2004-8-28 28 Henstridge X X X NaN
273 2004-8-28 28 Shoreham X X X NaN
274 2004-8-28 28 Kent Showground X X X NaN
275 2004-8-29 29 Shoreham X X X NaN
276 2004-8-29 29 Egerton NaN X X NaN
277 2004-8-29 29 Chart Sutton NaN X X NaN
278 2004-8-29 29 Kent Showground X NaN NaN NaN
279 2004-8-29 29 Little Gransden X X X NaN
280 2004-8-29 29 Elsham Wolds X NaN NaN NaN
281 2004-8-30 30 Ripley NaN NaN NaN X
282 2004-8-30 30 Caernarfon NaN NaN NaN X
283 2004-8-30 30 Aylsham X X X NaN
284 2004-8-30 30 Boxted NaN X NaN NaN
285 2004-8-30 30 Grimsthorpe X NaN X NaN
286 2004-8-30 30 Ripley NaN X NaN NaN
287 2004-8-30 30 Dinton NaN X NaN NaN
288 2004-9-3 3 Linton NaN X NaN NaN
289 2004-9-3 3 Leeming NaN X NaN NaN
290 2004-9-4 4 Peterlee NaN X NaN NaN
291 2004-9-4 4 Elvington NaN X NaN NaN
292 2004-9-4 4 Shackerstone NaN NaN NaN X
293 2004-9-4 4 Whittington NaN NaN NaN X
294 2004-9-4 4 White Waltham X NaN NaN X
295 2004-9-4 4 Brize Norton NaN NaN NaN X
296 2004-9-4 4 Duxford X X X NaN
297 2004-9-4 4 Goodwood X X X NaN
298 2004-9-4 4 Fareham X X X NaN
299 2004-9-5 5 Goodwood X X X NaN
300 2004-9-5 5 Staplehurst NaN X X NaN
301 2004-9-5 5 Duxford X X X NaN
302 2004-9-5 5 Ludford Manor X NaN NaN NaN
303 2004-9-5 5 Sutton on Trent NaN NaN NaN X
304 2004-9-5 5 Gedling Notts NaN NaN NaN X
305 2004-9-5 5 Chesterfield NaN NaN NaN X
306 2004-9-5 5 Elvington NaN NaN NaN X
307 2004-9-5 5 Wragby NaN NaN NaN X
308 2004-9-9 9 Guernsey X X X X
309 2004-9-9 9 Jersey X X X X
310 2004-9-10 10 Fylingdales NaN X NaN NaN
311 2004-9-10 10 Leeming NaN X NaN NaN
312 2004-9-11 11 Southport X X X NaN
313 2004-9-11 11 Morecambe X X X NaN
314 2004-9-11 11 Leuchars X X X NaN
315 2004-9-11 11 Rennes NaN X NaN X
316 2004-9-11 11 St Omer NaN X NaN X
317 2004-9-12 12 Moffat NaN X NaN NaN
318 2004-9-12 12 Morecambe X X X NaN
319 2004-9-12 12 Southport X X X NaN
320 2004-9-12 12 Newhaven Fort NaN X NaN NaN
321 2004-9-12 12 Isle of Sheppey NaN NaN NaN X
322 2004-9-12 12 Somerby NaN NaN NaN X
323 2004-9-12 12 Rotherham NaN NaN NaN X
324 2004-9-12 12 Thorpe Camp NaN NaN NaN X
325 2004-9-12 12 Newark NaN X NaN NaN
326 2004-9-12 12 Cosford NaN X NaN NaN
327 2004-9-13 13 Kings Lynn NaN X NaN NaN
328 2004-9-13 13 Norwich City 12:00 NaN X NaN NaN
329 2004-9-13 13 Norwich County 18:00 NaN X NaN NaN
330 2004-9-15 15 Coltishall NaN X NaN NaN
331 2004-9-15 15 Wyton NaN X NaN NaN
332 2004-9-15 15 Boston 11:00 NaN X NaN NaN
333 2004-9-15 15 Bentley Priory 19:00 NaN X NaN NaN
334 2004-9-15 15 Northolt 19:15 NaN X NaN NaN
335 2004-9-15 15 Hemswell X NaN NaN NaN
336 2004-9-16 16 Bicester 18:45 NaN X NaN NaN
337 2004-9-17 17 Shrivenham NaN X X NaN
338 2004-9-18 18 Biggin Hill NaN X x 2 X NaN
339 2004-9-18 18 Stanmore 16:25 NaN X NaN NaN
340 2004-9-18 18 Bentley Priory 16:30 NaN X NaN NaN
341 2004-9-18 18 Ginkelheide 11:00 NaN NaN NaN X
342 2004-9-18 18 Arnhem 18:30 NaN NaN NaN X
343 2004-9-18 18 Yeovilton X X X NaN
344 2004-9-18 18 Little Casterton X X X NaN
345 2004-9-19 19 Biggin Hill NaN X x 2 X NaN
346 2004-9-19 19 Croydon NaN X x 2 X NaN
347 2004-9-19 19 Sheringham NaN X NaN NaN
348 2004-9-19 19 West Runton NaN X NaN NaN
349 2004-9-19 19 Norwich NaN X NaN NaN
350 2004-9-19 19 Boston NaN X NaN NaN
351 2004-9-19 19 Arnham NaN NaN NaN X
352 2004-9-19 19 Nijmagen NaN NaN NaN X
353 2004-9-19 19 Oosterbeek NaN NaN NaN X
354 2004-9-25 25 Waddington NaN NaN NaN X
355 2004-9-25 25 Elvington NaN NaN NaN X
356 2004-9-25 25 Bovingdon NaN X NaN NaN
357 2004-9-25 25 Lichfield X X NaN NaN
358 2004-9-25 25 Grimsby X NaN NaN NaN
359 2004-9-26 26 Finningley NaN X NaN NaN
360 2004-9-26 26 Flixton X NaN NaN NaN
In [ ]:
In [ ]:
Best Regards,
Sandeep
GANGA SANDEEP KUMAR
Posts: 218
Threads: 27
Joined: May 2018
Jan-25-2020, 04:22 PM
(This post was last modified: Jan-25-2020, 04:24 PM by eddywinch82.)
Hi there,
This is the latest Python Code I have, which I run in Jupyter Notebook :- import pandas as pd
import requests
from bs4 import BeautifulSoup
res = requests.get("http://web.archive.org/web/20041020000138/http://www.raf.mod.uk/bbmf/displaydates.html")
soup = BeautifulSoup(res.content,'lxml')
table = soup.find_all('table', align="CENTER")[0]
df = pd.read_html(str(table))
df = df[0]
##################
##################
##################
pd.set_option('display.max_rows', 500)
pd.set_option('display.max_columns', 500)
pd.set_option('display.width', 1000)
#make df[0] to list
list=[]
for i in df[0]:
list.append(i)
#reverse the list to make split to sublist easier
list.reverse()
#split list to sublist using condition len(val)> 2
size = len(list)
idx_list = [idx + 1 for idx, val in
enumerate(list) if len(val) > 2]
res = [list[i: j] for i, j in
zip([0] + idx_list, idx_list +
([size] if idx_list[-1] != size else []))]
#make monthname to numbers and print
for i in res:
for j in range(len(i)):
if i[j].upper()=='JUNE':
i[j]='6'
elif i[j].upper() =='MAY':
i[j]='5'
elif i[j].upper() == 'APRIL':
i[j]='4'
elif i[j].upper() =='JANUARY':
i[j]='1'
elif i[j].upper() == 'FEBRUARY':
i[j]='2'
elif i[j].upper() =='MARCH':
i[j]='3'
elif i[j].upper() == 'JULY':
i[j]='7'
elif i[j].upper() =='AUGUST':
i[j]='8'
elif i[j].upper() == 'SEPTEMBER':
i[j]='9'
elif i[j].upper() =='OCTOBER':
i[j]='10'
elif i[j].upper() == 'NOVEMBER':
i[j]='11'
elif i[j].upper() =='DECEMBER':
i[j]='12'
#append string and append to new list
finallist=[]
for i in res:
for j in range(len(i)):
if j < len(i) - 1:
#print(f'2004-{i[-1]}-{i[j]}')
finallist.append(f'2004-{i[-1]}-{i[j]}')
#print(finallist)
finallist.reverse()
#print("\n=== ORIGINAL DF ===\n")
#print(df)
#convert dataframe to list
listtemp1=df.values.tolist()
#replace found below values with 0000_removable
removelist=['LOCATION','LANCASTER','SPITFIRE','HURRICANE','DAKOTA','DATE','JUNE','JANUARY','FEBRUARY','MARCH','MAY','JULY','AUGUST','SEPTEMBER','OCTOBER','NOVEMBER','DECEMBER','APRIL']
for i in listtemp1:
for j in range(len(i)):
for place in removelist:
if str(i[j]).upper()==place:
i[j]='0000_removable'
else:
pass
#remove sublists with the replaced values we redirected
dellist=['0000_removable', '0000_removable', '0000_removable', '0000_removable', '0000_removable', '0000_removable']
res = [i for i in listtemp1 if i != dellist]
#assign back to dataframe DF3
df3=pd.DataFrame()
df3=pd.DataFrame(res, columns=['Date','LOCATION','LANCASTER','SPITFIRE','HURRICANE','DAKOTA'])
#print("\n=== AFTER REMOVE month and column names from DF, assigned to new as DF3 ===\n")
#print(df3)
#now assign that sorted date list to dataframe DF3
idx = 0
#df3.insert(loc=idx, column='EDITED_DATE_FORMAT', value=finallist)
df3.insert(loc=idx, column='DATE', value=finallist)
pd.options.display.max_rows = 500
#print("\n=== FINAL DF3 after joining the edited date format column list ===\n")
#print(df3)
#validation logic if needed compare processed date from new joined "edited_Date_format" column with already existing "Date" column
#df3['ED1']= pd.to_datetime(df3['EDITED_DATE_FORMAT'],format='%Y-%m-%d').dt.day
#df3['validation of date'] = df3.apply(lambda x: str(x['ED1']) == x['Date'], axis=1)
#convert df3['EDITED_DATE_FORMAT'] column from object to datetime64 foramt
#df3['EDITED_DATE_FORMAT']= pd.to_datetime(df3['EDITED_DATE_FORMAT'],format='%Y-%m-%d')
##################
##################
##################
#df3 = df3.rename(columns=df.iloc[0])
#df3 = df.iloc[2:]
#df3.head(15)
pd.options.display.max_rows = 1000
display = df3[(df3['LOCATION'].str.contains('[a-zA-Z]')) & (df3['LANCASTER'].str.contains('X')) & (df3['SPITFIRE'].str.contains('X', na=True)) & (df3['DAKOTA'].str.contains('X', na=True))]
#print(display)
#display.drop('DAKOTA', axis=1, inplace=True)
display.drop('Date', axis=1, inplace=True)
display.dropna(subset=['SPITFIRE', 'HURRICANE'], how='all')
#print(display) It does what I want it to do, but I would like, the Dates shown in the DATE Column, to be in the Format Day-Month-Year i.e. 8-9-2004 . What do I need to change in that Code, to achieve that ?
Any help would be appreciated
Regards
Eddie Winch
Posts: 50
Threads: 2
Joined: Nov 2019
Jan-27-2020, 12:12 PM
(This post was last modified: Jan-27-2020, 12:12 PM by sandeep_ganga.)
Try this, converting object to datetime64 type
display['DATE']= pd.to_datetime(display['DATE'],format='%Y-%m-%d')
display['DATE']= pd.to_datetime(display['DATE']).dt.strftime('%d-%m-%Y') import pandas as pd
import requests
from bs4 import BeautifulSoup
res = requests.get("http://web.archive.org/web/20041020000138/http://www.raf.mod.uk/bbmf/displaydates.html")
soup = BeautifulSoup(res.content,'lxml')
table = soup.find_all('table', align="CENTER")[0]
df = pd.read_html(str(table))
df = df[0]
##################
##################
##################
pd.set_option('display.max_rows', 500)
pd.set_option('display.max_columns', 500)
pd.set_option('display.width', 1000)
#make df[0] to list
list=[]
for i in df[0]:
list.append(i)
#reverse the list to make split to sublist easier
list.reverse()
#split list to sublist using condition len(val)> 2
size = len(list)
idx_list = [idx + 1 for idx, val in
enumerate(list) if len(val) > 2]
res = [list[i: j] for i, j in
zip([0] + idx_list, idx_list +
([size] if idx_list[-1] != size else []))]
#make monthname to numbers and print
for i in res:
for j in range(len(i)):
if i[j].upper()=='JUNE':
i[j]='6'
elif i[j].upper() =='MAY':
i[j]='5'
elif i[j].upper() == 'APRIL':
i[j]='4'
elif i[j].upper() =='JANUARY':
i[j]='1'
elif i[j].upper() == 'FEBRUARY':
i[j]='2'
elif i[j].upper() =='MARCH':
i[j]='3'
elif i[j].upper() == 'JULY':
i[j]='7'
elif i[j].upper() =='AUGUST':
i[j]='8'
elif i[j].upper() == 'SEPTEMBER':
i[j]='9'
elif i[j].upper() =='OCTOBER':
i[j]='10'
elif i[j].upper() == 'NOVEMBER':
i[j]='11'
elif i[j].upper() =='DECEMBER':
i[j]='12'
#append string and append to new list
finallist=[]
for i in res:
for j in range(len(i)):
if j < len(i) - 1:
#print(f'2004-{i[-1]}-{i[j]}')
finallist.append(f'2004-{i[-1]}-{i[j]}')
#print(finallist)
finallist.reverse()
#print("\n=== ORIGINAL DF ===\n")
#print(df)
#convert dataframe to list
listtemp1=df.values.tolist()
#replace found below values with 0000_removable
removelist=['LOCATION','LANCASTER','SPITFIRE','HURRICANE','DAKOTA','DATE','JUNE','JANUARY','FEBRUARY','MARCH','MAY','JULY','AUGUST','SEPTEMBER','OCTOBER','NOVEMBER','DECEMBER','APRIL']
for i in listtemp1:
for j in range(len(i)):
for place in removelist:
if str(i[j]).upper()==place:
i[j]='0000_removable'
else:
pass
#remove sublists with the replaced values we redirected
dellist=['0000_removable', '0000_removable', '0000_removable', '0000_removable', '0000_removable', '0000_removable']
res = [i for i in listtemp1 if i != dellist]
#assign back to dataframe DF3
df3=pd.DataFrame()
df3=pd.DataFrame(res, columns=['Date','LOCATION','LANCASTER','SPITFIRE','HURRICANE','DAKOTA'])
#print("\n=== AFTER REMOVE month and column names from DF, assigned to new as DF3 ===\n")
#print(df3)
#now assign that sorted date list to dataframe DF3
idx = 0
#df3.insert(loc=idx, column='EDITED_DATE_FORMAT', value=finallist)
df3.insert(loc=idx, column='DATE', value=finallist)
pd.options.display.max_rows = 500
#print("\n=== FINAL DF3 after joining the edited date format column list ===\n")
#print(df3)
#validation logic if needed compare processed date from new joined "edited_Date_format" column with already existing "Date" column
#df3['ED1']= pd.to_datetime(df3['EDITED_DATE_FORMAT'],format='%Y-%m-%d').dt.day
#df3['validation of date'] = df3.apply(lambda x: str(x['ED1']) == x['Date'], axis=1)
#convert df3['EDITED_DATE_FORMAT'] column from object to datetime64 foramt
#df3['EDITED_DATE_FORMAT']= pd.to_datetime(df3['EDITED_DATE_FORMAT'],format='%Y-%m-%d')
#df3['EDITED_DATE_FORMAT']= pd.to_datetime(df3['EDITED_DATE_FORMAT']).dt.strftime('%d-%m-%Y')
#df["Date"] = pd.to_datetime(df["Date"]).dt.strftime('%Y-%m-%d')
##################
##################
##################
#df3 = df3.rename(columns=df.iloc[0])
#df3 = df.iloc[2:]
#df3.head(15)
pd.options.display.max_rows = 1000
display = df3[(df3['LOCATION'].str.contains('[a-zA-Z]')) & (df3['LANCASTER'].str.contains('X')) & (df3['SPITFIRE'].str.contains('X', na=True)) & (df3['DAKOTA'].str.contains('X', na=True))]
#print(display)
display['DATE']= pd.to_datetime(display['DATE'],format='%Y-%m-%d')
display['DATE']= pd.to_datetime(display['DATE']).dt.strftime('%d-%m-%Y')
##added two lines above to convert date format
#display.drop('DAKOTA', axis=1, inplace=True)
display.drop('Date', axis=1, inplace=True)
display.dropna(subset=['SPITFIRE', 'HURRICANE'], how='all')
#print(display) Best Regards,
Sandeep
GANGA SANDEEP KUMAR
|