CODEWITHSUNDEEP
pythonpandasdataframe
Abin Benny
Abin Benny

Reputation: 145

How to calculate values and stored in a particular column based on date using Python Dataframe?

I have a Dataframe which contains date,product,quantity and numbers columns.Based on this dataframe,I want to create a new Dataframe which contains month,year,product,measurement and value columns.I need overall data in each month and corresponding year in new dataframe instead of all dates.

Below images shows the two table:

First: enter image description here

I want the table like this:

Second : enter image description here

Dataframe:

import pandas as pd

data = {'Date': ['01-01-2021', '02-01-2021', '03-01-2021', '01-02-2021', '02-02-2021', '03-02-2021'],
    'product': ['Apple', 'Apple', 'Apple', 'Apple', 'Apple', 'Apple'],
    'quantity': [1,2,3,4,5,6],
    'numbers': [1,2,3,4,5,6]}
df = pd.DataFrame(data)
print(df)

Can anyone help me to solve this issue?

Upvotes: 0

Views: 67

Answers (1)

shubham koli
shubham koli

Reputation: 43

first we need to convert year, month, day, hours

# change the invoice_date format - String to Timestamp format
df['InvoiceDate'] = pd.to_datetime(df.InvoiceDate, format='%m/%d/%Y %H:%M')
df.insert(loc=2, column='Year_Month', value=df['InvoiceDate'].map(lambda x: 100*x.year + x.month))

df.insert(loc=3, column='Month', value=df.InvoiceDate.dt.month)

# +1 to make Monday=1.....until Sunday=7

df.insert(loc=4, column='Day', value=(df.InvoiceDate.dt.dayofweek)+1)

df.insert(loc=5, column='Hour', value=df.InvoiceDate.dt.hour)

Upvotes: 1

Related Questions