manipulation

Question 1

Union two datasets

Answer 1

pd.concat([df1, df2], ignore_index = True)

ignore_index is set so that the index continues counting and doesn’t restart at 0 when new dataset starts

Question 2

Union two datasets with different column names (and keep only the common columns)

Answer 2

pd.concat([df1, df2], join = ‘inner’)

Question 3

union two datasets with different columns (and keep all columns)

Answer 3

pd.concat([df1, df2], sort = True)

Question 4

Inner join

Answer 4

df = df1.merge(df2, on = “key”, how = “inner”)

Inner join is the default for .merge() so not necessary

Question 5

Inner join and add to the variables the original table as source

Answer 5

df = df1.merge(df2, on = “key”, how = “inner”, suffixes = (“_t1”, “_t2”))

Question 6

Left join with datasets where key is named differently

Answer 6

df = df1.merge(df2, left_on = “left_key”, right_on = “right_key”, how = “left”)

Question 7

Left join three datasets

Answer 7

df = df1.merge(df2, on = “key”, how = “left”) \

.merge(df3, on = “key”, how = “left”)

Question 8

Full join

Answer 8

df = df1.merge(df2, on = “key”, how = “outer”)

Question 9

Manipulate a wide into a long dataset

Example scores per year:
var1 var2 2016 2017 2018

Answer 9

df = df.melt(id_vars = [‘var1’, ‘var2’], var_name = [‘years’], value_name = “score”]

Question 10

Find complete duplicates, and drop complete duplicates

Answer 10

Find duplicates:
df[df.duplicated()]

Drop duplicates:
df.drop_duplicates(inplace = True)

Question 11

Hardcode with upper limit

ex: check if any score above 10, if yes replace with 10

Answer 11

df.loc[df[“var”] > 10, “var”] = 10

First part of loc identifies the rows (where df[‘var’] > 10), second part the column ‘var’

Question 12

Sort the dataframe based on a variable

Answer 12

df.sort_values(by = “id”)

Question 13

Drop rows via filtering

Example: drop all rows where var < 0

Answer 13

df = df[df[“var] > 0]

Question 14

Drop row via drop statement

Example: all cases where var < 0

Answer 14

df.drop(df[df[‘var’] < 0].index, inplace = True)

Question 15

Change string into integer

Answer 15

df[‘var’] = df[‘var’].astype(‘int’)

Question 16

Change integer into categorical

Answer 16

df[‘var’] = df[‘var’].astype(‘category’)

Question 17

Change string into date

Answer 17

df[‘date”] = pd.to_datetime(df[‘date’]).dt.date

Question 18

Remove $ sign from a string variable

Answer 18

df[“var”] = df[“var”].str.strip(‘$’)

Question 19

How to continue code on the next line

Answer 19

\

Question 20

Find all cases with a date in the future

Answer 20

import datetime as dt

df[df[‘date’] > dt.date.today()]

Question 21

Today’s date

Answer 21

import datetime as dt

today_date = dt.date.today()

Question 22

Get indication on type of join to avoid additional rows

Answer 22

.merge(validate = ‘one_to_one’)

of ook:
‘one_to_many’
‘many_to_many’
‘many_to_one’

Question 23

Joins - add indicator from in which table(s) the case was present

Answer 23

.merge(indicator = True)

Question 24

Check if value is in a list of ID’s from another dataset

Answer 24

df[‘id’].isin(df2[‘id’]

Question 25

Method to join time series

Answer 25

.merge_ordered()

Main difference with .merge -> default outer join.
More options to join based on dates and nearest match

Question 26

Join time series data with data lag on missing values

Answer 26

pd.merge_ordered(df1, df2, on = “key”, fill_method = “ffill”

Question 27

Join time series on nearest matching date

Answer 27

pd.merge_asof(df1, df2, on = “key”)

direction can be specified.
tables must be presorted on key

Question 28

SQL query in python

Answer 28

df.query(‘VAR > 0’)

Question 29

Sort the dataframe descending

Answer 29

df.sort_values(“var”, ascending = False)

Question 30

Sorting by two different variables, the first ascending, the second descending

Answer 30

df.sort_values([“var1”, “var2”], ascending = [True, False])

Question 31

Subsetting a column

Answer 31

df[“var”]

Question 32

Subsetting multiple columns

Answer 32

df[[“var1”, “var2”]]

Question 33

Subsetting rows

• greater than zero
• part of a list

Answer 33

df[df[‘var”] > 0]

df[df[‘var’].isin([‘option1’, ‘option2’])]

Question 34

Setting an index

Answer 34

df = df.set_index(‘var’)

Question 35

Removing an index

Answer 35

df = df.reset_index()

index will become a variable again unless argument (drop = True) is used

Question 36

Two ways to select a column

Answer 36

df[‘var’]
df.var

if var has only letters/numbers/underscore

Question 37

Select two columns

Answer 37

df[[‘col1’, ‘col2’]]

Question 38

Difference between df[‘var’] and df[[‘var’]]

Answer 38

df[‘var’] will return the column as a pandas series

df[[‘var’]] will return the column as a pandas dataframe

Question 39

Difference loc and iloc

Answer 39

Loc is label-based: specify rows and columns by their row and column labels
iloc is index based: specifcy rows and columns by their integer index (zero based indexing!)

df. loc[‘BE’, ‘population’] –> selects row with index BE and column population
df. loc[[‘BE’, ‘NL’], ‘population’]

df.iloc[1, 0]