def changeTableFormat(hist_table, table_name):
fields = [f.name for f in arcpy.ListFields(hist_table)]
# converts a table to NumPy structured array.
arr = arcpy.da.TableToNumPyArray(hist_table, fields)
# convert numpy array to pandas dataframe
df = pd.DataFrame(data=arr)
#r## remove column
del df['OBJECTID']
print df
##perform a psuedo pivot table
df = pd.melt(df,
id_vars=["LABEL"],
var_name="atlas_stco",
value_name="count")
##convert all column values to lowercase
df.columns = map(str.lower, df.columns)
#### format column in df #########################
## strip character string off all cells in column
df['atlas_stco'] = df['atlas_stco'].map(lambda x: x.strip('atlas_'))
## add zero infront of string if length is less than 2
df['atlas_stco'] = df['atlas_stco'].apply(lambda x: '{0:0>2}'.format(x))
#### add columns to table ########################
# df['yxc'] = yxc
df['acres'] = gen.getAcres(df['count'], 30)
# df['year'] = year
#### join tables to aquire the state abreviation #########
df = pd.merge(
df,
pd.read_sql_query(
'SELECT atlas_stco,atlas_name,acres_calc FROM spatial.counties;',
con=engine),
on='atlas_stco')
df['percent'] = (df.acres / df.acres_calc) * 100
print df
df.to_sql(table_name, engine, schema='counts', if_exists='replace')
def dmlPGtable(data, yxc):
#this is a sub function for addGDBTable2postgres()
value = data['global']['years_conv']
print value
count = getDF(data, yxc)
print count
acres = gen.getAcres(count, int(data['global']['res']))
print acres
series = data['global']['instance'] + '_seperate'
print series
cur = conn.cursor()
query = "INSERT INTO counts.{0}_{5}30_2008to2017_mmu5_separate (value, count, acres, series, yxc) VALUES ({1},{2},{3},'{4}','{5}')".format(
data['global']['instance'], value, count, acres, series, yxc)
print query
cur.execute(query)
conn.commit()
def changeTableFormat(data, yxc, year, table):
print('inside new', table)
# arr = arcpy.da.TableToNumPyArray(table, '*')
fields = [f.name for f in arcpy.ListFields(table)]
# converts a table to NumPy structured array.
arr = arcpy.da.TableToNumPyArray(table,fields)
# print arr
# convert numpy array to pandas dataframe
df = pd.DataFrame(data=arr)
#r## remove column
del df['OBJECTID']
print df
##perform a psuedo pivot table
df=pd.melt(df, id_vars=["LABEL"],var_name="atlas_st", value_name="count")
df.columns = map(str.lower, df.columns)
#### format column in df #########################
## strip character string off all cells in column
df['atlas_st'] = df['atlas_st'].map(lambda x: x.strip('Value_'))
## add zero infront of string if length is less than 2
df['atlas_st'] = df['atlas_st'].apply(lambda x: '{0:0>2}'.format(x))
#### add columns to table ########################
df['series'] = data['global']['instance']
df['yxc'] = yxc
df['acres'] = gen.getAcres(df['count'], 30)
df['year'] = year
#### join tables to get the state abreviation #########
df = pd.merge(df, pd.read_sql_query('SELECT atlas_st,st_abbrev FROM spatial.states;',con=engine), on='atlas_st')
print df
return df
def addGDBTable2postgresyo(raster, cy):
print 'raster:', raster
print 'cy:', cy
tablename = raster
# Execute AddField twice for two new fields
fields = [f.name for f in arcpy.ListFields(tablename)]
# converts a table to NumPy structured array.
arr = arcpy.da.TableToNumPyArray(tablename, fields)
print arr
# # convert numpy array to pandas dataframe
df = pd.DataFrame(data=arr)
df.columns = map(str.lower, df.columns)
print df
df['year'] = cy
df['acres'] = gen.getAcres(df['count'], 30)
print df
return df
def main():
arcpy.env.workspace = "C:\\Users\\Bougie\\Desktop\\Gibbs\\data\\usxp\\sa\\r2\\s21\\core\\core_s21.gdb"
engine = create_engine(
'postgresql://*****:*****@144.92.235.105:5432/usxp')
df_list = []
rasters = arcpy.ListRasters("*", "GRID")
for raster in rasters:
print(raster)
print raster.split('_')[5]
cy_dict = {
'2008to2010': 2009,
'2008to2011': 2010,
'2009to2012': 2011,
'2010to2013': 2012,
'2011to2014': 2013,
'2012to2015': 2014,
'2013to2016': 2015,
'2014to2017': 2016
}
cy = cy_dict.get(raster.split('_')[5])
print 'cy-----------------', cy
# df=addGDBTable2postgresyo(raster, cy)
tablename = raster
# Execute AddField twice for two new fields
fields = [f.name for f in arcpy.ListFields(tablename)]
# converts a table to NumPy structured array.
arr = arcpy.da.TableToNumPyArray(tablename, fields)
print arr
# # convert numpy array to pandas dataframe
df = pd.DataFrame(data=arr)
df.columns = map(str.lower, df.columns)
print df
df = df.rename(columns={'value': 'mtr'})
df['year'] = cy
print df
df['acres'] = gen.getAcres(df['count'], 30)
print df
df.to_sql('imw_{}'.format(str(cy)),
engine,
schema='counts_imw',
if_exists='replace')
df_list.append(createDFfromQuery(cy))
# ## MERGE all dataframes in list into one postgres table
df_final = pd.concat(df_list)
print 'df_final:', df_final
df_final.to_sql('merged_imw',
engine,
schema='counts_imw',
if_exists='replace')
pivotTableFromPostGres()
def getAcres(group_counts):
for key, value in group_counts.iteritems():
print key, gen.getAcres(pixel_count=value, resolution=30)