CODEWITHSUNDEEP
pythonpandasdataframe
user20311840
user20311840

Reputation:

String Conversion of Selected Columns Based on a Condition

I'm fairly new to Python and have been studying it for financial data manipulation.

I have a dataframe with strings representing millions (MM) of dollars of Free Cash Flow:

    FCF (t) FCF (t-1)   FCF (t-2)   FCF (t-3)   FCF (t-4)   FCF (t-5)   FCF (t-6)   Total Assets LTM
1006    0   0   0   0   0   0   0   378MM
1007    0   0   0   0   0   0   0   2.60B
1008    -3MM    -12MM   -1MM    -1MM    0   0   0   17MM
1009    261MM   265MM   603MM   118MM   148MM   199MM   179MM   5.00B

How do I convert these strings with "B" (billions) to be multiplied by 1000 so that the results are as shown below?

    FCF (t) FCF (t-1)   FCF (t-2)   FCF (t-3)   FCF (t-4)   FCF (t-5)   FCF (t-6)   Total Assets LTM
1006    0   0   0   0   0   0   0   378
1007    0   0   0   0   0   0   0   2600
1008    -3  -12 -1  -1  0   0   0   17
1009    261 265 603 118 148 199 179 5000

Thanks for the help!

Saw this suggestion using a series but I'm lost on how to apply this to a dataframe:

df = pd.DataFrame(
    {
        "date": pd.Series(["FCF (t)", "FCF (t-1)",   "FCF (t-2)",   "FCF (t-3)",   "FCF (t-4)",  "FCF (t-5)",   "FCF (t-6)",   "Total Assets"], dtype=np.dtype("O")),
        "value": pd.Series(["1242MM",    "-216MM",    "1.11B",   "-994MM",    "519MM", "-3069MM",   "1.49B",   "360"], dtype=np.dtype("O"))
    }
)

def parse_currency(value):
    if value[-1] == "M":
        return float(value[0:-1])
    elif value[-1] == "B":
        return float(value[0:-1]) * 1000
    else:
        return float(value[0:-1]) / 1000


ser = df["value"]
ser = ser.apply(parse_currency)
df["value"] = ser

Upvotes: 0

Views: 40

Answers (1)

YUVI_1303
YUVI_1303

Reputation: 122

Considering that your Dataframe has only values with MM and B, I created a function that first splits each element of the column "value" by either MM or B and takes the first element that is the number part, and saves it in another list.

This list now replaces the Column value entirely

def parse_currency(df):
    val = df["value"].to_list()
    val_new = []
    for cur in val:
        if "MM" in cur:
            val_new.append(cur.split("MM")[0])
        elif "B" in cur:
            val_new.append(float(cur.split("B")[0])*1000)
        
    return val_new
   
val = parse_currency(df)
df['value'] = val

# print(df)

Upvotes: 1

Related Questions