0

I want to separate my df according to client_id column. Then, I want to export each of them in different excel files. For example, I'd like to have an Excel file which only includes clients with id number 100. Also, the same for 200, 300 etc. How can I do that?

     id  employee_id company_name  client_id
0    A1            1            A        100
1    A2            2            A        100
2    A3            3            A        100
3    A4            4            A        100
4    B1            5            B        200
5    B2            6            B        200
6    B3            7            B        200
7    B4            8            B        200
8    B5            9            B        200
9    B6           10            B        200
10   B7           11            B        200
11   B8           12            B        200
12   C1           13            C        300
13   C2           14            C        300
14   C3           15            C        300
15   C4           16            C        300
16   C5           17            C        300
17   C6           18            C        300
18   C7           19            C        300
19   C8           20            C        300
20   C9           21            C        300
21  C10           22            C        300

I want to have this columns as an Excel file.

     id  employee_id company_name  client_id
0    A1            1            A        100
1    A2            2            A        100
2    A3            3            A        100
3    A4            4            A        100

Then following:

4    B1            5            B        200
5    B2            6            B        200
6    B3            7            B        200
7    B4            8            B        200
8    B5            9            B        200
9    B6           10            B        200
10   B7           11            B        200
11   B8           12            B        200

This is my df. I've tried:


for i in client_id:
    print(df[df.client_id == i])

I can separate them according to the unique values but I don't know how to store each of them and create different Excel file.

Tunahan A.
  • 142
  • 8
  • You are in the right direction though. But why store them? Why not write them straight away in an excel file using `df[df.client_id == i].to_excel('file.xlsx')`? – MikeMajara Sep 26 '19 at 17:48

1 Answers1

4

You can take advantage of groupby.

group = df.groupby('client_id')

for key, df in group:
    df.to_excel(f'{key}.xlsx')
Michael Gardner
  • 1,693
  • 1
  • 11
  • 13
  • Thank you so much! It worked. I was just wondering why you are using **f** before '{key}.xlsx'? – Tunahan A. Sep 26 '19 at 19:18
  • @dreamer `f-strings` are the latest and greatest way to format strings. All prior methods are terrible in comparison. https://realpython.com/python-f-strings/ – Michael Gardner Sep 26 '19 at 19:54