Let's try str.replace with the following pattern to remove spaces between Prod and digits.

df['Product'] = df.Product.str.replace('(Prod)(\s+)(\d)', r'\1\3')

Output:

            Product
0             Prod1
1             Prod1
2             Prod2
3             Prod2
4             Prod2
5             Prod3
6  Prod3  and so on

Without doing a complicated lookarounds, you can do the job in two steps. First remove leading and trailing space; then use regex to replace space inside the strings.

foo['testing'] = foo['testing'].str.strip().str.replace(r'\s+', ' ', regex=True)
print(foo)

          testing
0  this is test A
1  this is test B
2  this is test C
3  this is test D
4  this is test E

Use str.split() on string and then df.rename on index. See each step below.

import pandas as pd 

# making your df 
d = {'index':['Wells     Fargo'], 'col1':[123], 'col2':[123]}
df = pd.DataFrame(d)
df = df.set_index('index')

# get list of index strings
index_str_list = [strings for strings in df.index]

# format spaces and append to new list 
new_list = []
for i in index_str_list:
    s1,s2 = i.split()
    s = "{:6}{:}".format(s1,s2) # set your distance 
    new_list.append(s)

# change index value
for old,new in zip(index_str_list, new_list):
    df.rename(index={old:new}, inplace=True)

print(df)

Output:
             col1  col2
index                  
Wells Fargo   123   123

An idea would be to do a combination of:

• regex to remove duplicate spaces (e.g " James Bond" to " James Bond")
• str.strip to remove leading/trailing spaces (e.g " James Bond" to "James Bond").

You could do this one of two ways:

1. On the whole DataFrame:

df = df.replace("\s+", " ", regex=True).apply(lambda x: x.str.strip())

2. On each column individually:

for col in ["Name", "Country"]:
    df[col] = df[col].replace("\s+", " ", regex=True).str.strip()