Python export dictionary to csv in appropriate format

Question

Context
I am trying to export value_counts of a dataframe to a csv.

Blocker
The code below generate 2 columns, key and values.
[3]: https://i.stack.imgur.com/2Xw4S.png

What is the best way to export to csv? I've tried converting to dataframe, but this doesn't produce an appropriate output.

Input dataframe

# load data
df = sns.load_dataset('mpg')

# View input dataframe
df.head()

# convert value counts to a dataframe
freq = {}
for c in df.columns:
    fre= df[c].value_counts()
    freq[c] = fre

ff = pd.DataFrame(freq.items())

# View output dataframe 
print(ff)

# export dataframe to csv
ff.to_csv(file_path)

if you want to generate the output row wise then put freq inside a list — Wrench, Aug 15 '21 at 07:52
Please [do not post images](https://meta.stackoverflow.com/questions/285551/why-not-upload-images-of-code-errors-when-asking-a-question) of your data or errors. You can include [code that creates a dataframe such as `df.to_dict()` or the output of `print(df)`](https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples) — Cimbali, Aug 15 '21 at 07:53
Print `df` after reading it i.e. `df = sns.load_dataset('mpg')` then add the output to the question. — ThePyGuy, Aug 15 '21 at 07:55

score 0 · Accepted Answer · answered Aug 15 '21 at 08:38

It's not clear what format you want. But I can offer this if you like: three columns - value, amount and name. 'name' is the name of the column in the original dataframe, 'value' is the value of this column and 'amount' is the number of these values in the original dataset.

Here is the code to generate:

# load data
df = sns.load_dataset('mpg')

# View input dataframe
df.head()

# convert value counts to a dataframe
freq = []
for c in df.columns:
    fre= pd.DataFrame(df[c].value_counts()).reset_index()
    fre.columns=['value', 'amount']
    fre['column'] = c
    freq.append(fre)

ff = pd.concat(freq)

# View output dataframe 
print(ff)

# export dataframe to csv
ff.to_csv(file_path, index=False)

Output:

    value   amount  column
0   13.0    20  mpg
1   14.0    19  mpg
2   18.0    17  mpg
3   15.0    16  mpg
4   26.0    14  mpg
... ... ... ...
1071    toyota celica gt liftback   1   name
1072    buick skyhawk   1   name
1073    fiat strada custom  1   name
1074    mercury zephyr 6    1   name
1075    dodge coronet custom (sw)   1   name

Python export dictionary to csv in appropriate format

1 Answers1