Beispiel #1
0
    def test_verbose(self):
        qs = TradeLog.objects.all()
        df = read_frame(qs, verbose=True)
        self.assertListEqual(
            list(qs.values_list('trader__name', flat=True)),
            df.trader.tolist()
        )
        df1 = read_frame(qs, verbose=False)
        self.assertListEqual(
            list(qs.values_list('trader__pk', flat=True)),
            df1.trader.tolist()
        )

        # Testing verbose with annotated column:
        if django.VERSION >= (1, 10):
            from django.db.models import F, FloatField
            from django.db.models.functions import Cast
            qs1 = TradeLog.objects.all().annotate(
                total_sum=Cast(F('price') * F('volume'), FloatField()),
            )
            df2 = read_frame(
                qs1, fieldnames=['trader', 'total_sum'])
            self.assertListEqual(
                list(qs1.values_list('total_sum', flat=True)),
                df2.total_sum.tolist()
            )
            self.assertListEqual(
                list(qs1.values_list('trader__name', flat=True)),
                df2.trader.tolist()
            )
Beispiel #2
0
 def test_basic(self):
     qs = MyModel.objects.all()
     df = read_frame(qs)
     n, c = df.shape
     self.assertEqual(n, qs.count())
     fields = MyModel._meta.get_all_field_names()
     self.assertEqual(c, len(fields))
     df1 = read_frame(qs, ['col1', 'col2'])
     self.assertEqual(df1.shape, (qs.count(), 2))
Beispiel #3
0
 def test_verbose(self):
     qs = TradeLog.objects.all()
     df = read_frame(qs, verbose=True)
     self.assertListEqual(
         list(qs.values_list('trader__name', flat=True)),
         df.trader.tolist()
     )
     df1 = read_frame(qs, verbose=False)
     self.assertListEqual(
         list(qs.values_list('trader__pk', flat=True)),
         df1.trader.tolist()
     )
Beispiel #4
0
 def test_basic(self):
     qs = MyModel.objects.all()
     df = read_frame(qs)
     n, c = df.shape
     self.assertEqual(n, qs.count())
     if django.VERSION < (1, 10):
         fields = MyModel._meta.get_all_field_names()
     else:
         fields = [f.name for f in MyModel._meta.get_fields()]
     self.assertEqual(c, len(fields))
     df1 = read_frame(qs, ['col1', 'col2'])
     self.assertEqual(df1.shape, (qs.count(), 2))
Beispiel #5
0
 def test_index(self):
     qs = MyModel.objects.all()
     df = read_frame(qs, ['col1', 'col2', 'col3', 'col4'],
                     index_col='index_col')
     self.assertEqual(df.shape, (qs.count(), 4))
     self.assertEqual(set(df.index.tolist()),
                      set(qs.values_list('index_col', flat=True)))
Beispiel #6
0
    def export_to_xls_users(self, request, queryset):
        df = pd.DataFrame()
        for el in queryset:
            users = el.get_filtered_user_queryset()
            users = users.annotate(userlogins=Count('usersession'))
            users = users.annotate(usertime=Sum('usersession__duration'))

            df = pd.concat([df, read_frame(users,
                                           index_col='user__id',
                                           verbose=True,
                                           fieldnames=(
                                               'user__id', 'user__username', 'user__first_name', 'user__last_name',
                                               'patronimyc',
                                               'user__email', 'user__last_login', 'userlogins', 'usertime',
                                               'date_of_birth', 'age', 'gender', 'city__name', 'area__name',
                                               'score', 'total_score',
                                               'total_exchange',
                                               'speciality__name',
                                               'work__name', 'curing_form', 'position__name', 'category',
                                               'bed_quantity',
                                               'patient_quantity',
                                               'main_phone', 'secondary_phone', 'work_phone',
                                               'district', 'house', 'flat', 'index',))])
        df = translate_column_names(df)
        return export_to_xls(df, translit(el.name) + '.xls', engine='openpyxl')
Beispiel #7
0
def get_openings_df(from_date, to_date, freq='H'):
    query = {}
    if from_date is not None:
        query['time__gte'] = from_date
    if to_date is not None:
        query['time__lt'] = to_date

    # Grab openings in a pandas dataframe
    df = read_frame(SpaceStatus.objects.filter(**query))
    start = pd.to_datetime(from_date).tz_localize('Europe/Brussels')
    end = pd.to_datetime(to_date).tz_localize('Europe/Brussels')

    # No records found, just return a fake df, always closed
    if len(df) == 0:
        df = pd.DataFrame([
            {'time': start, 'is_open': 0},
            {'time': end, 'is_open': 0}
        ])

    # Drop duplicate time index
    df = df.groupby(df.time).last()

    start = start.replace(minute=0, second=0, microsecond=0)
    end = end.replace(minute=0, second=0, microsecond=0)

    # Reindex on a monotonic time index
    index = pd.date_range(start=start, end=end, freq=freq)
    df = df.reindex(index, method='ffill')
    nans = np.isnan(df.is_open.astype(np.float64))
    df[nans] = not df[~nans].is_open[0]
    return df
Beispiel #8
0
 def test_basic(self):
     qs = MyModel.objects.all()
     df = read_frame(qs)
     n, c = df.shape
     self.assertEqual(n, qs.count())
     from itertools import chain
     if django.VERSION < (1, 10):
         fields = MyModel._meta.get_all_field_names()
     else:
         fields = list(set(chain.from_iterable((field.name, field.attname) if hasattr(field, 'attname') else (field.name,)
             for field in MyModel._meta.get_fields()
             if not (field.many_to_one and field.related_model is None)
         )))
     self.assertEqual(c, len(fields))
     df1 = read_frame(qs, ['col1', 'col2'])
     self.assertEqual(df1.shape, (qs.count(), 2))
Beispiel #9
0
 def test_verbose_duplicates_fieldnames(self):
     qs = TradeLog.objects.all()
     df = read_frame(qs, fieldnames=['trader', 'trader', 'price'])
     self.assertListEqual(
         list(qs.values_list('price', flat=True)),
         df.price.tolist()
     )
Beispiel #10
0
 def export_to_xls_user_poll(self, request, queryset):
     uf = queryset[0]
     users = uf.get_filtered_user_queryset()
     if not uf.polls:
         uf.polls = Poll.objects.all()
     polls = UsersPoll.objects.filter(user__pk__in=users.values_list('pk'),
                                      poll__pk__in=uf.polls.values_list('pk'), )
     if uf.date_from:
         polls = polls.filter(date_passed__lte=uf.date_from)
     if uf.date_to:
         polls = polls.filter(date_passed__gte=uf.date_to)
     polls = Poll.objects.filter(pk__in=polls.values_list('poll'))
     answers = UserAnswer.objects.filter(user__pk__in=users.values_list('pk'),
                                         question__poll__pk__in=polls.values_list('pk'))
     answers = answers.order_by(uf.order, set(ORDERS - set(uf.order)).pop(), 'question__id', 'answer__id')
     df = read_frame(answers,
                     verbose=True,
                     fieldnames=(
                         'user__user__id', 'user__user__username',
                         'question__poll__id', 'question__poll__name',
                         'question__id', 'question__question_text',
                         'answer__id', 'answer__answer_text',
                         'other_answer',
                     ))
     df = translate_column_names(df)
     return export_to_xls(df, translit(uf.group.name) + '.xls', engine='openpyxl')
