3rd

Question 1

move a column to become the index

Answer 1

df.set_index(‘c’)

Question 2

Reset index

Answer 2

df.reset_index()

Question 3

Remove index col

Answer 3

df.reset_index(drop = True)

Question 4

Multiple index columns

Answer 4

df.set_index([‘c’, ‘c1’])

Question 5

Select only year from date

Answer 5

df[‘c’].dt.year

Question 6

Select only month from date

Answer 6

df[‘c’].dt.month

Question 7

Detect missing values

Answer 7

df.isna()

           name           breed
0         false             false
1          false             True
2         false             True
3         false              False

Question 8

Does df columns have NA

Answer 8

df.isna().any()

name false
breed True
color True

Question 9

Count # of na in a column

Answer 9

df.isna().sum()

name 0
breed 2
color 3

Question 10

Remove all NAs from a df

Answer 10

df.dropna()

Question 11

Replace NAs with a missing value

Answer 11

df.fillna(0)

Question 12

List of dictionaries

Answer 12

list_of_dicts = [
{‘name’: ‘Ginger’, ‘Breed : ‘Lab’, ‘kg’:22},
{‘name’: ‘qwuire’, ‘Breed : ‘good’, ‘kg’:12}
]

              name               breed             kg 0               Ginger               Lab               22 1                qwuire               good             12

Question 13

Dictionary of lists

Answer 13

dict_of_lists = {
‘name’ : [‘Ginger’, ‘qwuire’],
‘breed’ : [‘Lab’, ‘good’],
‘weight’ : [22, 12]
}

              name               breed             kg 0               Ginger               Lab               22 1                qwuire               good             12

Question 14

Write csv

Answer 14

pd.to_csv(‘file/path’)

Question 15

Inner join

Answer 15

Only return rows where the values match in both tables

new_df = df1.merge(df2, on = ‘col’)

Question 16

Add suffixes to joins

Answer 16

suffixes = (‘_x’, ‘_y’)

new_df = df1.merge(df2, on = ‘col’, suffixes = (‘_x’, ‘_y’))

Question 17

Merge on multiple columns

Answer 17

new_df = df1.merge(df2, on = [‘col’, ‘col1’])

Question 18

Merge multiple tables

Answer 18

new_df = df1.merge(df2, on = ‘col’) \ .merge(df3, on = ‘col1’)

Question 19

Left join

Answer 19

returns all rows of data from the left table and only the rows from the right table that match

new_df = df1.merge(df2, on = ‘col’, how = ‘left’)

Question 20

Merge on same column with two different names

Answer 20

new_df = df1.merge(df2, left_on = ‘df1_col’, right_on = ‘df2_col’ )

Question 21

Outer join

Answer 21

Join both columns regardless if there is a match between the two tables

new_df = df1.merge(df2, on = ‘col’, how = ‘outer’)

Question 22

Semi join

Answer 22

only columns from the left table, but with no duplicates. The first and then complete and moves on.

Question 23

Confirm your the filtered joins

Answer 23

new_df = df1.merge(df2, on = ‘col’)
obs = df1[df1[‘col’].isin(new_df[‘col’])]
print(obs)

Question 24

Anti Join

Answer 24

Only return columns from the left table and not the right table

Step 1
new_df = df1.merge(df2, on = ‘col’, how = ‘left’, indicator = True)

Step 2
df_bool = df_merge.loc[df_merge[‘_merge’] == ‘left_only’, ‘col’]

Step 3
df_left = df1[df1[‘col’].isin(df_bool)]

Question 25

Concatenate

Answer 25

pd.concat([df, df1, df2], axis = 0)

Question 26

Concatenate and ignore index

Answer 26

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

Question 27

Concatenate and set labels in index of original tables

Answer 27

pd.concat([df, df1, df2], axis = 0, ignore_index = False, keys = [‘1’, ‘2’, ‘3’])

          name     cid
1    0      jj         234
     1       ss         11
2   0
      1
3   0
      1

Question 28

Merge Ordered

Answer 28

Helpful for ordered or time series data as the results are sorted

df_merge = pd.merge_ordered(df1, df2, on = ‘col’, suffixes = (‘_df1’, ‘_df2’))

Question 29

Merge ordered forward fill

Answer 29

fill missing with previous value

df_merge = pd.merge_ordered(df1, df2, on = ‘col’, suffixes = (‘_df1’, ‘_df2’), fill_method = ‘ffill’)

Question 30

Merge as of

Answer 30

Similar to merge ordered

merges to the nearest key column and not exact matches

The merged on columns must be sorted

df_merge = pd.per_asof(df1, df2, on = ‘col’, suffixes = (‘_df1’, ‘_df2’))