import os
import json
os.environ.setdefault("DJANGO_SETTINGS_MODULE", "zuobiao.settings")
import django
django.setup()

from django.db.models import Avg, Count, Max, Q
from dashboard.models import Question, Anser, User
from dashboard.models import INCOME_CHOICES, EDU_CHOICES, ANSER_CHOICES, SEX_CHOICES

import pandas as pd
import datetime as dt
import plotly.plotly as py
from plotly.graph_objs import Bar, Scatter, Marker, Layout

def get_display(choice_id, choice_set):
    return [v for k, v in choice_set if choice_id == k][0]

PK = 48
user_count = 170764
question = Question.objects.get(pk=PK)
ansers = question.anser_set
title = 'Question {}: {}'.format(PK, question.name)

data = ansers.values('choice').annotate(count=Count('choice')).order_by('choice')
x = [get_display(item['choice'], ANSER_CHOICES) for item in data]
y = [item['count'] for item in data]
py.iplot({
    'data': [Bar(x=x, y=y)],
    'layout': { 
            'title': title,
        },
    })

def get_user_data(field, choice_set):
    data = []
    for choice_id in [1, 2, 3, 4]:
        fieldname = 'user__{}'.format(field)
        queryset = ansers.filter(choice=choice_id).values(fieldname).annotate(count=Count(fieldname)).order_by('-count')
        x = [get_display(item[fieldname], choice_set) for item in queryset]
        y = [item['count'] / user_count * 100 for item in queryset]
        data.append({'x': x, 'y': y, 'type': 'bar', 'name': get_display(choice_id, ANSER_CHOICES)})   
    return data
data = get_user_data('sex', SEX_CHOICES)
py.iplot({
'data': data,
'layout': {
        'barmode': 'stack',
        'title': title
    }
})

def get_user_data_banlance(field, choice_set):
    data = []
    choices = [k for k, v in choice_set]
    for choice_id in [1, 2, 3, 4]:
        x = [k for k, v in choice_set]
        y = [0] * len(choice_set)
        fieldname = 'user__{}'.format(field)
        queryset = ansers.filter(choice=choice_id).values(fieldname).annotate(count=Count(fieldname)).order_by('-count')
        for item in queryset:
            for i, choice in enumerate(choices):
                if item[fieldname] == choice:
                    x[i] = get_display(item[fieldname], choice_set)
                    y[i] = item['count']
        data.append({'x': x, 'y': y, 'type': 'bar', 'name': get_display(choice_id, ANSER_CHOICES)})   
    sums = [sum(t) for t in zip(*[item['y'] for item in data])]
    for item in data:
        item['y'] = [y / (sums[i] or 1) * 100 for i, y in enumerate(item['y'])]
    return data
data = get_user_data_banlance('sex', SEX_CHOICES)
py.iplot({
'data': data,
'layout': {
        'barmode': 'stack',
        'title': title
    }
})

data = get_user_data('income', INCOME_CHOICES)
py.iplot({
'data': data,
'layout': {
        'barmode': 'stack',
        'title': title
    }
})

data = get_user_data_banlance('income', INCOME_CHOICES)
py.iplot({
'data': data,
'layout': {
        'barmode': 'stack',
        'title': title
    }
})

data = get_user_data('education', EDU_CHOICES)
py.iplot({
'data': data,
'layout': {
        'barmode': 'stack',
        'title': title
    }
})

data = get_user_data_banlance('education', EDU_CHOICES)
py.iplot({
'data': data,
'layout': {
        'barmode': 'stack',
        'title': title
    }
})

data = []
for choice_id in [1, 2, 3, 4]:
    field = 'birthday'
    fieldname = 'user__{}'.format(field)
    queryset = ansers.filter(choice=choice_id).values(fieldname).annotate(count=Count(fieldname)).order_by('-count')[:20]
    x = [item[fieldname] for item in queryset]
    y = [item['count'] for item in queryset]
    data.append({'x': x, 'y': y, 'type': 'bar', 'name': get_display(choice_id, ANSER_CHOICES)})   
    
py.iplot({
'data': data,
'layout': {
        'barmode': 'stack',
        'title': title
    }
})

data = []
years = range(1930, 2000)
for choice_id in [1, 2, 3, 4]:
    field = 'birthday'
    x, y = list(range(1930, 2000)), [0] * 70 
    fieldname = 'user__{}'.format(field)
    queryset = ansers.filter(choice=choice_id).values(fieldname).annotate(count=Count(fieldname)).order_by('-count')
    for item in queryset:
        for i, year in enumerate(years):
            if item[fieldname] == year:
                x[i] = item[fieldname]
                y[i] = item['count']
    data.append({'x': x, 'y': y, 'type': 'bar', 'name': get_display(choice_id, ANSER_CHOICES)})   

sums = [sum(t) for t in zip(*[item['y'] for item in data])]
for item in data:
    item['y'] = [y / (sums[i] or 1) * 100 for i, y in enumerate(item['y'])]

py.iplot({
'data': data,
'layout': {
        'barmode': 'stack',
        'title': title,
    }
})

%%html
<style>a {text-decoration: none!important}</style>

Index¶