Basic Python Training at Arcesium - Day 4

Apr 15-18, 2019 Vikrant Patil

These notes are available online at http://notes.pipal.in/2019/arcesium_basic_apr/day4.html

© Pipal Academy LLP

Day 1 | Day 2 | Day 3 | Day 4

We will be using python 3 (>= 3.0) from anaconda for this training. You can download it from

https://www.anaconda.com/download/

classes

In [3]:
x = 1
In [4]:
type(x)
Out[4]:
int
In [5]:
class Foo:
    
    def __init__(self, x):
        self.x = x
In [6]:
f = Foo(2)
In [7]:
type(f)
Out[7]:
__main__.Foo
In [8]:
def fun():
    pass
In [9]:
fun
Out[9]:
<function __main__.fun()>
In [10]:
type(fun)
Out[10]:
function
In [11]:
l = list(range(5))
In [12]:
type(l)
Out[12]:
list

pandas

to install any python package use pip command

pip install pandas
python -m pip install pandas
In [13]:
import pandas as pd
In [14]:
import numpy as np
In [16]:
a = np.array(range(10))
In [17]:
a
Out[17]:
array([0, 1, 2, 3, 4, 5, 6, 7, 8, 9])
In [18]:
a.shape
Out[18]:
(10,)
In [19]:
a.reshape(5,2)
Out[19]:
array([[0, 1],
       [2, 3],
       [4, 5],
       [6, 7],
       [8, 9]])
In [20]:
e = np.zeros(1000).reshape(10, 10, 10)
In [22]:
e.shape
Out[22]:
(10, 10, 10)

series

In [23]:
s = pd.Series([1.1, 2.2, 3.3, 4,4])
In [24]:
s
Out[24]:
0    1.1
1    2.2
2    3.3
3    4.0
4    4.0
dtype: float64
In [25]:
s[0]
Out[25]:
1.1
In [26]:
s[4]
Out[26]:
4.0
In [28]:
s[-1] # this is not allowed

---------------------------------------------------------------------------
KeyError                                  Traceback (most recent call last)
<ipython-input-28-4f7a634f4128> in <module>
----> 1 s[-1] # this is not allowed

~/anaconda3/lib/python3.7/site-packages/pandas/core/series.py in __getitem__(self, key)
    765         key = com._apply_if_callable(key, self)
    766         try:
--> 767             result = self.index.get_value(self, key)
    768 
    769             if not is_scalar(result):

~/anaconda3/lib/python3.7/site-packages/pandas/core/indexes/base.py in get_value(self, series, key)
   3116         try:
   3117             return self._engine.get_value(s, k,
-> 3118                                           tz=getattr(series.dtype, 'tz', None))
   3119         except KeyError as e1:
   3120             if len(self) > 0 and self.inferred_type in ['integer', 'boolean']:

pandas/_libs/index.pyx in pandas._libs.index.IndexEngine.get_value()

pandas/_libs/index.pyx in pandas._libs.index.IndexEngine.get_value()

pandas/_libs/index.pyx in pandas._libs.index.IndexEngine.get_loc()

pandas/_libs/hashtable_class_helper.pxi in pandas._libs.hashtable.Int64HashTable.get_item()

pandas/_libs/hashtable_class_helper.pxi in pandas._libs.hashtable.Int64HashTable.get_item()

KeyError: -1
In [29]:
s = pd.Series([1.1, 2.2, 3.3, 4.4], index=['a','b','c','d'])
In [30]:
s
Out[30]:
a    1.1
b    2.2
c    3.3
d    4.4
dtype: float64
In [31]:
s['a']
Out[31]:
1.1
In [32]:
s['d']
Out[32]:
4.4
In [33]:
s = pd.Series([1.1, 2.2, 3.3, 4.4], index=range(1,5))
In [34]:
s
Out[34]:
1    1.1
2    2.2
3    3.3
4    4.4
dtype: float64
In [35]:
s[1]
Out[35]:
1.1
In [37]:
stocks = {"INFY":1030, "HCLTECH":500, "WIPRO":1631.0}
In [39]:
s= pd.Series(stocks)
In [40]:
s
Out[40]:
INFY       1030.0
HCLTECH     500.0
WIPRO      1631.0
dtype: float64
In [41]:
s['INFY']
Out[41]:
1030.0
In [42]:
s[0]
Out[42]:
1030.0
In [43]:
s.index
Out[43]:
Index(['INFY', 'HCLTECH', 'WIPRO'], dtype='object')
In [44]:
for key in s.index:
    print(key, s[key])

INFY 1030.0
HCLTECH 500.0
WIPRO 1631.0
In [45]:
for item in s:
    print(item)

1030.0
500.0
1631.0
In [46]:
stocks = {"INFY":1030, "HCLTECH":500, "WIPRO":1631.0}
index = ['WIPRO', "INFY", "HCLTECH"]
In [47]:
s = pd.Series(stocks, index=index)
In [48]:
s
Out[48]:
WIPRO      1631.0
INFY       1030.0
HCLTECH     500.0
dtype: float64
In [49]:
s[0]
Out[49]:
1631.0
In [50]:
s["WIPRO"]
Out[50]:
1631.0

filtering

In [53]:
stocksd = {"INFY":1030, "HCLTECH":500, "WIPRO":1631.0, "TATA":200, "RELIANCE":800}
order = ['WIPRO', "INFY", "HCLTECH", "TATA", "RELIANCE"]
stocks = pd.Series(stocksd, index=order)
In [54]:
stocks
Out[54]:
WIPRO       1631.0
INFY        1030.0
HCLTECH      500.0
TATA         200.0
RELIANCE     800.0
dtype: float64
In [55]:
stocks[stocks>300]
Out[55]:
WIPRO       1631.0
INFY        1030.0
HCLTECH      500.0
RELIANCE     800.0
dtype: float64
In [56]:
stocks > 100
Out[56]:
WIPRO       True
INFY        True
HCLTECH     True
TATA        True
RELIANCE    True
dtype: bool
In [57]:
stocks == 200.0
Out[57]:
WIPRO       False
INFY        False
HCLTECH     False
TATA         True
RELIANCE    False
dtype: bool
In [59]:
f500 = stocks >= 500
In [60]:
f500
Out[60]:
WIPRO        True
INFY         True
HCLTECH      True
TATA        False
RELIANCE     True
dtype: bool
In [62]:
stocks[f500]
Out[62]:
WIPRO       1631.0
INFY        1030.0
HCLTECH      500.0
RELIANCE     800.0
dtype: float64
In [63]:
f200 = stocks == 200.0
In [64]:
stocks[f200]
Out[64]:
TATA    200.0
dtype: float64
In [66]:
stocks[stocks <= 500]
Out[66]:
HCLTECH    500.0
TATA       200.0
dtype: float64
In [67]:
stocks[(stocks <= 500) & (stocks>200)]
Out[67]:
HCLTECH    500.0
dtype: float64
In [68]:
stocks[(stocks <= 500) | (stocks==800.0)]
Out[68]:
HCLTECH     500.0
TATA        200.0
RELIANCE    800.0
dtype: float64
In [69]:
stocks.index == "INFY"
Out[69]:
array([False,  True, False, False, False])
In [70]:
stocks[(stocks <= 500) | (stocks.index=="INFY")]
Out[70]:
INFY       1030.0
HCLTECH     500.0
TATA        200.0
dtype: float64
In [71]:
pd.Series([2, 3, 4, 5, 6])
Out[71]:
0    2
1    3
2    4
3    5
4    6
dtype: int64
In [73]:
pd.Series(["a","b","c"])
Out[73]:
0    a
1    b
2    c
dtype: object
In [74]:
pd.Series([1.1, 2.2, 3.5])
Out[74]:
0    1.1
1    2.2
2    3.5
dtype: float64
In [75]:
pd.Series(["a", "b", 1,1, 2.2])
Out[75]:
0      a
1      b
2      1
3      1
4    2.2
dtype: object
In [76]:
pd.Series([1, 2, 1,1, 2.2])
Out[76]:
0    1.0
1    2.0
2    1.0
3    1.0
4    2.2
dtype: float64

arithmatic operations

In [78]:
stocks +10
Out[78]:
WIPRO       1641.0
INFY        1040.0
HCLTECH      510.0
TATA         210.0
RELIANCE     810.0
dtype: float64
In [79]:
stocks * 2
Out[79]:
WIPRO       3262.0
INFY        2060.0
HCLTECH     1000.0
TATA         400.0
RELIANCE    1600.0
dtype: float64
In [80]:
s2 = stocks * 2
In [81]:
s2
Out[81]:
WIPRO       3262.0
INFY        2060.0
HCLTECH     1000.0
TATA         400.0
RELIANCE    1600.0
dtype: float64
In [82]:
stocks
Out[82]:
WIPRO       1631.0
INFY        1030.0
HCLTECH      500.0
TATA         200.0
RELIANCE     800.0
dtype: float64
In [83]:
stocks.index
Out[83]:
Index(['WIPRO', 'INFY', 'HCLTECH', 'TATA', 'RELIANCE'], dtype='object')
In [85]:
snew = stocks.reindex(index=['WIPRO', 'INFY', 'HCLTECH', 'TATA', 'RELIANCE', "BIRLA", "MARUTI"])
In [86]:
snew
Out[86]:
WIPRO       1631.0
INFY        1030.0
HCLTECH      500.0
TATA         200.0
RELIANCE     800.0
BIRLA          NaN
MARUTI         NaN
dtype: float64
In [87]:
snew["BIRLA"] = 100
In [88]:
snew
Out[88]:
WIPRO       1631.0
INFY        1030.0
HCLTECH      500.0
TATA         200.0
RELIANCE     800.0
BIRLA        100.0
MARUTI         NaN
dtype: float64
In [89]:
snew1 = stocks.reindex(index=['INFY', 'HCLTECH', 'TATA', 'RELIANCE', "BIRLA",'WIPRO',  "MARUTI"])
In [90]:
snew1
Out[90]:
INFY        1030.0
HCLTECH      500.0
TATA         200.0
RELIANCE     800.0
BIRLA          NaN
WIPRO       1631.0
MARUTI         NaN
dtype: float64
In [92]:
snew1.drop(labels=["BIRLA", "INFY"])
Out[92]:
HCLTECH      500.0
TATA         200.0
RELIANCE     800.0
WIPRO       1631.0
MARUTI         NaN
dtype: float64
In [93]:
help(snew.drop)

Help on method drop in module pandas.core.series:

drop(labels=None, axis=0, index=None, columns=None, level=None, inplace=False, errors='raise') method of pandas.core.series.Series instance
    Return Series with specified index labels removed.
    
    Remove elements of a Series based on specifying the index labels.
    When using a multi-index, labels on different levels can be removed
    by specifying the level.
    
    Parameters
    ----------
    labels : single label or list-like
        Index labels to drop.
    axis : 0, default 0
        Redundant for application on Series.
    index, columns : None
        Redundant for application on Series, but index can be used instead
        of labels.
    
        .. versionadded:: 0.21.0
    level : int or level name, optional
        For MultiIndex, level for which the labels will be removed.
    inplace : bool, default False
        If True, do operation inplace and return None.
    errors : {'ignore', 'raise'}, default 'raise'
        If 'ignore', suppress error and only existing labels are dropped.
    
    Returns
    -------
    dropped : pandas.Series
    
    See Also
    --------
    Series.reindex : Return only specified index labels of Series.
    Series.dropna : Return series without null values.
    Series.drop_duplicates : Return Series with duplicate values removed.
    DataFrame.drop : Drop specified labels from rows or columns.
    
    Raises
    ------
    KeyError
        If none of the labels are found in the index.
    
    Examples
    --------
    >>> s = pd.Series(data=np.arange(3), index=['A','B','C'])
    >>> s
    A  0
    B  1
    C  2
    dtype: int64
    
    Drop labels B en C
    
    >>> s.drop(labels=['B','C'])
    A  0
    dtype: int64
    
    Drop 2nd level label in MultiIndex Series
    
    >>> midx = pd.MultiIndex(levels=[['lama', 'cow', 'falcon'],
    ...                              ['speed', 'weight', 'length']],
    ...                      labels=[[0, 0, 0, 1, 1, 1, 2, 2, 2],
    ...                              [0, 1, 2, 0, 1, 2, 0, 1, 2]])
    >>> s = pd.Series([45, 200, 1.2, 30, 250, 1.5, 320, 1, 0.3],
    ...               index=midx)
    >>> s
    lama    speed      45.0
            weight    200.0
            length      1.2
    cow     speed      30.0
            weight    250.0
            length      1.5
    falcon  speed     320.0
            weight      1.0
            length      0.3
    dtype: float64
    
    >>> s.drop(labels='weight', level=1)
    lama    speed      45.0
            length      1.2
    cow     speed      30.0
            length      1.5
    falcon  speed     320.0
            length      0.3
    dtype: float64
In [95]:
snew1.drop( index=['HCLTECH', 'TATA', 'RELIANCE','WIPRO',  "MARUTI"])
Out[95]:
INFY     1030.0
BIRLA       NaN
dtype: float64

Dataframe

In [96]:
df = pd.DataFrame({
                    "col1":[1, 2, 3, 4, 5],
                    "col2": [1, 1, 1, 1, 1],
                    "cat": ["a","b","c","d","e"]
                    })
In [97]:
df
Out[97]:
col1 col2 cat
0 1 1 a
1 2 1 b
2 3 1 c
3 4 1 d
4 5 1 e
In [98]:
df['col1']
Out[98]:
0    1
1    2
2    3
3    4
4    5
Name: col1, dtype: int64
In [99]:
df.col1
Out[99]:
0    1
1    2
2    3
3    4
4    5
Name: col1, dtype: int64
In [100]:
df = pd.DataFrame({
                    "col1":[1, 2, 3, 4, 5],
                    "col2": [1, 1, 1, 1, 1],
                    "cat": ["a","b","c","d","e"],
                    "dog here":["*"*i for i in range(5)],
                    })
In [101]:
df
Out[101]:
col1 col2 cat dog here
0 1 1 a
1 2 1 b *
2 3 1 c **
3 4 1 d ***
4 5 1 e ****
In [102]:
df.cat
Out[102]:
0    a
1    b
2    c
3    d
4    e
Name: cat, dtype: object
In [103]:
df['dog here']
Out[103]:
0        
1       *
2      **
3     ***
4    ****
Name: dog here, dtype: object
In [104]:
df
Out[104]:
col1 col2 cat dog here
0 1 1 a
1 2 1 b *
2 3 1 c **
3 4 1 d ***
4 5 1 e ****
In [105]:
df[df.col1<3]
Out[105]:
col1 col2 cat dog here
0 1 1 a
1 2 1 b *
In [106]:
[1, None, 3]
Out[106]:
[1, None, 3]
In [107]:
snew
Out[107]:
WIPRO       1631.0
INFY        1030.0
HCLTECH      500.0
TATA         200.0
RELIANCE     800.0
BIRLA        100.0
MARUTI         NaN
dtype: float64
In [108]:
df = pd.DataFrame({
                    "col1":[1, 2, 3, np.NAN, 5],
                    "col2": [1, 1, 1, 1, 1],
                    "cat": ["a","b","c","d","e"],
                    "dog here":["*"*i for i in range(5)],
                    })
In [109]:
df
Out[109]:
col1 col2 cat dog here
0 1.0 1 a
1 2.0 1 b *
2 3.0 1 c **
3 NaN 1 d ***
4 5.0 1 e ****
In [110]:
df.col1
Out[110]:
0    1.0
1    2.0
2    3.0
3    NaN
4    5.0
Name: col1, dtype: float64
In [111]:
df.col1.sum()
Out[111]:
11.0
In [112]:
df.col1.mean()
Out[112]:
2.75
In [113]:
sum([1, 2, 3, 5],)
Out[113]:
11
In [114]:
11/4
Out[114]:
2.75
In [115]:
11/5
Out[115]:
2.2
In [116]:
df
Out[116]:
col1 col2 cat dog here
0 1.0 1 a
1 2.0 1 b *
2 3.0 1 c **
3 NaN 1 d ***
4 5.0 1 e ****
In [118]:
col22 = df.col2*2
In [119]:
col22
Out[119]:
0    2
1    2
2    2
3    2
4    2
Name: col2, dtype: int64
In [120]:
df['col22'] = col22
In [121]:
df
Out[121]:
col1 col2 cat dog here col22
0 1.0 1 a 2
1 2.0 1 b * 2
2 3.0 1 c ** 2
3 NaN 1 d *** 2
4 5.0 1 e **** 2
In [127]:
difford = pd.Series(list(reversed(range(5))), index=list(reversed(range(5))))
In [128]:
difford
Out[128]:
4    4
3    3
2    2
1    1
0    0
dtype: int64
In [129]:
df['difford'] = difford
In [130]:
df
Out[130]:
col1 col2 cat dog here col22 difford
0 1.0 1 a 2 0
1 2.0 1 b * 2 1
2 3.0 1 c ** 2 2
3 NaN 1 d *** 2 3
4 5.0 1 e **** 2 4
In [131]:
difford1 = pd.Series(list(reversed(range(4))), index=list(reversed(range(4))))
In [132]:
df['ord2'] = difford1
In [133]:
df
Out[133]:
col1 col2 cat dog here col22 difford ord2
0 1.0 1 a 2 0 0.0
1 2.0 1 b * 2 1 1.0
2 3.0 1 c ** 2 2 2.0
3 NaN 1 d *** 2 3 3.0
4 5.0 1 e **** 2 4 NaN
In [134]:
difford2 = pd.Series(list(reversed(range(6))), index=list(reversed(range(6))))
In [135]:
df['ord3']= difford2
In [136]:
df
Out[136]:
col1 col2 cat dog here col22 difford ord2 ord3
0 1.0 1 a 2 0 0.0 0
1 2.0 1 b * 2 1 1.0 1
2 3.0 1 c ** 2 2 2.0 2
3 NaN 1 d *** 2 3 3.0 3
4 5.0 1 e **** 2 4 NaN 4
In [137]:
stocks
Out[137]:
WIPRO       1631.0
INFY        1030.0
HCLTECH      500.0
TATA         200.0
RELIANCE     800.0
dtype: float64
In [141]:
import random
sdf = pd.DataFrame(stocks)
In [142]:
sdf
Out[142]:
0
WIPRO 1631.0
INFY 1030.0
HCLTECH 500.0
TATA 200.0
RELIANCE 800.0
In [147]:
value = [1030, 500, 1631.0,200,800]
volume = [100, 200, 140, 500, 1000]
order = ['WIPRO', "INFY", "HCLTECH", "TATA", "RELIANCE"]
stocksdf = pd.DataFrame( {"value":value, "volum":volume}, index=order)
In [148]:
stocksdf
Out[148]:
value volum
WIPRO 1030.0 100
INFY 500.0 200
HCLTECH 1631.0 140
TATA 200.0 500
RELIANCE 800.0 1000
In [149]:
backup = stocksdf.index
stocksdf.index = range(5)
In [150]:
stocksdf
Out[150]:
value volum
0 1030.0 100
1 500.0 200
2 1631.0 140
3 200.0 500
4 800.0 1000
In [151]:
stocksdf.index = backup
In [152]:
stocksdf
Out[152]:
value volum
WIPRO 1030.0 100
INFY 500.0 200
HCLTECH 1631.0 140
TATA 200.0 500
RELIANCE 800.0 1000
In [153]:
gain = pd.Series([5, 4, 3, 2, 1], index=["TATA", "RELIANCE", "HCLTECH", "WIPRO", "INFY"])
In [154]:
gain
Out[154]:
TATA        5
RELIANCE    4
HCLTECH     3
WIPRO       2
INFY        1
dtype: int64
In [155]:
stocksdf['gain'] = gain
In [156]:
stocksdf
Out[156]:
value volum gain
WIPRO 1030.0 100 2
INFY 500.0 200 1
HCLTECH 1631.0 140 3
TATA 200.0 500 5
RELIANCE 800.0 1000 4
In [157]:
gain1 = pd.Series([5, 4, 3, 2, 1, 7], index=["TATA", "RELIANCE", "HCLTECH", "WIPRO", "INFY", "XYZ"])
In [158]:
stocksdf['gain1'] = gain1
In [159]:
stocksdf
Out[159]:
value volum gain gain1
WIPRO 1030.0 100 2 2
INFY 500.0 200 1 1
HCLTECH 1631.0 140 3 3
TATA 200.0 500 5 5
RELIANCE 800.0 1000 4 4
In [160]:
stocksdf[stocksdf.volum>200]
Out[160]:
value volum gain gain1
TATA 200.0 500 5 5
RELIANCE 800.0 1000 4 4
In [161]:
stocksdf.volum[stocksdf.volum>200]
Out[161]:
TATA         500
RELIANCE    1000
Name: volum, dtype: int64