Beispiel #11
0
 def poll_to_xls(request, queryset):
     qs = None
     for poll in queryset:
         if qs:
             qs |= Answer.objects.filter(question__poll=poll.id)
         else:
             qs = Answer.objects.filter(question__poll=poll.id)
     qs = qs.annotate(useranswers=Count('useranswer'))
     qs = qs.order_by('-question__poll__id', 'question__id', 'id')
     df = read_frame(qs,
                     verbose=True,
                     fieldnames=('question__poll__id',
                                 'question__poll__name',
                                 'question__id',
                                 'question__question_text',
                                 'id',
                                 'answer_text',
                                 'useranswers'
                                 ))
     for i, row in df.iterrows():
         question = df.loc[i, 'question__id']
         answers = df.loc[i, 'useranswers']
         total_counts = sum(df[df['question__id'] == question]['useranswers'])
         if total_counts:
             percent = answers / total_counts * 100
         else:
             percent = 0
         df.ix[i, 'percent'] = str(percent) + '%'
     i = dt.datetime.now()
     path = 'oprosy' + "_%s_%s_%s" % (i.day, i.month, i.year) + '.xls'
     df = translate_column_names(df)
     return export_to_xls(df, path)
Beispiel #12
0
    def to_dataframe(self, fieldnames=(), verbose=True, index=None,
                     coerce_float=False):
        """
        Returns a DataFrame from the queryset, overrides the base method to
        enalbe lazy calculation


        :param fieldnames:  The model field names(columns) to utilise in
                     creating the DataFrame. You can span a relationships in
                     the usual Django ORM way by using the foreign key field
                     name separated by double underscores and refer to a field
                     in a related model.


        :param index:  specify the field to use  for the index. If the index
                field is not in fieldnames it will be appended. This
                is mandatory for timeseries.

        :param verbose: If  this is ``True`` then populate the DataFrame with
                the human readable versions for foreign key fields else
                use the actual values set in the model
        """
        if self.count_invalid() < self.MAX_NUM_CALCULATION:
            outdated_values = self.filter(_is_valid=False)
            outdated_values.recalculation()  #pylint: disable=E1101
        read_calc_value = '_calc_value' in fieldnames
        if 'value' in fieldnames:
            fieldnames[fieldnames.index('value')] = '_calc_value'
        frame = read_frame(self, fieldnames=fieldnames, verbose=verbose,
                           index_col=index, coerce_float=coerce_float)
        if '_calc_value' in frame.columns and not read_calc_value:
            new_labels = list(frame.columns)
            new_labels[new_labels.index('_calc_value')] = 'value'
            frame.columns = new_labels
        return frame
Beispiel #13
0
def similar_products2(deep_f):
	qs = Product.objects.all()
	df=read_frame(qs)
	df['idx'] = range(1, len(df) + 1)
	feature_list=[]
	asin_list=[]

	for prod in qs:
		feature_list.append(prod.get_features())
		asin_list.append(prod.asin)
	
		
	nparray = np.asarray(feature_list)
	#print nparray
	tree = BallTree(nparray)              
	dist, ind = tree.query(deep_f, k=5)
	print ind
	index = ind[0]
	recom = index[0:]
	recommended_asins =[];
	
	for i in recom:
		recommended_asins.append(asin_list[i])
	recommended_prods = Product.objects.filter(asin__in = recommended_asins)
	return recommended_prods

#    image_train = graphlab.SFrame(data=df)
#    cur_prod = image_train[18:19]
#    print cur_prod
#    print image_train
#    knn_model = graphlab.nearest_neighbors.create(image_train, features = ['features'],label = 'asin',distance = 'levenshtein',method = 'ball_tree')
#    knn_model.save('my_knn')
#    #knn_model= graphlab.load_model('my_knn')
#    #print knn_model.query(cur_prod)
#    #knn_model = graphlab.nearest_neighbors.create(image_train, features = ['features'],label = 'keywords')
Beispiel #14
0
def similar_products(product):
	qs = Product.objects.all()
	df=read_frame(qs)
	df['idx'] = range(1, len(df) + 1)
	feature_list=[]
	asin_list=[]
	product_index = 0
	inn=0
	for prod in qs:
		feature_list.append(prod.get_features())
		asin_list.append(prod.asin)
		if prod.asin == product.asin:
			product_index = inn
		inn+=1
		
	nparray = np.asarray(feature_list)
	#print nparray
	tree = BallTree(nparray)              
	dist, ind = tree.query(nparray[product_index], k=5)
	print ind
	index = ind[0]
	recom = index[1:]
	recommended_asins =[];
	
	for i in recom:
		recommended_asins.append(asin_list[i])
	recommended_prods = Product.objects.filter(asin__in = recommended_asins)
	return recommended_prods
Beispiel #15
0
 def test_duplicate_annotation(self):
     qs = MyModel.objects.all()
     qs = qs.values('index_col')
     qs = qs.annotate(col1=Sum('col1'))
     qs = qs.values()
     df = read_frame(qs)
     self.assertEqual(list(df.columns),
                      ['id', 'index_col', 'col1', 'col2', 'col3', 'col4'])
Beispiel #16
0
def dataset_detail(request, dataset_id):
    dataset = get_object_or_404(DataSet, pk=dataset_id)
    DataSetModel = getattr(shop.models, dataset.code)
    qs = DataSetModel.objects.all()
    df = read_frame(qs)
    df.to_pickle("dataset/{}".format(dataset.code))
    datahtml = df.to_html()
    return render(request, 'shop/dataset/detail.html', {'dataset': dataset, 'datavalues': datahtml})
Beispiel #17
0
    def test_related_cols(self):
        qs = TradeLog.objects.all()
        cols = ["log_datetime", "symbol", "symbol__isin", "trader__name", "price", "volume", "note__note"]
        df = read_frame(qs, cols, verbose=False)

        self.assertEqual(df.shape, (qs.count(), len(cols)))
        self.assertListEqual(list(qs.values_list("symbol__isin", flat=True)), df.symbol__isin.tolist())
        self.assertListEqual(list(qs.values_list("trader__name", flat=True)), df.trader__name.tolist())
Beispiel #18
0
 def test_values(self):
     qs = MyModel.objects.all()
     qs = qs.extra(select={"ecol1": "col1+1"})
     qs = qs.values("index_col", "ecol1", "col1")
     qs = qs.annotate(scol1 = Sum("col1"))
     df = read_frame(qs)
     self.assertEqual(list(df.columns), ['index_col', 'col1', 'scol1', 'ecol1'])
     self.assertEqual(list(df["col1"]), list(df["scol1"]))
Beispiel #19
0
    def test_choices(self):

        MyModelChoice.objects.create(col1=1, col2=9999.99)
        MyModelChoice.objects.create(col1=2, col2=0.99)
        MyModelChoice.objects.create(col1=3, col2=45.6)
        MyModelChoice.objects.create(col1=2, col2=2.6)

        qs = MyModelChoice.objects.all()
        df = read_frame(qs, verbose=True)
        self.assertEqual(df.col1[0], 'First')
        self.assertEqual(df.col1[1], 'Second')
        self.assertEqual(df.col1[2], 'Third')
        self.assertEqual(df.col1[3], 'Second')
        df = read_frame(qs, verbose=False)
        self.assertEqual(df.col1[0], 1)
        self.assertEqual(df.col1[1], 2)
        self.assertEqual(df.col1[2], 3)
        self.assertEqual(df.col1[3], 2)
Beispiel #20
0
def get_openings_df(freq='H', **filter_args):
    # Grab openings in a pandas dataframe
    df = read_frame(SpaceStatus.objects.filter(**filter_args))

    # Drop duplicate time index
    df = df[df.time.diff().dt.total_seconds() > 0]
    df.index = df.time

    # Reindex on a monotonic hourly time index
    index = pd.date_range(start=df.time.min(), end=df.time.max(), freq=freq)
    return df.reindex(index, method='pad')
Beispiel #21
0
    def test_many_to_many(self):
        qs = Portfolio.objects.all()
        cols = ['name', 'securities__symbol', 'securities__tradelog__log_datetime']
        df = read_frame(qs, cols, verbose=True)

        denormalized = Portfolio.objects.all().values_list(*cols)
        self.assertEqual(df.shape, (len(denormalized), len(cols)))
        for idx, row in enumerate(denormalized):
            self.assertListEqual(
                df.iloc[idx].tolist(),
                list(row)
            )
def get_user_data(username):
    """Get user's personal data."""
    dfs = {}

    df = read_frame(Video.objects.all(),
                    fieldnames=['id', 'video_id'] + VIDEO_FIELDS)
    dfs['all_video_ratings'] = df

    df = read_frame(
        VideoRating.objects.filter(user__user__username=username).all())
    dfs['my_video_scores'] = df

    df = read_frame(
        ExpertRating.objects.filter(user__user__username=username).all())
    dfs['my_expert_ratings'] = df

    df = read_frame(
        VideoReports.objects.filter(user__user__username=username).all())
    df.columns = [
        VIDEO_REPORT_FIELDS[x] if x in VIDEO_REPORT_FIELDS else x
        for x in df.columns
    ]
    dfs['my_video_reports'] = df

    df = read_frame(
        VideoComment.objects.filter(user__user__username=username).all())
    dfs['my_video_comments'] = df

    df = read_frame(
        VideoCommentMarker.objects.filter(user__user__username=username).all(),
        fieldnames=['id', 'user', 'comment__id', 'user', 'which'])
    dfs['my_video_comment_markers'] = df

    return dfs
Beispiel #23
0
def read_ligand_batches(ligand_batch):
    object_capitalized = ligand_batch.capitalize()
    model = get_model_by_name(object_capitalized)
    if ligand_batch in ["peptideBatch","antibodyBatch", "complexBatch"]:
        df = read_frame(model.objects.all(), ["sid", "labeling", "concentration","buffer", "ph","purity", "produced_by__username",
                                          "production_date", "comment",
                                          "ligand__sid"])
        df.replace([np.NaN], [None], inplace=True)
        df['purity'] = list(map(str, df['purity'].values))
        df['concentration'] = list(map(str, df['concentration'].values))

    elif ligand_batch == "virusBatch":
        df = read_frame(model.objects.all(),
                        ["sid", "labeling", "concentration","buffer", "ph","purity", "produced_by__username",
                                          "production_date", "comment",
                                          "ligand__sid", "passage_history","active"])
        df.replace([np.NaN], [None], inplace=True)
        df['active'] = list(map(str, df['active'].values))
        df['passage_history'] = list(map(str, df['passage_history'].values))
        df['purity'] = list(map(str, df['purity'].values))
        df['concentration'] = list(map(str, df['concentration'].values) )

    elif ligand_batch == "bufferBatch":
        df = read_frame(model.objects.all(),
                        ["sid", "buffer", "ph", "produced_by__username",
                         "production_date", "comment"])
        df.replace([np.NaN], [None], inplace=True)

    df['ph']=list(map(str, df['ph'].values))
    df['production_date']=list(map(str, df['production_date'].values))

    df.replace(["None"],[None], inplace=True)




    df = df.rename(columns={"ligand__sid":"ligand","produced_by__username":"******"})


    return df
Beispiel #24
0
def anticodon_counts(request, clade_txid, isotype):
    try:
        tax_qs = models.Taxonomy.objects.filter(taxid=clade_txid).values(
            'name', 'rank', 'taxid', 'domain')
        if len(tax_qs) > 1: tax_qs = tax_qs.exclude(rank='assembly')
        tax = tax_qs.get()
        if tax['rank'] == 'class': tax['rank'] = 'taxclass'
        trna_qs = models.tRNA.objects.filter(Q(**{tax['rank']: tax['taxid']}))
        if isotype != 'All':
            trna_qs = trna_qs.filter(isotype=isotype)
        trna_qs = trna_qs.values('anticodon',
                                 'isotype').annotate(clade=Count('anticodon'))
        clade_counts = read_frame(trna_qs)
        clade_counts = clade_counts.set_index(['isotype', 'anticodon'])

        domain_name = models.Taxonomy.objects.filter(
            taxid=tax['domain']).get().name
        trna_qs = models.tRNA.objects.filter(domain=tax['domain'])
        if isotype != 'All':
            trna_qs = trna_qs.filter(isotype=isotype)
        trna_qs = trna_qs.values('anticodon',
                                 'isotype').annotate(domain=Count('anticodon'))
        domain_counts = read_frame(trna_qs)
        domain_counts = domain_counts.set_index(['isotype', 'anticodon'])

        counts = clade_counts.join(domain_counts).sort_index().reset_index()
        counts.columns = ['Isotype', 'Anticodon', tax['name'], domain_name]
        counts = counts.set_index(['Isotype', 'Anticodon'])
        counts.index.names = [None, None]

        return HttpResponse(
            counts.to_html(classes='table',
                           border=0,
                           justify='left',
                           bold_rows=False,
                           na_rep='0',
                           sparsify=True))

    except:
        return HttpResponse('Unknown server error')
Beispiel #25
0
def get_database_as_pd():
    """Get database tables as pandas DataFrames."""
    result_df = {}

    df = read_frame(Video.objects.all(),
                    fieldnames=[
                        'id', 'video_id', 'name', 'duration', 'language',
                        'publication_date', 'views', 'uploader'
                    ] + VIDEO_FIELDS)
    result_df['videos'] = df

    df = read_frame(VideoRating.objects.all())
    result_df['video_ratings'] = df

    df = read_frame(ExpertRating.objects.all())
    result_df['expert_rating'] = df

    df = read_frame(DjangoUser.objects.all(),
                    fieldnames=[
                        'id', 'last_login', 'is_superuser', 'username',
                        'first_name', 'last_name', 'is_staff', 'is_active',
                        'date_joined', 'email', 'userpreferences__id'
                    ] + [f"userpreferences__{x}" for x in VIDEO_FIELDS])
    result_df['users'] = df

    df = read_frame(VideoComment.objects.all())
    result_df['video_comment'] = df

    df = read_frame(VideoCommentMarker.objects.all(),
                    fieldnames=['id', 'user', 'comment__id', 'user', 'which'])
    result_df['video_comment_marker'] = df

    return result_df
def scoreJson(request):
	print (request.body)
	qs = rankings.objects.all()
	df = read_frame(qs)


	df['weighted_points'] =  df['cur_year_avg_weighted'] + df['two_year_ago_weighted'] + df['three_year_ago_weighted']

	data = json.loads(request.body)
	x = data['Home'].capitalize()
	y = data['Away'].capitalize()

	home = df.loc[df['country_full'] == x]
	away = df.loc[df['country_full'] == y]

	frames = [home, away]
	result = pd.concat(frames)

	if x in df['country_full'].values and y in df['country_full'].values: 
		X_test3 = ['average_rank', 'rank_difference', 'point_difference', 'is_stake']
		row = pd.DataFrame(np.array([[np.nan, np.nan, np.nan, True]]), columns=X_test3)

		home_rank = result['rank'].iloc[0]
		home_points = result['weighted_points'].iloc[0]
		opp_rank = result['rank'].iloc[1]

		opp_points = result['weighted_points'].iloc[1]
		row['average_rank'] = (home_rank + opp_rank) / 2
		row['rank_difference'] = home_rank - opp_rank
		row['point_difference'] = home_points - opp_points

		home_win = model.predict_proba(row)
		print(home_win)
		#dataF = pd.DataFrame({'x':data})
		#print(dataF)
		#print(df)

		if home_win[0][0]>=0.45 and home_win[0][0]<=0.55:
			r="draw"
			print(r)
			return JsonResponse({'result':r})
		if home_win[0][0]>0.55:
			r = result['country_full'].iloc[1], "win with a probability of",home_win[0][0]
			print(r)
			return JsonResponse({'result':r})
		if home_win[0][1]>0.55:
			r = result['country_full'].iloc[0], "win with a probability of",home_win[0][1]
			print(r)
			return JsonResponse({'result':r})

	else:
		return JsonResponse({'result':'Error team not found!'})
Beispiel #27
0
def json(request):
    qs = Stock.objects.all()
    df = read_frame(qs)
    df2 = df.groupby(['symbol'])
    # d1 = df2['symbol']
    d = ["hhh", "hhhh", "kkkk"]
    d1 = df2.to_dict()

    context = {
        'latest_question_list': "error",
    }
    template = loader.get_template('stock/json.html')
    return JsonResponse({'foo': d1})
Beispiel #28
0
    def get_ratings_dataframe(self, run):
        rating_qs = self.filter(run=run).values("id", "parent_network__pk", "log_gamma", "log_gamma_uncertainty", "log_gamma_game_count").all()
        rating = read_frame(rating_qs)
        rating = rating.set_index("id")
        rating = rating.sort_index()

        rating["parent_network__pk"] = rating["parent_network__pk"].fillna(-1)
        rating["log_gamma"] = rating["log_gamma"].fillna(0)
        rating["log_gamma_uncertainty"] = rating["log_gamma_uncertainty"].fillna(0)
        rating["log_gamma_uncertainty"] = rating["log_gamma_uncertainty"].replace([np.inf, -np.inf], 0)
        rating["log_gamma_game_count"] = rating["log_gamma_game_count"].fillna(0)

        return rating
Beispiel #29
0
    def MakeDf(self):
        """[summary] make pd.DataFrame(df) from candles, df.rows=['time', 'open', 'high', 'low', 'close', 'volume'], df.index = df.time, where df.time.dtype = datetime64[ns].len(df) = self.num_data

        Returns:
            [type]: [description] pd.DataFrame
        """
        df = read_frame(
            self.datas.objects.filter(
                product_code=self.product_code).all().order_by("time"))
        df = df[["time", 'open', 'high', 'low', 'close', 'volume']]
        df = df.set_index("time")
        df = df[-self.num_data:]
        return df
Beispiel #30
0
 def publication_tags(self):
     if self._publication_tags is None:
         self._publication_tags = read_frame(
             PublicationTags.objects.filter(
                 publication__in=self.publication_queryset),
             fieldnames=['publication__id', 'tag__id', 'tag__name'],
             index_col='publication__id')
         self._publication_tags.rename(columns={
             'tag__name': 'name',
             'tag__id': 'related__id'
         },
                                       inplace=True)
     return self._publication_tags
Beispiel #31
0
def procesa_tabla(tabla):
       
   df= read_frame(tabla)   
   for item in tabla: 
      print(item.temperatura) 
   for item in tabla: 
      print(item.color)   
   for item in tabla: 
      print(item.inflamacion) 

   print(df)  
   
   return "Mi proceso"  
Beispiel #32
0
    def test_related_cols(self):
        qs = TradeLog.objects.all()
        cols = [
            'log_datetime', 'symbol', 'symbol__isin', 'trader__name', 'price',
            'volume', 'note__note'
        ]
        df = read_frame(qs, cols, verbose=False)

        self.assertEqual(df.shape, (qs.count(), len(cols)))
        self.assertListEqual(list(qs.values_list('symbol__isin', flat=True)),
                             df.symbol__isin.tolist())
        self.assertListEqual(list(qs.values_list('trader__name', flat=True)),
                             df.trader__name.tolist())
Beispiel #33
0
def get_user_data(username):
    """Get user's personal data."""
    dfs = {}

    df = read_frame(
        VideoRating.objects.filter(user__user__username=username).all())
    dfs['my_video_scores'] = df

    df = read_frame(
        ExpertRating.objects.filter(user__user__username=username).all())
    dfs['my_expert_ratings'] = df

    df = read_frame(
        VideoComment.objects.filter(user__user__username=username).all())
    dfs['my_video_comments'] = df

    df = read_frame(
        VideoCommentMarker.objects.filter(user__user__username=username).all(),
        fieldnames=['id', 'user', 'comment__id', 'user', 'which'])
    dfs['my_video_comment_markers'] = df

    return dfs
Beispiel #34
0
def index(request):
    boyds = BlackOystercatcher.objects.all()
    df = read_frame(boyds)
    ids = df['bird_id']
    data = df.head(0)
    template = loader.get_template('data_playground/index.html')
    context = {
        'props': json.dumps(ids.tolist()),
        'df': df,
        'data': data,
        'ids': ids
    }
    return HttpResponse(template.render(context, request))
Beispiel #35
0
def bird_lookup(bird_type):
    """Combine datasets for given bird and year"""
    # Retrieve datasets
    routes = read_frame(
        Routes.objects.values('countrynum', 'statenum', 'route', 'routename',
                              'active', 'latitude', 'longitude'))
    weather = read_frame(
        Weather.objects.values('countrynum', 'statenum', 'route', 'year',
                               'routedataid', 'rpid'))
    species = read_frame(Species.objects.values('aou', 'english_common_name'))
    total_state_df = read_frame(
        UsStates.objects.filter(aou=bird_type).values('countrynum', 'statenum',
                                                      'route', 'year',
                                                      'routedataid', 'rpid',
                                                      'aou', 'stoptotal',
                                                      'speciestotal'))

    # Merge into one dataframe
    all_df = pd.merge(weather,
                      routes,
                      how='left',
                      left_on=['countrynum', 'statenum', 'route'],
                      right_on=['countrynum', 'statenum', 'route'])
    all_df = pd.merge(all_df,
                      total_state_df,
                      how='right',
                      left_on=[
                          'countrynum', 'statenum', 'route', 'routedataid',
                          'rpid', 'year'
                      ],
                      right_on=[
                          'countrynum', 'statenum', 'route', 'routedataid',
                          'rpid', 'year'
                      ])
    all_df = pd.merge(all_df, species, how='left', on='aou')
    # Get sizes of dots per number of sightings
    all_df['species_size'] = all_df['speciestotal'].apply(species_sizer)
    all_df['routename'] = [capwords(state) for state in all_df['routename']]
    return (all_df)
Beispiel #36
0
def update_rules(request):
    if request.user.is_authenticated:
        my_tags = UserRule.objects.filter(user=request.user).all()
        df = read_frame(my_tags)
        df = df[[
            'begins_with', 'ends_with', 'description', 'category', 'tag', 'id'
        ]]
        df = df.values.tolist()
        template = loader.get_template('core_app/rule_details.html')
        context = {'results': df}
        return HttpResponse(template.render(context, request))
    else:
        return redirect('home')
