I used pandas and gspread to create a data frame based on a working google sheet. The dfrname
is my data frame:
sheetwork = client.open('RMA Daily Workload').sheet1
list_of_work = sheetwork.get_all_records()
dfr = pd.DataFrame(list_of_work, columns = ['Date' , '#Order'])
dfrname = dfr.rename(columns={"Date": "date", "#Order": "value"})
The output of my data frame is:
All the "Past" in the date column belongs to the former date, so I want to change all the "Past" in the date column to the former date. For example, change the date in row No.1 to 01/02/2020, and change the date in row No.3 to 01/03/2020.
The reason I want to change them is that I want to add up the values from those two cells so that my final output will be: 01/02/2020 16
(13 3), 01/03/2020 5
(2 3) ...
I'm new to python and I know this should be a simple question, but I searched online and didn't find any solutions. I tried so many ways but still cannot work it through. Please advise
CodePudding user response:
Try with where
and ffill
:
dfrname["date"] = dfrname["date"].where(dfrname["date"].ne("Past")).ffill()
>>> dfrname
date value
0 01/02/2020 13
1 01/02/2020 3
2 01/03/2020 2
3 01/03/2020 3
4 01/06/2020 5
CodePudding user response:
Pandas fillna()
is your friend.
Your date
values that are all strings called 'Past' should be converted to NaN
/None
. Then, you can 'forward-fill' those with the preceding value of the same column.
dfrname.loc[dfrname['date'] == 'Past', 'date'] = None
dfrname.fillna(method='ffill')
https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.fillna.html