CODEWITHSUNDEEP
pythonpandas
Antonio López Ruiz
Antonio López Ruiz

Reputation: 1466

Using different dataframes to change column values - Python Pandas

I have a dataframe1 like the following:

A  B   C   D  
1 111  a   9
2 121  b   8
3 122  c   7
4 121  d   6
5 131  e   5

Also, I have another dataframe2:

   Code   String
    111     s
    12      b
    13      u

What I want is to creat a dataframe like the following:

A  B   C   D  
1 111  S   9
2 121  b   8
3 122  c   7
4 121  b   6
5 131  u   5

That would be, take the first n digits (where n is the number of digits in Code column of dataframe2) and if it has the same numbers that the code, then the column C in dataframe1 would change for the string in dataframe2.

Upvotes: 1

Views: 63

Answers (1)

BENY
BENY

Reputation: 323396

Is this what you want ? The code is not very neat but work..

import pandas as pd 
DICT=df2.set_index('Code').T.to_dict('list')

Temp=[]

for key, value in DICT.items():
    n=len(str(key))
    D1={str(key):value[0]}
    T=df1.B.astype(str).apply(lambda x: x[:n]).map(D1)

    Temp2=(df1.B.astype(str).apply(lambda x: x[:n]))
    Tempdf=pd.DataFrame({'Ori':df1.B,'Now':Temp2,'C':df1.C})
    TorF=(Tempdf.groupby(['Now'])['Ori'].transform(min) == Tempdf['Ori'])

    for n, i in enumerate(T):
        if TorF[n]==False:
            T[n]=Tempdf.ix[n,0]
    Temp.append(T)
df1.C=pd.DataFrame(data=Temp).fillna(method='bfill').T.ix[:,0]

Out[255]: 
   A    B  C  D
0  1  111  s  9
1  2  121  b  8
2  3  122  c  7
3  4  121  b  6
4  5  131  u  5

Upvotes: 1

Related Questions