I have a problem with an excel file! and I want to automate it by using python script to complete a column based on the information of the first column: for example: if data == 'G711Alaw 64k' or 'G711Ulaw 64k' print('1-Jan) till find it == '2-Jan' then print('2-Jan') and so on.

befor automate

I need its looks like this after automate: after automate

Is there anyone can help me to do solve this issue? The file: the excel file

Thanks a lot for your help.

CodePudding user response：

Try this, pandas reads your jan-1 is datetime type, if you need to change it to a string you can set it directly in the code, the following code will directly assign the value read to the second column：

import pandas as pd

df = pd.read_excel("add_date_column.xlsx", engine="openpyxl")
sig = []


def t(x):
    global sig
    if not isinstance(x.values[0], str):
        tmp_sig = x.values[0]
        if tmp_sig not in sig:
            sig = [tmp_sig]
    x.values[1] = sig[-1]
    return x


new_df = df.apply(t, axis=1)
new_df.to_excel("new.xlsx", index=False)

CodePudding user response：

The concept is very simple :

• If the value is date/time, copy to the [same row, next column].
• If not, [same row, next column] is copied from [previous row, next column].

You do not specifically need Python for this task. The excel formula for this would be;

=IF(ISNUMBER(A:A),A:A,B1)

Instead of checking if it is date/time, I took adavantage of the fact that the rest of the entries are alphanumeric (including both alphabets and numbers). This formula is applied on the new column.

Of course, you might already be in Python and just work within the same environment. So, here's the loop :

for i in range(len(df)):
    if type(df["Orig. Codec"][i]) is datetime:
        df["Column1"][i] = df["Orig. Codec"][i]
    else:
        df["Column1"][i] = df["Column1"][i-1]

There might be ways to lambda function for the same concept, not that I am aware of how to apply lambda and shift at the same time.