CODEWITHSUNDEEP
pythonstringpandasdataframeindexing
parcolau
parcolau

Reputation: 137

Python Pandas Dataframe Customize Index

I have a simple dataframe and 2 string variables as below:

index  col1
0         x
1         x
2         x

str1 = 'USD'
str2 = 'pricing'

I would like to rename the index to something like 'str1-str2-###':

index             col1
USD-pricing-001      x
USD-pricing-002      x
USD-pricing-003      x

Any help is very much appreciated!

Upvotes: 0

Views: 1125

Answers (5)

ansev
ansev

Reputation: 30930

Use: DataFrame.add_prefix,

You also need use DataFrame.transpose beforehand because the add_prefix method acts on the columns. It is also necessary to use transpose at the end to return the dataframe to the original form 

prefix=str1+'-'+str2+'-00'
df=df.set_index('index').T.add_prefix(prefix).T
print(df)

                col1
index               
USD-pricing-000    x
USD-pricing-001    x
USD-pricing-002    x

To respond to @razdi comment, to a more general solution, you would use:

prefix=str1+'-'+str2+'-'
df['index']=[(3-len(key))*'0'+key for key in df['index'].astype(str)]
df=df.set_index('index').T.add_prefix(prefix).T
print(df)

Example

df=pd.DataFrame()
df['col1']='x x x x x x x x x x x x x x'.split()
df.reset_index(inplace=True)
print(df)

    index col1
0       0    x
1       1    x
2       2    x
3       3    x
4       4    x
5       5    x
6       6    x
7       7    x
8       8    x
9       9    x
10     10    x
11     11    x
12     12    x
13     13    x

Applying the code shown:

prefix=str1+'-'+str2+'-'
df['index']=[(3-len(key))*'0'+key for key in df['index'].astype(str)]
df=df.set_index('index').T.add_prefix(prefix).T
print(df)

                col1
index               
USD-pricing-000    x
USD-pricing-001    x
USD-pricing-002    x
USD-pricing-003    x
USD-pricing-004    x
USD-pricing-005    x
USD-pricing-006    x
USD-pricing-007    x
USD-pricing-008    x
USD-pricing-009    x
USD-pricing-010    x
USD-pricing-011    x
USD-pricing-012    x
USD-pricing-013    x

Of course this as long as your dataframe has less than 1000 lines.

Upvotes: 3

Andy L.
Andy L.

Reputation: 25269

Another shorter way here

df = df.rename(lambda x: f'{str1}-{str2}-{x:003}')

Out[757]:
                col1
index
USD-pricing-000    x
USD-pricing-001    x
USD-pricing-002    x

Try use listcomp, zip, and f-string to create list of joined string. Finally, assign it to df.index

arr = [f'{t[0]}-{t[1]}-{t[2]:03}' for t in 
               zip([str1] * df.index.size, [str2] * df.index.size, df.index)]

In [744]: arr
Out[744]: ['USD-pricing-000', 'USD-pricing-001', 'USD-pricing-002']

df.index = arr

Out[747]:
                col1
USD-pricing-000    x
USD-pricing-001    x
USD-pricing-002    x

Upvotes: 2

parcolau
parcolau

Reputation: 137

Figured out a way:

df['temp'] = str1 + '-' str2 + '-' + (df.index + 1).astype('str').str.zfill(3)
df.set_index('temp', inplace = True)

;)

Upvotes: 0

razdi
razdi

Reputation: 1440

Here is an easy to understand, single line solution:

df = pd.DataFrame({'col':['a',0]})

df.index = df.index.to_series().apply(lambda x: str(1) + '-' + str(2) + '-' + str(x).zfill(3))

Output:

>>> df
                  col
USD-pricing-000   a
USD-pricing-001   0

Using zfill avoids the issue of having index with numbers like 00100. Because you mentioned the format 'str1-str2-###', I'm assuming the number needs to be 3 digit. Using some of the previous formats, 00 will be fixed and will lead to 4 digit numbers with larger index values.

Upvotes: 1

spYder
spYder

Reputation: 317

df = pd.DataFrame(\
    {'col1': ['x' ,'x' ,'x' ,'x' ,'x'],\
    },)

loops = len(df) + 1 
list_index = []

for x in range(1,loops):
    list_index.append("USD-pricing-00"+str(x))

df.index = list_index

Upvotes: 1

Related Questions