In [1]:

    

import agate


    

In [2]:

    

text = agate.Text()
business_07 = agate.Table.from_csv('SBO_2007/SBO_2007.csv')


    

In [6]:

    

print(business_07)
business_07.print_table(10)


    

    




|-------------------+---------------|
|  column_names     | column_types  |
|-------------------+---------------|
|  Industry_07      | Text          |
|  MainOperator_07  | Text          |
|  Year             | Number        |
|  FIRMALL_07       | Number        |
|  FIRMALL_PCT_07   | Number        |
|  FIRMALL_S_07     | Number        |
|  FIRMALL_PCT_S_07 | Number        |
|-------------------+---------------|

|-----------------------+----------------------+-------+------------+----------------+--------------+-------------------|
|  Industry_07          | MainOperator_07      |  Year | FIRMALL_07 | FIRMALL_PCT_07 | FIRMALL_S_07 | FIRMALL_PCT_S_07  |
|-----------------------+----------------------+-------+------------+----------------+--------------+-------------------|
|  Total for all sec... | All firms            | 2,007 |  4,602,192 |                |            0 |                   |
|  Total for all sec... | Jointly owned and... | 2,007 |  1,200,010 |           45.0 |            0 |              0.2  |
|  Total for all sec... | Jointly owned but... | 2,007 |    935,633 |           35.1 |            1 |              0.2  |
|  Total for all sec... | Jointly owned but... | 2,007 |    310,194 |           11.6 |            1 |              0.1  |
|  Total for all sec... | Not jointly owned... | 2,007 |    222,059 |            8.3 |            2 |              0.1  |
|  Total for all sec... | Total reporting      | 2,007 |  2,667,896 |          100.0 |            0 |              0.0  |
|  Total for all sec... | Item not reported    | 2,007 |     21,621 |                |            5 |                   |
|  Total for all sec... | Complete nonresponse | 2,007 |  1,912,675 |                |            0 |                   |
|  Agriculture, fore... | All firms            | 2,007 |     67,458 |                |            3 |                   |
|  Agriculture, fore... | Jointly owned and... | 2,007 |     14,872 |           41.8 |            7 |              1.1  |
|  ...                  | ...                  |   ... |        ... |            ... |          ... |              ...  |
|-----------------------+----------------------+-------+------------+----------------+--------------+-------------------|

In [48]:

    

industry_ratios_07 = business_07.where(lambda row: row['MainOperator_07'] == 'Jointly owned and equally operated by a husband and wife').select([
        'Industry_07', 'FIRMALL_PCT_07'
    ]).order_by('FIRMALL_PCT_07', reverse=True)


    

In [49]:

    

print(len(industry_ratios_07.rows))


    

    




20

In [50]:

    

industry_ratios_07.print_table(max_column_width=50)


    

    




|-----------------------------------------------------+-----------------|
|  Industry_07                                        | FIRMALL_PCT_07  |
|-----------------------------------------------------+-----------------|
|  Accommodation and food services                    |           60.7  |
|  Utilities                                          |           51.0  |
|  Retail trade                                       |           48.8  |
|  Real estate and rental and leasing                 |           48.8  |
|  Educational services                               |           47.2  |
|  Transportation and warehousing                     |           47.1  |
|  Wholesale trade                                    |           47.0  |
|  Mining, quarrying, and oil and gas extraction      |           46.8  |
|  Management of companies and enterprises            |           46.5  |
|  Administrative and support and waste management... |           46.5  |
|  Arts, entertainment, and recreation                |           46.5  |
|  Manufacturing                                      |           46.0  |
|  Total for all sectors                              |           45.0  |
|  Information                                        |           43.2  |
|  Finance and insurance                              |           42.2  |
|  Other services (except public administration)      |           42.0  |
|  Agriculture, forestry, fishing and hunting         |           41.8  |
|  Construction                                       |           40.2  |
|  Health care and social assistance                  |           39.7  |
|  Professional, scientific, and technical services   |           39.1  |
|-----------------------------------------------------+-----------------|

In [46]:

    

business_12 = agate.Table.from_csv('SBO_2012/SBO_2012.csv')


    

In [47]:

    

print(business_12)


    

    




|-------------------+---------------|
|  column_names     | column_types  |
|-------------------+---------------|
|  Industry_12      | Text          |
|  MainOperator_12  | Text          |
|  Year             | Number        |
|  FIRMALL_12       | Number        |
|  FIRMALL_PCT_12   | Number        |
|  FIRMALL_S_12     | Number        |
|  FIRMALL_PCT_S_12 | Number        |
|-------------------+---------------|

In [53]:

    

industry_ratios_12 = business_12.where(lambda row: row['MainOperator_12'] == 'Jointly owned and equally operated by spouses').select([
        'Industry_12', 'FIRMALL_PCT_12'
    ]).order_by('FIRMALL_PCT_12', reverse=True)


    

