Python How Do I Perform The Below Operation In Dataframe

df1 = pd.DataFrame({ 'Year': ['1A', '2A', '3A', '4A', '5A'], 'Tval1' : [1, 9, 8, 1, 6], 'Tval2' : [34, 56, 67, 78, 89] })

Solution 1:

Idea is get numbers from Year column, then set new columns names after Year column and reshape by DataFrame.stack:

df1['Year'] = df1['Year'].str.extract('(\d+)')
df = df1.set_index('Year')

#add letters by length of columns, working for 1 to 26 columns A-Z
import string

df.columns = list(string.ascii_uppercase[:len(df.columns)])

#here working same like
#df.columns = ['A','B']

df = df.stack().reset_index(name='Val')
df['Year'] = df['Year'] + df.pop('level_1')
print (df)
  Year  Val
0   1A    1
1   1B   34
2   2A    9
3   2B   56
4   3A    8
5   3B   67
6   4A    1
7   4B   78
8   5A    6
9   5B   89

Another idea with DataFrame.melt:

df = (df1.replace({'Year': {'A':''}}, regex=True)
        .rename(columns={'Tval1':'A','Tval2':'B'})
        .melt('Year'))
df['Year'] = df['Year'] + df.pop('variable')
print (df)
  Year  value
0   1A      1
1   2A      9
2   3A      8
3   4A      1
4   5A      6
5   1B     34
6   2B     56
7   3B     67
8   4B     78
9   5B     89

Solution 2:

Try the below code. I split it into two dataframes, and then concatenated after changing the Years' ends to be a 'B' instead of an 'A'.

import pandas as pd
df = pd.DataFrame(data=dict(Year=['1A', '2A', '3A'], val1=[1, 2, 3], val2=[4,5,6]))
df1 = df.drop(columns=['val2'])
df2 = df.drop(columns=['val1'])
columns = ['Year', 'val']
df1.columns = columns
df2.columns = columns
df2['Year'] = df2['Year'].str.replace('A', 'B')
pd.concat([df1, df2]).reset_index(drop=True)