Reading files using pandas

url = "http://notes.pipal.in/2018/arcesium-basic-nov/loansData.csv"

In [218]:
url = "http://notes.pipal.in/2018/arcesium-basic-nov/loansData.csv"
In [219]:
loansData = pd.read_csv(url)
In [165]:
loansData
Out[165]:
Amount.Requested Amount.Funded.By.Investors Interest.Rate Loan.Length Loan.Purpose Debt.To.Income.Ratio State Home.Ownership Monthly.Income FICO.Range Open.CREDIT.Lines Revolving.CREDIT.Balance Inquiries.in.the.Last.6.Months Employment.Length
81174 20000 20000.00 8.90% 36 months debt_consolidation 14.90% SC MORTGAGE 6541.67 735-739 14.0 14272.0 2.0 < 1 year
99592 19200 19200.00 12.12% 36 months debt_consolidation 28.36% TX MORTGAGE 4583.33 715-719 12.0 11140.0 1.0 2 years
80059 35000 35000.00 21.98% 60 months debt_consolidation 23.81% CA MORTGAGE 11500.00 690-694 14.0 21977.0 1.0 2 years
15825 10000 9975.00 9.99% 36 months debt_consolidation 14.30% KS MORTGAGE 3833.33 695-699 10.0 9346.0 0.0 5 years
33182 12000 12000.00 11.71% 36 months credit_card 18.78% NJ RENT 3195.00 695-699 11.0 14469.0 0.0 9 years
62403 6000 6000.00 15.31% 36 months other 20.05% CT OWN 4891.67 670-674 17.0 10391.0 2.0 3 years
48808 10000 10000.00 7.90% 36 months debt_consolidation 26.09% MA RENT 2916.67 720-724 10.0 15957.0 0.0 10+ years
22090 33500 33450.00 17.14% 60 months credit_card 14.70% LA MORTGAGE 13863.42 705-709 12.0 27874.0 0.0 10+ years
76404 14675 14675.00 14.33% 36 months credit_card 26.92% CA RENT 3150.00 685-689 9.0 7246.0 1.0 8 years
15867 7000 7000.00 6.91% 36 months credit_card 7.10% CA RENT 5000.00 715-719 8.0 7612.0 0.0 3 years
94971 2000 2000.00 19.72% 36 months moving 10.29% FL RENT 3575.00 670-674 10.0 12036.0 0.0 6 years
36911 10625 10625.00 14.27% 36 months debt_consolidation 12.54% CA MORTGAGE 4250.00 665-669 14.0 10767.0 0.0 < 1 year
41200 28000 27975.00 21.67% 60 months debt_consolidation 13.07% CT MORTGAGE 14166.67 670-674 12.0 10311.0 0.0 1 year
83869 35000 34950.00 8.90% 36 months debt_consolidation 20.46% CT RENT 9166.67 735-739 19.0 21536.0 0.0 1 year
53853 9600 9600.00 7.62% 36 months debt_consolidation 3.45% DC RENT 11250.00 725-729 13.0 4606.0 0.0 < 1 year
21399 25000 24975.00 15.65% 60 months debt_consolidation 21.99% CA RENT 5416.67 730-734 6.0 13929.0 0.0 9 years
62127 10000 10000.00 12.12% 36 months debt_consolidation 17.72% CA RENT 9000.00 695-699 18.0 20317.0 0.0 7 years
23446 14000 13900.25 10.37% 60 months debt_consolidation 11.95% OH RENT 4333.33 740-744 6.0 7419.0 0.0 9 years
44987 10000 10000.00 9.76% 36 months credit_card 7.13% FL RENT 2733.33 730-734 7.0 6112.0 2.0 3 years
17977 5200 5175.00 9.99% 60 months debt_consolidation 10.29% AL MORTGAGE 3750.00 760-764 10.0 16094.0 0.0 < 1 year
86099 22000 21975.00 21.98% 36 months debt_consolidation 11.19% TX MORTGAGE 6666.67 665-669 9.0 23124.0 0.0 10+ years
99483 30000 30000.00 19.05% 60 months credit_card 21.25% FL MORTGAGE 6250.00 695-699 12.0 34927.0 0.0 6 years
28798 6500 6500.00 17.99% 60 months car 19.63% FL RENT 4100.00 665-669 11.0 11697.0 1.0 2 years
24168 17400 17400.00 11.99% 36 months credit_card 12.47% AZ RENT 6833.33 695-699 7.0 26587.0 0.0 7 years
10356 4000 4000.00 16.82% 60 months vacation 13.71% GA MORTGAGE 4500.00 670-674 5.0 20804.0 0.0 3 years
46027 7200 7200.00 7.90% 36 months debt_consolidation 24.82% TX RENT 5416.67 705-709 8.0 12017.0 0.0 7 years
2238 8000 8000.00 14.42% 36 months debt_consolidation 24.63% MA RENT 2964.17 675-679 9.0 8928.0 2.0 6 years
65278 8000 8000.00 15.31% 36 months debt_consolidation 15.46% CA MORTGAGE 2916.67 675-679 13.0 7152.0 1.0 5 years
4227 3000 3000.00 8.59% 36 months other 3.72% MA MORTGAGE 4167.00 765-769 4.0 7074.0 0.0 5 years
50182 14500 14500.00 7.90% 36 months debt_consolidation 4.85% GA MORTGAGE 3958.33 760-764 4.0 9598.0 0.0 4 years
... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
84265 20000 20000.00 22.95% 60 months debt_consolidation 7.10% NJ RENT 6750.00 665-669 6.0 16104.0 1.0 7 years
80231 19000 19000.00 7.90% 36 months debt_consolidation 9.76% RI MORTGAGE 5166.67 770-774 18.0 43617.0 2.0 10+ years
49533 17300 17250.00 22.45% 60 months wedding 3.58% PA MORTGAGE 5500.00 685-689 11.0 2306.0 3.0 4 years
102514 7000 711.54 15.13% 36 months major_purchase 18.91% CO MORTGAGE 3833.00 650-654 13.0 12634.0 0.0 2 years
78618 7200 7200.00 18.75% 36 months debt_consolidation 16.21% MI RENT 8333.33 660-664 8.0 28916.0 0.0 10+ years
86953 10000 10000.00 14.09% 36 months major_purchase 9.71% CO RENT 4583.33 675-679 6.0 3859.0 0.0 < 1 year
80129 4000 3925.00 14.09% 36 months credit_card 12.27% FL MORTGAGE 8583.33 675-679 9.0 36943.0 1.0 10+ years
85216 17500 17500.00 8.90% 36 months debt_consolidation 10.94% UT MORTGAGE 25000.00 730-734 9.0 34545.0 0.0 6 years
38247 20000 20000.00 11.71% 36 months credit_card 9.58% SD MORTGAGE 5416.67 725-729 12.0 18267.0 1.0 4 years
91245 16200 16200.00 15.80% 60 months debt_consolidation 7.92% PA MORTGAGE 4833.33 680-684 12.0 12313.0 2.0 10+ years
53041 10000 10000.00 6.03% 36 months small_business 13.03% FL RENT 5000.00 760-764 8.0 3952.0 0.0 2 years
63051 27000 27000.00 6.62% 36 months debt_consolidation 12.21% OH MORTGAGE 9250.00 810-814 12.0 4211.0 0.0 5 years
14446 4500 4475.00 7.51% 36 months small_business 20.27% VA MORTGAGE 7075.50 720-724 15.0 68618.0 2.0 10+ years
68628 15875 15875.00 14.33% 36 months small_business 17.44% MD MORTGAGE 3416.67 675-679 11.0 15891.0 0.0 2 years
98758 15000 15000.00 10.16% 36 months credit_card 28.28% OH MORTGAGE 6666.67 690-694 15.0 14880.0 0.0 10+ years
13070 25000 24950.00 10.75% 36 months debt_consolidation 20.48% OR MORTGAGE 7083.33 765-769 10.0 25429.0 0.0 6 years
45836 7000 7000.00 17.27% 36 months other 18.38% NY OWN 2464.37 665-669 9.0 7089.0 0.0 3 years
52330 15000 15000.00 19.99% 36 months wedding 18.05% CA RENT 8000.00 660-664 6.0 45976.0 1.0 2 years
48243 17000 17000.00 15.81% 36 months debt_consolidation 17.01% CO RENT 3833.33 685-689 6.0 15484.0 1.0 6 years
63256 19075 19075.00 18.75% 36 months debt_consolidation 15.23% NY RENT 5166.67 670-674 17.0 13749.0 3.0 10+ years
42124 10000 10000.00 11.71% 36 months debt_consolidation 8.40% CA RENT 4500.00 710-714 8.0 8404.0 1.0 3 years
78043 8475 8475.00 7.62% 36 months debt_consolidation 15.88% CA RENT 3983.33 720-724 9.0 6882.0 0.0 NaN
925 6400 6350.00 10.08% 36 months debt_consolidation 8.11% NJ MORTGAGE 5166.67 710-714 5.0 5815.0 2.0 10+ years
74047 30000 30000.00 23.28% 60 months other 12.10% IL MORTGAGE 7083.33 675-679 16.0 17969.0 1.0 10+ years
49957 24000 23975.00 14.65% 36 months debt_consolidation 15.29% WA MORTGAGE 6666.67 685-689 13.0 17521.0 0.0 5 years
23735 30000 29950.00 16.77% 60 months debt_consolidation 19.23% NY MORTGAGE 9250.00 705-709 15.0 45880.0 1.0 8 years
65882 16000 16000.00 14.09% 60 months home_improvement 21.54% MD OWN 8903.25 740-744 18.0 18898.0 1.0 10+ years
55610 10000 10000.00 13.99% 36 months debt_consolidation 4.89% PA MORTGAGE 2166.67 680-684 4.0 4544.0 0.0 10+ years
38576 6000 6000.00 12.42% 36 months major_purchase 16.66% NJ RENT 3500.00 675-679 8.0 7753.0 0.0 5 years
3116 9000 5242.75 13.79% 36 months debt_consolidation 6.76% NY RENT 3875.00 670-674 7.0 7589.0 0.0 10+ years

2500 rows × 14 columns

In [166]:
help(pd.read_excel)

Help on function read_excel in module pandas.io.excel:

read_excel(io, sheet_name=0, header=0, names=None, index_col=None, usecols=None, squeeze=False, dtype=None, engine=None, converters=None, true_values=None, false_values=None, skiprows=None, nrows=None, na_values=None, parse_dates=False, date_parser=None, thousands=None, comment=None, skipfooter=0, convert_float=True, **kwds)
    Read an Excel table into a pandas DataFrame
    
    Parameters
    ----------
    io : string, path object (pathlib.Path or py._path.local.LocalPath),
        file-like object, pandas ExcelFile, or xlrd workbook.
        The string could be a URL. Valid URL schemes include http, ftp, s3,
        and file. For file URLs, a host is expected. For instance, a local
        file could be file://localhost/path/to/workbook.xlsx
    sheet_name : string, int, mixed list of strings/ints, or None, default 0
    
        Strings are used for sheet names, Integers are used in zero-indexed
        sheet positions.
    
        Lists of strings/integers are used to request multiple sheets.
    
        Specify None to get all sheets.
    
        str|int -> DataFrame is returned.
        list|None -> Dict of DataFrames is returned, with keys representing
        sheets.
    
        Available Cases
    
        * Defaults to 0 -> 1st sheet as a DataFrame
        * 1 -> 2nd sheet as a DataFrame
        * "Sheet1" -> 1st sheet as a DataFrame
        * [0,1,"Sheet5"] -> 1st, 2nd & 5th sheet as a dictionary of DataFrames
        * None -> All sheets as a dictionary of DataFrames
    
    sheetname : string, int, mixed list of strings/ints, or None, default 0
    
        .. deprecated:: 0.21.0
           Use `sheet_name` instead
    
    header : int, list of ints, default 0
        Row (0-indexed) to use for the column labels of the parsed
        DataFrame. If a list of integers is passed those row positions will
        be combined into a ``MultiIndex``. Use None if there is no header.
    names : array-like, default None
        List of column names to use. If file contains no header row,
        then you should explicitly pass header=None
    index_col : int, list of ints, default None
        Column (0-indexed) to use as the row labels of the DataFrame.
        Pass None if there is no such column.  If a list is passed,
        those columns will be combined into a ``MultiIndex``.  If a
        subset of data is selected with ``usecols``, index_col
        is based on the subset.
    parse_cols : int or list, default None
    
        .. deprecated:: 0.21.0
           Pass in `usecols` instead.
    
    usecols : int or list, default None
        * If None then parse all columns,
        * If int then indicates last column to be parsed
        * If list of ints then indicates list of column numbers to be parsed
        * If string then indicates comma separated list of Excel column letters and
          column ranges (e.g. "A:E" or "A,C,E:F").  Ranges are inclusive of
          both sides.
    squeeze : boolean, default False
        If the parsed data only contains one column then return a Series
    dtype : Type name or dict of column -> type, default None
        Data type for data or columns. E.g. {'a': np.float64, 'b': np.int32}
        Use `object` to preserve data as stored in Excel and not interpret dtype.
        If converters are specified, they will be applied INSTEAD
        of dtype conversion.
    
        .. versionadded:: 0.20.0
    
    engine: string, default None
        If io is not a buffer or path, this must be set to identify io.
        Acceptable values are None or xlrd
    converters : dict, default None
        Dict of functions for converting values in certain columns. Keys can
        either be integers or column labels, values are functions that take one
        input argument, the Excel cell content, and return the transformed
        content.
    true_values : list, default None
        Values to consider as True
    
        .. versionadded:: 0.19.0
    
    false_values : list, default None
        Values to consider as False
    
        .. versionadded:: 0.19.0
    
    skiprows : list-like
        Rows to skip at the beginning (0-indexed)
    nrows : int, default None
        Number of rows to parse
    
        .. versionadded:: 0.23.0
    
    na_values : scalar, str, list-like, or dict, default None
        Additional strings to recognize as NA/NaN. If dict passed, specific
        per-column NA values. By default the following values are interpreted
        as NaN: '', '#N/A', '#N/A N/A', '#NA', '-1.#IND', '-1.#QNAN', '-NaN', '-nan',
        '1.#IND', '1.#QNAN', 'N/A', 'NA', 'NULL', 'NaN', 'n/a', 'nan',
        'null'.
    keep_default_na : bool, default True
        If na_values are specified and keep_default_na is False the default NaN
        values are overridden, otherwise they're appended to.
    verbose : boolean, default False
        Indicate number of NA values placed in non-numeric columns
    thousands : str, default None
        Thousands separator for parsing string columns to numeric.  Note that
        this parameter is only necessary for columns stored as TEXT in Excel,
        any numeric columns will automatically be parsed, regardless of display
        format.
    comment : str, default None
        Comments out remainder of line. Pass a character or characters to this
        argument to indicate comments in the input file. Any data between the
        comment string and the end of the current line is ignored.
    skip_footer : int, default 0
    
        .. deprecated:: 0.23.0
           Pass in `skipfooter` instead.
    skipfooter : int, default 0
        Rows at the end to skip (0-indexed)
    convert_float : boolean, default True
        convert integral floats to int (i.e., 1.0 --> 1). If False, all numeric
        data will be read in as floats: Excel stores all numbers as floats
        internally
    
    Returns
    -------
    parsed : DataFrame or Dict of DataFrames
        DataFrame from the passed in Excel file.  See notes in sheet_name
        argument for more information on when a Dict of Dataframes is returned.
    
    Examples
    --------
    
    An example DataFrame written to a local file
    
    >>> df_out = pd.DataFrame([('string1', 1),
    ...                        ('string2', 2),
    ...                        ('string3', 3)],
    ...                       columns=['Name', 'Value'])
    >>> df_out
          Name  Value
    0  string1      1
    1  string2      2
    2  string3      3
    >>> df_out.to_excel('tmp.xlsx')
    
    The file can be read using the file name as string or an open file object:
    
    >>> pd.read_excel('tmp.xlsx')
          Name  Value
    0  string1      1
    1  string2      2
    2  string3      3
    
    >>> pd.read_excel(open('tmp.xlsx','rb'))
          Name  Value
    0  string1      1
    1  string2      2
    2  string3      3
    
    Index and header can be specified via the `index_col` and `header` arguments
    
    >>> pd.read_excel('tmp.xlsx', index_col=None, header=None)
         0        1      2
    0  NaN     Name  Value
    1  0.0  string1      1
    2  1.0  string2      2
    3  2.0  string3      3
    
    Column types are inferred but can be explicitly specified
    
    >>> pd.read_excel('tmp.xlsx', dtype={'Name':str, 'Value':float})
          Name  Value
    0  string1    1.0
    1  string2    2.0
    2  string3    3.0
    
    True, False, and NA values, and thousands separators have defaults,
    but can be explicitly specified, too. Supply the values you would like
    as strings or lists of strings!
    
    >>> pd.read_excel('tmp.xlsx',
    ...               na_values=['string1', 'string2'])
          Name  Value
    0      NaN      1
    1      NaN      2
    2  string3      3
    
    Comment lines in the excel input file can be skipped using the `comment` kwarg
    
    >>> df = pd.DataFrame({'a': ['1', '#2'], 'b': ['2', '3']})
    >>> df.to_excel('tmp.xlsx', index=False)
    >>> pd.read_excel('tmp.xlsx')
        a  b
    0   1  2
    1  #2  3
    
    >>> pd.read_excel('tmp.xlsx', comment='#')
       a  b
    0  1  2
