Python Pandas - Writing Groupby Output To File

I used the following to get proportion information on my data: >>>testfile = pd.read_csv('CCCC_output_all_FINAL.txt', delimiter='\t', header=0) >>> testdf = pd.Da

Solution 1:

Use reset_index():

testdf.reset_index().to_csv('CCCC_output_summary.txt', sep='\t', header=True, index=False)

Solution 2:

I had the same problem. reset_index() as explained above did not work for me. I used an answer from another Stackoverflow and it worked wonderfully. Details are below.

Input csv has data under following two columns: Item Code, Quantity

Output needed: Average quantity grouped by item and both columns to be part of csv.

Initial code:

import pandas as pd

data_directory = os.path.join("D:\\data")
df = pd.read_csv(os.path.join(data_directory, "input_file.csv"))

df_avg = df.groupby("Item Code")["Quantity"].mean()
df_avg.reset_index().to_csv(os.path.join(data_directory,'output_file.csv'), sep='\t', header=True, index=False )

Output received: Only the average quantity was written to output file

Following code solved the problem:

import pandas as pd

data_directory = os.path.join("D:\\data")
df = pd.read_csv(os.path.join(data_directory, "input_file.csv"))

df.groupby("Item Code")["Quantity"].mean().reset_index()[["Item Code", "Quantity"]].to_csv(os.path.join(data_directory,'output_file.csv'))

By the above code, I got the output file which has two columns: Item Code and Quantity and the second column contains average of quantity for each Item code.

Other stack overflow reference: Pandas groupby to to_csv