Python Lesson Pandas

A pandas series is a 1D array object that contains a sequence of values (homogeneous or not) and an associated data label array, its index. You can define a series from any array, having integers from zero to the number of elements minus one as indexes.

series_ex_I = pd.Series([4,5,6,7,9])
print(series_ex_I)
series_ex_II = pd.Series([4,5,76.7,True])
print(series_ex_II)

You can access in a separate way indexes and values with the attributes index and value

print(series_ex_II.index)
print(series_ex_II.values)

You can provide the indexes labels -not necessarily integers- when creating the data structure

series_ex_III = pd.Series([4,5,76.7,True], index=["t1", "t2", "t3", "tbool1"])
print(series_ex_III)

You can access an element by its index value, or provide a list of indexes and access a subset of values.

# Accessing a single element
print(series_ex_III['t1'])
# Accessing several elements
print(series_ex_III[["t2","tbool1"]])

You can also perform NumPy-like operations on series and select elements in accordance with a given criterion

print(series_ex_I[series_ex_I > 5])
print(series_ex_I*4)
print(series_ex_III[series_ex_III > 5])
print(series_ex_III*4)

You can check the existence of a given index label with a syntax similar to the one used in dictionaries

"t2" in series_ex_III

You can also create directly a series from a dictionary

hash_0 = {"Sevilla": 2022, "Huelva": 2044, "Granada": 2033}
series_ex_IV = pd.Series(hash_0)
print(series_ex_IV.index)
print(series_ex_IV.values)

You can also explicitly control the index label ordering

series_ex_IV = pd.Series(hash_0, index = ["Huelva", "Granada", "Sevilla", "Ceuta"])
print(series_ex_IV)

As the Ceuta index does not exist in the dictionary it is created with a NaN value. You can check the occurrence with these values using the isnull function or method

print(pd.isnull(series_ex_IV))
print(series_ex_IV.isnull())

A very useful feature is that you can operate with pandas series and they will be aligned by index number.

hash_1 = {"Sevilla": 2, "Huelva": 4, "Granada": 3}
series_ex_V = pd.Series(hash_1, index = ["Ceuta","Granada", "Huelva", "Sevilla"])
# Index alignment
print(series_ex_IV + series_ex_V)

You can also name the series and its index to facilitate its identification

# Series and index names
series_ex_IV.name = "R&R Parameter"
series_ex_IV.index.name = "City"
#
print(series_ex_IV)

You can also alter in-place the index labels of a series

series_ex_III.index = ["Ceuta","Granada", "Huelva", "Sevilla"]

And you can operate with the new series

series_ex_IV + 2*series_ex_V + series_ex_III

The most known pandas data structure is the dataframe worked out to mimic the versatility of GNU R equally named data structures. A dataframe can be considered a set of series sharing the same indexes. Therefore they have an index for the rows and a label for each columns. The columns can have data of different dtype and even data within a column can be of different type.

We will see that there are several different ways of constructing a dataframe. An usual one is to start with a dict of equal-length lists or NumPy arrays.

data_student = {"Wall, L": [8,9,9,10,8], "Page, L": [9,7,9,10,10], "Gates, B": [8, 8, 8, 9, 9], "Thompson, K": [10,10,9,9,9], "Rosum, G van":[9,9,8,8,10]}
dframe_1 = pd.DataFrame(data_student)
dframe_1

As can be checked in the output, the rows index is a default one, as in the Series case, and the columns kept the dictionary order. You can sort them at your best convenience providing using the columns argument.

dframe_1 = pd.DataFrame(data_student, columns = sorted(data_student.keys()))
dframe_1

In fact if there are column names without associated data in the dictionary, the column is created with missing values (NaNs). And, as with series, you can provide a given index too.

cols = list(data_student.keys())
cols.append("Ritchie, D")
#
dframe_2 = pd.DataFrame(
    data_student, columns = sorted(cols), index = ["Math_01", "Math_02", "Alg_01", "OpSys", "Num_An"]
                       )
