CODEWITHSUNDEEP
pythonpython-3.xpandas
Jackey12345
Jackey12345

Reputation: 159

Pandas write data to seperate csv files

I got a dataframe that looks like this

  Name      Location  Date      Time  Open  High   Low  Close  Volume  VWAP  Trades
4   Orange  New York  20200501  15:30:00  5.50  5.85  5.45   5.70    1500  5.73      95
5   Orange  New York  20200501  17:00:00  5.65  5.70  5.50   5.60    1600  5.65      54
6   Orange  New York  20200501  20:00:00  5.80  5.85  5.45   5.81    1700  5.73      41
0    Apple  Minsk     20200504  15:30:00  3.70  3.97  3.65   3.75    1000  3.60      55
1    Apple  Minsk     20200504  17:00:00  3.65  3.95  3.50   3.80    1200  3.65      68
2    Apple  Minsk     20200504  20:00:00  3.50  3.83  3.44   3.60    1300  3.73      71

How do I write each row to a different file based on the 'Name' and 'Location' column?

Desired output:

Location: "Entities\New York\Orange\TwoHours.csv"

    Name    Location  Date      Time  Open  High   Low  Close  Volume  VWAP  Trades
4   Orange  New York  20200501  15:30:00  5.50  5.85  5.45   5.70    1500  5.73      95
5   Orange  New York  20200501  17:00:00  5.65  5.70  5.50   5.60    1600  5.65      54
6   Orange  New York  20200501  20:00:00  5.80  5.85  5.45   5.81    1700  5.73      41

Location: "Entities\Minsk\Apple\TwoHours.csv"

     Name      Location  Date      Time  Open  High   Low  Close  Volume  VWAP  Trades
0    Apple  Minsk     20200504  15:30:00  3.70  3.97  3.65   3.75    1000  3.60      55
1    Apple  Minsk     20200504  17:00:00  3.65  3.95  3.50   3.80    1200  3.65      68
2    Apple  Minsk     20200504  20:00:00  3.50  3.83  3.44   3.60    1300  3.73      71

Could anyone help me out? the current code that I am using writes the entire dataframe to each file rather than just the specific rows:

for idx, rows in Dataframe.iterrows():
dest_dir = os.path.join('Entities', rows.Location, rows.Name)
csv_file = os.path.join(dest_dir, 'TwoHours.csv')
if not os.path.exists(csv_file):
    os.makedirs(dest_dir)
    print("Writing .csv's " + str(idx))
    df1.to_csv(csv_file, sep=";", index=False)

Upvotes: 2

Views: 46

Answers (1)

jezrael
jezrael

Reputation: 862406

Here is possible loop by DataFrame.groupby object with unpack name and loc from tuple and if necessary create folders, last write groups to files:

for (name, loc), g in Dataframe.groupby(['Name','Location']):

    dest_dir = os.path.join('Entities', loc, name)
    csv_file = os.path.join(dest_dir, 'TwoHours.csv')

    if not os.path.exists(csv_file):
        os.makedirs(dest_dir)

    g.to_csv(csv_file, sep=";", index=False)

Upvotes: 3

Related Questions