CODEWITHSUNDEEP
pythonpython-3.xpandas
James Pi
James Pi

Reputation: 81

Python Pandas Write Calculations to New Csv File

Using: Python 3.6, Pandas 0.22

I am trying to take the final line of the DataFrame where I am calculating the average and write it to a new .csv file in a specific format.

df2 = pd.read_csv("Data.csv")

gname = df2.groupby(['NAME'])

for NAME,  NAME_df2 in gname:
    df2['DATE'] = pd.to_datetime(df2['DATE'])
    df2.groupby([df2.DATE.dt.month,'NAME'])['SNOW'].mean().sort_values().to_csv('avg.csv')

Here is my desired output for the avg.csv file:

NAME   MONTH    AVERAGE
    GRAND RAPIDS GERALD R FORD INTERNATIONAL AIRPORT, MI US January, 0.006451613

In my head, the logic should be:

df2.groupby([df2.DATE.dt.month,'NAME'])['SNOW'].mean().sort_values().to_csv('avg.csv', columns = 'NAME', 'MONTH', 'AVERAGE')

I have tried to create the months and column header 'AVERAGE' in variables to write to the new file, but that does not work. I have been searching through documentation to test more things, but cannot find anything relevant to this issue.

Or another attempt to create the new column, but this does not work for linking them with the 'NAME' column:

df2 = df2.convert_objects(convert_numeric=True)
df['MONTH']='?'

Upvotes: 2

Views: 574

Answers (1)

Alexander
Alexander

Reputation: 109706

(df
 .assign(MONTH=df['DATE'].dt.strftime('%B'))  # Use `%B` for full name of month
 .groupby(['NAME', 'MONTH'], as_index=False)['SNOW']
 .agg({'AVERAGE': 'mean'})  # Effectively renames the SNOW column to AVERAGE.
)

So that the months sort in natural order, you may want to use .strftime('(%m) %B') which would result in '(01) January', '(02) February', etc.

To include the year as a separate column, you could do something like this:

dates = pd.DatetimeIndex(df['DATE'])
(df
 .assign(MONTH=dates.dt.strftime('%B'),  # Use `%B` for full name of month
         YEAR=dates.dt.strftime('%Y'))
 .groupby(['NAME', 'YEAR', 'MONTH'], as_index=False)['SNOW']
 .agg({'AVERAGE': 'mean'})  # Effectively renames the SNOW column to AVERAGE.
)

Upvotes: 2

Related Questions