Pandas (Dataset)

Reading tables in Excel files

df = pd.read_excel('file.xlsx', sheet_name='Sheet 1')
# In addition to the first argument, a string with the file name,
# there's a second: sheet_name.
df = pd.read_excel('file.xlsx')
# If there's no second argument, the first sheet is read by default

Combining two data sets

data.merge(data2, on='merge_column', how='left')
data.merge(data2, on='merge_column', how='right')
# Parameters:
# the data set others are being joined to
# on: the column with the values that are being combined
# how: the combination type
#
# left: All the values from the data table have to be there,
# while values from data2 can be replaced with NaN.
# right: All the values from the data2 table have to be there,
# while values from data can be replaced with NaN.

Setting up a pivot table

data_pivot = data.pivot_table(index=['column1', 'column2'], columns='source',
values='column_pivot', aggfunc='function')
# Arguments:
# index: the column or columns data is grouped by
# columns: the column with the values used to group the data
# values: the values we want to see in the pivot table
# aggfunc: the function applied to those values

Pandas (Column)

Converting column values from the string type str to the real type float

pd.to_numeric(data['column'])
# Parameters: the column from the DataFrame;
# errors: what to do with errors.
pd.to_numeric(data['column'], errors='raise' )
# If we have errors='raise', which is the default value,
# an error is generated for incorrect values, stopping the conversion to numbers.
pd.to_numeric(data['column'], errors='coerce')
# If we have errors='coerce', incorrect values are forcibly replaced with NaN.
pd.to_numeric(data['column'], errors='ignore')
# If we have errors='ignore', incorrect values are ignored,
# leaving them where they are.
data['column'] = pd.to_numeric(data['column'])
# Returns a new column without replacing the previous one.
# If you want to replace it, you'll need to execute an assignment.

Converting column values to a different data type

data['column'].astype('type') # For example, int for integers, str for strings.
data['column'] = data['column'].astype('type')
# Returns a new column without replacing the previous one.
# If you want to replace it, you'll need to execute an assignment