Is there a python function or code that can rearrange(format) dataframe just like i showed?

Question

I need help formatting my datasets in python, it looks like this

l1	A1	B1	A2	B2	A3	B3	A4	B4
1	2	3	6	7	8	9	10	11
12	13	14	15	16	17	18	NAN	19
20	21	22	23	24	25	26	27	28

I need to format it to look like this.....

l1	A1	B1
1	2	3
12	6	7
20	8	9
30	10	11
40	12	13

please any python function that can rearrange this table will be highly appreciated. Thank you as I await your positive response

Answer 1

The logic is not fully clear on the first column and why part of the data gets missing, but it looks like you need to use pandas.wide_to_long:

(pd
 .wide_to_long(df, stubnames=['A', 'B'], i='l1', j='x')
 .droplevel('x')
 .reset_index()
)

output:

    l1    A   B
0    1    2   3
1   12   13  14
2   20   21  22
3    1    6   7
4   12   15  16
5   20   23  24
6    1    8   9
7   12   17  18
8   20   25  26
9    1   10  11
10  12  NAN  19
11  20   27  28

Answer 2

First, in order for the column 'A4' to be of the int type, you need to replace 'MAN' with a number and set the column type to int. In my understanding, the logic is as follows. For the values 'l1', the entire column is taken, where the values and we begin to add to the previous 'l1', the last value 'A1'.

"A" are even numbers, "B" odd even numbers taken from the zero line. Or read the first letter in the column name.To continue columns 'A1', 'B1' you need to add the first value 'A1'. Maybe someone will make it more beautiful on pandas.

df = df.replace('NAN', 777)
df['A4'] = df['A4'].astype(int)

a = [df[i][0] for i in df.columns if i[0] == 'A']
b = [df[i][0] for i in df.columns if i[0] == 'B']

l = df['l1'].to_list()
[l.append(l[i] + a[-1]) for i in range(2, 4)]#fill in the missing column data 'l1'

df = pd.DataFrame([l, a, b])
df = df.T
df = df.set_axis(['l1', 'A1', 'B1'], axis=1, inplace=False)
df.loc[4, ['A1', 'B1']] = (df['A1'][3] + df['A1'][0]), (df['B1'][3] + df['A1'][0])#fill in the missing columns data 'A1' and 'B1'

Output

   l1  A1  B1
0   1   2   3
1  12   6   7
2  20   8   9
3  30  10  11
4  40  12  13