Home > Enterprise >  Change values of some cells in a data frame by Python - change them to the same value from the forme
Change values of some cells in a data frame by Python - change them to the same value from the forme

Time:02-16

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: output

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

  • Related