Home / Questions / 549425
Accepted
LopezAi
Asked: 2022-08-02 03:38:55 +0800 CST 2022-08-02 03:38:55 +0800 CST

Transpose a column with a date reference - Python

  • 772

I have a dataframe where I need to divide the sensor data into 3 different columns (one for each type of sensor data), and they must match by date.

The following image is how I currently have the df:

![enter image description here

The following image is a sample of the output of the df I need:

enter image description here

I attach a link to the original dataframe:

https://drive.google.com/file/d/1-5X1lK8yYkYrS9-2myEa_-njtsdt57cN/view?usp=sharing

I've tried to transpose, but I'm not sure how to do it.

df = df.pivot(index='fechaValor', columns='sensorica', values='Valor')

Thanks in advance.

All the best.

python

1 Answers

  1. Best Answer

    Good day,

    You almost made it, there are several ways to do it:

    Note: The URL you put in doesn't seem to work, using the following dataframegeneric in a "sample2.csv" file

        id      sensorica       fechaValor          Valor
0   ADN-515 Temperatura     19/05/2022 22:02    233.081
1   ADN-515 Humedad         19/05/2022 22:02    658.732
2   ADN-515 Acelerómetro    19/05/2022 22:02    329.476
3   ADN-515 Temperatura     19/05/2022 22:12    229.924
4   ADN-515 Humedad         19/05/2022 22:12    680.006
5   ADN-515 Acelerómetro    19/05/2022 22:12    10.085

    Using pandas.pivot:

    import pandas as pd

df = pd.read_csv('sample2.csv')
df1 = df.pivot(index=['id', 'fechaValor'], columns='sensorica', values='Valor').reset_index()
df1.columns.name = '' #Esto para eliminar el nombre de las columnas que era "sensorica"
print(df1)

    When printing df1we get the following dataframe:

        id      fechaValor          Acelerómetro    Humedad Temperatura
0   ADN-515 19/05/2022 22:02    329.476         658.732 233.081
1   ADN-515 19/05/2022 22:12    10.085          680.006 229.924

    Using pandas.pivot_table:

    It would be exactly the same but using another function:

    import pandas as pd

df = pd.read_csv('sample2.csv')
df1 = df.pivot_table(index=['id', 'fechaValor'], columns='sensorica', values='Valor').reset_index()
df1.columns.name = ''
print(df1)

    usingpandas.crosstab

    import pandas as pd

df = pd.read_csv('sample2.csv')
df1 = pd.crosstab(index=[df['id'], df['fechaValor']], columns=df['sensorica'], colnames=[None], values = df['Valor'], aggfunc=lambda x: x).reset_index()
print(df1)

    Using pandas.DataFrame.groupby:

    import pandas as pd

df = pd.read_csv('sample2.csv')
df1 = df.groupby(['id', 'fechaValor', 'sensorica']).agg({'Valor': lambda x: x}).unstack('sensorica').reset_index()
df1.T.reset_index(drop=True).T
df1.columns = ['id', 'fechaValor', 'Acelerómetro', 'Humedad', 'Temperatura']
print(df1)

    A disadvantage of this method is that when using groupby, multiple levels of columns are created, so we have to use reset_indexthe dataframetranspose (Con pandas.DataFrame.T) and then transpose it again, at the end we must give each column a name.

    additional note

    I recommend you try all the methods (there are more ways to do it but the answer would be too big) and execute them line by line, also read the documentation of each one to see the difference, advantages and/or disadvantages.

    For all the examples the result is the same as the first example but I omitted them to make the answer shorter.

    • 1