Replacing strings from pandas with numbers before pair string in list

Question

Given the following pandas df:

import pandas as pd

df = pd.DataFrame({'1' : ['title1','R','R','R'],
               '2' : ["title2", "NR" ,"NR", "NR"],
               '3' : ["title3", "R" , "NR", "NR"],
               '4' : ["title4", "R", "NR", "R"]})

And a longer list of strings:

List = ['2633', 'title1', '3327', 'title2', '18', 'title3', '5', 'title4', '5835', 'title5', '394', 'title6']

Is there any possibility in python environment to replace the titles in the df with the number before each pair-title in the list of strings.

Expected output:

dfnew = pd.DataFrame({'1' : ['2633','R','R','R'],
               '2' : ["3327", "NR" ,"NR", "NR"],
               '3' : ["28", "R" , "NR", "NR"],
               '4' : ["5", "R", "NR", "R"]})

dfnew
    1    2      3   4
0   2633 3327   28  5
1   R    NR     R   R
2   R    NR     NR  NR
3   R    NR     NR  R

I assume that a regex would do the trick but I do not know how to access the correct numbers from the list.

Thanks for every help in advance!

Answer 1

Create a dict from even and odd indices as key-value pair and use replace to replace title by numbers:

d = {k:v for k,v in zip(List[1::2], List[::2])}

print(df.replace(d))

Output:

     1     2   3   4                                                                                                                 
0  2633  3327  18   5                                                                                                                 
1     R    NR   R   R                                                                                                                 
2     R    NR  NR  NR                                                                                                                 
3     R    NR  NR   R  

Explanation

List[1::2] will give you elements at odd indices from the list ['title1', 'title2', 'title3', 'title4', 'title5', 'title6']

And

List[::2] will give you elements at even indices from the list ['2633', '3327', '18', '5', '5835', '394']

Answer 2

I would do something like this:

import pandas as pd

df = pd.DataFrame({'1' : ['title1','R','R','R'],
               '2' : ["title2", "NR" ,"NR", "NR"],
               '3' : ["title3", "R" , "NR", "NR"],
               '4' : ["title4", "R", "NR", "R"]})
List = ['2633', 'title1', '3327', 'title2', '18', 'title3', '5', 'title4', '5835', 'title5', '394', 'title6']

# mapping every title with its number
mydict = {}
for i in range(len(List)) :
    if i %2 == 0 :
        mydict[List[i+1]] = List[i]

print mydict
#>>>{'title1': '2633', 'title2': '3327', 'title3': '18', 'title4': '5', 'title5': '5835', 'title6': '394'}

for k in df :
    title = df[k][0]
    df[k][0] = mydict[title]

print df
#>>>      1     2   3   4
#>>>0  2633  3327  18   5
#>>>1     R    NR   R   R
#>>>2     R    NR  NR  NR
#>>>3     R    NR  NR   R

Answer 3

List = [item.replace("title", "") for item in List]

output:

['2633', '1', '3327', '2', '18', '3', '5', '4', '5835', '5', '394', '6']

---

If you want to apply on DF you just need to switch List by your DF Column