<SYSTEM_TASK:>
Given the following text description, write Python code to implement the functionality described below step by step
<END_TASK>
<USER_TASK:>
Description:
 Reshaping data with stack and unstack
Pivoting
Data is often stored in CSV files or databases in so-called “stacked” or “record” format
Step1:  A better representation might be one where the different subjects are in rows, the applied treatments are in columns and outcomes are in the data frame values. 
<img src="img/stack.png" width=70%>
You can achieve this by pivot function
Step2:  If there is more that one record for each pair of "subject" and "treatment" (for example, the subject was tested twice with the same treatment at different times) you can use pivot_table. It works just like pivot but it allows to specify additionally an aggregation function ('mean' by default).
To take another example, we will use some data from expeditions to the Pole of Inaccessibility. We will read the data from SQL database.
Step3:  <div class="alert alert-success">
    <b>EXERCISE</b>
Step4:  Note how the two indexes are nested
Step5:  Note that it creates a standard data frame with "flat" index.
Step6:  Indexing on the second index only may be slightly involved
Step7:  Consult the documentation for other methods.
To return to orginal format with columns insted of indexes use reset_index
Step8:  <div class="alert alert-success">
    <b>EXERCISE</b>
Step9:  unstack reverses the operation
Step10:  We can "stack" it even further
Step11:  <div class="alert alert-success">
    <b>EXERCISE</b>
Step12:  Just reading the tab-delimited data
Step13:  The above data is clearly not ready to be used! Each row contains the 24 measurements for each hour of the day, and also contains a flag (0/1) indicating the quality of the data.
Lets replace the negative numbers by missing values and give columns proper names.
Step14:  For now, we disregard the 'flag' columns
<ASSISTANT_TASK:>
Python Code:
df = pd.DataFrame({'subject':['A', 'A', 'B', 'B'], 
                   'treatment':['CH', 'DT', 'CH', 'DT'], 
                   'concentration':range(4)},
                 columns=['subject', 'treatment', 'concentration'])
df
Explanation: Reshaping data with stack and unstack
Pivoting
Data is often stored in CSV files or databases in so-called “stacked” or “record” format:
End of explanation
pivoted = df.pivot(index='subject', columns='treatment', values='concentration')
pivoted
Explanation: A better representation might be one where the different subjects are in rows, the applied treatments are in columns and outcomes are in the data frame values. 
<img src="img/stack.png" width=70%>
You can achieve this by pivot function:
End of explanation
from sqlalchemy import create_engine
engine = create_engine('sqlite:///data/survey.db')
visited = pd.read_sql('Visited', engine, index_col='ident', parse_dates=['dated'])
visited
readings = pd.read_sql('Survey', engine).dropna()
readings = readings.drop_duplicates()
readings
Explanation: If there is more that one record for each pair of "subject" and "treatment" (for example, the subject was tested twice with the same treatment at different times) you can use pivot_table. It works just like pivot but it allows to specify additionally an aggregation function ('mean' by default).
To take another example, we will use some data from expeditions to the Pole of Inaccessibility. We will read the data from SQL database.
End of explanation
multi = df.set_index(['subject', 'treatment'])
multi
Explanation: <div class="alert alert-success">
    <b>EXERCISE</b>: Join the `readings` and `visited` tables.
</div>
<div class="alert alert-success">
    <b>EXERCISE</b>: Pivot the table such that we have sites in rows and different quantities in columns.
</div>
Hierarchical index
Hierarchical index of pandas is a way of introducing another dimension to a (two-dimensional) data frame. This is implemented by having multiple levels of the index. Let's look at an example.
End of explanation
multi.loc['A'] # first level only
Explanation: Note how the two indexes are nested: 2nd level index ('treatment') is grouped under the first level index ('subject'). To access the two levels you can use labels from the first level or both levels using a tuple.
End of explanation
multi.loc[('A', 'CH')] # two level
Explanation: Note that it creates a standard data frame with "flat" index.
End of explanation
multi.loc[(slice(None), 'CH'), :]
Explanation: Indexing on the second index only may be slightly involved:
End of explanation
multi.reset_index()
Explanation: Consult the documentation for other methods.
To return to orginal format with columns insted of indexes use reset_index:
End of explanation
result = multi['concentration'].unstack()
result
Explanation: <div class="alert alert-success">
    <b>EXERCISE</b>: Group the survey data by sites, date of measurement on each site and the quantity measured. List all readings for `site` DR-1; all readings of radiation using the hierchical index.
</div>
stack/unstack
stack &mdash; shifts last level of hierarchical rows to columns
unstack &mdash; does the opposite, i.e. shifts last level of hierarchical columns to rows
End of explanation
result.stack()
Explanation: unstack reverses the operation:
End of explanation
df = multi.stack()
df
Explanation: We can "stack" it even further:
End of explanation
!head -1 ./data/BETR8010000800100hour.1-1-1990.31-12-2012
Explanation: <div class="alert alert-success">
    <b>EXERCISE</b>: Rearange the data frame from last exercise, such that rows contain sites and dates (hierchical index) and columns different quantities. List all readings of radiation.
</div>
Formatting data &mdash; Case study
Going further with the time series case study test on the AirBase (The European Air quality dataBase) data.
One of the actual downloaded raw data files of AirBase is included in the repo:
End of explanation
data = pd.read_csv("data/BETR8010000800100hour.1-1-1990.31-12-2012", sep='\t')#, header=None)
data.head()
Explanation: Just reading the tab-delimited data:
End of explanation
hours = map(str, range(24))
flags = ['flag'] * 24
col_names = ['date'] + list(sum(zip(hours, flags), ()))
col_names[:5]
data = pd.read_csv("data/BETR8010000800100hour.1-1-1990.31-12-2012", sep='\t',
                   na_values=['-999', '-9999'],
                   names=col_names,
                   index_col='date')#, header=None)
Explanation: The above data is clearly not ready to be used! Each row contains the 24 measurements for each hour of the day, and also contains a flag (0/1) indicating the quality of the data.
Lets replace the negative numbers by missing values and give columns proper names.
End of explanation
data = data.drop('flag', axis=1)
data.head()
Explanation: For now, we disregard the 'flag' columns
End of explanation
<END_TASK>