40 Pandas Interview Questions (ANSWERED + CODE) For Data Scientists And ML Engineer

• We create a new column by assigning the output to the DataFrame with a new column name in between the [].
• Let's say we want to create a new column 'C' whose values are the multiplication of column 'B' with column 'A'. The operation will be easy to implement and will be element-wise, so there's no need to loop over rows.

import pandas as pd

# Create example data
df = pd.DataFrame({
  "A": [420, 380, 390],
  "B": [50, 40, 45]
})

df["C"] = df["A"] * df["B"]

• Also other mathematical operators (+, -, \*, /) or logical operators (<, >, =,…) work element-wise. But if we need more advanced logic, we can use arbitrary Python code via apply().
• Depending on the case, we can use rename with a dictionary or function to rename row labels or column names according to the problem.

A succinct way to convert a single column of boolean values to a column of integers 1 or 0 is:

df["somecolumn"] = df["somecolumn"].astype(int)

• By Simply iterating over columns, and printing the values.

for col in data.columns:
    print(col)

• Using .columns() method with the dataframe object, this returns the column labels of the DataFrame.

list(data.columns)

• Using the column.values() method to return an array of index.

list(data.columns.values)

• Using sorted() method, which will return the list of columns sorted in alphabetical order.

sorted(data)

• DataFrame.iloc is a method used to retrieve data from a Data frame, and it is an integer position-based locator (from 0 to length-1 of the axis), but may also be used with a boolean array. It takes input as integer, arrays of integers, a slice object, boolean array and functions.

df.iloc[0]
df.iloc[-5:]
df.iloc[:, 2]    # the : in the first position indicates all rows
df.iloc[:3, :3] # The upper-left 3 X 3 entries (assuming df has 3+ rows and columns)

• DataFrame.loc gets rows (and/or columns) with particular labels. It takes input as a single label, list of arrays and slice objects with labels.

df = pd.DataFrame(index=['a', 'b', 'c'], columns=['time', 'date', 'name'])
df.loc['a']     # equivalent to df.iloc[0]
df.loc['b':, 'date']   # equivalent to df.iloc[1:, 1]

The function used for sorting in pandas is called DataFrame.sort_values(). It is used to sort a DataFrame by its column or row values. The function comes with a lot of parameters, but the most important ones to consider for sort are:

• by: The optional by parameter is used to specify the column/row(s) which are used to determine the sorted order.
• axis: specifies whether sort for row (0) or columns (1),
• ascending: specifies whether to sort the dataframe in ascending or descending order. The default value is ascending. To sort in descending order, we need to specify ascending=False.

Example

>>> df = pd.DataFrame({
    'col1': ['A', 'A', 'B', np.nan, 'D', 'C'],
    'col2': [2, 1, 9, 8, 7, 4],
    'col3': [0, 1, 9, 4, 2, 3],
    'col4': ['a', 'B', 'c', 'D', 'e', 'F']
})

>>> df
  col1  col2  col3 col4
0    A     2     0    a
1    A     1     1    B
2    B     9     9    c
3  NaN     8     4    D
4    D     7     2    e
5    C     4     3    F

#Sort by col1
>>> df.sort_values(by=['col1'])
  col1  col2  col3 col4
0    A     2     0    a
1    A     1     1    B
2    B     9     9    c
5    C     4     3    F
4    D     7     2    e
3  NaN     8     4    D


# Sort by multiple columns
>>> df.sort_values(by=['col1', 'col2'])
  col1  col2  col3 col4
1    A     1     1    B
0    A     2     0    a
2    B     9     9    c
5    C     4     3    F
4    D     7     2    e
3  NaN     8     4    D

# Sort descending
df.sort_values(by='col1', ascending=False)
  col1  col2  col3 col4
4    D     7     2    e
5    C     4     3    F
2    B     9     9    c
0    A     2     0    a
1    A     1     1    B
3  NaN     8     4    D

Use the pandas idxmax and idxmin function. It's straightforward:

• Maximal value:

  >>> df['A'].idxmax()
      4

• Minimal value:

  >>>df['A'].idxmin()
     1

We can use the function select_dtypes() to select columns by dtype and then use the columns attribute to save the selected columns into a list.

In [2]: df = pd.DataFrame({'NAME': list('abcdef'),
        'On_Time': [True, False] * 3,
        'On_Budget': [False, True] * 3})

In [3]: df.select_dtypes(include=['bool'])
Out[3]:
          On_Budget On_Time
        0     False    True
        1      True   False
        2     False    True
        3      True   False
        4     False    True
        5      True   False

In [4]: mylist = list(df.select_dtypes(include=['bool']).columns)

In [5]: mylist
Out[5]: ['On_Budget', 'On_Time']

• In the first stage of the process, data contained in a pandas object, whether a Series, DataFrame, or otherwise, is split into groups based on one or more keys that we provide.

• The splitting is performed on a particular axis of an object. For example, a DataFrame can be grouped on its rows (axis=0) or its columns (axis=1).

• Once this is done, a function is applied to each group, producing a new value. Finally, the results of all those function applications are combined into a result object. The form of the resulting object will usually depend on what's being done to the data.

• In the figure below, this process is illustrated for a simple group aggregation.

You can use str.split by whitespace (default separator) and parameter expand=True for DataFrame with assign to new columns:

>>> df[['code','location']] = df['row'].str.split(n=1, expand=True)
>>> df
        row				code	location
    0	00000 UNITED STATES		00000	UNITED STATES
    1	01000 ALABAMA			01000	ALABAMA
    2	01001 Autauga County, AL	01001	Autauga County, AL
    3	01003 Baldwin County, AL	01003	Baldwin County, AL
    4	01005 Barbour County, AL	01005	Barbour County, AL

You can use the attribute df.empty to check whether it's empty or not:

if df.empty:
    print('DataFrame is empty!')

DataFrame.iterrows is a generator which yields both the index and row (as a Series):

import pandas as pd

df = pd.DataFrame({'c1': [10, 11, 12], 'c2': [100, 110, 120]})

for index, row in df.iterrows():
    print(row['c1'], row['c2'])

10 100
11 110
12 120

• Splitting the data into groups based on some criteria.
• Applying a function to each group independently.
• Combining the results into a data structure.

The percentiles describe the distribution of your data: 50 should be a value that describes the middle of the data, also known as median. 25, 75 is the border of the upper/lower quarter of the data. With this can get an idea of how skew our data is.

If the mean is higher than the median, the data is right skewed.

In general, it is safer to work on copies than on original DataFrames, except when you know that you won't be needing the original anymore and want to proceed with the manipulated version.

This is because in Pandas, indexing a DataFrame returns a reference to the initial DataFrame. Thus, changing the subset will change the initial DataFrame. Thus, you'd want to use the copy if you want to make sure the initial DataFrame shouldn't change.

Normally, you would still have some use for the original data frame to compare with the manipulated version, etc. Therefore, depending on the case it's a good practice to work on copies and merge at the end.

• The map() method is an elementwise method for only Pandas Series, it maps values of Series according to input correspondence.

  • It accepts dicts, Series, or callable. Values that are not found in the dict are converted to NaN,

# Example:

s = pd.Series(['cat', 'dog', np.nan, 'rabbit'])
print(s)
0      cat
1      dog
2      NaN
3   rabbit

s.map({'cat': 'kitten', 'dog': 'puppy'})

0   kitten
1    puppy
2      NaN
3      NaN

• The applymap() method is an elementwise function for only DataFrames, it applies a function that accepts and returns a scalar to every element of a DataFrame.

  • It accepts callables only i.e a Python function.

# Example:
 
df = pd.DataFrame([[1, 2.12], [3.356, 4.567]])
print(df)
       0      1
0  1.000  2.120
1  3.356  4.567

# Square each element

df.applymap(lambda x: x**2)
           0          1
0   1.000000   4.494400
1  11.262736  20.857489

• The apply() method also works elementwise, as it applies a function along input axis of DataFrame. It is suited to more complex operations and aggregation.

  • It accepts the callables parameter as well.

df = pd.DataFrame([[4, 9]] * 3, columns=['A', 'B'])

print(df)
   A  B
0  4  9
1  4  9
2  4  9

df.apply(np.sqrt)

     A    B
0  2.0  3.0
1  2.0  3.0
2  2.0  3.0

• Using .merge() method which merges DataFrame or named Series objects with a database-style join. You have inner, left, right and outer merge operation.

• By default, the Pandas merge operation acts with an “inner” merge. An inner merge, keeps only the common values in both the left and right dataframes for the result.

• Left merge, keeps every row in the left dataframe. Where there are missing values of the “on” variable in the right dataframe, it adds empty / NaN values in the result.
• Right merge, keeps every row in the right dataframe. Where there are missing values of the “on” variable in the left column, it adds empty / NaN values in the result.
• A full outer join returns all the rows from the left dataframe, all the rows from the right dataframe, and matches up rows where possible, with NaNs elsewhere

df1 = pd.DataFrame({'a': ['foo', 'bar'], 'b': [1, 2]})
df2 = pd.DataFrame({'a': ['foo', 'baz'], 'c': [3, 4]})
df1
      a  b
0   foo  1
1   bar  2
df2
      a  c
0   foo  3
1   baz  4

df1.merge(df2, how='inner', on='a')
      a  b  c
0   foo  1  3

df1.merge(df2, how='left', on='a')
      a  b  c
0   foo  1  3.0
1   bar  2  NaN

• If [0, 1, 2] are numerical labels and is not the index, then pandas.DataFrame.pivot_table works:

  In []:
  data = pd.DataFrame.from_records(
      [[0, 'A'], [0, 'B'], [1, 'B'], [1, 'C'], [1, 'D'], [2, 'B'], [2, 'D']],
      columns=['number_label', 'category'])
  data.pivot_table(index=['number_label'], columns=['category'], aggfunc=[len], fill_value=0)

  Out[]:
                len
  category      A      B      C      D
  number_label                       
  0             1      1      0      0
  1             0      1      1      1
  2             0      1      0      1

• Now if [0, 1, 2] is the index, then collections.Counter is useful:

  In []:
  >>> import collections
  >>> data2 = pd.DataFrame.from_dict(
      {'categories': {0: ['A', 'B'], 1: ['B', 'C', 'D'], 2:['B', 'D']}})
  >>> data3 = data2['categories'].apply(collections.Counter)
  >>> data3 = pd.DataFrame.from_records(data3).fillna(value=0)
  >>> data3.applymap(lambda x: int(x))

  Out[]:
        A      B      C      D
  0      1      1      0      0
  1      0      1      1      1
  2      0      1      0      1

• The approach here is to use groupby to group on the column of interest and then use the apply() method to apply list the function to every group generated:

Consider:

df1 = df.groupby('a')['b'].apply(list).reset_index(name='new')

   a        new
0  A     [1, 2]
1  B  [5, 5, 4]
2  C        [6]

• Use pd.Series.isin.
• For IN use: something.isin(somewhere)

df[df['A'].isin([3, 6])]

• For NOT IN: ~something.isin(somewhere)

df[-df["A"].isin([3, 6])]
df[~df["A"].isin([3, 6])]
df[df["A"].isin([3, 6]) == False]
df[np.logical_not(df["A"].isin([3, 6]))]

• For alternative readable solution use .query() method:

In [5]: df.query("countries in @countries")
Out[5]:
  countries
1        UK
3     China

In [6]: df.query("countries not in @countries")
Out[6]:
  countries
0        US
2   Germany

We can create a mask to separate the dataframe and then use the inverse operator (~) to take the complement of the mask.

import pandas as pd

# Create example data
>>> df = pd.DataFrame({'A': 'foo bar foo bar foo bar foo foo'.split(),
                   'B': 'one one two three two two one three'.split(),
                   'C': np.arange(8), 'D': np.arange(8) * 2})
>>> df
	A	B	C	D
0	foo	one	0	0
1	bar	one	1	2
2	foo	two	2	4
3	bar	three	3	6
4	foo	two	4	8
5	bar	two	5	10
6	foo	one	6	12
7	foo	three	7	14

# Mask with boolean criterion
>>> m = df['A'] != 'foo'
# Split dataframe
>>> a, b = df[m], df[~m]
# Results
>>> a
	A	B	C	D
1	bar	one	1	2
3	bar	three	3	6
5	bar	two	5	10
>>> b
	A	B	C	D
0	foo	one	0	0
2	foo	two	2	4
4	foo	two	4	8
6	foo	one	6	12
7	foo	three	7	14

Using Pandas to_sql module, you can create an SQLAlchemy engine, and write records stored in a DataFrame to a SQL database.

from sqlalchemy import create_engine
engine = create_engine('your_connection_string')
df.to_sql('table_name', engine)

We can use LabelEncoder to encode a Pandas DataFrame of string or numerical labels. If the dataframe has many columns (50+, for example) creating a LabelEncoder for each feature is not efficient. In scikit-learn, the recommended way is to encode all the features is:

OneHotEncoder().fit_transform(df)

On other hand, if we are interested in applying OneHotEncoder only to certain columns then we can use ColumnTransformer.

Depending on the problem, continuous values can be discretized using the cut() or qcut() function:

• cut() bins the data based on values. We use it when we need to segment and sort data values into bins evenly spaced. cut will choose the bins to be evenly spaced according to the values themselves and not the frequency of those values. For example, cut could convert ages to groups of age ranges.

• qcut() bins the data based on sample quantiles. We use it when we want to have the same number of records in each bin or simply study the data by quantiles. For example, if in a data we have 30 records, and we want to compute the quintiles, qcut() will divide the data such that we have 6 records in each bin.

scikit learn's train_test_split is a good one - it will split both numpy arrays as dataframes.

from sklearn.model_selection import train_test_split

train, test = train_test_split(df, test_size=0.2)

Iteration in Pandas is an anti-pattern and is something you should only do when you have exhausted every other option. Iterating through pandas objects is generally slow. In many cases, iterating manually over the rows is not needed. You should not use any function with "iter" in its name for more than a few thousand rows or you will have to get used to a lot of waiting.

Do you want to print a DataFrame? Use DataFrame.to_string().

Do you want to compute something? In that case, search for methods in this order (list modified from here):

1. Vectorization
2. Cython routines
3. List Comprehensions (vanilla for loop)
4. DataFrame.apply(): i)  Reductions that can be performed in Cython, ii) Iteration in Python space
5. DataFrame.itertuples() and iteritems()
6. DataFrame.iterrows()

iterrows and itertuples (both receiving many votes in answers to this question) should be used in very rare circumstances, such as generating row objects/nametuples for sequential processing, which is really the only thing these functions are useful for.

• to_numeric() - provides functionality to safely convert non-numeric types (e.g. strings) to a suitable numeric type.
• astype() - convert (almost) any type to (almost) any other type. Also allows you to convert to categorial types (very useful).
• infer_objects() - a utility method to convert object columns holding Python objects to a pandas type if possible. It does this by inferring better dtypes for object columns.
• convert_dtypes() - convert DataFrame columns to the "best possible" dtype that supports pd.NA (pandas' object to indicate a missing value).

• The applymap() method is good for elementwise transformations across multiple rows/columns.

df[['A', 'B', 'C']].applymap(str.strip)

• The apply method is useful for applying any function that cannot be vectorised.

df['sentences'].apply(nltk.sent_tokenize)

To provide the required df we use the function pivot_table with the parameters index='Col X', columns='Col Y' and as aggfunc, len:

>>> pd.pivot_table(df, index=['Col X'], columns=['Col Y'], aggfunc=len, fill_value=0)
    Col Y	cat 1	cat 2	cat 3
    Col X			
    class 1	    1	    0	    0
    class 2	    1	    0	    1
    class 3	    0	    1	    0

Another solution is using groupby on 'Col X','Col Y' with unstack over Col Y, then fill NaNs with zeros.

>>> df.groupby(['Col X','Col Y']).size().unstack('Col Y', fill_value=0)
['Col Y'], aggfunc=len, fill_value=0)
    Col Y	cat 1	cat 2	cat 3
    Col X			
    class 1	    1	    0	    0
    class 2	    1	    0	    1
    class 3	    0	    1	    0

merge is a function in the pandas namespace, and it is also available as a DataFrame instance method, with the calling DataFrame being implicitly considered the left object in the join.

The related DataFrame.join method, uses merge internally for the index-on-index and index-on-column(s) joins, but joins on indexes by default rather than trying to join on common columns (the default behavior for merge). If you are joining on index, you may wish to use DataFrame.join to save yourself some typing.

These are the main differences between df.join() and df.merge():