dframe_2

You can also set the name attribute for the dataframe columns and index and they will be displayed with the dataframe

dframe_2.index.name = "Subject Grades"
dframe_2.columns.name = "Pro Programmers"
dframe_2

You can retrieve any dataframe row or column. Columns are obtained as a series using the column name or by attribute, though the second option only is valid for column names that are also valid Python variable names.

dframe_2["Wall, L"]
# Not a valid variable name
# print(dframe_2."Wall, L")

Note that the series and index names are conserved. Rows can be retrieved using the loc attribute.

dframe_1.loc[1]

dframe_2.loc["OpSys"]

The values that you retrieve are not a copy of the underlying data, but a view of them. Be aware that you modify them in place this will affect the original dataframe. There is a copy method to obtain a copy of the data.

You can modify an existing column or create a new column with a default value by assignment

dframe_2["Ritchie, D"] = 9
dframe_2["Torvalds, L"] = 8
dframe_2

You can also provide as a value an array with the right number of elements

dframe_2["Torvalds, L"] = np.arange(6,11)
dframe_2

If instead of an array you provide a series, there are more flexibility as the indexes in the array will be aligned with the indexes in the dataframe and any missing index will be replaced by a NaN missing value.

series_Stallman = pd.Series([9,9,9,9], index = ["Math_01", "Math_02", "OpSys", "Num_An"])
#
dframe_2["Stallman, R"] = series_Stallman
#
dframe_2

You can add a boolean column using the NumPy syntax

dframe_2["test"] = dframe_2["Stallman, R"] >= 9
dframe_2

You can delete the added column using the del keyword

del(dframe_2["test"])
dframe_2

You can use a syntax similar to the one used in NumPy arrays to transpose a dataframe and exchange the indexes and columns roles.

dframe_2.T

There are many other ways of creating a dataframe, apart from the previous one, consisting on formatting your data as a dictionary of lists or NumPy vectors. We will provide some of them here:

• From a nested dict of dicts. The advantage in this case is that the nested dictionaries keys are the dataframe indexes

  # Dict of dicts
  d3_dict = {"Planck, M":{ "Hometown": "Kiel", "Born": 1858, "Died": 1947}, "Heisenberg, W":{ "Hometown": "Wurzburg", "Born": 1901, "Died": 1976}, "Fermi, E": { "Hometown": "Roma", "Born": 1901, "Died": 1954}, "Schroedinger, E": { "Hometown": "Erdberg", "Born": 1887, "Died": 1961}}
  dframe_3 = pd.DataFrame(d3_dict)
  dframe_3
  

• You can also use a dict of series to build the dataframe.

  # dict of series
  d4_dict = {"Page, L": dframe_2["Page, L"][:-1], "Torvalds, L": dframe_2["Torvalds, L"][1:] }
  dframe_4 = pd.DataFrame(d4_dict)
  dframe_4
  

• Two dimensional ndarray

  # 2D ndarray
  arr_2D = np.ones([4,4])
  dframe_5 = pd.DataFrame(arr_2D, columns=["Col_00", "Col_02","Col_03","Col_04"], index=["a", "b", "c", "d"])
  dframe_5
  

Reindexing a panda object creates a new object with different index labels. With a series it rearranges the data according to the new index. Any missing old index values is removed from the series and missing NaN values are introduced for nonexistent index values.

print(series_ex_I)
new_series = series_ex_I.reindex(np.arange(1,7))
new_series

When applied to a dataframe, reindex can modify rows, columns, or both. By default, rows are reindexed according to a given sequence in the same way than for series.

print(dframe_5)
dframe_8 = dframe_5.reindex(["a", "c", "b", "f", "e", "d"])
dframe_8

The reindex function has several arguments. For example, the argument fill_value allows for defining a default value for data associated to non-existent labels when reindexing