Beispiel #37
0
def sampleScenario(number_of_points=100,
                   buffer_size_km=10,
                   feature_names=[
                       "statenm", "statecd", "stateab", "subp", "n_inventor",
                       "elev", "invyr", "tree", "spcd", "family", "ht_m",
                       "ba_m2", "biomass_kg"
                   ]):
    points = sampleScenario1pre(number_of_points=number_of_points)
    selections = selectFromBuffer(points, buffer_size_km)
    dfsels = map(lambda q: read_frame(q, fieldnames=feature_names), selections)
    ## compent if prefer custom feature selection
    dfsels = getSummaryStatistics(dfsels)
    return dfsels
Beispiel #38
0
def home(request):
    try:
        a = Start.objects.all()
        if request.method == "GET":
            b = read_frame(a)
            return render(request, 'myapp/home.html', {'b': b})
        c = request.POST['resname']
        m = request.POST['addSuggestion']
        if (m == "0"):
            q = Start.objects.values("coupon").filter(name=c)
            qq = read_frame(q)
            print(q)
            # qqq = qq.coupon
            # return HttpResponse(q)
            return render(request, 'myapp/coupon.html', {'cou': qq})
        else:
            b = read_frame(a)
            suggestion = AddSuggestion.objects.create(suggestion=c)
            return render(request, 'myapp/home.html', {'b': b})

    except MultiValueDictKeyError:
        return render(request, 'myapp/home.html')
    def handle(self, *args, **kwargs):
        # Create a version
        version_obj, created = Version.objects.get_or_create(
            reference='200101_demo', version_date=datetime(2020, 1, 1))
        if kwargs['clean'] and not created:
            # Delete old data
            self.stdout.write('Deleting existing forecast...')
            forecast_objs = Forecast.objects.filter(version=version_obj)
            forecast_objs.delete()
            self.stdout.write('Existing forecast deleted')

        # Get past orders
        order_qs = OrderDetail.objects.values('warehouse__id', 'product__id',
                                              'category__id', 'circuit__id',
                                              'customer__id',
                                              'ordered_quantity',
                                              'order__ordered_at')
        order_df = read_frame(order_qs)
        order_df['ordered_quantity'] = order_df['ordered_quantity'].astype(
            'int32')
        self.stdout.write('Version object created')

        # Create forecast dataframe
        forecast_df = order_df.rename(
            columns={
                'ordered_quantity': 'forecasted_quantity',
                'order__ordered_at': 'forecast_date',
            })
        # Generate different values based on defined rate
        forecast_df['forecasted_quantity'] = pd.to_numeric(
            forecast_df['forecasted_quantity'] * 0.7)
        forecast_df['edited_forecasted_quantity'] = pd.to_numeric(
            forecast_df['forecasted_quantity'] * 1.3)

        self.stdout.write('Creating forecasts...')
        forecast_objs = [
            Forecast(
                warehouse_id=row['warehouse__id'],
                product_id=row['product__id'],
                category_id=row['category__id'],
                circuit_id=row['circuit__id'],
                customer_id=row['customer__id'],
                version_id=version_obj.id,
                forecast_date=row['forecast_date'],
                forecasted_quantity=row['forecasted_quantity'],
                edited_forecasted_quantity=row['edited_forecasted_quantity'],
            ) for i, row in forecast_df.iterrows()
        ]
        Forecast.objects.bulk_create(forecast_objs)

        self.stdout.write(self.style.SUCCESS('Forecasts created successfully'))
def prediction(request):
    template = 'stock_api/prediction.html'
    # filtering company
    kun_company = request.GET.get('company_abbr')
    qs = StockData.objects.filter(
        companyAbbr=kun_company)  # RUSSELL 2000 INDEX(company name)
    df = read_frame(qs)
    data = df['open']
    stock_data = np.array(data.values.reshape((len(data), 1)))
    stock_data.shape  # (1258, 1)
    # Split the data into training/testing sets
    stock_X_train = stock_data[:350]
    stock_X_test = stock_data[700:979, ]

    # Split the targets into training/testing sets
    stock_y_train = stock_data[350:700]
    stock_y_test = stock_data[979:]

    regr = linear_model.LinearRegression()
    regr.fit(stock_X_train, stock_y_train)
    stock_y_pred = regr.predict(stock_X_test)
    # stock_y_pred stock_X_test

    # # Statistical parameter
    # #coefficients
    # coefficients = regr.coef_
    # # mean squared error
    # MSE = mean_squared_error(mark_y_test, mark_y_pred)
    # # Explained variance score: 1 is perfect prediction
    # RSQ = r2_score(mark_y_test, mark_y_pred)

    # mark_y_pred_pd = pd.DataFrame(mark_y_pred.reshape(mark_y_pred.shape[1], -1), index=exam, columns=subject)
    # mark_X_test_pd = pd.DataFrame(mark_y_test.reshape(mark_y_test.shape[1], -1), index=exam, columns=subject)
    stock_y_pred_html = stock_y_pred  #.to_html()
    stock_X_test_html = stock_X_test  #.to_html()
    # plt.plot(stock_y_pred)
    # plt.plot(stock_X_test)

    print(stock_data[-1])
    last_data = regr.predict(stock_data[-1])
    print(last_data)

    context = {
        # 'MSE':MSE,
        # 'RSQ':RSQ,
        # 'coefficients':coefficients,
        'stock_y_pred_html': stock_y_pred_html,
        'stock_X_test_html': stock_X_test_html,
    }
    # return render(request, template, context)
    return JsonResponse({'tomorrow_predicted_value': last_data[0][0]})
def map2():
    adr = mergedTables.objects.all()
    qs_adr = adr
    df_of_query_result_adr = read_frame(qs_adr)
    newTable_adr = df_of_query_result_adr
    m = folium.Map(location=[48.8566, 2.3522],
                   tiles="Stamen Toner",
                   zoom_start=2)
    #cergy=['49.034955','2.069925']
    #pau=['43.319568','-0.360571']
    newTable_ent_adr = newTable_adr[['ENT_LAT', 'ENT_LON']]
    newTable_stu_adr = newTable_adr[['ADR_LAT', 'ADR_LON']]
    newTable_site_adr = newTable_adr[['SITE_LAT', 'SITE_LON']]
    newTable_ent_adr = newTable_ent_adr.dropna(axis=0,
                                               subset=['ENT_LAT', 'ENT_LON'])
    newTable_stu_adr = newTable_stu_adr.dropna(axis=0,
                                               subset=['ADR_LAT', 'ADR_LON'])
    newTable_site_adr = newTable_site_adr.dropna(
        axis=0, subset=['SITE_LAT', 'SITE_LON'])
    #newTable_ent_adr= [[row['ENT_LAT'],row['ENT_LON']] for index,row in newTable_ent_adr.iterrows() ]
    #newTable_stu_adr= [[row['ADR_LAT'],row['ADR_LON']] for index,row in newTable_stu_adr.iterrows() ]
    #HeatMap(cergy).add_to(m)
    HeatMap(newTable_stu_adr).add_to(m)
    mc = MarkerCluster()
    folium.CircleMarker(
        [49.034955, 2.069925],
        radius=20,
        popup='Cergy Campus',
        color='red',
    ).add_to(m)
    folium.CircleMarker(
        [43.319568, -0.360571],
        radius=20,
        popup='Pau Campus',
        color='red',
    ).add_to(m)

    for i in range(0, len(newTable_stu_adr)):
        #mc.add_child(folium.Marker([newTable_ent_adr.iloc[i]['ENT_LAT'],newTable_ent_adr.iloc[i]['ENT_LON']],icon=folium.Icon(icon='cloud'))).add_to(m)
        mc.add_child(
            folium.Marker([
                newTable_ent_adr.iloc[i]['ENT_LAT'],
                newTable_ent_adr.iloc[i]['ENT_LON']
            ])).add_to(m)
        #mc.add_child(folium.Marker([newTable_stu_adr.iloc[i]['ADR_LAT'],newTable_stu_adr.iloc[i]['ADR_LON']],icon=folium.Icon(color='red'))).add_to(m)
        #folium.Marker([newTable_site_adr.iloc[i]['SITE_LAT'],newTable_site_adr.iloc[i]['SITE_LON']],icon=folium.Icon(icon='green')).add_to(m)
        #m.add_children(plugins.HeatMap(newTable_adr_lo, radius=15))

    m.save(os.path.join(BASE_DIR,
                        'DjangoWeb V1\Interface\\templates\map.html'))
    return None
