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)
Post a Comment for "Python How Do I Perform The Below Operation In Dataframe"