w3resource

Pandas Joining and merging DataFrame : Exercises, Practice, Solution

[An editor is available at the bottom of the page to write and execute the scripts.]

Pandas Joining and merging DataFrame [ 15 exercises with solution]

1. Write a Pandas program to join the two given dataframes along rows and assign all data. Go to the editor
Test Data:

student_data1:
  student_id              name  marks
0         S1  Danniella Fenton    200
1         S2      Ryder Storey    210
2         S3      Bryce Jensen    190
3         S4         Ed Bernal    222
4         S5       Kwame Morin    199

student_data2:
  student_id              name  marks
0         S4  Scarlette Fisher    201
1         S5  Carla Williamson    200
2         S6       Dante Morse    198
3         S7    Kaiser William    219
4         S8   Madeeha Preston    201
Click me to see the sample solution

2. Write a Pandas program to join the two given dataframes along columns and assign all data. Go to the editor
Test Data:

student_data1:
  student_id              name  marks
0         S1  Danniella Fenton    200
1         S2      Ryder Storey    210
2         S3      Bryce Jensen    190
3         S4         Ed Bernal    222
4         S5       Kwame Morin    199

student_data2:
  student_id              name  marks
0         S4  Scarlette Fisher    201
1         S5  Carla Williamson    200
2         S6       Dante Morse    198
3         S7    Kaiser William    219
4         S8   Madeeha Preston    201
Click me to see the sample solution

3. Write a Pandas program to append rows to an existing DataFrame and display the combined data. Go to the editor
Test Data:

tudent_data1
  student_id              name  marks
0         S1  Danniella Fenton    200
1         S2      Ryder Storey    210
2         S3      Bryce Jensen    190
3         S4         Ed Bernal    222
4         S5       Kwame Morin    199

New Row(s)
student_id                  S6
name          Scarlette Fisher
marks                      205
dtype: object
Click me to see the sample solution

4. Write a Pandas program to append a list of dictioneries or series to a existing DataFrame and display the combined data. Go to the editor
Test Data:

  student_id              name  marks
0         S1  Danniella Fenton    200
1         S2      Ryder Storey    210
2         S3      Bryce Jensen    190
3         S4         Ed Bernal    222
4         S5       Kwame Morin    199

Dictionary:
student_id                  S6
name          Scarlette Fisher
marks                      205
dtype: object
Click me to see the sample solution

5. Write a Pandas program to join the two given dataframes along rows and merge with another dataframe along the common column id. Go to the editor
Test Data:

student_data1:
  student_id              name  marks
0         S1  Danniella Fenton    200
1         S2      Ryder Storey    210
2         S3      Bryce Jensen    190
3         S4         Ed Bernal    222
4         S5       Kwame Morin    199

student_data2:
  student_id              name  marks
0         S4  Scarlette Fisher    201
1         S5  Carla Williamson    200
2         S6       Dante Morse    198
3         S7    Kaiser William    219
4         S8   Madeeha Preston    201

exam_data:
   student_id  exam_id
0          S1       23
1          S2       45
2          S3       12
3          S4       67
4          S5       21
5          S7       55
6          S8       33
7          S9       14
8         S10       56
9         S11       83
10        S12       88
11        S13       12
Click me to see the sample solution

6. Write a Pandas program to join the two dataframes using the common column of both dataframes. Go to the editor
Test Data:

student_data1:
  student_id              name  marks
0         S1  Danniella Fenton    200
1         S2      Ryder Storey    210
2         S3      Bryce Jensen    190
3         S4         Ed Bernal    222
4         S5       Kwame Morin    199

student_data2:
  student_id              name  marks
0         S4  Scarlette Fisher    201
1         S5  Carla Williamson    200
2         S6       Dante Morse    198
3         S7    Kaiser William    219
4         S8   Madeeha Preston    201
Click me to see the sample solution

7. Write a Pandas program to join the two dataframes with matching records from both sides where available. Go to the editor
Test Data:

student_data1:
  student_id              name  marks
0         S1  Danniella Fenton    200
1         S2      Ryder Storey    210
2         S3      Bryce Jensen    190
3         S4         Ed Bernal    222
4         S5       Kwame Morin    199

student_data2:
  student_id              name  marks
0         S4  Scarlette Fisher    201
1         S5  Carla Williamson    200
2         S6       Dante Morse    198
3         S7    Kaiser William    219
4         S8   Madeeha Preston    201
Click me to see the sample solution

8. Write a Pandas program to join (left join) the two dataframes using keys from left dataframe only. Go to the editor
Test Data:

data1:
  key1 key2   P   Q
0   K0   K0  P0  Q0
1   K0   K1  P1  Q1
2   K1   K0  P2  Q2
3   K2   K1  P3  Q3

data2:
  key1 key2   R   S
0   K0   K0  R0  S0
1   K1   K0  R1  S1
2   K1   K0  R2  S2
3   K2   K0  R3  S3
Click me to see the sample solution

9. Write a Pandas program to join two dataframes using keys from right dataframe only. Go to the editor
Test Data:

data1:
  key1 key2   P   Q
0   K0   K0  P0  Q0
1   K0   K1  P1  Q1
2   K1   K0  P2  Q2
3   K2   K1  P3  Q3

data2:
  key1 key2   R   S
0   K0   K0  R0  S0
1   K1   K0  R1  S1
2   K1   K0  R2  S2
3   K2   K0  R3  S3
Click me to see the sample solution

10. Write a Pandas program to merge two given datasets using multiple join keys. Go to the editor
Test Data:

data1:
  key1 key2   P   Q
0   K0   K0  P0  Q0
1   K0   K1  P1  Q1
2   K1   K0  P2  Q2
3   K2   K1  P3  Q3

data2:
  key1 key2   R   S
0   K0   K0  R0  S0
1   K1   K0  R1  S1
2   K1   K0  R2  S2
3   K2   K0  R3  S3
Click me to see the sample solution

11. Write a Pandas program to create a new DataFrame based on existing series, using specified argument and override the existing columns names. Go to the editor
Click me to see the sample solution

12. Write a Pandas program to create a combination from two dataframes where a column id combination appears more than once in both dataframes.Go to the editor
Test Data: 

data1:
  key1 key2   P   Q
0   K0   K0  P0  Q0
1   K0   K1  P1  Q1
2   K1   K0  P2  Q2
3   K2   K1  P3  Q3

data2:
  key1 key2   R   S
0   K0   K0  R0  S0
1   K1   K0  R1  S1
2   K1   K0  R2  S2
3   K2   K0  R3  S3
Click me to see the sample solution

13. Write a Pandas program to combine the columns of two potentially differently-indexed DataFrames into a single result DataFrame. Go to the editor
Test Data: 

data1:
     A   B
K0  A0  B0
K1  A1  B1
K2  A2  B2

data2:
     C   D
K0  C0  D0
K2  C2  D2
K3  C3  D3
Click me to see the sample solution

14. Write a Pandas program to merge two given dataframes with different columns. Go to the editor
Test Data: 

data1:
  key1 key2   P   Q
0   K0   K0  P0  Q0
1   K0   K1  P1  Q1
2   K1   K0  P2  Q2
3   K2   K1  P3  Q3

data2:
  key1 key2   R   S
0   K0   K0  R0  S0
1   K1   K0  R1  S1
2   K1   K0  R2  S2
3   K2   K0  R3  S3
Click me to see the sample solution

15. Write a Pandas program to Combine two DataFrame objects by filling null values in one DataFrame with non-null values from other DataFrame. Go to the editor
Test Data: 

Original DataFrames:
     A  B
0  NaN  3
1  0.0  4
2  NaN  5

   A    B
0  1  3.0
1  1  NaN
2  3  3.0
Click me to see the sample solution

Python Code Editor:


More to Come !

Do not submit any solution of the above exercises at here, if you want to contribute go to the appropriate exercise page.

Test your Python skills with w3resource's quiz



Python: Tips of the Day

Python: Kwargs

**kwargs and *args are function arguments that can be very useful.

They are quite underused and often under-understood as well.

Let's try to explain what kwargs are and how to use them.

  • While *args are used to pass arguments at an unknown amount to functions, **kwargs are used to do the same but with named arguments.
  • So, if *args is a list being passed as an argument, you can think of **kwargs as a dictionary that's being passed as an argument to a function.
  • You can use arguments as you wish as long as you follow the correct order which is: arg1, arg2, *args, **kwargs. It's okay to use only one of those but you can't mix the order, for instance, you can't have: function(**kwargs, arg1), that'd be a major faux pas in Python.
  • Another example: You can do function(*args,**kwargs) since it follows the correct order.
  • Here is an example. Let's say satelites are given with their names and weight in tons in dictionary format. Code prints their weight as kilograms along with their names.
def payloads(**kwargs):
    for key, value in kwargs.items():
        print( key+" |||", float(value)*100)
payloads(NavSat1 = '2.5', BaysatG2 = '4')

Output:

NavSat1 ||| 250.0
BaysatG2 ||| 400.0

Since the function above would work for any number of dictionary keys, **kwargs makes perfect sense rather than passing arguments with a fixed amount.

def payloads(**kwargs):
    for key, value in kwargs.items():
        print( key+" |||", float(value)*100)

sats={"Tx211":"3", "V1":"0.50"}
payloads(**sats)

Output:

Tx211 ||| 300.0
V1 ||| 50.0