In [3]:
#!/usr/bin/env python3
# Project: flask_genetron
# Filename: import_db.py
# Description: 
# Author: Li Pidong
# E-mail: hope-dream@163.com
# Created: 22/10/16 20:25 
# Version:
# Last-Updated:

# from app import create_app, db

# import os
# from  app.genetron.models import Table, Patient
import pandas as pd


# app = create_app('development')
# app_context = app.app_context()
# app_context.push()
# db.create_all()
# for ii in range(100):
#     aa=Table(name='name'+str(ii), age=ii)
#     patient=Patient(patient_id='PA0'+str(ii), name='fad', age=11, tissue='tissue', cancer='cancer', start_time=None, finish_time=None)
#     db.session.add(aa)
#     db.session.add(patient)
# db.session.commit()
info_data=pd.read_excel('/Users/lipidong/Downloads/项目进展1021.xlsx')

In [7]:
import numpy as np

In [8]:
np.isnan(info_data.iloc[0,2])


Out[8]:
True

In [10]:
def proc_nan(_value):
    if np.isnan(_value):
        return None

In [12]:
import datetime

In [26]:
aa=datetime.datetime.strptime("2014-12-31 18:20:10", "%Y-%m-%d %H:%M:%S")
datetime.datetime.fromtimestamp(1421077403.0)
type(aa)


Out[26]:
datetime.datetime

In [25]:
type(info_data.iloc[1,10].date())


Out[25]:
datetime.date

In [22]:
help(pd.tslib.Timestamp)


Help on class Timestamp in module pandas.tslib:

