<SYSTEM_TASK:>
Given the following text description, write Python code to implement the functionality described below step by step
<END_TASK>
<USER_TASK:>
Description:
 This is the notebook for the python pandas dataframe course
The idea of this notebook is to show the power of working with pandas dataframes
Motivation
We usually work with tabular data
We should not handle them with bash commands like
Step1:  Series definition
Series is a one-dimensional labeled array capable of holding any data type
The axis labels are collectively referred to as the index
This is the basic idea of how to create a Series dataframe
Step2:  Create a Series array from a numpy array
If data is an ndarray, index must be the same length as data. 
If no index is passed, one will be created having values [0, ..., len(data) - 1]
Not including index
Step3:  Including index
Step4:  From scalar value
If data is a scalar value, an index must be provided
The value will be repeated to match the length of index
Step5:  Create a Series array from a python dictionary
Step6:  DataFrame definition
DataFrame is a 2-dimensional labeled data structure with columns of potentially different types (see also Panel - 3-dimensional array).
You can think of it like a spreadsheet or SQL table, or a dict of Series objects. 
It is generally the most commonly used pandas object. 
Like Series, DataFrame accepts many different kinds of input
Step7:  From dict of Series or dicts
Step8:  From dict of ndarrays / lists
The ndarrays must all be the same length. 
If an index is passed, it must clearly also be the same length as the arrays. 
If no index is passed, the result will be range(n), where n is the array length.
Step9:  From structured or record array
The ndarrays must all be the same length. If an index is passed, it must clearly also be the same length as the arrays. 
If no index is passed, the result will be range(n), where n is the array length.
Step10:  From a list of dicts
Step11:  <a id=exercise1></a>
Exercise 1
Step12:  <a id=io></a>
I/O
Reading from different sources into a DataFrame
Most of the times any study starts with an input file containing some data rather than having a python list or dictionary.
Here we present three different data sources and how to read them
Step13:  CSV.BZ2 (less storage, slower when reading because of decompression)
Step14:  Reading the full catalog at once (if the file is not very large)
Step15:  DataFrame.describe
Step16:  FITS file
Step17:  FITS file created using the same query as the CSV file
Step19:  - From Database
Step20:  Write to csv file
Step21:  Advanced example
Step22:  Opening file with the with method
Creating a file object using read_csv method
Looping by chunks using enumerate in order to also have the chunk number
Step23:  DataFrame plot method (just for curiosity!)
Step24:  <a id=selecting></a>
SELECTING AND SLICING
The idea of this section is to show how to slice and get and set subsets of pandas objects
The basics of indexing are as follows
Step25:  Select a column
Step26:  Select a row by index
Step27:  Select a row by integer location
Step28:  Slice rows
Step29:  Select rows by boolean vector
Step30:  Recap
Step31:  DO NOT LOOP THE PANDAS DATAFRAME IN GENERAL!
Step32:  <a id=exercise2></a>
Exercise 2
Step33:  <a id=merging></a>
Merge, join, and concatenate
https
Step34:  Using append method
Step35:  Note
Step36:  This is also a valid argument to DataFrame.append
Step37:  Mixing dimensions
Step38:  <a id=exercise3></a>
Exercise 3
Step39:  Merge method
Step40:  We are going to unset the index and rename the columns in order to use the "on" argument
Step41:  Now we have everything ready to make the JOINs
Step42:  <a id=functions></a>
More functions
Looping a dataframe (iterrows)
Step43:  FITS files
fitsio
And working by chunks
Step44:  .values DataFrame attribute
Some scipy functions do not allow to use pandas dataframe as arguments and therefore it is useful to use the values atribute, which is the numpy representation of NDFrame
The dtype will be a lower-common-denominator dtype (implicit upcasting); that is to say if the dtypes (even of numeric types) are mixed, the one that accommodates all will be chosen. Use this with care if you are not dealing with the blocks.
View vs. Copy
https
Step45:  Necessary to "modify" the file in order to convert it into a standard csv file, e.g.
<ASSISTANT_TASK:>
Python Code:
# Import libraries
import pandas as pd
import numpy as np
Explanation: This is the notebook for the python pandas dataframe course
The idea of this notebook is to show the power of working with pandas dataframes
Motivation
We usually work with tabular data
We should not handle them with bash commands like: for, split, grep, awk, etc...
And pandas is a very nice tool to handle this kind of data.
Welcome to Pandas!
Definition of pandas:
Python package providing fast, flexible, and expressive data structures designed to make working with “relational” or “labeled” data both easy and intuitive. 
It aims to be the fundamental high-level building block for doing practical, real world data analysis in Python. 
Additionally, it has the broader goal of becoming the most powerful and flexible open source data analysis / manipulation tool available in any language.
More information about pandas: http://pandas.pydata.org/pandas-docs/stable/
Contents of the course:
Know your data:
Dimensionality: Series or DataFrame
Index
Some examples
Exercise 1: Selecting pandas structure
I/O:
Reading: CSV, FITS, SQL
Writing: CSV
Advanced example: Reading and writing CSV files by chunks
Selecting and slicing:
loc. & iloc.
Advanced example: Estimate a galaxy property for a subset of galaxies using boolean conditions
Exercise 2: Estimate another galaxy property
Merge, join, and concatenate:
Exercise 3: Generate a random catalog using the concat method
Example: Merging dataframes using the merge method
More functions:
Loop a dataframe (itertuples and iterows)
Sort
Sample
Reshape: pivot, stack, unstack, etc.
Caveats and technicalities:
Floating point limitations
.values
FITS chunks
View or copy
Wrong input example
Some useful information
Ten minutes to pandas:
https://pandas.pydata.org/pandas-docs/stable/10min.html
Pandas cookbook:
https://pandas.pydata.org/pandas-docs/stable/cookbook.html
Nice pandas course:
https://www.datacamp.com/community/tutorials/pandas-tutorial-dataframe-python#gs.=B6Dr74
Multidimensional dataframes, xarray: 
http://xarray.pydata.org/en/stable/
Tips & Tricks
https://www.dataquest.io/blog/jupyter-notebook-tips-tricks-shortcuts/
<a id=know></a>
Know your data
Very important to (perfectly) know your data: structure, data type, index, relation, etc. (see Pau's talk for a much better explanation ;)
Dimensionality:
- 1-D: Series; e.g.
    - Solar planets: [Mercury, Venus, Earth, Mars, Jupiter, Saturn, Uranus, Neptune]
    - Set of astronomical objects and when they were observed:
        [[NGC1952, 2012-05-01],
         [NGC224, 2013-01-23],
         [NGC5194, 2014-02-13]]
- 2-D: DataFrame; e.g (more business oriented):
    - 3 months of sales information for 3 fictitious companies:
        sales = [{'account': 'Jones LLC', 'Jan': 150, 'Feb': 200, 'Mar': 140},
                 {'account': 'Alpha Co',  'Jan': 200, 'Feb': 210, 'Mar': 215},
                 {'account': 'Blue Inc',  'Jan': 50,  'Feb': 90,  'Mar': 95 }]
Index
It is the value (~key) we use as a reference for each element. (Note: It does not have to be unique)
Most of the data contain at least one index
End of explanation
solar_planets = ['Mercury','Venus','Earth','Mars','Jupiter','Saturn','Uranus','Neptune']
splanets = pd.Series(solar_planets)
# Tips and tricks
# To access the Docstring for quick reference on syntax use ? before:
#?pd.Series()
splanets
splanets.index
Explanation: Series definition
Series is a one-dimensional labeled array capable of holding any data type
The axis labels are collectively referred to as the index
This is the basic idea of how to create a Series dataframe:
s = pd.Series(data, index=index)
where data can be:
- list
- ndarray
- python dictionary
- scalar
and index is a list of axis labels
Create a Series array from a list
If no index is passed, one will be created having values [0, ..., len(data) - 1]
End of explanation
s1 = pd.Series(np.random.randn(5))
s1
s1.index
Explanation: Create a Series array from a numpy array
If data is an ndarray, index must be the same length as data. 
If no index is passed, one will be created having values [0, ..., len(data) - 1]
Not including index:
End of explanation
s2 = pd.Series(np.random.randn(5), index=['a', 'b', 'c', 'd', 'e'])
s2
s2.index
Explanation: Including index
End of explanation
s3 = pd.Series(5., index=['a', 'b', 'c', 'd', 'e'])
s3
s3.index
Explanation: From scalar value
If data is a scalar value, an index must be provided
The value will be repeated to match the length of index
End of explanation
d = {'a' : 0., 'b' : 1., 'c' : 2.}
sd = pd.Series(d)
sd
Explanation: Create a Series array from a python dictionary
End of explanation
sales = [{'account': 'Jones LLC', 'Jan': 150, 'Feb': 200, 'Mar': 140},
                 {'account': 'Alpha Co',  'Jan': 200, 'Feb': 210, 'Mar': 215},
                 {'account': 'Blue Inc',  'Jan': 50,  'Feb': 90,  'Mar': 95 }]
df = pd.DataFrame(sales)
df
df.info()
df.index
df = df.set_index('account')
df
Explanation: DataFrame definition
DataFrame is a 2-dimensional labeled data structure with columns of potentially different types (see also Panel - 3-dimensional array).
You can think of it like a spreadsheet or SQL table, or a dict of Series objects. 
It is generally the most commonly used pandas object. 
Like Series, DataFrame accepts many different kinds of input:
Dict of 1D ndarrays, lists, dicts, or Series
2-D numpy.ndarray
Structured or record ndarray
A Series
Another DataFrame
From a list of dictionaries
End of explanation
d = {'one' : pd.Series([1., 2., 3.], index=['a', 'b', 'c']),
     'two' : pd.Series([1., 2., 3., 4.], index=['a', 'b', 'c', 'd'])}
df = pd.DataFrame(d)
df
df.info()
pd.DataFrame(d, index=['d', 'b', 'a'])
df.index
df.columns
Explanation: From dict of Series or dicts
End of explanation
d = {'one' : [1., 2., 3., 4.], 'two' : [4., 3., 2., 1.]}
pd.DataFrame(d)
pd.DataFrame(d, index=['a', 'b', 'c', 'd'])
Explanation: From dict of ndarrays / lists
The ndarrays must all be the same length. 
If an index is passed, it must clearly also be the same length as the arrays. 
If no index is passed, the result will be range(n), where n is the array length.
End of explanation
data = np.random.random_sample((5, 5))
data
df = pd.DataFrame(data)
df
# Add index
df = pd.DataFrame(data,index = ['a','b','c','d','e'])
df
# Add column names
df = pd.DataFrame(data, index = ['a','b','c','d','e'], columns = ['ra', 'dec','z_phot','z_true','imag'])
df
Explanation: From structured or record array
The ndarrays must all be the same length. If an index is passed, it must clearly also be the same length as the arrays. 
If no index is passed, the result will be range(n), where n is the array length.
End of explanation
data2 = [{'a': 1, 'b': 2}, {'a': 5, 'b': 10, 'c': 20}]
pd.DataFrame(data2)
pd.DataFrame(data2, index=['first', 'second'])
pd.DataFrame(data2, columns=['a', 'b'])
Explanation: From a list of dicts
End of explanation
#Few galaxies with some properties: id, ra, dec, magi
galaxies = [
    {'id' : 1, 'ra' : 4.5, 'dec' : -55.6, 'magi' : 21.3},
    {'id' : 3, 'ra' : 23.5, 'dec' : 23.6, 'magi' : 23.3},
    {'id' : 25, 'ra' : 22.5, 'dec' : -0.3, 'magi' : 20.8},
    {'id' : 17, 'ra' : 33.5, 'dec' : 15.6, 'magi' : 24.3}   
]
# %load -r 1-19 solutions/06_01_pandas.py
Explanation: <a id=exercise1></a>
Exercise 1: Selecting pandas structure
Given a few galaxies with some properties ['id', 'ra', 'dec', 'magi'], choose which pandas structure to use and its index:
End of explanation
filename = '../resources/galaxy_sample.csv'
!head -30 ../resources/galaxy_sample.csv
Explanation: <a id=io></a>
I/O
Reading from different sources into a DataFrame
Most of the times any study starts with an input file containing some data rather than having a python list or dictionary.
Here we present three different data sources and how to read them: two file formats (CSV and FITS) and a database connection.
Advanced: More and more frequently the amount of data to handle is larger and larger (Big Data era) and therefore files are huge. This is why we strongly recommend to always program by chunks (sometimes it is mandatory and also it is not straight forward to implement).
- From a CSV (Comma Separated Value) file:
https://pandas.pydata.org/pandas-docs/stable/generated/pandas.read_csv.html
Reading the full catalog at once (if the file is not very large)
CSV file created using the following query (1341.csv.bz2):
SELECT unique_gal_id, ra_gal, dec_gal, z_cgal, z_cgal_v, lmhalo, (mr_gal - 0.8 * (atan(1.5 * z_cgal)- 0.1489)) AS abs_mag, gr_gal AS color, (des_asahi_full_i_true - 0.8 * (atan(1.5 * z_cgal)- 0.1489)) AS app_mag FROM micecatv2_0_view TABLESAMPLE (BUCKET 1 OUT OF 512)
End of explanation
filename_bz2 = '../resources/galaxy_sample.csv.bz2'
!head ../resources/galaxy_sample.csv.bz2
Explanation: CSV.BZ2 (less storage, slower when reading because of decompression)
End of explanation
# Field index name (known a priori from the header or the file description)
unique_gal_id_field = 'unique_gal_id'
galaxy_sample = pd.read_csv(filename, sep=',', index_col = unique_gal_id_field, comment='#', na_values = '\\N')
galaxy_sample.head()
galaxy_sample.tail()
Explanation: Reading the full catalog at once (if the file is not very large)
End of explanation
galaxy_sample.describe()
galaxy_sample.info()
galaxy_sample_bz2 = pd.read_csv(filename_bz2, sep=',', index_col = unique_gal_id_field, comment='#', na_values = r'\N')
galaxy_sample_bz2.head()
Explanation: DataFrame.describe:
Generates descriptive statistics that summarize the central tendency, dispersion and shape of a dataset’s distribution, excluding NaN values.
End of explanation
from astropy.table import Table
Explanation: FITS file:
Pandas does not read directly FITS files so it is necessary to make some "convertion"
We have found 2 different approaches:
Table method from astropy pyfits
fitsio (see "Caveats and technicalities" section below)
Not easy to read it by chunks (see also "Caveats and technicalities" section below)
Note: we strongly recommend to use CSV.BZ2!
Using astropy (or pyfits)
This method does not support "by chunks" and therefore you have to read it all at once
End of explanation
filename = '../resources/galaxy_sample.fits'
#?Table.read()
data = Table.read(filename)
type(data)
df = data.to_pandas()
df.head()
df = df.set_index('unique_gal_id')
df.head()
df.shape
df.values.dtype
df.info()
Explanation: FITS file created using the same query as the CSV file:
End of explanation
# For PostgreSQL access
from sqlalchemy.engine import create_engine
# Text wrapping
import textwrap
# Database configuration parameters
#db_url = '{scheme}://{user}:{password}@{host}/{database}'
db_url = 'sqlite:///../resources/pandas.sqlite'
sql_sample = textwrap.dedent(\
SELECT *
FROM micecatv1
WHERE ABS(ra_mag-ra) > 0.05
)
index_col = 'id'
# Create database connection
engine = create_engine(db_url)
df = pd.read_sql(sql_sample, engine,index_col = 'id')
df.head()
Explanation: - From Database:
https://pandas.pydata.org/pandas-docs/stable/generated/pandas.read_sql.html
End of explanation
outfile = '../resources/micecatv1_sample1.csv'
with open(outfile, 'w') as f_out:
    df.to_csv(f_out,
              columns = ['ra', 'dec','ra_mag','dec_mag'],
              index=True,
              header=True
             )
Explanation: Write to csv file:
End of explanation
filename = '../resources/galaxy_sample.csv'
outfile = '../resources/galaxy_sample_some_columns.csv'
# chunk size
gal_chunk = 100000
# Field index name (known a priori from the header or the file description)
unique_gal_id_field = 'unique_gal_id'
Explanation: Advanced example: Reading and writing by chunks
End of explanation
with open(filename, 'r') as galaxy_fd, open (outfile, 'w') as f_out:
    galaxy_sample_reader = pd.read_csv(
        galaxy_fd, 
        sep=',', 
        index_col = unique_gal_id_field, 
        comment='#', 
        na_values = '\\N', 
        chunksize=gal_chunk
    )
    for chunk, block in enumerate(galaxy_sample_reader):
        print(chunk)
        # In order not to write n chunk times the header (HELP PAU!)
        block.to_csv(f_out, 
            columns = ['ra_gal','dec_gal','z_cgal_v'], 
            index=True, 
            header= chunk==0, 
            mode='a'
       )
block.head()
block.tail(3)
Explanation: Opening file with the with method
Creating a file object using read_csv method
Looping by chunks using enumerate in order to also have the chunk number
End of explanation
# DataFrame plot method
%matplotlib inline
import matplotlib.pyplot as plt
block['lmhalo'].plot.hist(bins=100, logy = True)
plt.show()
Explanation: DataFrame plot method (just for curiosity!)
End of explanation
# Same dataframe as before
filename='../resources/galaxy_sample.csv.bz2'
galaxy_sample = pd.read_csv(filename, sep=',', index_col = unique_gal_id_field, comment='#', na_values = r'\N')
galaxy_sample.head()
Explanation: <a id=selecting></a>
SELECTING AND SLICING
The idea of this section is to show how to slice and get and set subsets of pandas objects
The basics of indexing are as follows:
| Operation                      | Syntax           | Result        |
|--------------------------------|------------------|---------------|
| Select column                  | df[column label] | Series        |
| Select row by index            | df.loc[index]    | Series        |
| Select row by integer location | df.iloc[pos]     | Series        |
| Slice rows                     | df[5:10]         | DataFrame     |
| Select rows by boolean vector  | df[bool_vec]     | DataFrame     |
End of explanation
galaxy_sample['ra_gal'].head()
type(galaxy_sample['dec_gal'])
galaxy_sample[['ra_gal','dec_gal','lmhalo']].head()
Explanation: Select a column
End of explanation
galaxy_sample.loc[28581888]
type(galaxy_sample.loc[28581888])
Explanation: Select a row by index
End of explanation
galaxy_sample.iloc[0]
type(galaxy_sample.iloc[0])
Explanation: Select a row by integer location
End of explanation
galaxy_sample.iloc[3:7]
galaxy_sample[3:7]
type(galaxy_sample.iloc[3:7])
Explanation: Slice rows
End of explanation
# Boolean vector
(galaxy_sample['ra_gal'] < 45).tail()
type(galaxy_sample['ra_gal'] < 45)
galaxy_sample[galaxy_sample['ra_gal'] < 45].head()
# redshift shell
galaxy_sample[(galaxy_sample.z_cgal <= 0.2) | (galaxy_sample.z_cgal >= 1.0)].head()
galaxy_sample[(galaxy_sample.z_cgal <= 1.0) & (galaxy_sample.index.isin([5670656,13615360,3231232]))]
galaxy_sample[(galaxy_sample['ra_gal'] < 1.) & (galaxy_sample['dec_gal'] < 1.)][['ra_gal','dec_gal']].head()
Explanation: Select rows by boolean vector: 
The operators are: | for or, & for and, and ~ for not. These must be grouped by using parentheses.
End of explanation
galaxy_sample.tail(10)
# Splitting the galaxies
# Boolean mask
has_disk_mask = (galaxy_sample['color']-0.29+0.03*galaxy_sample['abs_mag'] < 0)
has_disk_mask.tail(10)
print (len(has_disk_mask))
print (type(has_disk_mask))
# Counting how many spirals
n_spiral = has_disk_mask.sum()
# Counting how many ellipticals
n_elliptical = ~has_disk_mask.sum()
galaxy_sample[has_disk_mask].count()
galaxy_sample[has_disk_mask]['hubble_type'] = 'Spiral'
# It did not add any column! It was working in a view!
galaxy_sample.tail(10)
# This is the proper way of doing it if one wants to add another column
galaxy_sample.loc[has_disk_mask, 'hubble_type'] = 'Spiral'
galaxy_sample.loc[~has_disk_mask, 'hubble_type'] = 'Elliptical'
galaxy_sample.tail(10)
# We can use the numpy where method to do the same:
galaxy_sample['color_type'] = np.where(has_disk_mask, 'Blue', 'Red')
galaxy_sample.tail(10)
# The proper way would be to use a boolean field
galaxy_sample['has_disk'] = has_disk_mask
galaxy_sample.tail(10)
galaxy_sample.loc[~has_disk_mask, 'disk_length'] = 0.
galaxy_sample.loc[has_disk_mask, 'disk_length'] = np.fabs(
                np.random.normal(
                    0., scale=0.15, size=n_spiral
                )
            )
Explanation: Recap:
loc works on labels in the index.
iloc works on the positions in the index (so it only takes integers).
Advanced example: estimate the size of the disk (disk_length) for a set of galaxies
In this exercise we are going to use some of the previous examples. 
Also we are going to introduce how to add a column and some other concepts
We split the galaxies into two different populations, Ellipticals and Spirals, depending on the their color and absolute magnitude:
    if color - 0.29 + 0.03 * abs_mag &lt; 0 then Spiral
     else then Elliptical
How many galaxies are elliptical or spirals?
Elliptical galaxies do not have any disk (and therefore disk_length = 0).
The disk_length for spiral galaxies follows a normal distribution with mean = 0 and sigma = 0.15 (in arcsec). In addition, the minimum disk_length for a spiral galaxy is 1.e-3.
End of explanation
galaxy_sample.tail(10)
# Minimum value for disk_length for spirals
dl_min = 1.e-4;
disk_too_small_mask = has_disk_mask & (galaxy_sample['disk_length'] < dl_min)
disk_too_small_mask.sum()
galaxy_sample.loc[disk_too_small_mask, 'disk_length'].head()
galaxy_sample.loc[disk_too_small_mask, 'disk_length'] = dl_min
galaxy_sample.loc[disk_too_small_mask, 'disk_length'].head()
galaxy_sample.tail(10)
Explanation: DO NOT LOOP THE PANDAS DATAFRAME IN GENERAL!
End of explanation
# %load -r 20-102 solutions/06_01_pandas.py
Explanation: <a id=exercise2></a>
Exercise 2: Estimate another galaxy property
What is the mean value and the standard deviation of the disk_length for spiral galaxies (Tip: use the .mean() and .std() methods)
Estimate the bulge_length for elliptical galaxies. The bulge_length depends on the absolute magnitude in the following way:
bulge_length = exp(-1.145 - 0.269 * (abs_mag - 23.))
How many galaxies have bulge_lenth > 1.0?  
In our model the maximum bulge_length for an elliptical galaxy is 0.5 arcsec.
What is the mean value and the standard deviation of the bulge_length for elliptical galaxies. And for ellipticals with absolute magnitude brighter than -20?
End of explanation
df1 = pd.DataFrame(
    {'A': ['A0', 'A1', 'A2', 'A3'],
     'B': ['B0', 'B1', 'B2', 'B3'],
     'C': ['C0', 'C1', 'C2', 'C3'],
     'D': ['D0', 'D1', 'D2', 'D3']},
    index=[0, 1, 2, 3]
)
df2 = pd.DataFrame(
    {'A': ['A4', 'A5', 'A6', 'A7'],
     'B': ['B4', 'B5', 'B6', 'B7'],
     'C': ['C4', 'C5', 'C6', 'C7'],
     'D': ['D4', 'D5', 'D6', 'D7']},
    index=[4, 5, 6, 7]
) 
df3 = pd.DataFrame(
    {'A': ['A8', 'A9', 'A10', 'A11'],
     'B': ['B8', 'B9', 'B10', 'B11'],
     'C': ['C8', 'C9', 'C10', 'C11'],
     'D': ['D8', 'D9', 'D10', 'D11']},
    index=[8, 9, 10, 11]
)
frames = [df1, df2, df3]
result = pd.concat(frames)
result
# Multiindex
result = pd.concat(frames, keys=['x', 'y','z'])
result
result.index
result.loc['y']
df4 = pd.DataFrame(
    {'B': ['B2', 'B3', 'B6', 'B7'],
     'D': ['D2', 'D3', 'D6', 'D7'],
     'F': ['F2', 'F3', 'F6', 'F7']},
    index=[2, 3, 6, 7]
)
df4
df1
result = pd.concat([df1, df4])
result
result = pd.concat([df1, df4], axis=1)
result
result = pd.concat([df1, df4], axis=1, join='inner')
result
Explanation: <a id=merging></a>
Merge, join, and concatenate
https://pandas.pydata.org/pandas-docs/stable/merging.html
pandas provides various facilities for easily combining together Series, DataFrame, and Panel objects with various kinds of set logic for the indexes and relational algebra functionality in the case of join / merge-type operations.
concat method:
pd.concat(objs, axis=0, join='outer', join_axes=None, ignore_index=False,
          keys=None, levels=None, names=None, verify_integrity=False,
          copy=True)
End of explanation
df1
df2
result = df1.append(df2)
result
df1
df4
result = df1.append(df4)
result
result = pd.concat([df1,df4])
result
Explanation: Using append method:
End of explanation
result = pd.concat([df1,df4], ignore_index=True)
result
Explanation: Note: Unlike list.append method, which appends to the original list and returns nothing, append here does not modify df1 and returns its copy with df2 appended.
End of explanation
result = df1.append(df4, ignore_index = True)
result
Explanation: This is also a valid argument to DataFrame.append:
End of explanation
df1
s1 = pd.Series(['X0', 'X1', 'X2', 'X3'], name='X')
s1
result = pd.concat([s1,df1])
result
result = pd.concat([df1,s1], axis = 1)
result
s2 = pd.Series(['_0', '_1', '_2', '_3'])
result = pd.concat([df1,s2,s2,s2], axis = 1)
result
Explanation: Mixing dimensions
End of explanation
data =  [
    # halo_id, gal_id, ra, dec, z, abs_mag'
    [1, 1, 21.5, 30.1, 0.21, -21.2],
    [1, 2, 21.6, 29.0, 0.21, -18.3],
    [1, 3, 21.4, 30.0, 0.21, -18.5],
    [2, 1, 45.0, 45.0, 0.42, -20.4],
    [3, 1, 25.0, 33.1, 0.61, -21.2],
    [3, 2, 25.1, 33.2, 0.61, -20.3]
]
# %load -r 103-145 solutions/06_01_pandas.py
Explanation: <a id=exercise3></a>
Exercise 3: Generate a random catalog using concat method
In this exercise we will use the concat method and show a basic example of multiIndex.
Given a subset of a few galaxies with the following properties ['halo_id', 'gal_id' ,'ra', 'dec', 'z', 'abs_mag'], create a random catalog with 50 times more galaxies than the subset keeping the properties of the galaxies but placing them randomly in the first octant of the sky. 
The index of each galaxy is given by the tuple [halo_id, gal_id]
End of explanation
star_filename = '../resources/df_star.ssv'
spectra_filename = '../resources/df_spectra.ssv'
starid_specid_filename = '../resources/df_starid_specid.ssv'
df_spectra = pd.read_csv(spectra_filename, index_col=['spec_id', 'band'], sep = ' ')
df_spectra.head(41)
df_starid_specid = pd.read_csv(starid_specid_filename, sep=' ')
df_starid_specid.head(5)
# Given that the file is somehow corrupted we open it without defining any index
df_star = pd.read_csv(star_filename, sep=' ')
df_star.head(10)
df_star[(df_star['sdss_star_id'] == 1237653665258930303) & (df_star['filter'] == 'NB455')]
# Drop duplicates:
df_star.drop_duplicates(subset = ['sdss_star_id', 'filter'], inplace= True)
df_star[(df_star['sdss_star_id'] == 1237653665258930303) & (df_star['filter'] == 'NB455')]
df_starid_specid.head(5)
Explanation: Merge method: Database-style DataFrame joining/merging:
pandas has full-featured, high performance in-memory join operations idiomatically very similar to relational databases like SQL. These methods perform significantly better (in some cases well over an order of magnitude better) than other open source implementations (like base::merge.data.frame in R). The reason for this is careful algorithmic design and internal layout of the data in DataFrame
See the cookbook for some advanced strategies
Users who are familiar with SQL but new to pandas might be interested in a comparison with SQL
pd.merge(left, right, how='inner', on=None, left_on=None, right_on=None,
         left_index=False, right_index=False, sort=True,
         suffixes=('_x', '_y'), copy=True, indicator=False)
Example: Merging dataframes using the merge method (thanks Nadia!)
Goal: build a dataframe merging 2 different dataframes with complementary information, through the relation given by a third dataframe.
df_stars contains information of stars magnitudes per sdss_star_id and per filter:
['sdss_star_id', 'filter', 'expected_mag', 'expected_mag_err']
Note, the file is "somehow" corrupted and entries are duplicate several times
Unique entries are characterized by sdss_star_id and filter
df_spectra contains information of star flux per band (== filter) and per spec_id (!= sdss_star_id):
['spec_id', 'band', 'flux', 'flux_err']
Unique entries are characterized by spec_id and band
df_spec_IDs allows to make the correspondence between sdss_star_id (== objID) and spec_id (== specObjID):
['objID', 'specObjID']
Unique entries are characterized by objID
End of explanation
df_spectra.reset_index(inplace = True)
df_spectra.head()
df_spectra.rename(columns={'band': 'filter'}, inplace = True)
df_spectra.head()
df_starid_specid.rename(columns={'objID':'sdss_star_id', 'specObjID':'spec_id'}, inplace = True)
df_starid_specid.head()
Explanation: We are going to unset the index and rename the columns in order to use the "on" argument:
End of explanation
df_star_merged = pd.merge(df_star, df_starid_specid, on='sdss_star_id')
df_star_merged.head()
df_star_merged = pd.merge(df_star_merged, df_spectra, on=['spec_id','filter'])
df_star_merged.head(40)
df_star_merged.set_index(['sdss_star_id', 'filter'], inplace = True)
df_star_merged.head()
# Each element has been observed in how many bands?
count_bands = df_star_merged.groupby(level=0)['flux'].count()
count_bands.head(20)
df_star_merged.groupby(level=1)['flux_err'].mean().head(10)
Explanation: Now we have everything ready to make the JOINs
End of explanation
# e.g.: the decimal value 0.1 cannot be represented exactly as a base 2 fraction
(0.1 + 0.2) == 0.3
(0.1 + 0.2) - 0.3
Explanation: <a id=functions></a>
More functions
Looping a dataframe (iterrows):
https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.iterrows.html
sort method:
https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.sort_values.html
sample method:
https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.sample.html
Reshape dataframes (pivot, stack, unstack):
http://nikgrozev.com/2015/07/01/reshaping-in-pandas-pivot-pivot-table-stack-and-unstack-explained-with-pictures/
Data cleaning:
check for missing values (isnull): https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.isnull.html
drop missing values (dropna): https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.dropna.html
fill the missing values with other values (fillna): https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.fillna.html
replace values with different values (replace): https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.replace.html
Some general ideas to get home:
Do not loop a dataframe!
Try to work by chunks; create functions that work with chunks
Work with standard formats and "already implemented" functions
<a id=caveats></a>
Caveats and technicalities
Floating point limitations:
Be careful with exact comparisons!
End of explanation
import fitsio
filename = '../resources/galaxy_sample.fits'
fits=fitsio.FITS(filename)
data = fits[1]
# Number of rows
data.get_nrows()
# chunk size
gal_chunk = 300000
# e.g.to create the ranges!
import math
niter = int(math.ceil(data.get_nrows() / float(gal_chunk)))
for i in range(niter):
    s = i*gal_chunk
    f = min((i+1)*gal_chunk, data.get_nrows())
    chunk = data[s:f]
    print (i)
    print (type(chunk))
    print (chunk.dtype)
    df_chunk = pd.DataFrame(chunk)
    print (type(df_chunk))
    print (df_chunk.dtypes)
    df_chunk = df_chunk.set_index('unique_gal_id')
    print (df_chunk.head())
Explanation: FITS files
fitsio
And working by chunks
End of explanation
bad_filename = '../resources/steps.flagship.dat'
df_bad = pd.read_csv(bad_filename)
df_bad.head()
df_bad =  pd.read_csv(bad_filename, sep = ' ')
Explanation: .values DataFrame attribute
Some scipy functions do not allow to use pandas dataframe as arguments and therefore it is useful to use the values atribute, which is the numpy representation of NDFrame
The dtype will be a lower-common-denominator dtype (implicit upcasting); that is to say if the dtypes (even of numeric types) are mixed, the one that accommodates all will be chosen. Use this with care if you are not dealing with the blocks.
View vs. Copy
https://pandas.pydata.org/pandas-docs/stable/indexing.html#returning-a-view-versus-a-copy
Wrong input example:
.dat
Look at the file using e.g. head bash command
Note that there are more than one space, and if you do tail filename, different number of "spaces"
End of explanation
filename = '../resources/steps.flagship.ssv'
columns = ['step_num', 'r_min', 'r_max', 'r_med', 'a_med', 'z_med']
df = pd.read_csv(filename, sep = ' ', header = None, names = columns, index_col = 'step_num')
df.head()
Explanation: Necessary to "modify" the file in order to convert it into a standard csv file, e.g.:
cat steps.flagship.dat | tr -s " " | sed 's/^ *//g' &gt; steps.flagship.ssv
End of explanation
<END_TASK>