In [168]:
loansData.head(5)
Out[168]:
Amount.Requested Amount.Funded.By.Investors Interest.Rate Loan.Length Loan.Purpose Debt.To.Income.Ratio State Home.Ownership Monthly.Income FICO.Range Open.CREDIT.Lines Revolving.CREDIT.Balance Inquiries.in.the.Last.6.Months Employment.Length
81174 20000 20000.0 8.90% 36 months debt_consolidation 14.90% SC MORTGAGE 6541.67 735-739 14.0 14272.0 2.0 < 1 year
99592 19200 19200.0 12.12% 36 months debt_consolidation 28.36% TX MORTGAGE 4583.33 715-719 12.0 11140.0 1.0 2 years
80059 35000 35000.0 21.98% 60 months debt_consolidation 23.81% CA MORTGAGE 11500.00 690-694 14.0 21977.0 1.0 2 years
15825 10000 9975.0 9.99% 36 months debt_consolidation 14.30% KS MORTGAGE 3833.33 695-699 10.0 9346.0 0.0 5 years
33182 12000 12000.0 11.71% 36 months credit_card 18.78% NJ RENT 3195.00 695-699 11.0 14469.0 0.0 9 years
In [169]:
loansData.tail(5)
Out[169]:
Amount.Requested Amount.Funded.By.Investors Interest.Rate Loan.Length Loan.Purpose Debt.To.Income.Ratio State Home.Ownership Monthly.Income FICO.Range Open.CREDIT.Lines Revolving.CREDIT.Balance Inquiries.in.the.Last.6.Months Employment.Length
23735 30000 29950.00 16.77% 60 months debt_consolidation 19.23% NY MORTGAGE 9250.00 705-709 15.0 45880.0 1.0 8 years
65882 16000 16000.00 14.09% 60 months home_improvement 21.54% MD OWN 8903.25 740-744 18.0 18898.0 1.0 10+ years
55610 10000 10000.00 13.99% 36 months debt_consolidation 4.89% PA MORTGAGE 2166.67 680-684 4.0 4544.0 0.0 10+ years
38576 6000 6000.00 12.42% 36 months major_purchase 16.66% NJ RENT 3500.00 675-679 8.0 7753.0 0.0 5 years
3116 9000 5242.75 13.79% 36 months debt_consolidation 6.76% NY RENT 3875.00 670-674 7.0 7589.0 0.0 10+ years
In [170]:
loansData.describe()
Out[170]:
Amount.Requested Amount.Funded.By.Investors Monthly.Income Open.CREDIT.Lines Revolving.CREDIT.Balance Inquiries.in.the.Last.6.Months
count 2500.000000 2500.000000 2499.000000 2498.000000 2498.000000 2498.000000
mean 12406.500000 12001.573236 5688.931321 10.075661 15244.559648 0.906325
std 7801.544872 7745.320754 3963.118185 4.508644 18308.549795 1.231036
min 1000.000000 -0.010000 588.500000 2.000000 0.000000 0.000000
25% 6000.000000 6000.000000 3500.000000 7.000000 5585.750000 0.000000
50% 10000.000000 10000.000000 5000.000000 9.000000 10962.000000 0.000000
75% 17000.000000 16000.000000 6800.000000 13.000000 18888.750000 1.000000
max 35000.000000 35000.000000 102750.000000 38.000000 270800.000000 9.000000
In [171]:
hcl  = pd.read_csv("/home/vikrant/Downloads/18-03-2019-TO-16-04-2019HCLTECHALLN.csv")
In [172]:
infy = pd.read_csv("/home/vikrant/Downloads/18-03-2019-TO-16-04-2019INFYALLN.csv")
In [173]:
hcl
Out[173]:
Symbol Series Date Prev Close Open Price High Price Low Price Last Price Close Price Average Price Total Traded Quantity Turnover No. of Trades Deliverable Qty % Dly Qt to Traded Qty
0 HCLTECH EQ 18-Mar-2019 1029.30 1032.10 1039.95 1008.35 1015.70 1012.00 1018.66 1175027 1.196951e+09 62165 668156 56.86
1 HCLTECH EQ 19-Mar-2019 1012.00 1020.10 1042.00 1013.10 1040.75 1036.20 1026.58 1621276 1.664373e+09 71972 1038967 64.08
2 HCLTECH EQ 20-Mar-2019 1036.20 1042.00 1054.50 1031.50 1036.40 1037.95 1042.35 2204401 2.297748e+09 88784 1066063 48.36
3 HCLTECH EQ 22-Mar-2019 1037.95 1038.00 1048.00 1018.00 1030.45 1024.85 1028.34 2618545 2.692742e+09 110351 1849206 70.62
4 HCLTECH BL 25-Mar-2019 1420.00 1024.85 1024.85 1024.85 1024.85 1024.85 1024.85 186783 1.914246e+08 1 186783 100.00
5 HCLTECH EQ 25-Mar-2019 1024.85 1029.00 1029.00 1011.85 1017.70 1017.65 1017.35 1527955 1.554461e+09 50323 1057052 69.18
6 HCLTECH EQ 26-Mar-2019 1017.65 1013.35 1043.80 1011.50 1038.00 1034.60 1027.44 1655723 1.701149e+09 75011 1051791 63.52
7 HCLTECH EQ 27-Mar-2019 1034.60 1020.00 1056.35 1020.00 1042.50 1043.15 1043.39 1455845 1.519019e+09 65303 812220 55.79
8 HCLTECH EQ 28-Mar-2019 1043.15 1051.40 1088.35 1050.00 1085.00 1082.90 1077.27 4313807 4.647120e+09 121444 2316435 53.70
9 HCLTECH EQ 29-Mar-2019 1082.90 1090.00 1098.45 1067.65 1087.00 1087.45 1084.27 2827220 3.065474e+09 132698 1535638 54.32
10 HCLTECH EQ 01-Apr-2019 1087.45 1090.00 1120.00 1082.05 1110.00 1110.75 1109.12 2308865 2.560803e+09 87277 1203810 52.14
11 HCLTECH EQ 02-Apr-2019 1110.75 1110.00 1120.00 1092.50 1102.05 1102.60 1100.15 1393922 1.533523e+09 70655 670023 48.07
12 HCLTECH EQ 03-Apr-2019 1102.60 1102.60 1133.30 1091.55 1114.65 1120.60 1120.14 2648314 2.966495e+09 77938 1280139 48.34
13 HCLTECH EQ 04-Apr-2019 1120.60 1118.00 1118.05 1081.50 1090.35 1098.05 1094.16 4222104 4.619649e+09 153461 2467938 58.45
14 HCLTECH EQ 05-Apr-2019 1098.05 1090.35 1104.65 1085.50 1092.00 1092.80 1093.25 1704522 1.863477e+09 71310 982173 57.62
15 HCLTECH EQ 08-Apr-2019 1092.80 1095.00 1101.00 1080.95 1096.40 1096.35 1089.60 2013924 2.194381e+09 98317 1326487 65.87
16 HCLTECH EQ 09-Apr-2019 1096.35 1096.35 1128.85 1096.20 1118.00 1118.15 1118.64 3250491 3.636118e+09 117833 1665620 51.24
17 HCLTECH EQ 10-Apr-2019 1118.15 1120.00 1125.30 1096.20 1098.75 1098.70 1104.69 2413433 2.666090e+09 79204 1536537 63.67
18 HCLTECH EQ 11-Apr-2019 1098.70 1092.30 1101.10 1085.00 1088.90 1088.85 1092.36 1517790 1.657969e+09 112162 1040493 68.55
19 HCLTECH EQ 12-Apr-2019 1088.85 1088.80 1100.85 1076.60 1082.70 1083.45 1084.56 1419742 1.539789e+09 57359 758481 53.42
20 HCLTECH EQ 15-Apr-2019 1083.45 1089.00 1108.00 1085.00 1102.00 1103.25 1101.44 1934151 2.130355e+09 57781 1171051 60.55
21 HCLTECH EQ 16-Apr-2019 1103.25 1109.50 1116.25 1102.35 1103.00 1107.05 1108.50 1200591 1.330851e+09 55163 592041 49.31
In [176]:
hcl[hcl.Series=="EQ"]
Out[176]:
Symbol Series Date Prev Close Open Price High Price Low Price Last Price Close Price Average Price Total Traded Quantity Turnover No. of Trades Deliverable Qty % Dly Qt to Traded Qty
0 HCLTECH EQ 18-Mar-2019 1029.30 1032.10 1039.95 1008.35 1015.70 1012.00 1018.66 1175027 1.196951e+09 62165 668156 56.86
1 HCLTECH EQ 19-Mar-2019 1012.00 1020.10 1042.00 1013.10 1040.75 1036.20 1026.58 1621276 1.664373e+09 71972 1038967 64.08
2 HCLTECH EQ 20-Mar-2019 1036.20 1042.00 1054.50 1031.50 1036.40 1037.95 1042.35 2204401 2.297748e+09 88784 1066063 48.36
3 HCLTECH EQ 22-Mar-2019 1037.95 1038.00 1048.00 1018.00 1030.45 1024.85 1028.34 2618545 2.692742e+09 110351 1849206 70.62
5 HCLTECH EQ 25-Mar-2019 1024.85 1029.00 1029.00 1011.85 1017.70 1017.65 1017.35 1527955 1.554461e+09 50323 1057052 69.18
6 HCLTECH EQ 26-Mar-2019 1017.65 1013.35 1043.80 1011.50 1038.00 1034.60 1027.44 1655723 1.701149e+09 75011 1051791 63.52
7 HCLTECH EQ 27-Mar-2019 1034.60 1020.00 1056.35 1020.00 1042.50 1043.15 1043.39 1455845 1.519019e+09 65303 812220 55.79
8 HCLTECH EQ 28-Mar-2019 1043.15 1051.40 1088.35 1050.00 1085.00 1082.90 1077.27 4313807 4.647120e+09 121444 2316435 53.70
9 HCLTECH EQ 29-Mar-2019 1082.90 1090.00 1098.45 1067.65 1087.00 1087.45 1084.27 2827220 3.065474e+09 132698 1535638 54.32
10 HCLTECH EQ 01-Apr-2019 1087.45 1090.00 1120.00 1082.05 1110.00 1110.75 1109.12 2308865 2.560803e+09 87277 1203810 52.14
11 HCLTECH EQ 02-Apr-2019 1110.75 1110.00 1120.00 1092.50 1102.05 1102.60 1100.15 1393922 1.533523e+09 70655 670023 48.07
12 HCLTECH EQ 03-Apr-2019 1102.60 1102.60 1133.30 1091.55 1114.65 1120.60 1120.14 2648314 2.966495e+09 77938 1280139 48.34
13 HCLTECH EQ 04-Apr-2019 1120.60 1118.00 1118.05 1081.50 1090.35 1098.05 1094.16 4222104 4.619649e+09 153461 2467938 58.45
14 HCLTECH EQ 05-Apr-2019 1098.05 1090.35 1104.65 1085.50 1092.00 1092.80 1093.25 1704522 1.863477e+09 71310 982173 57.62
15 HCLTECH EQ 08-Apr-2019 1092.80 1095.00 1101.00 1080.95 1096.40 1096.35 1089.60 2013924 2.194381e+09 98317 1326487 65.87
16 HCLTECH EQ 09-Apr-2019 1096.35 1096.35 1128.85 1096.20 1118.00 1118.15 1118.64 3250491 3.636118e+09 117833 1665620 51.24
17 HCLTECH EQ 10-Apr-2019 1118.15 1120.00 1125.30 1096.20 1098.75 1098.70 1104.69 2413433 2.666090e+09 79204 1536537 63.67
18 HCLTECH EQ 11-Apr-2019 1098.70 1092.30 1101.10 1085.00 1088.90 1088.85 1092.36 1517790 1.657969e+09 112162 1040493 68.55
19 HCLTECH EQ 12-Apr-2019 1088.85 1088.80 1100.85 1076.60 1082.70 1083.45 1084.56 1419742 1.539789e+09 57359 758481 53.42
20 HCLTECH EQ 15-Apr-2019 1083.45 1089.00 1108.00 1085.00 1102.00 1103.25 1101.44 1934151 2.130355e+09 57781 1171051 60.55
21 HCLTECH EQ 16-Apr-2019 1103.25 1109.50 1116.25 1102.35 1103.00 1107.05 1108.50 1200591 1.330851e+09 55163 592041 49.31
In [177]:
 hceq = hcl[hcl.Series=="EQ"]
In [181]:
date = pd.to_datetime(hceq.Date)
In [182]:
hceq.index = date
In [183]:
hceq
Out[183]:
Symbol Series Date Prev Close Open Price High Price Low Price Last Price Close Price Average Price Total Traded Quantity Turnover No. of Trades Deliverable Qty % Dly Qt to Traded Qty
Date
2019-03-18 HCLTECH EQ 18-Mar-2019 1029.30 1032.10 1039.95 1008.35 1015.70 1012.00 1018.66 1175027 1.196951e+09 62165 668156 56.86
2019-03-19 HCLTECH EQ 19-Mar-2019 1012.00 1020.10 1042.00 1013.10 1040.75 1036.20 1026.58 1621276 1.664373e+09 71972 1038967 64.08
2019-03-20 HCLTECH EQ 20-Mar-2019 1036.20 1042.00 1054.50 1031.50 1036.40 1037.95 1042.35 2204401 2.297748e+09 88784 1066063 48.36
2019-03-22 HCLTECH EQ 22-Mar-2019 1037.95 1038.00 1048.00 1018.00 1030.45 1024.85 1028.34 2618545 2.692742e+09 110351 1849206 70.62
2019-03-25 HCLTECH EQ 25-Mar-2019 1024.85 1029.00 1029.00 1011.85 1017.70 1017.65 1017.35 1527955 1.554461e+09 50323 1057052 69.18
2019-03-26 HCLTECH EQ 26-Mar-2019 1017.65 1013.35 1043.80 1011.50 1038.00 1034.60 1027.44 1655723 1.701149e+09 75011 1051791 63.52
2019-03-27 HCLTECH EQ 27-Mar-2019 1034.60 1020.00 1056.35 1020.00 1042.50 1043.15 1043.39 1455845 1.519019e+09 65303 812220 55.79
2019-03-28 HCLTECH EQ 28-Mar-2019 1043.15 1051.40 1088.35 1050.00 1085.00 1082.90 1077.27 4313807 4.647120e+09 121444 2316435 53.70
2019-03-29 HCLTECH EQ 29-Mar-2019 1082.90 1090.00 1098.45 1067.65 1087.00 1087.45 1084.27 2827220 3.065474e+09 132698 1535638 54.32
2019-04-01 HCLTECH EQ 01-Apr-2019 1087.45 1090.00 1120.00 1082.05 1110.00 1110.75 1109.12 2308865 2.560803e+09 87277 1203810 52.14
2019-04-02 HCLTECH EQ 02-Apr-2019 1110.75 1110.00 1120.00 1092.50 1102.05 1102.60 1100.15 1393922 1.533523e+09 70655 670023 48.07
2019-04-03 HCLTECH EQ 03-Apr-2019 1102.60 1102.60 1133.30 1091.55 1114.65 1120.60 1120.14 2648314 2.966495e+09 77938 1280139 48.34
2019-04-04 HCLTECH EQ 04-Apr-2019 1120.60 1118.00 1118.05 1081.50 1090.35 1098.05 1094.16 4222104 4.619649e+09 153461 2467938 58.45
2019-04-05 HCLTECH EQ 05-Apr-2019 1098.05 1090.35 1104.65 1085.50 1092.00 1092.80 1093.25 1704522 1.863477e+09 71310 982173 57.62
2019-04-08 HCLTECH EQ 08-Apr-2019 1092.80 1095.00 1101.00 1080.95 1096.40 1096.35 1089.60 2013924 2.194381e+09 98317 1326487 65.87
2019-04-09 HCLTECH EQ 09-Apr-2019 1096.35 1096.35 1128.85 1096.20 1118.00 1118.15 1118.64 3250491 3.636118e+09 117833 1665620 51.24
2019-04-10 HCLTECH EQ 10-Apr-2019 1118.15 1120.00 1125.30 1096.20 1098.75 1098.70 1104.69 2413433 2.666090e+09 79204 1536537 63.67
2019-04-11 HCLTECH EQ 11-Apr-2019 1098.70 1092.30 1101.10 1085.00 1088.90 1088.85 1092.36 1517790 1.657969e+09 112162 1040493 68.55
2019-04-12 HCLTECH EQ 12-Apr-2019 1088.85 1088.80 1100.85 1076.60 1082.70 1083.45 1084.56 1419742 1.539789e+09 57359 758481 53.42
2019-04-15 HCLTECH EQ 15-Apr-2019 1083.45 1089.00 1108.00 1085.00 1102.00 1103.25 1101.44 1934151 2.130355e+09 57781 1171051 60.55
2019-04-16 HCLTECH EQ 16-Apr-2019 1103.25 1109.50 1116.25 1102.35 1103.00 1107.05 1108.50 1200591 1.330851e+09 55163 592041 49.31
In [185]:
hceq['Date'] = pd.to_datetime(hceq.Date)

