Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Weekly Challenges

Solve the challenge, share your solution and summit the ranks of our Community!

Also available in | Français | Português | Español | 日本語
IDEAS WANTED

Want to get involved? We're always looking for ideas and content for Weekly Challenges.

SUBMIT YOUR IDEA

Challenge #206: Hotel Reservations

NicoleJohnson
ACE Emeritus
ACE Emeritus

Alternative solution!

 

 

Spoiler
Continuing on my #SnakingMyWayThruChallenges journey, solved this one in Python (following the same general path that I took with my original Alteryx-only solution... found some cool new tricks with df.query this time, and starting to feel a bit more comfortable with with aggregation functions and manipulating the format of dataframes!). 

WeeklyChallenge206_Python.JPG

PYTHON SCRIPT:

#################################
from ayx import Package

from ayx import Alteryx
import pandas as pd

# Input Alteryx hotel bookings data
df = Alteryx.read("#1")


#################################
# Filter for only those records with reservation status = "Check-Out"
df = df.query('reservation_status == "Check-Out"')

# Format date fields for arrival date and checkout date, and calculate # of days between them
df['arrival_date'] = pd.to_datetime(df['arrival_date_year']+"-"+df['arrival_date_month']+"-"+df['arrival_date_day_of_month'], infer_datetime_format='True')
df['checkout_date'] = pd.to_datetime(df['reservation_status_date'])
df['DaysStayed'] = (df['checkout_date'] - df['arrival_date']).dt.days
df['Number of Reservations'] = 1

# Filter for only those reservations with a duration > 0 days, and format columns
df = df[['arrival_date_month','DaysStayed','Number of Reservations']] \
.query("DaysStayed != '0'") \
.rename(columns = {'arrival_date_month':'Month', 'DaysStayed':'Avg Length of Stay'})


#################################
# Create aggregations of booking data for Avg Length of Stay and Number of Reservations
hotel_bookings = df.groupby('Month', as_index = False).agg({'Avg Length of Stay': "mean", 'Number of Reservations':"sum"})


#################################
Alteryx.write(hotel_bookings,1)


#################################

Cheers!

NJ

 

loganmjung
7 - Meteor

I feel like my solution was mildly convoluted but was able to get the right solution! 

jatterberry
7 - Meteor
Spoiler
jatterberry_0-1589228505828.png

 

jatterberry_1-1589228515006.png

 

jatterberry_2-1589228523843.png

 

 

soha-elghany
8 - Asteroid

fun and quick challenge

datachick
5 - Atom

Done!

paul_houghton
12 - Quasar

Another challenge down.

 

Spoiler
206 Completed.jpg

lgrm_matrix
6 - Meteoroid

great learning, thanks

coracai
8 - Asteroid

My solution. 

DMContente
8 - Asteroid

Here's my solution

KaneG
Alteryx Alumni (Retired)

Nice quick one, could shorten it by removing the first filter and the 2nd will take care of the canceled anyway...

 

Spoiler
Challenge206.png