1. lookup on right table: df1.join(df2) always joins via the index of df2, but df1.merge(df2) can join to one or more columns of df2 (default) or to the index of df2 (with right_index=True).
2. lookup on left table: by default, df1.join(df2) uses the index of df1 and df1.merge(df2) uses column(s) of df1. That can be overridden by specifying df1.join(df2, on=key_or_keys) or df1.merge(df2, left_index=True).
3. left vs inner join: df1.join(df2) does a left join by default (keeps all rows of df1), but df.merge does an inner join by default (returns only matching rows of df1 and df2).

At a high level:

• .concat() simply stacks multiple DataFrame together either vertically, or stitches horizontally after aligning on index
• .merge() first aligns two DataFrame' selected common column(s) or index, and then pick up the remaining columns from the aligned rows of each DataFrame.

More specifically, .concat():

• Is a top-level pandas function
• Combines two or more pandas DataFrame vertically or horizontally
• Aligns only on the index when combining horizontally
• Errors when any of the DataFrame contains a duplicate index.
• Defaults to outer join with the option for inner join

And .merge():

• Exists both as a top-level pandas function and a DataFrame method (as of pandas 1.0)
• Combines exactly two DataFrame horizontally
• Aligns the calling DataFrame's column(s) or index with the other DataFrame's column(s) or index
• Handles duplicate values on the joining columns or index by performing a cartesian product
• Defaults to inner join with options for left, outer, and right

at and iat are functions meant to access a scalar, that is, a single element in the dataframe.

• With .at:

  • Selection is label based but it only selects a single 'cell' in your DataFrame.
  • We can assign new indices and columns.
  • To use .at, pass it both a row and column label separated by a comma.

• With .iat:

  • Selection with .iat is position based but it only selects a single scalar value.
  • We can't assign new indices and columns.
  • To use iat you must pass it an integer for both the row and column locations.

>>> df = pd.DataFrame([[0, 2, 3], [0, 4, 1], [10, 20, 30]],
     columns=['A', 'B', 'C'],
     index=['first', 'second', 'third'])

>>> df
         	A	B	C
    first	0	2	3
    second	0	4	1
    third	10	20	30

>>> df.at['second','B',]
    4
>>> df.iat[1, 1]
    4

• fillna() fills the NaN values with a given number with which you want to substitute. It gives you an option to fill according to the index of rows of a pd.DataFrame or on the name of the columns in the form of a python dict.

• interpolate() it gives you the flexibility to fill the missing values with many kinds of interpolations between the values like linear, time, etc (which fillna does not provide).

Both pivot_table and groupby are used to aggregate your dataframe. The difference is only with regard to the shape of the result.

• When using pd.pivot_table(df, index=["a"], columns=["b"], values=["c"], aggfunc=np.sum) a table is created where:

  • a is on the row axis,
  • b is on the column axis,

  • and the values are the sum of c.

  For example,

  df = pd.DataFrame({"a": [1,2,3,1,2,3], "b":[1,1,1,2,2,2], "c":np.random.rand(6)})
  pd.pivot_table(df, index=["a"], columns=["b"], values=["c"], aggfunc=np.sum)
            c
  b         1         2
  a                    
  1  0.528470  0.484766
  2  0.187277  0.144326
  3  0.866832  0.650100

• Using groupby:

  • The dimensions given are placed into columns.
  • The rows are created for each combination of those dimensions.
  • To obtain the "equivalent" output as before, we can create a Series of the sum of values c, grouped by all unique combinations of a and b.

  df.groupby(['a','b'])['c'].sum()
    a  b
    1  1    0.951714
       2    0.270743
    2  1    0.661338
       2    0.343530
    3  1    0.210315
       2    0.220240
  Name: c, dtype: f

  • A similar usage of groupby is if we omit the ['c']. In this case, it creates a DataFrame of the sums of all remaining columns grouped by unique values of a and b.

  df.groupby(["a","b"]).sum()
      		c
  a	b	
  1	1	0.951714
  	2	0.270743
  2	1	0.661338
  	2	0.343530
  3	1	0.210315
  	2	0.220240

As we can see, with pivot_table() the output is a DataFrame, and is easier to specify the changes, meanwhile with groupby() the output can be a DataFrame or Series depending on how we specify the case.

• Consider .concat() first when combining homogeneous DataFrame, while consider .merge() first when combining complementary DataFrame.
• If need to merge vertically, go with .concat(). If need to merge horizontally via columns, go with .merge(), which by default merge on the columns in common.