/home/vikrant/anaconda3/lib/python3.7/site-packages/ipykernel_launcher.py:1: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy
  """Entry point for launching an IPython kernel.
In [186]:
hceq
Out[186]:
Symbol Series Date Prev Close Open Price High Price Low Price Last Price Close Price Average Price Total Traded Quantity Turnover No. of Trades Deliverable Qty % Dly Qt to Traded Qty
Date
2019-03-18 HCLTECH EQ 2019-03-18 1029.30 1032.10 1039.95 1008.35 1015.70 1012.00 1018.66 1175027 1.196951e+09 62165 668156 56.86
2019-03-19 HCLTECH EQ 2019-03-19 1012.00 1020.10 1042.00 1013.10 1040.75 1036.20 1026.58 1621276 1.664373e+09 71972 1038967 64.08
2019-03-20 HCLTECH EQ 2019-03-20 1036.20 1042.00 1054.50 1031.50 1036.40 1037.95 1042.35 2204401 2.297748e+09 88784 1066063 48.36
2019-03-22 HCLTECH EQ 2019-03-22 1037.95 1038.00 1048.00 1018.00 1030.45 1024.85 1028.34 2618545 2.692742e+09 110351 1849206 70.62
2019-03-25 HCLTECH EQ 2019-03-25 1024.85 1029.00 1029.00 1011.85 1017.70 1017.65 1017.35 1527955 1.554461e+09 50323 1057052 69.18
2019-03-26 HCLTECH EQ 2019-03-26 1017.65 1013.35 1043.80 1011.50 1038.00 1034.60 1027.44 1655723 1.701149e+09 75011 1051791 63.52
2019-03-27 HCLTECH EQ 2019-03-27 1034.60 1020.00 1056.35 1020.00 1042.50 1043.15 1043.39 1455845 1.519019e+09 65303 812220 55.79
2019-03-28 HCLTECH EQ 2019-03-28 1043.15 1051.40 1088.35 1050.00 1085.00 1082.90 1077.27 4313807 4.647120e+09 121444 2316435 53.70
2019-03-29 HCLTECH EQ 2019-03-29 1082.90 1090.00 1098.45 1067.65 1087.00 1087.45 1084.27 2827220 3.065474e+09 132698 1535638 54.32
2019-04-01 HCLTECH EQ 2019-04-01 1087.45 1090.00 1120.00 1082.05 1110.00 1110.75 1109.12 2308865 2.560803e+09 87277 1203810 52.14
2019-04-02 HCLTECH EQ 2019-04-02 1110.75 1110.00 1120.00 1092.50 1102.05 1102.60 1100.15 1393922 1.533523e+09 70655 670023 48.07
2019-04-03 HCLTECH EQ 2019-04-03 1102.60 1102.60 1133.30 1091.55 1114.65 1120.60 1120.14 2648314 2.966495e+09 77938 1280139 48.34
2019-04-04 HCLTECH EQ 2019-04-04 1120.60 1118.00 1118.05 1081.50 1090.35 1098.05 1094.16 4222104 4.619649e+09 153461 2467938 58.45
2019-04-05 HCLTECH EQ 2019-04-05 1098.05 1090.35 1104.65 1085.50 1092.00 1092.80 1093.25 1704522 1.863477e+09 71310 982173 57.62
2019-04-08 HCLTECH EQ 2019-04-08 1092.80 1095.00 1101.00 1080.95 1096.40 1096.35 1089.60 2013924 2.194381e+09 98317 1326487 65.87
2019-04-09 HCLTECH EQ 2019-04-09 1096.35 1096.35 1128.85 1096.20 1118.00 1118.15 1118.64 3250491 3.636118e+09 117833 1665620 51.24
2019-04-10 HCLTECH EQ 2019-04-10 1118.15 1120.00 1125.30 1096.20 1098.75 1098.70 1104.69 2413433 2.666090e+09 79204 1536537 63.67
2019-04-11 HCLTECH EQ 2019-04-11 1098.70 1092.30 1101.10 1085.00 1088.90 1088.85 1092.36 1517790 1.657969e+09 112162 1040493 68.55
2019-04-12 HCLTECH EQ 2019-04-12 1088.85 1088.80 1100.85 1076.60 1082.70 1083.45 1084.56 1419742 1.539789e+09 57359 758481 53.42
2019-04-15 HCLTECH EQ 2019-04-15 1083.45 1089.00 1108.00 1085.00 1102.00 1103.25 1101.44 1934151 2.130355e+09 57781 1171051 60.55
2019-04-16 HCLTECH EQ 2019-04-16 1103.25 1109.50 1116.25 1102.35 1103.00 1107.05 1108.50 1200591 1.330851e+09 55163 592041 49.31
In [188]:
d1 = pd.to_datetime(hceq.Date)
In [189]:
del hceq['Date']
In [190]:
hceq['Date'] = d1

/home/vikrant/anaconda3/lib/python3.7/site-packages/ipykernel_launcher.py:1: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy
  """Entry point for launching an IPython kernel.
In [191]:
hcl
Out[191]:
Symbol Series Date Prev Close Open Price High Price Low Price Last Price Close Price Average Price Total Traded Quantity Turnover No. of Trades Deliverable Qty % Dly Qt to Traded Qty
0 HCLTECH EQ 18-Mar-2019 1029.30 1032.10 1039.95 1008.35 1015.70 1012.00 1018.66 1175027 1.196951e+09 62165 668156 56.86
1 HCLTECH EQ 19-Mar-2019 1012.00 1020.10 1042.00 1013.10 1040.75 1036.20 1026.58 1621276 1.664373e+09 71972 1038967 64.08
2 HCLTECH EQ 20-Mar-2019 1036.20 1042.00 1054.50 1031.50 1036.40 1037.95 1042.35 2204401 2.297748e+09 88784 1066063 48.36
3 HCLTECH EQ 22-Mar-2019 1037.95 1038.00 1048.00 1018.00 1030.45 1024.85 1028.34 2618545 2.692742e+09 110351 1849206 70.62
4 HCLTECH BL 25-Mar-2019 1420.00 1024.85 1024.85 1024.85 1024.85 1024.85 1024.85 186783 1.914246e+08 1 186783 100.00
5 HCLTECH EQ 25-Mar-2019 1024.85 1029.00 1029.00 1011.85 1017.70 1017.65 1017.35 1527955 1.554461e+09 50323 1057052 69.18
6 HCLTECH EQ 26-Mar-2019 1017.65 1013.35 1043.80 1011.50 1038.00 1034.60 1027.44 1655723 1.701149e+09 75011 1051791 63.52
7 HCLTECH EQ 27-Mar-2019 1034.60 1020.00 1056.35 1020.00 1042.50 1043.15 1043.39 1455845 1.519019e+09 65303 812220 55.79
8 HCLTECH EQ 28-Mar-2019 1043.15 1051.40 1088.35 1050.00 1085.00 1082.90 1077.27 4313807 4.647120e+09 121444 2316435 53.70
9 HCLTECH EQ 29-Mar-2019 1082.90 1090.00 1098.45 1067.65 1087.00 1087.45 1084.27 2827220 3.065474e+09 132698 1535638 54.32
10 HCLTECH EQ 01-Apr-2019 1087.45 1090.00 1120.00 1082.05 1110.00 1110.75 1109.12 2308865 2.560803e+09 87277 1203810 52.14
11 HCLTECH EQ 02-Apr-2019 1110.75 1110.00 1120.00 1092.50 1102.05 1102.60 1100.15 1393922 1.533523e+09 70655 670023 48.07
12 HCLTECH EQ 03-Apr-2019 1102.60 1102.60 1133.30 1091.55 1114.65 1120.60 1120.14 2648314 2.966495e+09 77938 1280139 48.34
13 HCLTECH EQ 04-Apr-2019 1120.60 1118.00 1118.05 1081.50 1090.35 1098.05 1094.16 4222104 4.619649e+09 153461 2467938 58.45
14 HCLTECH EQ 05-Apr-2019 1098.05 1090.35 1104.65 1085.50 1092.00 1092.80 1093.25 1704522 1.863477e+09 71310 982173 57.62
15 HCLTECH EQ 08-Apr-2019 1092.80 1095.00 1101.00 1080.95 1096.40 1096.35 1089.60 2013924 2.194381e+09 98317 1326487 65.87
16 HCLTECH EQ 09-Apr-2019 1096.35 1096.35 1128.85 1096.20 1118.00 1118.15 1118.64 3250491 3.636118e+09 117833 1665620 51.24
17 HCLTECH EQ 10-Apr-2019 1118.15 1120.00 1125.30 1096.20 1098.75 1098.70 1104.69 2413433 2.666090e+09 79204 1536537 63.67
18 HCLTECH EQ 11-Apr-2019 1098.70 1092.30 1101.10 1085.00 1088.90 1088.85 1092.36 1517790 1.657969e+09 112162 1040493 68.55
19 HCLTECH EQ 12-Apr-2019 1088.85 1088.80 1100.85 1076.60 1082.70 1083.45 1084.56 1419742 1.539789e+09 57359 758481 53.42
20 HCLTECH EQ 15-Apr-2019 1083.45 1089.00 1108.00 1085.00 1102.00 1103.25 1101.44 1934151 2.130355e+09 57781 1171051 60.55
21 HCLTECH EQ 16-Apr-2019 1103.25 1109.50 1116.25 1102.35 1103.00 1107.05 1108.50 1200591 1.330851e+09 55163 592041 49.31
In [192]:
hceq
Out[192]:
Symbol Series Prev Close Open Price High Price Low Price Last Price Close Price Average Price Total Traded Quantity Turnover No. of Trades Deliverable Qty % Dly Qt to Traded Qty Date
Date
2019-03-18 HCLTECH EQ 1029.30 1032.10 1039.95 1008.35 1015.70 1012.00 1018.66 1175027 1.196951e+09 62165 668156 56.86 2019-03-18
2019-03-19 HCLTECH EQ 1012.00 1020.10 1042.00 1013.10 1040.75 1036.20 1026.58 1621276 1.664373e+09 71972 1038967 64.08 2019-03-19
2019-03-20 HCLTECH EQ 1036.20 1042.00 1054.50 1031.50 1036.40 1037.95 1042.35 2204401 2.297748e+09 88784 1066063 48.36 2019-03-20
2019-03-22 HCLTECH EQ 1037.95 1038.00 1048.00 1018.00 1030.45 1024.85 1028.34 2618545 2.692742e+09 110351 1849206 70.62 2019-03-22
2019-03-25 HCLTECH EQ 1024.85 1029.00 1029.00 1011.85 1017.70 1017.65 1017.35 1527955 1.554461e+09 50323 1057052 69.18 2019-03-25
2019-03-26 HCLTECH EQ 1017.65 1013.35 1043.80 1011.50 1038.00 1034.60 1027.44 1655723 1.701149e+09 75011 1051791 63.52 2019-03-26
2019-03-27 HCLTECH EQ 1034.60 1020.00 1056.35 1020.00 1042.50 1043.15 1043.39 1455845 1.519019e+09 65303 812220 55.79 2019-03-27
2019-03-28 HCLTECH EQ 1043.15 1051.40 1088.35 1050.00 1085.00 1082.90 1077.27 4313807 4.647120e+09 121444 2316435 53.70 2019-03-28
2019-03-29 HCLTECH EQ 1082.90 1090.00 1098.45 1067.65 1087.00 1087.45 1084.27 2827220 3.065474e+09 132698 1535638 54.32 2019-03-29
2019-04-01 HCLTECH EQ 1087.45 1090.00 1120.00 1082.05 1110.00 1110.75 1109.12 2308865 2.560803e+09 87277 1203810 52.14 2019-04-01
2019-04-02 HCLTECH EQ 1110.75 1110.00 1120.00 1092.50 1102.05 1102.60 1100.15 1393922 1.533523e+09 70655 670023 48.07 2019-04-02
2019-04-03 HCLTECH EQ 1102.60 1102.60 1133.30 1091.55 1114.65 1120.60 1120.14 2648314 2.966495e+09 77938 1280139 48.34 2019-04-03
2019-04-04 HCLTECH EQ 1120.60 1118.00 1118.05 1081.50 1090.35 1098.05 1094.16 4222104 4.619649e+09 153461 2467938 58.45 2019-04-04
2019-04-05 HCLTECH EQ 1098.05 1090.35 1104.65 1085.50 1092.00 1092.80 1093.25 1704522 1.863477e+09 71310 982173 57.62 2019-04-05
2019-04-08 HCLTECH EQ 1092.80 1095.00 1101.00 1080.95 1096.40 1096.35 1089.60 2013924 2.194381e+09 98317 1326487 65.87 2019-04-08
2019-04-09 HCLTECH EQ 1096.35 1096.35 1128.85 1096.20 1118.00 1118.15 1118.64 3250491 3.636118e+09 117833 1665620 51.24 2019-04-09
2019-04-10 HCLTECH EQ 1118.15 1120.00 1125.30 1096.20 1098.75 1098.70 1104.69 2413433 2.666090e+09 79204 1536537 63.67 2019-04-10
2019-04-11 HCLTECH EQ 1098.70 1092.30 1101.10 1085.00 1088.90 1088.85 1092.36 1517790 1.657969e+09 112162 1040493 68.55 2019-04-11
2019-04-12 HCLTECH EQ 1088.85 1088.80 1100.85 1076.60 1082.70 1083.45 1084.56 1419742 1.539789e+09 57359 758481 53.42 2019-04-12
2019-04-15 HCLTECH EQ 1083.45 1089.00 1108.00 1085.00 1102.00 1103.25 1101.44 1934151 2.130355e+09 57781 1171051 60.55 2019-04-15
2019-04-16 HCLTECH EQ 1103.25 1109.50 1116.25 1102.35 1103.00 1107.05 1108.50 1200591 1.330851e+09 55163 592041 49.31 2019-04-16
In [195]:
laonsData.columns
Out[195]:
Index(['Amount.Requested', 'Amount.Funded.By.Investors', 'Interest.Rate',
       'Loan.Length', 'Loan.Purpose', 'Debt.To.Income.Ratio', 'State',
       'Home.Ownership', 'Monthly.Income', 'FICO.Range', 'Open.CREDIT.Lines',
       'Revolving.CREDIT.Balance', 'Inquiries.in.the.Last.6.Months',
       'Employment.Length'],
      dtype='object')
In [205]:
colnames = {name:name.replace(".", "_") for name in loansData.columns}
In [206]:
colnames
Out[206]:
{'Amount.Requested': 'Amount_Requested',
 'Amount.Funded.By.Investors': 'Amount_Funded_By_Investors',
 'Interest.Rate': 'Interest_Rate',
 'Loan.Length': 'Loan_Length',
 'Loan.Purpose': 'Loan_Purpose',
 'Debt.To.Income.Ratio': 'Debt_To_Income_Ratio',
 'State': 'State',
 'Home.Ownership': 'Home_Ownership',
 'Monthly.Income': 'Monthly_Income',
 'FICO.Range': 'FICO_Range',
 'Open.CREDIT.Lines': 'Open_CREDIT_Lines',
 'Revolving.CREDIT.Balance': 'Revolving_CREDIT_Balance',
 'Inquiries.in.the.Last.6.Months': 'Inquiries_in_the_Last_6_Months',
 'Employment.Length': 'Employment_Length'}
In [214]:
In [223]:
loansData.rename(columns=colnames, inplace=True)
In [224]:
loansData.describe()
Out[224]:
Amount_Requested Amount_Funded_By_Investors Monthly_Income Open_CREDIT_Lines Revolving_CREDIT_Balance Inquiries_in_the_Last_6_Months
count 2500.000000 2500.000000 2499.000000 2498.000000 2498.000000 2498.000000
mean 12406.500000 12001.573236 5688.931321 10.075661 15244.559648 0.906325
std 7801.544872 7745.320754 3963.118185 4.508644 18308.549795 1.231036
min 1000.000000 -0.010000 588.500000 2.000000 0.000000 0.000000
25% 6000.000000 6000.000000 3500.000000 7.000000 5585.750000 0.000000
50% 10000.000000 10000.000000 5000.000000 9.000000 10962.000000 0.000000
75% 17000.000000 16000.000000 6800.000000 13.000000 18888.750000 1.000000
max 35000.000000 35000.000000 102750.000000 38.000000 270800.000000 9.000000
In [225]:
loansData.Interest_Rate
Out[225]:
81174      8.90%
99592     12.12%
80059     21.98%
15825      9.99%
33182     11.71%
62403     15.31%
48808      7.90%
22090     17.14%
76404     14.33%
15867      6.91%
94971     19.72%
36911     14.27%
41200     21.67%
83869      8.90%
53853      7.62%
21399     15.65%
62127     12.12%
23446     10.37%
44987      9.76%
17977      9.99%
86099     21.98%
99483     19.05%
28798     17.99%
24168     11.99%
10356     16.82%
46027      7.90%
2238      14.42%
65278     15.31%
4227       8.59%
50182      7.90%
           ...  
84265     22.95%
80231      7.90%
49533     22.45%
102514    15.13%
78618     18.75%
86953     14.09%
80129     14.09%
85216      8.90%
38247     11.71%
91245     15.80%
53041      6.03%
63051      6.62%
14446      7.51%
68628     14.33%
98758     10.16%
13070     10.75%
45836     17.27%
52330     19.99%
48243     15.81%
63256     18.75%
42124     11.71%
78043      7.62%
925       10.08%
74047     23.28%
49957     14.65%
23735     16.77%
65882     14.09%
55610     13.99%
38576     12.42%
3116      13.79%
Name: Interest_Rate, Length: 2500, dtype: object

string operations

In [227]:
int_rate = loansData.Interest_Rate.str.replace("%", "",)
In [228]:
int_rate = pd.to_numeric(int_rate)
In [229]:
int_rate
Out[229]:
81174      8.90
99592     12.12
80059     21.98
15825      9.99
33182     11.71
62403     15.31
48808      7.90
22090     17.14
76404     14.33
15867      6.91
94971     19.72
36911     14.27
41200     21.67
83869      8.90
53853      7.62
21399     15.65
62127     12.12
23446     10.37
44987      9.76
17977      9.99
86099     21.98
99483     19.05
28798     17.99
24168     11.99
10356     16.82
46027      7.90
2238      14.42
65278     15.31
4227       8.59
50182      7.90
          ...  
84265     22.95
80231      7.90
49533     22.45
102514    15.13
78618     18.75
86953     14.09
80129     14.09
85216      8.90
38247     11.71
91245     15.80
53041      6.03
63051      6.62
14446      7.51
68628     14.33
98758     10.16
13070     10.75
45836     17.27
52330     19.99
48243     15.81
63256     18.75
42124     11.71
78043      7.62
925       10.08
74047     23.28
49957     14.65
23735     16.77
65882     14.09
55610     13.99
38576     12.42
3116      13.79
Name: Interest_Rate, Length: 2500, dtype: float64
In [230]:
int_rate = int_rate/100
In [231]:
int_rate
Out[231]:
81174     0.0890
99592     0.1212
80059     0.2198
15825     0.0999
33182     0.1171
62403     0.1531
48808     0.0790
22090     0.1714
76404     0.1433
15867     0.0691
94971     0.1972
36911     0.1427
41200     0.2167
83869     0.0890
53853     0.0762
21399     0.1565
62127     0.1212
23446     0.1037
44987     0.0976
17977     0.0999
86099     0.2198
99483     0.1905
28798     0.1799
24168     0.1199
10356     0.1682
46027     0.0790
2238      0.1442
65278     0.1531
4227      0.0859
50182     0.0790
           ...  
