Python export dictionary to csv in appropriate format

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 enter image description here

# 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)

Answer

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