This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
for schema_field in table.schema: # Для каждого поля в схеме | |
print (schema_field) # Печатаем поле схемы | |
print(table.num_rows) # Отображаем количество строк в таблице |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
table = client.get_table(table_ref) # Получаем данные о таблице | |
table.description = 'Моя таблица' # Задаем новый дескрипшн для таблицы | |
table = client.update_table(table, ['description']) # Обновляем таблицу, передав новый дескрипшн через API | |
print(table.description) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# Создаем тестовый dataframe | |
df = pd.DataFrame( | |
{ | |
'my_string': ['a', 'b', 'c'], | |
'my_int64': [1, 2, 3], | |
'my_float64': [4.0, 5.0, 6.0], | |
} | |
) | |
dataset_ref = client.dataset('my_dataset_2') # Определяем датасет | |
dataset = bigquery.Dataset(dataset_ref) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
from google.cloud import bigquery | |
client = bigquery.Client.from_service_account_json( | |
'/home/makarov/notebooks/my-bq-project-225910-6e534ba48078.json') | |
sql = ''' | |
SELECT DATE(creation_date) as date, DATE_TRUNC(DATE(creation_date), MONTH) as month, DATE_TRUNC(DATE(creation_date), YEAR) as year, COUNT(id) as questions | |
FROM | |
`bigquery-public-data.stackoverflow.posts_questions` | |
WHERE tags LIKE '%pandas%' |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
year_stats.to_gbq('my_dataset.my_table', project_id=project_id, if_exists='fail', private_key='my-bq-project-225910-6e534ba48078.json') |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
year_stats.columns = ['year','mean_questions','sum_questions','estimate'] |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
year_stats['estimate'] = year_stats[('questions','mean')]*12 | |
display(year_stats) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
year_stats = stats[(stats.month >= '2013-01-01') & (stats.month < '2018-09-01')].groupby(['year'],as_index=False).agg({'questions':['mean','sum']}) | |
display(year_stats) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
stats = df.groupby(['year','month'],as_index=False).agg({'questions':'sum'}) # Группируем данные по году и месяцу, используя в качестве агрегирующей функции сумму количества вопросов | |
display(stats.sort_values('questions',ascending=False).head(5)) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
df['month'] = df['date'].values.astype('datetime64[M]') # Создаем новый столбец с месяцем | |
df['year'] = df['date'].values.astype('datetime64[Y]') # Создаем новый столбец с годом | |
# Отображаем один день с максимальным количеством вопросов | |
display(df.sort_values('questions',ascending=False).head(1)) |