84265     0.2295
80231     0.0790
49533     0.2245
102514    0.1513
78618     0.1875
86953     0.1409
80129     0.1409
85216     0.0890
38247     0.1171
91245     0.1580
53041     0.0603
63051     0.0662
14446     0.0751
68628     0.1433
98758     0.1016
13070     0.1075
45836     0.1727
52330     0.1999
48243     0.1581
63256     0.1875
42124     0.1171
78043     0.0762
925       0.1008
74047     0.2328
49957     0.1465
23735     0.1677
65882     0.1409
55610     0.1399
38576     0.1242
3116      0.1379
Name: Interest_Rate, Length: 2500, dtype: float64
In [232]:
loansData['Interest_Rate'] = int_rate
In [233]:
loansData.describe()
Out[233]:
Amount_Requested Amount_Funded_By_Investors Interest_Rate Monthly_Income Open_CREDIT_Lines Revolving_CREDIT_Balance Inquiries_in_the_Last_6_Months
count 2500.000000 2500.000000 2500.000000 2499.000000 2498.000000 2498.000000 2498.000000
mean 12406.500000 12001.573236 0.130670 5688.931321 10.075661 15244.559648 0.906325
std 7801.544872 7745.320754 0.041782 3963.118185 4.508644 18308.549795 1.231036
min 1000.000000 -0.010000 0.054200 588.500000 2.000000 0.000000 0.000000
25% 6000.000000 6000.000000 0.101600 3500.000000 7.000000 5585.750000 0.000000
50% 10000.000000 10000.000000 0.131100 5000.000000 9.000000 10962.000000 0.000000
75% 17000.000000 16000.000000 0.158000 6800.000000 13.000000 18888.750000 1.000000
max 35000.000000 35000.000000 0.248900 102750.000000 38.000000 270800.000000 9.000000
In [234]:
loansData.head()
Out[234]:
Amount_Requested Amount_Funded_By_Investors Interest_Rate Loan_Length Loan_Purpose Debt_To_Income_Ratio State Home_Ownership Monthly_Income FICO_Range Open_CREDIT_Lines Revolving_CREDIT_Balance Inquiries_in_the_Last_6_Months Employment_Length
81174 20000 20000.0 0.0890 36 months debt_consolidation 14.90% SC MORTGAGE 6541.67 735-739 14.0 14272.0 2.0 < 1 year
99592 19200 19200.0 0.1212 36 months debt_consolidation 28.36% TX MORTGAGE 4583.33 715-719 12.0 11140.0 1.0 2 years
80059 35000 35000.0 0.2198 60 months debt_consolidation 23.81% CA MORTGAGE 11500.00 690-694 14.0 21977.0 1.0 2 years
15825 10000 9975.0 0.0999 36 months debt_consolidation 14.30% KS MORTGAGE 3833.33 695-699 10.0 9346.0 0.0 5 years
33182 12000 12000.0 0.1171 36 months credit_card 18.78% NJ RENT 3195.00 695-699 11.0 14469.0 0.0 9 years
In [237]:
Out[237]:
Int64Index([81174, 99592, 80059, 15825, 33182, 62403, 48808, 22090, 76404,
            15867,
            ...
            42124, 78043,   925, 74047, 49957, 23735, 65882, 55610, 38576,
             3116],
           dtype='int64', length=2500)
In [238]:
hcl
Out[238]:
Symbol Series Date Prev Close Open Price High Price Low Price Last Price Close Price Average Price Total Traded Quantity Turnover No. of Trades Deliverable Qty % Dly Qt to Traded Qty
0 HCLTECH EQ 18-Mar-2019 1029.30 1032.10 1039.95 1008.35 1015.70 1012.00 1018.66 1175027 1.196951e+09 62165 668156 56.86
1 HCLTECH EQ 19-Mar-2019 1012.00 1020.10 1042.00 1013.10 1040.75 1036.20 1026.58 1621276 1.664373e+09 71972 1038967 64.08
2 HCLTECH EQ 20-Mar-2019 1036.20 1042.00 1054.50 1031.50 1036.40 1037.95 1042.35 2204401 2.297748e+09 88784 1066063 48.36
3 HCLTECH EQ 22-Mar-2019 1037.95 1038.00 1048.00 1018.00 1030.45 1024.85 1028.34 2618545 2.692742e+09 110351 1849206 70.62
4 HCLTECH BL 25-Mar-2019 1420.00 1024.85 1024.85 1024.85 1024.85 1024.85 1024.85 186783 1.914246e+08 1 186783 100.00
5 HCLTECH EQ 25-Mar-2019 1024.85 1029.00 1029.00 1011.85 1017.70 1017.65 1017.35 1527955 1.554461e+09 50323 1057052 69.18
6 HCLTECH EQ 26-Mar-2019 1017.65 1013.35 1043.80 1011.50 1038.00 1034.60 1027.44 1655723 1.701149e+09 75011 1051791 63.52
7 HCLTECH EQ 27-Mar-2019 1034.60 1020.00 1056.35 1020.00 1042.50 1043.15 1043.39 1455845 1.519019e+09 65303 812220 55.79
8 HCLTECH EQ 28-Mar-2019 1043.15 1051.40 1088.35 1050.00 1085.00 1082.90 1077.27 4313807 4.647120e+09 121444 2316435 53.70
9 HCLTECH EQ 29-Mar-2019 1082.90 1090.00 1098.45 1067.65 1087.00 1087.45 1084.27 2827220 3.065474e+09 132698 1535638 54.32
10 HCLTECH EQ 01-Apr-2019 1087.45 1090.00 1120.00 1082.05 1110.00 1110.75 1109.12 2308865 2.560803e+09 87277 1203810 52.14
11 HCLTECH EQ 02-Apr-2019 1110.75 1110.00 1120.00 1092.50 1102.05 1102.60 1100.15 1393922 1.533523e+09 70655 670023 48.07
12 HCLTECH EQ 03-Apr-2019 1102.60 1102.60 1133.30 1091.55 1114.65 1120.60 1120.14 2648314 2.966495e+09 77938 1280139 48.34
13 HCLTECH EQ 04-Apr-2019 1120.60 1118.00 1118.05 1081.50 1090.35 1098.05 1094.16 4222104 4.619649e+09 153461 2467938 58.45
14 HCLTECH EQ 05-Apr-2019 1098.05 1090.35 1104.65 1085.50 1092.00 1092.80 1093.25 1704522 1.863477e+09 71310 982173 57.62
15 HCLTECH EQ 08-Apr-2019 1092.80 1095.00 1101.00 1080.95 1096.40 1096.35 1089.60 2013924 2.194381e+09 98317 1326487 65.87
16 HCLTECH EQ 09-Apr-2019 1096.35 1096.35 1128.85 1096.20 1118.00 1118.15 1118.64 3250491 3.636118e+09 117833 1665620 51.24
17 HCLTECH EQ 10-Apr-2019 1118.15 1120.00 1125.30 1096.20 1098.75 1098.70 1104.69 2413433 2.666090e+09 79204 1536537 63.67
18 HCLTECH EQ 11-Apr-2019 1098.70 1092.30 1101.10 1085.00 1088.90 1088.85 1092.36 1517790 1.657969e+09 112162 1040493 68.55
19 HCLTECH EQ 12-Apr-2019 1088.85 1088.80 1100.85 1076.60 1082.70 1083.45 1084.56 1419742 1.539789e+09 57359 758481 53.42
20 HCLTECH EQ 15-Apr-2019 1083.45 1089.00 1108.00 1085.00 1102.00 1103.25 1101.44 1934151 2.130355e+09 57781 1171051 60.55
21 HCLTECH EQ 16-Apr-2019 1103.25 1109.50 1116.25 1102.35 1103.00 1107.05 1108.50 1200591 1.330851e+09 55163 592041 49.31
In [239]:
infy
Out[239]:
Symbol Series Date Prev Close Open Price High Price Low Price Last Price Close Price Average Price Total Traded Quantity Turnover No. of Trades Deliverable Qty % Dly Qt to Traded Qty
0 INFY EQ 18-Mar-2019 718.55 721.55 723.70 705.35 709.50 710.20 715.00 8065544 5.766885e+09 169894 4656010 57.73
1 INFY EQ 19-Mar-2019 710.20 712.80 726.50 707.40 725.90 722.25 715.22 8917337 6.377898e+09 165399 5704607 63.97
2 INFY EQ 20-Mar-2019 722.25 727.25 742.30 727.15 737.80 738.95 737.53 13471173 9.935368e+09 368666 6929551 51.44
3 INFY BL 22-Mar-2019 734.95 738.95 738.95 738.95 738.95 738.95 738.95 1727935 1.276858e+09 1 1727935 100.00
4 INFY EQ 22-Mar-2019 738.95 745.00 746.35 732.30 745.00 743.10 741.40 8883260 6.586010e+09 186950 5182729 58.34
5 INFY EQ 25-Mar-2019 743.10 742.40 742.40 730.55 733.95 735.25 735.24 7644966 5.620908e+09 150353 4078146 53.34
6 INFY EQ 26-Mar-2019 735.25 735.25 735.25 719.10 727.80 727.75 724.65 9406261 6.816221e+09 220918 5868934 62.39
7 INFY EQ 27-Mar-2019 727.75 730.00 732.30 727.15 728.90 729.70 730.32 7387487 5.395200e+09 122789 5505910 74.53
8 INFY EQ 28-Mar-2019 729.70 730.00 745.00 730.00 739.10 737.80 739.73 9277241 6.862617e+09 187881 5521081 59.51
9 INFY EQ 29-Mar-2019 737.80 743.90 747.95 740.00 742.15 743.85 743.49 6924061 5.147991e+09 155083 4592403 66.33
10 INFY EQ 01-Apr-2019 743.85 742.00 760.00 742.00 753.75 755.10 754.32 6584982 4.967200e+09 149150 3788889 57.54
11 INFY EQ 02-Apr-2019 755.10 759.00 765.80 754.60 755.00 759.40 760.56 7283600 5.539625e+09 169885 4249666 58.35
12 INFY EQ 03-Apr-2019 759.40 756.00 758.50 746.80 751.65 753.30 752.48 6897127 5.189956e+09 200625 4213270 61.09
13 INFY EQ 04-Apr-2019 753.30 751.40 753.80 745.10 748.95 747.90 748.43 7688525 5.754346e+09 178872 5138276 66.83
14 INFY EQ 05-Apr-2019 747.90 750.90 760.95 748.00 759.55 759.30 758.69 6276040 4.761576e+09 148065 3851738 61.37
15 INFY EQ 08-Apr-2019 759.30 763.05 770.00 755.40 769.15 767.25 762.80 4919911 3.752910e+09 143815 2380635 48.39
16 INFY EQ 09-Apr-2019 767.25 770.00 773.00 758.20 760.80 760.60 765.16 6730692 5.150088e+09 146107 3353329 49.82
17 INFY EQ 10-Apr-2019 760.60 764.00 766.70 751.30 752.85 752.70 759.53 7031088 5.340337e+09 134089 4201714 59.76
18 INFY EQ 11-Apr-2019 752.70 751.75 751.75 740.05 743.40 742.70 744.27 8200995 6.103734e+09 158348 5047043 61.54
19 INFY EQ 12-Apr-2019 742.70 743.10 751.55 740.60 748.00 747.75 746.82 7043212 5.260021e+09 165165 2587611 36.74
20 INFY EQ 15-Apr-2019 747.75 724.70 731.35 712.60 728.20 727.50 724.58 31574803 2.287863e+10 566543 16753549 53.06
21 INFY EQ 16-Apr-2019 727.50 727.90 727.90 714.10 724.00 724.10 722.40 12524411 9.047647e+09 343101 7260034 57.97
In [240]:
loansData.index.name = "Index"
In [241]:
loansData
Out[241]:
Amount_Requested Amount_Funded_By_Investors Interest_Rate Loan_Length Loan_Purpose Debt_To_Income_Ratio State Home_Ownership Monthly_Income FICO_Range Open_CREDIT_Lines Revolving_CREDIT_Balance Inquiries_in_the_Last_6_Months Employment_Length
Index
81174 20000 20000.00 0.0890 36 months debt_consolidation 14.90% SC MORTGAGE 6541.67 735-739 14.0 14272.0 2.0 < 1 year
99592 19200 19200.00 0.1212 36 months debt_consolidation 28.36% TX MORTGAGE 4583.33 715-719 12.0 11140.0 1.0 2 years
80059 35000 35000.00 0.2198 60 months debt_consolidation 23.81% CA MORTGAGE 11500.00 690-694 14.0 21977.0 1.0 2 years
15825 10000 9975.00 0.0999 36 months debt_consolidation 14.30% KS MORTGAGE 3833.33 695-699 10.0 9346.0 0.0 5 years
33182 12000 12000.00 0.1171 36 months credit_card 18.78% NJ RENT 3195.00 695-699 11.0 14469.0 0.0 9 years
62403 6000 6000.00 0.1531 36 months other 20.05% CT OWN 4891.67 670-674 17.0 10391.0 2.0 3 years
48808 10000 10000.00 0.0790 36 months debt_consolidation 26.09% MA RENT 2916.67 720-724 10.0 15957.0 0.0 10+ years
22090 33500 33450.00 0.1714 60 months credit_card 14.70% LA MORTGAGE 13863.42 705-709 12.0 27874.0 0.0 10+ years
76404 14675 14675.00 0.1433 36 months credit_card 26.92% CA RENT 3150.00 685-689 9.0 7246.0 1.0 8 years
15867 7000 7000.00 0.0691 36 months credit_card 7.10% CA RENT 5000.00 715-719 8.0 7612.0 0.0 3 years
94971 2000 2000.00 0.1972 36 months moving 10.29% FL RENT 3575.00 670-674 10.0 12036.0 0.0 6 years
36911 10625 10625.00 0.1427 36 months debt_consolidation 12.54% CA MORTGAGE 4250.00 665-669 14.0 10767.0 0.0 < 1 year
41200 28000 27975.00 0.2167 60 months debt_consolidation 13.07% CT MORTGAGE 14166.67 670-674 12.0 10311.0 0.0 1 year
83869 35000 34950.00 0.0890 36 months debt_consolidation 20.46% CT RENT 9166.67 735-739 19.0 21536.0 0.0 1 year
53853 9600 9600.00 0.0762 36 months debt_consolidation 3.45% DC RENT 11250.00 725-729 13.0 4606.0 0.0 < 1 year
21399 25000 24975.00 0.1565 60 months debt_consolidation 21.99% CA RENT 5416.67 730-734 6.0 13929.0 0.0 9 years
62127 10000 10000.00 0.1212 36 months debt_consolidation 17.72% CA RENT 9000.00 695-699 18.0 20317.0 0.0 7 years
23446 14000 13900.25 0.1037 60 months debt_consolidation 11.95% OH RENT 4333.33 740-744 6.0 7419.0 0.0 9 years
44987 10000 10000.00 0.0976 36 months credit_card 7.13% FL RENT 2733.33 730-734 7.0 6112.0 2.0 3 years
17977 5200 5175.00 0.0999 60 months debt_consolidation 10.29% AL MORTGAGE 3750.00 760-764 10.0 16094.0 0.0 < 1 year
86099 22000 21975.00 0.2198 36 months debt_consolidation 11.19% TX MORTGAGE 6666.67 665-669 9.0 23124.0 0.0 10+ years
99483 30000 30000.00 0.1905 60 months credit_card 21.25% FL MORTGAGE 6250.00 695-699 12.0 34927.0 0.0 6 years
28798 6500 6500.00 0.1799 60 months car 19.63% FL RENT 4100.00 665-669 11.0 11697.0 1.0 2 years
24168 17400 17400.00 0.1199 36 months credit_card 12.47% AZ RENT 6833.33 695-699 7.0 26587.0 0.0 7 years
10356 4000 4000.00 0.1682 60 months vacation 13.71% GA MORTGAGE 4500.00 670-674 5.0 20804.0 0.0 3 years
46027 7200 7200.00 0.0790 36 months debt_consolidation 24.82% TX RENT 5416.67 705-709 8.0 12017.0 0.0 7 years
2238 8000 8000.00 0.1442 36 months debt_consolidation 24.63% MA RENT 2964.17 675-679 9.0 8928.0 2.0 6 years
65278 8000 8000.00 0.1531 36 months debt_consolidation 15.46% CA MORTGAGE 2916.67 675-679 13.0 7152.0 1.0 5 years
4227 3000 3000.00 0.0859 36 months other 3.72% MA MORTGAGE 4167.00 765-769 4.0 7074.0 0.0 5 years
50182 14500 14500.00 0.0790 36 months debt_consolidation 4.85% GA MORTGAGE 3958.33 760-764 4.0 9598.0 0.0 4 years
... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
84265 20000 20000.00 0.2295 60 months debt_consolidation 7.10% NJ RENT 6750.00 665-669 6.0 16104.0 1.0 7 years
80231 19000 19000.00 0.0790 36 months debt_consolidation 9.76% RI MORTGAGE 5166.67 770-774 18.0 43617.0 2.0 10+ years
49533 17300 17250.00 0.2245 60 months wedding 3.58% PA MORTGAGE 5500.00 685-689 11.0 2306.0 3.0 4 years
102514 7000 711.54 0.1513 36 months major_purchase 18.91% CO MORTGAGE 3833.00 650-654 13.0 12634.0 0.0 2 years
78618 7200 7200.00 0.1875 36 months debt_consolidation 16.21% MI RENT 8333.33 660-664 8.0 28916.0 0.0 10+ years
86953 10000 10000.00 0.1409 36 months major_purchase 9.71% CO RENT 4583.33 675-679 6.0 3859.0 0.0 < 1 year
80129 4000 3925.00 0.1409 36 months credit_card 12.27% FL MORTGAGE 8583.33 675-679 9.0 36943.0 1.0 10+ years
85216 17500 17500.00 0.0890 36 months debt_consolidation 10.94% UT MORTGAGE 25000.00 730-734 9.0 34545.0 0.0 6 years
38247 20000 20000.00 0.1171 36 months credit_card 9.58% SD MORTGAGE 5416.67 725-729 12.0 18267.0 1.0 4 years
91245 16200 16200.00 0.1580 60 months debt_consolidation 7.92% PA MORTGAGE 4833.33 680-684 12.0 12313.0 2.0 10+ years
53041 10000 10000.00 0.0603 36 months small_business 13.03% FL RENT 5000.00 760-764 8.0 3952.0 0.0 2 years
63051 27000 27000.00 0.0662 36 months debt_consolidation 12.21% OH MORTGAGE 9250.00 810-814 12.0 4211.0 0.0 5 years
14446 4500 4475.00 0.0751 36 months small_business 20.27% VA MORTGAGE 7075.50 720-724 15.0 68618.0 2.0 10+ years
68628 15875 15875.00 0.1433 36 months small_business 17.44% MD MORTGAGE 3416.67 675-679 11.0 15891.0 0.0 2 years
98758 15000 15000.00 0.1016 36 months credit_card 28.28% OH MORTGAGE 6666.67 690-694 15.0 14880.0 0.0 10+ years
13070 25000 24950.00 0.1075 36 months debt_consolidation 20.48% OR MORTGAGE 7083.33 765-769 10.0 25429.0 0.0 6 years
45836 7000 7000.00 0.1727 36 months other 18.38% NY OWN 2464.37 665-669 9.0 7089.0 0.0 3 years
52330 15000 15000.00 0.1999 36 months wedding 18.05% CA RENT 8000.00 660-664 6.0 45976.0 1.0 2 years
48243 17000 17000.00 0.1581 36 months debt_consolidation 17.01% CO RENT 3833.33 685-689 6.0 15484.0 1.0 6 years
63256 19075 19075.00 0.1875 36 months debt_consolidation 15.23% NY RENT 5166.67 670-674 17.0 13749.0 3.0 10+ years
42124 10000 10000.00 0.1171 36 months debt_consolidation 8.40% CA RENT 4500.00 710-714 8.0 8404.0 1.0 3 years
78043 8475 8475.00 0.0762 36 months debt_consolidation 15.88% CA RENT 3983.33 720-724 9.0 6882.0 0.0 NaN
925 6400 6350.00 0.1008 36 months debt_consolidation 8.11% NJ MORTGAGE 5166.67 710-714 5.0 5815.0 2.0 10+ years
74047 30000 30000.00 0.2328 60 months other 12.10% IL MORTGAGE 7083.33 675-679 16.0 17969.0 1.0 10+ years
49957 24000 23975.00 0.1465 36 months debt_consolidation 15.29% WA MORTGAGE 6666.67 685-689 13.0 17521.0 0.0 5 years
23735 30000 29950.00 0.1677 60 months debt_consolidation 19.23% NY MORTGAGE 9250.00 705-709 15.0 45880.0 1.0 8 years
65882 16000 16000.00 0.1409 60 months home_improvement 21.54% MD OWN 8903.25 740-744 18.0 18898.0 1.0 10+ years
55610 10000 10000.00 0.1399 36 months debt_consolidation 4.89% PA MORTGAGE 2166.67 680-684 4.0 4544.0 0.0 10+ years
38576 6000 6000.00 0.1242 36 months major_purchase 16.66% NJ RENT 3500.00 675-679 8.0 7753.0 0.0 5 years
3116 9000 5242.75 0.1379 36 months debt_consolidation 6.76% NY RENT 3875.00 670-674 7.0 7589.0 0.0 10+ years

