Python Pandas - Dataset with many columns - want to iterate over each column, add row values to new list only from fields that are not null

Question

I have a dataset that I am inhering of website logs that basically adds a new series of columns based on the number of pages visited. For example, if someone went to 2 pages on our website we'd have something like: visit_id, url_1, visit_datetime_1, url_2, visit_datetime_2. The problem is that some people visit just one page, and some visit 14. I want to simply this. See below for my current format and desired output. I guess I just don't understand how I will go through each column, when the number of fields are not always consistent (but the column names WILL be consistent: visit_id is a unique identifier, url_x, visit_datetime_x). I'm stumped.

Just to be clear below, visit_id 1000 visited 3 pages, 2000 visited 1 page, and 3000 visited 2 pages.

I've just never done anything like this before in Pandas and I'm just at a roadblock. I've gotten this far, which isn't far, but at least shows I'm trying. All help is appreciated.


visit_ids = []
urls = []
visit_datetimes = []

dataset = pd.read_excel('data.xlsx', engine='openpyxl')
df = pd.DataFrame(dataset)

for colname in df.iteritems():
    
    #do something to add to list

Is possible share Current format like text?

jezrael
– jezrael

2021-02-04 12:20:09 +00:00
Commented Feb 4, 2021 at 12:20 — jezrael
– jezrael, Commented Feb 4, 2021 at 12:20

jezrael · Accepted Answer · 2021-02-04 12:22:17Z

1

You can split last numbers after _ to MultiIndex and reshape by DataFrame.stack:

df = pd.read_excel('data.xlsx', engine='openpyxl')

df1 = df.set_index('visit_id')
df1.columns = df1.columns.str.rsplit('_', n=1, expand=True)

df1 = df1.stack().reset_index()

answered Feb 4, 2021 at 12:22

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

tenebris silentio Over a year ago

Thank you, this worked. Maybe it was much more simple than I expected. I ran this line by line and I think I understand now. Thank you! As soon as it will let me, I will check this off as answered.

Collectives™ on Stack Overflow

Python Pandas - Dataset with many columns - want to iterate over each column, add row values to new list only from fields that are not null

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related