In [4]:
#Iimports and aliases each of the library I plan to use
import numpy as np
from pandas import *
import pandas as pd
In [5]:
# Loads the RAW CSV FILE into a DataFrame
df = pd.read_csv("CompensationByState.csv")
In [6]:
# Filters out all rows which have a null value for column IndustryClassification
df = df[pd.notnull(df['IndustryClassification'])]
In [7]:
# Outputs head for sanity check
df.head()
Out[7]:
GeoFIPS
GeoName
Region
Table
LineCode
IndustryClassification
Description
1998
1999
2000
...
2005
2006
2007
2008
2009
2010
2011
2012
2013
2014
0
0
United States
SA7N 1/
50
...
Wages and salaries by place of work (thousands...
4177471000
4456769000
4823765000
...
5688826000
6051676000
6391231000
6528825000
6244497000
6368897000
6623278000
6920922000
7110424000
7430910000
1
0
United States
SA7N 1/
81
111-112
Farm wages and salaries
15883000
16169000
16970000
...
19022000
18764000
21140000
21777000
21806000
20753000
19888000
24065000
26687000
27681000
2
0
United States
SA7N 1/
82
...
Nonfarm wages and salaries
4161588000
4440600000
4806795000
...
5669804000
6032912000
6370091000
6507048000
6222691000
6348144000
6603390000
6896857000
7083737000
7403229000
3
0
United States
SA7N 1/
90
113-814
Private nonfarm wages and salaries
3469473000
3716124000
4036467000
...
4692227000
5012443000
5295743000
5377831000
5063520000
5174098000
5426885000
5716888000
5894439000
6199925250
4
0
United States
SA7N 1/
100
113-115
Forestry, fishing, and related activities
9080000
9544000
9881000
...
11627000
12145000
12714000
13152000
12716000
13725000
14120000
15110000
15853000
17292000
5 rows × 24 columns
In [8]:
#"Melts" the data, unpivots all of the "year" columns
survey = pd.melt(df, id_vars=["GeoFIPS", "GeoName", "Region", "Table", "LineCode", "IndustryClassification", "Description"], var_name="Year", value_name="Comepnsation")
In [9]:
survey
Out[9]:
GeoFIPS
GeoName
Region
Table
LineCode
IndustryClassification
Description
Year
ThousandsOfDollars
0
0
United States
SA7N 1/
50
...
Wages and salaries by place of work (thousands...
1998
4177471000
1
0
United States
SA7N 1/
81
111-112
Farm wages and salaries
1998
15883000
2
0
United States
SA7N 1/
82
...
Nonfarm wages and salaries
1998
4161588000
3
0
United States
SA7N 1/
90
113-814
Private nonfarm wages and salaries
1998
3469473000
4
0
United States
SA7N 1/
100
113-115
Forestry, fishing, and related activities
1998
9080000
5
0
United States
SA7N 1/
101
113
Forestry and logging
1998
2443000
6
0
United States
SA7N 1/
102
114
Fishing, hunting, and trapping
1998
479000
7
0
United States
SA7N 1/
103
115
Agriculture and forestry support activ...
1998
6158000
8
0
United States
SA7N 1/
200
21
Mining
1998
29471000
9
0
United States
SA7N 1/
201
211
Oil and gas extraction
1998
10235000
10
0
United States
SA7N 1/
202
212
Mining (except oil and gas)
1998
11169000
11
0
United States
SA7N 1/
203
213
Support activities for mining
1998
8067000
12
0
United States
SA7N 1/
300
22
Utilities
1998
34812000
13
0
United States
SA7N 1/
400
23
Construction
1998
211283000
14
0
United States
SA7N 1/
401
236
Construction of buildings
1998
52556000
15
0
United States
SA7N 1/
402
237
Heavy and civil engineering construction
1998
34072000
16
0
United States
SA7N 1/
403
238
Specialty trade contractors
1998
124655000
17
0
United States
SA7N 1/
500
31-33
Manufacturing
1998
680377000
18
0
United States
SA7N 1/
510
321,327-339
Durable goods manufacturing
1998
447900000
19
0
United States
SA7N 1/
511
321
Wood product manufacturing
1998
16923000
20
0
United States
SA7N 1/
512
327
Nonmetallic mineral product manufact...
1998
19269000
21
0
United States
SA7N 1/
513
331
Primary metal manufacturing
1998
27230000
22
0
United States
SA7N 1/
514
332
Fabricated metal product manufacturing
1998
61279000
23
0
United States
SA7N 1/
515
333
Machinery manufacturing
1998
62880000
24
0
United States
SA7N 1/
516
334
Computer and electronic product manu...
1998
98735000
25
0
United States
SA7N 1/
517
335
Electrical equipment and appliance m...
1998
21308000
26
0
United States
SA7N 1/
518
3361-3363
Motor vehicles, bodies and trailers,...
1998
57447000
27
0
United States
SA7N 1/
519
3364-3369
Other transportation equipment manuf...
1998
39580000
28
0
United States
SA7N 1/
521
337
Furniture and related product manufa...
1998
17756000
29
0
United States
SA7N 1/
522
339
Miscellaneous manufacturing
1998
25493000
...
...
...
...
...
...
...
...
...
...
116250
98000
Far West
8
SA7N 1/
1103
533
Lessors of nonfinancial intangible assets
2014
(NA)
116251
98000
Far West
8
SA7N 1/
1200
54
Professional, scientific, and technical ...
2014
152749569
116252
98000
Far West
8
SA7N 1/
1300
55
Management of companies and enterprises
2014
39420374
116253
98000
Far West
8
SA7N 1/
1400
56
Administrative and waste management serv...
2014
56821083
116254
98000
Far West
8
SA7N 1/
1401
561
Administrative and support services
2014
(NA)
116255
98000
Far West
8
SA7N 1/
1402
562
Waste management and remediation services
2014
(NA)
116256
98000
Far West
8
SA7N 1/
1500
61
Educational services
2014
20689169
116257
98000
Far West
8
SA7N 1/
1600
62
Health care and social assistance
2014
138309335
116258
98000
Far West
8
SA7N 1/
1601
621
Ambulatory health care services
2014
(NA)
116259
98000
Far West
8
SA7N 1/
1602
622
Hospitals
2014
(NA)
116260
98000
Far West
8
SA7N 1/
1603
623
Nursing and residential care facilities
2014
(NA)
116261
98000
Far West
8
SA7N 1/
1604
624
Social assistance
2014
(NA)
116262
98000
Far West
8
SA7N 1/
1700
71
Arts, entertainment, and recreation
2014
20076986
116263
98000
Far West
8
SA7N 1/
1701
711
Performing arts and spectator sports
2014
(NA)
116264
98000
Far West
8
SA7N 1/
1702
712
Museums, historical sites, zoos, and p...
2014
(NA)
116265
98000
Far West
8
SA7N 1/
1703
713
Amusement, gambling, and recreation
2014
(NA)
116266
98000
Far West
8
SA7N 1/
1800
72
Accommodation and food services
2014
58421234
116267
98000
Far West
8
SA7N 1/
1801
721
Accommodation
2014
(NA)
116268
98000
Far West
8
SA7N 1/
1802
722
Food services and drinking places
2014
(NA)
116269
98000
Far West
8
SA7N 1/
1900
81
Other services, except public administra...
2014
39278559
116270
98000
Far West
8
SA7N 1/
1901
811
Repair and maintenance
2014
(NA)
116271
98000
Far West
8
SA7N 1/
1902
812
Personal and laundry services
2014
(NA)
116272
98000
Far West
8
SA7N 1/
1903
813
Membership associations and organizations
2014
(NA)
116273
98000
Far West
8
SA7N 1/
1904
814
Private households
2014
(NA)
116274
98000
Far West
8
SA7N 1/
2000
...
Government and government enterprises
2014
228935723
116275
98000
Far West
8
SA7N 1/
2001
...
Federal, civilian
2014
29856157
116276
98000
Far West
8
SA7N 1/
2002
...
Military
2014
20801289
116277
98000
Far West
8
SA7N 1/
2010
...
State and local
2014
178278277
116278
98000
Far West
8
SA7N 1/
2011
...
State government
2014
(NA)
116279
98000
Far West
8
SA7N 1/
2012
...
Local government
2014
(NA)
116280 rows × 9 columns
In [10]:
#Outputs that data to a new CSV file
survey.to_csv("MeltedWagesByState.csv")
In [ ]:
Content source: opleban/BEA_DATA
Similar notebooks: