Skip to main content

Concatenation, Merging, and Joining in Pandas - Python

Here is how to concatenate, merge, or join in Pandas
Run the python code here: https://repl.it/@VinitKhandelwal/pandas-concat-merge-join
import numpy as np
import pandas as pd

print("CONCAT")
df1 = pd.DataFrame(np.random.randn(4,4), [0,1,2,3], ['A','B','C','D'])
print(df1)
df2 = pd.DataFrame(np.random.randn(4,4), [4,5,6,7], ['A','B','C','D'])
print(df2)
df3 = pd.DataFrame(np.random.randn(4,4), [8,9,10,11], ['A','B','C','D'])
print(df3)
print(pd.concat([df1,df2,df3]))
df1 = pd.DataFrame(np.random.randn(4,4), [0,1,2,3], ['A','B','C','D'])
print(df1)
df2 = pd.DataFrame(np.random.randn(4,4), [0,1,2,3], ['E','F','G','H'])
print(df2)
df3 = pd.DataFrame(np.random.randn(4,4), [0,1,2,3], ['I','J','K','L'])
print(pd.concat([df1,df2,df3], axis=1))
print("MERGE IS ON COLUMN AND NOT ON INDEX")
df1 = pd.DataFrame(np.random.randn(4,2), [0,1,2,3], ['A','B'])
print(df1)
df2 = pd.DataFrame(np.random.randn(4,2), [0,1,2,3], ['C','D'])
print(df2)
df1['KEY'] = ['K0','K1','K2','K3']
print(df1)
df2['KEY'] = ['K0','K1','K2','K3']
print(df2)
print(pd.merge(df1,df2,how='inner',on='KEY'))
print("TWO KEY INNER MERGE")
df1['KEY'] = ['K0','K0','K1','K2']
df1['KEY1'] = ['K0','K1','K0','K1']
print(df1)
df2['KEY'] = ['K0','K1','K1','K2']
df2['KEY1'] = ['K0','K0','K0','K0']
print(df2)
print(pd.merge(df1,df2,how='inner',on=['KEY', 'KEY1']))
print("TWO KEY OUTER MERGE")
print(pd.merge(df1,df2,how='outer',on=['KEY', 'KEY1']))
print("TWO KEY RIGHT MERGE")
print(pd.merge(df1,df2,how='right',on=['KEY', 'KEY1']))
print("TWO KEY LEFT MERGE")
print(pd.merge(df1,df2,how='left',on=['KEY', 'KEY1']))
print("JOIN IS ON INDEX AND NOR ON COLUMN")
df1 = pd.DataFrame(np.random.randn(3,2), ['K0','K1','K2'], ['A','B'])
print(df1)
df2 = pd.DataFrame(np.random.randn(3,2), ['K0','K2','K3'], ['C','D'])
print(df2)
print(df1.join(df2))
print(df2.join(df1))

OUTPUT

CONCAT
          A         B         C         D
0  0.916421  0.035595  0.293305 -0.402285
1 -0.835531 -1.602794  0.850362 -0.020083
2  1.170033  0.764249 -1.781987  0.577272
3 -0.649998 -0.059491  0.506015  1.386711
          A         B         C         D
4  0.027545  0.418065  0.023933  0.584219
5  1.241963 -1.198550 -1.076841 -1.907718
6  0.863230  0.820684  0.011180 -1.386358
7  0.356003 -0.248571 -0.197533  0.793309
           A         B         C         D
8  -0.660149  1.953251 -0.073291  0.836487
9  -0.203366  0.698548 -0.586643 -0.752695
10  0.516609 -0.326910  0.611201 -0.459187
11 -0.192555  1.355531 -0.268725  1.181282
           A         B         C         D
