How to loop over a dataframe, writing certain fields to files, per each unique value of a column and name the file according to that column?

user_6938793 0
Viewed 28

I have a combined table for multiple IDs.

ID Type Color Size Quantity
1 A Wht S 7
1 B Ylw L 9
2 A Grn S 2
2 C Wht M 7
3 B Blk M 3

For each unique ID, I would like to write a text file, named <ID>.txt.

Within each file, I would like to write the Type and Color values. For example:

1.txt

AWht
BYlw

Here's what I have so far, but I am stuck on two things:

  1. Insert ID values as the file names. It seems to only accept text strings.
  2. Insert Type and Color values in the written contents of each file.
import pandas as pd
data = [['1','A','Wht','S','7'],['1','B','Ylw','L','9'],['2','A','Grn','S','2'],['2','C','Wht','M','7'],['3','B','Blk','M','3']]
df = pd.DataFrame(data, columns = ['ID','Type','Color','Size','Quantity'])
IDs = df.ID.unique()
IDs.sort()
for i in IDs:
    with open('filename.txt', 'w') as f:
        f.write('TypeColor')
pandas python
1 Answers

You can use:

# Enhanced by @piRSquared
for name, subdf in df.groupby('ID'):
    data = subdf['Type'] + subdf['Color']
    data.to_csv(f'{name}.csv', index=False, header=None)

# Old answer
# for name, subdf in df.groupby('ID'):
#     with open(f'{name}.txt', 'w') as fp:
#         data = subdf['Type'] + subdf['Color']
#         data.to_csv(fp, index=False, header=None)

Output:

>>> %cat 1.txt
AWht
BYlw

>>> %cat 2.txt
AGrn
CWht

>>> %cat 3.txt
BBlk
user_152399510
Related