0

Suppose I have the following dataframe called "df":

ID;Type
1;A
1;A
1;A
1;A
1;A
2;A
2;A
3;B
4;A
4;B

Now I want to sum up / group by this dataframe by ID and Type and export the result as a csv.

The result should look like:

ID;Type;Count
1;A;5
2;A;2
3;B;1
4;A;1
4;B;1

My code for summing up / group by is as follows:

import pandas as pd
frameexport=df.groupby(["ID", "Type"])["Type"].count()
print(frameexport)

ID  Type
1   A       5
2   A       2
3   B       1
4   A       1
    B       1

This is not exaclty what I hoped for. The last entry for ID 4 is not repeating the "4" again.

I try to export it to a csv:

frameexport.to_csv(r'C:\Path\file.csv', index=False, sep=";")

But it doesn't work, it just looks like this:

Type
5
2
1
1
1

Problem here is that I call it on an object and obviously to_csv doesn't work like this.

How can I get the desired result:

ID  Type    Count
1   A       5
2   A       2
3   B       1
4   A       1
4   B       1

exported to a csv?

PSt
  • 97
  • 11

1 Answers1

1

There is MultiIndex Serie, so need remove index=False and for correct column name is used rename:

frameexport.rename('Count').to_csv(r'C:\Path\file.csv', sep=";")

Or create DataFrame by Series.reset_index, then default index is necessary omit, so index=False is correct:

frameexport.reset_index(name='Count').to_csv(r'C:\Path\file.csv', index=False, sep=";")
jezrael
  • 822,522
  • 95
  • 1,334
  • 1,252