2500 rows × 14 columns

In [245]:
class Foo:
    
    def __init__(self, x):
        self.x = x
        
    def hello(self):
        print("hello", self.x)
In [246]:
f = Foo(4)
In [247]:
f.x
Out[247]:
4
In [248]:
f.hello()

hello 4
In [249]:
f.x
Out[249]:
4
In [250]:
f.hello
Out[250]:
<bound method Foo.hello of <__main__.Foo object at 0x7fb7f7bbe7f0>>
In [252]:
f.name = "Foo"
In [253]:
f.name
Out[253]:
'Foo'
In [257]:
loansData.plot()
Out[257]:
<matplotlib.axes._subplots.AxesSubplot at 0x7fb7f74bda20>
In [256]:
%matplotlib inline

merging data

In [258]:
hcl
Out[258]:
Symbol Series Date Prev Close Open Price High Price Low Price Last Price Close Price Average Price Total Traded Quantity Turnover No. of Trades Deliverable Qty % Dly Qt to Traded Qty
0 HCLTECH EQ 18-Mar-2019 1029.30 1032.10 1039.95 1008.35 1015.70 1012.00 1018.66 1175027 1.196951e+09 62165 668156 56.86
1 HCLTECH EQ 19-Mar-2019 1012.00 1020.10 1042.00 1013.10 1040.75 1036.20 1026.58 1621276 1.664373e+09 71972 1038967 64.08
2 HCLTECH EQ 20-Mar-2019 1036.20 1042.00 1054.50 1031.50 1036.40 1037.95 1042.35 2204401 2.297748e+09 88784 1066063 48.36
3 HCLTECH EQ 22-Mar-2019 1037.95 1038.00 1048.00 1018.00 1030.45 1024.85 1028.34 2618545 2.692742e+09 110351 1849206 70.62
4 HCLTECH BL 25-Mar-2019 1420.00 1024.85 1024.85 1024.85 1024.85 1024.85 1024.85 186783 1.914246e+08 1 186783 100.00
5 HCLTECH EQ 25-Mar-2019 1024.85 1029.00 1029.00 1011.85 1017.70 1017.65 1017.35 1527955 1.554461e+09 50323 1057052 69.18
6 HCLTECH EQ 26-Mar-2019 1017.65 1013.35 1043.80 1011.50 1038.00 1034.60 1027.44 1655723 1.701149e+09 75011 1051791 63.52
7 HCLTECH EQ 27-Mar-2019 1034.60 1020.00 1056.35 1020.00 1042.50 1043.15 1043.39 1455845 1.519019e+09 65303 812220 55.79
8 HCLTECH EQ 28-Mar-2019 1043.15 1051.40 1088.35 1050.00 1085.00 1082.90 1077.27 4313807 4.647120e+09 121444 2316435 53.70
9 HCLTECH EQ 29-Mar-2019 1082.90 1090.00 1098.45 1067.65 1087.00 1087.45 1084.27 2827220 3.065474e+09 132698 1535638 54.32
10 HCLTECH EQ 01-Apr-2019 1087.45 1090.00 1120.00 1082.05 1110.00 1110.75 1109.12 2308865 2.560803e+09 87277 1203810 52.14
11 HCLTECH EQ 02-Apr-2019 1110.75 1110.00 1120.00 1092.50 1102.05 1102.60 1100.15 1393922 1.533523e+09 70655 670023 48.07
12 HCLTECH EQ 03-Apr-2019 1102.60 1102.60 1133.30 1091.55 1114.65 1120.60 1120.14 2648314 2.966495e+09 77938 1280139 48.34
13 HCLTECH EQ 04-Apr-2019 1120.60 1118.00 1118.05 1081.50 1090.35 1098.05 1094.16 4222104 4.619649e+09 153461 2467938 58.45
14 HCLTECH EQ 05-Apr-2019 1098.05 1090.35 1104.65 1085.50 1092.00 1092.80 1093.25 1704522 1.863477e+09 71310 982173 57.62
15 HCLTECH EQ 08-Apr-2019 1092.80 1095.00 1101.00 1080.95 1096.40 1096.35 1089.60 2013924 2.194381e+09 98317 1326487 65.87
16 HCLTECH EQ 09-Apr-2019 1096.35 1096.35 1128.85 1096.20 1118.00 1118.15 1118.64 3250491 3.636118e+09 117833 1665620 51.24
17 HCLTECH EQ 10-Apr-2019 1118.15 1120.00 1125.30 1096.20 1098.75 1098.70 1104.69 2413433 2.666090e+09 79204 1536537 63.67
18 HCLTECH EQ 11-Apr-2019 1098.70 1092.30 1101.10 1085.00 1088.90 1088.85 1092.36 1517790 1.657969e+09 112162 1040493 68.55
19 HCLTECH EQ 12-Apr-2019 1088.85 1088.80 1100.85 1076.60 1082.70 1083.45 1084.56 1419742 1.539789e+09 57359 758481 53.42
20 HCLTECH EQ 15-Apr-2019 1083.45 1089.00 1108.00 1085.00 1102.00 1103.25 1101.44 1934151 2.130355e+09 57781 1171051 60.55
21 HCLTECH EQ 16-Apr-2019 1103.25 1109.50 1116.25 1102.35 1103.00 1107.05 1108.50 1200591 1.330851e+09 55163 592041 49.31
In [259]:
infy
Out[259]:
Symbol Series Date Prev Close Open Price High Price Low Price Last Price Close Price Average Price Total Traded Quantity Turnover No. of Trades Deliverable Qty % Dly Qt to Traded Qty
0 INFY EQ 18-Mar-2019 718.55 721.55 723.70 705.35 709.50 710.20 715.00 8065544 5.766885e+09 169894 4656010 57.73
1 INFY EQ 19-Mar-2019 710.20 712.80 726.50 707.40 725.90 722.25 715.22 8917337 6.377898e+09 165399 5704607 63.97
2 INFY EQ 20-Mar-2019 722.25 727.25 742.30 727.15 737.80 738.95 737.53 13471173 9.935368e+09 368666 6929551 51.44
3 INFY BL 22-Mar-2019 734.95 738.95 738.95 738.95 738.95 738.95 738.95 1727935 1.276858e+09 1 1727935 100.00
4 INFY EQ 22-Mar-2019 738.95 745.00 746.35 732.30 745.00 743.10 741.40 8883260 6.586010e+09 186950 5182729 58.34
5 INFY EQ 25-Mar-2019 743.10 742.40 742.40 730.55 733.95 735.25 735.24 7644966 5.620908e+09 150353 4078146 53.34
6 INFY EQ 26-Mar-2019 735.25 735.25 735.25 719.10 727.80 727.75 724.65 9406261 6.816221e+09 220918 5868934 62.39
7 INFY EQ 27-Mar-2019 727.75 730.00 732.30 727.15 728.90 729.70 730.32 7387487 5.395200e+09 122789 5505910 74.53
8 INFY EQ 28-Mar-2019 729.70 730.00 745.00 730.00 739.10 737.80 739.73 9277241 6.862617e+09 187881 5521081 59.51
9 INFY EQ 29-Mar-2019 737.80 743.90 747.95 740.00 742.15 743.85 743.49 6924061 5.147991e+09 155083 4592403 66.33
10 INFY EQ 01-Apr-2019 743.85 742.00 760.00 742.00 753.75 755.10 754.32 6584982 4.967200e+09 149150 3788889 57.54
11 INFY EQ 02-Apr-2019 755.10 759.00 765.80 754.60 755.00 759.40 760.56 7283600 5.539625e+09 169885 4249666 58.35
12 INFY EQ 03-Apr-2019 759.40 756.00 758.50 746.80 751.65 753.30 752.48 6897127 5.189956e+09 200625 4213270 61.09
13 INFY EQ 04-Apr-2019 753.30 751.40 753.80 745.10 748.95 747.90 748.43 7688525 5.754346e+09 178872 5138276 66.83
14 INFY EQ 05-Apr-2019 747.90 750.90 760.95 748.00 759.55 759.30 758.69 6276040 4.761576e+09 148065 3851738 61.37
15 INFY EQ 08-Apr-2019 759.30 763.05 770.00 755.40 769.15 767.25 762.80 4919911 3.752910e+09 143815 2380635 48.39
16 INFY EQ 09-Apr-2019 767.25 770.00 773.00 758.20 760.80 760.60 765.16 6730692 5.150088e+09 146107 3353329 49.82
17 INFY EQ 10-Apr-2019 760.60 764.00 766.70 751.30 752.85 752.70 759.53 7031088 5.340337e+09 134089 4201714 59.76
18 INFY EQ 11-Apr-2019 752.70 751.75 751.75 740.05 743.40 742.70 744.27 8200995 6.103734e+09 158348 5047043 61.54
19 INFY EQ 12-Apr-2019 742.70 743.10 751.55 740.60 748.00 747.75 746.82 7043212 5.260021e+09 165165 2587611 36.74
20 INFY EQ 15-Apr-2019 747.75 724.70 731.35 712.60 728.20 727.50 724.58 31574803 2.287863e+10 566543 16753549 53.06
21 INFY EQ 16-Apr-2019 727.50 727.90 727.90 714.10 724.00 724.10 722.40 12524411 9.047647e+09 343101 7260034 57.97
In [261]:
both = pd.concat([hcl, infy])
In [267]:
both.loc[0] # to accesss rows
Out[267]:
Symbol Series Date Prev Close Open Price High Price Low Price Last Price Close Price Average Price Total Traded Quantity Turnover No. of Trades Deliverable Qty % Dly Qt to Traded Qty
0 HCLTECH EQ 18-Mar-2019 1029.30 1032.10 1039.95 1008.35 1015.7 1012.0 1018.66 1175027 1.196951e+09 62165 668156 56.86
0 INFY EQ 18-Mar-2019 718.55 721.55 723.70 705.35 709.5 710.2 715.00 8065544 5.766885e+09 169894 4656010 57.73
In [271]:
both.iloc[:,[0,1,2,3]]
Out[271]:
Symbol Series Date Prev Close
0 HCLTECH EQ 18-Mar-2019 1029.30
1 HCLTECH EQ 19-Mar-2019 1012.00
2 HCLTECH EQ 20-Mar-2019 1036.20
3 HCLTECH EQ 22-Mar-2019 1037.95
4 HCLTECH BL 25-Mar-2019 1420.00
5 HCLTECH EQ 25-Mar-2019 1024.85
6 HCLTECH EQ 26-Mar-2019 1017.65
7 HCLTECH EQ 27-Mar-2019 1034.60
8 HCLTECH EQ 28-Mar-2019 1043.15
9 HCLTECH EQ 29-Mar-2019 1082.90
10 HCLTECH EQ 01-Apr-2019 1087.45
11 HCLTECH EQ 02-Apr-2019 1110.75
12 HCLTECH EQ 03-Apr-2019 1102.60
13 HCLTECH EQ 04-Apr-2019 1120.60
14 HCLTECH EQ 05-Apr-2019 1098.05
15 HCLTECH EQ 08-Apr-2019 1092.80
16 HCLTECH EQ 09-Apr-2019 1096.35
17 HCLTECH EQ 10-Apr-2019 1118.15
18 HCLTECH EQ 11-Apr-2019 1098.70
19 HCLTECH EQ 12-Apr-2019 1088.85
20 HCLTECH EQ 15-Apr-2019 1083.45
21 HCLTECH EQ 16-Apr-2019 1103.25
0 INFY EQ 18-Mar-2019 718.55
1 INFY EQ 19-Mar-2019 710.20
2 INFY EQ 20-Mar-2019 722.25
3 INFY BL 22-Mar-2019 734.95
4 INFY EQ 22-Mar-2019 738.95
5 INFY EQ 25-Mar-2019 743.10
6 INFY EQ 26-Mar-2019 735.25
7 INFY EQ 27-Mar-2019 727.75
8 INFY EQ 28-Mar-2019 729.70
9 INFY EQ 29-Mar-2019 737.80
10 INFY EQ 01-Apr-2019 743.85
11 INFY EQ 02-Apr-2019 755.10
12 INFY EQ 03-Apr-2019 759.40
13 INFY EQ 04-Apr-2019 753.30
14 INFY EQ 05-Apr-2019 747.90
15 INFY EQ 08-Apr-2019 759.30
16 INFY EQ 09-Apr-2019 767.25
17 INFY EQ 10-Apr-2019 760.60
18 INFY EQ 11-Apr-2019 752.70
19 INFY EQ 12-Apr-2019 742.70
20 INFY EQ 15-Apr-2019 747.75
21 INFY EQ 16-Apr-2019 727.50
In [274]:
both.iloc[23,[0,1,2,3]] # first argument is for rows, 2nd argument is for columns
Out[274]:
Symbol               INFY
Series                 EQ
Date          19-Mar-2019
Prev Close          710.2
Name: 1, dtype: object
In [275]:
both.groupby("Symbol").mean()
Out[275]:
Prev Close Open Price High Price Low Price Last Price Close Price Average Price Total Traded Quantity Turnover No. of Trades Deliverable Qty % Dly Qt to Traded Qty
Symbol
HCLTECH 1088.163636 1071.031818 1086.481818 1059.645455 1073.506818 1073.736364 1073.050455 2.073383e+06 2.237726e+09 82568.727273 1.194414e+06 59.711818
INFY 741.629545 742.313636 747.818182 734.850000 742.015909 742.063636 741.889545 8.839120e+06 6.524183e+09 192349.954545 5.117866e+06 60.001818
In [276]:
both.groupby("Symbol").std()
Out[276]:
Prev Close Open Price High Price Low Price Last Price Close Price Average Price Total Traded Quantity Turnover No. of Trades Deliverable Qty % Dly Qt to Traded Qty
Symbol
HCLTECH 82.151002 36.682608 36.301697 34.596142 34.690946 36.469378 36.264050 9.746473e+05 1.076030e+09 33304.851941 5.435900e+05 11.470584
INFY 14.971963 15.118454 14.731829 15.687529 14.334535 14.541224 15.250877 5.592132e+06 4.020228e+09 110256.132759 2.930483e+06 11.733335
In [277]:
both.groupby("Symbol").max()
Out[277]:
Series Date Prev Close Open Price High Price Low Price Last Price Close Price Average Price Total Traded Quantity Turnover No. of Trades Deliverable Qty % Dly Qt to Traded Qty
Symbol
HCLTECH EQ 29-Mar-2019 1420.00 1120.0 1133.3 1102.35 1118.00 1120.60 1120.14 4313807 4.647120e+09 153461 2467938 100.0
INFY EQ 29-Mar-2019 767.25 770.0 773.0 758.20 769.15 767.25 765.16 31574803 2.287863e+10 566543 16753549 100.0
In [280]:
df = pd.DataFrame({"col1":[1, 2, 3, 4, 5],
              "col2":[1, 1, 1, 1, 1],
              "key": ['a','b','c','d','e']})
In [281]:
df
Out[281]:
col1 col2 key
0 1 1 a
1 2 1 b
2 3 1 c
3 4 1 d
4 5 1 e
In [284]:
df1 = pd.DataFrame({"col4":[2.1, 2.2, 2.3, 2.4],
                   "col5":[5.1, 5.2, 5.3, 5.4],
                    "key": ['c', 'a', 'b', 'e']})
In [285]:
df1
Out[285]:
col4 col5 key
0 2.1 5.1 c
1 2.2 5.2 a
2 2.3 5.3 b
3 2.4 5.4 e
In [295]:
pd.merge(df1, df , on="key", how='right')
Out[295]:
col4 col5 key col1 col2
0 2.1 5.1 c 3 1
1 2.2 5.2 a 1 1
2 2.3 5.3 b 2 1
3 2.4 5.4 e 5 1
4 NaN NaN d 4 1
In [288]:
help(pd.merge)

Help on function merge in module pandas.core.reshape.merge:

