KaneAI - World’s First E2E Software Testing Agent.Book a Demo 
How to use add_columns method in pandera
Best Python code snippet using pandera_python
queries.py
Source:queries.py 
...5from data_access.models import *6class user_views():7 def login_info(value):8 record = user_information.query.join(9 user_account).add_columns(10 user_information.first_name, 11 user_information.company_name,12 user_information.address, 13 user_account.id).filter(14 user_account.id==value).first()15 return record16 def profile_info(value):17 record = user_information.query.join(18 user_account19 ).add_columns(20 user_information.first_name,21 user_information.middle_name,22 user_information.last_name,23 user_information.company_name,24 user_information.bio,25 user_information.gender,26 user_information.birthday,27 user_information.address,28 user_information.address,29 user_information.telephone,30 user_information.mobile_number,31 user_account.username,32 user_account.password,33 user_account.email_address34 ).filter(user_information.id==value).first()35 return record36 def signatory_info(value):37 record = user_information.query.join(38 user_account39 ).add_columns(40 user_account.id,41 user_account.email_address,42 user_information.last_name43 ).filter(user_account.id==value).first()44 return record45 def profile_info_update(value):46 record = user_information.query.filter_by(id=current_user.id).first()47 return record48 def profile_acc_update(value):49 record = user_account.query.filter_by(id=current_user.id).first()50 return record51class linkage_views():52 def show_list(value):53 if value[0]=='all' and value[1]==' ' :54 record = user_account.query.join(55 user_information56 ).add_columns(57 user_account.id,58 user_account.info_id,59 user_information.company_name,60 (user_information.first_name + ' ' +61 func.left(user_information.middle_name,1) + '. ' +62 user_information.last_name).label('coordinator'),63 user_information.partner_thrust,64 user_information.bio,65 user_information.address,66 user_information.telephone,67 user_information.mobile_number,68 user_account.status69 ).filter(user_account.type==value[2]70 ).order_by(user_account.info_id.asc()71 ).paginate(int(value[3]), Config.POSTS_PER_PAGE, False)72 elif value[0]=='all' and value[1]!=' ' :73 record = user_account.query.join(74 user_information75 ).add_columns(76 user_account.id,77 user_account.info_id,78 user_information.company_name,79 (user_information.first_name + ' ' +80 func.left(user_information.middle_name,1) + '. ' +81 user_information.last_name).label('coordinator'),82 user_information.partner_thrust,83 user_information.bio,84 user_information.address,85 user_information.telephone,86 user_information.mobile_number,87 user_account.status88 ).filter(and_(user_account.type==value[2],89 or_(user_information.company_name.like('%'+value[1]+'%'),90 user_information.address.like('%'+value[1]+'%'),91 user_information.first_name.like('%'+value[1]+'%'),92 user_information.last_name.like('%'+value[1]+'%')))93 ).order_by(user_account.info_id.asc()94 ).paginate(int(value[3]), Config.POSTS_PER_PAGE, False)95 elif value[1]!=' ':96 record = user_account.query.join(97 user_information98 ).add_columns(99 user_account.id,100 user_account.info_id,101 user_information.company_name,102 (user_information.first_name + ' ' +103 func.left(user_information.middle_name,1) + '. ' +104 user_information.last_name).label('coordinator'),105 user_information.partner_thrust,106 user_information.bio,107 user_information.address,108 user_information.telephone,109 user_information.mobile_number,110 user_account.status,111 ).filter(and_(user_account.type==value[2],user_account.status==value[0],112 or_(user_information.company_name.like('%'+value[1]+'%'),113 user_information.address.like('%'+value[1]+'%'),114 user_information.first_name.like('%'+value[1]+'%'),115 user_information.last_name.like('%'+value[1]+'%')))116 ).order_by(user_account.info_id.asc()117 ).paginate(int(value[3]), Config.POSTS_PER_PAGE, False)118 else:119 record = user_account.query.join(120 user_information121 ).add_columns(122 user_account.id,123 user_account.info_id,124 user_information.company_name,125 (user_information.first_name + ' ' +126 func.left(user_information.middle_name,1) + '. ' +127 user_information.last_name).label('coordinator'),128 user_information.partner_thrust,129 user_information.bio,130 user_information.address,131 user_information.telephone,132 user_information.mobile_number,133 user_account.status,134 ).filter(and_(user_account.type==value[2], 135 user_account.status==value[0])136 ).order_by(user_account.info_id.asc()137 ).paginate(int(value[3]), Config.POSTS_PER_PAGE, False) 138 return record139 def show_info(value):140 141 record = user_account.query.join(142 user_information143 ).add_columns(144 user_account.id,145 user_account.info_id,146 user_information.first_name,147 user_information.middle_name,148 user_information.last_name,149 user_information.company_name,150 user_information.gender,151 user_information.birthday,152 user_information.address,153 user_information.telephone,154 user_information.mobile_number,155 user_account.username,156 user_account.email_address,157 user_account.last_active,158 user_account.status,159 ).filter(user_account.info_id==value[0]160 ).first()161 membership = audit_trail.query.filter(162 and_(audit_trail.affected_id==record.id, 163 audit_trail.target==value[1], 164 audit_trail.type==2)).first()165 return record, membership166 def target_linkages():167 record = user_information.query.join(168 user_account).add_columns(169 user_information.id, user_account.type, 170 user_information.address, user_information.company_name).filter(or_(171 user_account.type==4, user_account.type==3)).order_by(172 user_information.address.asc()173 ).all()174 return record175class event_views():176 def show_list(value):177 if value[0]=='all' and value[1]==' ':178 record = event_information.query.join(179 user_information, proposal_tracker180 ).add_columns(181 user_information.company_name,182 user_information.address,183 event_information.id,184 event_information.organizer_id,185 event_information.name,186 event_information.description,187 event_information.objective,188 event_information.location,189 event_information.event_date,190 event_information.min_age,191 event_information.max_age,192 event_information.thrust,193 event_information.type,194 event_information.event_status,195 proposal_tracker.proposed_on,196 proposal_tracker.status197 ).paginate(int(value[2]), Config.POSTS_PER_PAGE, False)198 elif value[0]=='all' and value[1]!=' ':199 record = event_information.query.join(200 user_information, proposal_tracker201 ).add_columns(202 user_information.company_name,203 user_information.address,204 event_information.id,205 event_information.organizer_id,206 event_information.name,207 event_information.description,208 event_information.objective,209 event_information.location,210 event_information.event_date,211 event_information.min_age,212 event_information.max_age,213 event_information.thrust,214 event_information.type,215 event_information.event_status,216 proposal_tracker.proposed_on,217 proposal_tracker.status218 ).filter(or_(user_information.company_name.like('%'+value[1]+'%'),219 event_information.name.like('%'+value[1]+'%'))220 ).paginate(int(value[2]), Config.POSTS_PER_PAGE, False)221 elif value[1]!=' ':222 record = event_information.query.join(223 user_information, proposal_tracker224 ).add_columns(225 user_information.company_name,226 user_information.address,227 event_information.id,228 event_information.organizer_id,229 event_information.name,230 event_information.description,231 event_information.objective,232 event_information.location,233 event_information.event_date,234 event_information.min_age,235 event_information.max_age,236 event_information.thrust,237 event_information.type,238 event_information.event_status,239 proposal_tracker.proposed_on,240 proposal_tracker.status241 ).filter(and_(event_information.event_status==value[0],or_(242 user_information.company_name.like('%'+value[1]+'%'),243 event_information.name.like('%'+value[1]+'%')))244 ).paginate(int(value[2]), Config.POSTS_PER_PAGE, False)245 else:246 record = event_information.query.join(247 user_information, proposal_tracker248 ).add_columns(249 user_information.company_name,250 user_information.address,251 event_information.id,252 event_information.organizer_id,253 event_information.name,254 event_information.description,255 event_information.objective,256 event_information.location,257 event_information.event_date,258 event_information.min_age,259 event_information.max_age,260 event_information.thrust,261 event_information.type,262 event_information.event_status,263 proposal_tracker.proposed_on,264 proposal_tracker.status265 ).filter(event_information.event_status==value[0]266 ).paginate(int(value[2]), Config.POSTS_PER_PAGE, False) 267 return record268 def show_info(value):269 270 record = event_information.query.join(271 user_information, proposal_tracker272 ).add_columns(273 event_information.id,274 event_information.organizer_id,275 user_information.company_name,276 user_information.first_name,277 user_information.last_name,278 event_information.name,279 event_information.description,280 event_information.objective,281 event_information.budget,282 event_information.location,283 event_information.event_date,284 event_information.thrust,285 event_information.event_status,286 event_information.participant_no,287 event_information.max_age,288 event_information.min_age,289 proposal_tracker.proposed_on,290 proposal_tracker.recop_accepted,291 proposal_tracker.fmi_signed,292 proposal_tracker.acad_signed,293 proposal_tracker.approved_on,294 proposal_tracker.status295 ).filter(event_information.id==value296 ).first()297 return record298 def show_participants(value):299 if value[1]==' ':300 record = event_participation.query.join(301 user_information302 ).add_columns(303 user_information.id,304 event_participation.event_id,305 (user_information.last_name + ', ' +306 user_information.first_name + ' '+ 307 func.left(user_information.middle_name,1) + '. '308 ).label('name'),309 user_information.address,310 event_participation.is_target,311 event_participation.status312 ).filter(and_(event_participation.event_id==value[0], 313 event_participation.status!='R', event_participation.is_target=='N')314 ).order_by(user_information.last_name.asc()).all()315 else:316 record = event_participation.query.join(317 user_information318 ).add_columns(319 user_information.id,320 event_participation.event_id,321 (user_information.last_name + ', ' +322 user_information.first_name + ' '+ 323 func.left(user_information.middle_name,1) + '. '324 ).label('name'),325 user_information.address,326 event_participation.is_target,327 event_participation.status328 ).filter(and_(event_participation.event_id==value[0], 329 event_participation.status!='R',330 event_participation.is_target=='N', or_(331 user_information.last_name.like('%'+value[1]+'%'),332 user_information.first_name.like('%'+value[1]+'%'),333 user_information.middle_name.like('%'+value[1]+'%'),334 user_information.address.like('%'+value[1]+'%')))335 ).order_by(user_information.last_name.asc()).all()336 return record337 def show_attended(value):338 if value[1]==' ':339 record = event_participation.query.join(340 user_information341 ).add_columns(342 user_information.id,343 event_participation.event_id,344 (user_information.last_name + ', ' +345 user_information.first_name + ' '+ 346 func.left(user_information.middle_name,1) + '. '347 ).label('name'),348 user_information.address,349 event_participation.is_target,350 event_participation.status, 351 event_participation.rating,352 event_participation.comment353 ).filter(and_(event_participation.event_id==value[0], 354 event_participation.status=='P', event_participation.is_target=='N')355 ).order_by(user_information.last_name.asc()).all()356 else:357 record = event_participation.query.join(358 user_information359 ).add_columns(360 user_information.id,361 event_participation.event_id,362 (user_information.last_name + ', ' +363 user_information.first_name + ' '+ 364 func.left(user_information.middle_name,1) + '. '365 ).label('name'),366 user_information.address,367 event_participation.is_target,368 event_participation.status, 369 event_participation.rating,370 event_participation.comment371 ).filter(and_(event_participation.event_id==value[0], 372 event_participation.status=='P',373 event_participation.is_target=='N', or_(374 user_information.last_name.like('%'+value[1]+'%'),375 user_information.first_name.like('%'+value[1]+'%'),376 user_information.middle_name.like('%'+value[1]+'%'),377 user_information.address.like('%'+value[1]+'%')))378 ).order_by(user_information.last_name.asc()).all()379 return record380 def religious_admin_events(value):381 if value[0]=='all' and value[1]==' ':382 record = event_information.query.join(383 user_information384 ).add_columns(385 event_information.id,386 event_information.organizer_id,387 event_information.name,388 event_information.description,389 event_information.objective,390 event_information.budget,391 event_information.location,392 event_information.event_date,393 event_information.thrust,394 event_information.event_status,395 user_information.company_name396 ).filter(or_(event_information.event_status=='S', event_information.event_status=='F')397 ).paginate(int(value[2]), Config.POSTS_PER_PAGE, False)398 399 elif value[0]=='all' and value[1]!=' ':400 record = event_information.query.join(401 user_information402 ).add_columns(403 event_information.id,404 event_information.organizer_id,405 event_information.name,406 event_information.description,407 event_information.objective,408 event_information.budget,409 event_information.location,410 event_information.event_date,411 event_information.thrust,412 event_information.event_status,413 user_information.company_name414 ).filter(or_(event_information.event_status=='S', event_information.event_status=='F'),415 or_(user_information.company_name.like('%'+value[1]+'%'),416 event_information.name.like('%'+value[1]+'%'))417 ).paginate(int(value[2]), Config.POSTS_PER_PAGE, False) 418 elif value[1]!=' ':419 record = event_information.query.join(420 user_information421 ).add_columns(422 event_information.id,423 event_information.organizer_id,424 event_information.name,425 event_information.description,426 event_information.objective,427 event_information.budget,428 event_information.location,429 event_information.event_date,430 event_information.thrust,431 event_information.event_status,432 user_information.company_name433 ).filter(event_information.event_status==value[0],434 or_(user_information.company_name.like('%'+value[1]+'%'),435 event_information.name.like('%'+value[1]+'%'))436 ).paginate(int(value[2]), Config.POSTS_PER_PAGE, False) 437 else:438 record = event_information.query.join(439 user_information440 ).add_columns(441 event_information.id,442 event_information.organizer_id,443 event_information.name,444 event_information.description,445 event_information.objective,446 event_information.budget,447 event_information.location,448 event_information.event_date,449 event_information.thrust,450 event_information.event_status,451 user_information.company_name452 ).filter(event_information.event_status==value[0],or_(453 user_information.company_name.like('%'+value[1]+'%'),454 event_information.name.like('%'+value[1]+'%'))455 ).paginate(int(value[2]), Config.POSTS_PER_PAGE, False) 456 return record457 def community_events(value):458 if value[0]=='all' and value[1]==' ':459 record = event_participation.query.join(460 event_information, user_information461 ).add_columns(462 event_information.id,463 event_information.organizer_id,464 event_information.name,465 event_information.description,466 event_information.objective,467 event_information.budget,468 event_information.location,469 event_information.event_date,470 event_information.thrust,471 event_information.event_status,472 event_participation.participant_id,473 user_information.company_name474 ).filter(and_(event_participation.participant_id==value[3],475 or_(event_information.event_status=='S', event_information.event_status=='F'))476 ).paginate(int(value[2]), Config.POSTS_PER_PAGE, False)477 478 elif value[0]=='all' and value[1]!=' ':479 record = event_participation.query.join(480 event_information, user_information481 ).add_columns(482 event_information.id,483 event_information.organizer_id,484 event_information.name,485 event_information.description,486 event_information.objective,487 event_information.budget,488 event_information.location,489 event_information.event_date,490 event_information.thrust,491 event_information.event_status,492 event_participation.participant_id,493 user_information.company_name494 ).filter(and_(event_participation.participant_id==value[3], 495 or_(event_information.event_status=='S', event_information.event_status=='F'),496 or_(user_information.company_name.like('%'+value[1]+'%'),497 event_information.name.like('%'+value[1]+'%')))498 ).paginate(int(value[2]), Config.POSTS_PER_PAGE, False) 499 elif value[1]!=' ':500 record = event_participation.query.join(501 event_information, user_information502 ).add_columns(503 event_information.id,504 event_information.organizer_id,505 event_information.name,506 event_information.description,507 event_information.objective,508 event_information.budget,509 event_information.location,510 event_information.event_date,511 event_information.thrust,512 event_information.event_status,513 event_participation.participant_id,514 user_information.company_name515 ).filter(and_(event_participation.participant_id==value[3], 516 event_information.event_status==value[0],517 or_(user_information.company_name.like('%'+value[1]+'%'),518 event_information.name.like('%'+value[1]+'%')))519 ).paginate(int(value[2]), Config.POSTS_PER_PAGE, False) 520 else:521 record = event_participation.query.join(522 event_information, user_information523 ).add_columns(524 event_information.id,525 event_information.organizer_id,526 event_information.name,527 event_information.description,528 event_information.objective,529 event_information.budget,530 event_information.location,531 event_information.event_date,532 event_information.thrust,533 event_information.event_status,534 event_participation.participant_id,535 user_information.company_name536 ).filter(and_(event_participation.participant_id==value[3], 537 event_information.event_status==value[0],or_(538 user_information.company_name.like('%'+value[1]+'%'),539 event_information.name.like('%'+value[1]+'%')))540 ).paginate(int(value[2]), Config.POSTS_PER_PAGE, False) 541 return record542 def linkages_events(value):543 if value[0]=='all' and value[1]==' ':544 record = event_information.query.join(545 user_information, proposal_tracker546 ).add_columns(547 event_information.id,548 event_information.organizer_id,549 event_information.name,550 event_information.description,551 event_information.objective,552 event_information.budget,553 event_information.location,554 event_information.event_date,555 event_information.thrust,556 event_information.event_status,557 user_information.company_name,558 user_information.address,559 proposal_tracker.proposed_on,560 proposal_tracker.recop_accepted,561 proposal_tracker.fmi_signed,562 proposal_tracker.acad_signed,563 proposal_tracker.approved_on,564 proposal_tracker.status565 ).filter(event_information.organizer_id==current_user.info_id566 ).paginate(int(value[2]), Config.POSTS_PER_PAGE, False)567 568 elif value[0]=='all' and value[1]!=' ':569 record = event_information.query.join(570 user_information, proposal_tracker571 ).add_columns(572 event_information.id,573 event_information.organizer_id,574 event_information.name,575 event_information.description,576 event_information.objective,577 event_information.budget,578 event_information.location,579 event_information.event_date,580 event_information.thrust,581 event_information.event_status,582 user_information.company_name,583 user_information.address,584 proposal_tracker.proposed_on,585 proposal_tracker.recop_accepted,586 proposal_tracker.fmi_signed,587 proposal_tracker.acad_signed,588 proposal_tracker.approved_on,589 proposal_tracker.status590 ).filter(and_(event_information.organizer_id==current_user.info_id,591 or_(user_information.company_name.like('%'+value[1]+'%'),592 event_information.name.like('%'+value[1]+'%')))593 ).paginate(int(value[2]), Config.POSTS_PER_PAGE, False) 594 elif value[1]!=' ':595 record = event_information.query.join(596 ser_information, proposal_tracker597 ).add_columns(598 event_information.id,599 event_information.organizer_id,600 event_information.name,601 event_information.description,602 event_information.objective,603 event_information.budget,604 event_information.location,605 event_information.event_date,606 event_information.thrust,607 event_information.event_status,608 user_information.company_name,609 user_information.address,610 proposal_tracker.proposed_on,611 proposal_tracker.recop_accepted,612 proposal_tracker.fmi_signed,613 proposal_tracker.acad_signed,614 proposal_tracker.approved_on,615 proposal_tracker.status616 ).filter(and_(event_information.organizer_id==current_user.info_id, 617 event_information.event_status==value[0],618 or_(user_information.company_name.like('%'+value[1]+'%'),619 event_information.name.like('%'+value[1]+'%')))620 ).paginate(int(value[2]), Config.POSTS_PER_PAGE, False) 621 else:622 record = event_information.query.join(623 user_information,proposal_tracker624 ).add_columns(625 event_information.id,626 event_information.organizer_id,627 event_information.name,628 event_information.description,629 event_information.objective,630 event_information.budget,631 event_information.location,632 event_information.event_date,633 event_information.thrust,634 event_information.event_status,635 user_information.company_name,636 user_information.address,637 proposal_tracker.proposed_on,638 proposal_tracker.recop_accepted,639 proposal_tracker.fmi_signed,640 proposal_tracker.acad_signed,641 proposal_tracker.approved_on,642 proposal_tracker.status643 ).filter(and_(event_information.organizer_id==current_user.info_id, 644 event_information.event_status==value[0],or_(645 user_information.company_name.like('%'+value[1]+'%'),646 event_information.name.like('%'+value[1]+'%')))647 ).paginate(int(value[2]), Config.POSTS_PER_PAGE, False) 648 return record649 def events_organized(value, search):650 if value=='all' and search==' ':651 record = event_information.query.join(652 user_information, proposal_tracker653 ).add_columns(654 user_information.company_name,655 user_information.address,656 event_information.id,657 event_information.name,658 event_information.description,659 event_information.objective,660 event_information.location,661 event_information.event_date,662 event_information.min_age,663 event_information.max_age,664 event_information.thrust,665 event_information.type,666 event_information.event_status,667 proposal_tracker.proposed_on,668 proposal_tracker.status669 ).filter(event_information.organizer_id==current_user.info_id670 ).order_by(proposal_tracker.proposed_on.desc()671 ).all()672 elif value=='all' and search!=' ':673 record = event_information.query.join(674 user_information, proposal_tracker675 ).add_columns(676 user_information.company_name,677 user_information.address,678 event_information.id,679 event_information.name,680 event_information.description,681 event_information.objective,682 event_information.location,683 event_information.event_date,684 event_information.min_age,685 event_information.max_age,686 event_information.thrust,687 event_information.type,688 event_information.event_status,689 proposal_tracker.proposed_on,690 proposal_tracker.status691 ).filter(and_(event_information.organizer_id==current_user.info_id,692 or_(user_information.company_name.like('%'+search+'%'),693 event_information.name.like('%'+search+'%')))694 ).order_by(proposal_tracker.proposed_on.desc()695 ).all()696 elif search!=' ':697 record = event_information.query.join(698 user_information, proposal_tracker699 ).add_columns(700 user_information.company_name,701 user_information.address,702 event_information.id,703 event_information.name,704 event_information.description,705 event_information.objective,706 event_information.location,707 event_information.event_date,708 event_information.min_age,709 event_information.max_age,710 event_information.thrust,711 event_information.type,712 event_information.event_status,713 proposal_tracker.proposed_on,714 proposal_tracker.status715 ).filter(and_(event_information.organizer_id==current_user.info_id,716 event_information.event_status==value,or_(717 user_information.company_name.like('%'+search+'%'),718 event_information.name.like('%'+search+'%')))719 ).order_by(proposal_tracker.proposed_on.desc()720 ).all()721 else:722 record = event_information.query.join(723 user_information, proposal_tracker724 ).add_columns(725 user_information.company_name,726 user_information.address,727 event_information.id,728 event_information.name,729 event_information.description,730 event_information.objective,731 event_information.location,732 event_information.event_date,733 event_information.min_age,734 event_information.max_age,735 event_information.thrust,736 event_information.type,737 event_information.event_status,738 proposal_tracker.proposed_on,739 proposal_tracker.status740 ).filter(event_information.organizer_id==current_user.info_id741 ).order_by(proposal_tracker.proposed_on.desc()742 ).all()743 return record744 def select_list():745 record = event_information.query.filter(event_information.event_status=='S').all()746 return record747 def comments(value):748 record = event_participation.query.join(749 user_information750 ).add_columns(751 (user_information.first_name + ' ' +752 func.left(user_information.middle_name,1) + '. ' +753 user_information.last_name).label('name'),754 event_participation.comment,755 event_participation.rating756 ).filter(and_(event_participation.event_id==value,757 event_participation.comment!=None, event_participation.comment!='')758 ).all()759 return record760 def registered_events(value):761 if value[0]=='all' and value[1]==' ':762 record = event_information.query.join(763 user_information, proposal_tracker764 ).add_columns(765 event_information.id,766 event_information.organizer_id,767 event_information.name,768 event_information.description,769 event_information.objective,770 event_information.budget,771 event_information.location,772 event_information.event_date,773 event_information.thrust,774 event_information.event_status,775 user_information.company_name,776 user_information.address,777 proposal_tracker.proposed_on,778 proposal_tracker.status779 ).filter(or_(event_information.event_status=='S',780 event_information.event_status=='F')781 ).paginate(int(value[2]), Config.POSTS_PER_PAGE, False)782 783 elif value[0]=='all' and value[1]!=' ':784 record = event_information.query.join(785 user_information, proposal_tracker786 ).add_columns(787 event_information.id,788 event_information.organizer_id,789 event_information.name,790 event_information.description,791 event_information.objective,792 event_information.budget,793 event_information.location,794 event_information.event_date,795 event_information.thrust,796 event_information.event_status,797 user_information.company_name,798 user_information.address,799 proposal_tracker.proposed_on,800 proposal_tracker.status801 ).filter(and_(event_information.event_status=='S',802 event_information.event_status=='F',803 or_(user_information.company_name.like('%'+value[1]+'%'),804 event_information.name.like('%'+value[1]+'%')))805 ).paginate(int(value[2]), Config.POSTS_PER_PAGE, False) 806 elif value[1]!=' ':807 record = event_information.query.join(808 ser_information, proposal_tracker809 ).add_columns(810 event_information.id,811 event_information.organizer_id,812 event_information.name,813 event_information.description,814 event_information.objective,815 event_information.budget,816 event_information.location,817 event_information.event_date,818 event_information.thrust,819 event_information.event_status,820 user_information.company_name,821 user_information.address,822 proposal_tracker.proposed_on,823 proposal_tracker.status824 ).filter(and_(event_information.event_status==value[0],825 or_(user_information.company_name.like('%'+value[1]+'%'),826 event_information.name.like('%'+value[1]+'%')))827 ).paginate(int(value[2]), Config.POSTS_PER_PAGE, False) 828 else:829 record = event_information.query.join(830 user_information,proposal_tracker831 ).add_columns(832 event_information.id,833 event_information.organizer_id,834 event_information.name,835 event_information.description,836 event_information.objective,837 event_information.budget,838 event_information.location,839 event_information.event_date,840 event_information.thrust,841 event_information.event_status,842 user_information.company_name,843 user_information.address,844 proposal_tracker.proposed_on,845 proposal_tracker.status846 ).filter(and_(event_information.event_status==value[0],or_(847 user_information.company_name.like('%'+value[1]+'%'),848 event_information.name.like('%'+value[1]+'%')))849 ).paginate(int(value[2]), Config.POSTS_PER_PAGE, False) 850 return record 851class community_views():852 def members_list(search):853 if search==' ' :854 record = community_member.query.join(855 user_information, 856 or_(857 community_member.member_id==user_information.id,858 community_member.community_id==user_information.id)859 ).add_columns(860 func.IF(user_information.id!=current_user.info_id,(user_information.first_name + ' ' +861 func.left(user_information.middle_name,1) + '. ' +862 user_information.last_name),'').label('member'),863 user_information.id,864 user_information.gender,865 user_information.birthday,866 user_information.address,867 user_information.telephone,868 user_information.mobile_number,869 community_member.member_id,870 community_member.occupation,871 community_member.income,872 community_member.religion,873 community_member.status,874 user_information.id875 ).filter(community_member.community_id==current_user.info_id876 ).order_by(user_information.id.asc()877 ).all()878 else:879 record = community_member.query.join(880 user_information,881 or_(882 community_member.member_id==user_information.id,883 community_member.community_id==user_information.id)884 ).add_columns(885 func.IF(user_information.id!=current_user.info_id,(user_information.first_name + ' ' +886 func.left(user_information.middle_name,1) + '. ' +887 user_information.last_name),'').label('member'),888 user_information.id,889 user_information.gender,890 user_information.birthday,891 user_information.address,892 user_information.telephone,893 user_information.mobile_number,894 community_member.member_id,895 community_member.occupation,896 community_member.income,897 community_member.religion,898 community_member.status,899 user_information.id900 ).filter(and_(community_member.community_id==current_user.info_id,901 or_(user_information.last_name.like('%'+search+'%'),902 user_information.first_name .like('%'+search+'%'),903 user_information.last_name.like('%'+search+'%'),904 user_information.address.like('%'+search+'%'),))905 ).all() 906 return record907 def members_show(value):908 record = community_member.query.join(909 user_information, 910 or_(911 community_member.member_id==user_information.id,912 community_member.community_id==user_information.id)913 ).add_columns(914 func.IF(user_information.id!=value,(user_information.first_name + ' ' +915 func.left(user_information.middle_name,1) + '. ' +916 user_information.last_name),'').label('member'),917 user_information.id,918 user_information.gender,919 user_information.birthday,920 user_information.address,921 user_information.telephone,922 user_information.mobile_number,923 community_member.member_id,924 community_member.occupation,925 community_member.income,926 community_member.religion,927 community_member.status,928 user_information.id929 ).filter(community_member.community_id==value930 ).order_by(user_information.id.asc()931 ).all()932 return record933 def event_participants(value):934 sub1 = community_member.query.join(935 user_information,936 community_member.member_id==user_information.id937 ).join(event_participation938 ).add_columns(939 (user_information.first_name + ' ' +940 func.left(user_information.middle_name,1) + '. ' +941 user_information.last_name).label('name'),942 user_information.id,943 event_participation.status.label('status'),944 func.IF(community_member.occupation==None,"Unemployed",community_member.occupation).label('occupation'),945 community_member.religion,946 user_information.address947 ).filter(community_member.community_id==current_user.info_id, event_participation.event_id==value948 )949 sub2 = community_member.query.join(950 user_information,951 community_member.member_id==user_information.id952 ).add_columns(953 (user_information.first_name + ' ' +954 func.left(user_information.middle_name,1) + '. ' +955 user_information.last_name).label('name'),956 user_information.id,957 community_member.status.label('status'),958 func.IF(community_member.occupation==None,"Unemployed",community_member.occupation).label('occupation'),959 community_member.religion,960 user_information.address961 ).filter(community_member.community_id==current_user.info_id, community_member.status=='A')962 record = sub1.union(sub2).group_by(user_information.id).all()963 return record964class donation_views():965 def show_list(value):966 sub1 = donation.query.join(967 user_information, 968 donation.sponsee_id==user_information.id969 ).add_columns(970 donation.id,971 user_information.address.label('sponsee'),972 user_information.company_name,973 donation.event_id,974 donation.sponsee_id,975 donation.sponsor_id,976 donation.status,977 donation.date_given,978 donation.transaction_slip,979 func.IF(donation.amount==0.00,'In kind',donation.amount).label('amount')980 ).filter(donation.sponsee_id!=None)981 sub2 = donation.query.join(982 event_information).join(983 user_information, 984 donation.sponsor_id==user_information.id).add_columns(985 donation.id,986 event_information.name.label('sponsee'),987 user_information.company_name,988 donation.event_id,989 donation.sponsee_id,990 donation.sponsor_id,991 donation.status,992 donation.date_given,993 donation.transaction_slip,994 func.IF(donation.amount==0.00,'In kind',donation.amount).label('amount')995 ).filter(donation.event_id!=None)996 if value[0]=='all' and value[1]==' ' :997 record = sub1.union(sub2).order_by(donation.id.asc()998 ).paginate(int(value[2]), Config.POSTS_PER_PAGE, False) 999 elif value[0]=='all' and value[1]!=' ' :1000 record = sub1.union(sub2).filter(or_(1001 event_information.name.like('%'+value[1]+'%'),1002 user_information.address.like('%'+value[1]+'%'),1003 donation.amount.like('%'+value[1]+'%'))1004 ).group_by(donation.id1005 ).order_by(donation.id.asc()1006 ).paginate(int(value[2]), Config.POSTS_PER_PAGE, False) 1007 elif value[1]!=' ':1008 record = sub1.union(sub2).filter(and_(1009 donation.status==value[0],or_(1010 event_information.name.like('%'+value[1]+'%'),1011 user_information.address.like('%'+value[1]+'%'),1012 donation.amount.like('%'+value[1]+'%')))1013 ).group_by(donation.id1014 ).order_by(donation.id.asc()1015 ).paginate(int(value[2]), Config.POSTS_PER_PAGE, False) 1016 else:1017 record = sub1.union(sub2).filter(donation.status==value[0]1018 ).order_by(donation.id.asc()1019 ).paginate(int(value[2]), Config.POSTS_PER_PAGE, False) 1020 return record1021 def donation_history(value):1022 sub1 = donation.query.join(1023 user_information, 1024 donation.sponsee_id==user_information.id1025 ).add_columns(1026 donation.id,1027 user_information.address.label('sponsee'),1028 user_information.company_name,1029 donation.event_id,1030 donation.sponsee_id,1031 donation.status,1032 donation.date_given,1033 func.IF(donation.amount==0.00,'In kind',donation.amount).label('amount')1034 ).filter(donation.sponsee_id!=None)1035 sub2 = donation.query.join(1036 event_information).join(1037 user_information, 1038 donation.sponsor_id==user_information.id).add_columns(1039 donation.id,1040 event_information.name.label('sponsee'),1041 user_information.company_name,1042 donation.event_id,1043 donation.sponsee_id,1044 donation.status,1045 donation.date_given,1046 func.IF(donation.amount==0.00,'In kind',donation.amount).label('amount')1047 ).filter(donation.event_id!=None)1048 record = sub1.union(sub2).filter(donation.sponsor_id==value).all()1049 return record1050 1051 def show_sponsors():1052 record = donation.query.join(1053 user_information, 1054 donation.sponsor_id==user_information.id1055 ).add_columns(1056 donation.id.label('did'),1057 user_information.id.label('id'),1058 donation.sponsor_id==user_information.id).add_columns(1059 donation.id,1060 (user_information.first_name + ' ' +1061 func.left(user_information.middle_name,1) + '. ' +1062 user_information.last_name).label('name'),1063 user_information.company_name).all()1064 return record1065 def breakdown():1066 record = inventory.query.join(1067 inventory_type1068 ).add_columns(1069 inventory.id,1070 inventory.donation_id,1071 inventory_type.name,1072 inventory.in_stock,1073 inventory.given,1074 inventory.expired1075 ).all()1076 return record1077class inventory_views():1078 def show_list(value):1079 if value[0]==' ':1080 record = inventory.query.join(1081 inventory_type1082 ).add_columns(1083 inventory.type_id,1084 inventory_type.name,1085 func.SUM(inventory.in_stock).label('in_stock'),1086 func.SUM(inventory.given).label('given'),1087 func.SUM(inventory.expired).label('expired'),1088 func.COUNT(inventory.id).label('total'),1089 func.COUNT(inventory.donation_id).label('donations')1090 ).group_by(inventory.type_id1091 ).order_by(inventory_type.name.asc()1092 ).paginate(int(value[1]), Config.POSTS_PER_PAGE, False) 1093 else:1094 record = inventory.query.join(1095 inventory_type1096 ).add_columns(1097 inventory.type_id,1098 inventory_type.name,1099 func.SUM(inventory.in_stock).label('in_stock'),1100 func.SUM(inventory.given).label('given'),1101 func.SUM(inventory.expired).label('expired'),1102 func.COUNT(inventory.id).label('total'),1103 func.COUNT(inventory.donation_id).label('donations')1104 ).filter(inventory_type.name.like('%'+value[0]+'%')1105 ).group_by(inventory.type_id1106 ).order_by(inventory_type.name.asc()1107 ).paginate(int(value[1]), Config.POSTS_PER_PAGE, False) ...
1_combine_intermediate-cv.py
Source:1_combine_intermediate-cv.py
...21dataframe = datasetify(str(here("./data/intermediate/agriculture/ag_indicator_cv.tif")), 22 "agriculture")23dataframe24# flatten other rasters and add them to the dataset25def add_columns(file, name):26 ar = gdal.Open(str(file)).ReadAsArray()27 if len(ar.shape) == 2:28 ar = ar.reshape(ar.shape[0]*ar.shape[1]) #flatten the array29 dataframe[name] = ar30 elif len(ar.shape) == 3:31 ar = ar.reshape(ar.shape[0], ar.shape[1]*ar.shape[2]) #flatten the array same as above32 ar = ar.reshape(ar.shape[0]*ar.shape[1]) # flatten again33 dataframe[name] = ar34 else: 35 raise Exception("Unexpected number of dimensions")36################################################################37# add all time invarying variables38add_columns(str(here("./data/intermediate/counterf/counterf_indicator_cv.tif")), 39 "counterfactual")40# add the extra counterfactual datasets 41add_columns(str(here("./data/intermediate/counterf/fveg_indicator_cv.tif")), 42 "fveg")43add_columns(str(here("./data/intermediate/counterf/potected_areas/CPAD123_indicator_cv.tif")), 44 "cpad")45add_columns(str(here("./data/intermediate/counterf/cpad_fveg_indicator_cv.tif")), 46 "cpad_fveg")47add_columns(str(here("./data/intermediate/counterf/cdl_fveg_indicator_cv.tif")), 48 "cdl_fveg")49add_columns(str(here("./data/intermediate/topography/elevation_cv.tif")), 50 "elevation")51add_columns(str(here("./data/intermediate/topography/aspect_cv.tif")), 52 "aspect")53add_columns(str(here("./data/intermediate/topography/slope_cv.tif")), 54 "slope")55add_columns(str(here("./data/intermediate/CA_storie/CA_storie_cv.tif")), 56 "soil")57add_columns(str(here("./data/intermediate/water/water21_cv.tif")), 58 "water21")59add_columns(str(here("./data/intermediate/water/water51_cv.tif")), 60 "water51")61add_columns(str(here("./data/intermediate/water/water101_cv.tif")), 62 "water101")63add_columns(str(here("./data/intermediate/water/water201_cv.tif")), 64 "water201")65################################################################66# save the time invarying version67dataframe.to_csv(str(here("./data/for_analysis/full_grid_time_invariant_cv.csv")), index=False)68################################################################69# add ET and PET70add_columns(str(here("./data/intermediate/PET/PET_grouped_0_cv.tif")), 71 "PET0")72add_columns(str(here("./data/intermediate/PET/PET_grouped_1_cv.tif")), 73 "PET1")74add_columns(str(here("./data/intermediate/PET/PET_grouped_2_cv.tif")), 75 "PET2")76add_columns(str(here("./data/intermediate/PET/PET_grouped_3_cv.tif")), 77 "PET3")78add_columns(str(here("./data/intermediate/PET/PET_grouped_4_cv.tif")), 79 "PET4")80add_columns(str(here("./data/intermediate/PET/PET_grouped_5_cv.tif")), 81 "PET5")82add_columns(str(here("./data/intermediate/ECOSTRESS_cv/ET_mean/0.tif")), 83 "ET0")84add_columns(str(here("./data/intermediate/ECOSTRESS_cv/ET_mean/1.tif")), 85 "ET1")86add_columns(str(here("./data/intermediate/ECOSTRESS_cv/ET_mean/2.tif")), 87 "ET2")88add_columns(str(here("./data/intermediate/ECOSTRESS_cv/ET_mean/3.tif")), 89 "ET3")90add_columns(str(here("./data/intermediate/ECOSTRESS_cv/ET_mean/4.tif")), 91 "ET4")92add_columns(str(here("./data/intermediate/ECOSTRESS_cv/ET_mean/5.tif")), 93 "ET5")94# save the ET PET version95dataframe.to_csv(str(here("./data/for_analysis/full_grid_not_tidy_cv.csv")), index=False)96# filter only agriculture97agriculture = dataframe.query('agriculture==1')98# save99agriculture.to_csv(str(here("./data/for_analysis/agriculture_not_tidy_cv.csv")), index=False)100# filter only counterfactual101counterfactual = dataframe.query('counterfactual==1')102# save103counterfactual.to_csv(str(here("./data/for_analysis/counterfactual_not_tidy_cv.csv")), index=False)104# filter only fveg105fveg = dataframe.query('fveg==1')106# save107fveg.to_csv(str(here("./data/for_analysis/fveg_not_tidy_cv.csv")), index=False)108# filter only cpad109cpad = dataframe.query('cpad==1')110# save111cpad.to_csv(str(here("./data/for_analysis/cpad_not_tidy_cv.csv")), index=False)112# filter only cpad_fveg113cpad_fveg = dataframe.query('cpad_fveg==1')114# save115cpad_fveg.to_csv(str(here("./data/for_analysis/cpad_fveg_not_tidy_cv.csv")), index=False)116# filter only cdl_fveg117cdl_fveg = dataframe.query('cdl_fveg==1')118# save119cdl_fveg.to_csv(str(here("./data/for_analysis/cdl_fveg_not_tidy_cv.csv")), index=False)120# add time varying variables (PET and ET)121# # first read in the start dates that each layer corresponds to122# with open(str(here("./data/intermediate/start_dates.pkl")), 'rb') as f:123# start_date = pickle.load(f)124# # repeat the dataframe once for each start date125# repeated_start_date = np.repeat(start_date, dataframe.shape[0])126# dataframe = pd.concat([dataframe]*len(start_date))127# dataframe["start_date"] = repeated_start_date128# # add PET and ET129# add_columns(str(here("./data/intermediate/PET/PET_rolling_avg.tif")), 130# "PET")131# # save without ET132# dataframe.to_csv(str(here("./data/for_analysis/full_grid_no_ET.csv")), index=False)133# add_columns(str(here("./data/intermediate/ECOSTRESS/ETinst_rolling_average.tif")), 134# "ET")135# # save the full dataset136# dataframe.to_csv(str(here("./data/for_analysis/full_grid.csv")), index=False)137# # filter the dataset to only agriculture and save 138# ag = dataframe.loc[(dataframe.agriculture == 1)]139# ag.to_csv(here("./data/for_analysis/agriculture.csv"), index=False)140# # filter the dataset to only vegetation and save141# veg = dataframe.loc[(dataframe.counterfactual == 1)]...
add_columns.py
Source:add_columns.py
1import pandas as pd2from constants import *3import os4from collections import deque, defaultdict5import tqdm6def restore_columns(df: pd.DataFrame, need: str = 'id_driver') -> pd.DataFrame:7 """8 Calculates count/count_of_speeding/avg_speed in every last 7 days and overall9 :param df: Tracks10 :type df: pd.DataFrame11 :param need: Needed column12 :type need: str13 :return: Restored dataframe14 """15 history = defaultdict(lambda: deque())16 sums = defaultdict(int)17 sums_time = defaultdict(int)18 add_columns = defaultdict(list)19 all_sums = defaultdict(int)20 all_sums_time = defaultdict(int)21 all_cnts = defaultdict(int)22 for i in tqdm.tqdm(df.itertuples(), total=df.shape[0]):23 now_id = eval(f'i.{need}')24 dist = calc_distance(i.from_latitude, i.from_longitude, i.to_latitude, i.to_longitude) + i.arrived_distance25 history[now_id].append([i.dt_15_min, dist, i.duration + i.arrived_duration])26 sums[now_id] += dist27 all_sums[now_id] += dist28 sums_time[now_id] += i.duration + i.arrived_duration29 all_sums_time[now_id] += i.duration + i.arrived_duration30 all_cnts[now_id] += 131 while (i.dt_15_min - history[now_id][0][0]).total_seconds() > WEEK_SECONDS:32 sums[now_id] -= history[now_id][0][1]33 sums_time[now_id] -= history[now_id][0][2]34 history[now_id].popleft()35 add_columns['order_ids'].append(i.id_order)36 add_columns['last_total_dist'].append(sums[now_id])37 add_columns['last_total_time'].append(sums_time[now_id])38 add_columns['last_total_cnt'].append(len(history[now_id]))39 add_columns['total_dist'].append(all_sums[now_id])40 add_columns['total_time'].append(all_sums_time[now_id])41 add_columns['total_cnt'].append(all_cnts[now_id])42 add_columns = pd.DataFrame(add_columns)43 add_columns = df.merge(add_columns, right_on='order_ids', left_on='id_order')44 add_columns = add_columns.drop('order_ids', axis=1)45 return add_columns46def main():47 df1 = pd.read_csv(os.path.join(COMMON_PATH, 'df_ride_data.csv'))48 df2 = pd.read_csv(os.path.join(COMMON_PATH, 'df_ride_data_part2.csv'))49 df2.index += df1.index.max()50 df2 = pd.concat([df1, df2])51 df2.dt_15_min = pd.to_datetime(df2.dt_15_min)52 df2.sort_values(by='dt_15_min', inplace=True)53 restore_columns(df2, 'id_client').to_csv('merged_data_client.csv')54if __name__ == '__main__':...
Automation Testing Tutorials
Learn to execute automation testing from scratch with LambdaTest Learning Hub. Right from setting up the prerequisites to run your first automation test, to following best practices and diving deeper into advanced test scenarios. LambdaTest Learning Hubs compile a list of step-by-step guides to help you be proficient with different test automation frameworks i.e. Selenium, Cypress, TestNG etc.
LambdaTest Learning Hubs:
- JUnit Tutorial
- TestNG Tutorial
- Webdriver Tutorial
- WebDriverIO Tutorial
- Protractor Tutorial
- Selenium 4 Tutorial
- Jenkins Tutorial
- NUnit Tutorial
- Jest Tutorial
- Playwright Tutorial
- Cypress Tutorial
- PyTest Tutorial
YouTube
You could also refer to video tutorials over LambdaTest YouTube channel to get step by step demonstration from industry experts.
Run pandera automation tests on LambdaTest cloud grid
Perform automation testing on 3000+ real desktop and mobile devices online.
Try LambdaTest Now !!
Get 100 minutes of automation test minutes FREE!!
Was this article helpful?