class Timestamp(_Timestamp)
 |  TimeStamp is the pandas equivalent of python's Datetime
 |  and is interchangable with it in most cases. It's the type used
 |  for the entries that make up a DatetimeIndex, and other timeseries
 |  oriented data structures in pandas.
 |  
 |  Method resolution order:
 |      Timestamp
 |      _Timestamp
 |      datetime.datetime
 |      datetime.date
 |      __builtin__.object
 |  
 |  Methods defined here:
 |  
 |  __new__(...)
 |  
 |  __radd__(...)
 |  
 |  __reduce__(...)
 |  
 |  __repr__(...)
 |  
 |  __setstate__(...)
 |  
 |  astimezone = tz_convert(...)
 |  
 |  isoformat(...)
 |  
 |  normalize(...)
 |      Normalize Timestamp to midnight, preserving
 |      tz information.
 |  
 |  replace(...)
 |  
 |  to_julian_date(...)
 |      Convert TimeStamp to a Julian Date.
 |      0 Julian date is noon January 1, 4713 BC.
 |  
 |  to_period(...)
 |      Return an period of which this timestamp is an observation.
 |  
 |  to_pydatetime(...)
 |      If warn=True, issue warning if nanoseconds is nonzero
 |  
 |  tz_convert(...)
 |      Convert Timestamp to another time zone or localize to requested time
 |      zone
 |      
 |      Parameters
 |      ----------
 |      tz : string, pytz.timezone, dateutil.tz.tzfile or None
 |          Time zone for time which Timestamp will be converted to.
 |          None will remove timezone holding UTC time.
 |      
 |      Returns
 |      -------
 |      converted : Timestamp
 |  
 |  tz_localize(...)
 |      Convert naive Timestamp to local time zone, or remove
 |      timezone from tz-aware Timestamp.
 |      
 |      Parameters
 |      ----------
 |      tz : string, pytz.timezone, dateutil.tz.tzfile or None
 |          Time zone for time which Timestamp will be converted to.
 |          None will remove timezone holding local time.
 |      ambiguous : bool, 'NaT', default 'raise'
 |          - bool contains flags to determine if time is dst or not (note
 |          that this flag is only applicable for ambiguous fall dst dates)
 |          - 'NaT' will return NaT for an ambiguous time
 |          - 'raise' will raise an AmbiguousTimeError for an ambiguous time
 |      
 |      Returns
 |      -------
 |      localized : Timestamp
 |  
 |  ----------------------------------------------------------------------
 |  Class methods defined here:
 |  
 |  combine(...) from __builtin__.type
 |  
 |  fromordinal(...) from __builtin__.type
 |      passed an ordinal, translate and convert to a ts
 |      note: by definition there cannot be any tz info on the ordinal itself
 |  
 |  fromtimestamp(...) from __builtin__.type
 |  
 |  now(...) from __builtin__.type
 |      Return the current time in the local timezone.  Equivalent
 |      to datetime.now([tz])
 |      
 |      Parameters
 |      ----------
 |      tz : string / timezone object, default None
 |          Timezone to localize to
 |  
 |  today(...) from __builtin__.type
 |      Return the current time in the local timezone.  This differs
 |      from datetime.today() in that it can be localized to a
 |      passed timezone.
 |      
 |      Parameters
 |      ----------
 |      tz : string / timezone object, default None
 |          Timezone to localize to
 |  
 |  utcfromtimestamp(...) from __builtin__.type
 |  
 |  utcnow(...) from __builtin__.type
 |  
 |  ----------------------------------------------------------------------
 |  Data descriptors defined here:
 |  
 |  __dict__
 |      dictionary for instance variables (if defined)
 |  
 |  __weakref__
 |      list of weak references to the object (if defined)
 |  
 |  asm8
 |  
 |  dayofweek
 |  
 |  dayofyear
 |  
 |  days_in_month
 |  
 |  daysinmonth
 |  
 |  freq
 |  
 |  freqstr
 |  
 |  is_month_end
 |  
 |  is_month_start
 |  
 |  is_quarter_end
 |  
 |  is_quarter_start
 |  
 |  is_year_end
 |  
 |  is_year_start
 |  
 |  microsecond
 |  
 |  quarter
 |  
 |  tz
 |      Alias for tzinfo
 |  
 |  week
 |  
 |  weekofyear
 |  
 |  ----------------------------------------------------------------------
 |  Data and other attributes defined here:
 |  
 |  __qualname__ = 'Timestamp'
 |  
 |  max = Timestamp('2262-04-11 23:47:16.854775807')
 |  
 |  min = Timestamp('1677-09-22 00:12:43.145225')
 |  
 |  ----------------------------------------------------------------------
 |  Methods inherited from _Timestamp:
 |  
 |  __add__(...)
 |      x.__add__(y) <==> x+y
 |  
 |  __eq__(...)
 |      x.__eq__(y) <==> x==y
 |  
 |  __ge__(...)
 |      x.__ge__(y) <==> x>=y
 |  
 |  __gt__(...)
 |      x.__gt__(y) <==> x>y
 |  
 |  __hash__(...)
 |      x.__hash__() <==> hash(x)
 |  
 |  __le__(...)
 |      x.__le__(y) <==> x<=y
 |  
 |  __lt__(...)
 |      x.__lt__(y) <==> x<y
 |  
 |  __ne__(...)
 |      x.__ne__(y) <==> x!=y
 |  
 |  __rsub__(...)
 |      x.__rsub__(y) <==> y-x
 |  
 |  __sub__(...)
 |      x.__sub__(y) <==> x-y
 |  
 |  to_datetime(...)
 |  
 |  to_datetime64(...)
 |      Returns a numpy.datetime64 object with 'ns' precision
 |  
 |  ----------------------------------------------------------------------
 |  Data descriptors inherited from _Timestamp:
 |  
 |  nanosecond
 |  
 |  offset
 |  
 |  value
 |  
 |  ----------------------------------------------------------------------
 |  Data and other attributes inherited from _Timestamp:
 |  
 |  __pyx_vtable__ = <capsule object NULL>
 |  
 |  ----------------------------------------------------------------------
 |  Methods inherited from datetime.datetime:
 |  
 |  __getattribute__(...)
 |      x.__getattribute__('name') <==> x.name
 |  
 |  __str__(...)
 |      x.__str__() <==> str(x)
 |  
 |  ctime(...)
 |      Return ctime() style string.
 |  
 |  date(...)
 |      Return date object with same year, month and day.
 |  
 |  dst(...)
 |      Return self.tzinfo.dst(self).
 |  
 |  strptime(...)
 |      string, format -> new datetime parsed from a string (like time.strptime()).
 |  
 |  time(...)
 |      Return time object with same time but with tzinfo=None.
 |  
 |  timetuple(...)
 |      Return time tuple, compatible with time.localtime().
 |  
 |  timetz(...)
 |      Return time object with same time and tzinfo.
 |  
 |  tzname(...)
 |      Return self.tzinfo.tzname(self).
 |  
 |  utcoffset(...)
 |      Return self.tzinfo.utcoffset(self).
 |  
 |  utctimetuple(...)
 |      Return UTC time tuple, compatible with time.localtime().
 |  
 |  ----------------------------------------------------------------------
 |  Data descriptors inherited from datetime.datetime:
 |  
 |  hour
 |  
 |  minute
 |  
 |  second
 |  
 |  tzinfo
 |  
 |  ----------------------------------------------------------------------
 |  Data and other attributes inherited from datetime.datetime:
 |  
 |  resolution = datetime.timedelta(0, 0, 1)
 |  
 |  ----------------------------------------------------------------------
 |  Methods inherited from datetime.date:
 |  
 |  __format__(...)
 |      Formats self with strftime.
 |  
 |  isocalendar(...)
 |      Return a 3-tuple containing ISO year, week number, and weekday.
 |  
 |  isoweekday(...)
 |      Return the day of the week represented by the date.
 |      Monday == 1 ... Sunday == 7
 |  
 |  strftime(...)
 |      format -> strftime() style string.
 |  
 |  toordinal(...)
 |      Return proleptic Gregorian ordinal.  January 1 of year 1 is day 1.
 |  
 |  weekday(...)
 |      Return the day of the week represented by the date.
 |      Monday == 0 ... Sunday == 6
 |  
 |  ----------------------------------------------------------------------
 |  Data descriptors inherited from datetime.date:
 |  
 |  day
 |  
 |  month
 |  
 |  year


