How to unify dates with Python dataframe

Question

Good afternoon guys, I have the following situation. I have a spreadsheet with the following columns: name, surname, dates(from 2011 until 2021)

Follow the table to view:

As you can see, these dates are out of order. How can I sort them without changing the columns name and surname?

I came to execute the code in the following way:

df_json_meses = df_json1.iloc[:, 58:64] # Coluna referente aos meses de ago até dez/2020

df_json_meses2 = df_json1.iloc[:, 52:55] # Coluna que se refere aos meses jan a maio/2021

df_json_meses3 = df_json1.iloc[:, 99] # coluna referente ao mês de jun/2021

df_json_meses4 = df_json1.iloc[:, 55:57] # coluna referente aos meses Jul e Ago/2021

df_json_soma_meses = pd.concat([df_json_meses, df_json_meses2 , df_json_meses3, 

df_json_meses4], Axis=1)

df_json_nome = df_json1.iloc[:, :2]


df_json_unico = pd.concat([df_json_nome, df_json_soma_meses], axis=1)

I managed to organize as I wanted, only that I answer me for this report, now if I generate a new report, and generate new columns or fewer columns and the amount is smaller or greater than I have set in the code, I have to change in hand? If so, I don’t want it. How can I automate this code in the best way possible?

Follow the final result of the report:

I really appreciate anyone who can help.

Answer 1

Starting from the following dataset as an example:

df = pd.DataFrame({
    'Nome': ['João', 'José'],
    'Sobrenome': ['Da Silva', 'Soares'],
    '2011-02': [10, 20],
    '2009-08': [90, 200],
    '2011-12': [1, 5],
    })
print(df)

# output:
#    Nome Sobrenome  2011-02  2009-08  2011-12
# 0  João  Da Silva       10       90        1
# 1  José    Soares       20      200        5

First, reorder all columns with df.sort_index (how dates are in format YYYY-MM, the default sorting algorithm is able to sort them in ascending order):

df = df.sort_index(axis=1)
print(df)

# output:
#    2009-08  2011-02  2011-12  Nome Sobrenome
# 0       90       10        1  João  Da Silva
# 1      200       20        5  José    Soares

The columns of dates were ordered - all that remains is to bring the columns Nome and Sobrenome forward. We can do this using df.pop and df.insert to remove the column and reinsert it at the beginning of the dataframe:

df.insert(0, "Sobrenome", df.pop('Sobrenome'))
df.insert(0, "Nome", df.pop('Nome'))
print(df)

# output:
#    Nome Sobrenome  2009-08  2011-02  2011-12
# 0  João  Da Silva       90       10        1
# 1  José    Soares      200       20        5

Finally, if you want the date columns to be in descending order, just pass the argument ascending=False when calling the method df.sort_index.