In [1]:
import pandas as pd
import numpy as np
pieces = []
columns = ["State","State_ANSI", "Ag_District", "Ag_District_Code", "County",
"County_ANSI","Commodity","Data_Item", "Domain", "Domain_Category", "Value", "CV", "FIPS"]
for i in range(1, 7):
path = 'farm_data/veggies_by_county_{0}.csv'.format(i)
frame = pd.read_csv(path, names=columns, skiprows=1)
pieces.append(frame)
# Concatenate everything into a single DataFrame
veg_df = pd.concat(pieces, ignore_index=True)
In [2]:
# make strings to concatenate into FIPS codes
veg_df.State_ANSI = veg_df.State_ANSI.astype(str)
veg_df.County_ANSI = veg_df.County_ANSI.astype(str)
# more readable in title case than all caps
veg_df.State = veg_df.apply(lambda x: x.State.title(), axis = 1)
veg_df.County = veg_df.apply(lambda x: x.County.title(), axis = 1)
veg_df.Commodity = veg_df.apply(lambda x: x.Commodity.title(), axis = 1)
In [3]:
def prepend_zeros(row):
if len(row.County_ANSI) == 1:
return ''.join(['00', row.County_ANSI])
elif len(row.County_ANSI) == 2:
return ''.join(['0', row.County_ANSI])
else:
return row.County_ANSI
veg_df.County_ANSI = veg_df.apply(prepend_zeros, axis=1)
In [13]:
# index by combined ids
veg_df.FIPS = veg_df.apply(lambda row: ''.join([row.State_ANSI, row.County_ANSI]), axis = 1)
veg_df.tail(30)
Out[13]:
State
State_ANSI
Ag_District
Ag_District_Code
County
County_ANSI
Commodity
Data_Item
Domain
Domain_Category
Value
CV
FIPS
226176
Wyoming
56
WEST
30
Lincoln
023
Vegetable Totals
VEGETABLE TOTALS, IN THE OPEN, FRESH MARKET - ...
TOTAL
NOT SPECIFIED
2
54.2
56023
226177
Wyoming
56
WEST
30
Sublette
035
Cabbage
CABBAGE, HEAD - ACRES HARVESTED
TOTAL
NOT SPECIFIED
(D)
(D)
56035
226178
Wyoming
56
WEST
30
Sublette
035
Cabbage
CABBAGE, HEAD - OPERATIONS WITH AREA HARVESTED
TOTAL
NOT SPECIFIED
1
NaN
56035
226179
Wyoming
56
WEST
30
Sublette
035
Cabbage
CABBAGE, HEAD, FRESH MARKET - ACRES HARVESTED
TOTAL
NOT SPECIFIED
(D)
(D)
56035
226180
Wyoming
56
WEST
30
Sublette
035
Cabbage
CABBAGE, HEAD, FRESH MARKET - OPERATIONS WITH ...
TOTAL
NOT SPECIFIED
1
NaN
56035
226181
Wyoming
56
WEST
30
Sublette
035
Carrots
CARROTS - ACRES HARVESTED
TOTAL
NOT SPECIFIED
(D)
(D)
56035
226182
Wyoming
56
WEST
30
Sublette
035
Carrots
CARROTS - OPERATIONS WITH AREA HARVESTED
TOTAL
NOT SPECIFIED
1
NaN
56035
226183
Wyoming
56
WEST
30
Sublette
035
Carrots
CARROTS, FRESH MARKET - ACRES HARVESTED
TOTAL
NOT SPECIFIED
(D)
(D)
56035
226184
Wyoming
56
WEST
30
Sublette
035
Carrots
CARROTS, FRESH MARKET - OPERATIONS WITH AREA H...
TOTAL
NOT SPECIFIED
1
NaN
56035
226185
Wyoming
56
WEST
30
Sublette
035
Greens
GREENS, KALE - ACRES HARVESTED
TOTAL
NOT SPECIFIED
(D)
(D)
56035
226186
Wyoming
56
WEST
30
Sublette
035
Greens
GREENS, KALE - OPERATIONS WITH AREA HARVESTED
TOTAL
NOT SPECIFIED
1
NaN
56035
226187
Wyoming
56
WEST
30
Sublette
035
Greens
GREENS, KALE, FRESH MARKET - ACRES HARVESTED
TOTAL
NOT SPECIFIED
(D)
(D)
56035
226188
Wyoming
56
WEST
30
Sublette
035
Greens
GREENS, KALE, FRESH MARKET - OPERATIONS WITH A...
TOTAL
NOT SPECIFIED
1
NaN
56035
226189
Wyoming
56
WEST
30
Sublette
035
Lettuce
LETTUCE - ACRES HARVESTED
TOTAL
NOT SPECIFIED
(D)
(D)
56035
226190
Wyoming
56
WEST
30
Sublette
035
Lettuce
LETTUCE - OPERATIONS WITH AREA HARVESTED
TOTAL
NOT SPECIFIED
1
NaN
56035
226191
Wyoming
56
WEST
30
Sublette
035
Lettuce
LETTUCE, FRESH MARKET - ACRES HARVESTED
TOTAL
NOT SPECIFIED
(D)
(D)
56035
226192
Wyoming
56
WEST
30
Sublette
035
Lettuce
LETTUCE, FRESH MARKET - OPERATIONS WITH AREA H...
TOTAL
NOT SPECIFIED
1
NaN
56035
226193
Wyoming
56
WEST
30
Sublette
035
Lettuce
LETTUCE, LEAF - ACRES HARVESTED
TOTAL
NOT SPECIFIED
(D)
(D)
56035
226194
Wyoming
56
WEST
30
Sublette
035
Lettuce
LETTUCE, LEAF - OPERATIONS WITH AREA HARVESTED
TOTAL
NOT SPECIFIED
1
NaN
56035
226195
Wyoming
56
WEST
30
Sublette
035
Lettuce
LETTUCE, LEAF, FRESH MARKET - ACRES HARVESTED
TOTAL
NOT SPECIFIED
(D)
(D)
56035
226196
Wyoming
56
WEST
30
Sublette
035
Lettuce
LETTUCE, LEAF, FRESH MARKET - OPERATIONS WITH ...
TOTAL
NOT SPECIFIED
1
NaN
56035
226197
Wyoming
56
WEST
30
Sublette
035
Peas
PEAS, GREEN, (EXCL SOUTHERN) - ACRES HARVESTED
TOTAL
NOT SPECIFIED
(D)
(D)
56035
226198
Wyoming
56
WEST
30
Sublette
035
Peas
PEAS, GREEN, (EXCL SOUTHERN) - OPERATIONS WITH...
TOTAL
NOT SPECIFIED
1
NaN
56035
226199
Wyoming
56
WEST
30
Sublette
035
Peas
PEAS, GREEN, (EXCL SOUTHERN), FRESH MARKET - A...
TOTAL
NOT SPECIFIED
(D)
(D)
56035
226200
Wyoming
56
WEST
30
Sublette
035
Peas
PEAS, GREEN, (EXCL SOUTHERN), FRESH MARKET - O...
TOTAL
NOT SPECIFIED
1
NaN
56035
226201
Wyoming
56
WEST
30
Sublette
035
Vegetable Totals
VEGETABLE TOTALS, IN THE OPEN - ACRES HARVESTED
TOTAL
NOT SPECIFIED
(D)
(D)
56035
226202
Wyoming
56
WEST
30
Sublette
035
Vegetable Totals
VEGETABLE TOTALS, IN THE OPEN - OPERATIONS WIT...
AREA HARVESTED, FRESH MARKET & PROCESSING
AREA HARVESTED, FRESH MARKET & PROCESSING: (0....
1
55.9
56035
226203
Wyoming
56
WEST
30
Sublette
035
Vegetable Totals
VEGETABLE TOTALS, IN THE OPEN - OPERATIONS WIT...
TOTAL
NOT SPECIFIED
1
54.2
56035
226204
Wyoming
56
WEST
30
Sublette
035
Vegetable Totals
VEGETABLE TOTALS, IN THE OPEN, FRESH MARKET - ...
TOTAL
NOT SPECIFIED
(D)
(D)
56035
226205
Wyoming
56
WEST
30
Sublette
035
Vegetable Totals
VEGETABLE TOTALS, IN THE OPEN, FRESH MARKET - ...
TOTAL
NOT SPECIFIED
1
54.2
56035
30 rows × 13 columns
In [5]:
veg_df2 = veg_df.Data_Item.apply(lambda x: pd.Series(x.split('- ')))
veg_df2.columns = ['Spec_Commodity', 'Measure']
veg_df2.head()
Out[5]:
Spec_Commodity
Measure
0
BEANS, SNAP
ACRES HARVESTED
1
BEANS, SNAP
OPERATIONS WITH AREA HARVESTED
2
BEANS, SNAP, FRESH MARKET
ACRES HARVESTED
3
BEANS, SNAP, FRESH MARKET
OPERATIONS WITH AREA HARVESTED
4
BEANS, SNAP, PROCESSING
ACRES HARVESTED
5 rows × 2 columns
In [6]:
veg_df3 = pd.merge(veg_df, veg_df2, left_index= True, right_index= True)
In [12]:
veg_df3[(veg_df3.Commodity == 'Eggplant') & (veg_df3.County == "Lancaster")]
Out[12]:
State
State_ANSI
Ag_District
Ag_District_Code
County
County_ANSI
Commodity
Data_Item
Domain
Domain_Category
Value
CV
FIPS
Spec_Commodity
Measure
118437
Nebraska
31
EAST
60
Lancaster
109
Eggplant
EGGPLANT - ACRES HARVESTED
TOTAL
NOT SPECIFIED
(D)
(D)
31109
EGGPLANT
ACRES HARVESTED
118438
Nebraska
31
EAST
60
Lancaster
109
Eggplant
EGGPLANT - OPERATIONS WITH AREA HARVESTED
TOTAL
NOT SPECIFIED
2
(H)
31109
EGGPLANT
OPERATIONS WITH AREA HARVESTED
118439
Nebraska
31
EAST
60
Lancaster
109
Eggplant
EGGPLANT, FRESH MARKET - ACRES HARVESTED
TOTAL
NOT SPECIFIED
(D)
(D)
31109
EGGPLANT, FRESH MARKET
ACRES HARVESTED
118440
Nebraska
31
EAST
60
Lancaster
109
Eggplant
EGGPLANT, FRESH MARKET - OPERATIONS WITH AREA ...
TOTAL
NOT SPECIFIED
2
(H)
31109
EGGPLANT, FRESH MARKET
OPERATIONS WITH AREA HARVESTED
171258
Pennsylvania
42
SOUTHEASTERN
90
Lancaster
071
Eggplant
EGGPLANT - ACRES HARVESTED
TOTAL
NOT SPECIFIED
15
28.2
42071
EGGPLANT
ACRES HARVESTED
171259
Pennsylvania
42
SOUTHEASTERN
90
Lancaster
071
Eggplant
EGGPLANT - OPERATIONS WITH AREA HARVESTED
TOTAL
NOT SPECIFIED
33
31.7
42071
EGGPLANT
OPERATIONS WITH AREA HARVESTED
171260
Pennsylvania
42
SOUTHEASTERN
90
Lancaster
071
Eggplant
EGGPLANT, FRESH MARKET - ACRES HARVESTED
TOTAL
NOT SPECIFIED
(D)
(D)
42071
EGGPLANT, FRESH MARKET
ACRES HARVESTED
171261
Pennsylvania
42
SOUTHEASTERN
90
Lancaster
071
Eggplant
EGGPLANT, FRESH MARKET - OPERATIONS WITH AREA ...
TOTAL
NOT SPECIFIED
33
31.7
42071
EGGPLANT, FRESH MARKET
OPERATIONS WITH AREA HARVESTED
171262
Pennsylvania
42
SOUTHEASTERN
90
Lancaster
071
Eggplant
EGGPLANT, PROCESSING - ACRES HARVESTED
TOTAL
NOT SPECIFIED
(D)
(D)
42071
EGGPLANT, PROCESSING
ACRES HARVESTED
171263
Pennsylvania
42
SOUTHEASTERN
90
Lancaster
071
Eggplant
EGGPLANT, PROCESSING - OPERATIONS WITH AREA HA...
TOTAL
NOT SPECIFIED
2
93.7
42071
EGGPLANT, PROCESSING
OPERATIONS WITH AREA HARVESTED
10 rows × 15 columns
In [14]:
veg_df3.head()
Out[14]:
State
State_ANSI
Ag_District
Ag_District_Code
County
County_ANSI
Commodity
Data_Item
Domain
Domain_Category
Value
CV
FIPS
Spec_Commodity
Measure
0
Alabama
1
BLACK BELT
40
Autauga
001
Beans
BEANS, SNAP - ACRES HARVESTED
TOTAL
NOT SPECIFIED
12
(H)
1001
BEANS, SNAP
ACRES HARVESTED
1
Alabama
1
BLACK BELT
40
Autauga
001
Beans
BEANS, SNAP - OPERATIONS WITH AREA HARVESTED
TOTAL
NOT SPECIFIED
8
31.5
1001
BEANS, SNAP
OPERATIONS WITH AREA HARVESTED
2
Alabama
1
BLACK BELT
40
Autauga
001
Beans
BEANS, SNAP, FRESH MARKET - ACRES HARVESTED
TOTAL
NOT SPECIFIED
(D)
(D)
1001
BEANS, SNAP, FRESH MARKET
ACRES HARVESTED
3
Alabama
1
BLACK BELT
40
Autauga
001
Beans
BEANS, SNAP, FRESH MARKET - OPERATIONS WITH AR...
TOTAL
NOT SPECIFIED
7
32.6
1001
BEANS, SNAP, FRESH MARKET
OPERATIONS WITH AREA HARVESTED
4
Alabama
1
BLACK BELT
40
Autauga
001
Beans
BEANS, SNAP, PROCESSING - ACRES HARVESTED
TOTAL
NOT SPECIFIED
(D)
(D)
1001
BEANS, SNAP, PROCESSING
ACRES HARVESTED
5 rows × 15 columns
In [19]:
# isolate Value column for rows that correspond to fresh market acres harvested, create boolean mask
test_series = veg_df3[veg_df3.Data_Item.str.contains("FRESH MARKET") & (veg_df3.Measure == "ACRES HARVESTED")].Value.str.contains("(D)")
# proportion of True/Total
float(np.sum(test_series))/len(test_series)
Out[19]:
0.5867018511854784
From USDA Census Report, Appendix A:
DISCLOSURE REVIEW
After tabulation and review of the aggregates, a comprehensive disclosure review was conducted. NASS is obligated to withhold, under Title 7, U.S. Code, any total that would reveal an individual’s information or allow it to be closely estimated by the public. Cell suppression was used to protect the cells that were determined to be sensitive to a disclosure of information. Farm counts are not considered sensitive and are not subject to disclosure controls.
Based on agency standards, data cells were determined to be sensitive to a disclosure of information if they violated either of two criteria rules. The threshold rule was violated if the data cell contained less than three operations. For example, if only one farmer produced turkeys in a county, NASS could not publish the county total for turkey inventory without disclosing that individual’s information. The dominance rule was violated if the distribution of the data within the cell allowed a data user to estimate any respondent’s data too closely. For example, if there are many farmers producing turkeys in a county and some of them were large enough to dominate the cell total, NASS could not publish the county total for turkey inventory without risking disclosing an individual respondent’s data. In both of these situations, the data were suppressed and a “(D)” was placed in the cell in the census publication table. These data cells were referred to as primary suppressions.
In [ ]:
# IF WANT TO USE ACRES HARVESTED: replace null code Values (such as '(D)','(Z)', etc) with 0
#veg_df3.Value = veg_df3.Value.replace(to_replace = r'([A-Z+])', value = 0, regex=True)
In [20]:
veg_df4 = veg_df3[veg_df3.Data_Item.str.contains("FRESH MARKET") & (veg_df3.Measure == "OPERATIONS WITH AREA HARVESTED")]
In [24]:
veg_df4.Value = veg_df4.Value.astype(int)
veg_df4.FIPS = veg_df4.FIPS.astype(int)
In [25]:
# replace null values with 0
veg_df4.Value = veg_df4.Value.fillna(0)
veg_df4.Value = veg_df4.Value.replace(to_replace = ',', value = '', regex=True)
In [27]:
veg_df4.head()
Out[27]:
State
State_ANSI
Ag_District
Ag_District_Code
County
County_ANSI
Commodity
Data_Item
Domain
Domain_Category
Value
CV
FIPS
Spec_Commodity
Measure
3
Alabama
1
BLACK BELT
40
Autauga
001
Beans
BEANS, SNAP, FRESH MARKET - OPERATIONS WITH AR...
TOTAL
NOT SPECIFIED
7
32.6
1001
BEANS, SNAP, FRESH MARKET
OPERATIONS WITH AREA HARVESTED
9
Alabama
1
BLACK BELT
40
Autauga
001
Broccoli
BROCCOLI, FRESH MARKET - OPERATIONS WITH AREA ...
TOTAL
NOT SPECIFIED
1
53.3
1001
BROCCOLI, FRESH MARKET
OPERATIONS WITH AREA HARVESTED
13
Alabama
1
BLACK BELT
40
Autauga
001
Carrots
CARROTS, FRESH MARKET - OPERATIONS WITH AREA H...
TOTAL
NOT SPECIFIED
2
67.1
1001
CARROTS, FRESH MARKET
OPERATIONS WITH AREA HARVESTED
17
Alabama
1
BLACK BELT
40
Autauga
001
Cucumbers
CUCUMBERS, FRESH MARKET - OPERATIONS WITH AREA...
TOTAL
NOT SPECIFIED
1
35.3
1001
CUCUMBERS, FRESH MARKET
OPERATIONS WITH AREA HARVESTED
21
Alabama
1
BLACK BELT
40
Autauga
001
Eggplant
EGGPLANT, FRESH MARKET - OPERATIONS WITH AREA ...
TOTAL
NOT SPECIFIED
1
54.5
1001
EGGPLANT, FRESH MARKET
OPERATIONS WITH AREA HARVESTED
5 rows × 15 columns
In [23]:
# check out California veggies
veg_df4[veg_df4.State == 'California'].head(50)
Out[23]:
State
State_ANSI
Ag_District
Ag_District_Code
County
County_ANSI
Commodity
Data_Item
Domain
Domain_Category
Value
CV
FIPS
Spec_Commodity
Measure
10796
California
6
CENTRAL COAST
40
Alameda
001
Beans
BEANS, SNAP, FRESH MARKET - OPERATIONS WITH AR...
TOTAL
NOT SPECIFIED
3
29.8
6001
BEANS, SNAP, FRESH MARKET
OPERATIONS WITH AREA HARVESTED
10800
California
6
CENTRAL COAST
40
Alameda
001
Beets
BEETS, FRESH MARKET - OPERATIONS WITH AREA HAR...
TOTAL
NOT SPECIFIED
10
25.7
6001
BEETS, FRESH MARKET
OPERATIONS WITH AREA HARVESTED
10804
California
6
CENTRAL COAST
40
Alameda
001
Broccoli
BROCCOLI, FRESH MARKET - OPERATIONS WITH AREA ...
TOTAL
NOT SPECIFIED
1
12.9
6001
BROCCOLI, FRESH MARKET
OPERATIONS WITH AREA HARVESTED
10808
California
6
CENTRAL COAST
40
Alameda
001
Cabbage
CABBAGE, HEAD, FRESH MARKET - OPERATIONS WITH ...
TOTAL
NOT SPECIFIED
3
31.5
6001
CABBAGE, HEAD, FRESH MARKET
OPERATIONS WITH AREA HARVESTED
10812
California
6
CENTRAL COAST
40
Alameda
001
Carrots
CARROTS, FRESH MARKET - OPERATIONS WITH AREA H...
TOTAL
NOT SPECIFIED
1
19.5
6001
CARROTS, FRESH MARKET
OPERATIONS WITH AREA HARVESTED
10816
California
6
CENTRAL COAST
40
Alameda
001
Cauliflower
CAULIFLOWER, FRESH MARKET - OPERATIONS WITH AR...
TOTAL
NOT SPECIFIED
1
17.4
6001
CAULIFLOWER, FRESH MARKET
OPERATIONS WITH AREA HARVESTED
10820
California
6
CENTRAL COAST
40
Alameda
001
Cucumbers
CUCUMBERS, FRESH MARKET - OPERATIONS WITH AREA...
TOTAL
NOT SPECIFIED
3
25.2
6001
CUCUMBERS, FRESH MARKET
OPERATIONS WITH AREA HARVESTED
10824
California
6
CENTRAL COAST
40
Alameda
001
Greens
GREENS, COLLARD, FRESH MARKET - OPERATIONS WIT...
TOTAL
NOT SPECIFIED
2
46.3
6001
GREENS, COLLARD, FRESH MARKET
OPERATIONS WITH AREA HARVESTED
10828
California
6
CENTRAL COAST
40
Alameda
001
Herbs
HERBS, FRESH CUT, FRESH MARKET - OPERATIONS WI...
TOTAL
NOT SPECIFIED
2
33.6
6001
HERBS, FRESH CUT, FRESH MARKET
OPERATIONS WITH AREA HARVESTED
10832
California
6
CENTRAL COAST
40
Alameda
001
Lettuce
LETTUCE, FRESH MARKET - OPERATIONS WITH AREA H...
TOTAL
NOT SPECIFIED
15
18.3
6001
LETTUCE, FRESH MARKET
OPERATIONS WITH AREA HARVESTED
10836
California
6
CENTRAL COAST
40
Alameda
001
Lettuce
LETTUCE, HEAD, FRESH MARKET - OPERATIONS WITH ...
TOTAL
NOT SPECIFIED
1
16.4
6001
LETTUCE, HEAD, FRESH MARKET
OPERATIONS WITH AREA HARVESTED
10840
California
6
CENTRAL COAST
40
Alameda
001
Lettuce
LETTUCE, LEAF, FRESH MARKET - OPERATIONS WITH ...
TOTAL
NOT SPECIFIED
15
23.3
6001
LETTUCE, LEAF, FRESH MARKET
OPERATIONS WITH AREA HARVESTED
10844
California
6
CENTRAL COAST
40
Alameda
001
Lettuce
LETTUCE, ROMAINE, FRESH MARKET - OPERATIONS WI...
TOTAL
NOT SPECIFIED
1
14.9
6001
LETTUCE, ROMAINE, FRESH MARKET
OPERATIONS WITH AREA HARVESTED
10848
California
6
CENTRAL COAST
40
Alameda
001
Melons
MELONS, CANTALOUP, FRESH MARKET - OPERATIONS W...
TOTAL
NOT SPECIFIED
2
23.6
6001
MELONS, CANTALOUP, FRESH MARKET
OPERATIONS WITH AREA HARVESTED
10852
California
6
CENTRAL COAST
40
Alameda
001
Onions
ONIONS, DRY, FRESH MARKET - OPERATIONS WITH AR...
TOTAL
NOT SPECIFIED
3
21.5
6001
ONIONS, DRY, FRESH MARKET
OPERATIONS WITH AREA HARVESTED
10856
California
6
CENTRAL COAST
40
Alameda
001
Onions
ONIONS, GREEN, FRESH MARKET - OPERATIONS WITH ...
TOTAL
NOT SPECIFIED
9
46.5
6001
ONIONS, GREEN, FRESH MARKET
OPERATIONS WITH AREA HARVESTED
10860
California
6
CENTRAL COAST
40
Alameda
001
Peas
PEAS, GREEN, (EXCL SOUTHERN), FRESH MARKET - O...
TOTAL
NOT SPECIFIED
1
30.4
6001
PEAS, GREEN, (EXCL SOUTHERN), FRESH MARKET
OPERATIONS WITH AREA HARVESTED
10864
California
6
CENTRAL COAST
40
Alameda
001
Peppers
PEPPERS, BELL, FRESH MARKET - OPERATIONS WITH ...
TOTAL
NOT SPECIFIED
2
26.3
6001
PEPPERS, BELL, FRESH MARKET
OPERATIONS WITH AREA HARVESTED
10868
California
6
CENTRAL COAST
40
Alameda
001
Peppers
PEPPERS, CHILE, FRESH MARKET - OPERATIONS WITH...
TOTAL
NOT SPECIFIED
4
29.2
6001
PEPPERS, CHILE, FRESH MARKET
OPERATIONS WITH AREA HARVESTED
10872
California
6
CENTRAL COAST
40
Alameda
001
Potatoes
POTATOES, FRESH MARKET - OPERATIONS WITH AREA ...
TOTAL
NOT SPECIFIED
2
29.3
6001
POTATOES, FRESH MARKET
OPERATIONS WITH AREA HARVESTED
10876
California
6
CENTRAL COAST
40
Alameda
001
Pumpkins
PUMPKINS, FRESH MARKET - OPERATIONS WITH AREA ...
TOTAL
NOT SPECIFIED
1
24
6001
PUMPKINS, FRESH MARKET
OPERATIONS WITH AREA HARVESTED
10880
California
6
CENTRAL COAST
40
Alameda
001
Squash
SQUASH, FRESH MARKET - OPERATIONS WITH AREA HA...
TOTAL
NOT SPECIFIED
10
18.8
6001
SQUASH, FRESH MARKET
OPERATIONS WITH AREA HARVESTED
10884
California
6
CENTRAL COAST
40
Alameda
001
Squash
SQUASH, SUMMER, FRESH MARKET - OPERATIONS WITH...
TOTAL
NOT SPECIFIED
8
19.7
6001
SQUASH, SUMMER, FRESH MARKET
OPERATIONS WITH AREA HARVESTED
10888
California
6
CENTRAL COAST
40
Alameda
001
Squash
SQUASH, WINTER, FRESH MARKET - OPERATIONS WITH...
TOTAL
NOT SPECIFIED
2
22.8
6001
SQUASH, WINTER, FRESH MARKET
OPERATIONS WITH AREA HARVESTED
10892
California
6
CENTRAL COAST
40
Alameda
001
Sweet Corn
SWEET CORN, FRESH MARKET - OPERATIONS WITH ARE...
TOTAL
NOT SPECIFIED
2
23.2
6001
SWEET CORN, FRESH MARKET
OPERATIONS WITH AREA HARVESTED
10896
California
6
CENTRAL COAST
40
Alameda
001
Tomatoes
TOMATOES, IN THE OPEN, FRESH MARKET - OPERATIO...
TOTAL
NOT SPECIFIED
35
19.9
6001
TOMATOES, IN THE OPEN, FRESH MARKET
OPERATIONS WITH AREA HARVESTED
10905
California
6
CENTRAL COAST
40
Alameda
001
Vegetable Totals
VEGETABLE TOTALS, IN THE OPEN, FRESH MARKET - ...
TOTAL
NOT SPECIFIED
38
19.9
6001
VEGETABLE TOTALS, IN THE OPEN, FRESH MARKET
OPERATIONS WITH AREA HARVESTED
10911
California
6
CENTRAL COAST
40
Alameda
001
Vegetables, Other
VEGETABLES, OTHER, FRESH MARKET - OPERATIONS W...
TOTAL
NOT SPECIFIED
3
36.2
6001
VEGETABLES, OTHER, FRESH MARKET
OPERATIONS WITH AREA HARVESTED
10915
California
6
CENTRAL COAST
40
Contra Costa
013
Asparagus
ASPARAGUS, FRESH MARKET - OPERATIONS WITH AREA...
TOTAL
NOT SPECIFIED
1
28.5
6013
ASPARAGUS, FRESH MARKET
OPERATIONS WITH AREA HARVESTED
10919
California
6
CENTRAL COAST
40
Contra Costa
013
Beans
BEANS, SNAP, FRESH MARKET - OPERATIONS WITH AR...
TOTAL
NOT SPECIFIED
11
29.8
6013
BEANS, SNAP, FRESH MARKET
OPERATIONS WITH AREA HARVESTED
10925
California
6
CENTRAL COAST
40
Contra Costa
013
Broccoli
BROCCOLI, FRESH MARKET - OPERATIONS WITH AREA ...
TOTAL
NOT SPECIFIED
1
12.9
6013
BROCCOLI, FRESH MARKET
OPERATIONS WITH AREA HARVESTED
10929
California
6
CENTRAL COAST
40
Contra Costa
013
Carrots
CARROTS, FRESH MARKET - OPERATIONS WITH AREA H...
TOTAL
NOT SPECIFIED
1
19.5
6013
CARROTS, FRESH MARKET
OPERATIONS WITH AREA HARVESTED
10933
California
6
CENTRAL COAST
40
Contra Costa
013
Cucumbers
CUCUMBERS, FRESH MARKET - OPERATIONS WITH AREA...
TOTAL
NOT SPECIFIED
3
25.2
6013
CUCUMBERS, FRESH MARKET
OPERATIONS WITH AREA HARVESTED
10937
California
6
CENTRAL COAST
40
Contra Costa
013
Eggplant
EGGPLANT, FRESH MARKET - OPERATIONS WITH AREA ...
TOTAL
NOT SPECIFIED
6
34.4
6013
EGGPLANT, FRESH MARKET
OPERATIONS WITH AREA HARVESTED
10941
California
6
CENTRAL COAST
40
Contra Costa
013
Garlic
GARLIC, FRESH MARKET - OPERATIONS WITH AREA HA...
TOTAL
NOT SPECIFIED
2
24
6013
GARLIC, FRESH MARKET
OPERATIONS WITH AREA HARVESTED
10945
California
6
CENTRAL COAST
40
Contra Costa
013
Herbs
HERBS, FRESH CUT, FRESH MARKET - OPERATIONS WI...
TOTAL
NOT SPECIFIED
5
33.6
6013
HERBS, FRESH CUT, FRESH MARKET
OPERATIONS WITH AREA HARVESTED
10949
California
6
CENTRAL COAST
40
Contra Costa
013
Horseradish
HORSERADISH, FRESH MARKET - OPERATIONS WITH AR...
TOTAL
NOT SPECIFIED
1
22.1
6013
HORSERADISH, FRESH MARKET
OPERATIONS WITH AREA HARVESTED
10953
California
6
CENTRAL COAST
40
Contra Costa
013
Lettuce
LETTUCE, FRESH MARKET - OPERATIONS WITH AREA H...
TOTAL
NOT SPECIFIED
9
18.3
6013
LETTUCE, FRESH MARKET
OPERATIONS WITH AREA HARVESTED
10957
California
6
CENTRAL COAST
40
Contra Costa
013
Lettuce
LETTUCE, HEAD, FRESH MARKET - OPERATIONS WITH ...
TOTAL
NOT SPECIFIED
3
16.4
6013
LETTUCE, HEAD, FRESH MARKET
OPERATIONS WITH AREA HARVESTED
10961
California
6
CENTRAL COAST
40
Contra Costa
013
Lettuce
LETTUCE, LEAF, FRESH MARKET - OPERATIONS WITH ...
TOTAL
NOT SPECIFIED
6
23.3
6013
LETTUCE, LEAF, FRESH MARKET
OPERATIONS WITH AREA HARVESTED
10965
California
6
CENTRAL COAST
40
Contra Costa
013
Lettuce
LETTUCE, ROMAINE, FRESH MARKET - OPERATIONS WI...
TOTAL
NOT SPECIFIED
1
14.9
6013
LETTUCE, ROMAINE, FRESH MARKET
OPERATIONS WITH AREA HARVESTED
10969
California
6
CENTRAL COAST
40
Contra Costa
013
Melons
MELONS, CANTALOUP, FRESH MARKET - OPERATIONS W...
TOTAL
NOT SPECIFIED
7
23.6
6013
MELONS, CANTALOUP, FRESH MARKET
OPERATIONS WITH AREA HARVESTED
10973
California
6
CENTRAL COAST
40
Contra Costa
013
Melons
MELONS, WATERMELON, FRESH MARKET - OPERATIONS ...
TOTAL
NOT SPECIFIED
4
25.5
6013
MELONS, WATERMELON, FRESH MARKET
OPERATIONS WITH AREA HARVESTED
10977
California
6
CENTRAL COAST
40
Contra Costa
013
Onions
ONIONS, DRY, FRESH MARKET - OPERATIONS WITH AR...
TOTAL
NOT SPECIFIED
9
21.5
6013
ONIONS, DRY, FRESH MARKET
OPERATIONS WITH AREA HARVESTED
10981
California
6
CENTRAL COAST
40
Contra Costa
013
Onions
ONIONS, GREEN, FRESH MARKET - OPERATIONS WITH ...
TOTAL
NOT SPECIFIED
1
46.5
6013
ONIONS, GREEN, FRESH MARKET
OPERATIONS WITH AREA HARVESTED
10985
California
6
CENTRAL COAST
40
Contra Costa
013
Peas
PEAS, CHINESE (SUGAR & SNOW), FRESH MARKET - O...
TOTAL
NOT SPECIFIED
5
33.5
6013
PEAS, CHINESE (SUGAR & SNOW), FRESH MARKET
OPERATIONS WITH AREA HARVESTED
10989
California
6
CENTRAL COAST
40
Contra Costa
013
Peppers
PEPPERS, BELL, FRESH MARKET - OPERATIONS WITH ...
TOTAL
NOT SPECIFIED
2
26.3
6013
PEPPERS, BELL, FRESH MARKET
OPERATIONS WITH AREA HARVESTED
10993
California
6
CENTRAL COAST
40
Contra Costa
013
Peppers
PEPPERS, CHILE, FRESH MARKET - OPERATIONS WITH...
TOTAL
NOT SPECIFIED
4
29.2
6013
PEPPERS, CHILE, FRESH MARKET
OPERATIONS WITH AREA HARVESTED
10997
California
6
CENTRAL COAST
40
Contra Costa
013
Pumpkins
PUMPKINS, FRESH MARKET - OPERATIONS WITH AREA ...
TOTAL
NOT SPECIFIED
5
24
6013
PUMPKINS, FRESH MARKET
OPERATIONS WITH AREA HARVESTED
11001
California
6
CENTRAL COAST
40
Contra Costa
013
Spinach
SPINACH, FRESH MARKET - OPERATIONS WITH AREA H...
TOTAL
NOT SPECIFIED
5
30.2
6013
SPINACH, FRESH MARKET
OPERATIONS WITH AREA HARVESTED
50 rows × 15 columns
In [ ]:
# Create pivot table
veg_pt = pd.pivot_table(veg_df4, values = 'Value', rows = 'FIPS', cols= 'Commodity', aggfunc = np.sum)
Lots of NaN values in the pivot table. Will convert to zeros.
In [32]:
veg_pt.head()
Out[32]:
Commodity
Artichokes
Asparagus
Beans
Beets
Broccoli
Brussels Sprouts
Cabbage
Carrots
Cauliflower
Celery
Chicory
Cucumbers
Daikon
Eggplant
Escarole & Endive
Garlic
Ginseng
Greens
Herbs
Horseradish
FIPS
1001
NaN
NaN
7
NaN
1
NaN
NaN
2
NaN
NaN
NaN
1
NaN
1
NaN
NaN
NaN
3
NaN
NaN
...
1003
NaN
NaN
21
NaN
3
NaN
NaN
1
NaN
NaN
NaN
14
NaN
3
NaN
NaN
NaN
NaN
NaN
NaN
...
1005
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
1
NaN
NaN
...
1007
NaN
NaN
2
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
2
NaN
NaN
...
1009
NaN
NaN
38
NaN
1
NaN
2
NaN
1
NaN
NaN
22
NaN
3
NaN
NaN
NaN
11
1
NaN
...
5 rows × 40 columns
In [40]:
veg_pt = veg_pt.fillna(0)
veg_pt.head()
Out[40]:
Commodity
Artichokes
Asparagus
Beans
Beets
Broccoli
Brussels Sprouts
Cabbage
Carrots
Cauliflower
Celery
Chicory
Cucumbers
Daikon
Eggplant
Escarole & Endive
Garlic
Ginseng
Greens
Herbs
Horseradish
FIPS
1001
0
0
7
0
1
0
0
2
0
0
0
1
0
1
0
0
0
3
0
0
...
1003
0
0
21
0
3
0
0
1
0
0
0
14
0
3
0
0
0
0
0
0
...
1005
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
1
0
0
...
1007
0
0
2
0
0
0
0
0
0
0
0
0
0
0
0
0
0
2
0
0
...
1009
0
0
38
0
1
0
2
0
1
0
0
22
0
3
0
0
0
11
1
0
...
5 rows × 40 columns
In [42]:
veg_pt.to_csv('vegetables.csv')
Content source: annaswigart/local-food-access
Similar notebooks: