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

it looks more like this

enter image description here

and I want to change it to make it look like this, the 2nd column is moved under the individual row.

enter image description here

Wasif
  • 14,755
  • 3
  • 14
  • 34
user13412850
  • 509
  • 1
  • 5
  • 16

2 Answers2

1

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)
yonatansc97
  • 584
  • 6
  • 16
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
jezrael
  • 822,522
  • 95
  • 1,334
  • 1,252
  • 1
    excellent thanks that worked! (and thanks for re-opening the Q as well!) – user13412850 Dec 04 '20 at 08:44
  • can I ask what if I had to go other way around, starting with the output of this question and getting the orignal df back. (I can put that in a new Q if you want) – user13412850 Dec 04 '20 at 08:54
  • @user13412850 - It is up to you ;) Do you think using first or second solution? – jezrael Dec 04 '20 at 08:55
  • first please, and here is the new Q https://stackoverflow.com/questions/65140570/python-how-do-i-perform-a-specific-df-operation – user13412850 Dec 04 '20 at 08:58