Trying to assign string values to column. But all I get is Nan. What to do?-CodePudding

I'm trying to update a Pandas df column with a column from another df, which changes daily.

What I mean to do is to transplant what's in this: Daily schedule of workers for all year

To this: Schedule of workers for today, column in red

I'd like to do it every day until July. It usually worked quite well. In 2023, the calendar was made with slight changes in the format, and I can't make Pandas read the data as I'd like.

I cannot actually assign a column from one database to a columns from the other. The code is accepted by Python, but all I get is NaN, not the strings I hoped for. All the values from the other column are strings. What am I doing wrong? Thanks!

Here's my code:

today = datetime.today().strftime("%d/%m/%Y")
df["status"] = df_diario[today].astype('str')
df["status"]

    nome
Ad                       NaN
Al                       NaN
An                       NaN
Ca                       NaN
Cl                       NaN
Da                       NaN
El                       NaN
Ga                       NaN
Hu                       NaN
Jo                       NaN
Jo                       NaN
Jo                       NaN
Jo                       NaN
Le                       NaN
Lu                       NaN
Lu                       NaN
Lui                      NaN
Ma                       NaN
Mar                      NaN
Mau                      NaN
Om                       NaN
Pa                       NaN
Pau                      NaN
Pe                       NaN
Ro                      inativo
Ro                       NaN
Ro                       NaN
Ron                      NaN
Vi                       NaN
Name: status, dtype: object

CodePudding user response：

what is the variable today that you used as the DataFrame accessor? Also, please format your answer so that others can read clearly and help you better.

However, if you do check your answer, there's one line that is not NaN, it is inactivo. It could be that both DataFrames are incompatible and having different indices. If you want to do an reassignment this way, you need to have identical index in both DataFrames.

CodePudding user response：

Found the answer! It actually was in MS Excel. If you type some text that looks like a date, it will automatically define it in a date format. For this reason, I could not transplant my column as I'd like.

Pandas "inherits" the date format from the Excel spreadsheet, so to speak. It would import some of my dates as datetime objects, unrecognizable by the code I had written. It didn't import all of them as such because I had done the 2022 table with Python itself, from Jan 24th on. Because I had manually typed 10/01/2023, in this year's table, Pandas interpreted it as datetime and thus my code didn't work. To prevent the mess, I had to type an apostrophe before the date in an Excel cell.