python script sample

 # Create a new DataFrame that includes only the title_name and genres columns, and remove the square brackets and spaces from the genres column

df = dim_titles_roku_python[['title_name', 'genres']].copy()  # make a copy to avoid modifying the original DataFrame

df.loc[:, 'genres'] = df['genres'].str.replace('[', '', regex=True).str.replace(']', '', regex=True).str.replace(' ', '', regex=True)

# df['genres'] = df['genres'].str.replace('[', '').str.replace(']', '').str.replace(' ', '')

# Use the `apply` function to split the genres column into a list of individual genres

df = df.apply(

    lambda x: pd.Series(x['genres'].split(',')),

    axis=1).stack().reset_index(level=1, drop=True)

# Use the `reset_index` function to create a new DataFrame where each genre is a separate row

df = df.reset_index(name='genre')

# Use the `groupby` and `size` functions to count the number of occurrences of each genre

df = df.groupby('genre').size().reset_index(name='frequency')

# Sort the resulting DataFrame by the frequency column in descending order

df = df.sort_values(by='frequency', ascending=False).head(10)

print(df)

Top & Bottom Categories each Year for Men and Woman based on Product Reviews

 df = dim_category_nike_python.copy()

#print(df)

product = dim_product_nike_python.copy()

product = product.merge(df,on='category_id',how='inner')

product = product[['order_date','gender','category_name','product_reviews']]

product['order_date'] = pd.to_datetime(product['order_date']).dt.year

product=product.groupby(['order_date','gender','category_name'])['product_reviews'].mean().reset_index()

product['top_rank']=product.groupby(['order_date','gender'])['product_reviews'].rank(ascending=False)

product['bottom_rank']=product.groupby(['order_date','gender'])['product_reviews'].rank(ascending=True)

new_df = product[(product['top_rank']==1)|(product['bottom_rank']==1)]

new_df = new_df.copy()

new_df.loc[:,'Category_type'] = new_df.apply(lambda row: 'Top' if row['top_rank']== 1 else 'Bottom',axis=1 )

grouped  =new_df.groupby(['order_date','gender','Category_type'])['category_name'].first().reset_index()

pivot_df = grouped.pivot(index='order_date',columns=['gender','Category_type'],values='category_name')

pivot_df.columns = [  f"{gender}_{abc}_Category"  for gender,abc in pivot_df.columns ]

pivot_df = pivot_df.reset_index()

column = {'men_Bottom_Category':'Men_Bottom_Category',

    'woman_Top_Category':'Woman_Top_Category',

    'woman_Bottom_Category':'Woman_Bottom_Category',

    'men_Top_Category':'Men_Top_Category',

    'order_date':'order_year'

    

}

pivot_df=pivot_df.rename(columns = column)

#print(product[product['order_date']==2020])

print(pivot_df)

python script to aggregate , merge, sort etc

 df= fact_orders_grubhub_python.merge(dim_delivery_person_grubhub_python, on='Delivery_person_ID')

# Use the `groupby` and `agg` functions to compute the average age and rating for each city_type

df = df.groupby('city_type').agg({

    'Delivery_person_Age': 'mean',

    'Delivery_person_Ratings': 'mean'

}).reset_index()

# Use the `round` function to round the age and rating columns to 2 decimal places

df['Delivery_person_Age'] = df['Delivery_person_Age'].round(2)

df['Delivery_person_Ratings'] = df['Delivery_person_Ratings'].round(2)

# Rename the columns to match the names in the SQL query

df.rename(columns={

    'Delivery_person_Age': 'avg_age',

    'Delivery_person_Ratings': 'avg_rating'

}, inplace=True)

print(df.head(5))

Qlik connection string switch by environment

 

 Environment var.

LET v.SYS.ServerName = UPPER(ComputerName());

 

//storing environment name into a varibale

LET v.SYS.EnvironmentName = if(index('$(v.SYS.ServerName)','DV'),'dev connection string name', 

                            if(index('$(v.SYS.ServerName)','UA'),'UAT connection string name'                   ));

LIB CONNECT TO  $(v.SYS.EnvironmentName);