Pandas Joining and merging DataFrame.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "printing a original data of first:\n",
      "  student_id              name  marks\n",
      "0         S1  Danniella Fenton    200\n",
      "1         S2      Ryder Storey    210\n",
      "2         S3      Bryce Jensen    190\n",
      "3         S4         Ed Bernal    222\n",
      "4         S5       Kwame Morin    199\n",
      "printing a original data of second:\n",
      "  student_id              name  marks\n",
      "0         S4  Scarlette Fisher    201\n",
      "1         S5  Carla Williamson    200\n",
      "2         S6       Dante Morse    198\n",
      "3         S7    Kaiser William    219\n",
      "4         S8   Madeeha Preston    201\n",
      "  student_id              name  marks\n",
      "0         S1  Danniella Fenton    200\n",
      "1         S2      Ryder Storey    210\n",
      "2         S3      Bryce Jensen    190\n",
      "3         S4         Ed Bernal    222\n",
      "4         S5       Kwame Morin    199\n",
      "0         S4  Scarlette Fisher    201\n",
      "1         S5  Carla Williamson    200\n",
      "2         S6       Dante Morse    198\n",
      "3         S7    Kaiser William    219\n",
      "4         S8   Madeeha Preston    201\n"
     ]
    }
   ],
   "source": [
    "#Write a Pandas program to join the two given dataframes along rows and assign all data\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "data1=pd.DataFrame({\n",
    "        'student_id': ['S1', 'S2', 'S3', 'S4', 'S5'],\n",
    "         'name': ['Danniella Fenton', 'Ryder Storey', 'Bryce Jensen', 'Ed Bernal', 'Kwame Morin'], \n",
    "        'marks': [200, 210, 190, 222, 199]})\n",
    "data2=pd.DataFrame({\n",
    "        'student_id': ['S4', 'S5', 'S6', 'S7', 'S8'],\n",
    "        'name': ['Scarlette Fisher', 'Carla Williamson', 'Dante Morse', 'Kaiser William', 'Madeeha Preston'], \n",
    "        'marks': [201, 200, 198, 219, 201]})\n",
    "print(\"printing a original data of first:\")\n",
    "print(data1)\n",
    "print(\"printing a original data of second:\")\n",
    "print(data2)\n",
    "final=pd.concat([data1,data2])\n",
    "print(final)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "original data:\n",
      "  student_id              name  marks\n",
      "0         S1  Danniella Fenton    200\n",
      "1         S2      Ryder Storey    210\n",
      "2         S3      Bryce Jensen    190\n",
      "3         S4         Ed Bernal    222\n",
      "4         S5       Kwame Morin    199\n",
      "-------------------\n",
      "  student_id              name  marks\n",
      "0         S4  Scarlette Fisher    201\n",
      "1         S5  Carla Williamson    200\n",
      "2         S6       Dante Morse    198\n",
      "3         S7    Kaiser William    219\n",
      "4         S8   Madeeha Preston    201\n",
      "joinign with column:\n",
      "  student_id              name  marks student_id              name  marks\n",
      "0         S1  Danniella Fenton    200         S4  Scarlette Fisher    201\n",
      "1         S2      Ryder Storey    210         S5  Carla Williamson    200\n",
      "2         S3      Bryce Jensen    190         S6       Dante Morse    198\n",
      "3         S4         Ed Bernal    222         S7    Kaiser William    219\n",
      "4         S5       Kwame Morin    199         S8   Madeeha Preston    201\n"
     ]
    }
   ],
   "source": [
    "#Write a Pandas program to join the two given dataframes along columns and assign all data.\n",
    "data1=pd.DataFrame({\n",
    "        'student_id': ['S1', 'S2', 'S3', 'S4', 'S5'],\n",
    "         'name': ['Danniella Fenton', 'Ryder Storey', 'Bryce Jensen', 'Ed Bernal', 'Kwame Morin'], \n",
    "        'marks': [200, 210, 190, 222, 199]})\n",
    "data2=pd.DataFrame({\n",
    "        'student_id': ['S4', 'S5', 'S6', 'S7', 'S8'],\n",
    "        'name': ['Scarlette Fisher', 'Carla Williamson', 'Dante Morse', 'Kaiser William', 'Madeeha Preston'], \n",
    "        'marks': [201, 200, 198, 219, 201]})\n",
    "print(\"original data:\")\n",
    "print(data1)\n",
    "print(\"-------------------\")\n",
    "print(data2)\n",
    "print(\"joinign with column:\")\n",
    "final=pd.concat([data1,data2],axis=1)\n",
    "print(final)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# ADDING A ROW IN A DATAFRAME"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "original data:\n",
      "  student_id              name  marks\n",
      "0         S1  Danniella Fenton    200\n",
      "1         S2      Ryder Storey    210\n",
      "2         S3      Bryce Jensen    190\n",
      "3         S4         Ed Bernal    222\n",
      "4         S5       Kwame Morin    199\n",
      "student_id        S6\n",
      "name          carlos\n",
      "marks            295\n",
      "dtype: object\n",
      "  student_id              name marks\n",
      "0         S1  Danniella Fenton   200\n",
      "1         S2      Ryder Storey   210\n",
      "2         S3      Bryce Jensen   190\n",
      "3         S4         Ed Bernal   222\n",
      "4         S5       Kwame Morin   199\n",
      "5         S6            carlos   295\n"
     ]
    }
   ],
   "source": [
    "#Write a Pandas program to append rows to an existing DataFrame and display the combined data. \n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "data1=pd.DataFrame({\n",
    "        'student_id': ['S1', 'S2', 'S3', 'S4', 'S5'],\n",
    "         'name': ['Danniella Fenton', 'Ryder Storey', 'Bryce Jensen', 'Ed Bernal', 'Kwame Morin'], \n",
    "        'marks': [200, 210, 190, 222, 199]})\n",
    "print(\"original data:\")\n",
    "print(data1)\n",
    "x=pd.Series(['S6','carlos','295'],index=['student_id','name','marks'])\n",
    "print(x)\n",
    "main=data1.append(x,ignore_index=True)\n",
    "print(main)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  student_id              name  marks\n",
      "0         S1  Danniella Fenton    200\n",
      "1         S2      Ryder Storey    210\n",
      "2         S3      Bryce Jensen    190\n",
      "3         S4         Ed Bernal    222\n",
      "4         S5       Kwame Morin    199\n",
      "student_id       S6\n",
      "name          Bibek\n",
      "marks           295\n",
      "dtype: object\n",
      "  student_id              name  marks\n",
      "0         S1  Danniella Fenton    200\n",
      "1         S2      Ryder Storey    210\n",
      "2         S3      Bryce Jensen    190\n",
      "3         S4         Ed Bernal    222\n",
      "4         S5       Kwame Morin    199\n",
      "5         s6             Bibek    260\n"
     ]
    }
   ],
   "source": [
    "#Write a Pandas program to append a list of dictioneries or series to a existing DataFrame and display the combined data.\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "data1=pd.DataFrame({\n",
    "        'student_id': ['S1', 'S2', 'S3', 'S4', 'S5'],\n",
    "         'name': ['Danniella Fenton', 'Ryder Storey', 'Bryce Jensen', 'Ed Bernal', 'Kwame Morin'], \n",
    "        'marks': [200, 210, 190, 222, 199]})\n",
    "print(data1)\n",
    "x=pd.Series(['S6','Bibek','295'],index=['student_id','name','marks'])\n",
    "print(x)\n",
    "y=pd.DataFrame({'student_id':'s6','name':'Bibek','marks':260},index=[5])\n",
    "#print(y)\n",
    "# yy=pd.DataFrame(y,index=[1])\n",
    "# print(yy)\n",
    "final=data1.append(y)\n",
    "print(final)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  student_id              name  marks\n",
      "0         S1  Danniella Fenton    200\n",
      "1         S2      Ryder Storey    210\n",
      "2         S3      Bryce Jensen    190\n",
      "3         S4         Ed Bernal    222\n",
      "4         S5       Kwame Morin    199\n",
      "[{'student_id': 's6', 'name': 'Bibek', 'marks': 260}]\n",
      "  student_id              name  marks\n",
      "0         S1  Danniella Fenton    200\n",
      "1         S2      Ryder Storey    210\n",
      "2         S3      Bryce Jensen    190\n",
      "3         S4         Ed Bernal    222\n",
      "4         S5       Kwame Morin    199\n",
      "5         s6             Bibek    260\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "data1=pd.DataFrame({\n",
    "        'student_id': ['S1', 'S2', 'S3', 'S4', 'S5'],\n",
    "         'name': ['Danniella Fenton', 'Ryder Storey', 'Bryce Jensen', 'Ed Bernal', 'Kwame Morin'], \n",
    "        'marks': [200, 210, 190, 222, 199]})\n",
    "print(data1)\n",
    "x=[{'student_id':'s6','name':'Bibek','marks':260}]\n",
    "print(x)\n",
    "final=data1.append(x,ignore_index=True)\n",
    "print(final)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  student_id   name  marks\n",
      "a         s6  Bibek    260\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "y={'student_id':'s6','name':'Bibek','marks':260}\n",
    "yy=pd.DataFrame(y,index=['a'])\n",
    "print(yy)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  student_id              name  marks\n",
      "0         S1  Danniella Fenton    200\n",
      "1         S2      Ryder Storey    210\n",
      "2         S3      Bryce Jensen    190\n",
      "3         S4         Ed Bernal    222\n",
      "4         S5       Kwame Morin    199\n",
      "-----------------------------------------\n",
      "  student_id              name  marks\n",
      "0         S4  Scarlette Fisher    201\n",
      "1         S5  Carla Williamson    200\n",
      "2         S6       Dante Morse    198\n",
      "3         S7    Kaiser William    219\n",
      "4         S8   Madeeha Preston    201\n",
      "-----------------------------------------\n",
      "   student_id  exam_id\n",
      "0          S1       23\n",
      "1          S2       45\n",
      "2          S3       12\n",
      "3          S4       67\n",
      "4          S5       21\n",
      "5          S7       55\n",
      "6          S8       33\n",
      "7          S9       14\n",
      "8         S10       56\n",
      "9         S11       83\n",
      "10        S12       88\n",
      "11        S13       12\n",
      "------------------------------------------\n",
      "merging data1 and data2:\n",
      "  student_id              name  marks\n",
      "0         S1  Danniella Fenton    200\n",
      "1         S2      Ryder Storey    210\n",
      "2         S3      Bryce Jensen    190\n",
      "3         S4         Ed Bernal    222\n",
      "4         S5       Kwame Morin    199\n",
      "5         S4  Scarlette Fisher    201\n",
      "6         S5  Carla Williamson    200\n",
      "7         S6       Dante Morse    198\n",
      "8         S7    Kaiser William    219\n",
      "9         S8   Madeeha Preston    201\n",
      "-------------------------------------------\n",
      "  student_id              name  marks  exam_id\n",
      "0         S1  Danniella Fenton    200       23\n",
      "1         S2      Ryder Storey    210       45\n",
      "2         S3      Bryce Jensen    190       12\n",
      "3         S4         Ed Bernal    222       67\n",
      "4         S4  Scarlette Fisher    201       67\n",
      "5         S5       Kwame Morin    199       21\n",
      "6         S5  Carla Williamson    200       21\n",
      "7         S7    Kaiser William    219       55\n",
      "8         S8   Madeeha Preston    201       33\n"
     ]
    }
   ],
   "source": [
    "#Write a Pandas program to join the two given dataframes along rows and merge with another dataframe along the common column id.\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "data1=pd.DataFrame({\n",
    "        'student_id': ['S1', 'S2', 'S3', 'S4', 'S5'],\n",
    "         'name': ['Danniella Fenton', 'Ryder Storey', 'Bryce Jensen', 'Ed Bernal', 'Kwame Morin'], \n",
    "        'marks': [200, 210, 190, 222, 199]})\n",
    "data2=pd.DataFrame({\n",
    "        'student_id': ['S4', 'S5', 'S6', 'S7', 'S8'],\n",
    "        'name': ['Scarlette Fisher', 'Carla Williamson', 'Dante Morse', 'Kaiser William', 'Madeeha Preston'], \n",
    "        'marks': [201, 200, 198, 219, 201]})\n",
    "exam_data = pd.DataFrame({\n",
    "        'student_id': ['S1', 'S2', 'S3', 'S4', 'S5', 'S7', 'S8', 'S9', 'S10', 'S11', 'S12', 'S13'],\n",
    "        'exam_id': [23, 45, 12, 67, 21, 55, 33, 14, 56, 83, 88, 12]})\n",
    "print(data1)\n",
    "print(\"-----------------------------------------\")\n",
    "print(data2)\n",
    "print(\"-----------------------------------------\")\n",
    "print(exam_data)\n",
    "print(\"------------------------------------------\")\n",
    "final=data1.append(data2,ignore_index=True)\n",
    "print(\"merging data1 and data2:\")\n",
    "print(final)\n",
    "print(\"-------------------------------------------\")\n",
    "final_merge=pd.merge(final,exam_data,on='student_id')\n",
    "print(final_merge)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  student_id              name  marks\n",
      "0         S1  Danniella Fenton    200\n",
      "1         S2      Ryder Storey    210\n",
      "2         S3      Bryce Jensen    190\n",
      "3         S4         Ed Bernal    222\n",
      "4         S5       Kwame Morin    199\n",
      "-----------------------------------\n",
      "  student_id              name  marks\n",
      "0         S4  Scarlette Fisher    201\n",
      "1         S5  Carla Williamson    200\n",
      "2         S6       Dante Morse    198\n",
      "3         S7    Kaiser William    219\n",
      "4         S8   Madeeha Preston    201\n",
      "after merging a data:\n",
      "  student_id            name_x  marks_x            name_y  marks_y\n",
      "0         S1  Danniella Fenton    200.0               NaN      NaN\n",
      "1         S2      Ryder Storey    210.0               NaN      NaN\n",
      "2         S3      Bryce Jensen    190.0               NaN      NaN\n",
      "3         S4         Ed Bernal    222.0  Scarlette Fisher    201.0\n",
      "4         S5       Kwame Morin    199.0  Carla Williamson    200.0\n",
      "5         S6               NaN      NaN       Dante Morse    198.0\n",
      "6         S7               NaN      NaN    Kaiser William    219.0\n",
      "7         S8               NaN      NaN   Madeeha Preston    201.0\n"
     ]
    }
   ],
   "source": [
    "#Write a Pandas program to join the two dataframes with matching records from both sides where available.\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "data1=pd.DataFrame({\n",
    "        'student_id': ['S1', 'S2', 'S3', 'S4', 'S5'],\n",
    "         'name': ['Danniella Fenton', 'Ryder Storey', 'Bryce Jensen', 'Ed Bernal', 'Kwame Morin'], \n",
    "        'marks': [200, 210, 190, 222, 199]})\n",
    "data2=pd.DataFrame({\n",
    "        'student_id': ['S4', 'S5', 'S6', 'S7', 'S8'],\n",
    "        'name': ['Scarlette Fisher', 'Carla Williamson', 'Dante Morse', 'Kaiser William', 'Madeeha Preston'], \n",
    "        'marks': [201, 200, 198, 219, 201]})\n",
    "print(data1)\n",
    "print(\"-----------------------------------\")\n",
    "print(data2)\n",
    "print(\"after merging a data:\")\n",
    "final=pd.merge(data1,data2,on='student_id',how='outer')\n",
    "print(final)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  key1 key2   P   Q\n",
      "0   K0   K0  P0  Q0\n",
      "1   K0   K1  P1  Q1\n",
      "2   K1   K0  P2  Q2\n",
      "3   K2   K1  P3  Q3\n",
      "-----------------------------\n",
      "  key1 key2   R   S\n",
      "0   K0   K0  R0  S0\n",
      "1   K1   K0  R1  S1\n",
      "2   K1   K0  R2  S2\n",
      "3   K2   K0  R3  S3\n",
      "print final merge data:\n",
      "  key1 key2   P   Q    R    S\n",
      "0   K0   K0  P0  Q0   R0   S0\n",
      "1   K0   K1  P1  Q1  NaN  NaN\n",
      "2   K1   K0  P2  Q2   R1   S1\n",
      "3   K1   K0  P2  Q2   R2   S2\n",
      "4   K2   K1  P3  Q3  NaN  NaN\n"
     ]
    }
   ],
   "source": [
    "#Write a Pandas program to join (left join) the two dataframes using keys from left dataframe only.\n",
    "import pandas as pd\n",
    "data1=pd.DataFrame({'key1': ['K0', 'K0', 'K1', 'K2'],\n",
    "                     'key2': ['K0', 'K1', 'K0', 'K1'],\n",
    "                     'P': ['P0', 'P1', 'P2', 'P3'],\n",
    "                     'Q': ['Q0', 'Q1', 'Q2', 'Q3']})\n",
    "data2=pd.DataFrame({'key1': ['K0', 'K1', 'K1', 'K2'],\n",
    "                      'key2': ['K0', 'K0', 'K0', 'K0'],\n",
    "                      'R': ['R0', 'R1', 'R2', 'R3'],\n",
    "                      'S': ['S0', 'S1', 'S2', 'S3']})\n",
    "print(data1)\n",
    "print(\"-----------------------------\")\n",
    "print(data2)\n",
    "print(\"print final merge data:\")\n",
    "final=pd.merge(data1,data2,how='left',on=['key1','key2'])\n",
    "print(final)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  key1 key2   P   Q\n",
      "0   K0   K0  P0  Q0\n",
      "1   K0   K1  P1  Q1\n",
      "2   K1   K0  P2  Q2\n",
      "3   K2   K1  P3  Q3\n",
      "-----------------------------\n",
      "  key1 key2   R   S\n",
      "0   K0   K0  R0  S0\n",
      "1   K1   K0  R1  S1\n",
      "2   K1   K0  R2  S2\n",
      "3   K2   K0  R3  S3\n",
      "print final merge data:\n",
      "  key1 key2    P    Q   R   S\n",
      "0   K0   K0   P0   Q0  R0  S0\n",
      "1   K1   K0   P2   Q2  R1  S1\n",
      "2   K1   K0   P2   Q2  R2  S2\n",
      "3   K2   K0  NaN  NaN  R3  S3\n"
     ]
    }
   ],
   "source": [
    "#Write a Pandas program to join two dataframes using keys from right dataframe only.\n",
    "#Write a Pandas program to join (left join) the two dataframes using keys from left dataframe only.\n",
    "import pandas as pd\n",
    "data1=pd.DataFrame({'key1': ['K0', 'K0', 'K1', 'K2'],\n",
    "                     'key2': ['K0', 'K1', 'K0', 'K1'],\n",
    "                     'P': ['P0', 'P1', 'P2', 'P3'],\n",
    "                     'Q': ['Q0', 'Q1', 'Q2', 'Q3']})\n",
    "data2=pd.DataFrame({'key1': ['K0', 'K1', 'K1', 'K2'],\n",
    "                      'key2': ['K0', 'K0', 'K0', 'K0'],\n",
    "                      'R': ['R0', 'R1', 'R2', 'R3'],\n",
    "                      'S': ['S0', 'S1', 'S2', 'S3']})\n",
    "print(data1)\n",
    "print(\"-----------------------------\")\n",
    "print(data2)\n",
    "print(\"print final merge data:\")\n",
    "final=pd.merge(data1,data2,how='right',on=['key1','key2'])\n",
    "print(final)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  key1 key2   P   Q\n",
      "0   K0   K0  P0  Q0\n",
      "1   K0   K1  P1  Q1\n",
      "2   K1   K0  P2  Q2\n",
      "3   K2   K1  P3  Q3\n",
      "-----------------------------\n",
      "  key1 key2   R   S\n",
      "0   K0   K0  R0  S0\n",
      "1   K1   K0  R1  S1\n",
      "2   K1   K0  R2  S2\n",
      "3   K2   K0  R3  S3\n",
      "print final merge data:\n",
      "  key1 key2   P   Q   R   S\n",
      "0   K0   K0  P0  Q0  R0  S0\n",
      "1   K1   K0  P2  Q2  R1  S1\n",
      "2   K1   K0  P2  Q2  R2  S2\n"
     ]
    }
   ],
   "source": [
    "#Write a Pandas program to merge two given datasets using multiple join keys\n",
    "import pandas as pd\n",
    "data1=pd.DataFrame({'key1': ['K0', 'K0', 'K1', 'K2'],\n",
    "                     'key2': ['K0', 'K1', 'K0', 'K1'],\n",
    "                     'P': ['P0', 'P1', 'P2', 'P3'],\n",
    "                     'Q': ['Q0', 'Q1', 'Q2', 'Q3']})\n",
    "data2=pd.DataFrame({'key1': ['K0', 'K1', 'K1', 'K2'],\n",
    "                      'key2': ['K0', 'K0', 'K0', 'K0'],\n",
    "                      'R': ['R0', 'R1', 'R2', 'R3'],\n",
    "                      'S': ['S0', 'S1', 'S2', 'S3']})\n",
    "print(data1)\n",
    "print(\"-----------------------------\")\n",
    "print(data2)\n",
    "print(\"print final merge data:\")\n",
    "final=pd.merge(data1,data2,on=['key1','key2'])\n",
    "print(final)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  key1 key2   P   Q\n",
      "0   K0   K0  P0  Q0\n",
      "1   K0   K1  P1  Q1\n",
      "2   K1   K0  P2  Q2\n",
      "3   K2   K1  P3  Q3\n",
      "-----------------------------\n",
      "  key1 key2   R   S\n",
      "0   K0   K0  R0  S0\n",
      "1   K1   K0  R1  S1\n",
      "2   K1   K0  R2  S2\n",
      "3   K2   K0  R3  S3\n",
      "print final merge data(many to many join):\n",
      "  key1 key2_x   P   Q key2_y   R   S\n",
      "0   K0     K0  P0  Q0     K0  R0  S0\n",
      "1   K0     K1  P1  Q1     K0  R0  S0\n",
      "2   K1     K0  P2  Q2     K0  R1  S1\n",
      "3   K1     K0  P2  Q2     K0  R2  S2\n",
      "4   K2     K1  P3  Q3     K0  R3  S3\n"
     ]
    }
   ],
   "source": [
    "#Write a Pandas program to create a combination from two dataframes where a column id combination \n",
    "#appears more than once in both dataframes\n",
    "import pandas as pd\n",
    "data1=pd.DataFrame({'key1': ['K0', 'K0', 'K1', 'K2'],\n",
    "                     'key2': ['K0', 'K1', 'K0', 'K1'],\n",
    "                     'P': ['P0', 'P1', 'P2', 'P3'],\n",
    "                     'Q': ['Q0', 'Q1', 'Q2', 'Q3']})\n",
    "data2=pd.DataFrame({'key1': ['K0', 'K1', 'K1', 'K2'],\n",
    "                      'key2': ['K0', 'K0', 'K0', 'K0'],\n",
    "                      'R': ['R0', 'R1', 'R2', 'R3'],\n",
    "                      'S': ['S0', 'S1', 'S2', 'S3']})\n",
    "print(data1)\n",
    "print(\"-----------------------------\")\n",
    "print(data2)\n",
    "print(\"print final merge data(many to many join):\")\n",
    "final=pd.merge(data1,data2,on='key1')\n",
    "print(final)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "     A   B\n",
      "K0  A0  B0\n",
      "K1  A1  B1\n",
      "K2  A2  B2\n",
      "     C   D\n",
      "K0  C0  D0\n",
      "K2  C2  D2\n",
      "K3  C3  D3\n",
      "final after merging data:\n",
      "     A   B    C    D\n",
      "K0  A0  B0   C0   D0\n",
      "K1  A1  B1  NaN  NaN\n",
      "K2  A2  B2   C2   D2\n"
     ]
    }
   ],
   "source": [
    "#Write a Pandas program to combine the columns of two potentially differently-indexed DataFrames into a single result DataFrame\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "data1=pd.DataFrame({'A': ['A0', 'A1', 'A2'],\n",
    "                      'B': ['B0', 'B1', 'B2']},\n",
    "                     index=['K0', 'K1', 'K2'])\n",
    "data2=pd.DataFrame({'C': ['C0', 'C2', 'C3'],\n",
    "                      'D': ['D0', 'D2', 'D3']},\n",
    "                     index=['K0', 'K2', 'K3'])\n",
    "print(data1)\n",
    "print(data2)\n",
    "print(\"final after merging data:\")\n",
    "final=data1.join(data2)\n",
    "print(final)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  key1 key2   P   Q\n",
      "0   K0   K0  P0  Q0\n",
      "1   K0   K1  P1  Q1\n",
      "2   K1   K0  P2  Q2\n",
      "3   K2   K1  P3  Q3\n",
      "  key1 key2   R   S\n",
      "0   K0   K0  R0  S0\n",
      "1   K1   K0  R1  S1\n",
      "2   K1   K0  R2  S2\n",
      "3   K2   K0  R3  S3\n",
      "  key1 key2    P    Q    R    S\n",
      "0   K0   K0   P0   Q0  NaN  NaN\n",
      "1   K0   K1   P1   Q1  NaN  NaN\n",
      "2   K1   K0   P2   Q2  NaN  NaN\n",
      "3   K2   K1   P3   Q3  NaN  NaN\n",
      "4   K0   K0  NaN  NaN   R0   S0\n",
      "5   K1   K0  NaN  NaN   R1   S1\n",
      "6   K1   K0  NaN  NaN   R2   S2\n",
      "7   K2   K0  NaN  NaN   R3   S3\n"
     ]
    }
   ],
   "source": [
    "#Write a Pandas program to merge two given dataframes with different columns. \n",
    "import pandas as pd\n",
    "data1 = pd.DataFrame({'key1': ['K0', 'K0', 'K1', 'K2'],\n",
    "                     'key2': ['K0', 'K1', 'K0', 'K1'],\n",
    "                     'P': ['P0', 'P1', 'P2', 'P3'],\n",
    "                     'Q': ['Q0', 'Q1', 'Q2', 'Q3']}) \n",
    "data2 = pd.DataFrame({'key1': ['K0', 'K1', 'K1', 'K2'],\n",
    "                      'key2': ['K0', 'K0', 'K0', 'K0'],\n",
    "                      'R': ['R0', 'R1', 'R2', 'R3'],\n",
    "                      'S': ['S0', 'S1', 'S2', 'S3']})\n",
    "print(data1)\n",
    "print(data2)\n",
    "final=pd.concat([data1,data2],ignore_index=True)\n",
    "print(final)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}