How to make a new subset of columns from a dataframe rows?

user_17748458 0
Viewed 41

I'm still relatively new to Python. I have data that has come in a csv like this

ID Postcode Status Date Value
1 E1 2QJ sold June 2022 1000000
1 E1 2QJ listed February 2022 900000
1 E1 2QJ sold June 2018 850000
2 E2 3JJ sold January 2022 1250000
2 E2 3JJ listed October 2021 1000000
3 E3 4IQ listed July 2022 900000
4 E4 8RJ sold March 2021 850000

I would like it to look like this:

ID Postcode Status Date Value Status1 Date1 Value1 Status2 Date2 Value2
1 E1 2QJ sold Jun 2022 1000000 listed Feb 2022 900000 sold June 2018 850000
2 E2 3JJ sold Jan 2022 1250000 listed Oct 2021 1000000 NaN NaN NaN
3 E3 4IQ listed Jul 2022 900000 NaN NaN NaN Nan NaN NaN
4 E4 8RJ sold Mar 2021 850000 NaN NaN NaN Nan NaN NaN

Does anyone know what is the best way to do this?

pandas python
1 Answers

With the dataframe you provided:

import pandas as pd

df = pd.DataFrame(
    {
        "ID": [1, 1, 1, 2, 2, 3, 4],
        "Postcode": [
            "E1 2QJ",
            "E1 2QJ",
            "E1 2QJ",
            "E2 3JJ",
            "E2 3JJ",
            "E3 4IQ",
            "E4 8RJ",
        ],
        "Status": ["sold", "listed", "sold", "sold", "listed", "listed", "sold"],
        "Date": [
            "June 2022",
            "February 2022",
            "June 2018",
            "January 2022",
            "October 2021",
            "July 2022",
            "March 2021",
        ],
        "Value": [1000000, 900000, 850000, 1250000, 1000000, 900000, 850000],
    }
)

Here is one way to do it:

# Setup
records = (
    df.groupby(["ID", "Postcode"])
    .agg(list)[["Status", "Date", "Value"]]
    .to_dict(orient="index")
)
dfs = []

# Get new intermediate dataframes (1 row, n columns)
for key, value in records.items():
    temp_df = pd.DataFrame({"ID": [key[0]], "Postcode": [key[1]]})
    status, date, value = value.values()
    for i, (s, d, v) in enumerate(zip(status, date, value)):
        temp_df = pd.concat(
            [
                temp_df,
                pd.DataFrame({f"Status{i}": [s], f"Date{i}": [d], f"Value{i}": [v]}),
            ],
            axis=1,
        )
    dfs.append(temp_df)

# Final result
new_df = pd.concat(dfs).reset_index(drop=True)

And so:

print(new_df)
# Output
   ID Postcode Status0         Date0   Value0 Status1          Date1     Value1 Status2      Date2    Value2
0   1   E1 2QJ    sold     June 2022  1000000  listed  February 2022   900000.0    sold  June 2018  850000.0
1   2   E2 3JJ    sold  January 2022  1250000  listed   October 2021  1000000.0     NaN        NaN       NaN
2   3   E3 4IQ  listed     July 2022   900000     NaN            NaN        NaN     NaN        NaN       NaN
3   4   E4 8RJ    sold    March 2021   850000     NaN            NaN        NaN     NaN        NaN       NaN
user_112460560
Related