In [1]:
import pandas as pd
import numpy as np
columns = ["State","State_ANSI", "Ag_District", "Ag_District_Code", "County",
"County_ANSI","Commodity","Data_Item", "Domain", "Domain_Category", "Value", "CV", "FIPS"]
path = 'fruit_by_county.csv'
fruit_df = pd.read_csv(path, names=columns, skiprows=1)
In [2]:
# make strings to concatenate into FIPS codes
fruit_df.State_ANSI = fruit_df.State_ANSI.astype(str)
fruit_df.County_ANSI = fruit_df.County_ANSI.astype(str)
# more readable in title case than all caps
fruit_df.State = fruit_df.apply(lambda x: x.State.title(), axis = 1)
fruit_df.County = fruit_df.apply(lambda x: x.County.title(), axis = 1)
fruit_df.Commodity = fruit_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
fruit_df.County_ANSI = fruit_df.apply(prepend_zeros, axis=1)
In [5]:
# index by combined ids
fruit_df.FIPS = fruit_df.apply(lambda row: ''.join([row.State_ANSI, row.County_ANSI]), axis = 1)
fruit_df.head()
Out[5]:
In [6]:
fruit_df2 = fruit_df.Data_Item.apply(lambda x: pd.Series(x.split('- ')))
fruit_df2.columns = ['Spec_Commodity', 'Measure']
fruit_df2.head()
Out[6]:
In [7]:
fruit_df3 = pd.merge(fruit_df, fruit_df2, left_index= True, right_index= True)
In [8]:
fruit_df3.head()
Out[8]:
In [14]:
# isolate Value column for rows that correspond to fresh market acres harvested, create boolean mask
test_series = fruit_df3[fruit_df3.Measure == "ACRES BEARING"].Value.str.contains("(D)")
# proportion of True/Total
float(np.sum(test_series))/len(test_series)
Out[14]:
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
#fruit_df3.Value = fruit_df3.Value.replace(to_replace = r'([A-Z+])', value = 0, regex=True)
In [22]:
fruit_df4 = fruit_df3[fruit_df3.Measure == "OPERATIONS WITH AREA BEARING"]
fruit_df4.head()
Out[22]:
In [24]:
# replace null values with 0
fruit_df4.Value = fruit_df4.Value.fillna(0)
fruit_df4.Value = fruit_df4.Value.replace(to_replace = ',', value = '', regex=True)
In [25]:
fruit_df4.Value = fruit_df4.Value.astype(int)
fruit_df4.FIPS = fruit_df4.FIPS.astype(int)
In [26]:
fruit_df4.head()
Out[26]:
In [27]:
# check out California fruitgies
fruit_df4[fruit_df4.State == 'California'].head(50)
Out[27]:
In [28]:
# Create pivot table
fruit_pt = pd.pivot_table(fruit_df4, values = 'Value', rows = 'FIPS', cols= 'Commodity', aggfunc = np.sum)
Lots of NaN values in the pivot table. Will convert to zeros.
In [29]:
fruit_pt.head()
Out[29]:
In [30]:
fruit_pt = fruit_pt.fillna(0)
fruit_pt.head()
Out[30]:
In [31]:
fruit_pt.to_csv('fruit.csv')