0

I have a dataframe of 3 columns. The dataframe example is presented below with the intended final result:

initial data                                            final result

column1    column2    column3                       column2       count     avg_column3
123        -6.23,101.45        3        -->       -6.23,101.45        3         4
335        -6.23,101.45        4        -->       -6.12,102.33        2         8
456        -6.23,101.45        5
324        -6.12,102.33        10
232        -6.12,102.33        6

I am trying to count how many unique values in column2 while simultaneously getting the value of column3 and getting the average. I know how to get the unique values using pivot_tables but how do I get the average of column3?

muka lipan
  • 47
  • 4
  • 5
    ``df.groupby("column2").agg(count=("column2", "size"), avg=("column3", "mean"))`` You can use a named aggregation – sammywemmy Dec 28 '20 at 06:58
  • Hi thank you for the prompt response. I seem to get an error of "No numeric types to aggregate". When I check the data type all the columns are in 'object' format. I have edited the question further cause my 2nd column is actually a coordinate. Any suggestion? – muka lipan Dec 28 '20 at 08:21
  • you probably need to convert column3 to numeric type(pd.to_numeric might help, or astype) – sammywemmy Dec 28 '20 at 08:24
  • Is t possible to convert column2 to numeric type? I still got the same error as before and I suspect the problem might have been column2 – muka lipan Dec 28 '20 at 08:50
  • @sammywemmy My mistake. you're right to convert the column3 to numeric type. Thank you so much for the help! – muka lipan Dec 29 '20 at 02:19

0 Answers0