Groupby dataframe by columns and save to different directories

user_11055992 0
Viewed 418

i have the following dataset:

X Y Z S A
_ _ _ _ _
0 1 2 1 1
8 5 3 1 2
5 6 7 2 1
9 8 9 2 2
6 7 7 1 3

i want to folder them accordingly their S and A columns like this

DATA\S1\A1
DATA\S1\A2
DATA\S2\A1

and I only want the corresponding XYZ values in my csv file

I have tried this piece of code. Creation of the folders work but I cannot save the corresponding csv file to proper folder. How should I fix the issue

df is list that containing 3 different pandas-dataframes

def write2path(df):
    path = getcwd()
    subjects = ["subject%d"%i for i in range(1,31)]
    activities = [str(i) for i in range(1,7)]
    try:
        path = path + "\\DATA"
        mkdir(path)
        
        for l in subjects:
            temp_path = path +"\\"+ l
            mkdir(temp_path)
            for k in activities:
                temp_path_child = temp_path +"\\" + k
                mkdir(temp_path_child)
                for j in range(len(df)):
                    ch='x'
                    
                    for i, x in df[j].loc[(['Subject0'] == int(l[7])) & (['Activity0'] == int(k))]: 
                        print(x)
                        val=chr(ord(ch)+j)
                        x.to_csv(temp_path_child+"\\"+f'sensor-{val}.csv', index=False)

    return self._engine.get_loc(self._maybe_cast_indexer(key))
  File "pandas\_libs\index.pyx", line 111, in pandas._libs.index.IndexEngine.get_loc
  File "pandas\_libs\index.pyx", line 135, in pandas._libs.index.IndexEngine.get_loc
  File "pandas\_libs\index_class_helper.pxi", line 109, in pandas._libs.index.Int64Engine._check_type
KeyError: False

what i am trying to achieve

pandas-groupby pandas dataframe csv python
2 Answers

Let's use pathlib and use some tidy functions:

from pathlib import Path

def create_folder(path):
    if not path.is_dir():
        path.mkdir(parents=True)

def if_file_exists(filename,trg_folder,dataframe):
    if trg_folder.joinpath(file).is_file():
        # if file exists then write behavior here.
    else:
        dataframe.to_csv(trg_folder.joinpath(filename),index=False)

then list your fully qualfied start_dir in the variable below and groupby - write to your directory:

start_dir = r'your_start_dir' # like /tmp/files/data 

for paths, group in df.groupby(['S','A']):
    paths = list(map(str,paths)) # if ints.
    target_folder = Path(start_dir).joinpath('DATA','S'+paths[0],'A'+paths[1])
    create_folder(target_folder)
    file_name = f"S{paths[0]}-A{paths[1]}.csv"
    if_file_exists(file_name,target_folder,group)

enter image description here

df = pd.read_csv("your_start_dir\DATA\S1\A1\S1-A1.csv")

print(df)

   X  Y  Z  S  A
0  0  1  2  1  1
user_93751020

If you want to create a csv file (say data.csv) for each combination of 'S' and 'A' then you do groupby and then iterate over each group saving each group seperately

Sample

df = pd.DataFrame({
    'X': [0,8,5,9,6],
    'Y': [1,5,6,8,7],
    'Z': [2,3,7,8,7],
    'S': [1,1,2,2,1],
    'A': [1,2,1,2,3]
})

for group_name, df_group in df.groupby(['S','A']):
  S = f"S{group_name[0]}"
  A = f"A{group_name[1]}"
  p = os.path.join(S, A)
  if not os.path.exists(p):
    os.makedirs(p)
    
  df_group[['X', 'Y', 'Z']].reset_index(drop=True).to_csv(os.path.join(p, f"{S}_{A}.csv"), index=False)

You will get files in below directory structure:

enter image description here

user_4239260
Related