merge(left, right, how='inner', on=None, left_on=None, right_on=None, left_index=False, right_index=False, sort=False, suffixes=('_x', '_y'), copy=True, indicator=False, validate=None)
    Merge DataFrame objects by performing a database-style join operation by
    columns or indexes.
    
    If joining columns on columns, the DataFrame indexes *will be
    ignored*. Otherwise if joining indexes on indexes or indexes on a column or
    columns, the index will be passed on.
    
    Parameters
    ----------
    left : DataFrame
    right : DataFrame
    how : {'left', 'right', 'outer', 'inner'}, default 'inner'
        * left: use only keys from left frame, similar to a SQL left outer join;
          preserve key order
        * right: use only keys from right frame, similar to a SQL right outer join;
          preserve key order
        * outer: use union of keys from both frames, similar to a SQL full outer
          join; sort keys lexicographically
        * inner: use intersection of keys from both frames, similar to a SQL inner
          join; preserve the order of the left keys
    on : label or list
        Column or index level names to join on. These must be found in both
        DataFrames. If `on` is None and not merging on indexes then this defaults
        to the intersection of the columns in both DataFrames.
    left_on : label or list, or array-like
        Column or index level names to join on in the left DataFrame. Can also
        be an array or list of arrays of the length of the left DataFrame.
        These arrays are treated as if they are columns.
    right_on : label or list, or array-like
        Column or index level names to join on in the right DataFrame. Can also
        be an array or list of arrays of the length of the right DataFrame.
        These arrays are treated as if they are columns.
    left_index : boolean, default False
        Use the index from the left DataFrame as the join key(s). If it is a
        MultiIndex, the number of keys in the other DataFrame (either the index
        or a number of columns) must match the number of levels
    right_index : boolean, default False
        Use the index from the right DataFrame as the join key. Same caveats as
        left_index
    sort : boolean, default False
        Sort the join keys lexicographically in the result DataFrame. If False,
        the order of the join keys depends on the join type (how keyword)
    suffixes : 2-length sequence (tuple, list, ...)
        Suffix to apply to overlapping column names in the left and right
        side, respectively
    copy : boolean, default True
        If False, do not copy data unnecessarily
    indicator : boolean or string, default False
        If True, adds a column to output DataFrame called "_merge" with
        information on the source of each row.
        If string, column with information on source of each row will be added to
        output DataFrame, and column will be named value of string.
        Information column is Categorical-type and takes on a value of "left_only"
        for observations whose merge key only appears in 'left' DataFrame,
        "right_only" for observations whose merge key only appears in 'right'
        DataFrame, and "both" if the observation's merge key is found in both.
    
    validate : string, default None
        If specified, checks if merge is of specified type.
    
        * "one_to_one" or "1:1": check if merge keys are unique in both
          left and right datasets.
        * "one_to_many" or "1:m": check if merge keys are unique in left
          dataset.
        * "many_to_one" or "m:1": check if merge keys are unique in right
          dataset.
        * "many_to_many" or "m:m": allowed, but does not result in checks.
    
        .. versionadded:: 0.21.0
    
    Notes
    -----
    Support for specifying index levels as the `on`, `left_on`, and
    `right_on` parameters was added in version 0.23.0
    
    Examples
    --------
    
    >>> A              >>> B
        lkey value         rkey value
    0   foo  1         0   foo  5
    1   bar  2         1   bar  6
    2   baz  3         2   qux  7
    3   foo  4         3   bar  8
    
    >>> A.merge(B, left_on='lkey', right_on='rkey', how='outer')
       lkey  value_x  rkey  value_y
    0  foo   1        foo   5
    1  foo   4        foo   5
    2  bar   2        bar   6
    3  bar   2        bar   8
    4  baz   3        NaN   NaN
    5  NaN   NaN      qux   7
    
    Returns
    -------
    merged : DataFrame
        The output type will the be same as 'left', if it is a subclass
        of DataFrame.
    
    See also
    --------
    merge_ordered
    merge_asof
    DataFrame.join
In [297]:
hceq
Out[297]:
Symbol Series Prev Close Open Price High Price Low Price Last Price Close Price Average Price Total Traded Quantity Turnover No. of Trades Deliverable Qty % Dly Qt to Traded Qty Date
Date
2019-03-18 HCLTECH EQ 1029.30 1032.10 1039.95 1008.35 1015.70 1012.00 1018.66 1175027 1.196951e+09 62165 668156 56.86 2019-03-18
2019-03-19 HCLTECH EQ 1012.00 1020.10 1042.00 1013.10 1040.75 1036.20 1026.58 1621276 1.664373e+09 71972 1038967 64.08 2019-03-19
2019-03-20 HCLTECH EQ 1036.20 1042.00 1054.50 1031.50 1036.40 1037.95 1042.35 2204401 2.297748e+09 88784 1066063 48.36 2019-03-20
2019-03-22 HCLTECH EQ 1037.95 1038.00 1048.00 1018.00 1030.45 1024.85 1028.34 2618545 2.692742e+09 110351 1849206 70.62 2019-03-22
2019-03-25 HCLTECH EQ 1024.85 1029.00 1029.00 1011.85 1017.70 1017.65 1017.35 1527955 1.554461e+09 50323 1057052 69.18 2019-03-25
2019-03-26 HCLTECH EQ 1017.65 1013.35 1043.80 1011.50 1038.00 1034.60 1027.44 1655723 1.701149e+09 75011 1051791 63.52 2019-03-26
2019-03-27 HCLTECH EQ 1034.60 1020.00 1056.35 1020.00 1042.50 1043.15 1043.39 1455845 1.519019e+09 65303 812220 55.79 2019-03-27
2019-03-28 HCLTECH EQ 1043.15 1051.40 1088.35 1050.00 1085.00 1082.90 1077.27 4313807 4.647120e+09 121444 2316435 53.70 2019-03-28
2019-03-29 HCLTECH EQ 1082.90 1090.00 1098.45 1067.65 1087.00 1087.45 1084.27 2827220 3.065474e+09 132698 1535638 54.32 2019-03-29
2019-04-01 HCLTECH EQ 1087.45 1090.00 1120.00 1082.05 1110.00 1110.75 1109.12 2308865 2.560803e+09 87277 1203810 52.14 2019-04-01
2019-04-02 HCLTECH EQ 1110.75 1110.00 1120.00 1092.50 1102.05 1102.60 1100.15 1393922 1.533523e+09 70655 670023 48.07 2019-04-02
2019-04-03 HCLTECH EQ 1102.60 1102.60 1133.30 1091.55 1114.65 1120.60 1120.14 2648314 2.966495e+09 77938 1280139 48.34 2019-04-03
2019-04-04 HCLTECH EQ 1120.60 1118.00 1118.05 1081.50 1090.35 1098.05 1094.16 4222104 4.619649e+09 153461 2467938 58.45 2019-04-04
2019-04-05 HCLTECH EQ 1098.05 1090.35 1104.65 1085.50 1092.00 1092.80 1093.25 1704522 1.863477e+09 71310 982173 57.62 2019-04-05
2019-04-08 HCLTECH EQ 1092.80 1095.00 1101.00 1080.95 1096.40 1096.35 1089.60 2013924 2.194381e+09 98317 1326487 65.87 2019-04-08
2019-04-09 HCLTECH EQ 1096.35 1096.35 1128.85 1096.20 1118.00 1118.15 1118.64 3250491 3.636118e+09 117833 1665620 51.24 2019-04-09
2019-04-10 HCLTECH EQ 1118.15 1120.00 1125.30 1096.20 1098.75 1098.70 1104.69 2413433 2.666090e+09 79204 1536537 63.67 2019-04-10
2019-04-11 HCLTECH EQ 1098.70 1092.30 1101.10 1085.00 1088.90 1088.85 1092.36 1517790 1.657969e+09 112162 1040493 68.55 2019-04-11
2019-04-12 HCLTECH EQ 1088.85 1088.80 1100.85 1076.60 1082.70 1083.45 1084.56 1419742 1.539789e+09 57359 758481 53.42 2019-04-12
2019-04-15 HCLTECH EQ 1083.45 1089.00 1108.00 1085.00 1102.00 1103.25 1101.44 1934151 2.130355e+09 57781 1171051 60.55 2019-04-15
2019-04-16 HCLTECH EQ 1103.25 1109.50 1116.25 1102.35 1103.00 1107.05 1108.50 1200591 1.330851e+09 55163 592041 49.31 2019-04-16
In [301]:
help(pd.read_excel)

Help on function read_excel in module pandas.io.excel:

read_excel(io, sheet_name=0, header=0, names=None, index_col=None, usecols=None, squeeze=False, dtype=None, engine=None, converters=None, true_values=None, false_values=None, skiprows=None, nrows=None, na_values=None, parse_dates=False, date_parser=None, thousands=None, comment=None, skipfooter=0, convert_float=True, **kwds)
    Read an Excel table into a pandas DataFrame
    
    Parameters
    ----------
    io : string, path object (pathlib.Path or py._path.local.LocalPath),
        file-like object, pandas ExcelFile, or xlrd workbook.
        The string could be a URL. Valid URL schemes include http, ftp, s3,
        and file. For file URLs, a host is expected. For instance, a local
        file could be file://localhost/path/to/workbook.xlsx
    sheet_name : string, int, mixed list of strings/ints, or None, default 0
    
        Strings are used for sheet names, Integers are used in zero-indexed
        sheet positions.
    
        Lists of strings/integers are used to request multiple sheets.
    
        Specify None to get all sheets.
    
        str|int -> DataFrame is returned.
        list|None -> Dict of DataFrames is returned, with keys representing
        sheets.
    
        Available Cases
    
        * Defaults to 0 -> 1st sheet as a DataFrame
        * 1 -> 2nd sheet as a DataFrame
        * "Sheet1" -> 1st sheet as a DataFrame
        * [0,1,"Sheet5"] -> 1st, 2nd & 5th sheet as a dictionary of DataFrames
        * None -> All sheets as a dictionary of DataFrames
    
    sheetname : string, int, mixed list of strings/ints, or None, default 0
    
        .. deprecated:: 0.21.0
           Use `sheet_name` instead
    
    header : int, list of ints, default 0
        Row (0-indexed) to use for the column labels of the parsed
        DataFrame. If a list of integers is passed those row positions will
        be combined into a ``MultiIndex``. Use None if there is no header.
    names : array-like, default None
        List of column names to use. If file contains no header row,
        then you should explicitly pass header=None
    index_col : int, list of ints, default None
        Column (0-indexed) to use as the row labels of the DataFrame.
        Pass None if there is no such column.  If a list is passed,
        those columns will be combined into a ``MultiIndex``.  If a
        subset of data is selected with ``usecols``, index_col
        is based on the subset.
    parse_cols : int or list, default None
    
        .. deprecated:: 0.21.0
           Pass in `usecols` instead.
    
    usecols : int or list, default None
        * If None then parse all columns,
        * If int then indicates last column to be parsed
        * If list of ints then indicates list of column numbers to be parsed
        * If string then indicates comma separated list of Excel column letters and
          column ranges (e.g. "A:E" or "A,C,E:F").  Ranges are inclusive of
          both sides.
    squeeze : boolean, default False
        If the parsed data only contains one column then return a Series
    dtype : Type name or dict of column -> type, default None
        Data type for data or columns. E.g. {'a': np.float64, 'b': np.int32}
        Use `object` to preserve data as stored in Excel and not interpret dtype.
        If converters are specified, they will be applied INSTEAD
        of dtype conversion.
    
        .. versionadded:: 0.20.0
    
    engine: string, default None
        If io is not a buffer or path, this must be set to identify io.
        Acceptable values are None or xlrd
    converters : dict, default None
        Dict of functions for converting values in certain columns. Keys can
        either be integers or column labels, values are functions that take one
        input argument, the Excel cell content, and return the transformed
        content.
    true_values : list, default None
        Values to consider as True
    
        .. versionadded:: 0.19.0
    
    false_values : list, default None
        Values to consider as False
    
        .. versionadded:: 0.19.0
    
    skiprows : list-like
        Rows to skip at the beginning (0-indexed)
    nrows : int, default None
        Number of rows to parse
    
        .. versionadded:: 0.23.0
    
    na_values : scalar, str, list-like, or dict, default None
        Additional strings to recognize as NA/NaN. If dict passed, specific
        per-column NA values. By default the following values are interpreted
        as NaN: '', '#N/A', '#N/A N/A', '#NA', '-1.#IND', '-1.#QNAN', '-NaN', '-nan',
        '1.#IND', '1.#QNAN', 'N/A', 'NA', 'NULL', 'NaN', 'n/a', 'nan',
        'null'.
    keep_default_na : bool, default True
        If na_values are specified and keep_default_na is False the default NaN
        values are overridden, otherwise they're appended to.
    verbose : boolean, default False
        Indicate number of NA values placed in non-numeric columns
    thousands : str, default None
        Thousands separator for parsing string columns to numeric.  Note that
        this parameter is only necessary for columns stored as TEXT in Excel,
        any numeric columns will automatically be parsed, regardless of display
        format.
    comment : str, default None
        Comments out remainder of line. Pass a character or characters to this
        argument to indicate comments in the input file. Any data between the
        comment string and the end of the current line is ignored.
    skip_footer : int, default 0
    
        .. deprecated:: 0.23.0
           Pass in `skipfooter` instead.
    skipfooter : int, default 0
        Rows at the end to skip (0-indexed)
    convert_float : boolean, default True
        convert integral floats to int (i.e., 1.0 --> 1). If False, all numeric
        data will be read in as floats: Excel stores all numbers as floats
        internally
    
    Returns
    -------
    parsed : DataFrame or Dict of DataFrames
        DataFrame from the passed in Excel file.  See notes in sheet_name
        argument for more information on when a Dict of Dataframes is returned.
    
    Examples
    --------
    
    An example DataFrame written to a local file
    
    >>> df_out = pd.DataFrame([('string1', 1),
    ...                        ('string2', 2),
    ...                        ('string3', 3)],
    ...                       columns=['Name', 'Value'])
    >>> df_out
          Name  Value
    0  string1      1
    1  string2      2
    2  string3      3
    >>> df_out.to_excel('tmp.xlsx')
    
    The file can be read using the file name as string or an open file object:
    
    >>> pd.read_excel('tmp.xlsx')
          Name  Value
    0  string1      1
    1  string2      2
    2  string3      3
    
    >>> pd.read_excel(open('tmp.xlsx','rb'))
          Name  Value
    0  string1      1
    1  string2      2
    2  string3      3
    
    Index and header can be specified via the `index_col` and `header` arguments
    
    >>> pd.read_excel('tmp.xlsx', index_col=None, header=None)
         0        1      2
    0  NaN     Name  Value
    1  0.0  string1      1
    2  1.0  string2      2
    3  2.0  string3      3
    
    Column types are inferred but can be explicitly specified
    
    >>> pd.read_excel('tmp.xlsx', dtype={'Name':str, 'Value':float})
          Name  Value
    0  string1    1.0
    1  string2    2.0
    2  string3    3.0
    
    True, False, and NA values, and thousands separators have defaults,
    but can be explicitly specified, too. Supply the values you would like
    as strings or lists of strings!
    
    >>> pd.read_excel('tmp.xlsx',
    ...               na_values=['string1', 'string2'])
          Name  Value
    0      NaN      1
    1      NaN      2
    2  string3      3
    
    Comment lines in the excel input file can be skipped using the `comment` kwarg
    
    >>> df = pd.DataFrame({'a': ['1', '#2'], 'b': ['2', '3']})
    >>> df.to_excel('tmp.xlsx', index=False)
    >>> pd.read_excel('tmp.xlsx')
        a  b
    0   1  2
    1  #2  3
    
    >>> pd.read_excel('tmp.xlsx', comment='#')
       a  b
    0  1  2

Writing Excel files

pip install XlsxWriter

In [298]:
from xlsxwriter import Workbook
In [320]:
w = Workbook("tables.xlsx")
In [321]:
type(w)
Out[321]:
xlsxwriter.workbook.Workbook
In [322]:
s = w.add_worksheet("sheet1")
In [323]:
type(s)
Out[323]:
xlsxwriter.worksheet.Worksheet
In [324]:
s.write("A1", "Hello")
Out[324]:
0
In [325]:
for i in range(1,6):
    for j in range(1, 11):
        s.write(i+1, j, i*j)
In [326]:
w.close()

formats

In [307]:
w = Workbook("formats.xlsx")
s = w.add_worksheet("format")
In [308]:
b = w.add_format({'bold':True})
In [309]:
n = w.add_format({"num_format":"$#,###"})
In [310]:
s.write("A1", "Name", b)
s.write("B1", "Salary", b)
for i in range(10):
    s.write(i+1, 0, "Name"+str(i))
    s.write(i+1, 1, 1000+i, n)
In [311]:
w.close()
In [313]:
w = Workbook("tables.xlsx")
In [318]:
w.close()
In [327]:
import openpyxl
In [332]:
w = openpyxl.load_workbook("tables.xlsx")
In [333]:
w['sheet1']["C10"] = 10
In [335]:
w.save("tables.xlsx")
In [336]:
w.close()

Working with web

In [337]:
import requests

requests.get()
requests.put()
requests.post()
requests.delete()
In [338]:
url = "https://api.github.com/orgs/google/repos"
In [339]:
repos = requests.get(url).json()
In [340]:
type(repos)
Out[340]:
list
In [341]:
repos[0]
Out[341]:
{'id': 1936771,
 'node_id': 'MDEwOlJlcG9zaXRvcnkxOTM2Nzcx',
 'name': 'truth',
 'full_name': 'google/truth',
 'private': False,
 'owner': {'login': 'google',
  'id': 1342004,
  'node_id': 'MDEyOk9yZ2FuaXphdGlvbjEzNDIwMDQ=',
  'avatar_url': 'https://avatars1.githubusercontent.com/u/1342004?v=4',
  'gravatar_id': '',
  'url': 'https://api.github.com/users/google',
  'html_url': 'https://github.com/google',
  'followers_url': 'https://api.github.com/users/google/followers',
  'following_url': 'https://api.github.com/users/google/following{/other_user}',
  'gists_url': 'https://api.github.com/users/google/gists{/gist_id}',
  'starred_url': 'https://api.github.com/users/google/starred{/owner}{/repo}',
  'subscriptions_url': 'https://api.github.com/users/google/subscriptions',
  'organizations_url': 'https://api.github.com/users/google/orgs',
  'repos_url': 'https://api.github.com/users/google/repos',
  'events_url': 'https://api.github.com/users/google/events{/privacy}',
  'received_events_url': 'https://api.github.com/users/google/received_events',
  'type': 'Organization',
  'site_admin': False},
 'html_url': 'https://github.com/google/truth',
 'description': 'Fluent assertions for Java',
 'fork': False,
 'url': 'https://api.github.com/repos/google/truth',
 'forks_url': 'https://api.github.com/repos/google/truth/forks',
 'keys_url': 'https://api.github.com/repos/google/truth/keys{/key_id}',
 'collaborators_url': 'https://api.github.com/repos/google/truth/collaborators{/collaborator}',
 'teams_url': 'https://api.github.com/repos/google/truth/teams',
 'hooks_url': 'https://api.github.com/repos/google/truth/hooks',
 'issue_events_url': 'https://api.github.com/repos/google/truth/issues/events{/number}',
 'events_url': 'https://api.github.com/repos/google/truth/events',
 'assignees_url': 'https://api.github.com/repos/google/truth/assignees{/user}',
 'branches_url': 'https://api.github.com/repos/google/truth/branches{/branch}',
 'tags_url': 'https://api.github.com/repos/google/truth/tags',
 'blobs_url': 'https://api.github.com/repos/google/truth/git/blobs{/sha}',
 'git_tags_url': 'https://api.github.com/repos/google/truth/git/tags{/sha}',
 'git_refs_url': 'https://api.github.com/repos/google/truth/git/refs{/sha}',
 'trees_url': 'https://api.github.com/repos/google/truth/git/trees{/sha}',
 'statuses_url': 'https://api.github.com/repos/google/truth/statuses/{sha}',
 'languages_url': 'https://api.github.com/repos/google/truth/languages',
 'stargazers_url': 'https://api.github.com/repos/google/truth/stargazers',
 'contributors_url': 'https://api.github.com/repos/google/truth/contributors',
 'subscribers_url': 'https://api.github.com/repos/google/truth/subscribers',
 'subscription_url': 'https://api.github.com/repos/google/truth/subscription',
 'commits_url': 'https://api.github.com/repos/google/truth/commits{/sha}',
 'git_commits_url': 'https://api.github.com/repos/google/truth/git/commits{/sha}',
 'comments_url': 'https://api.github.com/repos/google/truth/comments{/number}',
 'issue_comment_url': 'https://api.github.com/repos/google/truth/issues/comments{/number}',
 'contents_url': 'https://api.github.com/repos/google/truth/contents/{+path}',
 'compare_url': 'https://api.github.com/repos/google/truth/compare/{base}...{head}',
 'merges_url': 'https://api.github.com/repos/google/truth/merges',
 'archive_url': 'https://api.github.com/repos/google/truth/{archive_format}{/ref}',
 'downloads_url': 'https://api.github.com/repos/google/truth/downloads',
 'issues_url': 'https://api.github.com/repos/google/truth/issues{/number}',
 'pulls_url': 'https://api.github.com/repos/google/truth/pulls{/number}',
 'milestones_url': 'https://api.github.com/repos/google/truth/milestones{/number}',
 'notifications_url': 'https://api.github.com/repos/google/truth/notifications{?since,all,participating}',
 'labels_url': 'https://api.github.com/repos/google/truth/labels{/name}',
 'releases_url': 'https://api.github.com/repos/google/truth/releases{/id}',
 'deployments_url': 'https://api.github.com/repos/google/truth/deployments',
 'created_at': '2011-06-22T18:55:12Z',
 'updated_at': '2019-04-17T20:59:20Z',
 'pushed_at': '2019-04-16T16:07:30Z',
 'git_url': 'git://github.com/google/truth.git',
 'ssh_url': 'git@github.com:google/truth.git',
 'clone_url': 'https://github.com/google/truth.git',
 'svn_url': 'https://github.com/google/truth',
 'homepage': 'http://google.github.io/truth',
 'size': 26830,
 'stargazers_count': 1676,
 'watchers_count': 1676,
 'language': 'Java',
 'has_issues': True,
 'has_projects': True,
 'has_downloads': True,
 'has_wiki': True,
 'has_pages': True,
 'forks_count': 180,
 'mirror_url': None,
 'archived': False,
 'disabled': False,
 'open_issues_count': 69,
 'license': {'key': 'apache-2.0',
  'name': 'Apache License 2.0',
  'spdx_id': 'Apache-2.0',
  'url': 'https://api.github.com/licenses/apache-2.0',
  'node_id': 'MDc6TGljZW5zZTI='},
 'forks': 180,
 'open_issues': 69,
 'watchers': 1676,
 'default_branch': 'master',
 'permissions': {'admin': False, 'push': False, 'pull': True}}