dframe_8 = dframe_5.reindex(["a", "c", "b", "f", "e", "d"], fill_value=0)
dframe_8

There is also an option to fill missing values when reindexing, which is quite useful when working with time series. The option name is method and with the value ffill~(~bfill) performs a forward(backward) filling.

dframe_9 = pd.DataFrame(np.random.randn(4,4), columns=["Col_00", "Col_02","Col_03","Col_04"], index=np.arange(0,8,2))
print(dframe_9)
dframe_10 = dframe_9.reindex(np.arange(0,8), method="ffill")
dframe_10

You can also reindex columns using the columns keyword

dframe_11 = dframe_5.reindex(columns=["Col_04", "Col_03", "Col_02", "Col_01", "Col_00"])
dframe_11

Notice the difference between reindexing and renaming columns either creating a new object or in-place editing the dataframe.

print(dframe_11)
print(dframe_11.rename({"Col_04":"Col_a", "Col_02":"Col_b", "Col_00":"Col_d"}, axis = 1))
print(dframe_11)
dframe_11.rename({"Col_04":"Col_a", "Col_02":"Col_b", "Col_00":"Col_d"}, axis = 1, inplace=True)
print(dframe_11)

You can use the drop method that returns a new object with the indicated entries deleted. This can be applied to series and dataframes.

print(series_ex_VI)
new_series = series_ex_VI.drop([1,3])
print(new_series)
print(series_ex_VI)

new_dfram = dframe_10.drop(range(2,6))
print(new_dfram)
new_dfram = dframe_10.drop(["Col_03", "Col_00"], axis=1)
print(new_dfram)

The inplace=True option allows for the editing of the object, avoiding the creation of a new object.

You can use a syntax similar to the one used by Numpy with pandas series, in particular you can use either the index values or integers.

print(series_ex_V)
print(series_ex_V["Granada"])
print(series_ex_V[1])
#
print(series_ex_V[["Granada", "Sevilla"]])

You can apply filters to the series

print(series_ex_V[series_ex_V > 2])

You can also use slicing with integers of index labels, but notice the difference. The end point of the range is included in the labels case.

print(series_ex_V["Granada":"Sevilla"])
print(series_ex_V[1:3])

You can assign values using these methods

series_ex_V["Granada":"Sevilla"] = 10
print(series_ex_V)

By default, indexing refers to columns and you can set values

print(dframe_7)
print(dframe_7["Thompson, K"])
print(dframe_7[["Gates, B","Thompson, K"]])
dframe_7["Ritchie, D"] = 6

If you use slicing, this works over indexes

print(dframe_7[1:3])

You can select data using a Boolean array

print(dframe_7[dframe_7>8])
print(dframe_7[dframe_7["Page, L"]>8])

You can assign also using this syntax

dframe_7[dframe_7 <= 8] = 5

You can also use loc and iloc for row indexing using axis labels or integers, respectively.

We can select two rows and two columns by label and by integer values as follows

dframe_7.loc[["Math_01","OpSys"], ["Rosum, G van", "Wall, L"]]
dframe_7.iloc[[0,3], [3,5]]

Both ways of selecting elements work with slices

dframe_7.loc["OpSys":, "Rosum, G van":"Wall, L"]
dframe_7.iloc[0:3, 0:4]

It is important to take into account that if you have an axis index containing integers, data selection will always be label-oriented to avoid possible ambiguities. In these cases is preferably to use loc or iloc.

The arithmetic between series with different indexes is performed in such a way that the final set of indexes is the union of the involved sets and missing values are introduced in the elements where the two series don’t overlap and propagate through arithmetic operations.

An example with two series is

print(series_ex_I)
print(series_ex_II)
series_ex_I + series_ex_II

In the case of dataframes a double alignment is performed, in indexes and columns

print(dframe_4)
print(dframe_7)
dframe_4 + dframe_7

If there are neither columns nor rows in common the resulting series will be only made of NaN‘s. Using the add method you can pass an argument to fill with a given value the non-overlapping elements of the dataframe, though if both elements are missing the result will still be a NaN.

dframe_4.add(dframe_7, fill_value = 0.0)

The following methods are available for series and dataframe arithmetics that have also reversed versions.

1. add [~radd~]: addition
2. sub [~rsub~]: subtraction
3. div [~rdiv~]: division
4. floordiv [~rfloordiv~]: floor division
5. mul [~rmul~]: multiplication
6. pow [~rpow~]: exponentiation

You can mix series and dataframes in operations that are performed in a similar way to broadcasting in NumPy. By default the series index is matched against the dataframe’s columns, broadcasting down the rows

series_ex_VII = pd.Series(np.random.randn(5), index =["Col_00", "Col_01", "Col_02","Col_03","Col_04"] )
print(series_ex_VII)
print(dframe_8)
#
dframe_8 * series_ex_VII

You can also broadcast on the rows, matching the series index versus the dataframe index. In this case you need to use the method notation.

dframe_2.rsub(series_Stallman, axis = 'index')

You can also perform arithmetic operations with dataframes

print(dframe_10)
print(dframe_9)
dframe_10/dframe_9

NumPy universal functions (ufuncs) can be used in series and dataframes

np.cos(dframe_10)

The apply method allows for applying a function to each row or column of a dataframe and giving as a result a a dataframe or a series with the corresponding indexes, depending on the function output. By default, the function is applied to each column.

# series output
g = lambda x: np.mean(x)/np.std(x)
print(dframe_10.apply(g))
##
# dataframe output
f = lambda x: (x - np.mean(x))/np.std(x)
#
dframe_12 = dframe_10.apply(f)
print(dframe_12)
#
print(dframe_12.mean(axis = 0))
#
dframe_13 = dframe_10.apply(f, axis="columns")
print(dframe_13)
#
dframe_13.mean(axis = 1)

You can apply a function that returns multiple values, as a series

def h(x):
    return pd.Series([x.min(), x.max(), x.mean(), x.std(), x.sum()], index=['min', 'max', 'mean', 'std', 'sum'])
print(dframe_10)
dframe_13 = dframe_10.apply(h)
print(dframe_13)

You can apply also element-wise functions to a Series with map and to a Dataframe with applymap as follows

format_floats = lambda x: '%.2f' % x
# Series
print(dframe_10["Col_00"].apply(format_floats))
# Dataframe
dframe_10.applymap(format_floats)

The sort_index method returns a new object, lexicographically sorted by row or column, and can be applied to Series,

# Sort series by index
print(series_ex_IV)
#
series_ex_IV.sort_index()

and DataFrames. In this case you can also sort by column name.

# Sort dataframe by index
print(dframe_7)

print(dframe_7.sort_index())

print(dframe_7.sort_index(axis=1, ascending=False))

If instead of sorting by indexes you need to sort by Series of DataFrame values the method is called sort_values, notice that NaN values are sent to the end of the series. In case of DataFrames you can use as sorting keys one or various columns of the DataFrame. In this case it is mandatory to include at least one column name as a by= argument.

# Sort Series by values 
print(series_ex_IV.sort_values())
# Sort DataFrame by values 
print(dframe_7.sort_values(by="Wall, L"))
print(dframe_7.sort_values(by=["Wall, L", "Thompson, K"]))

Related to sorting is ordering elements by its rank. This is accomplished with the rank method. At first sight it can be surprising to find non integer positions, explained by the fact that the method breaks ties assigning the mean value to the group

print(series_ex_IV)
print(series_ex_IV.rank())
print(dframe_7)
print(dframe_7.rank())
print(dframe_7.rank(axis="columns"))

There are other ways of breaking the ties, using the options min or max that assigns the minimum or maximum rank to the whole group; first that takes into account the order of appearance of the element; or dense, similar to min, but with ranks always increasing by one, independently of the number of elements in the group.

print(dframe_7)
print(dframe_7.rank(method="min"))
print(dframe_7.rank(method="dense"))

Pandas provides a set of useful methods to compute descriptive statistical quantities of your Series or DataFrame.

1. count: Number of non-NaNs.
2. describe: Provides summary statistics for a Series or for each DataFrame column.
3. min, max: Minimum and maximum values
4. argmin, argmax: Index locations (integers) at which minimum or maximum value is obtained.
5. idxmin, idxmax: Index labels at which minimum or maximum value is obtained.
6. quantile: Sample quantile ranging from 0 to 1.
7. sum: Sum of values.
8. mean: Mean of values.
9. median: Arithmetic median (50% quantile) of values.
10. mad: Mean absolute deviation from mean value.
11. prod: Product of all values.
12. var: Variance of values.
13. std: Standard deviation of values.
14. skew: Skewness of values (third moment).
15. kurt: Excess Kurtosis of values (fourth moment – 3).
16. cumsum: Cumulative sum of values.
17. cummin, cummax: Cumulative minimum or maximum of values, respectively.
18. cumprod: Cumulative product of values.
19. diff: Compute first arithmetic difference (useful for time series).
20. pct_change: Compute percent changes.

Some examples are provided for the normal distribution

# Random Gaussian Dataframe
Elements = 1000
arr_2D = np.random.randn(Elements,6)
cols = []
for index in range(6):
    cols.append("Set_0{0}".format(index))
dframe_14 = pd.DataFrame(arr_2D, columns=cols)
dframe_14

print("Mean\n", dframe_14.mean(), "\n Std.\n", dframe_14.std(), "\nQuantile\n", dframe_14.quantile(q = 0.68), "\nSkewness\n", dframe_14.skew(), "\nKurtosis\n", dframe_14.kurt())

We can also compute more elaborate statistics. As an example we load COVID19 data for Spain and analyze the data for total number of cases in Andalousian provinces. The first step is to download the data and build the requested dataframe

# Library
from datetime import date, timedelta 
#
# Data Reading
data=pd.read_csv('https://cnecovid.isciii.es/covid19/resources/datos_provincias.csv') 
# 
# Andalousian Provinces
Andalousie = ["AL", "CA", "CO", "GR", "H", "J", "MA", "SE"]
#
# Create a Dataframe with total number of cases
temp_dict = {"AL": data[data["provincia_iso"]=="AL"]["num_casos"]} 
dframe_AND = pd.DataFrame(temp_dict)
dframe_AND = dframe_AND.set_index(data[data["provincia_iso"]=="AL"]["fecha"])
dframe_AND_index = dframe_AND.index
#
for province in Andalousie[1:]:
    series_province = data[data["provincia_iso"]==province]["num_casos"]
    series_province.index = dframe_AND.index
    dframe_AND[province] = series_province

Now we can compute some statistics. For example the covariance and correlation matrices

print(dframe_AND.cov())
print(dframe_AND.corr())

We can compute also the percent change for a given period

percent_change = dframe_AND.pct_change(periods=7)
percent_change.tail()

We can compute the correlation with other Series of Dataframe column using corrwith

Province = "M"
Series_P = data[data["provincia_iso"]==Province]["num_casos"]
Series_P.index = dframe_AND_index
dframe_AND.corrwith(Series_P)

Other methods of interest related with Series and DataFrame description is unique that provides an array of unique values and value_counts that computes value frequencies

# Sevilla sorted unique values
uniques_SE = dframe_AND["SE"].unique()
uniques_SE.sort()
uniques_SE

# Sevilla value count
values_SE = dframe_AND["SE"].value_counts()
values_SE

The isin method performs a vectorized check of membership for a given set and is used to filter a Series or a DataFrame column down to a values subset

mask_SE = dframe_AND["SE"].isin([0,1,2])
dframe_AND["SE"][mask_SE]

Related to this method is the Index.get_indexer method that provides an index array of distinct values. In this example we got an array for dates when the number of cases is less than or equal to three.

pd.Index(pd.Series([0,1,2,3])).get_indexer(dframe_AND["SE"])

Using apply one can perform rather complex data manipulation in a concise way. We can, for example, count the distinct values for each column of our example dataframe

dframe_AND.apply(pd.value_counts).fillna(0)

The main functions in Pandas to deal with text-formatted files are

read_csv

Load delimited data from a file, URL, or file-like object. The default delimiter is the comma.

read_table

Load delimited data from a file, URL, or file-like object. The default delimiter is the tab (‘\t’).

read_fwf

Read data without delimiters in a fixed-width column format.

read_clipboard

Version of read_table that reads data from the clipboard. Useful for converting tables from web

pages.

We have already seen an example of read_csv in action loading data from a URL. The large variety of formats and ways of encoding information into text files can be handled at the cost of having a plethora of options and modifiers to the previous functions. The most common ones are

path

String indicating filesystem location, URL, or file-like object.

sep or delimiter

Character sequence or regular expression that marks field separation in each row.

header

Row number whose entries are used as column names. Defaults to the first row, and should be None if there is no header row.

index_col

Column numbers or names to use as the row index in the result; can be a single name/number or a list of them for a hierarchical index.

names

List of column names for result, combine with header=None.

skiprows

Number of rows ignored at the beginning of the file to ignore. Also it can be given as a list of row numbers (starting from 0) to skip.

na_values

Sequence of values to replace with NA.

comment

Character(s) marking comments to split comments off the end of lines.

parse_dates

Attempt to parse data to datetime; False by default. If True, will attempt to parse all columns. Otherwise can specify a list of column numbers or name to parse. If element of list is tuple or list, will combine multiple columns together and parse to date (e.g., if date/time split across two columns).

keep_date_col

If joining columns to parse date, keep the joined columns; False by default.

converters

Dict containing column number of name mapping to functions (e.g., {‘foo’: f} would apply the function f to all values in the ‘foo’ column).

dayfirst

When parsing potentially ambiguous dates, treat as international format (e.g., 7/6/2012 -> June 7, 2012); False by default.

date_parser

Function to use to parse dates.

nrows

Number of rows to read from beginning of file.

iterator

Return a TextParser object for reading file piecemeal.

chunksize

For iteration, size of file chunks.

skip_footer

Number of lines to ignore at end of file.

verbose

Print various parser output information, like the number of missing values placed in non-numeric columns.

encoding

Text encoding for Unicode (e.g., ‘utf-8’ for UTF-8 encoded text).

squeeze

If the parsed data only contains one column, return a Series.

thousands

Separator for thousands (e.g., ‘,’ or ‘.’).

As an example we can read into a dataframe one of the monthly temperature files used in previous examples

pd.read_csv("files/TData/T_Araxos_EM.csv")

By default the first row elements are used to label columns. We can choose our own labels and also transform the year into the dataframe index

tdata_Araxos = pd.read_csv("files/TData/T_Araxos_EM.csv", index_col=0, names = ["Jan", "Feb", "Mar", "Apr","May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"], skiprows=1)
tdata_Araxos

Notice that in this case we are also forced to skip the first row.

In case that a file is very long, we may need to read it piecewise or iterate over files small portions using the nrows argument.

The method to_csv allows for writing data files as comma separated files. We can write the dataframe read above into a file. By default the separator used is a comma, but you can define another character as separator with the sep option

tdata_Araxos.to_csv("temp_Data_Araxos.csv", sep=";")
!cat temp_Data_Araxos.csv

By defauly row and column labels are included in the file. This can be disabled using the index=False and header=False options, respectively. Note that missing values appear as empty strings and can be denoted by a so called sentinel value using the option na_rep.

tdata_Araxos["UndefVals"] = np.nan
tdata_Araxos.to_csv("temp_Data_Araxos.csv", sep=":", na_rep="NULL")
!cat temp_Data_Araxos.csv

You can also save a number of columns and in an arbitrary order you define

tdata_Araxos.to_csv("temp_Data_Araxos.csv", sep=":", columns=["Mar", "Apr", "May"])
!cat temp_Data_Araxos.csv

read_excel

Read tabular data from an Excel XLS or XLSX file. Use packages xlrd and openpyxl.

read_hdf

Read HDF5 files written by pandas.

read_html

Read all tables found in the given HTML or XML document.

read_json

Read data from a JSON (JavaScript Object Notation) string representation. Library: json.

read_msgpack

Read pandas data encoded using the MessagePack binary format.

read_pickle

Read an arbitrary object stored in Python pickle format.

read_sas

Read a SAS dataset stored in one of the SAS system’s custom storage formats.

read_sql

Read the results of a SQL query (using SQLAlchemy) as a pandas DataFrame.

read_stata

Read a dataset from Stata file format.

read_feather

Read the Feather binary file format

You can define series and dataframes with more than one level of indexing paving the way to multidimensional data treatment in a lower dimensional form. This is an example for a series

series_hind = pd.Series(np.random.randn(10), index=[[0,0,0,0,1,1,1,2,3,4],["x","y","z","t","x","y","z","x","y","z"]])
#
print(series_hind)
print(series_hind.index)

You can access the elements making use of the different index levels

print(series_hind[0])
print(series_hind[1,"z"])

And you can also slice through the indexes making use of the loc function and the slice function or the Numpy syntax.

print(series_hind.loc[slice(1,3)])
print(series_hind.loc[1:3])
print(series_hind.loc[:,"z"])

Note than in this case slices are inclusive.

Hierarchical index eases the reshaping of data. Making use of the unstack method we can transform the previous series into a dataframe, filling with NaNs undefined values

series_hind.unstack()

The opposite operation is performed -unsurprisingly- by the stack method, transforming a dataframe into a multi-index series.

The extension of hierarchical indexes to dataframes implies that both rows and columns can have multiple indexes. Let’s build an example dataframe with hierarchical indexes in rows and columns.

frame_hind = pd.DataFrame(np.random.randint(0,100,size=(6,6)), index=[["i","i","ii","ii","iii","iii"],["a","b","a","b","a","b"]], columns=[["CA","CA","CA","HU","HU","HU"], ["S1", "S2", "S3","S1", "S2", "S3"]])
frame_hind

You can name the indexes, helping to make your code more understandable

frame_hind.index.names = ["Key a", "Key b"]
frame_hind.columns.names = ["Prov", "Section"]
frame_hind

It is now easy to select a group of columns

frame_hind["HU"]

You can change the order of the levels in rows and columns and also sort the data according to some criterion. If, for example, you want to exchange the levels order in both rows and columns you can use the swaplevel function, taking into account that it returns a new object

frame_hind_new = frame_hind.swaplevel("Prov", "Section", axis = 1).swaplevel("Key a", "Key b")
frame_hind_new 

The sort_index command sorts levels according to a single index level

frame_hind.sort_index(level=1)

You can combine swaplevel and sort_index functions

frame_hind.swaplevel("Prov", "Section", axis = 1).sort_index(level=1, axis=1)

In many statistics functions you have a level option that allows for specifying the level at which the manipulation is intended

print(frame_hind.sum(level="Key b"))
print(frame_hind.sum(axis=1,level="Prov"))
print(frame_hind.mean(axis=1, level="Section"))

You can easily create new DataFrames with hierarchical indexing using the set_index and reset_index functions. The first one takes one or more columns as new indexes for a new DataFrame, while the second does the inverse operation. Depending on the drop argument value, the columns will disappear or not.

test_ri = frame_hind_new.reset_index()
print(test_ri)
test_si = test_ri.set_index(["Key b", "Key a"], drop=False)
print(test_si)

Different Pandas objects can be combined using the functions merge and concat.

The merge function connect rows in DataFrames based on one or more keys, in a way familiar to those used to work with relational databases.

frame_1= pd.DataFrame(np.random.randint(0,4,size=(5,4)), columns=["S1", "S2", "S3","S4"])
frame_2= pd.DataFrame(np.random.randint(0,6,size=(6,4)), columns=["K1", "K2", "K3","K4"])
#
print(frame_1)
print(frame_2)
#
pd.merge(frame_1, frame_2, left_on = "S3", right_on = "K3")

By default an inner merge is performed and the intersection of the key set is used. Therefore the common elements in the columns indicated are selected and with the the accompanying elements from other columns. The default behavior can be changed using the left, right, or outer options

pd.merge(frame_1, frame_2, left_on = "S3", right_on = "K3", how="outer")

You can also use keys from various columns

pd.merge(frame_1, frame_2, left_on = ["S2","S3"], right_on = ["K1","K3"], how="inner")

When columns names are equal on the joined dataframe, pandas by default add a name_x or name_y suffix. This can be changed using the suffixes option to merge.

The concat function concatenates or stacks together objects along a given axis, in a similar way to the NumPy concantenate function. The labeled axes in Pandas results in different options when binding two Series or DataFrames. You can choose either to stack only the intersection or the union of the indexes, you can decide to discard the labels on the axes being binded or keep the concatenated data structures identifiable after merging.

If we use as arguments Series having no common index, the concat function simply stacks them and creates a new Series

pd.concat([series_ex_I,series_ex_III,series_ex_VII])

By default concat acts on axis=0, creating a new Series, the option axis=1 will turn the output into a DataFrame with each Series as a column and filling with NaN the void values (an outer join).

pd.concat([series_ex_I,series_ex_III,series_ex_VII],axis =1)

If there are common indexes and the operation is performed along axis = 0 there will be repeated index values while in the axis = 1 case the number of undefined NaN values will be reduced.

series_ex_VIII = pd.concat([series_ex_III,series_ex_VII]) + 1
print(pd.concat([series_ex_I,series_ex_III,series_ex_VIII]))
print(pd.concat([series_ex_I,series_ex_III,series_ex_VIII], axis = 1))

In the axis=1 case, you can perform an inner join using the argument join = inner.

pd.concat([series_ex_III,series_ex_VIII], axis=1, join = "inner")

To keep track of the initial arguments you can use an hierarchical index in the concatenation axis with the keys argument

pd.concat([series_ex_I,series_ex_III,series_ex_VIII],axis =0, keys=["I", "III", "VIII"])

If axis=1 the keys will be used as column names.

pd.concat([series_ex_I,series_ex_III,series_ex_VIII],axis =1, keys=["I", "III", "VIII"])

You can also concatenate DataFrames. Notice the difference between specifying axis = 1 or using the default. If you specify a keys argument, it is used to define a hierarchical axis in a given axis.

print(pd.concat([dframe_1,dframe_4]))
#
print(pd.concat([dframe_1,dframe_4], axis=1))
#
print(pd.concat([dframe_1,dframe_4], keys=["DF_1", "DF_4"]))
print(pd.concat([dframe_1,dframe_4], axis=1, keys=["DF_1", "DF_4"]))

You can also specify the key values as the keys of a dictionary that replaces the list argument.

print(pd.concat({"DF_1": dframe_1,"DF_4": dframe_4}, axis=1))

In case the information on the index is not relevant you can use the ignore_index=True option

print(pd.concat([dframe_1,dframe_5], axis=0, ignore_index=True))
print(pd.concat([dframe_1,dframe_5], axis=1, ignore_index=True))