In [54]:

    

print(len(industry_ratios_12.rows))


    

    




20

In [55]:

    

industry_ratios_12.print_table(max_column_width=50)


    

    




|-----------------------------------------------------+-----------------|
|  Industry_12                                        | FIRMALL_PCT_12  |
|-----------------------------------------------------+-----------------|
|  Accommodation and food services                    |           61.9  |
|  Arts, entertainment, and recreation                |           52.4  |
|  Real estate and rental and leasing                 |           52.0  |
|  Educational services                               |           49.5  |
|  Retail trade                                       |           49.2  |
|  Manufacturing                                      |           48.5  |
|  Health care and social assistance                  |           47.6  |
|  Administrative and support and waste management... |           47.5  |
|  Total for all sectors                              |           46.9  |
|  Information                                        |           46.1  |
|  Wholesale trade                                    |           44.8  |
|  Transportation and warehousing                     |           43.7  |
|  Management of companies and enterprises            |           43.6  |
|  Mining, quarrying, and oil and gas extraction      |           42.4  |
|  Agriculture, forestry, fishing and hunting         |           42.3  |
|  Other services (except public administration)      |           42.2  |
|  Professional, scientific, and technical services   |           42.0  |
|  Finance and insurance                              |           40.5  |
|  Utilities                                          |           38.4  |
|  Construction                                       |           38.0  |
|-----------------------------------------------------+-----------------|

In [58]:

    

industry_ratios_0712 = industry_ratios_07.join(industry_ratios_12,  'Industry_07', 'Industry_12', inner=True)


    

In [59]:

    

industry_ratios_0712.print_table()


    

    




|-----------------------+----------------+-----------------|
|  Industry_07          | FIRMALL_PCT_07 | FIRMALL_PCT_12  |
|-----------------------+----------------+-----------------|
|  Accommodation and... |           60.7 |           61.9  |
|  Utilities            |           51.0 |           38.4  |
|  Retail trade         |           48.8 |           49.2  |
|  Real estate and r... |           48.8 |           52.0  |
|  Educational services |           47.2 |           49.5  |
|  Transportation an... |           47.1 |           43.7  |
|  Wholesale trade      |           47.0 |           44.8  |
|  Mining, quarrying... |           46.8 |           42.4  |
|  Management of com... |           46.5 |           43.6  |
|  Administrative an... |           46.5 |           47.5  |
|  Arts, entertainme... |           46.5 |           52.4  |
|  Manufacturing        |           46.0 |           48.5  |
|  Total for all sec... |           45.0 |           46.9  |
|  Information          |           43.2 |           46.1  |
|  Finance and insur... |           42.2 |           40.5  |
|  Other services (e... |           42.0 |           42.2  |
|  Agriculture, fore... |           41.8 |           42.3  |
|  Construction         |           40.2 |           38.0  |
|  Health care and s... |           39.7 |           47.6  |
|  Professional, sci... |           39.1 |           42.0  |
|-----------------------+----------------+-----------------|

In [81]:

    

industry_ratios_pc = industry_ratios_0712.compute([
        ('pc_change', agate.PercentChange('FIRMALL_PCT_07', 'FIRMALL_PCT_12'))
    ]).order_by('pc_change')
from decimal import Decimal

number_type = agate.Number()

def round_pc(row):
    return row['pc_change'].quantize(Decimal('0.1'))

rounded = industry_ratios_pc.compute([
    ('pc_rounded', agate.Formula(number_type, round_pc))
])
for_printing = rounded.select([
        'Industry_07', 'pc_rounded'
    ])


    

In [82]:

    

for_printing.print_table(max_column_width=50)


    

    




|-----------------------------------------------------+-------------|
|  Industry_07                                        | pc_rounded  |
|-----------------------------------------------------+-------------|
|  Utilities                                          |      -24.7  |
|  Mining, quarrying, and oil and gas extraction      |       -9.4  |
|  Transportation and warehousing                     |       -7.2  |
|  Management of companies and enterprises            |       -6.2  |
|  Construction                                       |       -5.5  |
|  Wholesale trade                                    |       -4.7  |
|  Finance and insurance                              |       -4.0  |
|  Other services (except public administration)      |        0.5  |
|  Retail trade                                       |        0.8  |
|  Agriculture, forestry, fishing and hunting         |        1.2  |
|  Accommodation and food services                    |        2.0  |
|  Administrative and support and waste management... |        2.2  |
|  Total for all sectors                              |        4.2  |
|  Educational services                               |        4.9  |
|  Manufacturing                                      |        5.4  |
|  Real estate and rental and leasing                 |        6.6  |
|  Information                                        |        6.7  |
|  Professional, scientific, and technical services   |        7.4  |
|  Arts, entertainment, and recreation                |       12.7  |
|  Health care and social assistance                  |       19.9  |
|-----------------------------------------------------+-------------|

In [ ]: