<jupyter_start><jupyter_text>TM1py:Reading DataGoing through all the different ways to get data into your Python scripts  Part 1: Reading data from a CSV fileIntroduction to [pandas](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.read_csv.html)<jupyter_code>#import pandas to get data from csv file
import pandas as pd
# pd.read_csv will store the information into a pandas dataframe called df
df = pd.read_csv('reading_data.csv')
#A pandas dataframe has lots of cool pre-built functions such as:
# print the result
df.head()
#write data to csv
df.to_csv('my_new_filePyPal.csv')
# Find all unique values for one column
df.Country.unique()<jupyter_output><empty_output><jupyter_text>Part 2: Reading data from TM1  Going through all TM1py options to load data from TM1 into our Jupyter notebook Setting up connection to TM1<jupyter_code>#import TM1py services
from TM1py.Services import TM1Service
from TM1py.Utils import Utils
#TM1 credentials
ADDRESS = "localhost"
PORT = 8009
USER = "admin"
PWD = "<PASSWORD>"
SSL = True
#Connect to the TM1 instance
tm1 = TM1Service(address=ADDRESS, port=PORT, user=USER, password=<PASSWORD>, ssl=SSL)
# Cube view used in this notbook
cube_name = 'Bike Shares'
view_name = '2014 to 2017 Counts by Day'<jupyter_output><empty_output><jupyter_text>Getting the view as map of coordinates and values- **Use Case:** Get the values with all intersections<jupyter_code># query first 5 cells from the cube view as coordinate-cell dictionary
cells = tm1.cubes.cells.execute_view(cube_name=cube_name, view_name=view_name, private=False, top=5)
cells
# print first entries from coordinates-cell dictionary instead of [Version].[Version].[Actual] returns Actual
for element_unique_names, cell in cells.items():
    # extract element names from unique-element-names
    element_names = Utils.element_names_from_element_unique_names(
        element_unique_names=element_unique_names)
    # take value from cell
    value = cell["Value"]
    print(element_names, value)<jupyter_output>('Actual', '2014-01-01', 'NYC', 'Count') 6059
('Actual', '2014-01-01', 'Chicago', 'Count') 123
('Actual', '2014-01-01', 'Washington', 'Count') 3011
('Actual', '2014-01-02', 'NYC', 'Count') 8600
('Actual', '2014-01-02', 'Chicago', 'Count') 112<jupyter_text>Getting the number of cells- **Use Case:** Check how many cells are you going to work with- **Note**: Very fast<jupyter_code>#tm1.cubes.cells.execute_view_csv or tm1.cubes.cells.execute_mdx_csv
%time
df_cellcount= tm1.cubes.cells.execute_view_cellcount(cube_name=cube_name, view_name=view_name, private=False)
df_cellcount<jupyter_output><empty_output><jupyter_text>Getting data as CSV fomat- **Use Case**: Get your data as CSV format- **Note**: Very fast<jupyter_code>#tm1.cubes.cells.execute_view_csv or tm1.cubes.cells.execute_mdx_csv
%time
csv = tm1.cubes.cells.execute_view_csv(cube_name=cube_name, view_name=view_name, private=False)
#diplay the result as CSV format
csv[0:200]
#diplay first 10 lines of the result
for line in csv.split("\r\n")[0:10]:
    print(line)
#diplay last 20 lines of the result
for line in csv.split("\r\n")[-10:]:
    print(line)<jupyter_output>2017-12-29,NYC,13759
2017-12-29,Chicago,1076
2017-12-29,Washington,3088
2017-12-30,NYC,5956
2017-12-30,Chicago,548
2017-12-30,Washington,1876
2017-12-31,NYC,6569
2017-12-31,Chicago,651
2017-12-31,Washington,1437<jupyter_text>Getting data as (pandas) dataframe- **Use Case**: Get your data as a pandas dataframe- **Note**: useful for further data analysis in python<jupyter_code>%time
df = tm1.cubes.cells.execute_view_dataframe(cube_name=cube_name, view_name=view_name, private=False)
df.head()
df.to_csv(view_name+"Pypal.csv")<jupyter_output><empty_output><jupyter_text>Getting data as (pandas) pivot dataframe - **Use Case**: Get your data as a pandas dataframe following your view structure- **Note**: useful for further data analysis in python<jupyter_code>%time
df_pivot = tm1.cubes.cells.execute_view_dataframe_pivot(cube_name=cube_name, view_name=view_name, private=False)
# print first 5 records
df_pivot.head()
# print last 5 records
df_pivot.tail()<jupyter_output><empty_output><jupyter_text>Getting data in custom JSON format- **Use Case**: Query additional information, such as:    - Cell is RuleDerived    - Cell is Consolidated    - Member properties    - Attribute Values- **Note**: very flexible. Not fast.<jupyter_code>%time
raw_json = tm1.cubes.cells.execute_view_raw(
    cube_name=cube_name, 
    view_name=view_name, 
    private=False, 
    elem_properties=["Type"],
    cell_properties=["RuleDerived", "Value"])
# print full response
raw_json
# Extract cube name from response
raw_json['Cube']<jupyter_output><empty_output><jupyter_text>Getting cell values- **Use Case**: sometimes you are only interested in the cell values. Skipping the elements in the response increases performance- **Note**: Fast and light<jupyter_code>%time
values = tm1.cubes.cells.execute_view_values(cube_name=cube_name, view_name=view_name, private=False)
# extract first ten values
first_ten = list(values)[0:10]
# print first ten values
print(first_ten)<jupyter_output>[]<jupyter_text>Getting row elements and cell values only- **Use Case**: sometimes elements in columns and titles are irrelevant. Skipping these elements in the response increases performance- **Note**: Faster than querying everything<jupyter_code>rows_and_values = tm1.cubes.cells.execute_view_rows_and_values(
    cube_name=cube_name, 
    view_name=view_name, 
    private=False,
    element_unique_names=False)
for row_elements, values_by_row in rows_and_values.items():
    print(row_elements, values_by_row)<jupyter_output><empty_output><jupyter_text>Getting data with attributes valuesTo get attributes values, you will need to get the data from an MDX query<jupyter_code>mdx = """
    WITH MEMBER [Bike Shares Measure].[City Alias] AS [}ElementAttributes_City].([}ElementAttributes_City].[City Alias])
    SELECT 
    NON EMPTY {[Date].Members}*{TM1SubsetAll([City])} ON ROWS, 
    NON EMPTY {[Bike Shares Measure].[Count], [Bike Shares Measure].[City Alias] } ON COLUMNS 
    FROM [Bike Shares] 
    WHERE ([Version].[Actual],[Bike Shares Measure].[Count])"""

# get table'ish dataframe
data = tm1.cubes.cells.execute_mdx(mdx)
#Build pandas dataframe
df = Utils.build_pandas_dataframe_from_cellset(data, multiindex=False)
print(df)
# get pivot dataframe
pivot = tm1.cubes.cells.execute_mdx_dataframe_pivot(mdx)
print(pivot)<jupyter_output>Values          
Bike Shares Measure       City Alias     Count
Date       City                               
2014       Chicago                     2454634
           NYC         New York City   8081216
           Total City                 13449000
           Washington                  2913150
2014-01    Chicago                       25076
           NYC         New York City    300400
           Total City                   438173
           Washington                   112697
2014-01-01 Chicago                         123
           NYC         New York City      6059
           Total City                     9193
           Washington                     3011
2014-01-02 Chicago                         112
           NYC         New York City      8600
           Total City                    12028
           Washington                     3316
2014-01-03 Chicago                           6
           NYC         New York City      1144
           To[...]<jupyter_text>Part 3: Reading from APIs Getting data from a web service, introduction to a JSON fileThe code below has been extracted from this article: [Upload weather data from web service](https://code.cubewise.com/tm1py-help-content/upload-weather-data-from-web-services-into-planning-analytics)<jupyter_code>#library for HTTP / REST Request against Webservices
import requests
#standard library for JSON parsing, manipulation
import json
# Define constants
STATION = 'GHCND:USW00014732'
FROM, TO = '2017-01-01', '2017-01-04'
HEADERS = {"token": '<KEY>'}
url = 'https://www.ncdc.noaa.gov/cdo-web/api/v2/data?' \
      'datasetid=GHCND&' \
      'startdate=' + FROM + '&' \
      'enddate=' + TO + '&' \
      'limit=1000&' \
      'datatypeid=TMIN&' \
      'datatypeid=TAVG&' \
      'datatypeid=TMAX&' \
      'stationid=' + STATION

print(url)
#Execute the URL against the NOAA API to get the results
#Prettyprint first three items from result-set

response = requests.get(url, headers=HEADERS).json()
results = response["results"]   

print(json.dumps(results[0:3], indent=2))
#Rearrange the data
cells = dict()

for record in results:
    value = record['value'] / 10
    coordinates = ("Actual", record['date'][0:10], "NYC", record['datatype'])
    cells[coordinates] = value
for coordinate, value in cells.items():
    print(coordinate, value)
# Write values back to TM1
tm1.cubes.cells.write_values("Weather Data", cells)<jupyter_output><empty_output>
StarcoderdataJupyterStructuredCleanDedup