Pandas split data frames into multiple csv's based on value from column

Question

I have a question similar to this one but I need some further steps. The thing is my file contains like 50k+ lines. Each line have 4 values "Indicator","Country","Date" and "value". I want to split my CSV based on country. I do not know how many countries there is so all countries with similar name should be in one CSV file and so on. The CSV file in not order either. I am using pandas and here is my code so far:

import pandas as pd
def read_csvfile():
    df = pd.read_csv('ebola_data_db_format.csv', sep= ',')
    
    #remove the unneeded columns
    df = df[df['Country'] != "Guinea 2"]  
    df = df[df['Country'] != "Liberia 2"]
   
    #reset the index
    df.reset_index(drop=True, inplace=True)
    print (df.head(10))

read_csvfile()

I want to be able to have a CSV file for every country so I can plot their data separately. Help please!

Quang Hoang · Accepted Answer · 2020-11-12 15:29:45Z

3

You can use groupby:

country_dfs = {k:v for k,v in df.groupby('Country')}

To save them in several csv files:

for k, v in df.groupby('Country'):
    v.to_csv(f'{k}.csv')

or from country_dfs:

for k, v in country_dfs.items():
    v.to_csv(f'{k}.csv')

edited Nov 12, 2020 at 15:29

answered Nov 12, 2020 at 15:21

Quang Hoang

151k11 gold badges64 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mohammed Over a year ago

After I group them how can I then split them into several csv depending on country? This is only to group not to split I guess

Mayank Porwal · Accepted Answer · 2021-03-04 08:33:50Z

2

You can use dictionary comprehension here:

dfs_dict = {j: df[df['Country'] == j] for j in df['Country'].unique()}

For saving country-wise data in country cdv's, do this:

for k, v in dfs_dict.items():
    v.to_csv('{}.csv'.format(k))

edited Mar 4, 2021 at 8:33

answered Nov 12, 2020 at 15:20

Mayank Porwal

34.2k9 gold badges45 silver badges65 bronze badges

2 Comments

Mohammed Over a year ago

This will group my csv file into countries. But I want to be able to save data depending on country automatically. I don't want to manually access "Guinea 2" by dfs_dict['Guinea 2']. I want my data to be saved automatically to files names after the country for example "Guinea.csv" and so on. How can I achieve that

Aaron Lee Over a year ago

@Mayank Porwal Hi I tried the saving formula but it gave me an error "'dict' object is not callable", do you know how to solve this? Thanks!

Collectives™ on Stack Overflow

Pandas split data frames into multiple csv's based on value from column

2 Answers 2

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related