How to pivot a specific dataframe?-CodePudding

I have a dataframe df with mixed data (float and text) which, when printed, looks like this (it's a very small part of the printing):

              0           1
0   Le Maurepas         NaN
1       CODE_90     AREA_HA
2           112      194.97
3           121       70.37
4           211      113.86
5    La Rolande         NaN
6       CODE_90     AREA_HA
7           112      176.52
8           211       97.28

If necessary, this output can be reproduced by the following code (for example):

import pandas as pd

fst_col = ['Le Maurepas', 'CODE_90', 112, 121, 211, 'La Rolande', 'CODE_90', 112, 211]
snd_col = ['NaN', 'AREA_HA', 194.97, 70.37, 113.86, 'NaN', 'AREA_HA', 176.52, 97.28]

df = pd.DataFrame({'0' : fst_col, '1' : snd_col})

df

I would like to give another structure to my dataframe df and get it to look like this when printed:

           Name     Code      Area
0   Le Maurepas      112    194.97
1   Le Maurepas      121     70.37
2   Le Maurepas      211    113.86
3    La Rolande      112    176.52
4    La Rolande      211     97.28

I browsed SO and I am aware that a function like pivot(index='', columns='', values='') could maybe do the job, but I don't know if it is applicable in my case, and, in fact, I don't know how to apply it...

Do I still have to insist with this function, by manipulating the parameters index, columns, values, or is there a particular way, corresponding more precisely to the structure of my initial dataframe df?

Any help welcome.

CodePudding user response：

IIUC, try:

#change the string "NaN" empty values
df["1"] = df["1"].replace("NaN", None)

output = pd.DataFrame()
output["Name"] = df.loc[df["1"].isnull(), "0"].reindex(df.index, method="ffill")
output["Code"] = pd.to_numeric(df["0"], errors="coerce")
output["Area"] = pd.to_numeric(df["1"], errors="coerce")
output = output.dropna().reset_index(drop=True)

>>> output

          Name   Code    Area
0  Le Maurepas  112.0  194.97
1  Le Maurepas  121.0   70.37
2  Le Maurepas  211.0  113.86
3   La Rolande  112.0  176.52
4   La Rolande  211.0   96.28

CodePudding user response：

You can use:

indexes = (df[df['0'].eq('CODE_90')].index - 1).to_list()
indexes.append(len(df))
all_dfs = []
for idx in range(0, len(indexes)-1):
    df_temp = df.loc[indexes[idx]:indexes[idx 1]-1]
    print(df_temp)
    df_temp['Name'] = df_temp['0'].iloc[0]
    df_temp.rename(columns={'0': 'Code', '1': 'Area'}, inplace=True)
    all_dfs.append(df_temp.iloc[2:])

df = pd.concat(all_dfs, ignore_index=True)
print(df)