Статья Автор: Лебедев Дмитрий Алексеевич

Решения заданий модуля Работа с таблицами Pandas от Сириуса

Используя датасет user.csv ответьте на вопросы

Сколько в таблице user представлено учеников, родившихся в сентябре?
Какое количество учеников последний раз посещали платформу в первые 14 дней сентября?
На сколько человек в данных больше девятиклассников, чем девятиклассниц?
Какой процент учеников, у которых указан класс, обучается в 9 классе?
Ответ округлите до целых.
Сколько в данных представлено учеников из регионов, в названиях которых в качестве подстроки содержится слово «город»?
При расчёте статистики приведите названия регионов к строчному написанию.

#Решение задания 12
import pandas as pd
ds = pd.read_csv('user.csv')
ds['birthday'] = pd.to_datetime(ds['birthday'], format = '%d.%m.%Y')
ans =[x for x in ds['birthday'].dt.month if x == 9]
print(len(ans))

#Решение задания 13
import pandas as pd
ds = pd.read_csv('user.csv')
ds['last_visited'] = pd.to_datetime(ds['last_visited'], format = '%d.%m.%Y %H:%M:%S')
ans =[x for x in ds['last_visited'].dt.floor('D') if x.month == 9 and x.day < 15 ]
print(len(ans))

#Решение задания 14
import pandas as pd
ds = pd.read_csv('user.csv')
ds['grade'] = ds['grade'].fillna(0).astype(int) 
ansX =len([x for x in zip(ds['grade'], ds['gender']) if x[0] == 9 and x[1] == 'М'])
ansY =len([x for x in zip(ds['grade'], ds['gender']) if x[0] == 9 and x[1] == 'Ж'])
print(ansX - ansY)

#Решение задания 15
import pandas as pd
ds = pd.read_csv('user.csv')
ans = [x for x in ds['grade'] if x> 0]
ans9 =sum([x == 9 for x in ans])
print(round(100*ans9/len(ans)))

#Решение задания 16
import pandas as pd
ds = pd.read_csv('user.csv')
ans = [x for x in ds['region'].str.lower() if 'город' in x]
print(len(ans))

#Решение задания 20
import pandas as pd
ds1 = pd.read_csv('course.csv')
ds2 = pd.read_csv('user_course_progress.csv')
ds = ds1.merge(ds2, left_on = 'id', right_on= 'course_id')
ans = [x for x in ds['subject'].str.lower() if x == 'химия']
print(len(ans))

#Решение задания 21
import pandas as pd
ds1 = pd.read_csv('course.csv')
ds2 = pd.read_csv('user_course_progress.csv')
ds = ds1.merge(ds2, left_on = 'id', right_on= 'course_id')
ans = [x for x in ds['max_score'].notna() if x]
print(len(ans))

#Решение задания 22
import pandas as pd
ds1 = pd.read_csv('course.csv')
ds2 = pd.read_csv('user_course_progress.csv')
ds = ds1.merge(ds2, left_on = 'id', right_on= 'course_id')
ds0 = ds[ds['max_score'].notna() == True]
ds0 = ds0.copy(deep = True)
ds0['relative_progress'] = ds0['progress']/ds0['max_score'] 
ans = ds0['relative_progress'].mean().round(2)
print(ans)

#Решение задания 23
import pandas as pd
ds1 = pd.read_csv('course.csv')
ds2 = pd.read_csv('user_course_progress.csv')
ds = ds1.merge(ds2, left_on = 'id', right_on= 'course_id')
ds0 = ds[ds['max_score'].notna() == True]
ds0 = ds0.copy(deep = True)
ds0['relative_progress'] = ds0['progress']/ds0['max_score'] 
ans =len( [x for x in ds0['relative_progress'] if x>=0.8])
print(ans)

#Решение задания 24
import pandas as pd
ds1 = pd.read_csv('course.csv')
ds2 = pd.read_csv('user_course_progress.csv')
ds = ds1.merge(ds2, left_on = 'id', right_on= 'course_id')
ds0 = ds[ds['max_score'].notna() == True]
ds0 = ds0.copy(deep = True)
ds0['relative_progress'] = ds0['progress']/ds0['max_score'] 
ds0 = ds0[ds0['relative_progress']>=0.8]
ds0 = ds0.groupby('subject', as_index = False).agg({'relative_progress': 'count'})
#ans =len( [x for x in zip(ds0[ds0['relative_progress'] if x>=0.8])
print(ds0)