Beispiel #42
0
def get_meta_info(icustay_id):
    """
    This function  is supposed to return an df containing meta info like:
    - ward, moves, outcomes, admission ID, patient ID, etc FOR A GIVEN ICUSTAY.
    - this is what will become the labels later!

    :param icustay_id:
    :return:
    """
    stay = ICUSTAY.objects.filter(icustayID=icustay_id)
    stay = read_frame(stay)
    stay['intime'] = pd.to_datetime(stay['intime'])
    stay['outtime'] = pd.to_datetime(stay['outtime'])

    # admission
    admission_obj_str = stay['admission'].values[0] # this is a string: `Admission object (199395)`
    admission_id = re.search(re.compile('(\d+)'), admission_obj_str).group()
    admission = Admission.objects.filter(admID=admission_id)
    admission = read_frame(admission)
    stay['admission'] = admission_id

    # patient
    subject_obj_str = stay['subject'].values[0] # this is a string: `Patient object (199395)`
    subject_id = re.search(re.compile('(\d+)'), subject_obj_str).group()
    patient = Patient.objects.filter(subjectID=subject_id)
    patient = read_frame(patient)
    stay['subject'] = subject_id

    # gather info:
    stay.drop(['first_ward_id', 'last_ward_id', 'first_cu', 'last_cu'], axis=1, inplace=True)

    for c in ['adm_time', 'disch_time', 'inpmor', 'pdismor', 'read']:
        stay[c] = admission[c]

    for c in ['gender', 'age', 'date_of_death_hosp']:
        stay[c] = patient[c]

    return stay
Beispiel #43
0
def protocol_summary(request):
    '''
    Relies on django_highcharts for serializing data. Makes multiple chart
    rendering on a page more simple, however it doesn't appear to allow much
    in they way of customization.
    define a range, avg, diplay max and min
    '''
    athlete = get_user(request)
    data = BoulderingRoutineMetrics.objects.filter(session__athlete=athlete)
    test = BoulderingRoutineMetrics.objects.values(
        'min', 'max').filter(session__athlete=athlete)
    df = read_frame(data, verbose=False)
    df['avg'] = df['total_points'] / df['total_climbs']
    df['range'] = df['max'] - df['min']
    df = df.drop(['id', 'total_climbs', 'total_points'],
                 axis=1).set_index('session')
    dataset = BoulderingRoutineMetrics.objects \
        .values('session__sessionDate', 'min','max', 'total_points', 'total_climbs') \
        .filter(session__athlete = athlete)
    df2 = read_frame(dataset, verbose=False).rename(
        columns={
            'session__sessionDate': 'session date',
            'min': 'min value',
            'max': 'max value'
        })
    df3 = read_frame(dataset, fieldnames=['min','max'])\
                   .rename(columns={'session__sessionDate':'Session Date'})
    # .drop(['total_points', 'total_climbs'])

    df4 = df2.merge(df3, how='left', left_index=True, right_index=True)
    df4['avg'] = df4['total_points'] / df4['total_climbs']
    df4['range'] = df4['max value'] - df4['min value']
    chart = serialize(df4,
                      render_to='my-chart',
                      kind="bar",
                      output_type='json')

    return render(request, 'schedule/protocol_summary.html', {'chart': chart})
Beispiel #44
0
def get_stay_lab_timeseries(icustay_id):
    """
    For a given admission ID -> get the ts with all the labevents.
    then get in and out time from an icustay and cut the bits out that fall between
    """
    # get ICUstay:
    stay = ICUstay.objects.filter(icustayID=icustay_id)
    stay = read_frame(stay)
    stay['intime'] = pd.to_datetime(stay['intime'])
    stay['outtime'] = pd.to_datetime(stay['outtime'])

    admission_obj_str = stay['admission'].values[0] # this is a string: `Admission object (199395)`
    admission_id = re.search(re.compile('(\d+)'), admission_obj_str).group()

    stay_period = pd.Interval(stay['intime'].values[0],
                              stay['outtime'].values[0])

    # filter the lab_events:
    events = LabEventValue.objects.filter(admission_id=admission_id)
    events = read_frame(events)
    events['chart_time'] = pd.to_datetime(events['chart_time'])
    events.set_index('chart_time', inplace=True)
    idx = [t in stay_period for t in events.index.values]

    events = events.loc[idx].reset_index()
    events['icustayID'] = stay['icustayID'].unique()[0]

    # convert to ts:
    events_ts = _events_to_ts(events, variable_col='itemID', value='value').reset_index()

    if events_ts.empty:
        return events_ts

    intime = pd.Timestamp(stay['intime'].values[0])

    events_ts = add_hours_elpased_to_events(events_ts, dt=intime)

    return events_ts
Beispiel #45
0
def results(request):
    movies = Movies.objects.all()
    movies_df = read_frame(movies)
    ratings = Ratings.objects.all()
    ratings_df = read_frame(ratings)
    data = pd.merge(movies_df, ratings_df, on="movieid")
    # creating dataframe with 'rating' count values
    ratings = pd.DataFrame(data.groupby('title')['rating'].mean())
    ratings['num of ratings'] = pd.DataFrame(
        data.groupby('title')['rating'].count())
    moviemat = data.pivot_table(index='userid',
                                columns='title',
                                values='rating')
    ratings.sort_values('num of ratings', ascending=False).head(10)
    currently_rated_movies = request.session.get('movies')
    results = {}
    for item in currently_rated_movies:
        # analysing correlation with similar movies
        _user_ratings = moviemat[item["title"]]
        similar_to_ = moviemat.corrwith(_user_ratings)
        corr_ = pd.DataFrame(similar_to_, columns=['Correlation'])
        corr_.dropna(inplace=True)
        # Similar movies like current title
        corr_.sort_values('Correlation', ascending=False).head(10)
        corr_ = corr_.join(ratings['num of ratings'])
        strongest_corr = corr_[corr_['num of ratings'] > 100].sort_values(
            'Correlation', ascending=False).head(1)
        strongest_corr = strongest_corr.rename(
            columns={"num of ratings": "no_of_ratings"})
        if not strongest_corr.empty:
            results[strongest_corr.index.values[0]] = Ratings.objects.filter(
                userid=request.user.id).filter(
                    movieid=item["movieid"]).values_list(
                        'rating', flat=True)[0] * strongest_corr.Correlation[
                            0] * strongest_corr.no_of_ratings[0]

    print(results)
    return render(request, 'nxtwatch/results.html', {'results': results})