0   0.916421  0.035595  0.293305 -0.402285
1  -0.835531 -1.602794  0.850362 -0.020083
2   1.170033  0.764249 -1.781987  0.577272
3  -0.649998 -0.059491  0.506015  1.386711
4   0.027545  0.418065  0.023933  0.584219
5   1.241963 -1.198550 -1.076841 -1.907718
6   0.863230  0.820684  0.011180 -1.386358
7   0.356003 -0.248571 -0.197533  0.793309
8  -0.660149  1.953251 -0.073291  0.836487
9  -0.203366  0.698548 -0.586643 -0.752695
10  0.516609 -0.326910  0.611201 -0.459187
11 -0.192555  1.355531 -0.268725  1.181282
          A         B         C         D
0  0.983828  1.203951  0.125771  1.475740
1 -0.729233  1.444557 -1.518726 -1.060839
2 -1.537408  0.876840 -0.422955  1.548782
3  0.691144 -0.335625 -1.222966 -0.066613
          E         F         G         H
0 -0.018694  0.239440  0.418294 -0.315046
1 -0.081236  0.506776 -1.272656 -0.425861
2  0.375903  0.005405 -0.134107 -0.090822
3  0.454768  1.042870  0.682735 -0.241464
          A         B         C    ...            J         K         L
0  0.983828  1.203951  0.125771    ...    -1.402984 -0.247747  0.079447
1 -0.729233  1.444557 -1.518726    ...     1.171449 -1.739336  1.420380
2 -1.537408  0.876840 -0.422955    ...    -1.773472  1.165093 -1.308042
3  0.691144 -0.335625 -1.222966    ...     2.433909 -0.755073 -1.218695

 [4 rows x 12 columns]
MERGE
          A         B
0  1.842076  1.069991
1 -1.195149 -0.248629
2 -0.283477 -0.770632
3 -0.289297  0.396434
          C         D
0  1.738321 -0.397815
1  1.990671  0.324250
2 -0.226425  0.451265
3 -1.031861 -0.509392
          A         B KEY
0  1.842076  1.069991  K0
1 -1.195149 -0.248629  K1
2 -0.283477 -0.770632  K2
3 -0.289297  0.396434  K3
          C         D KEY
0  1.738321 -0.397815  K0
1  1.990671  0.324250  K1
2 -0.226425  0.451265  K2
3 -1.031861 -0.509392  K3
          A         B KEY         C         D
0  1.842076  1.069991  K0  1.738321 -0.397815
1 -1.195149 -0.248629  K1  1.990671  0.324250
2 -0.283477 -0.770632  K2 -0.226425  0.451265
3 -0.289297  0.396434  K3 -1.031861 -0.509392
TWO KEY INNER MERGE
          A         B KEY KEY1
0  1.842076  1.069991  K0   K0
1 -1.195149 -0.248629  K0   K1
2 -0.283477 -0.770632  K1   K0
3 -0.289297  0.396434  K2   K1
          C         D KEY KEY1
0  1.738321 -0.397815  K0   K0
1  1.990671  0.324250  K1   K0
2 -0.226425  0.451265  K1   K0
3 -1.031861 -0.509392  K2   K0
          A         B KEY KEY1         C         D
0  1.842076  1.069991  K0   K0  1.738321 -0.397815
1 -0.283477 -0.770632  K1   K0  1.990671  0.324250
2 -0.283477 -0.770632  K1   K0 -0.226425  0.451265
TWO KEY OUTER MERGE
          A         B KEY KEY1         C         D
0  1.842076  1.069991  K0   K0  1.738321 -0.397815
1 -1.195149 -0.248629  K0   K1       NaN       NaN
2 -0.283477 -0.770632  K1   K0  1.990671  0.324250
3 -0.283477 -0.770632  K1   K0 -0.226425  0.451265
4 -0.289297  0.396434  K2   K1       NaN       NaN
5       NaN       NaN  K2   K0 -1.031861 -0.509392
TWO KEY RIGHT MERGE
          A         B KEY KEY1         C         D
0  1.842076  1.069991  K0   K0  1.738321 -0.397815
1 -0.283477 -0.770632  K1   K0  1.990671  0.324250
2 -0.283477 -0.770632  K1   K0 -0.226425  0.451265
3       NaN       NaN  K2   K0 -1.031861 -0.509392
TWO KEY LEFT MERGE
          A         B KEY KEY1         C         D
0  1.842076  1.069991  K0   K0  1.738321 -0.397815
1 -1.195149 -0.248629  K0   K1       NaN       NaN
2 -0.283477 -0.770632  K1   K0  1.990671  0.324250
3 -0.283477 -0.770632  K1   K0 -0.226425  0.451265
4 -0.289297  0.396434  K2   K1       NaN       NaN
JOINING
           A         B
K0  0.204361 -3.018034
K1  0.145593 -0.052479
K2 -1.994043 -1.221300
           C         D
K0  0.186839 -0.937334
K2  1.017004 -0.094436
K3  1.506828 -0.676077
           A         B         C         D
K0  0.204361 -3.018034  0.186839 -0.937334
K1  0.145593 -0.052479       NaN       NaN
K2 -1.994043 -1.221300  1.017004 -0.094436
           C         D         A         B
K0  0.186839 -0.937334  0.204361 -3.018034
K2  1.017004 -0.094436 -1.994043 -1.221300
K3  1.506828 -0.676077       NaN       NaN

Comments

Post a Comment

Popular posts from this blog

Python - List - Append, Count, Extend, Index, Insert, Pop, Remove, Reverse, Sort

🐍 Advance List List is widely used and it's functionalities are heavily useful. Append Adds one element at the end of the list. Syntax list1.append(value) Input l1 = [1, 2, 3] l1.append(4) l1 Output [1, 2, 3, 4] append can be used to add any datatype in a list. It can even add list inside list. Caution: Append does not return anything. It just appends the list. Count .count(value) counts the number of occurrences of an element in the list. Syntax list1.count(value) Input l1 = [1, 2, 3, 4, 3] l1.count(3) Output 2 It returns 0 if the value is not found in the list. Extend .count(value) counts the number of occurrences of an element in the list. Syntax list1.extend(list) Input l1 = [1, 2, 3] l1.extend([4, 5]) Output [1, 2, 3, 4, 5] If we use append, entire list will be added to the first list like one element. Extend, i nstead of considering a list as one element, it joins the two lists one after other. Append works in the following way. Input l1 = [1, 2, 3] l1.append([4, 5]) Output...
Post a Comment
Read more

Difference between .exec() and .execPopulate() in Mongoose?

Here I answer what is the difference between .exec() and .execPopulate() in Mongoose? .exec() is used with a query while .execPopulate() is used with a document Syntax for .exec() is as follows: Model.query() . populate ( 'field' ) . exec () // returns promise . then ( function ( document ) { console . log ( document ); }); Syntax for .execPopulate() is as follows: fetchedDocument . populate ( 'field' ) . execPopulate () // returns promise . then ( function ( document ) { console . log ( document ); }); When working with individual document use .execPopulate(), for model query use .exec(). Both returns a promise. One can do without .exec() or .execPopulate() but then has to pass a callback in populate.
1 comment
Read more

683 K Empty Slots

  Approach #1: Insert Into Sorted Structure [Accepted] Intuition Let's add flowers in the order they bloom. When each flower blooms, we check it's neighbors to see if they can satisfy the condition with the current flower. Algorithm We'll maintain  active , a sorted data structure containing every flower that has currently bloomed. When we add a flower to  active , we should check it's lower and higher neighbors. If some neighbor satisfies the condition, we know the condition occurred first on this day. Complexity Analysis Time Complexity (Java):  O(N \log N) O ( N lo g N ) , where  N N  is the length of  flowers . Every insertion and search is  O(\log N) O ( lo g N ) . Time Complexity (Python):  O(N^2) O ( N 2 ) . As above, except  list.insert  is  O(N) O ( N ) . Space Complexity:  O(N) O ( N ) , the size of  active . Approach #2: Min Queue [Accepted] Intuition For each contiguous block ("window") of  k  po...
Post a Comment
Read more