0

I'm trying to spread the measure column. Have no idea why its not working.. Can anybody help please. Thanks.

here is the head() of the dataset:

    year    month   measure        day  Temp
0   2014    12  Max.TemperatureF    X1  64
1   2014    12  Mean.TemperatureF   X1  52
2   2014    12  Min.TemperatureF    X1  39
3   2014    12  Max.Dew.PointF      X1  46
4   2014    12  MeanDew.PointF      X1  40

The code:

df = df.pivot)table(index=['year','month','day'],columns='measure',values='Temp')

The Error:

DataError: No numeric types to aggregate
phil
  • 3
  • 2
  • I don't think `pivot` handles several columns in index. Try `pivot_table` instead? – Ben.T Jan 09 '20 at 20:38
  • Thanks for the help. I tried, and now theres a new error: "DataError: No numeric types to aggregate" – phil Jan 09 '20 at 20:54
  • what gives `df.Temp.dtype`? see this [link](https://stackoverflow.com/questions/39229005/pivot-table-no-numeric-types-to-aggregate) for other solutions maybe :) – Ben.T Jan 09 '20 at 21:01

1 Answers1

0

Use pivot_table for this.

import pandas as pd

df = pd.DataFrame({
    "year":  [2014]*5,
    "month": [12]*5,
    "day": [1]*5,
    "measure": ["Max.TemperatureF", "Mean.TemperatureF", "Min.TemperatureF", "Max.Dew.PointF", "Mean.Dew.PointF"],
    "Temp": [64, 52, 39, 46, 40]
})
df.head()

   year  month  day            measure  Temp
0  2014     12    1   Max.TemperatureF    64
1  2014     12    1  Mean.TemperatureF    52
2  2014     12    1   Min.TemperatureF    39
3  2014     12    1     Max.Dew.PointF    46
4  2014     12    1    Mean.Dew.PointF    40

df.pivot_table(values="Temp", index=["year", "month", "day"], columns="measure")
df.head()

measure         Max.Dew.PointF  Max.TemperatureF  Mean.Dew.PointF  Mean.TemperatureF  Min.TemperatureF
year month day
2014 12    1                46                64               40                 52                39
Russell
  • 3,099
  • 2
  • 14
  • 18
  • DataError: No numeric types to aggregate – phil Jan 09 '20 at 20:57
  • @phil that means there's non-numeric data in the `Temp` column. I replicated your error by changing one of the Temps in the toy example to be `"test"`. My guess is you have a string in the field and need to replace it with `None` ahead of time so it gets properly represented as null/NaN. – Russell Jan 09 '20 at 21:52