Beispiel #46
0
def election(request):
    station_ids = request.GET.getlist('station_id')
    if len(station_ids) < 2:
        _response400("station_id is required at least 2")
    try:
        station_ids = [int(station_id) for station_id in station_ids]
    except ValueError:
        _response400(f"station_id is invalid: {station_ids}")
    df_stations = read_frame(Station.objects.all())
    results = Election(df_stations, station_ids).run()
    response = dict(station_ids=station_ids, results=results)
    return JsonResponse(response,
                        safe=False,
                        json_dumps_params={'ensure_ascii': False})
Beispiel #47
0
def update_tags(request):
    if request.user.is_authenticated:
        my_tags = Tags.objects.filter(
            user=request.user).order_by('category').all()
        df = read_frame(my_tags)
        df = df[[
            'category', 'tag', 'drill_down', 'id', 'budget', 'fixed_cost'
        ]]
        df = df.values.tolist()
        template = loader.get_template('core_app/tag_details.html')
        context = {'results': df}
        return HttpResponse(template.render(context, request))
    else:
        return redirect('home')
Beispiel #48
0
def get_list(request):
    custid = request.POST.get('custid')
    #history = purchases.objects.all
    cust = customer.objects.all
    customers = read_frame(customer.objects.all())
    customers = customers['Customer_ID']
    custid = int(custid)

    if custid in customers.unique():
        #funtion
        df = read_frame(purchases.objects.all())
        df = df.loc[df['Customer_ID'] == custid]
        array = df['Products'].astype(int)
        cat = read_frame(catalogue.objects.all())
        cat = cat.head(5)
        pd.to_numeric(cat['objectID'])
        display = cat.loc[cat['objectID'].isin(array)]
        #function
        list1 = {"dataframe": cat, "customers": cust}
        return render(request, "get_list.html", list1)
    else:
        messages.error(request, 'Please enter valid username')
        return render(request, "index.html")
Beispiel #49
0
def players_ctx(pos,
                ctx,
                form,
                x_axis='points_per_game',
                y_axis='now_cost',
                size='selected_by_percent',
                limit=50):
    position = Position.objects.get(id=pos)
    players = Player.objects.filter(position=position).filter(
        minutes__gt=0).order_by('-' + x_axis)
    gw = Fixtures.objects.filter(
        finished=True).order_by('event_id').last().event_id
    ## Create DataFrame for Goalkeepers
    gkp = read_frame(
        players,
        fieldnames=[
            'first_name', 'second_name', 'team', 'points_per_game',
            'total_points', 'now_cost', 'form', 'minutes', 'assists',
            'goals_scored', 'own_goals', 'influence', 'influence_rank',
            'creativity', 'creativity_rank', 'threat', 'threat_rank',
            'clean_sheets', 'goals_conceded', 'saves', 'penalties_saved',
            'yellow_cards', 'red_cards', 'dreamteam_count',
            'selected_by_percent', 'transfers_in', 'transfers_out'
        ])
    # Merge first_name and second_name
    gkp['name'] = gkp['first_name'] + ' ' + gkp['second_name']
    gkp.drop(['first_name', 'second_name'], axis=1, inplace=True)
    # Change number of minutes to percent of time
    max_minutes = 90 * gw
    gkp.minutes = gkp.minutes.apply(
        lambda x: round(float(x) * 100 / max_minutes, 2))
    # Filter
    min_filter = gkp.minutes >= limit
    gkp = gkp[min_filter]
    # Add data to ctx dictionary
    ctx['title'] = f'Chart - {position.name}s'
    ctx['x'] = list(gkp[x_axis])
    ctx['y'] = list(gkp[y_axis])
    ctx['max_size'] = max(gkp[size]) if len(gkp[size]) > 0 else 0
    ctx['size'] = list(((gkp[size] / ctx['max_size']) + 1) * 10)
    ctx['players'] = gkp['name'].tolist()
    ctx['form'] = form
    x_regr, pred_regr = linear_regr(gkp[x_axis], gkp[y_axis])
    ctx['x_regr'] = x_regr
    ctx['pred_regr'] = pred_regr
    x_regr_poly, pred_regr_poly = poly_regr(gkp[x_axis], gkp[y_axis])
    ctx['x_regr_poly'] = x_regr_poly
    ctx['pred_regr_poly'] = pred_regr_poly

    return ctx
Beispiel #50
0
    def get(self, *args, **kwargs):
        user_taste = generics.get_object_or_404(UserTaste,
                                                user=self.request.user)

        reviews_from_user = Review.objects.all().filter(created_by=user_taste)

        reviews_from_user_df = read_frame(reviews_from_user,
                                          fieldnames=['game_id__id', 'rating'])
        reviews_from_user_df = reviews_from_user_df.rename(columns={
            'game_id__id': 'game_key',
            'rating': 'rating'
        })

        return Response(selfmade_KnnWithMeans_approach(reviews_from_user_df))
Beispiel #51
0
 def export_to_xls_search_history(self, request, queryset):
     df = read_frame(queryset,
                     verbose=True,
                     fieldnames=(
                         'user__user__id', 'user__user__username',
                         'date',
                         'name',
                     ))
     df = translate_column_names(df)
     return export_to_xls(df,
                          'search_history' +
                          request.GET.get('drf__day__gte ', '') +
                          request.GET.get('drf__day__lte', '') + '.xls',
                          engine='openpyxl')
Beispiel #52
0
def get_stocks_by_date_range(min: Optional[datetime] = None,
                             max: Optional[datetime] = None) -> pd.DataFrame:
    """
    Gets stock data from the database based on date range.
    """
    from stocks.stocks.models import Stock

    if min is None:
        return get_stocks()

    if max is None:
        max = datetime.now()

    return read_frame(Stock.objects.filter(price_date__range=(min, max)))
    def __init__(self):
        self.df = read_frame(
            get_user_model().objects.filter(
                date_joined__gte=(datetime.now() - timedelta(days=15))
            )
        )
        self.df['date_joined'] = pd.to_datetime(self.df['date_joined'])

        # TODO: Optimise next queries
        self.active = self.df[self.df.is_active==True][
            ['date_joined', 'id']
        ].groupby('date_joined').count().resample('D').count()
        self.nonactive = self.df[self.df.is_active==False][
            ['date_joined', 'id']
        ].groupby('date_joined').count().resample('D').count()
Beispiel #54
0
    def test_related_cols(self):
        qs = TradeLog.objects.all()
        cols = ['log_datetime', 'symbol', 'symbol__isin', 'trader__name',
                'price', 'volume', 'note__note']
        df = read_frame(qs, cols, verbose=False)

        self.assertEqual(df.shape, (qs.count(), len(cols)))
        self.assertListEqual(
            list(qs.values_list('symbol__isin', flat=True)),
            df.symbol__isin.tolist()
        )
        self.assertListEqual(
            list(qs.values_list('trader__name', flat=True)),
            df.trader__name.tolist()
        )
Beispiel #55
0
 def export_to_xls_exchange(self, request, queryset, ):
     df = read_frame(queryset,
                     index_col='date',
                     verbose=True,
                     fieldnames=(
                         'date',
                         'user__user__id', 'user__user__username',
                         'user__user__email',
                         'exchange',
                         'user__score', 'user__total_score',
                         'user__total_exchange',
                     ))
     df = translate_column_names(df)
     return export_to_xls(df,
                          'exchange' + request.GET.get('drf__day__gte ', '') + '_' +
                          request.GET.get('drf__day__lte', '') + '.xls',
                          engine='openpyxl')