In [342]:
for repo in repos:
    print(repo['full_name'], repo['forks'])

google/truth 180
google/ruby-openid-apps-discovery 17
google/autoparse 34
google/gitkit-ruby 0
google/anvil-build 17
google/googletv-android-samples 75
google/ChannelPlate 9
google/GL-Shader-Validator 11
google/qpp 10
google/CSP-Validator 12
google/embed-dart-vm 20
google/module-server 49
google/cxx-std-draft 15
google/filesystem-proposal 12
google/libcxx 49
google/tracing-framework 217
google/namebench 92
google/devtoolsExtended 20
google/sirius 0
google/testRunner 8
google/crx2app 11
google/episodes.dart 21
google/cpp-netlib 58
google/dagger 1607
google/ios-webkit-debug-proxy 357
google/google.github.io 43
google/kratu 31
google/build-debian-cloud 21
google/traceur-compiler 584
google/firmata.py 12
In [344]:
def get_forks(r):
    return r['forks']
for repo in sorted(repos, key=get_forks, reverse=True):
    print(repo['full_name'], repo['forks'])

google/dagger 1607
google/traceur-compiler 584
google/ios-webkit-debug-proxy 357
google/tracing-framework 217
google/truth 180
google/namebench 92
google/googletv-android-samples 75
google/cpp-netlib 58
google/module-server 49
google/libcxx 49
google/google.github.io 43
google/autoparse 34
google/kratu 31
google/episodes.dart 21
google/build-debian-cloud 21
google/embed-dart-vm 20
google/devtoolsExtended 20
google/ruby-openid-apps-discovery 17
google/anvil-build 17
google/cxx-std-draft 15
google/CSP-Validator 12
google/filesystem-proposal 12
google/firmata.py 12
google/GL-Shader-Validator 11
google/crx2app 11
google/qpp 10
google/ChannelPlate 9
google/testRunner 8
google/gitkit-ruby 0
google/sirius 0
In [358]:
def mystr(x):
    return str(x).replace(",","_")

with open("repos.csv", "w") as f:
    f.write(",".join(repos[0].keys()))
    f.write("\n")
    for repo in sorted(repos, key=get_forks, reverse=True):
        f.write(",".join(map(mystr, [value for value in repo.values()])))
        f.write("\n")
In [359]:
pd.read_csv("repos.csv")
Out[359]:
id node_id name full_name private owner html_url description fork url ... mirror_url archived disabled open_issues_count license forks open_issues watchers default_branch permissions
0 7968417 MDEwOlJlcG9zaXRvcnk3OTY4NDE3 dagger google/dagger False {'login': 'google'_ 'id': 1342004_ 'node_id': ... https://github.com/google/dagger A fast dependency injector for Android and Java. True https://api.github.com/repos/google/dagger ... None False False 96 {'key': 'apache-2.0'_ 'name': 'Apache License ... 1607 96 13333 master {'admin': False_ 'push': False_ 'pull': True}
1 9060347 MDEwOlJlcG9zaXRvcnk5MDYwMzQ3 traceur-compiler google/traceur-compiler False {'login': 'google'_ 'id': 1342004_ 'node_id': ... https://github.com/google/traceur-compiler Traceur is a JavaScript.next-to-JavaScript-of-... False https://api.github.com/repos/google/traceur-co... ... None False False 296 {'key': 'apache-2.0'_ 'name': 'Apache License ... 584 296 7892 master {'admin': False_ 'push': False_ 'pull': True}
2 8165161 MDEwOlJlcG9zaXRvcnk4MTY1MTYx ios-webkit-debug-proxy google/ios-webkit-debug-proxy False {'login': 'google'_ 'id': 1342004_ 'node_id': ... https://github.com/google/ios-webkit-debug-proxy A DevTools proxy (Chrome Remote Debugging Prot... False https://api.github.com/repos/google/ios-webkit... ... None False False 18 {'key': 'other'_ 'name': 'Other'_ 'spdx_id': '... 357 18 4294 master {'admin': False_ 'push': False_ 'pull': True}
3 6601918 MDEwOlJlcG9zaXRvcnk2NjAxOTE4 tracing-framework google/tracing-framework False {'login': 'google'_ 'id': 1342004_ 'node_id': ... https://github.com/google/tracing-framework Web Tracing Framework libraries and extensions. False https://api.github.com/repos/google/tracing-fr... ... None False False 118 {'key': 'bsd-3-clause'_ 'name': 'BSD 3-Clause ... 217 118 2544 master {'admin': False_ 'push': False_ 'pull': True}
4 1936771 MDEwOlJlcG9zaXRvcnkxOTM2Nzcx truth google/truth False {'login': 'google'_ 'id': 1342004_ 'node_id': ... https://github.com/google/truth Fluent assertions for Java False https://api.github.com/repos/google/truth ... None False False 69 {'key': 'apache-2.0'_ 'name': 'Apache License ... 180 69 1676 master {'admin': False_ 'push': False_ 'pull': True}
5 6694773 MDEwOlJlcG9zaXRvcnk2Njk0Nzcz namebench google/namebench False {'login': 'google'_ 'id': 1342004_ 'node_id': ... https://github.com/google/namebench namebench False https://api.github.com/repos/google/namebench ... None False False 9 {'key': 'apache-2.0'_ 'name': 'Apache License ... 92 9 649 master {'admin': False_ 'push': False_ 'pull': True}
6 5072378 MDEwOlJlcG9zaXRvcnk1MDcyMzc4 googletv-android-samples google/googletv-android-samples False {'login': 'google'_ 'id': 1342004_ 'node_id': ... https://github.com/google/googletv-android-sam... Source for many GoogleTV Example applications. False https://api.github.com/repos/google/googletv-a... ... None False False 0 None 75 0 148 master {'admin': False_ 'push': False_ 'pull': True}
7 7776515 MDEwOlJlcG9zaXRvcnk3Nzc2NTE1 cpp-netlib google/cpp-netlib False {'login': 'google'_ 'id': 1342004_ 'node_id': ... https://github.com/google/cpp-netlib The C++ Network Library Project -- header-only... True https://api.github.com/repos/google/cpp-netlib ... None False False 0 {'key': 'bsl-1.0'_ 'name': 'Boost Software Lic... 58 0 280 master {'admin': False_ 'push': False_ 'pull': True}
8 6093488 MDEwOlJlcG9zaXRvcnk2MDkzNDg4 module-server google/module-server False {'login': 'google'_ 'id': 1342004_ 'node_id': ... https://github.com/google/module-server module-server False https://api.github.com/repos/google/module-server ... None False False 3 {'key': 'apache-2.0'_ 'name': 'Apache License ... 49 3 574 master {'admin': False_ 'push': False_ 'pull': True}
9 6461369 MDEwOlJlcG9zaXRvcnk2NDYxMzY5 libcxx google/libcxx False {'login': 'google'_ 'id': 1342004_ 'node_id': ... https://github.com/google/libcxx libc++; cloned from http://llvm.org/git/libcxx... False https://api.github.com/repos/google/libcxx ... None False False 0 {'key': 'other'_ 'name': 'Other'_ 'spdx_id': '... 49 0 107 master {'admin': False_ 'push': False_ 'pull': True}
10 8459994 MDEwOlJlcG9zaXRvcnk4NDU5OTk0 google.github.io google/google.github.io False {'login': 'google'_ 'id': 1342004_ 'node_id': ... https://github.com/google/google.github.io None False https://api.github.com/repos/google/google.git... ... None False False 0 None 43 0 36 master {'admin': False_ 'push': False_ 'pull': True}
11 3248531 MDEwOlJlcG9zaXRvcnkzMjQ4NTMx autoparse google/autoparse False {'login': 'google'_ 'id': 1342004_ 'node_id': ... https://github.com/google/autoparse A dynamically generated parsing system using J... False https://api.github.com/repos/google/autoparse ... None False False 4 {'key': 'apache-2.0'_ 'name': 'Apache License ... 34 4 133 master {'admin': False_ 'push': False_ 'pull': True}
12 8566972 MDEwOlJlcG9zaXRvcnk4NTY2OTcy kratu google/kratu False {'login': 'google'_ 'id': 1342004_ 'node_id': ... https://github.com/google/kratu None False https://api.github.com/repos/google/kratu ... None False False 0 {'key': 'apache-2.0'_ 'name': 'Apache License ... 31 0 279 master {'admin': False_ 'push': False_ 'pull': True}
13 7697149 MDEwOlJlcG9zaXRvcnk3Njk3MTQ5 episodes.dart google/episodes.dart False {'login': 'google'_ 'id': 1342004_ 'node_id': ... https://github.com/google/episodes.dart A framework for timing performance of web apps. False https://api.github.com/repos/google/episodes.dart ... None False False 0 {'key': 'bsd-3-clause'_ 'name': 'BSD 3-Clause ... 21 0 12 master {'admin': False_ 'push': False_ 'pull': True}
14 8858648 MDEwOlJlcG9zaXRvcnk4ODU4NjQ4 build-debian-cloud google/build-debian-cloud False {'login': 'google'_ 'id': 1342004_ 'node_id': ... https://github.com/google/build-debian-cloud Script to create Debian Squeeze & Wheezy Amazo... True https://api.github.com/repos/google/build-debi... ... None False False 5 {'key': 'other'_ 'name': 'Other'_ 'spdx_id': '... 21 5 30 master {'admin': False_ 'push': False_ 'pull': True}
15 5844236 MDEwOlJlcG9zaXRvcnk1ODQ0MjM2 embed-dart-vm google/embed-dart-vm False {'login': 'google'_ 'id': 1342004_ 'node_id': ... https://github.com/google/embed-dart-vm None False https://api.github.com/repos/google/embed-dart-vm ... None False False 2 None 20 2 64 master {'admin': False_ 'push': False_ 'pull': True}
16 7411412 MDEwOlJlcG9zaXRvcnk3NDExNDEy devtoolsExtended google/devtoolsExtended False {'login': 'google'_ 'id': 1342004_ 'node_id': ... https://github.com/google/devtoolsExtended devtoolsExtended: Extended Chrome Devtools in ... False https://api.github.com/repos/google/devtoolsEx... ... None False False 2 None 20 2 58 devtoolsApp {'admin': False_ 'push': False_ 'pull': True}
17 3248507 MDEwOlJlcG9zaXRvcnkzMjQ4NTA3 ruby-openid-apps-discovery google/ruby-openid-apps-discovery False {'login': 'google'_ 'id': 1342004_ 'node_id': ... https://github.com/google/ruby-openid-apps-dis... None False https://api.github.com/repos/google/ruby-openi... ... None False False 1 None 17 1 32 master {'admin': False_ 'push': False_ 'pull': True}
18 3975462 MDEwOlJlcG9zaXRvcnkzOTc1NDYy anvil-build google/anvil-build False {'login': 'google'_ 'id': 1342004_ 'node_id': ... https://github.com/google/anvil-build A parallel build system and content pipeline False https://api.github.com/repos/google/anvil-build ... None False False 7 {'key': 'other'_ 'name': 'Other'_ 'spdx_id': '... 17 7 50 master {'admin': False_ 'push': False_ 'pull': True}
19 6461354 MDEwOlJlcG9zaXRvcnk2NDYxMzU0 cxx-std-draft google/cxx-std-draft False {'login': 'google'_ 'id': 1342004_ 'node_id': ... https://github.com/google/cxx-std-draft C++ standards drafts True https://api.github.com/repos/google/cxx-std-draft ... None False False 0 None 15 0 41 master {'admin': False_ 'push': False_ 'pull': True}
20 5815969 MDEwOlJlcG9zaXRvcnk1ODE1OTY5 CSP-Validator google/CSP-Validator False {'login': 'google'_ 'id': 1342004_ 'node_id': ... https://github.com/google/CSP-Validator CSP-Validator False https://api.github.com/repos/google/CSP-Validator ... None False False 1 {'key': 'other'_ 'name': 'Other'_ 'spdx_id': '... 12 1 17 master {'admin': False_ 'push': False_ 'pull': True}
21 6461358 MDEwOlJlcG9zaXRvcnk2NDYxMzU4 filesystem-proposal google/filesystem-proposal False {'login': 'google'_ 'id': 1342004_ 'node_id': ... https://github.com/google/filesystem-proposal Boost Filesystem Library with modifications fo... True https://api.github.com/repos/google/filesystem... ... None False False 0 None 12 0 12 master {'admin': False_ 'push': False_ 'pull': True}
22 9065917 MDEwOlJlcG9zaXRvcnk5MDY1OTE3 firmata.py google/firmata.py False {'login': 'google'_ 'id': 1342004_ 'node_id': ... https://github.com/google/firmata.py None False https://api.github.com/repos/google/firmata.py ... None False False 0 {'key': 'apache-2.0'_ 'name': 'Apache License ... 12 0 14 master {'admin': False_ 'push': False_ 'pull': True}
23 5753459 MDEwOlJlcG9zaXRvcnk1NzUzNDU5 GL-Shader-Validator google/GL-Shader-Validator False {'login': 'google'_ 'id': 1342004_ 'node_id': ... https://github.com/google/GL-Shader-Validator None False https://api.github.com/repos/google/GL-Shader-... ... None False False 1 {'key': 'other'_ 'name': 'Other'_ 'spdx_id': '... 11 1 30 master {'admin': False_ 'push': False_ 'pull': True}
24 7411430 MDEwOlJlcG9zaXRvcnk3NDExNDMw crx2app google/crx2app False {'login': 'google'_ 'id': 1342004_ 'node_id': ... https://github.com/google/crx2app None False https://api.github.com/repos/google/crx2app ... None False False 0 None 11 0 15 master {'admin': False_ 'push': False_ 'pull': True}
25 5753483 MDEwOlJlcG9zaXRvcnk1NzUzNDgz qpp google/qpp False {'login': 'google'_ 'id': 1342004_ 'node_id': ... https://github.com/google/qpp Querypoint Debugging Prototype False https://api.github.com/repos/google/qpp ... None False False 34 {'key': 'other'_ 'name': 'Other'_ 'spdx_id': '... 10 34 38 master {'admin': False_ 'push': False_ 'pull': True}
26 5511393 MDEwOlJlcG9zaXRvcnk1NTExMzkz ChannelPlate google/ChannelPlate False {'login': 'google'_ 'id': 1342004_ 'node_id': ... https://github.com/google/ChannelPlate JavaScript library for postMessage over Messag... False https://api.github.com/repos/google/ChannelPlate ... None False False 1 None 9 1 15 master {'admin': False_ 'push': False_ 'pull': True}
27 7411426 MDEwOlJlcG9zaXRvcnk3NDExNDI2 testRunner google/testRunner False {'login': 'google'_ 'id': 1342004_ 'node_id': ... https://github.com/google/testRunner testRunner False https://api.github.com/repos/google/testRunner ... None False False 0 {'key': 'other'_ 'name': 'Other'_ 'spdx_id': '... 8 0 7 master {'admin': False_ 'push': False_ 'pull': True}
28 3276484 MDEwOlJlcG9zaXRvcnkzMjc2NDg0 gitkit-ruby google/gitkit-ruby False {'login': 'google'_ 'id': 1342004_ 'node_id': ... https://github.com/google/gitkit-ruby None False https://api.github.com/repos/google/gitkit-ruby ... None False False 2 None 0 2 8 master {'admin': False_ 'push': False_ 'pull': True}
29 7411424 MDEwOlJlcG9zaXRvcnk3NDExNDI0 sirius google/sirius False {'login': 'google'_ 'id': 1342004_ 'node_id': ... https://github.com/google/sirius sirius False https://api.github.com/repos/google/sirius ... None False False 1 None 0 1 4 master {'admin': False_ 'push': False_ 'pull': True}

30 rows × 74 columns

reading password protected excel file on windows

In [360]:
url = "C:\\Users\\prahlada\\Downloads\\file2.xlsx"
from xlrd import *
import win32com.client
import csv
import sys
password = "Arcesium123"
xlwb = xlApp.Workbooks.Open(url, False, True, None, password)
xlws = xlwb.Sheets(1)
content = xlws.Range(xlws.Cells(1,1),xlws.Cells(5,10)).value
DF = pd.DataFrame(list(content))

---------------------------------------------------------------------------
ModuleNotFoundError                       Traceback (most recent call last)
<ipython-input-360-ad39aec89cd9> in <module>
      1 url = "C:\\Users\\prahlada\\Downloads\\file2.xlsx"
      2 from xlrd import *
----> 3 import win32com.client
      4 import csv
      5 import sys

ModuleNotFoundError: No module named 'win32com'

but it should work on windows machine!

In [ ]: