CODEWITHSUNDEEP
pandas
Eurydice
Eurydice

Reputation: 8429

why pandas badly format a row containing date?

I have an excel file with the following kind of structure

            x        y        z
toto 13/12/20 13/12/20 13/12/20
titi        1        2        3
tata        4        5        6

where the row toto contains date. When reading such data with pandas.read_excel, I end up with the following data frame:

                       x                    y                    z
toto 2020-12-13 00:00:00  2020-12-13 00:00:00  2020-12-13 00:00:00
titi                   1                    2                    3
tata                   4                    5                    6

which when saved back to excel produces cells which are not interpreted as valid dates anymore. I know that there is a way to read dates with parse_dates and date_parser keywords but these apply to columns and not row. Would you know how to make pandas interpret these row correctly ?

Upvotes: 0

Views: 28

Answers (1)

Anshul
Anshul

Reputation: 1413

While there could be a way to handle reading the dates directly in the format that you need. There is fairly simple way to use double transpose (T, two times) and format the dates your required format:

import pandas as pd

data = {
"x":["2020-12-13 00:00:00",1,4],
"y":["2020-12-13 00:00:00",2,5],
"z":["2020-12-13 00:00:00",3,6]
}

idx = ['toto', 'titi', 'tata']

df = pd.DataFrame(data, index = idx)

print(df)

.

                        x                    y                    z
toto  2020-12-13 00:00:00  2020-12-13 00:00:00  2020-12-13 00:00:00
titi                    1                    2                    3
tata                    4                    5                    6

.

df1 = df.T

df1['toto'] = pd.to_datetime(df1['toto'], infer_datetime_format=True)
df1['toto'] = df1['toto'].dt.strftime('%d/%m/%y')

df_final = df1.T

print(df_final)

Final Output:

             x         y         z
toto  13/12/20  13/12/20  13/12/20
titi         1         2         3
tata         4         5         6

Upvotes: 1

Related Questions