This should do it for you:

# Find the name of the column by index
n = df.columns[1]

# Drop that column
df.drop(n, axis = 1, inplace = True)

# Put whatever series you want in its place
df[n] = newCol

...where [1] can be whatever the index is, axis = 1 should not change.

This answers your question very literally where you asked to drop a column and then add one back in. But the reality is that there is no need to drop the column if you just replace it with newCol.

You were pretty close to the solution.

What I did was:

data.replace({'Good': '1', 'Average': '2', 'Bad': '3'}, regex=True)

and obtain the result that you were looking:

enter image description here

Use regex=True for subtring replacement:

df['BrandName'] = df['BrandName'].replace(['ABC', 'AB'], 'A', regex=True)
print (df)
  BrandName Specialty
0         A         H
1         B         I
2       A B         J
3         D         K
4         A         L

Another solution is necessary, if need to avoid replacement values in anaother substrings, like ABCD is not replaced, then need regex words boundaries:

print (df)
  BrandName Specialty
0    A ABCD         H
1         B         I
2     ABC B         J
3         D         K
4        AB         L


L = [r"\b{}\b".format(x) for x in ['ABC', 'AB']]

df['BrandName1'] = df['BrandName'].replace(L, 'A', regex=True)
df['BrandName2'] = df['BrandName'].replace(['ABC', 'AB'], 'A', regex=True)
print (df)
  BrandName Specialty BrandName1 BrandName2
0    A ABCD         H     A ABCD       A AD
1         B         I          B          B
2     ABC B         J        A B        A B
3         D         K          D          D
4        AB         L          A          A

Edit(from the questioner):

To speed it up, you can have a look here: Speed up millions of regex replacements in Python 3

The best one is the trieapproach:

def trie_regex_from_words(words):
    trie = Trie()
    for word in words:
        trie.add(word)
    return re.compile(r"\b" + trie.pattern() + r"\b", re.IGNORECASE)

union = trie_regex_from_words(strings)
df['BrandName'] = df['BrandName'].replace(union, 'A', regex=True)

If you create a dictionary that maps from list1 to list2 then you can use Series.map:

df = pd.read_clipboard()

list1 = ['19990506', '19990607', '20000802']
list2 = ['1999201', '1999232', '2000252']

# When I read in your data I get ints in the Date column
#   so I need ints in the replacement map, if you have
#   strings then remove the int() conversions
replacement_map = {int(i1): int(i2) for i1, i2 in zip(list1, list2)}
df['Date'] = df['Date'].map(replacement_map)
df
Out[13]: 
      Date  Value
0  1999201    0.6
1  1999201    0.8
2  1999232    1.2
3  2000252    0.4

This will be easier if you read the raw csv, process it and then convert the result to pandas dataframe, but if you need to read the dataframe before, this could be an option:

data=[ {"query": "What is the type of \<mach-name> machine>", "description": "\<mach-name> is ..."},
      {"query": "What is the use of \<mach-name> machine>", "description": "The use of \<mach-name> is ..."},
      {"query": "How long it takes to rain in \<state-name>", "description": "It rains for ... hours in \<state-name>"}]
      
df = pd.DataFrame(data)

#mark rows that should that satisfy the conditions
df["replace_mach"] = df['query'].str.contains('\<mach-name>') &\ 
                     df['description'].str.contains('\<mach-name>')
df["replace_state"] = df['query'].str.contains('\<state-name>') &\ 
                      df['description'].str.contains('\<state-name>')


dfs_list = []
mach_name = ["Drilling", "ABC", "XYZ"]
state_name = ["New York", "London", "Delhi"]


for n in mach_name:
    aux = df[df["replace_mach"]].copy()
    aux["query"] = aux["query"].str.replace(r"\\<mach-name>",n)
    aux["description"] = aux["description"].str.replace(r"\\<mach-name>",n)
    dfs_list.append(aux)
    
for n in state_name:
    aux = df[df["replace_state"]].copy()
    aux["query"] = aux["query"].str.replace(r"\\<state-name>",n)
    aux["description"] = aux["description"].str.replace(r"\\<state-name>",n)
    dfs_list.append(aux)
    
# add records without wild cards to dataframe
dfs_list.append(df[~((df["replace_mach"])|(df["replace_state"]))]

replaced_df = pd.concat(dfs_list)
replaced_df

You can set the Sample column as index and then replace values on the whole data frame based on conditions:

df = df.set_index('Sample')
df[df < -1] = np.nan
df[(df < 0) & (df > -1)] = 0.05

Which gives:

#           element1    element2    element3
#  Sample           
#   alpha       1.00        1.0          NaN
#    beta       0.05        1.0         1.00
#   gamma        NaN        1.0         1.00
#   delta       1.00        NaN         0.05
# epsilon        NaN        2.0         2.00