Good afternoon!

I have a dataset from an excel file which looks as follows:

     0         1         2      3    ...
1   NaN       NaN       V001   V002
2   NaN       NaN       Total  Total
3   NaN       NaN       EUR    EUR
4   NaN       NaN       Text   Text
5   NaN       NaN       Text   Text
6   NaN       NaN       Text   Text
7   NaN       NaN       Text   Text
8   NaN       NaN       Text   Text
9   NaN       NaN       Text   Text
10  NaN       NaN       Text   Text
11  U1        Totalprod Text   Text
12  U2        7000      5000   3000
...

I need to delete NaN only from column A, then transpose it to a row and paste it to the row number 8 and then table will move down by one row. It should look like this result:

     0         1     2     3   ...
1   NaN       V001  V002  V003
2   NaN       Total Total Total
3   NaN       EUR   EUR   EUR
4   NaN       NaN   Text  Text
5   NaN       NaN   Text  Text
6   NaN       NaN   Text  Text
7   NaN       NaN   Text  Text
8   NaN       U1    U2    U3
9   NaN       NaN   Text  Text
10  NaN       NaN   Text  Text
11  NaN       NaN   Text  Text
12  Totalprod 7000  5000  3000
13  3232      3223  999   3000
...

Can anybody help me to achieve this using python and pandas libraries?

enter image description here

CodePudding user response：

• systematically code what you describe and show
• get values you want as row 8
• remove and rename columns left
• concatenate three parts together

import io

df = pd.read_csv(
    io.StringIO("""     A         B         C      D
1   NaN       NaN       V001   V002
2   NaN       NaN       Total  Total
3   NaN       NaN       EUR    EUR
4   NaN       NaN       Text   Text
5   NaN       NaN       Text   Text
6   NaN       NaN       Text   Text
7   NaN       NaN       Text   Text
8   NaN       NaN       Text   Text
9   NaN       NaN       Text   Text
10  NaN       NaN       Text   Text
11  U1        Totalprod Text   Text
12  U2        7000      5000   3000"""),sep="\s ",)

# get values that will become row
s_a = df["A"].dropna()
# drop unwanted column A and rename columns A to Z
df = df.drop(columns="A").pipe(
    lambda d: d.rename(columns={c: df.columns[i] for i, c in enumerate(d.columns)})
)

# concat all parts together
df = pd.concat(
    [
        df.loc[0:8],
        pd.DataFrame(
            np.array([s_a.values]),
            columns=[
                f"{'' if (c//26)==0 else chr((c//26) 64)}{chr((c%26) 65)}"
                for c in range(len(s_a))
            ],
        ),
        df.loc[9:],
    ]
).reset_index(drop=True)

df