#!/usr/bin/env python
# coding: utf-8

# ## Data Management
# 
# In this guide you will learn how to load different data files into DataFrames and how to interact with the CARTO platform to upload DataFrames into tables and download tables or SQL queries into DataFrames.
# 
# CARTOframes is built on top of [Pandas](https://pandas.pydata.org/) and [GeoPandas](https://geopandas.org/). Therefore, it's compatible with all the data formats supported in those projects like GeoJSON, Shapefile, CSV, etc.
# 
# There are two main concepts we should know before continuing with the guide:
# - A [DataFrame](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.html) is a two-dimensional data structure for generic data. It can be thought of as a table with rows and columns. It's composed of [Series](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.Series.html) objects, which are one-dimensional data structures.
# - A [GeoDataFrame](https://geopandas.org/data_structures.html#geodataframe) is a DataFrame with an extra geometry column. This geometry column is a [GeoSeries](https://geopandas.org/data_structures.html#geoseries) object.
# 
# Every time we manage geographic data, a GeoDataFrame should be used. In case a DataFrame with an encoded geometry column is used (WKB, WKT, etc.), every method contains a `geom_col` param to provide the name of that column and decode the geometry internally.
# 
# For further learning you can checkout the [Data Management examples](/developers/cartoframes/examples/#example-data-management).

# ### Read a GeoJSON file
# 
# This is how to load geographic data from a GeoJSON file using [GeoPandas](https://geopandas.org/). To read pure JSON files check this [example](/developers/cartoframes/examples/#example-read-a-json-file).

# In[1]:


from geopandas import read_file

gdf = read_file('https://libs.cartocdn.com/cartoframes/samples/starbucks_brooklyn_geocoded.geojson')
gdf.head()


# ### Read a Shapefile
# 
# Shapefile is a complex format, compared to CSV or GeoJSON. To learn more about this format check [GeoPandas documentation](https://geopandas.org/io.html#reading-spatial-data).

# In[2]:


from geopandas import read_file

gdf = read_file('https://libs.cartocdn.com/cartoframes/samples/starbucks_brooklyn_geocoded.zip')
gdf.head()


# ### Read a CSV file

# **Compute geometry from longitude and latitude**

# In[3]:


from pandas import read_csv
from geopandas import GeoDataFrame, points_from_xy

df = read_csv('https://libs.cartocdn.com/cartoframes/samples/sf_incidents.csv')

gdf = GeoDataFrame(df, geometry=points_from_xy(df['longitude'], df['latitude']))
gdf.head()


# **Compute geometry from WKT/WKB**

# In[4]:


from pandas import read_csv
from geopandas import GeoDataFrame
from cartoframes.utils import decode_geometry

df = read_csv('https://libs.cartocdn.com/cartoframes/samples/starbucks_brooklyn_geocoded.csv')

gdf = GeoDataFrame(df, geometry=decode_geometry(df['the_geom']))
gdf.head()


# ### Read data from a CARTO table
# 
# _Note: You'll need your [CARTO Account](https://carto.com/signup) credentials to perform this action._

# In[5]:


from cartoframes.auth import set_default_credentials

set_default_credentials('cartoframes')


# In[6]:


from cartoframes import read_carto

gdf = read_carto('starbucks_brooklyn')
gdf.head()


# ### Read data from a CARTO SQL Query
# 
# _Note: You'll need your [CARTO Account](https://carto.com/signup) credentials to perform this action._

# In[7]:


from cartoframes.auth import set_default_credentials

set_default_credentials('cartoframes')


# In[8]:


from cartoframes import read_carto

gdf = read_carto("SELECT * FROM starbucks_brooklyn WHERE revenue > 1200000")
gdf.head()


# ### Upload data to CARTO
# 
# _Note: You'll need your [CARTO Account](https://carto.com/signup) credentials to perform this action._

# In[9]:


from cartoframes.auth import set_default_credentials

set_default_credentials('creds.json')


# In[10]:


from cartoframes import to_carto

to_carto(gdf, 'starbucks_brooklyn_filtered', if_exists='replace')