#Решение задания 25
import pandas as pd
ds1 = pd.read_csv('course.csv')
ds2 = pd.read_csv('user_course_progress.csv')
ds = ds1.merge(ds2, left_on = 'id', right_on= 'course_id')
ds0 = ds[ds['max_score'].notna() == True]
ds0 = ds0.copy(deep = True)
ds0['relative_progress'] = ds0['progress']/ds0['max_score'] 
ds0 = ds0[ds0['relative_progress']>=0.8]
ds0 = ds0.groupby('subject', as_index = False).agg({'relative_progress': 'count'})
#ans =len( [x for x in zip(ds0[ds0['relative_progress'] if x>=0.8])
print(ds0)

#Решение задания 13 Модуля ВАД
import pandas as pd
import seaborn as sns
#import matplotlib.pyplot as plt
sns.set(rc={'figure.figsize':(11.7,8.27)})
age_bin, age_labels = [0,20,35,float('inf')], ['0-20','20-35','>35']
avg_bin, avg_labels = [0,10,40,float('inf')], ['0-10','10-40','>40']
df = pd.read_csv('user_statistics.csv')
df['age_bin'] = pd.cut(df['age'], age_bin, labels = age_labels,include_lowest =  True).astype(str) #.fillna('0')
df['avg_bin'] = pd.cut(df['avg_session_time'], avg_bin, labels = avg_labels,include_lowest =  True).astype(str)
#ds = df[['age_bin','avg_bin','user_id']].groupby(['age_bin','avg_bin']).agg({'user_id':'count'})
ds = df.groupby(['age_bin','avg_bin'],observed= False)['user_id'].count().fillna(0).unstack()
sns.heatmap(ds,annot=True, fmt='g') 
#plt.show()
print(ds)

#Решение задания 6 модуля "Метрики"
import pandas as pd
df = pd.read_csv('user_1.csv', encoding = 'utf-8')
ansM =  (df['gender'] == 'М').mean().round(2)
print(ansM)

#Решение задания 7 модуля "Метрики"
import pandas as pd
df = pd.read_csv('user_1.csv', encoding = 'utf-8')
#print(df)
df =  df[df['region'] == 'Краснодарский край']
ansD =  (df['last_visited'].between('2023-09-01', '2023-10-01')).mean().round(2)
print(ansD)

#Решение задания 8 модуля "Метрики"
import pandas as pd
dfs = pd.read_csv('user_session.csv', encoding = 'utf-8')
dfu = pd.read_csv('user_1.csv', encoding = 'utf-8')
ans0 =len(dfu)
ans1 =  len(dfs[dfs['date'].between('2023-05-01', '2023-06-01')].groupby('user_id').agg('min'))
print(ans1/ans0)

#Решение задания 9 модуля "Метрики"
import pandas as pd
df = pd.read_csv('user_session.csv', encoding = 'utf-8')
df['time_watched'] = df['time_watched'].astype(int)
df =  df[df['date'].between('2023-03-01', '2023-04-01')]
ds = df.groupby(['user_id'])['time_watched'].sum()
ans0 = len(ds)
ans1 = len([x for x in ds if x>=60])
print(ans1, ans0)

#Решение заданий 10-14 модуля "Метрики"
import pandas as pd
df = pd.read_csv('user_session.csv', encoding = 'utf-8')
df['day'] = pd.to_datetime(df['date']).dt.floor("D")
ds = df.groupby(['user_id', 'day']).agg({'time_watched': 'sum'}).reset_index()
ans10 =len(ds); print(f'{ans10=}')
ans11 = ds['time_watched'].sum(); print(f"{ans11 =}")
print(f"ans12 = {round(ans11/ans10, 2)}")
ans13 = df.query('user_id == 738').groupby(['day']).agg({'time_watched': 'sum'})['time_watched'].mean()
print(f"ans13 = {round(ans13, 2)}")
#ds14 = df.groupby(['user_id','day']).agg({'time_watched':'sum'}).reset_index()
#ds14a = ds14.groupby(['user_id']).agg({'time_watched':'mean'})
ans14 = df.groupby(['user_id','day']).agg({'time_watched':'sum'}).reset_index().groupby(['user_id']).agg({'time_watched':'mean'}).mean()
print(f'{ans14=}')

#решение заданий 9 - 10 модуля Исследовательский анализ данных
import pandas as pd
def parse_user_action_solution(line):
    arg = dict([x.replace("'",'').split(': ') for x in  line[1:-1].split(', ')])
    for x in arg : arg[x] = [arg[x]]
    ds = pd.DataFrame(arg)
    ds['id'] = ds['id'].astype(int)
    ds['action']=ds['action'].astype(str)
    ds['time'] = pd.to_datetime(ds['time'])
    ans = ds.to_dict(orient='index')
    return ans[0]['time']
df = pd.read_csv("user_session_sample.csv")
print(f"id = {df['id'].isna().sum()}")
print(f"user_id = {df['user_id'].isna().sum()}")
print(f"ip = {df['ip'].isna().sum()}")
print(f"action_time = {df['action_time'].isna().sum()}")
print(f"action = {df['action'].isna().sum()}")
isn = df['action_time'].isna()
df.loc[isn,'action_time'] = df.loc[isn,'action'].map(parse_user_action_solution)
df['action_time'] = pd.to_datetime(df['action_time'])       
ds = df[df['action_time'].dt.floor('D').between('2023-08-01','2023-08-31')]      
print(f'ans10 ={len(ds)}')
print(ds[['action','action_time']])

#решение заданий 11-13 модуля Исследовательский анализ данных
import pandas as pd
def parse_user_action_solution(line):
    arg = dict([x.replace("'",'').split(': ') for x in  line[1:-1].split(', ')])
    for x in arg : arg[x] = [arg[x]]
    ds = pd.DataFrame(arg)
    ds['id'] = ds['id'].astype(int)
    ds['action']=ds['action'].astype(str)
    ds['time'] = pd.to_datetime(ds['time'])
    ans = ds.to_dict(orient='index')
    return ans[0]['time']
def round_to_2 (x):
    return round(x,2)
def get_sessions(action_times, max_delta=40 * 60):
    sessions = []
    cur_session = []
    
    prev_time = None
    
    for time in action_times:
        if prev_time is None or (time - prev_time).total_seconds() > max_delta:
            sessions.append(cur_session)
            cur_session = [time]
        else:
            cur_session.append(time)
            
        prev_time = time
        
    sessions.append(cur_session)
    
    return sessions
def get_avg_session_time(action_times):
    sss = get_sessions(action_times)
    st, k = 0, 0
    for ss in sss :
      if len(ss) < 2 : continue
      k += 1
      st += (ss[-1]-ss[0]).total_seconds()
    ans = 0
    #st = round(st/60)
    if k > 0  : ans = st/(k*60)
    #ans = round(ans,2)
    return ans
df = pd.read_csv('user_session_sample.csv') 
isn = df['action_time'].isna()
df.loc[isn,'action_time'] = df.loc[isn,'action'].map(parse_user_action_solution)
df = df[['ip','action_time']]
df['action_time']= pd.to_datetime(df['action_time'])
df = df.sort_values('action_time')
ds = df.groupby(['ip'])['action_time'].agg(list).reset_index()
ds['mean_sessions'] = ds['action_time'].map(get_avg_session_time)
ds = ds.sort_values('mean_sessions')
ans12 = ds['mean_sessions'].mean()
#rez = ds['mean_sessions'].to_list()
#rez.sort()
#print(rez[4620:4640])
print(f'{ans12=}' )
ans13a = len(ds)
dss = ds[ds['mean_sessions'] > 0]
print(f'ans13a={1-len(dss)/ans13a} ans13b={dss['mean_sessions'].mean()}' )
print(ans13a, len(dss))      
#4624, 4635

#Решение задания 8-10 Модуля Проверка гипотез
import pandas as pd
import seaborn as sns
import matplotlib.pyplot as plt
import random
df = pd.read_csv('general_vals.csv')
ans8 = df['metric'].mean()
print(f'{ans8=}')
#sns.histplot(df['metric'])
#plt.show()
task_2_random_gen = random.Random(0)
ds = df['metric'].tolist()
m = int(0.03*len(ds))
task_2_random_gen.shuffle(ds)
vs = ds[:m]
rz = sum(vs)/m
ans9 = abs(rz - ans8)
print(f'{ans9=}')
ds10 = []
for _ in range(1000):
  random.shuffle(ds)
  vs = ds[:m]
  ds10.append(sum(vs)/m)
ans10 = abs(sum(ds10)/1000 - ans8)
print(f'{ans10=}')
sns.histplot(ds10)
plt.show()

Attached files
course.csv
general_vals.csv
user.csv
user_1.csv
user_course_progress.csv
user_session.csv
user_session_sample.csv
user_statistics.csv

Печать