Beispiel #56
0
 def monthly_patients(self):
     dataframe = read_frame(PatientVaccination.objects.all())
     dataframe['month'] = [date.strftime('%B') for date in dataframe['dateofvaccinereceiption']]
     groups = dataframe.groupby('month')['id'].count()
     return groups
Beispiel #57
0
    def runRedo(self):
        '''
        将更新票项的过程中失败的数据重做直到成功
        '''
        # 查出需要进行重做的数据
        self.writeSystemLog(u'重做过程开始执行')

        # 尝试进程登录
        loginResult = self.login(self.username, self.password)
        if loginResult:
            self.writeSystemLog(u'登录成功!')
        else:
            self.writeSystemLog(u'登录失败!')
            raise Exception(u'登录失败!')

        # 执行时间变量的初始化
        runStartTime = datetime.now()
        currentTime = datetime.now()
        timeSpend = currentTime - runStartTime
        # 获取总出错项
        qs = RefreshRedo.objects.filter(state=u'redoing')
        redos = read_frame(qs)
        errorCount = len(redos)
        self.writeSystemLog(u'共有%d次错误需要重做' % errorCount)


        # 不停处理每一个信息项,直到没有错误需要重做,或者执行时间到了
        while errorCount > 0:
            try:
                qs = RefreshRedo.objects.filter(state=u'redoing')
                redos = read_frame(qs)
                errorCount = len(redos)
                if errorCount == 0:
                    self.writeSystemLog(u'已无项目需要处理,程序将退出')
                else:
                    self.writeSystemLog(u'开始一次重做,共%d需要处理...' % errorCount)

                # 根据出错列表调用重新回订过程
                successCount = 0
                # 按唯一的日期获取数据,提高访问效率
                for day in redos.beginDay.drop_duplicates():
                    self.writeSystemLog(u'检查%s的余票信息' % day)

                    # 获取有余票的项
                    ticketInfo = pitcher.getTicketInfo(day)
                    c1 = ticketInfo[u'余票'].apply(lambda x: int(x)) > 0
                    remain = ticketInfo[c1]
                    remain[u'开航日期'] = day

                    # 余票信息和重做记录关联,得出可以重做的票项
                    leftColumns = ['beginDay', 'beginTime', 'departure', 'arrival']
                    rightColumns = [u'开航日期', u'开航时间', u'出发码头', u'抵达码头']
                    todos = redos.merge(remain, left_on=leftColumns, right_on=rightColumns).to_dict(outtype='records')

                    if todos:
                        self.writeSystemLog(u'有%d个重做项出现余票' % len(todos))
                    else:
                        self.writeSystemLog(u'无重做项有余票')
                        # 避免过分频繁访问服务器
                        time.sleep(self.normalWaitingSecond)

                    # 循环对有余票的项目进程操作
                    for todo in todos:
                        # 初始化相关信息
                        departure = todo['departure']
                        arrival = todo['arrival']
                        beginDay = todo['beginDay']
                        beginTime = todo['beginTime']
                        cnt = todo['cnt']
                        remainCnt = int(todo[u'余票'])
                        redoId = todo['id']
                        redo = RefreshRedo.objects.get(id=redoId)
                        # 如果当前时间和开航日期0点的时间小于1天,则不再刷新这个票项
                        # 因为正常刷新提前5天前就刷新好了,所以不会有影响
                        # 这样做主要是防止重做票项无限增长,且去订之前日期的票
                        beginDateTime = parser.parse(beginDay)
                        if (beginDateTime - currentTime).days < 1:
                            redo.state = u'failed'
                            redo.save()
                            self.writeSystemLog(
                                u'出发:%s,抵达:%s,开航时间:%s %s,人数:%s' % (departure, arrival, beginDay, beginTime, cnt))
                            self.writeSystemLog(u'该项已经超过可以重做的时间,已标记为失败!!!')
                            continue

                        # 尝试重调订票过程
                        dailyFlightId = pitcher.getDailyFlightId(beginDay, beginTime, departure, arrival)
                        if dailyFlightId:
                            self.writeSystemLog(
                                u'出发:%s,抵达:%s,开航时间:%s %s,人数:%s' % (departure, arrival, beginDay, beginTime, cnt))
                            if remainCnt >= cnt:
                                # 如果票数量够,按需票量抢
                                result = pitcher.orderTicket(dailyFlightId, cnt)
                                if result:
                                    # 重做成功将记录标识为完成
                                    redo.state = u'finished'
                                    redo.save()
                                    self.writeSystemLog(u'该项已经重做成功!')
                                    successCount += 1
                                else:
                                    self.writeSystemLog(u'虽有余票项但抢订失败!')
                            else:
                                # 如果票数不够,按实际票数抢
                                result = pitcher.orderTicket(dailyFlightId, remainCnt)
                                if result:
                                    redo.cnt -= remainCnt
                                    redo.save()
                                    self.writeSystemLog(u'重做项余票数不够,但已抢回%d张' % remainCnt)
                                else:
                                    self.writeSystemLog(u'虽有余票项但抢订失败!')
                        # 避免过分频繁访问服务器(for todo1 in todos:)
                        time.sleep(self.normalWaitingSecond)
                # 避免过分频繁访问服务器(for day in redos.beginDay.drop_duplicates())
                time.sleep(self.normalWaitingSecond)

                # 完成一次重做,打印执行信息
                errorCount -= successCount
                self.writeSystemLog(u'完成一次重做:%d项成功,%d项失败.' % (successCount, errorCount))
                # 更新执行时间
                currentTime = datetime.now()
                timeSpend = currentTime - runStartTime
            except Exception as e:
                self.writeSystemLog(u'发生异常:%s,程序将继续执行' % unicode(e))

            # 检查是否是登录状态,如果登录状态丢失则重新登录
            if not self.isLogin():
                self.writeSystemLog(u'连接丢失,将重新登录...')
                loginErrorCount = 0
                while True:
                    loginResult = self.login(self.username, self.password)
                    if loginResult:
                        # 如果登录成功清空原来登录失败的记录
                        self.writeSystemLog(u'登录成功!')
                        break
                    else:
                        loginErrorCount += 1
                        if loginErrorCount > self.maxLoginError:
                            self.writeSystemLog(u'登录失败超过%d次,程序退出!' % self.maxLoginError)
                            return
                        else:
                            self.writeSystemLog(u'登录失败!')
                            self.writeSystemLog(u'等待%s秒... ...' % self.errorWaitingSecond)
                            time.sleep(self.errorWaitingSecond)

        self.writeSystemLog(u'重做过程执行结束')
Beispiel #58
0
 def monthly_vaccine(self):
     dataframe = read_frame(PatientVaccination.objects.all())
     dataframe['month'] = [date.strftime('%B') for date in dataframe['dateofvaccinereceiption']]
     groups = dataframe.groupby(['month', 'patient_vaccine'])['id'].count().reset_index(name='count')
     return groups
Beispiel #59
0
 def monthly_transactions(self):
     dataframe = read_frame(Transaction.objects.all())
     dataframe['month'] = [date.strftime('%B') for date in dataframe['date']]
     dataframe['year'] = [date.strftime('%Y') for date in dataframe['date']]
     groups = dataframe.groupby(['year', 'month', 'type'])['amount'].sum().reset_index(name='amount')
     return groups
Beispiel #60
0
 def top_recipients(self):
     dataframe = read_frame(Transaction.objects.all())
     recipient_dataframe = dataframe[dataframe.type == 'Sent Transactions']
     return recipient_dataframe.groupby(['recipient', 'type'])['amount'].sum().reset_index(name='amount').sort('amount', ascending=False)