In [35]:
info


Out[35]:
编号                           PC562TQ2
患者姓名                              李锦生
性别                                NaN
年龄                                NaN
病理提示              胶质母细胞瘤,WHO III-IV 级
Indications             Glioblastoma 
组织             Central nervous system
突变基因数目                            NaN
panel                             509
接收                                NaN
应出                2016-10-13 00:00:00
当前日期              2016-10-21 00:00:00
剩余天数                               -8
项目进展情况                  2016-10-9要病理;
报告初稿                              NaN
Unnamed: 15                       NaN
审核                                NaN
Unnamed: 17                       NaN
复核                                NaN
Unnamed: 19                       NaN
销售                                NaN
病历信息                              NaN
备注                                NaN
Name: 1, dtype: object

In [11]:
info_data.dtypes


Out[11]:
编号                     object
患者姓名                   object
性别                    float64
年龄                    float64
病理提示                   object
Indications            object
组织                     object
突变基因数目                float64
panel                   int64
接收                     object
应出             datetime64[ns]
当前日期           datetime64[ns]
剩余天数                  float64
项目进展情况                 object
报告初稿                  float64
Unnamed: 15           float64
审核                    float64
Unnamed: 17           float64
复核                    float64
Unnamed: 19           float64
销售                    float64
病历信息                  float64
备注                    float64
dtype: object

In [33]:
np.isnan(info[4])


---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
<ipython-input-33-8148e7f9019f> in <module>()
----> 1 np.isnan(info[4])

TypeError: Not implemented for this type

In [30]:
ii = 1
info = info_data.iloc[ii, :]
patient_id = info[0]
name = info[1]
sex = proc_nan(info[2])
age = proc_nan(info[3])
histology = proc_nan(info[4])
indication = proc_nan(info[5])
tissue = proc_nan(info[6])
panel = proc_nan(info[8])
start_time = info[9].date()
dead_line = info[10].date()


---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
<ipython-input-30-accadc5d89d9> in <module>()
      5 sex = proc_nan(info[2])
      6 age = proc_nan(info[3])
----> 7 histology = proc_nan(info[4])
      8 indication = proc_nan(info[5])
      9 tissue = proc_nan(info[6])

<ipython-input-10-619083472d3f> in proc_nan(_value)
      1 def proc_nan(_value):
----> 2     if np.isnan(_value):
      3         return None

TypeError: Not implemented for this type

In [4]:
for ii in range(len(info_data)):


Out[4]:
编号 患者姓名 性别 年龄 病理提示 Indications 组织 突变基因数目 panel 接收 ... 项目进展情况 报告初稿 Unnamed: 15 审核 Unnamed: 17 复核 Unnamed: 19 销售 病历信息 备注
0 PC562TQ1 李锦生 NaN NaN 混合型少突星形细胞瘤,WHO II级 Oligoastrocytoma Central nervous system NaN 509 NaN ... 2016-10-9要病理;检测项目为500panel.艾吉泰康捕获,正常出具500panel... NaN NaN NaN NaN NaN NaN NaN NaN NaN
1 PC562TQ2 李锦生 NaN NaN 胶质母细胞瘤,WHO III-IV 级 Glioblastoma Central nervous system NaN 509 NaN ... 2016-10-9要病理; NaN NaN NaN NaN NaN NaN NaN NaN NaN
2 PC562TQ3 李锦生 NaN NaN 少突胶质细胞瘤 Oligodendroglioma Central nervous system NaN 509 NaN ... 2016-10-9要病理; NaN NaN NaN NaN NaN NaN NaN NaN NaN
3 PC562TQ4 李锦生 NaN NaN 少突胶质细胞瘤 Oligodendroglioma Central nervous system NaN 509 NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
4 PC562TQ5 李锦生 NaN NaN 癌旁 NaN Central nervous system NaN 509 NaN ... 2016-10-9要病理; NaN NaN NaN NaN NaN NaN NaN NaN NaN

5 rows × 23 columns


In [ ]: